SQL Scripts Samples
   Scripts for Two Analytical Models for SQL Stats
 
 

Listing 1: AWR SQL Stats by SQL ID per Snapshot

WITH x AS (
	SELECT DBID, INSTANCE_NUMBER,
	LAG(SNAP_ID, 1) OVER (PARTITION BY DBID,INSTANCE_NUMBER ORDER BY SNAP_ID) begin_snap_id,
	SNAP_ID end_snap_id, END_INTERVAL_TIME end_snap_time
	FROM DBA_HIST_SNAPSHOT WHERE DBID=&dbid AND INSTANCE_NUMBER=&instance_number AND END_INTERVAL_TIME > SYSDATE - &days
)
SELECT x.begin_snap_id||'-'||x.end_snap_id snap_id, x.end_snap_time, y.INSTANCE_NUMBER inst#,
y.SQL_ID, y.PLAN_HASH_VALUE, ROUND(y.ELAPSED_TIME_DELTA/1000000) elapsed_seconds, ROUND(y.CPU_TIME_DELTA/1000000) cpu_seconds,
y.ROWS_PROCESSED_DELTA rows_processed, y.BUFFER_GETS_DELTA buffer_gets, y.DISK_READS_DELTA  disk_reads,
y.EXECUTIONS_DELTA  executions, y.PARSE_CALLS_DELTA parses
FROM DBA_HIST_SQLSTAT y JOIN x
ON (y.DBID=x.dbid AND y.INSTANCE_NUMBER=x.instance_number AND y.SNAP_ID=x.end_snap_id)
WHERE x.begin_snap_id IS NOT NULL AND y.SQL_ID='&sql_id'
ORDER BY x.begin_snap_id, x.end_snap_id, y.PLAN_HASH_VALUE
;

Listing 2: AWR SQL Elapsed Time by SQL ID per Snapshot

 WITH x AS (
    SELECT DBID, INSTANCE_NUMBER,
    LAG(SNAP_ID, 1) OVER (PARTITION BY DBID,INSTANCE_NUMBER ORDER BY SNAP_ID) begin_snap_id,
    SNAP_ID end_snap_id, END_INTERVAL_TIME end_snap_time
    FROM DBA_HIST_SNAPSHOT WHERE END_INTERVAL_TIME > SYSDATE - ?
 )
 SELECT y.INSTANCE_NUMBER inst#, x.begin_snap_id||'-'||x.end_snap_id snap_id, x.end_snap_time,
 y.SQL_ID, y.PLAN_HASH_VALUE,
 ROUND(y.ELAPSED_TIME_DELTA/1000000) elapsed_seconds,
 ROUND(y.CPU_TIME_DELTA/1000000) cpu_seconds,
 ROUND(y.CLWAIT_DELTA/1000000) cluster_wait_seconds,
 ROUND(y.IOWAIT_DELTA/1000000) io_wait_seconds,
 ROUND(y.CCWAIT_DELTA/1000000) concurrency_wait_seconds,
 ROUND(y.APWAIT_DELTA/1000000) app_wait_seconds,
 ROUND(y.PLSEXEC_TIME_DELTA/1000000) plsql_exec_seconds,
 ROUND(y.JAVEXEC_TIME_DELTA/1000000) java_exec_seconds
 FROM DBA_HIST_SQLSTAT y JOIN x ON (y.DBID=x.dbid AND y.INSTANCE_NUMBER=x.instance_number AND y.SNAP_ID=x.end_snap_id)
 WHERE x.begin_snap_id IS NOT NULL AND y.DBID=? AND y.INSTANCE_NUMBER=? AND y.SQL_ID=?
 ORDER BY x.begin_snap_id, x.end_snap_id, y.PLAN_HASH_VALUE
 ;

Listing 3: SQL Plan Change by SQL ID

WITH x AS (
	SELECT DBID, INSTANCE_NUMBER, SNAP_ID, SQL_ID, PLAN_HASH_VALUE,
	LAG(PLAN_HASH_VALUE, 1) OVER (PARTITION BY DBID, INSTANCE_NUMBER ORDER BY SNAP_ID) previous_plan_hash_value
	FROM DBA_HIST_SQLSTAT
	WHERE DBID=&dbid AND SQL_ID='&sql_id'
	AND SNAP_ID BETWEEN &begin_snap_id AND &end_snap_id
)
SELECT x.instance_number inst#, x.snap_id, y.END_INTERVAL_TIME end_snap_time, x.sql_id, x.plan_hash_value
FROM x JOIN DBA_HIST_SNAPSHOT y
ON (x.DBID=y.DBID AND x.INSTANCE_NUMBER=y.INSTANCE_NUMBER AND x.SNAP_ID=y.SNAP_ID)
WHERE (x.previous_plan_hash_value IS NULL OR x.previous_plan_hash_value!=x.plan_hash_value)
ORDER BY x.snap_id, x.instance_number
;

Listing 4: ASH Top SQL

WITH x AS (
	SELECT INSTANCE_NUMBER, SQL_ID, NVL(EVENT,'CPU + Wait for CPU') event, WAIT_CLASS,
	COUNT(1) dimension2_count, ROUND((RATIO_TO_REPORT(COUNT(1)) OVER ())*100,2) dimension2_pct,
	COUNT(DISTINCT SQL_PLAN_HASH_VALUE) distinct_plan,
	COUNT(DISTINCT 'ID='||SQL_PLAN_LINE_ID||',OPERATION='||SQL_PLAN_OPERATION||',OPTION='||SQL_PLAN_OPTIONS) distinct_row_source,
	COUNT(DISTINCT SESSION_ID) distinct_sid,
	MIN(MIN(SNAP_ID)) OVER (PARTITION BY INSTANCE_NUMBER) begin_snap_id,
	MAX(MAX(SNAP_ID)) OVER (PARTITION BY INSTANCE_NUMBER) end_snap_id
	FROM DBA_HIST_ACTIVE_SESS_HISTORY
	WHERE DBID=&dbid AND INSTANCE_NUMBER=&instance_number AND SNAP_ID BETWEEN &begin_snap_id AND &end_snap_id
	GROUP BY INSTANCE_NUMBER, SQL_ID, EVENT, WAIT_CLASS
)
SELECT instance_number inst#, begin_snap_id||'-'||end_snap_id snap_id,
sql_id, event, wait_class, dimension2_pct, dimension2_count, distinct_plan, distinct_row_source, distinct_sid
FROM x
WHERE sql_id IS NOT NULL AND dimension2_pct>=&pct_threshold
AND SQL_ID = '&sql_id'
ORDER BY dimension2_count DESC
;

Listing 5: AWR SQL Text and SQL Plan by SQL ID

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR(?))
;

Copyright 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us