SPO coe_gen_sql_profile.log;
SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;
SET SERVEROUT ON SIZE UNL;
REM
REM $Header: 215187.1 coe_gen_sql_profile.sql 11.4.4.4 2012/04/02 carlos.sierra $
REM
REM Copyright (c) 2000-2011, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   coe_gen_sql_profile.sql
REM
REM DESCRIPTION
REM   Inputs one SQL_ID and outputs a set of scripts generated by
REM   coe_xfr_sql_profile.sql. One for each known plan of given
REM   SQL_ID.
REM
REM PRE-REQUISITES
REM   1. Oracle Tuning Pack license.
REM
REM PARAMETERS
REM   1. SQL_ID (required)
REM
REM EXECUTION
REM   1. Connect into SQL*Plus as user with access to data dictionary.
REM   2. Execute script coe_gen_sql_profile.sql passing SQL_ID
REM      (parameter can be passed inline or until requested).
REM
REM EXAMPLE
REM   # sqlplus system
REM   SQL> START coe_gen_sql_profile.sql [SQL_ID];
REM   SQL> START coe_gen_sql_profile.sql gnjy0mn4y9pbm;
REM   SQL> START coe_gen_sql_profile.sql;
REM
REM NOTES
REM   1. For possible errors see coe_gen_sql_profile.log
REM   2. Be aware that using DBMS_SQLTUNE requires a license for
REM      Oracle Tuning Pack.
REM
SET TERM ON ECHO OFF;
PRO
PRO Parameter 1:
PRO SQL_ID (required)
PRO
DEF sql_id0 = '&1';
PRO
SET TERM OFF;
PRO
PRO Plans from GV$SQL
PRO ~~~~~~~~~~~~~~~~~
SELECT plan_hash_value,
       ROUND(SUM(elapsed_time)/SUM(executions)/1e6, 3) avg_et_secs,
       ROUND(SUM(cpu_time)/SUM(executions)/1e6, 3) avg_cpu_secs,
       ROUND(SUM(user_io_wait_time)/SUM(executions)/1e6, 3) avg_io_secs,
       ROUND(SUM(buffer_gets)/SUM(executions)) avg_buffer_gets,
       ROUND(SUM(disk_reads)/SUM(executions)) avg_disk_reads,
       ROUND(SUM(rows_processed)/SUM(executions)) avg_rows,
       SUM(executions) executions
  FROM gv$sql
 WHERE sql_id = TRIM('&&sql_id0.')
   AND executions > 0
 GROUP BY
       plan_hash_value
 ORDER BY
       2, 3;
PRO
PRO
PRO Plans from DBA_HIST_SQLSTAT
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~
SELECT plan_hash_value,
       ROUND(SUM(elapsed_time_total)/SUM(executions_total)/1e6, 3) avg_et_secs,
       ROUND(SUM(cpu_time_total)/SUM(executions_total)/1e6, 3) avg_cpu_secs,
       ROUND(SUM(iowait_total)/SUM(executions_total)/1e6, 3) avg_io_secs,
       ROUND(SUM(buffer_gets_total)/SUM(executions_total)) avg_buffer_gets,
       ROUND(SUM(disk_reads_total)/SUM(executions_total)) avg_disk_reads,
       ROUND(SUM(rows_processed_total)/SUM(executions_total)) avg_rows,
       SUM(executions_total) executions
  FROM dba_hist_sqlstat
 WHERE sql_id = TRIM('&&sql_id0.')
   AND executions_total > 0
 GROUP BY
       plan_hash_value
 ORDER BY
       2, 3;
PRO
PRO
PRO Plans from GV$SQL and/or DBA_HIST_SQLSTAT
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
BEGIN
  FOR i IN (WITH
            p AS (
            SELECT plan_hash_value
              FROM gv$sql_plan
             WHERE sql_id = TRIM('&&sql_id0.')
               AND other_xml IS NOT NULL
             UNION
            SELECT plan_hash_value
              FROM dba_hist_sql_plan
             WHERE sql_id = TRIM('&&sql_id0.')
               AND other_xml IS NOT NULL ),
            m AS (
            SELECT plan_hash_value,
                   SUM(elapsed_time)/SUM(executions) avg_et_secs
              FROM gv$sql
             WHERE sql_id = TRIM('&&sql_id0.')
               AND executions > 0
             GROUP BY
                   plan_hash_value ),
            a AS (
            SELECT plan_hash_value,
                   SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
              FROM dba_hist_sqlstat
             WHERE sql_id = TRIM('&&sql_id0.')
               AND executions_total > 0
             GROUP BY
                   plan_hash_value )
            SELECT p.plan_hash_value,
                   ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
              FROM p, m, a
             WHERE p.plan_hash_value = m.plan_hash_value(+)
               AND p.plan_hash_value = a.plan_hash_value(+)
             ORDER BY
                   avg_et_secs NULLS LAST)
  LOOP
    DBMS_OUTPUT.PUT_LINE('-- phv:'||i.plan_hash_value||' avg_et_secs:'||i.avg_et_secs);
    DBMS_OUTPUT.PUT_LINE('@@coe_xfr_sql_profile.sql &&sql_id0. '||i.plan_hash_value||';');
  END LOOP;
END;
/
SPO coe_gen_sql_profile_&&sql_id0..sql;
/
SPO OFF;
@coe_gen_sql_profile_&&sql_id0..sql;
HOS zip -mT coe_gen_sql_profile_&&sql_id0 coe_gen_sql_profile.log coe_xfr_sql_profile.log coe_gen_sql_profile_&&sql_id0..sql coe_xfr_sql_profile_&&sql_id0._*.sql
HOS zip -d coe_gen_sql_profile_&&sql_id0 coe_xfr_sql_profile.log
SET TERM ON;
HOS unzip -l coe_gen_sql_profile_&&sql_id0
UNDEFINE 1 sql_id0
CL COL
PRO
PRO coe_gen_sql_profile completed.
