SQL PROFILE 로 SQL TEXT 수정 없이, 튜닝 하기
-. ORACLE Tuning Pack 라이센스 필요 ( SQL PLAN BASELINES 는 별도 라이센스 필요 없음 )
-. SQL PROFILE 로 긴급 조치 후, 반드시 Application(SQL TEXT) 수정 필요
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-- 1. V$SQL 통해 SQL PROFILE 적용하기
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
SCOTT@ORACLE19>alter system flush shared_pool;
System altered.
-- 적용하고 싶은 PLAN : aq1rf843yf3bm ( NL PLAN )
-- 수정 하고 싶은 SQL : 6d1sumdtz1mr8 ( HASH PLAN )
SCOTT@ORACLE19>set autotrace on explain
SCOTT@ORACLE19>SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981.06.09 00:00:00 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981.11.17 00:00:00 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980.12.17 00:00:00 800 20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981.04.02 00:00:00 2975 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1982.12.09 00:00:00 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981.02.20 00:00:00 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981.02.22 00:00:00 1250 500 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981.09.28 00:00:00 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981.05.01 00:00:00 2850 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."DEPTNO"="A"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - TG_PROFILE
SCOTT@ORACLE19>SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980.12.17 00:00:00 800 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981.02.20 00:00:00 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981.02.22 00:00:00 1250 500 30
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981.04.02 00:00:00 2975 20
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981.09.28 00:00:00 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981.05.01 00:00:00 2850 30
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981.06.09 00:00:00 2450 10
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1982.12.09 00:00:00 3000 20
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981.11.17 00:00:00 5000 10
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."DEPTNO"="A"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - TG_PROFILE
SCOTT@ORACLE19>
SCOTT@ORACLE19>set autotrace off
SCOTT@ORACLE19> SELECT /*+ SEARCH_SQL_ID */ A.SQL_ID, A.PLAN_HASH_VALUE, CHILD_NUMBER, A.ADDRESS, A.SQL_FULLTEXT, EXECUTIONS, SQL_PROFILE
FROM GV$SQL A
WHERE 1=1
AND ( INSTR(SQL_TEXT,'/*+ TG_PROFILE ORDERED',1,1) >= 1 )
AND ( INSTR(SQL_TEXT,'SEARCH_SQL_ID',1,1) = 0 )
AND SQL_TEXT NOT LIKE 'EXPLAIN PLAN%'
AND SQL_TEXT NOT LIKE 'DECLARE%'
ORDER BY LAST_ACTIVE_TIME ;
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER ADDRESS SQL_FULLTEXT EXECUTIONS SQL_PROFILE
------------- --------------- ------------ ---------------- -------------------------------------------------- ---------- ------------------------------
aq1rf843yf3bm 4192419542 0 000000006A603308 SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM 1
dept a, emp b WHERE b.deptno = a.deptno
6d1sumdtz1mr8 615168685 0 0000000068EAC8E8 SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FRO 1
M dept a, emp b WHERE b.deptno = a.deptno
------------------------------------------------------------------------------------------------------------------------------------
-- V$SQL PLAN 가지고 SQL PROFILE 생성 하기
------------------------------------------------------------------------------------------------------------------------------------
-- 적용하고 싶은 PLAN : aq1rf843yf3bm ( NL PLAN )
-- 수정 하고 싶은 SQL : 6d1sumdtz1mr8 ( HASH PLAN )
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
select extractvalue(value(d), '/hint') as outline_hints bulk collect
into ar_profile_hints
from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from v$sql_plan
where sql_id = 'aq1rf843yf3bm' -- apply plan
and child_number = 0 -- apply plan
and other_xml is not null ) ) d;
select sql_fulltext
into cl_sql_text
from v$sql
where sql_id = '6d1sumdtz1mr8' -- target sql
and child_number = 0; -- target sql
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => 'DEFAULT'
, name => '6d1sumdtz1mr8_profile'
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, REPLACE => TRUE
);
end;
/
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
-- ENDABLE / DISABLED 확인
SELECT NAME, CATEGORY, SQL_TEXT, CREATED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING
FROM DBA_SQL_PROFILES ;
-- DROP SQL PROFILE
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'6d1sumdtz1mr8');
-- DISABLED
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(NAME => 'tune_tg_full', ATTRIBUTE_NAME => 'STATUS', VALUE => 'DISABLED');
-- ENABLED
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(NAME => 'tune_tg_full', ATTRIBUTE_NAME => 'STATUS', VALUE => 'ENABLED');
-- SQL PROFILE 작동 확인
SCOTT@ORACLE19>set autotrace on explain
SCOTT@ORACLE19>SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981.06.09 00:00:00 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981.11.17 00:00:00 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980.12.17 00:00:00 800 20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981.04.02 00:00:00 2975 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1982.12.09 00:00:00 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981.02.20 00:00:00 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981.02.22 00:00:00 1250 500 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981.09.28 00:00:00 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981.05.01 00:00:00 2850 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 | <<< USE_HASH 힌트가 있음에도 불구하고, NESTED LOOP JOIN 으로 풀림
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."DEPTNO"="A"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (2), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
U - ORDERED / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
E - TG_PROFILE
3 - SEL$1 / B@SEL$1
U - USE_HASH(B) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- SQL profile "6d1sumdtz1mr8_profile" used for this statement << SQL PROFILE 사용 NOTE 됨
SCOTT@ORACLE19>
SCOTT@ORACLE19>set autotrace off
SCOTT@ORACLE19>SELECT /*+ SEARCH_SQL_ID */ A.SQL_ID, A.PLAN_HASH_VALUE, CHILD_NUMBER, A.ADDRESS, A.SQL_FULLTEXT, EXECUTIONS, SQL_PROFILE
FROM GV$SQL A
WHERE 1=1
AND ( INSTR(SQL_TEXT,'/*+ TG_PROFILE ORDERED',1,1) >= 1 )
AND ( INSTR(SQL_TEXT,'SEARCH_SQL_ID',1,1) = 0 )
AND SQL_TEXT NOT LIKE 'EXPLAIN PLAN%'
AND SQL_TEXT NOT LIKE 'DECLARE%'
ORDER BY LAST_ACTIVE_TIME ;
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER ADDRESS SQL_FULLTEXT EXECUTIONS SQL_PROFILE
------------- --------------- ------------ ---------------- -------------------------------------------------- ---------- ------------------------------
aq1rf843yf3bm 4192419542 0 000000006A603308 SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM 1
dept a, emp b WHERE b.deptno = a.deptno
6d1sumdtz1mr8 4192419542 0 0000000068EAC8E8 SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FRO 1 6d1sumdtz1mr8_profile <<< V$SQL.SQL_PROFILE 정보가 업데이트 됨
M dept a, emp b WHERE b.deptno = a.deptno
2 rows selected.
SCOTT@ORACLE19>
-- DROP SQL PROFILE
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'6d1sumdtz1mr8_profile');
-- ENDABLE / DISABLED 확인
SELECT NAME, CATEGORY, SQL_TEXT, CREATED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING
FROM DBA_SQL_PROFILES ;
-- 삭제 확인
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
-- 2. AWR 통해 SQL PROFILE 적용하기
-------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------
SCOTT@ORACLE19>alter system flush shared_pool ;
System altered.
SCOTT@ORACLE19>
SCOTT@ORACLE19>SELECT /*+ SEARCH_SQL_ID */ A.SQL_ID, A.PLAN_HASH_VALUE, CHILD_NUMBER, A.ADDRESS, A.SQL_FULLTEXT, EXECUTIONS, SQL_PROFILE
FROM GV$SQL A
WHERE 1=1
AND ( INSTR(SQL_TEXT,'/*+ TG_PROFILE ORDERED',1,1) >= 1 )
AND ( INSTR(SQL_TEXT,'SEARCH_SQL_ID',1,1) = 0 )
AND SQL_TEXT NOT LIKE 'EXPLAIN PLAN%'
AND SQL_TEXT NOT LIKE 'DECLARE%'
ORDER BY LAST_ACTIVE_TIME ;
SQL_ID PLAN_HASH_VALUE CHILD_NUMBER ADDRESS SQL_FULLTEXT EXECUTIONS SQL_PROFILE
------------- --------------- ------------ ---------------- -------------------------------------------------- ---------- ------------------------------
aq1rf843yf3bm 4192419542 0 0000000068A1D2C8 SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM 1
dept a, emp b WHERE b.deptno = a.deptno
6d1sumdtz1mr8 615168685 0 0000000067677688 SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FRO 1
M dept a, emp b WHERE b.deptno = a.deptno
2 rows selected.
SCOTT@ORACLE19>
-- AWR 로 부터 PLAN 을 가져오기 위해서는 DBA_HIST_SQLSTAT 에 등록 필요함
SELECT * from dba_hist_sql_plan where sql_id = 'aq1rf843yf3bm' and plan_hash_value = 4192419542 ;
SELECT * from dba_hist_sqlstat where sql_id = 'aq1rf843yf3bm' and plan_hash_value = 4192419542 ;
-- dba_hist_sqlstat 에 없으면 임의로 반복 수행 해서 dba_hist_sqlstat 에 등록 되도록 유도
DECLARE
I NUMBER := 1 ;
V_STR VARCHAR2(400) := 'SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno';
BEGIN
FOR I IN 1..100000
LOOP
EXECUTE IMMEDIATE V_STR ;
END LOOP ;
COMMIT;
END ;
-- snap_shot 찍기
select dbms_workload_repository.create_snapshot() AS Snap_id from dual ;
-- 적용하고 싶은 SQL 로 마찬가지로 V$SQL 에 등록을 위해 임의로 수행 필요함
SCOTT@ORACLE19>set autotrace on explain
SCOTT@ORACLE19>SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980.12.17 00:00:00 800 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981.02.20 00:00:00 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981.02.22 00:00:00 1250 500 30
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981.04.02 00:00:00 2975 20
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981.09.28 00:00:00 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981.05.01 00:00:00 2850 30
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981.06.09 00:00:00 2450 10
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1982.12.09 00:00:00 3000 20
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981.11.17 00:00:00 5000 10
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 14 | 812 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 532 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B"."DEPTNO"="A"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
E - TG_PROFILE
SCOTT@ORACLE19>
SCOTT@ORACLE19>SELECT SQL_ID, PLAN_HASH_VALUE, SQL_PROFILE from dba_hist_sqlstat
where sql_id in ( 'aq1rf843yf3bm' ,'6d1sumdtz1mr8') ;
SQL_ID PLAN_HASH_VALUE SQL_PROFILE
------------- --------------- ------------------------------
aq1rf843yf3bm 4192419542
1 row selected. -- dba_hist_sqlstat 등록 확인
SCOTT@ORACLE19>
------------------------------------------------------------------------------------------------------------------------------------
-- AWR PLAN 가지고 SQL PROFILE 만들기
------------------------------------------------------------------------------------------------------------------------------------
-- apply plan : aq1rf843yf3bm 4192419542 SELECT /*+ TG_PROFILE ORDERED USE_NL(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno
-- target sql : 6d1sumdtz1mr8 615168685 SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno
declare
ar_profile_hints sys.sqlprof_attr;
cl_sql_text clob;
begin
--select extractvalue(value, '/hint') as outline_hints bulk collect
select extractvalue(value(d), '/hint') as outline_hints bulk collect
into ar_profile_hints
from xmltable('/*/outline_data/hint' passing (select xmltype(other_xml) as xmlval
from dba_hist_sql_plan
where sql_id = 'aq1rf843yf3bm' -- apply plan
and plan_hash_value = 4192419542 -- apply plan
and other_xml is not null ) ) d;
select sql_text
into cl_sql_text
-- from dba_hist_sqltext
from v$sql
where sql_id = '6d1sumdtz1mr8'; -- target sql
dbms_sqltune.import_sql_profile(
sql_text => cl_sql_text
, profile => ar_profile_hints
, category => 'DEFAULT'
, name => '6d1sumdtz1mr8_profile' -- target sql
-- use force_match => true
-- to use CURSOR_SHARING=SIMILAR
-- behaviour, i.e. match even with
-- differing literals
, REPLACE => TRUE
);
end;
/
-- 참고 : xmltable 의 value 함수 에러의 경우 value(d) 를 column_value로 변경하시기 바랍니다.
------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------
SQL> -- ENDABLE / DISABLED 확인
SQL> SELECT NAME, CATEGORY, SQL_TEXT, CREATED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING
FROM DBA_SQL_PROFILES;
NAME CATEGORY SQL_TEXT CREATED DESCRIPTION TYPE STATUS FOR
----------------------- ---------- --------------------------------------------------------------------------------------------- ------------------------------- ------------- ------- -------- ---
6d1sumdtz1mr8_profile DEFAULT SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno 2022/04/25 20:09:43.876488 MANUAL ENABLED NO
1 rows selected.
-- SQL PROFILE 작동 확인
SCOTT@ORACLE19>set autotrace on explain
SCOTT@ORACLE19>SELECT /*+ TG_PROFILE ORDERED USE_HASH(B) */ * FROM dept a, emp b WHERE b.deptno = a.deptno;
DEPTNO DNAME LOC EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------- ------------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
10 ACCOUNTING NEW YORK 7782 CLARK MANAGER 7839 1981.06.09 00:00:00 2450 10
10 ACCOUNTING NEW YORK 7839 KING PRESIDENT 1981.11.17 00:00:00 5000 10
10 ACCOUNTING NEW YORK 7934 MILLER CLERK 7782 1982.01.23 00:00:00 1300 10
20 RESEARCH DALLAS 7369 SMITH CLERK 7902 1980.12.17 00:00:00 800 20
20 RESEARCH DALLAS 7566 JONES MANAGER 7839 1981.04.02 00:00:00 2975 20
20 RESEARCH DALLAS 7788 SCOTT ANALYST 7566 1982.12.09 00:00:00 3000 20
20 RESEARCH DALLAS 7876 ADAMS CLERK 7788 1983.01.12 00:00:00 1100 20
20 RESEARCH DALLAS 7902 FORD ANALYST 7566 1981.12.03 00:00:00 3000 20
30 SALES CHICAGO 7499 ALLEN SALESMAN 7698 1981.02.20 00:00:00 1600 300 30
30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981.02.22 00:00:00 1250 500 30
30 SALES CHICAGO 7654 MARTIN SALESMAN 7698 1981.09.28 00:00:00 1250 1400 30
30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981.05.01 00:00:00 2850 30
30 SALES CHICAGO 7844 TURNER SALESMAN 7698 1981.09.08 00:00:00 1500 0 30
30 SALES CHICAGO 7900 JAMES CLERK 7698 1981.12.03 00:00:00 950 30
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4192419542
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 812 | 10 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 14 | 812 | 10 (0)| 00:00:01 | <<<< NL 로 풀림
| 2 | TABLE ACCESS FULL| DEPT | 4 | 80 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| EMP | 4 | 152 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("B"."DEPTNO"="A"."DEPTNO")
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (2), E - Syntax error (1))
---------------------------------------------------------------------------
1 - SEL$1
U - ORDERED / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
E - TG_PROFILE
3 - SEL$1 / B@SEL$1
U - USE_HASH(B) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- SQL profile "6d1sumdtz1mr8_profile" used for this statement <<< SQL PROFILE 정보 NOTE 됨
SCOTT@ORACLE19>
-- DISABLED SQL PROFILE
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(NAME => 'tune_tg_full', ATTRIBUTE_NAME => 'STATUS', VALUE => 'DISABLED');
-- ENABLED SQL PROFILE
EXEC DBMS_SQLTUNE.ALTER_SQL_PROFILE(NAME => 'tune_tg_full', ATTRIBUTE_NAME => 'STATUS', VALUE => 'ENABLED');
-- SQL PROFILE 정보 확인 ( ENDABLE / DISABLED )
SELECT NAME, CATEGORY, SQL_TEXT, CREATED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING
FROM DBA_SQL_PROFILES ;
-- SQL_PROFILE 삭제
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE(NAME=>'6d1sumdtz1mr8_profile');
SELECT NAME, CATEGORY, SQL_TEXT, CREATED, DESCRIPTION, TYPE, STATUS, FORCE_MATCHING
FROM DBA_SQL_PROFILES ;