Re: Missing DB time and enq:TM question;

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_at_gmail.com> 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_at_gmail.com
> > 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
>>
>
>

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