Re: AWR Sample Report

Comments inline:
On Nov 10, 6:07 am, raja <dextersu..._at_gmail.com> wrote:
> I am still not clear with some details.
> 1. why oracle should create parameter that have side effects ( may be
> thats y they are created as hidden parameters ) ?

Hidden parameters (those which begin with _ ) should only be changed after consulting Oracle Support, regardless of what some websites suggest to use in order to "super-size" a parameter to improve performance - chances are, there is another website which suggests that changing that hidden parameter has undesirable side effects. It is important to understand the undesirable side effects for all parameter values, especially if those parameters are hidden parameters.

Oracle databases must work efficiently with many different types of applications (online transaction processing [OLTP], batch processing, data warehouse, etc.), and when you consider that versions of Oracle databases exist (or have existed) for somewhere around 100 different operating systems, there needs to be flexibility in the database instance, which is controlled through the use of different parameter values. Parameters (and features) which have no visible side effects in an OLTP database may have severe side effects in a data warehouse database, and the various parameter values help eliminate the side effects.

It would be nice if there were a checklist built into the database software that someone could complete, which would then suggest the optimal parameters for the database instance, the suggested size and number of redo logs, the most appropriate location of the data files, the number and speed of the CPUs, the amount of memory, the optimal operating system, PCTFREE values for tables, etc. To my knowledge, that checklist does not exist. Thus, the DBA must be willing to investigate the optimal settings for the specific database instances, and the applications that those database instances serve. An optimal parameter value for an Oracle 8.1.7.3 database instance may not be an optimal value for an Oracle 11.1.0.7 database instance due to improvements/changes in optimizer features.

> 2. setting the values are still not clear, because some have
> dependancy, some are system specific,....
>
> From the above discussions / analysis,
>
> No changes to the following parameter values are to be made :
>
> 1. _newsort_enabled is set to FALSE
> 2. _optim_peek_user_binds is set to FALSE
> 3. resource_limit is set to TRUE
>
> Changes to the parameter values to be made are as follows :
>
> 1. cursor_space_for_time, should be set to FALSE from the current
> value TRUE
> 2. db_file_multiblock_read_count - has to be removed from init.ora, if
> we are using only init.ora file alone.
> 3. optimizer mode - set the value as ALL_ROWS, from the current value
> CHOOSE
> 4. optimizer_index_cost_adj - should increase the value from the
> existing value 5.
> Current values : optimizer_index_caching 90 ;
> optimizer_index_cost_adj 5.
> I hope i can set optimizer_index_cost_adj to 10, considering not
> to have too low value ( 5 ).
> 5. db_writer_processes - The value should be decreased from current
> value 7. Let me check on what value should be set and get back to you.
>
> Please check, whether the conclusions are right/wrong.

I believe that the above values for optimizer_index_caching and optimizer_index_cost_adj still need a little more adjustment to avoid an over-dependence on nested loop joins and to avoid utilizing index based access (possibly with the wrong index) when a full tablescan might be more efficient.
Paraphrased from "Cost-Based Oracle Fundamentals": "OPTIMIZER_INDEX_CACHING helps correct the optimizer’s assumption that all reads are physical reads. This has an effect when multiple tables are joined using nested loops with index access for the inner (second) table, but also has an effect for cost calculation of in-list iteration. This parameter does not affect the cost of a single table indexed access path. A starting value of 75 is a reasonable starting guess."

Paraphrased from "Cost-Based Oracle Fundamentals": "OPTIMIZER_INDEX_COST_ADJ reduces the cost of single block reads, which tends to reduce the tendency of excessive tablescans. The downside is that due to rounding errors, the optimizer may decide to use the wrong index."

Paraphrased from "Troubleshooting Oracle Performance": "Oracle 8i and earlier use the I/O cost model, which by default costs single block and multi-block reads the same. OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING help to correct this issue by making index access (single block access) appear to be less expensive. CPU cost model (system statistics), used by default with Oracle 10g, takes into account the performance of the CPU and the I/O subsystem." The CPU cost model nearly eliminates the need to change OPTIMIZER_INDEX_COST_ADJ and OPTIMIZER_INDEX_CACHING from their defaults. CPU cost model statistics are stored in SYS.AUX_STATS$ .

Based on the above, you might want to consider setting (test the effects of changes):

* OPTIMIZER_INDEX_COST_ADJ close to 100
* OPTIMIZER_INDEX_CACHING close to 0
* Values in SYS.AUX_STATS$ to appropriate values

The following SQL*Plus command will automatically calculate the values in SYS.AUX_STATS$ based on a 60 minute time interval (starting immediately after you execute this statement): EXEC DBMS_STATS.GATHER_SYSTEM_STATS('interval',interval=>60)

While you are checking SYS.AUX_STATS$, you may also want to verify that fixed object statistics have been gathered by querying SYS.TAB_STATS$ - if no rows are returned, then fixed object statistics have not been gathered and poor performance or session crashes may result, see:
http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/e0599d3e043fc199

> I will also try to get another AWR Report for about 10-15 minutes.

That would be a good idea. Keep in mind that the above suggestions are very general in nature, and may not exactly apply to your database instance. The AWR report for 10-15 minutes will allow for more specific/relevant suggestions.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Nov 10 2008 - 08:53:18 CST