Is there some kind of a bench mark for the below mentioned Timed event observed in AWR report;--
Log file sync
db file sequemtial read
db file scattered read
All events represent a point where the database had to wait for something ... hence the name 'wait events'.
Are you looking for some magic number, below which you can say 'no problem' and above which you say 'urgent! this must be solved!'?
I have 3 ropes. One is braided nylon, one is twisted hemp, the third is polypropylene. Can you give me a benchmark on how long they should be?
You could be asking for "good" values for the *average* wait time (e.g. "how many milliseconds is a 'db file sequential read' wait acceptable ? 1ms or 2ms would generally mean a fetch from OS filesystem cache, 4ms to 10ms could mean disk i/o -- what would be unacceptable disk i/o performance ? 12ms ?)
Hemant K Chitale
So here is one of my AWR report snap during erroneous period. What am I suppose to infer from it then?
Top 5 Timed Foreground Events
|Event||Waits||Time(s)||Avg wait (ms)||% DB time||Wait Class|
|SQL*Net message from dblink||78,850||153||2||3.81||Network|
|db file sequential read||102,205||120||1||2.98||User I/O|
|row cache lock||79,603||63||1||1.56||Concurrency|
|log file sync||26,618||50||2||1.25||Commit|
Obviously, disk is not a problem. During the snapshot, for which you didn't post how long it lasted, Oracle spent less than 5 percent of the time waiting for disk.
The top 5 events account for roughly 40 percent of the time, so there is some other 60 percent nobody knows what is going on, and why this is an 'erroneous period'.
Also, as usual, you didn't mention 4 digit database version and platform, nor the number of CPUs, so really nothing can be derived from this, other than you DON'T have an IO problem.
Senior Oracle DBA
Disk I/O wait times are not an issue going by the Avg wait times reported. Also, they do not take any sigifcant time. Your database is CPU bound. Either you do not have enough CPU cores on the server AND/OR you have SQLs that are doing too many logical I/Os (reading the same blocks from the buffer cache repeatedly).
Hemant K Chitale
the numbers you posted could mean anything from "no problem at all" to "a total disaster", depending on the context. The context is determined by:
1) the length of the interval the report was taken for
2) the number of CPUs on the system
3) amount of CPU activity on the server by sources other than the database in question
But most importantly, you need to tell us what the problem was. If it was just one query, or one session, or one small group of sessions (pertaining to
a certain application or module) that was slow, then AWR isn't the right tool for the problem, and you may not be able to find any answers there.
ASH could be way more helpful (see ASH basics | Oracle Diagnostician for some useful ASH queries).
If the problem you're trying to investigate is global, then AWR might be the useful, but in any case numbers in there are meaningless without context,
so post the following sections of your report:
1) header (platform, version, RAC/standalone, duration of report etc.)
2) load profile
3) OS stats
4) time model statistics
You can also find some information about reading AWR reports with examples in my blog:
Here are some of the snaps of AWR that you have asked for.
|DB Name||DB Id||Instance||Inst num||Startup Time||Release||RAC|
|Host Name||Platform||CPUs||Cores||Sockets||Memory (GB)|
|cbbrdb22||AIX-Based Systems (64-bit)||16||4||40.00|
|Snap Id||Snap Time||Sessions||Cursors/Session|
|Begin Snap:||11960||17-Oct-13 11:00:38||1133||1.1|
|End Snap:||11961||17-Oct-13 11:30:02||1165||1.0|
|DB Time:||83.35 (mins)|
|Per Second||Per Transaction||Per Exec||Per Call|
|W/A MB processed:||4.1||0.1|
Instance Efficiency Percentages (Target 100%)
|Buffer Nowait %:||99.99||Redo NoWait %:||100.00|
|Buffer Hit %:||76.38||In-memory Sort %:||100.00|
|Library Hit %:||94.63||Soft Parse %:||73.06|
|Execute to Parse %:||46.17||Latch Hit %:||99.93|
|Parse CPU to Parse Elapsd %:||34.30||% Non-Parse CPU:||80.99|
|Statistic Name||Time (s)||% of DB Time|
|sql execute elapsed time||2,829.52||56.58|
|parse time elapsed||2,145.33||42.90|
|hard parse (sharing criteria) elapsed time||1,741.89||34.83|
|hard parse elapsed time||1,524.90||30.49|
|connection management call elapsed time||762.12||15.24|
|failed parse elapsed time||249.81||5.00|
|PL/SQL compilation elapsed time||144.77||2.89|
|PL/SQL execution elapsed time||73.42||1.47|
|hard parse (bind mismatch) elapsed time||0.96||0.02|
|repeated bind elapsed time||0.29||0.01|
|sequence load elapsed time||0.04||0.00|
|background elapsed time||443.61|
|background cpu time||159.20|