Script: View historical SQL Execution Information

Script: View historical SQL Execution Information SELECT * FROM (SELECT '1. v $ SQL '| 'instance No.:' | GV $ SQL. inst_id source, SQL _ID, plan_hash_value, TO_CHAR (FIRST_LOAD_TIME) begin_time, 'end_time, executions "No. of exec ", (buffer_gets/executions)" LIO/exec ", (cpu_time/executions/1000000)" CPUTIM/exec ", (elapsed_time/executions/1000000) "ETIME/exec", (disk_reads/executions) "PIO/exec", (ROWS_PROCESSED/executions) "ROWs/exec" FROM Gv $ SQL where SQL _id = '9mq90p42frzgz 'UNION ALL SELECT' 2. sqltuning set 'source, SQL _id, plan_hash_value, 'just SQLSET NO date' begin_time, 'just SQLSET NO date' end_time, EXECUTIONS "No. of exec ", (buffer_gets/executions)" LIO/exec ", (cpu_time/executions/1000000)" CPUTIM/exec ", (elapsed_time/executions/1000000) "ETIME/exec", (disk_reads/executions) "PIO/exec", (ROWS_PROCESSED/executions) "ROWs/exec" FROM dba_sqlset_statements WHERE SQL _ID = '9mq90p42frzgz 'UNION ALL select' 3. dba_advisor_sqlstats 'source, SQL _id, plan_hash_value, 'just SQLSET NO date' begin_time, 'just SQLSET NO date' end_time, EXECUTIONS "No. of exec ", (buffer_gets/executions)" LIO/exec ", (cpu_time/executions/1000000)" CPUTIM/exec ", (elapsed_time/executions/1000000) "ETIME/exec", (disk_reads/executions) "PIO/exec", (ROWS_PROCESSED/executions) "ROWs/exec" FROM dba_sqlset_statements WHERE SQL _ID = '9mq90p42frzgz 'UNION ALL SELECT DISTINCT' 4. dba_hist_sqlstat '| 'instance number:' | SQL. INSTANCE_NUMBER source, SQL _id, PLAN_HASH_VALUE, TO_CHAR (s. BEGIN_INTERVAL_TIME, 'yyyy-MM-DD hh24: mi: ss') begin_time, TO_CHAR (s. END_INTERVAL_TIME, 'yyyy-MM-DD hh24: mi: ss') end_time, SQL .exe cutions_delta, SQL. buffer_gets_delta/DECODE (NVL (SQL .exe cutions_delta, 0), 0, 1, SQL .exe cutions_delta) "LIO/exec", (SQL. cpu_time_delta/1000000)/DECODE (NVL (SQL .exe cutions_delta, 0), 0, 1, SQL .exe cutions_delta) "CPUTIM/exec", (SQL. elapsed_time_delta/1000000)/DECODE (NVL (SQL .exe cutions_delta, 0), 0, 1, SQL .exe cutions_delta) "ETIME/exec", SQL. DISK_READS_DELTA/DECODE (NVL (SQL .exe cutions_delta, 0), 0, 1, SQL .exe cutions_delta) "PIO/exec", SQL. ROWS_PROCESSED_DELTA/DECODE (NVL (SQL .exe cutions_delta, 0), 0, 1, SQL .exe cutions_delta) "ROWs/exec" FROM dba_hist_sqlstat SQL, dba_hist_snapshot s where SQL. INSTANCE_NUMBER = s. INSTANCE_NUMBER and SQL. dbid = (SELECT dbid FROM v $ database) AND s. snap_id = SQL. snap_id AND SQL _id IN ('9mq90p42frzgz ') ORDER BY source, begin_time DESC;