Find the high elapsed time queries 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, elapsed_time, cpu_time, disk_reads,
rank ()
over (order by elapsed_time desc)
as sql_rank
from v$sql)
where sql_rank < 10;

Related posts

How to deal with ORA-00020: maximum number of processes (%s) exceeded

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database 

Công cụ tự động khai báo datafile – Oracle