DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Tuning doctor was called. What is his plan?

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    Tuning doctor was called. What is his plan?

    A client reports that his database is running very slow. The queries take too long. Creating of indexes on suspected queries seems to make the performance worse. The database server is on its last legs about to collapse anytime. Tuning doctor was called.

    How to go about tuning the database? Where to look? What are the first steps? run statspack? What next? Any readymade scripts? Any road map to tuning is appreciated.

  2. #2
    Join Date
    Jan 2003
    Location
    india
    Posts
    175

    Re: Tuning doctor was called. What is his plan?

    Originally posted by newbie5
    The queries take too long.

    What are the first steps?
    check what is the execution plan?

    -Raja

  3. #3
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The manual includes a "road map" - worth thinking about even if you decide to skip a lot of the steps.
    http://www.csee.umbc.edu/help/oracle...a67775/toc.htm

  4. #4
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    I read the 9.2 manual side to side. However, I believe reading a manual and try to tune first time is like trying to prepare a dinner for 100 guests from a recipe book or attempting brain surgery from a step-by-step instruction manual.

    This requires finesse that can come only from previous experience. I will take my chances with metalink but I am looking for more learned suggestions.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    After killing 100 patients, a person becomes doctor.

    Tamil

  6. #6
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Thumbs up

    Originally posted by newbie5
    . . . or attempting brain surgery from a step-by-step instruction manual.
    Is there any other way?


    I'm sure I can't do any better than the manual, except to summarise:

    - be methodical, keep good records of action & result.
    - analyse (that means to break up into smaller pieces - opposite of synthesise), "tuning the db" is too big for my small brain.
    - gather evidence, statspack, user complaints etc.
    - prioritise, go for whatever is hurting the most.
    - make your own simple tools (test cases, benchmarks) tkprof will tell you what they are doing.
    - prepare to be wrong, esp. recognising if you're in a dead-end.

    - let us know how you get on.

  7. #7
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    My suggestion in approaching this would be try and take one poorly performing query at a time. Don't try and find a "cure all" for everything; there might not be one. Find out from the users what the top, say 5, most time critical queries are and then perform a 10046 trace for each one in turn. Use Trace Analyzer to analyze the trace file and find out where all the time is going, and then try and eliminate that time spent waiting. This might be SQL tuning, or it might be tuning system wide parameters. Once one query is dealt with then you may have the good luck of a resolving an issue that was effecting all the other queries, but then again you might not. So move onto the next...

    You can download Trace Analyzer from

    here.

    To perform a 10046 trace find the spid with the following query:

    SELECT p.spid
    FROM v$session s,
    v$process p
    WHERE s.paddr = p.addr
    AND s.username = 'USER YOU WANT TO TRACE'

    alter system set max_dump_file_size = unlimited;
    oradebug setospid SPID FROM QUERY ABOVE;
    oradebug unlimit;
    oradebug event 10046 trace name context forever, level 12;

    The resulting trace file will appear in user_dump_dest and can be analyzed by Trace Analyzer.

    IMHO, I'd avoid the temptation to concern yourself with systemwide stats, just focus on the individual user actions.

  8. #8
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    Thanks hacketta1. I have one week to tune a beast of the database that is running a java app on linux with multiple tables having more than 50 million rows. I will start with the queries. I will also look at shared pool and I/O issues and go from there.

    I will keep this post updated with feedback.

  9. #9
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    I will also look at shared pool and I/O issues and go from there
    If these are the problem then you'll find this out when you trace your queries. It can be very difficult to determine whether a problem exists when you just look at these things in isolation. Worse still, you might spend a lot of time tuning something that has little tangible benefit for end users. Go wherever the trace files take you!

  10. #10
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Ever heard of 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."

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