Announcement

Collapse
No announcement yet.

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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.
    Code:
    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:
    Code:
    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:
    Code:
    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
Working...
X