Re: AWR Sample Report

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 :
2. due to the side effect of this new parameter, which causes again a sort by row id eventhough the rows are in order.
3. due to the use of the materialized views, which may use the row id. and to avoid this extra sort I feel that this new sort method still has some problem ( like beta version ) due to its side effects and hence they might have been avoided.
4. _optim_peek_user_binds is set to FALSE (there may be a very good reason),
   - enable peeking of user binds
   
   • Default value: TRUE
   • There are three things that might put you at risk of unstable plans due to bind variable peeking. Those are histograms, partitions, and range-based predicates.
   • Its set to FALSE, may be due to one of the following reasons :
5. not mess with the CBO explain plans
6. to make CBO create proper plans with respect to bind variables
7. cursor_space_for_time is set to TRUE,
   - This parameter specifies whether a cursor can be deallocated from
   the library cache to make room for a new SQL statement.
   - Lets you use more space for cursors in order to save time. It
   affects both the shared SQL area and the client's private SQL area. Then a cursor can be deallocated only when all application cursors associated with its statement are closed. In this case, Oracle need not verify that a cursor is in the cache, because it cannot be deallocated while an application cursor associated with it is open.
   - Default value: FALSE
   
   • 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.
8. 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
   - It specifies the initial number of database writer processes for an
   instance.
   - db_writer_processes will help only if you have multiple
   processors... else try setting dbwr_io_slaves
   - Multiple DBWn is useful for systems that modify data heavily.
   It specifies the initial number of database writer processes for an instance. Set automatically based on cpu_count. One DBWn for every eight CPU's.
   - With Oracle8 db_writer_processes, each writer process is assigned to
   a LRU latch set. 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.
9. 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
10. 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,..).

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.

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.

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

With Regards,
Raja. Received on Sun Nov 09 2008 - 10:26:16 CST