Queries – KTEXPERTS

Top Oracle CPU & ELAPSED Queries

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will see the following Top CPU & ELAPSED Queries. Top CPU Queries :

select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows
FROM DBA_HIST_SQLTEXT DHST,
(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows
FROM DBA_HIST_SQLSTAT DHSS
WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT
WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI')
) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC
FETCH FIRST 50 ROWS ONLY)y
order by y.cpuperexec desc

select * from (SELECT dhst.sql_id,SQL_TEXT,X.CPU_TIME/1000000 CPU_Time_inSec,X.Num_Execs,round(((X.CPU_TIME/1000000)/X.Num_Execs),3) CPUPEREXEC, X.num_rows

FROM DBA_HIST_SQLTEXT DHST,

(SELECT DHSS.SQL_ID SQL_ID,SUM(DHSS.CPU_TIME_DELTA) CPU_TIME ,sum(executions_delta) as Num_Execs,sum(ROWS_PROCESSED_delta) as num_rows

FROM DBA_HIST_SQLSTAT DHSS

WHERE DHSS.SNAP_ID IN(SELECT SNAP_ID FROM DBA_HIST_SNAPSHOT

WHERE begin_interval_time > =TO_DATE('25/APR/2019 20:50','DD-MON-YYYY HH24:MI')

AND END_INTERVAL_TIME<=TO_DATE('26/APR/2019 06:30','DD-MON-YYYY HH24:MI')

) GROUP BY DHSS.SQL_ID) X WHERE X.SQL_ID=DHST.SQL_ID ORDER BY X.CPU_TIME DESC

FETCH FIRST 50 ROWS ONLY)y

order by y.cpuperexec desc

Top ELAPSED Queries :

select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id
where r < 10
order by avg_query_time desc

select sub.sql_id,

txt.sql_text,

parsing_schema_name,

sub.seconds_since_date as TOTALELAPSEDTIME,

sub.execs_since_date as NUmberofExecs,

sub.gets_rows_date as RowsProcessed,

round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time

from ( -- sub to sort before top N filter

select sql_id,

g.parsing_schema_name,

round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,

sum(executions_delta) as execs_since_date,

sum(buffer_gets_delta) as gets_since_date,

sum(ROWS_PROCESSED_delta) as gets_rows_date,

row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r

from dba_hist_snapshot natural

join dba_hist_sqlstat g

where begin_interval_time > =TO_DATE('02/APR/2019 07:50','DD-MON-YYYY HH24:MI')

AND END_INTERVAL_TIME<=TO_DATE('02/APR/2019 08:20','DD-MON-YYYY HH24:MI')

and parsing_schema_name = 'SCOTT'

group by sql_id, g.parsing_schema_name) sub

join dba_hist_sqltext txt on sub.sql_id = txt.sql_id

where r < 10

order by avg_query_time desc

TOP Elapsed Queries with Text :

select sub.sql_id,
txt.sql_text,
parsing_schema_name,
sub.seconds_since_date as TOTALELAPSEDTIME,
sub.execs_since_date as NUmberofExecs,
sub.gets_rows_date as RowsProcessed,
round(sub.seconds_since_date / (sub.execs_since_date + 0.01), 3) avg_query_time
from ( -- sub to sort before top N filter
select sql_id,
g.parsing_schema_name,
round(sum(ELAPSED_TIME_delta) / 1000000) as seconds_since_date,
sum(executions_delta) as execs_since_date,
sum(buffer_gets_delta) as gets_since_date,
sum(ROWS_PROCESSED_delta) as gets_rows_date,
row_number() over (order by round(sum(elapsed_time_delta) / 1000000) desc) r
from dba_hist_snapshot natural
join dba_hist_sqlstat g
where begin_interval_time > =TO_DATE('22/OCT/2019 10:40','DD-MON-YYYY HH24:MI')
AND END_INTERVAL_TIME<=TO_DATE('22/OCT/2019 14:00','DD-MON-YYYY HH24:MI')
and parsing_schema_name = 'SCOTT'
group by sql_id, g.parsing_schema_name) sub
join dba_hist_sqltext txt on sub.sql_id = txt.sql_id and SQL_TEXT like 'update%'
--where r < 50
order by avg_query_time desc