Re: Missing DB time and enq:TM question;

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 29 Nov 2017 08:35:55 +0000
Message-ID: <LOXP123MB0086DAD23205BA9AAD616542A53B0_at_LOXP123MB0086.GBRP123.PROD.OUTLOOK.COM>

1)
A couple of comments:
It's statspack, not AWR, so there may be a couple of anomalies where an AWR report would give you different figures. Example: The "LGWR worker group idle" is a background idling event, so shouldn't appear in the foreground active list.

You have 20 Cores and 22 CPUs - that's very odd, CPUs is usually an integer multiple of Cores. Do you have an explicit setting for CPU_COUNT in your parameter file that is set for resource manager reasons ? Is it possible that you have hit an oddity with an accounting error because of this, or because of some CPU configuration anomaly at the O/S level.

I would always cross-check the DB CPU figure with the OS Stats CPU information, and with the Instance Activity "CPU used by this session, CPU used when session started" figures - they may reveal an interesting inconsistency. I would also examine the headline details on SQL ordered by CPU to see if the amount of CPU was hugely inconsistent with the DB CPU reported.

In this case (i.e. statspack) I would check the big numbers in the "idle waits" and "background waits" sections just in case a relevant foreground wait that made up the difference had been reported in the wrong category.

2) Please clarify the question. Is the current insert waiting to acquire a TM lock (and in what mode), or is a session that you believe has only done inserts into a table causing other sessions to wait, if so in what mode is the holder holding, and in what mode are the requesters waiting ? If the state is still live, post the extract from v$lock that shows the holders and (a few of the) waiters

Regards
Jonathan Lewis

---

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Ashish Lunawat <ashish.lunawat_at_gmail.com> Sent: 29 November 2017 01:59
To: oracle-l_at_freelists.org
Subject: Missing DB time and enq:TM question;

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 - 09:35:55 CET