Missing DB time and enq:TM question;

From: Ashish Lunawat <ashish.lunawat_at_gmail.com>
Date: Wed, 29 Nov 2017 09:59:19 +0800
Message-ID: <CAEzAyeDno6BBnsCK=bdfSNBCSFQc-JrfZxBcykTcVXkxvafSfA_at_mail.gmail.com>

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

Received on Wed Nov 29 2017 - 02:59:19 CET