How to get SQL Standard Deviation information of SQL ID in Oracle Database.
Please user the below SQL to get the information of SQL Standard Deviation information. Don't forget to remove comment lines before running and formatting the sql.
WITH sqlstat
AS
(
SELECT TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) snap_time -- Change to MI, HH , or DD for a individual sql statement to checking performance over time
,s.instance_number inst_num
--,MIN(h.begin_interval_time) min_start_date
--,MAX(h.begin_interval_time) max_end_date
--,MAX(s.snap_id) max_snap_id
,s.sql_id
,s.plan_hash_value
,parsing_schema_name schema
,module
,ROUND((SUM(s.elapsed_time_delta)/1000000)/60,2) elapsed_time_minutes
,ROUND(SUM(s.elapsed_time_delta)/1000000,2) elapsed_time_seconds
,SUM(s.executions_delta) executions_total
,SUM(s.rows_processed_delta) rows_processed_total
,ROUND(SUM(s.ELAPSED_TIME_DELTA)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/10000002) avg_elap_per_stmt_seconds
,ROUND(SUM(s.ELAPSED_TIME_DELTA)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_elap_stmt_ms
--/****
,ROUND(SUM(apwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_apwait_ms
,ROUND(SUM(clwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_cl_wait_ms
,ROUND(SUM(iowait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_io_wait_ms
,ROUND(SUM(ccwait_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_cc_wait_ms
,ROUND(SUM(cpu_time_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END)/1000,2) avg_cpu_per_ms
--,ROUND(SUM(s.cpu_time_delta)/1000000,2) cpu_time_total_seconds
--,SUM(s.px_servers_execs_delta) px_servers_execs_total
,ROUND(SUM(s.cpu_time_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2) avg_cpu_time
,ROUND(SUM(s.buffer_gets_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2) avg_buffer_gets
,ROUND(SUM(s.direct_writes_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2) avg_direct_writes
,ROUND(SUM(s.disk_reads_delta)/(CASE WHEN sum(executions_delta)=0 THEN 1 ELSE sum(executions_delta) END),2) avg_disk_reads
FROM dba_hist_sqlstat s
JOIN dba_hist_snapshot h
ON s.snap_id = h.snap_id
AND s.instance_number = h.instance_number
WHERE s.elapsed_time_delta > 0
AND (h.begin_interval_time > SYSDATE -:NUMBER_OF_DAYS OR :NUMBER_OF_DAYS IS NULL)
AND (s.module LIKE '%'||:MODULE||'%' OR :MODULE IS NULL)
AND (h.begin_interval_time >= SYSDATE -:NUMBER_OF_HOURS/24 OR :NUMBER_OF_HOURS IS NULL)
AND (h.begin_interval_time >= SYSDATE -:NUMBER_OF_HOURS/24 OR :NUMBER_OF_HOURS IS NULL)
AND (h.begin_interval_time BETWEEN TO_DATE(:START_DATE,:DATE_FORMAT) AND TO_DATE(:END_DATE,:DATE_FORMAT) OR :START_DATE IS NULL OR :END_DATE IS NULL)
AND parsing_schema_name LIKE '%'||:SCHEMA_NAME_LIKE||'%'
GROUP BY TRUNC(h.begin_interval_time,:TRUNC_DATE_FORMAT) -- Change to HH or DD for a individual sql statement to checking performance over time
,s.instance_number
--,s.snap_id
,s.sql_id
,s.plan_hash_value
,s.parsing_schema_name
,s.module
ORDER BY 1 DESC, 2
), sqlstat_stddev AS
(
SELECT
snap_time
,inst_num
--,min_start_date
--,max_end_date
--,max_snap_id
,sql_id
,plan_hash_value
,SCHEMA
,module
,elapsed_time_minutes
,elapsed_time_seconds
,executions_total
,rows_processed_total
,avg_elap_per_stmt_seconds
,avg_elap_stmt_ms
,ROUND(STDDEV(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) stddev_elap_ms
,ROUND(AVG(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) avg_elap_ms
,ROUND(MIN(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) min_elap_ms
,ROUND(MAX(avg_elap_stmt_ms) OVER(PARTITION BY sql_id),2) max_elap_ms
--,avg_elap_per_stmt_ms
,avg_apwait_ms
,avg_cl_wait_ms
,avg_io_wait_ms
,avg_cc_wait_ms
,avg_cpu_per_ms
--,cpu_time_total_seconds
--,px_servers_execs_total
,avg_cpu_time
,avg_buffer_gets
,avg_direct_writes
,avg_disk_reads
,RANK() OVER (PARTITION BY sql_id ORDER BY avg_elap_stmt_ms, snap_time DESC ) avg_elap_ms_fastest
,RANK() OVER (PARTITION BY sql_id ORDER BY avg_elap_stmt_ms DESC,snap_time DESC) avg_elap_ms_slowest
FROM sqlstat s
WHERE s.executions_total > :MIN_EXECUTIONS
), sql_stat_stdfinal AS
(
SELECT s.*
,(CASE WHEN (avg_elap_ms_slowest = 1 OR avg_elap_ms_fastest=1) AND MIN(plan_hash_value) OVER (PARTITION BY sql_id ) <> MAX(plan_hash_value) OVER (PARTITION BY sql_id ) THEN 'Y'
ELSE 'N' END) plan_change
FROM sqlstat_stddev s
-- WHERE ((s.avg_elap_stmt_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_slowest =1)
-- OR (s.max_elap_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_fastest =1))
WHERE ((s.avg_elap_stmt_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_slowest =1)
OR (s.max_elap_ms NOT BETWEEN s.avg_elap_ms - s.stddev_elap_ms AND s.avg_elap_ms + s.stddev_elap_ms AND avg_elap_ms_fastest =1)
OR s.stddev_elap_ms > s.min_elap_ms
OR s.stddev_elap_ms > :MAX_STDDEV_MS OR :MAX_STDDEV_MS IS NULL)
AND s.stddev_elap_ms > :MIN_STDDEV_MS -- Min STDDEV required
AND (s.stddev_elap_ms > (s.max_elap_ms*(:PCT_VARIANCE/100)) OR :PCT_VARIANCE IS NULL) --STDDEV Greater than 10% of the max .
AND (s.avg_elap_ms_fastest = 1 OR s.avg_elap_ms_slowest =1)
)
SELECT s.*
,t.sql_text
FROM sql_stat_stdfinal s
JOIN dba_hist_sqltext t
ON s.sql_id = t.sql_id
WHERE plan_change = :PLAN_CHANGE_Y_OR_N_OR_NULL OR :PLAN_CHANGE_Y_OR_N_OR_NULL IS NULL
ORDER BY stddev_elap_ms DESC, avg_elap_ms_fastest
--**/
;
Comments
Post a Comment