Re: AWR Sample Report
Date: Fri, 7 Nov 2008 01:31:50 -0800 (PST)
Message-ID: <742a0cf4-c07d-4787-8446-2afdef765344@r36g2000prf.googlegroups.com>
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.
- 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 ?.
- 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 ?
- 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 ?
- 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 00 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 0TOOLS /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 -
- optimizer_mode : should be changed from 'choose' to 'all_rows', correct ?
- 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.
Received on Fri Nov 07 2008 - 03:31:50 CST