Steve,
Thanks for your reply.
1. As you said, they are the top "5" events. And there are hundreds of
other events.
[A] I tried to count the time spent in those other events and it`s barely
anything which makes me wonder why is it missing.
2. An "insert append" will also require a lock at object level.
[A] In my case the insert query do not contain append hint.
Thanks
Regards,
Ashish
On Wed, Nov 29, 2017 at 11:55 AM, Steven Heterogeneous <
steven.heterogeneous@xxxxxxxxx> wrote:
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.
Steven
On Wed, Nov 29, 2017 at 12:59 PM, Ashish Lunawat <ashish.lunawat@xxxxxxxxx
wrote:
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
