Handy SQL

Top 10 sql commands in the system that are using most Memory



select * from (select sql_text, sharable_mem + persistent_mem from v$sql order by 2 desc) where rownum < 11 order by 2

/

Top 10 sql commands in the system that are using most CPU




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

/

Top 10 sql commands in the system that are using most I/O




select * from (select sql_text, disk_reads, rows_processed from v$sqlarea order by cpu_time desc, disk_reads desc
) where rownum < 11

/


Transaction Pers Second



Transaction in oracle means all DMLs done between a commit/rollback and the next commit/rollback. Oracle generates a new SCN at commit time. So, I would would calculate number of SCNs generated per second and that will roughly give me transactions per second. v$log_history can be used to calculate number of SCNs generated.

col FIRST_CHANGE# for 999,999,999,999
col NEXT_CHANGE# for 999,999,999,999
alter session set nls_date_format='mm/dd/yy hh24:mi:ss';
select * from (
select FIRST_CHANGE#, NEXT_CHANGE#, SEQUENCE# , FIRST_TIME
from v$log_history order by 4 desc)
where rownum < 5
order by 4;

FIRST_CHANGE# NEXT_CHANGE# SEQUENCE# FIRST_TIME
---------------- ---------------- ---------- -----------------
18,501,481,600 18,501,516,335 13815 09/12/07 12:01:31
18,501,516,335 18,501,544,326 13816 09/12/07 12:21:32
18,501,544,326 18,501,572,873 13817 09/12/07 12:41:30
18,501,572,873 18,501,604,213 13818 09/12/07 13:01:31

You can see that between 12:01 and 12:21 number of SCNs generated = 18,501,516,335 - 18,501,481,600 = 34,935

34,735 transactions in 20 minutes = 28.94 transactions per second.

Note that this is an approximation because all SCNs are not generated due to commit, but most of the SCNs are generated at commit time.