Re: AWR Sample Report

On Dec 19, 6:17 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> Kindly check the attached excel sheet.
> Since i am not able to post here, i am sending the output to your mail
> id.
>
> Query :
> select
>   dhse.event_name,
>   to_char(begin_interval_time,'YYYY_MM_DD HH24:MI'),
>   dhse.instance_number,
>   time_waited_micro - nvl(lag(time_waited_micro, 1, 0) over (partition
> by dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
> time_waited,
>   total_waits - nvl(lag(total_waits, 1, 0) over (partition by
> dhse.instance_number, dhse.event_name order by dhse.snap_id),0) as
> total_waits
> from dba_hist_snapshot dhs, dba_hist_system_event dhse
> where dhs.snap_id = dhse.snap_id
>   and dhs.instance_number = dhse.instance_number
> order by 2, 4;
>
> Kindly provide me your suggestions.
>
> With Regards,
> Raja.

As an experiment, I examined four time intervals for a single day, and posted those numbers below. "Wait/Sec" is a calculation where I divided the original TIME_WAITED value by 1,000,000 so that the wait time was stated in seconds, and then divided that value by 3,600 (there are 3,600 seconds in an hour, as the totals were over the course of an hour). The number calculated is the number of seconds waited per second, accumulated for all sessions/processes waiting on that event (that would explain the value of 9.52 below).

Between 2008_12_16 02:00 and 2008_12_16 03:00

EVENT_NAME                     TOTAL_WAITS  Wait/Sec
jobq slave wait                      1,394      1.05
db file sequential read            469,456      0.59
SQL*Net break/reset to client    9,236,797      0.35
SQL*Net more data from dblink      220,844      0.28
db file parallel write             260,297      0.17
Backup: sbtwrite2                   36,033      0.10
log file parallel write              8,771      0.06
log file sequential read             6,602      0.02
single-task message                    489      0.01
log file sync                        2,238      0.01
Log archive I/O                      7,783      0.00
RMAN backup & recovery I/O           5,302      0.00

Between 2008_12_16 07:00 and 2008_12_16 08:00
EVENT_NAME                     TOTAL_WAITS  Wait/Sec
PX Deq: Execution Msg               97,375      9.52
PX Idle Wait                        14,453      6.52
PX Deq Credit: send blkd         3,162,989      5.72
PX Deq: Table Q Normal          17,889,617      4.82
jobq slave wait                      4,112      3.20
db file scattered read             435,471      1.61
db file sequential read            881,400      0.69
PX Deq: Execute Reply               17,145      0.58
SQL*Net break/reset to client    7,065,596      0.37
direct path write temp              10,411      0.33
read by other session              705,527      0.26
Backup: sbtwrite2                   40,263      0.24
PX Deq Credit: need buffer         287,494      0.22
direct path read                 1,363,276      0.16
db file parallel write              20,396      0.13
PX qref latch                  204,808,981      0.10
direct path read temp               36,759      0.07
log file parallel write              9,155      0.04
single-task message                    516      0.01
log file sequential read             3,559      0.01
log file sync                        3,387      0.01
optimizer stats update retry           130      0.01
PX Deq: Msg Fragment                21,945      0.01
enq: TX - row lock contention          233      0.00
enq: MS - contention                     3      0.00

Between 2008_12_16 14:00 and 2008_12_16 15:00
EVENT_NAME                     TOTAL_WAITS  Wait/Sec
jobq slave wait                      3,792      2.95
db file sequential read          1,044,182      0.49
SQL*Net break/reset to client    9,797,440      0.33
Backup: sbtwrite2                   13,535      0.04
db file scattered read             152,387      0.03
read by other session               30,474      0.02
single-task message                    516      0.01
db file parallel write               3,351      0.00
log file parallel write              5,155      0.00
log file sync                        2,678      0.00

Between 2008_12_16 19:00 and 2008_12_16 20:00
EVENT_NAME                     TOTAL_WAITS  Wait/Sec
Backup: sbtwrite2                  352,599      3.74
jobq slave wait                      3,709      2.89
SQL*Net break/reset to client    8,473,325      0.36
db file sequential read            161,017      0.21
RMAN backup & recovery I/O          13,208      0.11
log file parallel write              7,232      0.03
db file parallel write              13,223      0.03
single-task message                    511      0.01
log file sequential read             3,066      0.01
log file sync                        2,695      0.00
read by other session                  972      0.00

The above may contain some "idle" wait events. What is possibly interesting is the consistently high number of waits on the event "SQL*Net break/reset to client", indicating that the client is submitting something, possibly a badly formed SQL statement, and the database instance is rejecting the message sent by the client. The time consumed seems to be a faily consistent 1/3 of a second per second in all four of the time intervals. You might check the per session statistics to see which sessions are involved.

I will delay my analysis of the above until you have had an opportunity to research and report your findings regarding what the above may be showing. I would encourage others viewing this thread to offer suggestions or an analysis of the above in the context of this thread.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Dec 19 2008 - 09:38:50 CST