This discussion is archived
1 2 Previous Next 23 Replies Latest reply: Oct 29, 2013 12:50 AM by Girish Sharma RSS

Key Performance Related Parameters in Oracle 10g

dbanirav Newbie
Currently Being Moderated

I have setup of Oracle 10gR2 Standard Edition. I want to know key performance related parameter on which I could focus to optimize DB performance....

 

Thanks

  • 1. Re: Key Performance Related Parameters in Oracle 10g
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    leave the parameters alone. It's XXI century, nobody is doing parameter-based database tuning anymore.

     

    Best regards,

      Nikolay

  • 2. Re: Key Performance Related Parameters in Oracle 10g
    sybrand_b Guru
    Currently Being Moderated

    1 A badly written application will not perform well by tuning the database, Oracle recommends to tune your SQL first

    2 Increasing memory may result in extra paging and swapping

    3 Incorrect usage of disk layout (everything on one disk, using RAID-5) will be not remedied by 'tuning the database'

     

    That said you need to use the various v$%advice views to determine optimal configuration of memory parameters

     

    Please note that parsing every statement and/or committing every individual record for sure will result in bad performance.

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 3. Re: Key Performance Related Parameters in Oracle 10g
    Hoek Guru
    Currently Being Moderated

    If you want to optimize database performance, then being able to boldly tweak a bunch of parameters isn't enough, you'll first have to understand how things work and what to keep en eye on. Here's a good starting point:

    http://docs.oracle.com/cd/E11882_01/server.112/e41573/part3.htm#i436439

  • 4. Re: Key Performance Related Parameters in Oracle 10g
    Saugat Chatterjee Newbie
    Currently Being Moderated

    Try these

     

    1) you simple keep an eye on the top sql statements and try to tune them,you can use EM for getting various sql statements which are creating problem,then you can run SQL tuning advisor for various tuning related advisories.

    2) Run ADDM regulary and see if there is performance related issue

    3) Try to compare AWR reports of different time intervals and find out problems

    4) Keep an eye on the various HIT RATIOS

    5) keep an eye on the sort area size,tablespace sizes,SGA,buffer cache,log buffer,redo buffer etc

    6) Keep an eye on the operating system CPU usage

     

    furthermore u should constantly keep monitoring your database using EM

     

    regards

    saugat

  • 5. Re: Key Performance Related Parameters in Oracle 10g
    sybrand_b Guru
    Currently Being Moderated

    Ratio tuning doesn't have any positive impact. Connor McDonald has demonstrated, in the 20th century, you can force Oracle to reach any hitratio.

    Basically in step 4) 5) and 6) you are advocating to throw hardware at the problem.

    Usually this will only work if the number of records and table sizes are constant. If the database keeps growing, you will throw hardware at the problem forever.

    IMO, this is misguided advice and only a last resort.

     

    ----------

    Sybrand Bakker

    Senior Oracle DBA

  • 6. Re: Key Performance Related Parameters in Oracle 10g
    Alvaro Pro
    Currently Being Moderated

    Agreed with what you said, just adding that while buffer cache hit ratios are to be taken with a large grain of salt, Row cache and library cache hit ratio are still pretty important for an overall health check of the database.

     

    Regards

  • 7. Re: Key Performance Related Parameters in Oracle 10g
    sb92075 Guru
    Currently Being Moderated

    >Row cache and library cache hit ratio are still pretty important for an overall health check of the database.


    How so?

    Please quantify.

  • 8. Re: Key Performance Related Parameters in Oracle 10g
    JohnWatson Guru
    Currently Being Moderated

    SaugatChatterjee wrote:

     

    Try these

     

    1) you simple keep an eye on the top sql statements and try to tune them,you can use EM for getting various sql statements which are creating problem,then you can run SQL tuning advisor for various tuning related advisories.

    2) Run ADDM regulary and see if there is performance related issue

    3) Try to compare AWR reports of different time intervals and find out problems

    4) Keep an eye on the various HIT RATIOS

    5) keep an eye on the sort area size,tablespace sizes,SGA,buffer cache,log buffer,redo buffer etc

    6) Keep an eye on the operating system CPU usage

     

    furthermore u should constantly keep monitoring your database using EM

     

    regards

    saugat

    You can't use ADDM or any advisor on Standard Edition. It would be illegal. And anyway, what decent DBA relies on them? All they do is apply some general principles that sometimes happen to be useful. I have not often seen them give advice that was definitely wrong, but I do know that I can usually do better. If they were any good, no-one would need a DBA.

  • 9. Re: Key Performance Related Parameters in Oracle 10g
    Girish Sharma Guru
    Currently Being Moderated

    I guess below link will give you a good start :

    Top Performance Areas - 11g Release 1 (11.1.1)

    Table 2-2 Important init.ora Oracle 10g Database Tuning Parameters

     

    Since above link is just saying "Oracle 10g Database" i.e. no mention of Standard or Enterprise Edition, so I am sure, they will work for standard edition too.

     

    Regards

    Girish Sharma

  • 10. Re: Key Performance Related Parameters in Oracle 10g
    Saugat Chatterjee Newbie
    Currently Being Moderated

    sir

     

    what i stated is just the starting point for performance tuning,and ofcourse a DBA should do a lot more than the mentioned points

  • 11. Re: Key Performance Related Parameters in Oracle 10g
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

    SaugatChatterjee wrote:

     

    Try these

     

    1) you simple keep an eye on the top sql statements and try to tune them,you can use EM for getting various sql statements which are creating problem,then you can run SQL tuning advisor for various tuning related advisories.

    2) Run ADDM regulary and see if there is performance related issue

    3) Try to compare AWR reports of different time intervals and find out problems

    4) Keep an eye on the various HIT RATIOS

    5) keep an eye on the sort area size,tablespace sizes,SGA,buffer cache,log buffer,redo buffer etc

    6) Keep an eye on the operating system CPU usage

     

    furthermore u should constantly keep monitoring your database using EM

     

    regards

    saugat

    1. WRONG. This is a typical example of DBA "make work" job. The only valid reason to tune SQL is to create value for your business, not because it occupies a high position is some list. Occasionally, you may have to tune a statement from the benefit of the entire system -- but this is only justifiable if you have evidence that there is a deficit of resource consumed by that statement. E.g. if you have a query responsible for 60% of CPU consumption by the database, but the database is only consuming 20% of the server's CPU (and it's 75% idle), then let it me -- tuning it won't generate any system-wide performance benefits. You can tune it if business wants it run faster, but then it doesn't matter what position it occupies in what lists

    2. WRONG. Looking for performance related issues in ADDM or other system-generated reports is another example of "make work". Performance issue is when users wait longer then they should for their screens to respond, i.e. in a way, it's always about an SLA (explicit or implied) breach. ADDM doesn't know anything about your SLAs, so it cannot tell you about your performance. Looking at ADDM to see if you have a performance issue is like going to a doctor and asking him how you feel.

    3. WRONG. "Make work" again. Don't just compare AWR reports of "different time intervals" to find out problems. Find out about problems (from your users, business analysts, or better yet, by monitoring SLAs and application-side KPIs), and then, if the scope of the problem is right, go to AWR to see if it can help you find the root cause.

    4. WRONG (see comments by other posters)

    5. Even WRONGER. Look out the window -- it's the second decade of XXI century. You're no longer running on computers that only have enough space for buffer cache or log buffer, but not for both at once. And you're no longer stuck with ancient database versions which require you to size every little thing manually. With the advent of ASMM and other similar technologies, worrying about proper sizes is no longer a part of DBA's day-to-day job. But most importantly, these days performance issues almost never happen because some buffer is too small. Performance issues happens because people write crappy code (that would exhaust any amount of resources you throw at it), because people design relational databases with no regard to relational design principles, because people use misguided indexing and partitioning strategies, etc. This is not something ADDM can diagnose for you.

    6. Finally, CORRECT. OS CPU usage has to be monitored regardless to whether or not CPU is an issue:

     

    Is CPU usage 100% really okay? | Oracle Diagnostician

     

    Best regards,

    Nikolay

  • 12. Re: Key Performance Related Parameters in Oracle 10g
    Girish Sharma Guru
    Currently Being Moderated

    >ADDM doesn't know anything about your SLAs, so it cannot tell you about your performance. Looking at ADDM to see if you have a performance issue is like going to a doctor and asking him how you feel.

     

    While docs are saying :

    The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system.


    Automatic Performance Diagnostics


    Regards

    Girish Sharma

  • 13. Re: Key Performance Related Parameters in Oracle 10g
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    GirishSharma wrote:

     

    >ADDM doesn't know anything about your SLAs, so it cannot tell you about your performance. Looking at ADDM to see if you have a performance issue is like going to a doctor and asking him how you feel.

     

    While docs are saying :

    The Automatic Database Diagnostic Monitor (ADDM) analyzes the AWR data on a regular basis, then locates the root causes of performance problems, provides recommendations for correcting any problems, and identifies non-problem areas of the system.


    Automatic Performance Diagnostics

    Oh no. Really? How suprising. That's probably the first case in the history of mankind when a manufacturer claims his product can do things that it can't.

     

    Best regards,

    Nikolay

  • 14. Re: Key Performance Related Parameters in Oracle 10g
    Girish Sharma Guru
    Currently Being Moderated

    >Oh no. Really? How suprising. That's probably the first case in the history of mankind when a manufacturer claims his product can do things that it can't.

     

    It depends, who is manufacturer, how his products are recognized / rating in the industry etc.  When official docs are claiming that ADDM can locate the root causes of performance problem, it means YES it can (if it is properly used, configured and understood) and if it is not, then anyone can file a doc bug with providing a complete test case.

     

    Even though, I am not providing/knows a test case that how ADDM will locate performance problems but since it is written in the docs and have not seen a test case which shows that ADDM is not able to locate performance issue by providing test case, then I have to believe upon it.

     

    Great scientist (Oracle Engineers) have invented many surprising things/theory/algorithm for mankind though and mankind always have being surprised by their inventions many time.

     

    Regards

    Girish Sharma

1 2 Previous Next

Legend

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