Saturday, March 9, 2013

SQL usage & statistics in the shared pool

v$sqlarea contains statistics about SQL in the shared pool and its usage statistics

select * from
                     ( select sql_text,
                                 cpu_time/1000000000 cpu_time,
                                 elapsed_time/1000000000 elapsed_time,
                                 disk_reads,
                                 buffer_gets,
                                 rows_processed
                       from v$sqlarea
                       order by cpu_time desc, disk_reads desc
                     )
where rownum   < 21

Query shows SQL statement by their CPU usage.

No comments: