what are the most expensive queries?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: what are the most expensive queries?

  1. #1
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93

    what are the most expensive queries?

    Hi, anybody know how to extract the queries that are executing and the cost of them (processing, buffers, sorting, etc) ?
    I want to know what is the cost of these queries in order to improve performance.

    Thank you for your help.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    You do not need cost of any query until the results are unacceptable. Fix things only when they break (My policy in tuning atleast). So, you know whats performing in unacceptable limits when people complain, take it from there and try tuning that component/query using oracle native tools like TKPROF, STATSPACK or other 3rd party tools.
    Reddy,Sam

  3. #3
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Good answer.
    Actually I'm performing some analysis to databases but nobody has complained yet. I want to go a forward step, before the problem arrives. Meanwhile I'll follow your advice.

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    The Oracle tuning pack is a great tool for finding problem sql before anyone knows it is a problem. I don't always foolow its advice exactly but it points me in the right direction. I think that time wise it is a lot more effective than tkprof and statspack. There are other programs that will probably give you the same thing.

  5. #5
    Join Date
    Sep 2001
    Location
    Mexico
    Posts
    93
    Recently I found this sqlplus script in the Internet. Any ideas ?

    -- #############################################################################################
    --
    -- %Purpose: Show the most resource intensive SQL statements that have been recently executed
    --
    -- Displays a list of the most resource intensive SQL statements
    -- that have been recently executed. Resource use is ranked by the
    -- number of SGA buffer gets, which is a good indicator of the work done.
    -- Only statements that are still cached in the SGA are searched -
    -- statements are discarded using an LRU algorithim.
    --
    -- #############################################################################################
    --
    set linesize 1200 verify off feedback 100

    accept gets default 100000 prompt "Min buffer gets [100,000] "

    col sql_text for a1000

    select
    s.BUFFER_GETS,
    s.DISK_READS,
    s.ROWS_PROCESSED,
    s.EXECUTIONS,
    substr(u.NAME,1,10) Username,
    s.SQL_TEXT
    from
    v$sqlarea s,
    sys.user$ u
    where
    s.buffer_gets > &&gets and
    s.parsing_user_id = u.user# and
    u.name <> 'SYS'
    order by
    s.buffer_gets desc
    /
    set feedback on

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