Re: AWR Sample Report

Hi,

Thanks for guiding me Charles and Steve.

From the wait list given by Charles, i have tried to look into the wait event descriptions and made my following observations.

In general the wait list has been split into 4 parts : Part 1 -> 2am to 3am ; Part 2 -> 7am to 8am ; Part 3 -> 2pm to 3pm ; Part 4 -> 7pm to 8pm

First let us Neglect following the idle events from the wait list : jobq slave wait
PX Deq Credit: need buffer

PX Deq: Execute Reply
PX Deq: Execution Msg
PX Deq: Msg Fragment
PX Deq: Table Q Normal

In general - about the application analysis - the following 2 observations are :
a. if we see the no.of wait events, it looks like they are at : Part 2 -> 7am to 8am, which should be the peak hour of the transaction. b. the jobs, backup and recovery, archiving should be run when it is in less peak hour : Part 1 -> 2am to 3am, since there are the following events :
Backup: sbtwrite2 - ( Administrative - Wait class )              Log archive I/O                  
RMAN backup & recovery I/O.

With respect to wait events, the observations are as follows : c. The following wait events are due to parallel process - it is like running parallel queries, using parallel hints. PX Deq Credit: send blkd
PX qref latch
solution to solve this problem is (from web) : increasing the value of parallel_execution_message_size parameter. But I feel that reducing the usage of parallel hints will reduce this problem. but this would either cause not the efficient usage of the CPU or not finishing the query execution faster !!!

d. read by other session :
In previous versions this wait was classified under the "buffer busy waits" event. However, in Oracle 10.1 and higher this wait time is now broken out into the "read by other session" wait event. Excessive waits for this event are typically due to several processes repeatedly reading the same blocks, e.g. many sessions scanning the same index or performing full table scans on the same table. solution to solve this problem is (from web) :
->buffer busy waits / read by other session: Increase DB_CACHE_SIZE
(DB_BLOCK_BUFFERS prior to 9i)/ Analyze contention from SYS.V$BH But going through the link : http://www.confio.com/English/Tips/Read_By_Other_Session.php, i feel that tuning the queries would be the option to reduce this wait. Have to check for hot blocks/objects too ?

e.
In Oracle, we see two types of data block access - db file sequential read and db file scattered read :
->db file sequential read — A single-block read (i.e., index fetch by
ROWID).
A large number of waits here could indicate poor joining orders of tables, or unselective indexing. It is normal for this number to be large for a high-transaction, well-tuned system, but it can indicate problems in some circumstances.
They can also show up as direct path read/write waits.

->db file scattered read — A multiblock read (a full-table scan, OPQ,
sorting)
A large number here indicates that your table may have missing or suppressed indexes. Although it may be more efficient in your situation to perform a full table scan than an index scan, check to ensure that full table scans are necessary when you see these waits. Try to cache small tables to avoid reading them in over and over again
solution to solve this problem is (from web) : increase the RAM size of the db_cache_size to reduce disk I/O, tune the SQL to reduce disk I/ O, or invest in a faster disk I/O subsystem.

->log file sequential read - indicates that the process is waiting for
blocks to be read from the online redo log into memory. This primarily occurs at instance startup and when the ARCH process archives filled online redo logs.

->db file parallel write - occurs when the process, typically DBWR,
has issued multiple I/O requests in parallel to write dirty blocks from the buffer cache to disk, and is waiting for all requests to complete.
( dont know much - have to check regarding DBWR ) Looks like this is mostly due to i/o issue.

The direct path read, direct path write wait events are also associated with the data block access (db file sequential read and db file scattered read ) - dont know about these wait events, have to check regarding these.

Info : Starting in Oracle 10g release2, Oracle recommends not setting the db_file_multiblock_read_count parameter. solution to solve these problem are (from web) :
->db file sequential read: Tune SQL to do less I/O. Make sure all
objects are analyzed. Redistribute I/O across disks.

So, in general to reduce the following waits, we have to tune the sql queries (checking the indexes and joins) may give improvement in performance :
db file sequential read,
db file scattered read,
log file sequential read,
db file parallel write,
direct path read, and
direct path write

f. The following 3 wait events comes together, since they are related to LGWR/redo log ( dont know much - have to check regarding LGWR/redo log )
->log file sequential read - LGWR background process waits for this
event while it is copying redo records from the memory Log Buffer cache to the current redo group's member logfiles on disk.
->log file sync - User session waits on this wait event while waiting
for LGWR to post after commit write of dirty blocks  
->log file parallel write - Waiting for the writes of redo records to
the redo log files

solution to solve these problem are (from web) :
->log file sync: If this event is in the top 5, you are committing too
often (talk to your developers)                 
->log file parallel write: deals with flushing out the redo log buffer
to disk. Your disks may be too slow or you have an I/O bottleneck.

Conclusion from the above wait event list given by you : More waits are seen (db file sequential read, db file scattered read ), which are due to i/o's.
To reduce these wait events, have to tune the sql ( check index and joins).

Thanks. I have learnt something about the wait events.

Please check whether the above observations are correct ?

Also, Please provide your suggestions too...

With Regards,
Raja. Received on Tue Dec 23 2008 - 10:21:15 CST