[SOLVED] Helpful DBA SQL Queries - Current Processes, Pig SQL

[chrisplough]

Hello!

Working with OTM - there are some SQL queries that you'll use over and over (and over!). I want to share them, as it may make your lives a bit easier

If you run these through SqlBackdoor, make sure you remove the ending ";".


This query will tell you what is currently running against the database just make sure to set your linesize from 20 to 1000.

select sid,username,osuser, sql_text 
from v$sqltext,v$session 
where address=sql_address 
and sql_hash_value=hash_value 
and status='ACTIVE' 
order by sid, piece;

Here is the historic pig SQL call:

select s.hash_value, s.sql_text, round(buffer_gets/executions) average_gets, round(disk_reads/executions) average_reads, s.executions, s.ROWS_PROCESSED from v$sqlarea s where executions > 0 and (buffer_gets + 100*disk_reads) / executions > 1000 order by (buffer_gets + 100*disk_reads) desc;

And this call is the current pig SQL calls against the database:

select s.hash_value, s.sql_text, round(buffer_gets/executions) average_gets, round(disk_reads/executions) average_reads, s.executions, s.ROWS_PROCESSED from v$sqlarea s where executions > 0 and (buffer_gets + 100*disk_reads) / executions > 1000 and exists (select vs.sql_address from  v$session vs where s.address = vs.sql_address ) order by (buffer_gets + 100*disk_reads) desc;

Hope this helps!

--Chris