Monitor running SQL



My OraFAQ Blog

Contact me

This query is useful if you're running a monitoring tool like Spotlight, see a sudden peak in database activity, and want to know what's responsible.

Depending on where the activity peak is, you might want to know which SQL statements are returning the most rows; which have the most buffer gets; or Which have the most disk reads. Change the ORDER BY in the script below to suit your needs.

select s.username curr_user, s.machine, sql_text, executions, 
round(decode(executions,0,0,(disk_reads/executions))) reads_per, 
round(decode(executions,0,0,(buffer_gets/executions))) buff_per, 
round(decode(executions,0,0,(rows_processed/executions))) rows_per,  
from v$sqlarea v, dba_users d, v$session s
where d.user_id = v.parsing_user_id
and s.sql_address=v.address and s.sql_hash_value=v.hash_value
--and s.sid=&session_id
order by decode(executions,0,0,(rows_processed/executions)) desc;

Note: Proofread any scripts before using. Always try scripts on a test instance first. I'm not responsible for any damage, even if you somehow manage to make my scripts corrupt every last byte of your data, set your server on fire and serve you personally with an eviction notice from your landlord!
All scripts and tips © Natalka Roshak 2001-2005.
Enjoy the FREE tips folks...