Re: AWR Sample Report

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Sun, 9 Nov 2008 18:49:31 -0800 (PST)
Message-ID: <2aeb30b0-ab48-49ee-b264-ec1ad7dd41e8@k36g2000pri.googlegroups.com>


Raja, you are making progress. You provided the standard definitions of the parameters, but for the most part did not indicate why the current parameters are set. Comments inline:

On Nov 9, 12:26 pm, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I have some details regarding the parameters that you have told to
> look into.
>
> 1. _newsort_enabled is set to FALSE
> - Activates new sorting algorithm in 10gR2, which is more efficient on
> memory and CPU
> - Default: TRUE
> - Its set to FALSE, may be due to one of the following reasons :
>   a. due to the side effect of this new parameter, which causes again
> a sort by row id eventhough the rows are in order.
(snip)

You might see if you are able to find a specific example in your database why this is disabled. Possible resource: http://jonathanlewis.wordpress.com/2007/06/03/sorting/

> 2. _optim_peek_user_binds is set to FALSE (there may be a very good
> reason),
> - Its set to FALSE, may be due to one of the following reasons :
>   a. not mess with the CBO explain plans
>   b. to make CBO create proper plans with respect to bind variables

Did you find that when it was set to TRUE Oracle was generating bad plans?

> 3. cursor_space_for_time is set to TRUE,
(snip)
> - I found that it is recommended by many people to have the value of
> this parameter as default ( FALSE ), since this has some side effects.

That seems to be the general rule, and I believe that I saw that this parameter would be depreciated in a future version of Oracle (Oracle 11g R2?).

> 4. db_writer_processes is set to 7 (there may be a very good reason
> for not doing this),
> - Default value : 1 or CPU_COUNT / 8, whichever is greater. Range of
> values 1 to 20

(snip)
>   Thus, it is recommended to set db_writer_processes equal to the
> number of LRU latches (db_block_lru_latches) and not exceed the number
> of CPUs on the system.

I was hoping that you would find the following article. After reading, you may consider significantly decreasing the value of db_writer_processes:
http://kevinclosson.wordpress.com/2007/08/10/learn-how-to-obliterate-processor-caches-configure-lots-and-lots-of-dbwr-processes/

> 5. optimizer_index_cost_adj is set to 5 (there is a very good reason
> for not doing this)
> - Default value : 100, Range of values zero to 10,000
> - This parameter alters the costing algorithm for access paths
> involving indexes. The smaller the value, the cheaper the cost of
> index access.
> - For some OLTP systems, re-setting this parameter to a smaller value
> (between 10- to 30) may result in huge performance gains!
> - Oracle support(without knowing anything about my system) is telling
> to use the following settings:
> OPTIMIZER_INDEX_CACHING = 50
> OPTIMIZER_INDEX_COST_ADJ = 5
Those appear to be suggestions that might be more appropriate prior to the introduction of CPU costing, which is enabled by default on Oracle 10g. OPTIMIZER_INDEX_COST_ADJ = 5 is far too low of a value, which basically tells Oracle to multiply the current index type access cost by 0.05. Not only does this have a significant chance of forcing unnecessary index scans when full table scans might be more appropriate, but it also increases the chances that the wrong index will be used for data retrieval due to rounding errors during cost calculations. References:
http://books.google.com/books?id=w8qzDTUVHSQC&pg=PA185&lpg=PA185&dq=troubleshooting+oracle+performance++OPTIMIZER_INDEX_COST_ADJ&source=web&ots=_eoA6x-X2H&sig=GndPn0-bZrVWfuMSStzqsxJ05wU&hl=en&sa=X&oi=book_result&resnum=4&ct=result http://jonathanlewis.wordpress.com/2006/10/24/optimizer_index_cost_adj/

> 6. resource_limit is set to TRUE.
> - Default value : false
> - determines whether resource limits are enforced in database
> profiles.
> - resource_limit = TRUE, Enables the enforcement of resource limits.
> I think that this would allow to control the resource limits for a
> particular user using profiles
> ( limits can be like max no of sessions used, max amt of SGA used, max
> connection time, max block read/session, CPU time limit/call,..).

What I was hoping that you would determine by looking at resource_limit is that potentially someone enabled a resource limit which is artifically causing an apparent performance problem. There is not enough evidence to suggest that this is the case, but you may want to take a look at that as a possibility.

> From the above analysis, I feel that the parameter values that are
> required to be changed are as follows :
> I. cursor_space_for_time, should be set to FALSE from the current
> value TRUE
> II. multi block read parameter has to be removed from init.ora, if we
> are using only init.ora file alone.
> III. change the optimizer mode from CHOOSE to the 10g default value
> ALL_ROWS
>
> Please check whether the details of these parameters are correct,
> including the final analysis/conclusion regarding the parameters.

The above seem to be logical conclusions to your research.

> Your Question :
> Regarding your guess about the single block reads, assume that during
> the 31 second time interval, an application was submitting a couple of
> queries to the database, and Oracle needed to read a couple (11,529
> in
> 31 seconds) 8KB blocks from disk to supply an answer to the
> application.  If the disk subsystem (or the connection between the
> server and the disk subsystem) was running near or at maximum
> capacity
> (possibly maximum number of random access IOs) due to a concurrent
> backup job, what happens to the performance of the application?
>
> Performance of the system will decrease.

Yes, that was exactly what I had hoped you would see as a possibility, although looking at an AWR report for a 31.2 second time period might be a bit misleading.

> Also, please check whether the analysis that i have made in my
> previous mail are correct or not.

I only commented on those items where it was easy to see that you had worked to find the solution and showed your analysis. I might have overlooked a couple items in your posts. I would suggest collecting an AWR report for 10 to 15 minutes, unless the performance problem is only present in this 31 second time period.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Nov 09 2008 - 20:49:31 CST

Original text of this message