Find the high elapsed time queries in Oracle

Top Query with high elapsed time in Oracle

Find the query taking long time to execute in Oracle

Select
module,parsing_schema_name,inst_id,sql_id,CHILD_NUMBER,sql_plan_baseline,sql_profile,plan_hash_value,sql_fulltext,
to_char(last_active_time,'DD/MM/YY HH24:MI:SS' ),executions, elapsed_time/executions/1000/1000,
rows_processed,sql_plan_baseline from gv$sql where executions <> 0 order by elapsed_time/executions desc

Find the highest elapsed time queries

select sql_id, child_number, sql_text, elapsed_time
from (select sql_id_child_number, sql_text, elaped_time,cpu_time,disk_reads,
rank ()
over (order by elapsed_time desc)
as sql_rank
from v$sql)
where sql_rank < 10;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.