Re: Discrepancy between Top 5 wait events and object statistics

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 13 Jan 2010 06:28:10 -0000
Message-ID: <5u2dncGYvtVn-tDWnZ2dnUVZ8vudnZ2d_at_bt.com>



"vsevolod afanassiev" <vsevolod.afanassiev_at_gmail.com> wrote in message news:6b53357e-dde5-42ad-91e7-e9bfc850d423_at_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
Received on Wed Jan 13 2010 - 00:28:10 CST

Original text of this message