Find top 10 SQL statement in the memory with the greatest aggregate elapsed time

posted May 5, 2012, 1:10 PM by Sachchida Ojha


SQL> 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 elapsed_rank from v$sql)
where elapsed_rank <=10;

SQL_ID          CHILD_NUMBER
------------- ------------
SQL_TEXT
--------------------------------------------------------------------------------
ELAPSED_TIME
------------
cvn54b7yz0s8u         0
select /*+ index(idl_ub1$ i_idl_ub11) +*/ piece#,length,piece from idl_ub1$ wher
e obj#=:1 and part=:2 and version=:3 order by piece#
   171139501





Now you can take the sql_id and child_number from the above query and put them in the below SQL to get the detailed plan

SQL> Select * from TABLE(DBMS_XPLAN.display_cursor('cvn54b7yz0s8u','0', 'TYPICAL -BYTES'));





Comments