This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Aug 3, 2012 11:00 AM by jgarry RSS

Database Tuning

953640 Newbie
Currently Being Moderated
hi, i'm new to oracle and my superior ask me to do some database tuning for him
(Performance Tunig, SQL Tuning, Etc)
so i'm wondering what should i do first to do Database Tuning.

i can do the SQL tuning just fine, but since it is the first time i touch the configuration(example : tablespace, redo log, etc)
i don't know what should i do first.

can anyone give me helpful tips or step by step to do it
  • 1. Re: Database Tuning
    rp0428 Guru
    Currently Being Moderated
    Welcome to the forum!

    First thing to do is read the FAQ for how to post a tuning request and the information you need to provide.
    >
    hi, i'm new to oracle and my superior ask me to do some database tuning for him
    >
    That's gotta be one of the most ridiculous things I've ever heard.

    The first rule of tuning is: if it ain't broke, don't fix it.

    You don't just decide to tune a database. There has to be some reason for thinking that there is anything that even needs tuned.

    Ask your superior

    1. Why do they think anything needs tuned?
    2. What is the list of things that they think need tuned?
    3. Why do they think that those things need to be tuned?
    4. Why are you even asking me to do such a ridiculous thing when there is no rhyme or reason for it?

    Ok, for #4 maybe you should just think that one to yourself. But the others are legitimate questions that you should ask before you do anything. And be sure to document the answers so you don't get left holding the bag when it turns out there wasn't any tuning to do.
  • 2. Re: Database Tuning
    Pavel Explorer
    Currently Being Moderated
    Hello

    take a look at
    Re: Oracle database Performance testing

    Regards,
    Pavel
  • 3. Re: Database Tuning
    sb92075 Guru
    Currently Being Moderated
    HOW To Make TUNING request
    SQL and PL/SQL FAQ
  • 4. Re: Database Tuning
    953640 Newbie
    Currently Being Moderated
    that just it, i don't know where to start tuning
    so i was hoping any of the experts here can tell me what to do
  • 5. Re: Database Tuning
    sb92075 Guru
    Currently Being Moderated
    950637 wrote:
    that just it, i don't know where to start tuning
    so i was hoping any of the experts here can tell me what to do
    Why do you think any tuning needs to be done?
    If it ain't broke, then don't change anything.

    come back & ask more questions here after Reading The Fine Manual below

    http://docs.oracle.com/cd/E11882_01/server.112/e16638/toc.htm
  • 6. Re: Database Tuning
    rp0428 Guru
    Currently Being Moderated
    >
    that just it, i don't know where to start tuning
    so i was hoping any of the experts here can tell me what to do
    >
    I already did that above. Why are you replying to yourself instead of replying to the people trying to help you?
  • 7. Re: Database Tuning
    953640 Newbie
    Currently Being Moderated
    well my supervisor ask me to check wether any tuning is needed
    i know how to do SQL tuning, and i can check the SQL response time, etc.
    but then my supervisor ask me to do some configuration tuning

    and that is my question,
    1. how to determine wether any configuration tuning is need to be done or not
    2. how can i see the result to any configuration changes i've made

    thank you
  • 8. Re: Database Tuning
    953640 Newbie
    Currently Being Moderated
    sorry, i'm still new to this stuff
  • 9. Re: Database Tuning
    sb92075 Guru
    Currently Being Moderated
    950637 wrote:
    well my supervisor ask me to check wether any tuning is needed
    i know how to do SQL tuning, and i can check the SQL response time, etc.
    but then my supervisor ask me to do some configuration tuning
    In my opinion one tunes a configuration, by tuning 1 SQL statement at a time.

    post results from SQL below

    SELECT * FROM V$VERSION;

    >
    and that is my question,
    1. how to determine wether any configuration tuning is need to be done or not
    Which application operation do user report runs "too slow"?
    2. how can i see the result to any configuration changes i've made
    Benchmark current performance
    make a change
    Benchmark new performance

    Is application faster after the change than before?
    If not, back out the last change.
  • 10. Re: Database Tuning
    953640 Newbie
    Currently Being Moderated
    1. Why do they think anything needs tuned?
    well, the time it takes to process a report is taking too long.

    2. What is the list of things that they think need tuned?
    they already give me the module to tune, and as just like i stated before
    i've already done the SQL tuning, but then they ask me
    can i do some configuration tuning?

    3. Why do they think that those things need to be tuned?
    well just like i said they said the amount of time required to process a report is too long

    4. Why are you even asking me to do such a ridiculous thing when there is no rhyme or reason for it?
    i'm sorry if i take your time or offended you in anyway,
    i'm a university student who is currently doing an internship with an IT Consultant
    and they give me a case and i don't have an oracle background
  • 11. Re: Database Tuning
    953640 Newbie
    Currently Being Moderated
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE     11.2.0.1.0     Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    this will sound very stupid, but where/how i benchmark my performance?
  • 12. Re: Database Tuning
    sb92075 Guru
    Currently Being Moderated
    950637 wrote:
    1. Why do they think anything needs tuned?
    well, the time it takes to process a report is taking too long.
    1) enable SQL_TRACE & run the report.
    2) process trace file with tkprof to see which SQL take a long time, then tune those SQL
    3) go to #1 above & repeat
  • 13. Re: Database Tuning
    Nikolay Savvinov Guru
    Currently Being Moderated
    Hi,

    I think your task is to tell your boss in the politest way possible that we he is asking you to do is nonsense. "Configuration tuning" in Oracle has been essentially dead for quite a few years. It was used in dark ages, when SQL tracing was either inaccessible or not familiar to most DBAs, when the databases didn't know how to auto-configure themselves, and when memory was so expensive that you almost always something was undersized and could benefit from being given more memory. Back in the day, the database was looked at as a black box with knobs and buttons, so if something goes wrong, you start turning the knobs and pushing the buttons, using global counters and ratios to guide you through the process.

    Since the advent of Milsap and Holt's book "Optimizing Oracle Performance", most database analysts worth their salt have abandoned this terribly inefficient process in favor of what the authors called "Method R": take user actions that your business cares most about, see why it takes them so long to complete, and work on it.

    Note that it doesn't mean that system-wide tuning is completely gone -- in some cases, if your problem is big enough, you may see it a tad faster in an AWR report rather than a trace file; and in some cases, when investigating a specific SQL statement, you may find that it's slow because of some global system parameter (although this is happening less and less often). But most of the time performance tuning boils down to one or a few of the following:

    1) identifying poorly written code and fixing it
    2) identifying data design errors and fixing it
    3) identifying physical data layout issues and fixing it
    4) identifying "crazy" plans and fixing it

    and the fastest way possible to get to the bottom of the problem is by looking at response time profile of the user actions that your business cares about the most. In your case it's even simpler, because your boss already told you what report he wants to run faster.

    You're saying that you've already tuned it -- if this is really the case (i.e. you haven't just fixed the obvious problems, you really minimized the report time to the minimum theoretically possible value), then you should go to your boss and tell him to buy faster hardware. But before doing that, I would double-check, e.g. by posting the plan with cardinality feedback & timings here.

    Best regards,
    Nikolay
  • 14. Re: Database Tuning
    jgarry Guru
    Currently Being Moderated
    950637 wrote:
    1. Why do they think anything needs tuned?
    well, the time it takes to process a report is taking too long.
    If you are licensed for performance tuning (you are allowed to use the AWR reports and the performance screens in dbconsole), then you should check the AWR report for the times when the report didn't used to take too long. Also, an explain plan from that time would be good. You see, Oracle has a cost based optimizer, and, while there are ways to lock in a plan, one of the common reasons for a sudden change in the performance of a report is that for one of many reasons, even just a little more data, the optimizer decided to change the plan. In some places, benchmark plans are created as part of the development process, for this very reason. If you don't have one of those, we can help you with what is current as Nikolai said. If you are not licensed, you should google and check the docs for statspack. The use for AWR or statspack in these situations is to see what has changed, and if the report you are tuning is the culprit or the victim of something else. For example, it could be the first clue you have a dying disk controller, or some other process is fighting for your data.

    >
    2. What is the list of things that they think need tuned?
    they already give me the module to tune, and as just like i stated before
    i've already done the SQL tuning, but then they ask me
    can i do some configuration tuning?
    Yes, it doesn't hurt to check. However, most tuning problems are the sql. If you can compare a good-times awr or statspack to the current problem time, something may pop out.

    >
    3. Why do they think that those things need to be tuned?
    well just like i said they said the amount of time required to process a report is too long
    Is there anything else wrong? You don't want to make changes outside the scope of the problem, that can create more problems. There are no silver bullets, and there is instrumentation to tell you what, if anything, is wrong. Sometimes you simply do just need to throw hardware at it. Sometimes the best performance comes from not doing something.

    >
    4. Why are you even asking me to do such a ridiculous thing when there is no rhyme or reason for it?
    i'm sorry if i take your time or offended you in anyway,
    i'm a university student who is currently doing an internship with an IT Consultant
    and they give me a case and i don't have an oracle background
    Well, that's a learning experience! I don't think he is mad at you, but rather that is how he would feel given your task. Rather than be mad, I would be poking around to see what people are really complaining about, and aren't. It does happen that some systems are so off that people don't even complain, because it's been bad since some vendor hack installed it. It also happens that people complain because they really don't understand what the system does, or sometimes just because they can't complain about what is really bothering them. It can take some detective work, and you don't have the "intuition" that comes with experience. Fresh eyes can be good, too.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points