Proactive tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Proactive tuning

  1. #1
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999

    Question Proactive tuning

    I support a half dozen databases, almost all of Solaris 8, running either 8i or 9i. We are in the process of converting to 10g, but that may not happen for a while. I was planning on running this query from a stored procedure on each database everyday to compile all of the long running sql. I will run this from one database. Then I can check the output on a regular basis and use it to try and tune any badly performing SQL that I find.

    What do other people do to find and fix poorly tuned SQL?

    Code:
    SELECT v$sqlarea.sql_text,
           Trunc(v$sqlarea.disk_reads/
                 Decode(v$sqlarea.executions,0,1,v$sqlarea.executions)) reads_per_exec, 
           v$sqlarea.buffer_gets, 
           v$sqlarea.disk_reads, 
           v$sqlarea.executions, 
           v$sqlarea.sorts,
           v$session_longops.LAST_UPDATE_TIME,
           v$session_longops.TIME_REMAINING,
           v$session_longops.ELAPSED_SECONDS
      FROM v$sqlarea, v$session_longops
     WHERE v$sqlarea.address = v$session_longops.SQL_ADDRESS
       AND v$session_longops.ELAPSED_SECONDS > 5;
    this space intentionally left blank

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you do know your worst sql might not show up in v$session_longops?
    Last edited by davey23uk; 08-22-2006 at 04:35 PM.

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    statspack
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    SQL analyze, Oracle expert, third party tools.........
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    My goal was just to get a list of SQL that needed to get tuned. I can certainly run Statspack. In fact I will probably work on it at some point.
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by gandolf989
    My goal was just to get a list of SQL that needed to get tuned.
    sure you can do it through SQL analyze GUI tool will catch all the worst performing queries with all cost calculations and index recommendations. You can play around before implementing.

    If like doing scripting use statspack, analyze, tkprof and............
    "What is past is PROLOGUE"

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