Coe Scripts usage

1) coe_xfr_sql_profile.sql  - Captures the good plan from the source and can be executed in the target to create sql profile.( Target can be either on the same
db or different db)


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 2ne 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>