ASH Report For CDB10/cdb10

DB Name         DB Id    Instance     Inst Num Release     RAC Host
------------ ----------- ------------ -------- ----------- --- ------------
CDB10         1193559071 cdb10               1 10.2.0.1.0  NO  tsukuba

CPUs           SGA Size       Buffer Cache        Shared Pool    ASH Buffer Size
---- ------------------ ------------------ ------------------ ------------------
   2        440M (100%)         28M (6.4%)       128M (29.1%)        4.0M (0.9%)


          Analysis Begin Time:   31-Jul-07 17:52:21
            Analysis End Time:   31-Jul-07 18:07:21
                 Elapsed Time:        15.0 (mins)
                 Sample Count:       2,647
      Average Active Sessions:        2.94
  Avg. Active Session per CPU:        1.47
                Report Target:   None specified

Top User Events                  DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                                                               Avg Active
Event                               Event Class     % Activity   Sessions
----------------------------------- --------------- ---------- ----------
db file sequential read             User I/O             26.60       0.78
CPU + Wait for CPU                  CPU                   8.88       0.26
db file scattered read              User I/O              7.25       0.21
log file sync                       Commit                5.44       0.16
log buffer space                    Configuration         4.53       0.13
          -------------------------------------------------------------

Top Background Events            DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                                                               Avg Active
Event                               Event Class     % Activity   Sessions
----------------------------------- --------------- ---------- ----------
db file parallel write              System I/O           21.61       0.64
log file parallel write             System I/O           18.21       0.54
          -------------------------------------------------------------

Top Event P1/P2/P3 Values        DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

Event                          % Event  P1 Value, P2 Value, P3 Value % Activity
------------------------------ ------- ----------------------------- ----------
Parameter 1                Parameter 2                Parameter 3
-------------------------- -------------------------- --------------------------
db file sequential read          26.97             "201","66953","1"       0.11
file#                      block#                     blocks

db file parallel write           21.61          "3","0","2147483647"       3.21
requests                   interrupt                  timeout

                                                "2","0","2147483647"       2.49


                                                "5","0","2147483647"       2.42


log file parallel write          18.21                "1","2022","1"       0.68
files                      blocks                     requests

db file scattered read            7.37             "201","72065","8"       0.23
file#                      block#                     blocks

log file sync                     5.48                "4114","0","0"       0.30
buffer#                    NOT DEFINED                NOT DEFINED

          -------------------------------------------------------------

Top Service/Module               DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

Service        Module                   % Activity Action               % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$USERS      UNNAMED                       50.70 UNNAMED                 50.70
SYS$BACKGROUND UNNAMED                       41.56 UNNAMED                 41.56
cdb10          OEM.SystemPool                 2.64 UNNAMED                  1.47
                                                   XMLLoader0               1.17
