Re: AWR Sample Report

On Nov 7, 4:31 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> Thanks for all your comments / suggestions.
>
> Charles / Steve,
>
> Thanks for ur guidance.
>
> I am just starting / want to interpret the statspack / AWR Report.
> So, Please check whether my observations are correct, regarding the
> AWR Report.
>

(Snip)
> 5. While seeing the init.ora Parameters, the parameters here are not
> according to 10g defaults, it looks like they are according to 9i
> defaults ( recently
>
> migrated from 9i to 10g ).
> I. I found one parameter that needs to be changed -
> a. optimizer_mode : should be changed from 'choose' to 'all_rows',
> correct ?
> b. db_file_multiblock_read_count : i came to know that, this value
> should not be set in oracle 10g, i.e., remove this parameter from
> init.ora file.
> correct ?
> II. Any other parameters that are needed to be changed ?
>
> init.ora Parameters
>
> Parameter Name Begin value End value (if different)
> _newsort_enabled FALSE
> _optim_peek_user_binds FALSE
> compatible 10.2.0.3
> cursor_space_for_time TRUE
> db_block_size 8192
> db_cache_advice on
> db_file_multiblock_read_count 8
> db_files 1024
> db_writer_processes 7
> job_queue_processes 6
> log_buffer 14242816
> max_dump_file_size 16384
> open_cursors 1024
> optimizer_index_caching 90
> optimizer_index_cost_adj 5
> optimizer_mode choose
> pga_aggregate_target 4294967296
> processes 300
> recovery_parallelism 4
> resource_limit TRUE
> session_cached_cursors 256
> session_max_open_files 128
> sga_target 8589934592
> statistics_level typical
> timed_statistics TRUE
> undo_management AUTO
> undo_retention 6000
> undo_tablespace UNDO
>
> 6. Charles Question : if the disk subsystem is reading on average
> about 24MB per second and writing on average about 15MB per second,
> what happens when the
>
> 11,529 (roughly 370 per second) single block 8KB reads occur during
> query execution? Do those 11,529 single block reads occur
> immediately, or must they
>
> queue behind other read and write requests as well as wait for the
> drive heads to relocate over the correct location on the physical
> disk?
>
> My Guess : Those 11,529 single block reads will wait requesting for
> next read/write, since block size is 8KB ( db_block_size : 8192 )
> Correct ?
>
> With Regards,
> Raja.

It appears that you have spent some time investigating these items.

You probably do not want to have the optimizer mode set to choose. A while ago I was testing Oracle 11.1.0.6 and found when looking at DBMS Xplans (which show the expected execution order and method for retrieving data) that the rule based optimizer was used for some SQL statements - I thought that was an odd comment to appear in a 11.1.0.6 DBMS Xplan as I was under the impression that the rule based optimizer was obsolete as of Oracle 10g. ALL_ROWS is probably a better parameter value to use, as FIRST_ROWS (and its variants) may cause odd performance problems as this value tends to cause Oracle to use indexes excessively (in testing that optimizer mode, I recall having significant performance problems when querying the data dictionary).

There are a couple articles describing the Oracle 10g R2 change in the default behavior of db_file_multiblock_read_count in the website that I provided in my first response in this thread, and I believe that the change in default behavior is also mentioned in the Oracle documentation (possibly the Performance Tuning Guide). There is a right way and a wrong way to reset the parameter - if the database instance is only using a pfile (init.ora) and not a spfile, removing the db_file_multiblock_read_count from the pfile is the proper way to reset this parameter. With an 8KB block size, you will likely find that Oracle will auto-set this parameter to 128. It is hard to say whether or not changing this parameter will improve overall performance. During testing here, I changed the parameter from 32 to a value auto-tuned by Oracle and found that full tablescans required roughly 1/8 as much time as before, but those results are likely not typical.

I would recommend determining why _newsort_enabled is set to FALSE, why _optim_peek_user_binds is set to FALSE (there may be a very good reason), why cursor_space_for_time is set to TRUE, why db_writer_processes is set to 7 (there may be a very good reason for not doing this), why optimizer_index_cost_adj is set to 5 (there is a very good reason for not doing this), and why resource_limit is set to TRUE. Please post your analysis of why those parameters are set to those values.

Regarding your guess about the single block reads, assume that during the 31 second time interval, an application was submitting a couple 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?

On this forum, as you show that you are making an effort to understand what is happening, and show the work that you have performed when analyzing a problem, you will find that more people will be willing to offer assistance. I have seen some of Sybrand's responses when people have demonstrated that they have attempted to work through their problem - and I have been impressed with the quality of those answers. Sybrand is providing a hint to you that you should show your work and show that you made an effort to solve the problem.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Nov 07 2008 - 06:57:32 CST