Re: Missing DB time and enq:TM question;

Hi Ashish,

1. As you said, they are the top "5" events. And there are hundreds of other events.
2. An "insert append" will also require a lock at object level.

> Hi,
>
> In this statspack report the top 5 event accounts only for about 55% of
> the DB time. Any clue where is the remaining time gone?
>
> Database DB Id Instance Inst Num Startup Time Release RAC
> ~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
> 1 12.1.0.2.0 NO
>
> Host Name Platform CPUs Cores Sockets Memory
> (G)
> ~~~~ ---------------- ---------------------- ----- ----- -------
> ------------
> Solaris Operating Syst 22 20 2
> 128.0
>
> Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
> ~~~~~~~~ ---------- ------------------ -------- ---------
> ------------------
> Begin Snap: 9083 20-Nov-17 10:00:03 159 8.1
> End Snap: 9084 20-Nov-17 11:00:08 186 8.7
> Elapsed: 60.08 (mins) Av Act Sess: 10.6
> DB time: 635.94 (mins) DB CPU: 77.03 (mins)
>
> Cache Sizes Begin End
> ~~~~~~~~~~~ ---------- ----------
> Buffer Cache: 7,552M 7,456M Std Block Size: 8K
> Shared Pool: 2,240M 2,336M Log Buffer: 29,768K
>
> Load Profile Per Second Per Transaction Per Exec Per
> Call
> ~~~~~~~~~~~~ ------------------ ----------------- -----------
> -----------
> DB time(s): 10.6 0.4 0.01
> 0.01
> DB CPU(s): 1.3 0.1 0.00
> 0.00
> Redo size: 482,697.7 18,323.1
> Logical reads: 112,797.9 4,281.8
> Block changes: 3,588.2 136.2
> Physical reads: 1,654.0 62.8
> Physical writes: 197.9 7.5
> User calls: 1,845.5 70.1
> Parses: 732.2 27.8
> Hard parses: 8.0 0.3
> W/A MB processed: 59.3 2.3
> Logons: 0.2 0.0
> Executes: 1,174.5 44.6
> Rollbacks: 0.1 0.0
> Transactions: 26.3
>
> Instance Efficiency Indicators
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> Buffer Nowait %: 99.74 Redo NoWait %: 100.00
> Buffer Hit %: 98.88 Optimal W/A Exec %: 100.00
> Library Hit %: 99.05 Soft Parse %: 98.91
> Execute to Parse %: 37.66 Latch Hit %: 99.83
> Parse CPU to Parse Elapsd %: 8.71 % Non-Parse CPU: 97.64
>
> Shared Pool Statistics Begin End
> ------ ------
> Memory Usage %: 81.35 81.99
> % SQL with executions>1: 75.54 79.69
> % Memory for SQL w/exec>1: 90.89 92.14
>
> Top 5 Timed Events Avg
> %Total
> ~~~~~~~~~~~~~~~~~~ wait
> Call
> Event Waits Time (s) (ms)
> Time
> ----------------------------------------- ------------ ----------- ------
> ------
> db file sequential read 2,686,039 17,055 6
> 27.4
> log file sync 94,630 6,260 66
> 10.1
> CPU time 4,344
> 7.0
> LGWR worker group idle 83,645 4,190 50
> 6.7
> enq: TX - row lock contention 906 3,804 4199
> 6.1
> -------------------------------------------------------------
>
> Host CPU (CPUs: 22 Cores: 20 Sockets: 2)
> ~~~~~~~~ Load Average
> Begin End User System Idle WIO
> WCPU
> ------- ------- ------- ------- ------- -------
> --------
> 2.79 2.12 6.02 1.68 92.30 0.00
> 6.00
>
>
> Second question, how is it possible for an insert on table with no foreign
> keys on it cause a enq: TM lock wait ?
>
> Thanks
> Regards,
> Ashish
>

--
http://www.freelists.org/webpage/oracle-l