SYS$USERS      sqlplus@tsukuba (TNS V1-       1.55 UNNAMED                  1.55
cdb10          Lab128                         1.36 UNNAMED                  1.36
          -------------------------------------------------------------

Top Client IDs                   DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top SQL Command Types            DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> 'Distinct SQLIDs' is the count of the distinct number of SQLIDs
      with the given SQL Command Type found over all the ASH samples
      in the analysis period

                                           Distinct            Avg Active
SQL Command Type                             SQLIDs % Activity   Sessions
---------------------------------------- ---------- ---------- ----------
INSERT                                           28      27.81       0.82
SELECT                                           45      12.73       0.37
UPDATE                                           11       3.85       0.11
DELETE                                            4       3.70       0.11
          -------------------------------------------------------------

Top SQL Statements              DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

       SQL ID    Planhash % Activity Event                             % Event
------------- ----------- ---------- ------------------------------ ----------
fd6a0p6333g8z  2993408006       7.59 db file sequential read              3.06
 SELECT trunc(SYSDATE, 'HH24') HOUR_STAMP, CM_ID, MA
X(SUBSTR(CM_DESC, 1, 12)) CM_DESC, MAX(UP_ID) UP_ID, MA
X(DOWN_ID) DOWN_ID, MAX(MAC_ID) MAC_ID, MAX(CMTS_
ID) CMTS_ID, SUM(BYTES_UP) SUM_BYTES_UP, SUM(BY

                                     direct path write temp               1.74

                                     db file scattered read               1.32

298wmz1kxjs1m  4251515144       5.25 CPU + Wait for CPU                   2.68
INSERT INTO CM_QOS_PROF SELECT :B1 , R.TOPOLOGYID, :B1 - :B4 , P.NODE_PROFILE_ID
, R.DOCSIFCMTSSERVICEQOSPROFILE FROM CM_SID_RAWDATA R, ( SELECT DISTINCT T.CMID,
 P.QOS_PROF_IDX, P.NODE_PROFILE_ID FROM TMP_TOP_SLOW_CM T, CMTS_QOS_PROF P WHERE
 T.CMTSID = P.TOPOLOGYID AND P.SECONDID = :B1 ) P WHERE R.BATCHID = :B3 AND R.PR

                                     db file sequential read              1.78

fhawr20n0wy5x  1792062018       3.40 db file sequential read              2.91
INSERT INTO TMP_CALC_HFC_SLOW_CM_TMP SELECT T.CMTSID, T.DOWNID, T.CMID, 0, 0, 0,
 T.DOWN_SNR_CNR_A3, T.DOWN_SNR_CNR_A2, T.DOWN_SNR_CNR_A1, T.DOWN_SNR_CNR_A0, R.S
YSUPTIME, R.DOCSIFSIGQUNERROREDS, R.DOCSIFSIGQCORRECTEDS, R.DOCSIFSIGQUNCORRECTA
BLES, R.DOCSIFSIGQSIGNALNOISE, :B3 , L.PREV_SECONDID, L.PREV_DOCSIFSIGQUNERRORED

3a11s4c86wdu5  1366293986       3.21 db file sequential read              1.85
DELETE FROM CM_RAWDATA WHERE BATCHID = 0 AND PROFINDX = :B1

                                     log buffer space                     1.06

998t5bbdfm5rm  1914870171       3.21 db file sequential read              1.70
INSERT INTO CM_RAWDATA SELECT PROFINDX, 0 BATCHID, TOPOLOGYID, SAMPLETIME, SYSUP
TIME, DOCSIFCMTSCMSTATUSVALUE, DOCSIFCMTSSERVICEINOCTETS, DOCSIFCMTSSERVICEOUTOC
TETS, DOCSIFCMSTATUSTXPOWER, DOCSIFCMTSCMSTATUSRXPOWER, DOCSIFDOWNCHANNELPOWER,
DOCSIFSIGQUNERROREDS, DOCSIFSIGQCORRECTEDS, DOCSIFSIGQUNCORRECTABLES, DOCSIFSIGQ

          -------------------------------------------------------------

Top SQL using literals           DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top Sessions                    DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> '# Samples Active' shows the number of ASH samples in which the session
      was found waiting for that particular event. The percentage shown
      in this column is calculated with respect to wall clock time
      and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
      the PQ slave activity into the session issuing the PQ. Refer to
      the 'Top Sessions running PQs' section for such statistics.

   Sid, Serial# % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
      126,    5      33.59 db file sequential read             18.62
STARGUS                                                 493/900 [ 55%]        4

                           CPU + Wait for CPU                   5.52
                                                        146/900 [ 16%]        2

                           db file scattered read               5.02
                                                        133/900 [ 15%]        2

      167,    1      21.80 db file parallel write              21.61
SYS                  oracle@tsukuba (DBW0)              572/900 [ 64%]        0

      166,    1      18.47 log file parallel write             18.21
SYS                  oracle@tsukuba (LGWR)              482/900 [ 54%]        0

      133,  763       9.67 db file sequential read              4.80
STARGUS                                                 127/900 [ 14%]        1

                           direct path write temp               1.74
                                                         46/900 [  5%]        0

                           db file scattered read               1.32
                                                         35/900 [  4%]        0

      152,  618       3.10 db file sequential read              1.10
STARGUS                                                  29/900 [  3%]        1

          -------------------------------------------------------------

Top Blocking Sessions            DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> Blocking session activity percentages are calculated with respect to
      waits on enqueues, latches and "buffer busy" only
-> '% Activity' represents the load on the database caused by
      a particular blocking session
-> '# Samples Active' shows the number of ASH samples in which the
      blocking session was found active.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
      when the blocking session was found active.

   Blocking Sid % Activity Event Caused                      % Event
--------------- ---------- ------------------------------ ----------
User                 Program                          # Samples Active     XIDs
-------------------- ------------------------------ ------------------ --------
      166,    1       5.48 log file sync                        5.48
SYS                  oracle@tsukuba (LGWR)              512/900 [ 57%]        0

          -------------------------------------------------------------

Top Sessions running PQs        DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Top DB Objects                   DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> With respect to Application, Cluster, User I/O and buffer busy waits only.

      Object ID % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
Object Name (Type)                                    Tablespace
----------------------------------------------------- -------------------------
          52652       4.08 db file scattered read               4.08
STARGUS.TMP_CALC_HFC_SLOW_CM_TMP (TABLE)              SYSTEM

          52543       3.32 db file sequential read              3.32
STARGUS.PK_CM_RAWDATA (INDEX)                         TS_STARGUS

          52698       3.21 db file sequential read              2.98
STARGUS.TMP_TOP_SLOW_CM (TABLE)                       SYSTEM

          52542       2.98 db file sequential read              2.98
STARGUS.CM_RAWDATA (TABLE)                            TS_STARGUS

          52699       1.78 db file sequential read              1.78
STARGUS.PK_TMP_TOP_SLOW_CM (INDEX)                    SYSTEM

          -------------------------------------------------------------

Top DB Files                     DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> With respect to Cluster and User I/O events only.

        File ID % Activity Event                             % Event
--------------- ---------- ------------------------------ ----------
File Name                                             Tablespace
----------------------------------------------------- -------------------------
              6      23.31 db file sequential read             19.83
/export/home/oracle10/oradata/cdb10/ts_stargus_01.dbf TS_STARGUS

                           db file scattered read               1.59


                           direct path write temp               1.59


          -------------------------------------------------------------

Top Latches                      DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)

                  No data exists for this section of the report.
          -------------------------------------------------------------

Activity Over Time              DB/Inst: CDB10/cdb10  (Jul 31 17:52 to 18:07)
-> Analysis period is divided into smaller time slots
-> Top 3 events are reported in each of those slots
-> 'Slot Count' shows the number of ASH samples in that slot
-> 'Event Count' shows the number of ASH samples waiting for
   that event in that slot
-> '% Event' is 'Event Count' over all ASH samples in the analysis period

                         Slot                                   Event
Slot Time (Duration)    Count Event                             Count % Event
-------------------- -------- ------------------------------ -------- -------
17:52:21   (1.7 min)      354 log file parallel write              85    3.21
                              db file sequential read              82    3.10
                              db file parallel write               65    2.46
17:54:00   (2.0 min)      254 CPU + Wait for CPU                   73    2.76
                              db file sequential read              46    1.74
                              log file parallel write              44    1.66
17:56:00   (2.0 min)      323 log file parallel write              94    3.55
                              db file parallel write               85    3.21
                              db file sequential read              85    3.21
17:58:00   (2.0 min)      385 log file parallel write             109    4.12
                              db file parallel write               95    3.59
                              db file sequential read              71    2.68
18:00:00   (2.0 min)      470 db file sequential read             169    6.38
                              db file parallel write               66    2.49
                              log file parallel write              61    2.30
18:02:00   (2.0 min)      277 db file sequential read             139    5.25
                              db file parallel write               58    2.19
                              CPU + Wait for CPU                   39    1.47
18:04:00   (2.0 min)      364 db file parallel write              105    3.97
                              db file scattered read               90    3.40
                              db file sequential read              80    3.02
18:06:00   (1.4 min)      220 db file parallel write               67    2.53
                              db file scattered read               44    1.66
                              db file sequential read              42    1.59
          -------------------------------------------------------------

End of Report