studyhard24

--DBMS_XPLAN_예제

-- 해당 SQL 찾을 때 사용, SQL_ID 알거나 SQL 일부 내용 알 때 사용
SELECT *  
FROM V$SQL 
WHERE SQL_FULLTEXT LIKE '%SELECT%TB_TEST_1%' 
--WHERE SQL_ID='7abcdefegd' 
; 

-- ALTER SESSION SET CURRENT_SCHEMA 설정시 필요한 SYNONYM 조회 

-- DBA_OBJECTS에서 조회하거나 정확하게 하려면 DBA_SYNONYMS 조회

SELECT * 
FROM DBA_OBJECTS
WHERE OBJECT_NAME='TB_TEST_1';

SELECT * 

FROM DBA_SYNONYMS 

WHERE TABLE_NAME='TB_TEST_1';

-- SQL에 OWNER 없을 때 주로 사용 

ALTER SESSION SET CURRENT_SCHEMA=USERNAME; 

-- BIND 변수 사용할 때 사용

-- V$BIND_CAPTURE 에 없을 때 DBA_HIST_SQLBIND 에서 조회

SELECT * FROM V$BIND_CAPTURE WHERE SQL_ID='7abcdefegd';
SELECT * 
FROM DBA_HIST_SQLBIND 
WHERE SQL_ID='7abcdefegd' ORDER BY SNAP_ID DESC, INSTANCE_NUMBER, POSITION;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(SQL_ID,PLAN_HASH_VALUE,NULL,'advanced allstats last'));
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('7abcdefegd',1107028900,NULL,'advanced allstats last'));

-- 플랜 확인 필요한 SQL에  /*+ gather_plan_statistics */ 삽입
SELECT /*+ gather_plan_statistics */ *

FROM TB_TEST_1;

-- 직전에 사용한 실제 플랜 확인 가능 

-- 결과값을 메모장이나 별도 화면에서 붙여 넣어서 플랜 확인

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));

-- 현재 세션에서 수행했던 실제 플랜 확인 가능 (주로 위에 방법이 편해서 사용)
SELECT PREV_SQL_ID, PREV_CHILD_NUMBER 
FROM V$SESSION
WHERE SID=USERENV('SID') AND USERNAME IS NOT NULL
AND PREV_HASH_VALUE <>0;

SELECT * FROM TABLE(DBMS_XPALN.DISPLAY_CURSOR('7abcdefegd','0','advanced allstats last'));
 

-- SQL 문에 /*+ gather_plan_statistics */ 입력

SELECT /*+ gather_plan_statistics */ SYSDATE FROM DUAL;

-- 실제 플랜 확인할 SQL 수행한 후에 바로 수행 

SELECT * FROM TABLE(DBMS_XPALN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED ALLSTATS LAST'));

-- 내가 수행한 SQL 찾을 때 사용

SELECT PREV_SQL_ID, PREV_CHILD_NUMBER FROM V$SESSION WHERE SID= USERENV('SID') AND USERNAME IS NOT NULL AND PREV_HASH_VALUE <>= 0;

-- SQLID 

SELECT * FROM TABLE(DBMS_XPLAN.IDSPLAY_CURSOR('SQLID','0','ADVANCED ALLSTATS LAST'));

oracle 일 경우 update , merge 둘 다 가능

UPDATE 문

-- TB_TEST1가 100건 , TB_TEST2 30건일 경우 30건만 UPDATE 수행 됩니다.

-- 하지만 WHERE 을 생략할 경우 100건  모두 UPDATE ( 당연한 얘기같지만 혹시나 해서요..)

UPDATE  TB_TEST1 A

     SET (COL3, COL4 ) = ( SELECT COL3, COL4

                                  FROM TB_TEST2 

                                 WHERE COL1 = A.COL1 AND COL2 = A.COL2 )

WHERE EXISTS

(SELECT 1

 FROM TB_TEST2

WHERE COL1 = A.COL1 AND COL2 = A.COL2);

MERGE 문

-- MERGE는 INSERT를 같이 할 수 있는 장점도 있습니다.

-- ON 조건에 만족하지 않으면 INSERT 할 때는 아래 주석을 푸시면 됩니다.

MERGE INTO TB_TEST1 A

USING TB_TEST2 B

ON ( A.COL1 = B.COL1 AND A.COL2 = B.COL2 )

WHERE MATCHED THEN

UPDATE SET A.COL3= B.COL3, A.COL4=B.COL4

-- WHERE NOT MATCHED THEN 

-- INSERT (A.COL1, A.COL2, A.COL3, A.COL4)

-- VALUES (B.COL1, B.COL2, B.COL3, B.COL4)                   

-- ** oracle 10g까지만 update 할 때 /*+ bypass_ujvc */ 가능

declare

     x number;

begin

 dbms_job.submit

(

job =>x

,what =>'t_owner.p_test_01;'

,next_date=>sysdate

,interval =>'trunc(sysdate)+1'

,no_parse => false

);

commit;

dbms_output.put_line('job number is:'||to_char(x));

end;

/

create or replace procedure t_owner.p_create_table is

v_sql_cd number(10);