Coe Scripts usage
2) coe_load_sql_profile.sql - Plan of one sqlid can be plug in to another sqlid
Here we are going to change the plan of a21rhzpqzmrh9 sqlid with the plan of g59vz2u4cu404.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID a21rhzpqzmrh9, child number 0
-------------------------------------
select count(*) from emp where EMPNO between 1 and 3699999
Plan hash value: 3878085942
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4100 (100)|
| 1 | SORT AGGREGATE | | 1 | 5 | |
|* 2 | INDEX FAST FULL SCAN| IDX_EMP | 3699K| 17M| 4100 (1)| 00:00:50
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID g59vz2u4cu404, child number 0
-------------------------------------
select count(*) from emp
Plan hash value: 2083865914
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 12723 (100)| |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| EMP | 3699K| 12723 (1)| 00:02:33 |
-------------------------------------------------------------------
Values passed to coe_load_sql_profile:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORIGINAL_SQL_ID: "a21rhzpqzmrh9"
MODIFIED_SQL_ID: "g59vz2u4cu404"
PLAN_HASH_VALUE: "2083865914"
After the execution completes now lets us see the plan of the a21rhzpqzmrh9
new 1: select * from table (dbms_xplan.display_cursor('a21rhzpqzmrh9',0))
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID a21rhzpqzmrh9, child number 0
-------------------------------------
select count(*) from emp where EMPNO between 1 and 3699999
Plan hash value: 2083865914
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12741 (100)| |
| 1 | SORT AGGREGATE | | 1 | 5 | | |
|* 2 | TABLE ACCESS FULL| EMP | 3699K| 17M| 12741 (1)| 00:02:33 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(("EMPNO">=1 AND "EMPNO"<=3699999))
Note
-----
- SQL profile A21RHZPQZMRH9_2083865914 used for this statement
INST_ID SQL_ID CHILD_NUMBER PLAN_HASH_VALUE (ELAPSED_TIME/1000000)/EXECUTIONS EXECUTIONS SQL_PROFILE
---------- ------------- ------------ --------------- --------------------------------- ---------- ----------------------------------------------------------------
1 a21rhzpqzmrh9 0 2083865914 1.10267 1 A21RHZPQZMRH9_2083865914
==Plan modified.
3) coe_load_sql_baseline.sql ( Same as 2nd but to create baseline instead of sql profile)
4) coe_gen_sql_patch.sql - For generating trace for monitoring.
SQL Patch "coe_a21rhzpqzmrh9" will be used on next parse.
Look for some new 10053 traces on udump:
1. files with name *_a21rhzpqzmrh9_10053_c*
2. grep -c "sql_id=a21rhzpqzmrh9" *.trc | grep -v :0$
Monitor SQL performance with SQLT XTRACT.
To drop SQL Patch and stop 10053 on this SQL:
EXEC DBMS_SQLDIAG.DROP_SQL_PATCH(name => 'coe_a21rhzpqzmrh9')
ALTER SYSTEM SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:a21rhzpqzmrh9] off'
select
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "EMP"@"SEL$1")
END_OUTLINE_DATA
*/
count(*) from emp where EMPNO between 1 and 3699999;
Hints of sql can be taken from below.
SQL> select other_xml from V$SQL_PLAN_STATISTICS_ALL where SQL_ID = 'a21rhzpqzmrh9';
OTHER_XML
--------------------------------------------------------------------------------
<other_xml><info type="db_version">11.2.0.3</info><info type="parse_schema"><![C
DATA["MANZOOR"]]></info><info type="plan_hash">2083865914</info><info type="plan
_hash_2">3281146378</info><info type="sql_profile"><![CDATA["coe_a21rhzpqzmrh9_3
878085942"]]></info><outline_data><hint><![CDATA[IGNORE_OPTIM_EMBEDDED_HINTS]]><
/hint><hint><![CDATA[OPTIMIZER_FEATURES_ENABLE('11.2.0.3')]]></hint><hint><![CDA
TA[DB_VERSION('11.2.0.3')]]></hint><hint><![CDATA[ALL_ROWS]]></hint><hint><![CDA
TA[OUTLINE_LEAF(@"SEL$1")]]></hint><hint><![CDATA[FULL(@"SEL$1" "EMP"@"SEL$1")]]
></hint></outline_data></other_xml>
