Where did it go

From: Ram Raman <veeeraman_at_gmail.com>
Date: Tue, 17 Feb 2015 18:23:26 -0600
Message-ID: <CAHSa0M2AhD-wGm=eR_J768FORnGx3z4zLtV2KDdXUWobTtbwPw_at_mail.gmail.com>

List

Below is a copy and paste of a section of statspack from a DB on a v11.2 on windows. The DB Time of more than 17 mins compared to DB CPU of 2.4 mins got me scratching my head. I feel that it means there is lots of wait somewhere. But, the top most wait of dbf seq read is only about 7 mins. Where did the other 8 mins go?

---

Snapshot Snap Id Snap Time Sessions Curs/Sess Comment

~~~~~~~~    ---------- ------------------ -------- ---------
------------------
Begin Snap:       4132 16-Feb-15 22:00:27       54       9.1
  End Snap:       4143 16-Feb-15 23:00:27       51      10.1
   Elapsed:      60.00 (mins) Av Act Sess:       0.3
   DB time:      17.38 (mins)      DB CPU:       2.44 (mins)

Cache Sizes            Begin        End
~~~~~~~~~~~       ---------- ----------
    Buffer Cache:     3,696M              Std Block Size:         8K
     Shared Pool:       224M                  Log Buffer:    18,240K

Load Profile              Per Second    Per Transaction    Per Exec    Per
Call
~~~~~~~~~~~~      ------------------  ----------------- -----------
-----------
      DB time(s):                0.3                1.0        0.01
 0.01
       DB CPU(s):                0.0                0.1        0.00
 0.00
       Redo size:            7,804.9           26,016.4
   Logical reads:              522.3            1,741.0
   Block changes:               37.9              126.3
  Physical reads:               19.6               65.4
 Physical writes:                1.8                6.0
      User calls:               20.9               69.8
          Parses:                7.4               24.7
     Hard parses:                0.8                2.8
W/A MB processed:                1.4                4.6
          Logons:                0.0                0.1
        Executes:               38.0              126.8
       Rollbacks:                0.0                0.0
    Transactions:                0.3

Instance Efficiency Indicators

---

            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   96.29  Optimal W/A Exec %:  100.00
            Library Hit   %:   91.51        Soft Parse %:   88.83
         Execute to Parse %:   80.50         Latch Hit %:   99.99
Parse CPU to Parse Elapsd %:   91.35     % Non-Parse CPU:   96.31

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   82.91   82.79

    % SQL with executions>1: 79.32 85.25   % Memory for SQL w/exec>1: 86.04 88.77

Top 5 Timed Events                                                    Avg
%Total
~~~~~~~~~~~~~~~~~~                                                   wait
Call
Event                                            Waits    Time (s)   (ms)

Time

----------------------------------------- ------------ ----------- ------
------
db file sequential read                         45,696         430      9
76.9
CPU time                                                        86
 15.4
db file scattered read                             918          16     18
 2.9
log file parallel write                          2,442           8      3
 1.4
control file sequential read                     7,108           6      1
 1.2
          -------------------------------------------------------------
Host CPU  (CPUs: 2  Cores: 2  Sockets: 2)
~~~~~~~~              Load Average
                      Begin     End      User  System    Idle     WIO
WCPU
                    ------- -------   ------- ------- ------- -------
--------
                                         1.23    0.52   98.25

Instance CPU
~~~~~~~~~~~~                                       % Time (seconds)
                                            -------- --------------
                     Host: Total time (s):                  7,195.7
                  Host: Busy CPU time (s):                    125.8
                   % of time Host is Busy:       1.7
             Instance: Total CPU time (s):                    148.4
          % of Busy CPU used for Instance:     117.9
        Instance: Total Database time (s):                  1,069.3
  %DB time waiting for CPU (Resource Mgr):       0.0



*******************************************************************************

Thanks

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

Received on Wed Feb 18 2015 - 01:23:26 CET