Using V$SQL

V$SQL lists statistics on shared SQL area. Statistics displayed in V$SQL are normally updated at the end of query execution. 


This Query can help tell you which SQL your Oracle DB has spent the most time parsing, executing and fetching.


SELECT * FROM
(SELECT to_char( to_date( round(ELAPSED_TIME/1000000, 0), 'sssss'), 'hh24:mi:ss') as TIME,
      SQL_ID as "SQL ID", 
      Executions as "Executions",
      DISK_READS as "Disk Reads",
      DIRECT_WRITES as "Disk Writes",
      to_char(LAST_ACTIVE_TIME, 'YYYY-MON-DD-HH24:MI:SS') as "Last Active Time",
      BUFFER_GETS as "Buffer Gets",
      CPU_TIME as "CPU Time",
      floor(round(sum((Executions * ELAPSED_TIME)/1000000), 0)/86400) || 'd ' || 
           to_char(to_date(mod(round(sum((Executions * ELAPSED_TIME)/1000000), 0),86400), 'sssssss'), 'hh24"h" mi"m" ss"s"') as "Total Time: EXECS * TIME"
 from v$sql    
 WHERE ELAPSED_TIME >= 10000000 --this is 10 seconds
 GROUP BY ELAPSED_TIME, SQL_ID, Executions, DISK_READS, DIRECT_WRITES, LAST_ACTIVE_TIME, BUFFER_GETS, CPU_TIME
 ORDER BY elapsed_time DESC)
WHERE ROWNUM < 60
/

You can modify the time by altering the the elapsed_time where clause.

Output looks like this:

TIME     SQL ID        Executions Disk Reads Disk Writes Last Active Time     Buffer Gets   CPU Time Total Time: EXECS * TIME

-------- ------------- ---------- ---------- ----------- -------------------- ----------- ---------- -----------------------------------------------------

00:22:33 5b264bnr9f694          2      42037           0 2012-FEB-23-18:20:39    78613409 1340365913 0d 00h 45m 07s

00:19:28 7xmzgv6w2svbx      48942         26           0 2012-FEB-23-18:32:06    73855656 1051322071 661d 19h 23m 09s

00:11:11 fpd8h590w7wdq          1      17669           0 2012-FEB-23-18:32:06    39302547  665371395 0d 00h 11m 11s

...

...

...

...

7xmzgv6w2svbx has been executed 48942 time at total CPU cost of 1051322071

Note that the Disk reads is very low and the Buffer Gets are quite hight.

If we look at the SQL behind 7xmzgv6w2svbx:

SQL> set long 10000

SQL> select SQL_FULLTEXT from v$sql where SQL_ID='7xmzgv6w2svbx';

SQL_FULLTEXT

---------------------------------------------------------------------------

SELECT SUM(NVL(X.CUR_VAL, 0)) TOTAL_CUR_VAL FROM TABLE_X X WHERE X.ID = :B1 

We can see it's a very simple SQL statement, but statistically the heaviest statement running within this DB