Prev: Materialized Views
Next: Certification matrix for Windows 7
From: vsevolod afanassiev on 13 Jan 2010 00:37
Hi,

Since we started running Statspack at level 7 I noticed that often
"Top 5 Wait Events" section does not match object statistics section.

For example:


Top 5 Timed Events
~~~~~~~~~~~~~~~~~~
% Total
Event Waits Time (s)
Ela Time
-------------------------------------------- ------------ -----------
--------
enqueue 4,818
13,538 57.12
CPU time
4,849 20.46
db file sequential read 1,650,718
3,044 12.84
db file scattered read 364,762
925 3.90
log file sync 61,011
587 2.48


So there were 4,818 enqueue waits with total duration of 13,538
seconds.

Looking on object statistics section of the same report:

Top 5 Row Lock Waits per Segment for DB: PRD1P Instance: PRD1P
Snaps: 52102 -5
-> End Segment Row Lock Waits Threshold: 100

Subobject Obj.
Owner Tablespace Object Name Name Type
---------- ---------- ------------------------------ ---------- -----
Row
Lock
Waits %Total
------------ -------
PRDCMDDL PR_INDEX_M PR_SVC_ORD_IDX01 INDEX
213 11.60
PRDCMDDL PR_INDEX_L PR_WORK_ORD_IDX01 INDEX
152 8.28
PRDCMDDL PR_INDEX_L PM_ACT_INST_IDX13 INDEX
94 5.12
PRDCMDDL PR_INDEX PR_ADDR_SUB_PREMISE_ULL_IDX01 INDEX
63 3.43
PRDCMDDL PR_INDEX_L TA_AUDIT_IDX02 INDEX
63 3.43

If 213 waits is 11.6% of total then total is approx 2,000 waits.

This doesn't match 4,818 waits reported in Top 5.

What do I miss here?
From: Jonathan Lewis on 13 Jan 2010 01:28
"vsevolod afanassiev" <vsevolod.afanassiev(a)gmail.com> wrote in message
news:6b53357e-dde5-42ad-91e7-e9bfc850d423(a)a32g2000yqm.googlegroups.com...
> Hi,
>
> Since we started running Statspack at level 7 I noticed that often
> "Top 5 Wait Events" section does not match object statistics section.
>
> For example:
>
>
> Top 5 Timed Events
> ~~~~~~~~~~~~~~~~~~
> % Total
> Event Waits Time (s)
> Ela Time
> -------------------------------------------- ------------ -----------
> --------
> enqueue 4,818
> 13,538 57.12
> CPU time
> 4,849 20.46
> db file sequential read 1,650,718
> 3,044 12.84
> db file scattered read 364,762
> 925 3.90
> log file sync 61,011
> 587 2.48
>
>
> So there were 4,818 enqueue waits with total duration of 13,538
> seconds.
>
> Looking on object statistics section of the same report:
>
> Top 5 Row Lock Waits per Segment for DB: PRD1P Instance: PRD1P
> Snaps: 52102 -5
> -> End Segment Row Lock Waits Threshold: 100
>
> Subobject Obj.
> Owner Tablespace Object Name Name Type
> ---------- ---------- ------------------------------ ---------- -----
> Row
> Lock
> Waits %Total
> ------------ -------
> PRDCMDDL PR_INDEX_M PR_SVC_ORD_IDX01 INDEX
> 213 11.60
> PRDCMDDL PR_INDEX_L PR_WORK_ORD_IDX01 INDEX
> 152 8.28
> PRDCMDDL PR_INDEX_L PM_ACT_INST_IDX13 INDEX
> 94 5.12
> PRDCMDDL PR_INDEX PR_ADDR_SUB_PREMISE_ULL_IDX01 INDEX
> 63 3.43
> PRDCMDDL PR_INDEX_L TA_AUDIT_IDX02 INDEX
> 63 3.43
>
> If 213 waits is 11.6% of total then total is approx 2,000 waits.
>
> This doesn't match 4,818 waits reported in Top 5.
>
> What do I miss here?


"Row lock waits" is just one class of enqueue waits.
Take a look at the enqueue stats, which will probably
identify where the difference comes from. Row lock waits
will be a subset of the TX waits.

There may also be some variation in counting - some stats
count each timeout as a new wait, others count only the
final acquisition of the lock.

--
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com



 | 
Pages: 1
Prev: Materialized Views
Next: Certification matrix for Windows 7