Capture Resource intensive SQLs
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Capture Resource intensive SQLs

  1. #1
    Join Date
    Sep 2000
    Posts
    155
    I was wondering if there is a way to capture "resource intensive" SQL statements in the database without turning
    the trace on.

    I want avoid turning tracing on in the production database
    and also avoid generating lot fo trace files.

    Are there any useful SQL statements to capture "resource intensive" SQL statements?


    TIA

  2. #2
    Join Date
    Feb 2002
    Posts
    13
    Here are two:

    ------------------------------------------------------------------------
    -- Purpose:to find expensive sql that may need tuning
    ------------------------------------------------------------------------
    column load format a6 justify right
    column executes format 9999999
    break on load on executes

    select substr(to_char(s.pct,'99.00'),2)||'%' load,
    s.executions executes, p.sql_text
    from
    (select address, disk_reads, executions, pct,
    rank() over (order by disk_reads desc) ranking
    from
    (select address, disk_reads, executions,
    100 * ratio_to_report(disk_reads) over () pct
    from v$sql
    where command_type != 47)
    where disk_reads > 50 * executions) s,
    sys.v_$sqltext p
    where s.ranking <= 5
    and p.address = s.address
    order by 1, s.address, p.piece
    /
    undefine Percent

    AND

    ------------------------------------------------------------------------
    -- Purpose: to find suspicious sql that may need tuning
    ------------------------------------------------------------------------column load format a6 justify right
    column executes format 9999999
    break on load on executes
    select substr(to_char(s.pct, '99.00'), 2) || '%' load,
    s.executions executes, p.sql_text
    from (select address, buffer_gets, executions, pct,
    rank() over (order by buffer_gets desc) ranking
    from (select address, buffer_gets, executions,
    100 * ratio_to_report(buffer_gets) over () pct
    from sys.v$sql
    where command_type != 47)
    where buffer_gets > 50 * executions) s, v$sqltext p
    where s.ranking <= 5
    and p.address = s.address
    order by 1, s.address, p.piece
    /
    undefine Percent

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width