86 lines
3.9 KiB
SQL
86 lines
3.9 KiB
SQL
/* ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|
|
Author : Mohamed Houri
|
|
Date : 03/08/2016
|
|
Scope : This is an updated version of an existing script (over the web)
|
|
in which I have taken into account the following points:
|
|
|
|
-- Superfluous executions and not used plan_hash_value are excluded.
|
|
Superfluous plan_hash_value are inserted into dba_hist_sqlstat
|
|
because they were present in gv$sql at the AWR capture time.
|
|
This generally happens when a SQL_ID has several child cursors
|
|
in gv$sql. All these child cursors will be captured regardless
|
|
of their activity (used or not used). These superfluous executions
|
|
are excluded using the following where clause:
|
|
|
|
WHERE avg_lio != 0
|
|
|
|
But a "lock table" for example doesn't consume any logical I/O. This
|
|
is why I added the following extra where clause:
|
|
|
|
OR (avg_lio =0 AND avg_etime > 0)
|
|
|
|
-- When a query is run in PARALLEL the avg_etime represents the time
|
|
spent by all parallel servers concurrently. So if avg_px is not null then
|
|
avg_etime represents the cumulated time of all PX servers. To have
|
|
the approximate wall clock time of the query I have divided the
|
|
avg_time by the avg_px to obtain avg_px_time.
|
|
|
|
Warning : avg_px_time do not include the Query Coordinator time
|
|
I still have not figured out how to get the QC time from a historical
|
|
table. I have a script QCelaps.sql which gives the QC time but it
|
|
is based on gv$sql_monitor. This is obviously not reliable when
|
|
dealing with historical executions.
|
|
|
|
Investigation : may be I have to investigate the new 12c RTSM
|
|
historical tables : dba_hist_reports
|
|
dba_hist_reports_details
|
|
Update : 28-09-2016 : add end_of_fetch column
|
|
if end_of_fetch = 0 and exec = 1 then
|
|
this means that the query not finished during the snapshot
|
|
end if
|
|
When you see avg_rows = 0 this doesnt' necessarily means that
|
|
the query has not finished during the snapshot
|
|
|
|
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ */
|
|
col snap_begin format a25
|
|
col sql_profile format a20
|
|
col execs format 9999999
|
|
SELECT
|
|
snap_begin
|
|
,snap_id
|
|
,plan_hash_value
|
|
,sql_profile
|
|
,execs
|
|
,end_of_fetch
|
|
,avg_etime
|
|
,avg_px
|
|
,trunc(avg_etime/decode(avg_px,0,1,avg_px) ,2) avg_px_time
|
|
,avg_pio
|
|
,avg_lio
|
|
,avg_rows
|
|
FROM
|
|
(SELECT
|
|
sn.begin_interval_time snap_begin
|
|
,sn.snap_id
|
|
,plan_hash_value
|
|
,st.sql_profile
|
|
,executions_delta execs
|
|
,end_of_fetch_count_delta end_of_fetch
|
|
,trunc(elapsed_time_delta/1e6/decode(executions_delta, 0, 1, executions_delta)) avg_etime
|
|
,round(disk_reads_delta/decode(executions_delta,0,1, executions_delta),1) avg_pio
|
|
,round(buffer_gets_delta/decode(executions_delta,0,1, executions_delta), 1) avg_lio
|
|
,round(px_servers_execs_delta/decode(executions_delta,0,1, executions_delta), 1) avg_px
|
|
,round(rows_processed_delta/decode(executions_delta,0, 1, executions_delta), 1) avg_rows
|
|
FROM
|
|
dba_hist_sqlstat st,
|
|
dba_hist_snapshot sn
|
|
WHERE st.snap_id = sn.snap_id
|
|
AND st.instance_number = sn.instance_number
|
|
AND sql_id = '&sql_id'
|
|
AND begin_interval_time > to_date('&from_date','ddmmyyyy')
|
|
)
|
|
WHERE avg_lio != 0
|
|
OR (avg_lio =0 AND avg_etime > 0)
|
|
ORDER by 1 asc;
|
|
|
|
|