AWR Sample Report
in [Oracle]
|
From: sybrandb on 6 Nov 2008 17:08 On Thu, 6 Nov 2008 04:59:04 -0800 (PST), Charles Hooper <hooperc2000(a)yahoo.com> wrote: >Raja, it is good that you want to understand how to read AWR/Statspack >reports. I highly recommend reading the blog entries at the link that >I previously posted in this thread. Charles, please do not respond to this person. The only thing he wants is - avoid reading the documentatioin - free abstracts from the documentation as he can't be bothered to search for it - you doing HIS work for free. He is asking similar questions in various newsgroups, all of them boil down to him getting free consultancy. Regards, -- Sybrand Bakker Senior Oracle DBA
From: Charles Hooper on 6 Nov 2008 20:13 On Nov 6, 5:08 pm, sybra...(a)hccnet.nl wrote: > On Thu, 6 Nov 2008 04:59:04 -0800 (PST), Charles Hooper > > <hooperc2...(a)yahoo.com> wrote: > >Raja, it is good that you want to understand how to read AWR/Statspack > >reports. I highly recommend reading the blog entries at the link that > >I previously posted in this thread. > > Charles, please do not respond to this person. The only thing he wants > is > - avoid reading the documentatioin > - free abstracts from the documentation as he can't be bothered to > search for it > - you doing HIS work for free. > > He is asking similar questions in various newsgroups, all of them boil > down to him getting free consultancy. > > Regards, > > -- > Sybrand Bakker > Senior Oracle DBA Sybrand, I understand your point. It appears that Raja missed most of the clues I provided to him in my intial post. He did take the time to visit the link that I provided to him, where he posted the same AWR report to Jonathan's blog. If nothing else, I wonder how close my *guess* is to what is really happening in the 31 second time period on his system. While Raja might not benefit from the clues which have been provided to him in this thread, there is a chance that the clues might provide an opportunity to spark performance tuning curiosity in other readers. Out of personal curiosity, it might be interesting to see if there are any clues to point the possible cause of an apparent performance problem in a different direction. Raja, if you really want to understand what the AWR report is showing, take a look at the clues provided by Steve Howard, Sybrand, and me in this thread. Ask yourself why those clues were pointed out in response to the section of the AWR which you provided, and whether or not other clues exist in the full AWR report which agree with or refute the suggestions which were provided. Search the Internet for the meaning of the various wait events and statistics which have large values. Take this as an opportunity to learn about AWR/statspack reports. Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
From: raja on 7 Nov 2008 04:31 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. 1. I tried to guess with the "11,529", what you have mentioned. It is one of the 'Top 5 Timed Events' and its the first one, 'db file sequential read'. Is the problem with the index ? Should i increase the value of PGA AGGREGATE TARGET parameter ?. 2. Also, one of the other 'Top 5 Timed Events' is, 'Backup: sbtwrite2', which you have mentioned. Though it is one of the performance degrade, i think it cant be the actual problem, as it is listed last and any backup process will take time. Also, comparitively, 'db file sequential read' looks to be high. Comparing the above 2 values, Is this due more full table scans / proper indexes are not created ? 3. Execute to Parse %: 45.10 Parses: 8.93 25.45 Hard parses: 0.19 0.55 Comparing, Looks to be very less. Does this mean that, many sql are used, which are not used frequently ? 4. Looking at the data present in IO Status (Tablespace IO Stats and File IO Stats ), looks like there are more reads on Materialized Views. Should we try to tune those Materialized Views ? Tablespace IO Stats - ordered by IOs (Reads + Writes) desc Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) REPADMIN_MVIEWS 10,396 332 2.64 1.00 26 1 0 0.00 UNDO 8 0 2.50 1.00 1,530 49 0 0.00 PWMWI_IND 11 0 1.82 1.00 994 32 0 0.00 PWMWI_TAB 92 3 7.39 1.00 910 29 0 0.00 REPADMIN_IND 830 26 3.04 1.00 54 2 0 0.00 SYSAUX 189 6 7.35 1.00 1 0 0 0.00 TEMP 0 0 0.00 10 0 0 0.00 SYSTEM 4 0 7.50 1.00 1 0 0 0.00 PWM2FA_TAB 1 0 0.00 1.00 3 0 0 0.00 MVIEW_LOGS 1 0 0.00 1.00 1 0 0 0.00 PAMM42M_TAB 1 0 0.00 1.00 1 0 0 0.00 PAMSDFT01 1 0 0.00 1.00 1 0 0 0.00 PWM2FA_IND 1 0 0.00 1.00 1 0 0 0.00 REORG 1 0 0.00 1.00 1 0 0 0.00 TOOLS 1 0 0.00 1.00 1 0 0 0.00 File IO Stats ordered by Tablespace, File Tablespace Filename Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) MVIEW_LOGS /pamprd4_dbf/mview_logs_f01.dbf 1 0 0.00 1.00 1 0 0 0.00 PAMM42M_TAB /pamprd4_dbf/pamm42m_tab_f01.dbf 1 0 0.00 1.00 1 0 0 0.00 PAMSDFT01 /pamprd4_dbf/pamsdft01_f01.dbf 1 0 0.00 1.00 1 0 0 0.00 PWM2FA_IND /pamprd4_dbf/pwm2fa_ind_f01.dbf 1 0 0.00 1.00 1 0 0 0.00 PWM2FA_TAB /pamprd4_dbf/pwm2fa_tab_f01.dbf 1 0 0.00 1.00 3 0 0 0.00 PWMWI_IND /pamprd4_dbf/pwmwi_ind_f01.dbf 6 0 3.33 1.00 641 20 0 0.00 PWMWI_IND /pamprd4_dbf/pwmwi_ind_f02.dbf 5 0 0.00 1.00 353 11 0 0.00 PWMWI_TAB /pamprd4_dbf/pwmwi_tab_f01.dbf 22 1 5.00 1.00 451 14 0 0.00 PWMWI_TAB /pamprd4_dbf/pwmwi_tab_f02.dbf 70 2 8.14 1.00 459 15 0 0.00 REORG /pamprd4_dbf/reorg.dbf 1 0 0.00 1.00 1 0 0 0.00 REPADMIN_IND /pamprd4_dbf/repadmin_ind_f01.dbf 191 6 4.03 1.00 7 0 0 0.00 REPADMIN_IND /pamprd4_dbf/repadmin_ind_f02.dbf 551 18 1.72 1.00 32 1 0 0.00 REPADMIN_IND /pamprd4_dbf/repadmin_ind_f03.dbf 86 3 9.19 1.00 9 0 0 0.00 REPADMIN_IND /pamprd4_dbf/repadmin_ind_f04.dbf 2 0 5.00 1.00 6 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f01.dbf 754 24 3.26 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f02.dbf 646 21 2.60 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f03.dbf 1,121 36 2.52 1.00 7 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f04.dbf 616 20 2.31 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f05.dbf 457 15 3.79 1.00 4 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f06.dbf 1,096 35 2.27 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f07.dbf 260 8 2.92 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f08.dbf 737 24 3.15 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f09.dbf 1,209 39 2.74 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f10.dbf 767 24 2.28 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f11.dbf 836 27 3.18 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f12.dbf 1,019 33 1.72 1.00 2 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f13.dbf 369 12 2.76 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f14.dbf 398 13 2.21 1.00 1 0 0 0.00 REPADMIN_MVIEWS /pamprd4_dbf/repadmin_mviews_f15.dbf 111 4 3.60 1.00 2 0 0 0.00 SYSAUX /pamprd4_dbf/sysaux01.dbf 189 6 7.35 1.00 1 0 0 0.00 SYSTEM /pamprd4_dbf/system_f01.dbf 4 0 7.50 1.00 1 0 0 0.00 TEMP /pamprd4_dbf/temp_f01.dbf 0 0 10 0 0 TOOLS /pamprd4_dbf/tools_f01.dbf 1 0 0.00 1.00 1 0 0 0.00 UNDO /pamprd4_dbf/undo_f01.dbf 1 0 0.00 1.00 28 1 0 0.00 UNDO /pamprd4_dbf/undo_f02.dbf 1 0 0.00 1.00 36 1 0 0.00 UNDO /pamprd4_dbf/undo_f03.dbf 1 0 0.00 1.00 20 1 0 0.00 UNDO /pamprd4_dbf/undo_f04.dbf 1 0 0.00 1.00 40 1 0 0.00 UNDO /pamprd4_dbf/undo_f05.dbf 1 0 0.00 1.00 88 3 0 0.00 UNDO /pamprd4_dbf/undo_f06.dbf 1 0 20.00 1.00 1,276 41 0 0.00 UNDO /pamprd4_dbf/undo_f07.dbf 1 0 0.00 1.00 41 1 0 0.00 UNDO /pamprd4_dbf/undo_f08.dbf 1 0 0.00 1.00 1 0 0 0.00 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.
From: Charles Hooper on 7 Nov 2008 07:57 On Nov 7, 4:31 am, raja <dextersu...(a)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.
From: raja on 9 Nov 2008 11:26
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. b. 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. 2. _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 : a. not mess with the CBO explain plans b. to make CBO create proper plans with respect to bind variables 3. 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. 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 - 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. 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 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,..). 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. |