Re: performance questions and help!

On Dec 6, 11:45 pm, scorpio1..._at_yahoo.com wrote:
> On Dec 6, 8:18 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> > Warning about the above: the above, while potential answers to the
> > questions, are definitely not the answers that your instructors expect
> > to see.  The above is intended to help you think about performance
> > issues, and they include clues to the simple answers that your
> > instructors likely expect.
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc

> thank you so much for the help.
> i realy like to understand  the topic and concepts but i can't find
> the good place to look and all i have to do is
> to search and aske.
> any way thank you very much

The last link provided by Robert Klemme is the Oracle 10g R2 Performance Tuning Guide, and if that is the specific Oracle version which your class is using, you will likely have the most success reading that book.

Part of my problem with the questions is that they are very open ended. There are simple answers, such as using autotrace, but as you gain experience through reading and experimentation, you may occasionally find that the plan displayed by autotrace is not necessarily the actual execution plan. The question then becomes what do you do when the simple answers do not seem to work? If 10 frequent contributors to this forum correctly answered the three questions you provided, you would likely have 10 completely different answers for each of the three questions.

Another problem that I have with the questions is that the simple answer is Oracle version dependent. The simple answers on Oracle 8.0.5 Standard Edition differ significantly from the simple answers on Oracle 11.1.0.6 Enterprise Edition with all of the additional cost licenses.

Another problem that I have with the questions is that the question which follows the statement is not always a logical next question to ask If you take the question "What steps can you take to reduce the sorting?", my first question would be how did we come to the conclusion that it is an excessive sorting to disk problem? Maybe the sorts are required by the SQL statements to retrieve the data in a specific sorted order, or maybe someone disabled the group by optimization in Oracle 10g, or maybe someone is rebuilding all indexes in the database on a daily or weekly schedule. Maybe the CPU is overloaded, so the DBA selected to reduce the CPU load by decreasing the number of "WORKAREA EXECUTIONS - OPTIMAL", causing the statistic "WORKAREA EXECUTIONS - ONEPASS" to increase. Maybe the sorts are required, and SORT_AREA_SIZE, SORT_AREA_RETAINED_SIZE, HASH_AREA_SIZE need to be adjusted - but what if PGA_AGGREGATE_TARGET is also set to a value?

You might want to save the answers that you receive in this thread so that they may be re-read after you have gained additional experience in Oracle performance tuning. As you read books and blog articles about performancing tuning, what was stated here will begin to make sense.

Some feedback regarding your response to hpuxrac, a summarized version of the possible answers which you provided:

Question 1:  auto trace on / explain plan
Question 2:  make the another temprary tablespace
Question 3:  bind variables

For question 1, keep in mind that autotrace and explain plan may show you a different execution plan than what was actually used when the SQL statement executed. Recent versions of Oracle allow the actual execution plan to be pulled directly from Oracle's views, or the actual execution plans may be obtained from 10046 trace files. Your answer for question #1 might be good enough for the class. For question 2: that will not decrease the amount of sorting, it just spreads the sorts to disk across more files, and may actually increase the time required for the disk sorts to complete. Your answer for question #2 needs a lot of work.
For question 3: using bind variables is usually better than specifying constants in the SQL statements. Keep in mind that even with the use of bind variables, you may still see duplicate SQL statements in V$SQL if the length of the data supplied for the bind variables varies, or for the other reasons I outlined in my original response. Automatically generated histograms in Oracle 10g and 11g, with the combination of bind variable peeking, may create a performance problem which is significantly worse than having essentially the same SQL statement with different constant values sitting in the shared pool. Your answer for question #3 lists one of the possible solutions.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Dec 07 2008 - 08:13:41 CST