1 Reply Latest reply: Jul 15, 2013 7:57 AM by Nikolay Savvinov RSS

    Performance and tunning

    Faruk.m

      Hi,

       

      I am facing serious performance issue in my database my db getting slow day by day.i am not expert in performance and tunning.pls advise the best way to get rid of this.

       

      what i did to db.

       

      Gathered statistics.

      increased SGA size 48 GB to 60 GB.

      increased shared pool size

       

      Further i am advised to do table shrink.

       

      pls find below awr report ,report taken during the peak hour (1 hour).report take after above the activity.

       

      DB NameDB IdInstanceInst numReleaseRACHost
      110.2.0.4.0NO

       

       

      Snap IdSnap TimeSessionsCursors/Session
      Begin Snap:2169812-Jul-13 22:30:4395299.1
      End Snap:2169912-Jul-13 23:30:3191267.8
      Elapsed: 59.81 (mins)
      DB Time: 1,226.09 (mins)

       

       

      Report Summary

      Cache Sizes

       

      BeginEnd
      Buffer Cache:54,160M54,160MStd Block Size:8K
      Shared Pool Size:5,120M5,120MLog Buffer:79,764K

       

      Load Profile

       

      Per SecondPer Transaction
      Redo size:4,977,446.898,703.34
      Logical reads:74,430.12130.15
      Block changes:12,934.0622.62
      Physical reads:2,049.243.58
      Physical writes:690.931.21
      User calls:21,151.5636.98
      Parses:496.740.87
      Hard parses:0.380.00
      Sorts:2.960.01
      Logons:0.030.00
      Executes:12,059.5621.09
      Transactions:571.90

       

       

      % Blocks changed per Read:17.38Recursive Call %:3.19
      Rollback per transaction %:0.00Rows per Sort:205.03

       

      Instance Efficiency Percentages (Target 100%)

       

      Buffer Nowait %:99.98Redo NoWait %:99.99
      Buffer Hit %:97.25In-memory Sort %:100.00
      Library Hit %:100.05Soft Parse %:99.92
      Execute to Parse %:95.88Latch Hit %:99.33
      Parse CPU to Parse Elapsd %:2.17% Non-Parse CPU:100.00

       

      Shared Pool Statistics

       

      BeginEnd
      Memory Usage %:16.0217.00
      % SQL with executions>1:70.4680.92
      % Memory for SQL w/exec>1:80.1284.04

       

      Top 5 Timed Events

       

      EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait Class
      db file sequential read7,288,16754,841874.5User I/O
      CPU time 36,949 50.2
      log file sync2,055,3752,37513.2Commit
      ARCH wait on SENDREQ18,4121,642892.2Network
      db file parallel write608,47582611.1System I/O

       

      Main Report

       

      Back to Top

      Wait Events Statistics

      Back to Top

      Time Model Statistics

      • Total time in database user-calls (DB Time): 73565.2s
      • Statistics including the word "background" measure background process time, and so do not contribute to the DB time statistic
      • Ordered by % or DB time desc, Statistic name
      Statistic NameTime (s)% of DB Time
      sql execute elapsed time69,288.5794.19
      DB CPU36,948.8050.23
      PL/SQL execution elapsed time1,801.272.45
      parse time elapsed63.000.09
      repeated bind elapsed time27.110.04
      hard parse elapsed time7.150.01
      sequence load elapsed time3.270.00
      PL/SQL compilation elapsed time0.520.00
      hard parse (bind mismatch) elapsed time0.110.00
      hard parse (sharing criteria) elapsed time0.110.00
      connection management call elapsed time0.040.00
      DB time73,565.20
      background elapsed time4,469.44
      background cpu time418.34

      Back to Wait Events Statistics
      Back to Top

      Wait Class

      • s - second
      • cs - centisecond - 100th of a second
      • ms - millisecond - 1000th of a second
      • us - microsecond - 1000000th of a second
      • ordered by wait time desc, waits desc
      Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      User I/O7,353,6170.0055,14873.58
      Commit2,055,3750.002,37511.00
      Network93,713,9240.002,297045.66
      System I/O1,971,9910.001,99310.96
      Configuration1,8500.0050270.00
      Concurrency38,1303.514210.02
      Other138,9620.211100.07
      Application3270.00000.00

      Back to Wait Events Statistics
      Back to Top

      Wait Events

      • s - second
      • cs - centisecond - 100th of a second
      • ms - millisecond - 1000th of a second
      • us - microsecond - 1000000th of a second
      • ordered by wait time desc, waits desc (idle events last)
      EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      db file sequential read7,288,1670.0054,84183.55
      log file sync2,055,3750.002,37511.00
      ARCH wait on SENDREQ18,4120.001,642890.01
      db file parallel write608,4750.0082610.30
      log file sequential read18,6430.00597320.01
      SQL*Net more data to client16,306,1830.0051807.94
      log file parallel write1,300,3080.0050900.63
      read by other session27,5870.0215050.01
      db file scattered read17,9080.0013980.01
      SQL*Net message to client74,012,6220.0081036.06
      SQL*Net more data from client3,376,7070.005601.65
      log file switch completion1,7030.0050290.00
      Log archive I/O19,9800.004220.01
      enq: TX - index contention3,2090.002470.00
      cursor: pin S wait on X1,34399.6313100.00
      control file sequential read20,7220.001310.01
      direct path read15,5550.001110.01
      enq: CF - contention442.2781830.00
      direct path write4,3980.00610.00
      control file parallel write3,6350.00620.00
      buffer busy waits25,7800.00400.01
      LGWR wait for redo copy60,8450.00200.03
      os thread startup320.001330.00
      latch: cache buffers chains4,5420.00000.00
      latch free1650.00010.00
      cursor: pin S77,4760.00000.04
      latch: In memory undo latch3,1010.00000.00
      kksfbc child completion3100.000490.00
      log file single write2280.00000.00
      enq: SQ - contention1270.00000.00
      latch: library cache510.00010.00
      latch: row cache objects310.00010.00
      db file parallel read20.000100.00
      SQL*Net break/reset to client3270.00000.00
      latch: library cache pin250.00010.00
      latch: object queue header operation1160.00000.00
      latch: messages130.00010.00
      latch: redo writing130.00000.00
      enq: HW - contention70.00000.00
      latch: cache buffers lru chain30.00010.00
      latch: enqueue hash chains40.00000.00
      latch: redo allocation20.00010.00
      buffer deadlock28598.95000.00
      rdbms ipc reply50.00000.00
      latch: shared pool20.00000.00
      latch: session allocation10.00000.00
      cursor: mutex S140.00000.00
      SQL*Net message from client74,012,6190.00117,904236.06
      class slave wait7546.508,641114600.00
      ASM background timer2,0590.003,50317010.00
      jobq slave wait1,19896.663,50029220.00
      Streams AQ: qmn slave idle wait1280.003,484272150.00
      Streams AQ: qmn coordinator idle wait25750.193,483135540.00
      virtual circuit status119100.003,468291420.00
      Streams AQ: waiting for time management or cleanup tasks2070.001,829914580.00
      KSV master wait7000.29790.00

      Back to Wait Events Statistics
      Back to Top

      Background Wait Events

      • ordered by wait time desc, waits desc (idle events last)
      EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn
      ARCH wait on SENDREQ18,4120.001,642890.01
      db file parallel write608,4750.0082610.30
      log file sequential read18,6430.00597320.01
      log file parallel write1,300,3120.0050900.63
      Log archive I/O19,9800.004220.01
      control file sequential read19,9620.001310.01
      direct path read15,5550.001110.01
      events in waitclass Other61,0010.001000.03
      direct path write4,3890.00610.00
      control file parallel write3,6350.00620.00
      os thread startup320.001330.00
      db file sequential read660.00060.00
      log file single write2280.00000.00
      latch: redo writing130.00000.00
      latch: shared pool10.00000.00
      buffer busy waits30.00000.00
      latch: cache buffers chains30.00000.00
      rdbms ipc message3,799,4640.4968,046181.85
      smon timer1573.333,5672378090.00
      ASM background timer2,0590.003,50317010.00
      pmon timer1,19799.923,50129250.00
      Streams AQ: qmn slave idle wait1280.003,484272150.00
      Streams AQ: qmn coordinator idle wait25750.193,483135540.00
      Streams AQ: waiting for time management or cleanup tasks2070.001,829914580.00
      KSV master wait7000.29790.00

      Back to Wait Events Statistics
      Back to Top

      Operating System Statistics

      StatisticTotal
      NUM_LCPUS0
      NUM_VCPUS0
      AVG_BUSY_TIME149,514
      AVG_IDLE_TIME209,369
      AVG_IOWAIT_TIME31,870
      AVG_SYS_TIME14,269
      AVG_USER_TIME135,113
      BUSY_TIME8,977,590
      IDLE_TIME12,569,276
      IOWAIT_TIME1,919,144
      SYS_TIME863,628
      USER_TIME8,113,962
      LOAD0
      OS_CPU_WAIT_TIME9,317,800
      RSRC_MGR_CPU_WAIT_TIME0
      PHYSICAL_MEMORY_BYTES###############
      NUM_CPUS60
      NUM_CPU_CORES30

      Back to Wait Events Statistics
      Back to Top

      Service Statistics

      • ordered by DB Time
      Service NameDB Time (s)DB CPU (s)Physical ReadsLogical Reads
      EQMFPRDB73,547.7036,927.607,339,187266,622,822
      SYS$USERS1.900.803132,717
      EQMFPRDBXDB0.000.0000
      SYS$BACKGROUND0.000.0015,928130,593

      Back to Wait Events Statistics
      Back to Top

      Service Wait Class Stats

      • Wait Class info for services in the Service Statistics section.
      • Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
      • Time Waited (Wt Time) in centisecond (100th of a second)
      Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
      EQMFPRDB73330055512749380914128009369276665499
      SYS$USERS3131000000
      SYS$BACKGROUND205832009391050018412164179

      Back to Wait Events Statistics
      Back to Top

      SQL Statistics

      Back to Top

      SQL ordered by Elapsed Time

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      Elapsed Time (s)CPU Time (s)ExecutionsElap per Exec (s)% Total DB TimeSQL IdSQL ModuleSQL Text
      19,49510,4875,820,4190.0026.50476h6xk3spkvwtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      9,4422,7691,218,9520.0112.837zm3adrfb2xm3tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      9,1704,3741,251,0010.0112.471d8js9tmyy2hytSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2,9291,4742,424,5620.003.98d1d7svqcuggbftSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2,056454223,1640.012.79a350dbnj90v8sjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,917790223,1350.012.6138x9uk8t9fhqrtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,762542103,6250.022.39g51rraq75wx4vjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_PAYMENT_DUE...
      1,014278137,8340.011.385rqqqzgvkfb9ctSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,004463137,8790.011.36ck64hdnv2fmk1tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      941377106,7650.011.2859aa1s0m76dh9tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      9255969,0870.011.268a9hpm966b6najsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      898541536,9130.001.220s0ywwtg2fxshjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      87418234,5380.031.194xufj2aawdzdkjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_LD_L000 d USIN...
      865204106,7590.011.183apgv3suvuq58jsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      786783533,1230.001.075v35vpkcg4wyzjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_BALANCES d ...
      7676385,721,7810.001.04dmxatw70zd80jtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM FBNK_L...
      763307440,1030.001.045qwjkv3n6m1yfjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...

      Back to SQL Statistics
      Back to Top

      SQL ordered by CPU Time

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      CPU Time (s)Elapsed Time (s)ExecutionsCPU per Exec (s)% Total DB TimeSQL IdSQL ModuleSQL Text
      10,48719,4955,820,4190.0026.50476h6xk3spkvwtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      4,3749,1701,251,0010.0012.471d8js9tmyy2hytSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2,7699,4421,218,9520.0012.837zm3adrfb2xm3tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,4742,9292,424,5620.003.98d1d7svqcuggbftSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      7901,917223,1350.002.6138x9uk8t9fhqrtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      783786533,1230.001.075v35vpkcg4wyzjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_BALANCES d ...
      6387675,721,7810.001.04dmxatw70zd80jtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM FBNK_L...
      5421,762103,6250.012.39g51rraq75wx4vjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_PAYMENT_DUE...
      541898536,9130.001.220s0ywwtg2fxshjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      5135361,197,8110.000.733yhkww3ru5cvmjsh@equitas.pr1 (TNS V1-V3)MERGE INTO F_JOB_LIST_33 d USI...
      4631,004137,8790.001.36ck64hdnv2fmk1tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      4542,056223,1640.002.79a350dbnj90v8sjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      377941106,7650.001.2859aa1s0m76dh9tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      307763440,1030.001.045qwjkv3n6m1yfjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2781,014137,8340.001.385rqqqzgvkfb9ctSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      204865106,7590.001.183apgv3suvuq58jsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      18287434,5380.011.194xufj2aawdzdkjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_LD_L000 d USIN...
      5992569,0870.001.268a9hpm966b6najsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...

      Back to SQL Statistics
      Back to Top

      SQL ordered by Gets

      • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
      • Total Buffer Gets: 267,111,630
      • Captured SQL account for 62.9% of Total
      Buffer GetsExecutionsGets per Exec%TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      29,797,2305,820,4195.1211.1610486.7819494.68476h6xk3spkvwtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      22,842,6565,721,7813.998.55637.99767.22dmxatw70zd80jtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM FBNK_L...
      13,342,2591,197,81111.145.00512.91535.933yhkww3ru5cvmjsh@equitas.pr1 (TNS V1-V3)MERGE INTO F_JOB_LIST_33 d USI...
      11,822,587103,625114.094.43541.851761.72g51rraq75wx4vjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_PAYMENT_DUE...
      10,987,8332,424,5624.534.111473.542929.33d1d7svqcuggbftSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      7,948,4712,429,3413.272.98241.89241.932c5ahv1sh642ktSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      7,096,79820,993338.062.66161.85713.51gsyqdnhuax1vjtSS@equitas.pr1 (TNS V1-V3)MERGE INTO FTN0_LD_L000 d USIN...
      6,925,3521,218,9525.682.592768.679441.867zm3adrfb2xm3tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      6,489,3272,171,0932.992.43141.27141.304cupapb1ru3ajjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM F_BATC...
      6,097,2081,251,0014.872.284373.619170.321d8js9tmyy2hytSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      4,965,653533,1239.311.86783.21785.855v35vpkcg4wyzjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_BALANCES d ...
      4,510,66134,538130.601.69181.98873.854xufj2aawdzdkjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_LD_L000 d USIN...
      3,515,23659,89858.691.32276.94438.8870ng60744nwkutSS@equitas.pr1 (TNS V1-V3)MERGE INTO FTN0_PD_PAYMENT_DUE...
      3,043,08112,089251.721.1487.68396.18974w7y41jp8rktSS@equitas.pr1 (TNS V1-V3)MERGE INTO FRJ0_LD_L000 d USIN...

      Back to SQL Statistics
      Back to Top

      SQL ordered by Reads

      • Total Disk Reads: 7,354,234
      • Captured SQL account for 95.3% of Total
      Physical ReadsExecutionsReads per Exec%TotalCPU Time (s)Elapsed Time (s)SQL IdSQL ModuleSQL Text
      2,856,0505,820,4190.4938.8410486.7819494.68476h6xk3spkvwtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,132,9861,218,9520.9315.412768.679441.867zm3adrfb2xm3tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,046,7471,251,0010.8414.234373.619170.321d8js9tmyy2hytSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      310,2662,424,5620.134.221473.542929.33d1d7svqcuggbftSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      224,373223,1641.013.05454.462056.00a350dbnj90v8sjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      199,479223,1350.892.71789.691916.8638x9uk8t9fhqrtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      119,128137,8340.861.62278.131013.985rqqqzgvkfb9ctSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      115,638137,8790.841.57463.001003.89ck64hdnv2fmk1tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      98,473106,7650.921.34377.28940.5659aa1s0m76dh9tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      94,036536,9130.181.28541.02897.800s0ywwtg2fxshjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      90,357106,7590.851.23204.33865.293apgv3suvuq58jsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      89,401103,6250.861.22541.851761.72g51rraq75wx4vjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_PAYMENT_DUE...
      89,09269,0871.291.2159.32925.418a9hpm966b6najsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      75,448440,1030.171.03307.47762.895qwjkv3n6m1yfjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...

      Back to SQL Statistics
      Back to Top

      SQL ordered by Executions

      • Total Executions: 43,278,815
      • Captured SQL account for 66.3% of Total
      ExecutionsRows ProcessedRows per ExecCPU per Exec (s)Elap per Exec (s)SQL IdSQL ModuleSQL Text
      5,820,4195,786,0080.990.000.00476h6xk3spkvwtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      5,721,7815,705,8401.000.000.00dmxatw70zd80jtSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM FBNK_L...
      2,429,3412,419,6551.000.000.002c5ahv1sh642ktSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2,424,5622,422,0551.000.000.00d1d7svqcuggbftSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      2,171,0932,159,7280.990.000.004cupapb1ru3ajjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM F_BATC...
      1,251,0011,218,7840.970.000.011d8js9tmyy2hytSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,218,9521,217,7221.000.000.017zm3adrfb2xm3tSS@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      1,197,8111,182,4890.990.000.003yhkww3ru5cvmjsh@equitas.pr1 (TNS V1-V3)MERGE INTO F_JOB_LIST_33 d USI...
      1,196,6293,5390.000.000.006xph1vmcr46tfjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FTN0_LMM_HIS...
      536,913536,5911.000.000.000s0ywwtg2fxshjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...
      533,123522,9100.980.000.005v35vpkcg4wyzjsh@equitas.pr1 (TNS V1-V3)MERGE INTO FAP0_PD_BALANCES d ...
      440,103439,7551.000.000.005qwjkv3n6m1yfjsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD.getClobVal(...

      Back to SQL Statistics
      Back to Top

      SQL ordered by Parse Calls

      • Total Parse Calls: 1,782,687
      • Captured SQL account for 98.3% of Total
      Parse CallsExecutions% Total ParsesSQL IdSQL ModuleSQL Text
      1,191,3441,196,62966.836xph1vmcr46tfjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FTN0_LMM_HIS...
      214,258214,69712.024nd2aq0rtbb0fjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FMH0_LMM_HIS...
      125,377125,7407.031b9817gnwzfjcjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FRJ0_LMM_HIS...
      104,700104,9745.87f9nswt61c2yg4jsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FMP0_LMM_HIS...
      55,95756,0603.143d69mnc8aj9ywjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FGJ0_LMM_HIS...
      34,37034,5341.938ybmhpr54wm5cjsh@equitas.pr1 (TNS V1-V3)SELECT RECID FROM FAP0_LMM_HIS...
      4,6564,6560.264m7m0t6fjcs5x update seq$ set increment$=:2,...
      2,4872,4870.142ym6hhaq30r73 select type#, blocks, extents,...
      1,69235,7880.0904dnjk5z43bv3jsh@equitas.pr1 (TNS V1-V3)SELECT x.XMLRECORD FROM VOC x ...
      1,0721,0720.06bsa0wjtftg3uw select file# from file$ where ...

      Back to SQL Statistics
      Back to Top

      SQL ordered by Sharable Memory

      No data exists for this section of the report.Back to SQL Statistics
      Back to Top

      SQL ordered by Version Count

      No data exists for this section of the report.Back to SQL Statistics
      Back to Top

      Complete List of SQL Text

      SQL IdSQL Text
      04dnjk5z43bv3SELECT x.XMLRECORD FROM VOC x WHERE RECID = :REC
      0s0ywwtg2fxshSELECT x.XMLRECORD.getClobVal() FROM FAP0_PD_BALANCES x WHERE RECID = :REC
      1b9817gnwzfjcSELECT RECID FROM FRJ0_LMM_HISTORY_TODAY WHERE RECID = :REC
      1d8js9tmyy2hySELECT x.XMLRECORD.getClobVal() FROM FTN0_LD_L000 x WHERE RECID = :REC
      2c5ahv1sh642kSELECT x.XMLRECORD.getClobVal() FROM F_LOCKING x WHERE RECID = :REC
      2ym6hhaq30r73select type#, blocks, extents, minexts, maxexts, extsize, extpct, user#, iniexts, NVL(lists, 65535), NVL(groups, 65535), cachehint, hwmincr, NVL(spare1, 0), NVL(scanhint, 0) from seg$ where ts#=:1 and file#=:2 and block#=:3
      38x9uk8t9fhqrSELECT x.XMLRECORD.getClobVal() FROM FMH0_LD_L000 x WHERE RECID = :REC
      3apgv3suvuq58SELECT x.XMLRECORD.getClobVal() FROM FMP0_LMM_000 x WHERE RECID = :REC
      3d69mnc8aj9ywSELECT RECID FROM FGJ0_LMM_HISTORY_TODAY WHERE RECID = :REC
      3yhkww3ru5cvmMERGE INTO F_JOB_LIST_33 d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=:XMLRECORD WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, :XMLRECORD )
      476h6xk3spkvwSELECT x.XMLRECORD.getClobVal() FROM FBNK_CUSTOMER x WHERE RECID = :REC
      4cupapb1ru3ajSELECT x.XMLRECORD FROM F_BATCH_STATUS x WHERE RECID = :REC
      4m7m0t6fjcs5xupdate seq$ set increment$=:2, minvalue=:3, maxvalue=:4, cycle#=:5, order$=:6, cache=:7, highwater=:8, audit$=:9, flags=:10 where obj#=:1
      4nd2aq0rtbb0fSELECT RECID FROM FMH0_LMM_HISTORY_TODAY WHERE RECID = :REC
      4xufj2aawdzdkMERGE INTO FAP0_LD_L000 d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )
      59aa1s0m76dh9SELECT x.XMLRECORD.getClobVal() FROM FMP0_LD_L000 x WHERE RECID = :REC
      5qwjkv3n6m1yfSELECT x.XMLRECORD.getClobVal() FROM FMH0_EB_C004 x WHERE RECID = :REC
      5rqqqzgvkfb9cSELECT x.XMLRECORD.getClobVal() FROM FRJ0_LMM_000 x WHERE RECID = :REC
      5v35vpkcg4wyzMERGE INTO FAP0_PD_BALANCES d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )
      6xph1vmcr46tfSELECT RECID FROM FTN0_LMM_HISTORY_TODAY WHERE RECID = :REC
      70ng60744nwkuMERGE INTO FTN0_PD_PAYMENT_DUE d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )
      7zm3adrfb2xm3SELECT x.XMLRECORD.getClobVal() FROM FTN0_LMM_000 x WHERE RECID = :REC
      8a9hpm966b6naSELECT x.XMLRECORD.getClobVal() FROM FAP0_PD_PAYMENT_DUE x WHERE RECID = :REC
      8ybmhpr54wm5cSELECT RECID FROM FAP0_LMM_HISTORY_TODAY WHERE RECID = :REC
      974w7y41jp8rkMERGE INTO FRJ0_LD_L000 d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )
      a350dbnj90v8sSELECT x.XMLRECORD.getClobVal() FROM FMH0_LMM_000 x WHERE RECID = :REC
      bsa0wjtftg3uwselect file# from file$ where ts#=:1
      ck64hdnv2fmk1SELECT x.XMLRECORD.getClobVal() FROM FRJ0_LD_L000 x WHERE RECID = :REC
      d1d7svqcuggbfSELECT x.XMLRECORD.getClobVal() FROM FTN0_EB_C004 x WHERE RECID = :REC
      dmxatw70zd80jSELECT x.XMLRECORD FROM FBNK_LIMIT_LIABILITY x WHERE RECID = :REC
      f9nswt61c2yg4SELECT RECID FROM FMP0_LMM_HISTORY_TODAY WHERE RECID = :REC
      g51rraq75wx4vMERGE INTO FAP0_PD_PAYMENT_DUE d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )
      gsyqdnhuax1vjMERGE INTO FTN0_LD_L000 d USING DUAL ON (DUAL.dummy IS NOT NULL AND d.RECID=:REC) WHEN MATCHED THEN UPDATE SET XMLRECORD=XMLTYPE(:XMLRECORD, NULL, 1, 1) WHEN NOT MATCHED THEN INSERT (RECID, XMLRECORD) VALUES(:REC, XMLTYPE(:XMLRECORD, NULL, 1, 1) )

      Back to SQL Statistics
      Back to Top

      Instance Activity Statistics

      Back to Top

      Instance Activity Stats

      StatisticTotalper Secondper Trans
      CPU used by this session210,79458.740.10
      CPU used when call started210,49158.650.10
      CR blocks created1,507,327420.010.73
      Cached Commit SCN referenced9,7202.710.00
      Commit SCN cached1620.050.00
      DB time7,929,9352,209.663.86
      DBWR checkpoint buffers written2,474,944689.641.21
      DBWR checkpoints380.010.00
      DBWR object drop buffers written00.000.00
      DBWR thread checkpoint buffers written1,085,847302.570.53
      DBWR transaction table writes1,0450.290.00
      DBWR undo block writes1,002,391279.310.49
      IMU CR rollbacks127,49735.530.06
      IMU Flushes1,861,095518.590.91
      IMU Redo allocation size7,176,832,2561,999,810.033,496.78
      IMU commits50,62114.110.02
      IMU contention12,0173.350.01
      IMU ktichg flush1,784,448497.230.87
      IMU pool not allocated140,77439.230.07
      IMU recursive-transaction flush80.000.00
      IMU undo allocation size8,522,208,2882,374,696.394,152.29
      IMU- failed to get a private strand140,77439.230.07
      SMON posted for undo segment shrink00.000.00
      SQL*Net roundtrips to/from client74,161,70420,665.0136.13
      active txn count during cleanout869,429242.260.42
      application wait time10.000.00
      background checkpoints completed420.010.00
      background checkpoints started380.010.00
      background timeouts21,0965.880.01
      branch node splits170.000.00
      buffer is not pinned count76,771,72121,392.2937.41
      buffer is pinned count541,674150.940.26
      bytes received via SQL*Net from client24,693,065,4746,880,673.5812,031.23
      bytes sent via SQL*Net to client68,299,866,01119,031,621.8233,277.82
      calls to get snapshot scn: kcmgss201,823,02656,237.5898.33
      calls to kcmgas5,373,6621,497.362.62
      calls to kcmgcs204,30856.930.10
      change write time30,6458.540.01
      cleanout - number of ktugct calls447,827124.790.22
      cleanouts and rollbacks - consistent read gets311,79586.880.15
      cleanouts only - consistent read gets22,5386.280.01
      cluster key scan block gets27,1617.570.01
      cluster key scans13,0243.630.01
      commit batch/immediate performed7690.210.00
      commit batch/immediate requested7690.210.00
      commit cleanout failures: buffer being written660.020.00
      commit cleanout failures: callback failure32,7749.130.02
      commit cleanout failures: cannot pin11,1063.090.01
      commit cleanouts6,256,7701,743.443.05
      commit cleanouts successfully completed6,212,8241,731.193.03
      commit immediate performed7690.210.00
      commit immediate requested7690.210.00
      commit txn count during cleanout175,20748.820.09
      concurrency wait time4,2401.180.00
      consistent changes14,720,7974,101.927.17
      consistent gets197,877,79155,138.2596.41
      consistent gets - examination141,210,56239,348.0468.80
      consistent gets direct00.000.00
      consistent gets from cache197,877,79355,138.2596.41
      cursor authentications4810.130.00
      data blocks consistent reads - undo records applied2,923,592814.651.42
      db block changes46,417,18312,934.0622.62
      db block gets69,233,86319,291.8833.73
      db block gets direct2210.060.00
      db block gets from cache69,233,64219,291.8233.73
      deferred (CURRENT) block cleanout applications2,946,135820.931.44
      dirty buffers inspected270.010.00
      enqueue conversions2,7240.760.00
      enqueue releases5,533,0951,541.792.70
      enqueue requests5,637,1581,570.782.75
      enqueue timeouts104,17729.030.05
      enqueue waits3,3430.930.00
      exchange deadlocks2850.080.00
      execute count43,278,81512,059.5621.09
      failed probes on index block reclamation2020.060.00
      free buffer inspected9,329,4852,599.644.55
      free buffer requested13,655,0703,804.966.65
      heap block compress818,408228.050.40
      hot buffers moved to head of LRU2,012,811560.870.98
      immediate (CR) block cleanout applications334,33393.160.16
      immediate (CURRENT) block cleanout applications358,08599.780.17
      index crx upgrade (found)00.000.00
      index crx upgrade (positioned)7,4212.070.00
      index fetch by key43,050,88111,996.0420.98
      index scans kdiixs1580,080161.640.28
      leaf node 90-10 splits4610.130.00
      leaf node splits5,0631.410.00
      lob reads26,816,0287,472.2313.07
      lob writes7,404,2912,063.193.61
      lob writes unaligned7,273,5922,026.773.54
      logons cumulative930.030.00
      messages received4,707,4591,311.722.29
      messages sent4,707,4591,311.722.29
      no buffer to keep pinned count00.000.00
      no work - consistent read gets16,602,4004,626.238.09
      opened cursors cumulative1,785,322497.480.87
      parse count (failures)00.000.00
      parse count (hard)1,3810.380.00
      parse count (total)1,782,687496.740.87
      parse time cpu1170.030.00
      parse time elapsed5,3911.500.00
      physical read IO requests7,320,5012,039.843.57
      physical read bytes60,245,884,92816,787,396.0129,353.67
      physical read total IO requests7,378,2442,055.933.59
      physical read total bytes98,621,528,57627,480,692.7848,051.48
      physical read total multi block requests54,25515.120.03
      physical reads7,354,2342,049.243.58
      physical reads cache7,338,6782,044.913.58
      physical reads cache prefetch33,7599.410.02
      physical reads direct15,5564.330.01
      physical reads direct temporary tablespace00.000.00
      physical reads prefetch warmup00.000.00
      physical write IO requests753,184209.870.37
      physical write bytes20,312,727,5525,660,101.139,896.99
      physical write total IO requests5,634,2551,569.972.75
      physical write total bytes95,922,043,90426,728,486.7446,736.21
      physical write total multi block requests4,431,6371,234.872.16
      physical writes2,479,581690.931.21
      physical writes direct4,6091.280.00
      physical writes direct (lob)00.000.00
      physical writes from cache2,474,972689.651.21
      physical writes non checkpoint2,029,478565.510.99
      pinned buffers inspected1,0150.280.00
      prefetch warmup blocks aged out before use00.000.00
      process last non-idle time00.000.00
      recursive aborts on index block reclamation20.000.00
      recursive calls2,501,381697.001.22
      recursive cpu usage7710.210.00
      redo blocks written36,865,14410,272.4017.96
      redo buffer allocation retries1,3720.380.00
      redo entries15,823,7034,409.247.71
      redo log space requests1,8020.500.00
      redo log space wait time5,1331.430.00
      redo ordering marks878,021244.660.43
      redo size17,862,847,3644,977,446.898,703.34
      redo subscn max counts1,428,947398.170.70
      redo synch time278,00377.460.14
      redo synch writes2,052,974572.061.00
      redo wastage415,015,052115,643.12202.21
      redo write time79,04122.020.04
      redo writer latching time2230.060.00
      redo writes1,300,928362.500.63
      rollback changes - undo records applied3,0820.860.00
      rollbacks only - consistent read gets1,192,762332.360.58
      rows fetched via callback31,971,7298,908.8615.58
      session connect time00.000.00
      session cursor cache hits1,754,479488.880.85
      session logical reads267,111,63074,430.12130.15
      session pga memory max81,805,29622,794.8839.86
      session uga memory###############74,104,486.90129,575.71
      session uga memory max113,551,45631,640.8955.33
      shared hash latch upgrades - no wait7,238,0392,016.873.53
      shared hash latch upgrades - wait7590.210.00
      sorts (memory)10,6302.960.01
      sorts (rows)2,179,489607.311.06
      sql area evicted00.000.00
      sql area purged670.020.00
      summed dirty queue length330.010.00
      switch current to new buffer606,344168.960.30
      table fetch by rowid36,451,73110,157.2017.76
      table fetch continued row4,200,2861,170.402.05
      table scan blocks gotten4,763,1821,327.252.32
      table scan rows gotten6,694,4151,865.393.26
      table scans (long tables)00.000.00
      table scans (short tables)4,710,4691,312.562.30
      total number of times SMON posted40.000.00
      transaction rollbacks7690.210.00
      transaction tables consistent read rollbacks400.010.00
      transaction tables consistent reads - undo records applied16,4904.590.01
      undo change vector size6,873,916,3401,915,403.123,349.19
      user I/O wait time5,513,9831,536.462.69
      user calls75,907,80121,151.5636.98
      user commits2,052,414571.901.00
      user rollbacks00.000.00
      workarea executions - optimal6,7931.890.00
      write clones created in background30.000.00
      write clones created in foreground1860.050.00

      Back to Instance Activity Statistics
      Back to Top

      Instance Activity Stats - Absolute Values

      • Statistics with absolute values (should not be diffed)
      StatisticBegin ValueEnd Value
      session cursor cache count63,22882,533
      opened cursors current28,41724,373
      logons current9591

      Back to Instance Activity Statistics
      Back to Top

      Instance Activity Stats - Thread Activity

      • Statistics identified by '(derived)' come from sources other than SYSSTAT
      StatisticTotalper Hour
      log switches (derived)3838.12

      Back to Instance Activity Statistics
      Back to Top

      IO Stats

      Back to Top

      Tablespace IO Stats

      • ordered by IOs (Reads + Writes) desc
      TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
      T24DATAXML7,218,3652,0117.521.00582,43616234,3753.09
      T24INDEXXML102,017286.731.00103,2032915,0123.45
      UNDOTBS117900.001.0069,095193,9600.06
      SYSAUX50404.701.00630000.00
      SYSTEM30405.791.00164000.00
      EXAMPLE2800.001.0028000.00
      USERS2800.001.0028000.00

      Back to IO Stats
      Back to Top

      File IO Stats

      • ordered by Tablespace, File
      TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
      EXAMPLE+ASMPRDDB/eqmfprdb/datafile/example.363.7935891412800.001.0028000.00
      SYSAUX+ASMPRDDB/eqmfprdb/datafile/sysaux.360.79358911550404.701.00630000.00
      SYSTEM+ASMPRDDB/eqmfprdb/datafile/system.365.79358923930405.791.00164000.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.256.79357175183,474237.691.013,93111677.43
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.257.79357175190,222257.301.013,78411766.82
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.258.79357171574,212217.291.005,11413272.97
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.259.79357172789,401257.181.014,80012006.45
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.260.79357174190,619257.471.014,09311967.14
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.261.79357173990,550257.421.007,14722476.11
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.262.79357171169,804197.471.004,97114482.03
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.263.79357173987,234247.701.018,02322434.28
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.264.79357173786,154247.371.013,90311825.82
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.265.79357171574,463217.471.005,19613683.13
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.266.79357174996,354277.571.016,71922365.42
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.267.79357171391,416257.391.004,89311895.77
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.268.79357172591,305257.541.005,44223069.51
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.269.793571751105,259297.441.003,89111965.61
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.270.79357173797,427277.461.014,50611597.55
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.271.79357175192,625267.461.013,97711816.52
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.272.79357171172,636207.391.004,52213612.71
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.273.79357172592,893267.181.016,47022165.74
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.274.79357171566,125187.361.004,74715921.82
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.275.79357173992,012267.151.015,27212065.92
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.276.79357175191,906267.171.014,04012066.21
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.277.79357171569,342197.341.004,96116613.10
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.278.79357172795,685277.361.014,86711996.23
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.279.79357172588,795257.461.004,26511705.53
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.280.79357172784,903247.531.004,79412933.55
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.281.79357171390,769257.341.004,99611697.46
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.282.79357175996,733277.391.003,59611776.55
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.283.793571761100,768287.431.014,41612196.99
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.284.79357176397,003277.551.013,93912186.01
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.285.79357176396,167277.501.003,80111926.35
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.286.793571763102,249287.581.004,00312086.92
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.287.79357176393,296267.781.013,98811466.51
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.288.79357177384,964247.751.005,12111547.14
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.289.79357177598,822287.321.003,56413783.04
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.290.79357177597,283277.341.003,49411636.50
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.291.793571775100,151287.481.004,11011,7174.24
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.292.79357177584,831247.531.005,69722164.17
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.293.79357177791,671267.411.013,87011707.18
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.294.793571787101,883287.481.005,28112784.42
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.295.79357178791,278257.371.015,34412195.43
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.296.793571787106,588307.471.004,92112125.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.297.79357178779,811227.491.015,12914362.43
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.298.79357178993,781267.511.014,02611865.43
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.299.79357179165,006187.551.014,99915372.09
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.300.79357179374,752217.651.015,91125881.79
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.301.79357179373,400207.431.015,17115031.63
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.302.79357179585,553247.811.006,07821514.30
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.303.79357320965,721187.281.015,11213811.94
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.304.79358038760,274177.631.016,94123932.95
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.305.79358038977,215227.431.018,67423483.19
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.306.79358039178,019227.501.009,07334062.32
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.307.79358039369,348197.351.005,51627991.53
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.308.79358039520,17068.591.018,6592720.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.309.793580397109,211305.731.012,08313136.04
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.310.79358039713,56848.851.003,4251599.49
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.311.79358039927,32989.121.002,3221142.86
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.312.79358041366,762197.271.015,78524671.67
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.313.79358041774,820217.711.019,14033792.30
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.314.79358041974,223217.101.004,99213613.19
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.315.79358042348,129137.751.0110,97233350.60
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.316.79358042513,01548.151.013,3571341.18
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.317.793580427103,777295.931.012,64412846.27
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.318.79358042910,73238.651.001,400068.33
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.319.79358043123,26569.091.001,92911396.92
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.320.79358043767,307197.291.015,24211543.64
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.321.79358044181,491237.881.019,16635,3300.25
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.322.79358044365,471187.701.005,73424502.82
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.323.79358044545,739138.101.0010,36334310.49
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.324.79358044912,74749.181.014,6071370.27
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.325.79358045113,30448.431.045,5252410.49
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.326.79358045324,20178.651.004,027181.25
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.327.79358045520,95968.391.002,498192.22
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.328.79358047965,136187.581.015,23814622.42
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.329.79358048575,754217.451.016,43225953.26
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.330.79358048776,457217.991.009,23535172.57
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.331.79358048971,441207.281.005,52427351.05
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.332.79358049326,56978.591.017,96722030.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.333.793580495122,384346.011.003,31413236.90
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.334.7935804959,99438.961.002,399110.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.335.79358049723,72578.431.003,441130.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.336.79358052966,341187.531.015,87125691.58
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.337.79358053383,585237.451.008,55922234.48
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.338.79358053777,468227.591.008,61126411.76
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.339.79358053969,320197.331.005,75727341.47
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.340.79358054120,21168.611.019,2223620.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.341.793580541114,461326.031.013,13413187.26
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.342.79358054326,02479.101.003,29415943.90
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.343.79358054525,30078.491.014,5731171.18
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.344.79358063167,784197.311.015,24815683.84
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.345.79358063581,527237.711.018,99634062.96
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.346.79358063765,316187.431.005,50526791.06
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.347.79358063924,27678.651.009,05333580.45
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.348.7935806417,21728.751.021,967130.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.349.79358064517,01058.751.005,5452290.34
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.350.79358064722,27868.901.003,9661310.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.351.79358190721,72368.681.002,452120.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.352.7935882734,16118.651.001,236030.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.353.7935883178,94428.411.00977020.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.354.79358838310,75938.871.001,820110.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.356.79358895318,70358.501.0097101145.96
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.358.79358910320,26568.581.001,894177.14
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.359.79358910915,25748.481.001,3530320.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.361.79358912912,02438.581.001,8121115.45
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.362.79358913316,08348.771.001,4340702.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.364.79358923311,92138.521.001,50205474.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.367.79710169934,01298.761.001,3490942.87
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.368.79710177927,49988.341.001,1440626.77
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.369.79710186536,654108.731.001,2960161.88
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.370.79710195925,80079.171.001,3220113.64
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.371.79710206122,50368.761.001,39101,1030.11
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.372.79710221316,71458.611.001,420050.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.373.80435283926,52178.391.001,831178.57
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.374.80435293921,94168.471.011,9581512.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.375.80435304518,87458.721.011,6210133.08
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.376.80435320919,14558.991.001,7961120.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.377.80435342911,97538.711.001,885152.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.378.81022440524,08678.941.005,2321820.61
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.379.81022449117,51458.661.004,144136.67
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.380.81282247921,77068.731.003,8241827.50
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.381.81282257119,66958.421.007,0222572.28
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.382.8128226997,91527.871.003,052136.67
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.383.8128228455,02518.971.002,039120.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.384.8128229675,84927.221.001,903100.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.385.8128231176,27627.271.002,868154.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.386.8128232776,37925.491.003,444152.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.387.8128233951,46606.831.003,836100.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.388.8128235971,15006.671.001,721000.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.389.8128237271,18106.221.00749000.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.390.8128239832,16816.851.00801030.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.391.8128242853,28317.771.001,259010.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.392.8128244097,50928.301.005,591200.00
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.393.8128245495,28118.081.002,90715211.15
      T24DATAXML+ASMPRDDB/eqmfprdb/datafile/t24dataxml.394.8128246792,41617.811.001,76002016.00
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.266.74068686,61626.931.007,64729324.54
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.267.74068706,73726.701.007,55921,7972.29
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.268.74068716,62026.221.008,47728782.82
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.269.74068728,33526.101.008,92021,8233.01
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.270.74068737,10226.321.007,86221,5143.46
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.271.74068746,56226.351.007,39829284.46
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.272.74068757,08926.781.006,99128974.16
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.273.74068778,63226.491.007,50229594.11
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.274.74068789,09337.101.007,46621,0223.74
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.275.74068798,82927.031.007,48529603.82
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.276.74068808,39927.131.007,27721,1043.46
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.277.74068818,08327.131.007,54828704.09
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.278.74068827,85927.171.008,33421,3192.69
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.281.804352598506.871.001,4730210.00
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.282.804355163805.051.00690030.00
      T24INDEXXML+ASMPRDIND/eqmfprdb/datafile/t24indexxml.283.804440343805.391.00574040.00
      UNDOTBS1+ASMPRDDB/eqmfprdb/datafile/undotbs1.355.7935889392800.001.0023,58272,7590.05
      UNDOTBS1+ASMPRDUNDO/eqmfprdb/datafile/undotbs1.266.7406832912800.001.0022,69966880.07
      UNDOTBS1+ASMPRDUNDO/eqmfprdb/datafile/undotbs1.268.74175511312300.001.0022,81465130.04
      USERS+ASMPRDDB/eqmfprdb/datafile/users.357.7935889552800.001.0028000.00

      Back to IO Stats
      Back to Top

      Buffer Pool Statistics

      • Standard block size Pools D: default, K: keep, R: recycle
      • Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
      PNumber of BuffersPool Hit%Buffer GetsPhysical ReadsPhysical WritesFree Buff WaitWrit Comp WaitBuffer Busy Waits
      D6,698,91597266,967,0777,338,7372,474,9720053,365
      K253,312 000000


      Back to Top

      Advisory Statistics

      Back to Top

      Instance Recovery Stats

      • B: Begin snapshot, E: End snapshot
      Targt MTTR (s)Estd MTTR (s)Recovery Estd IOsActual Redo BlksTarget Redo BlksLog File Size Redo BlksLog Ckpt Timeout Redo BlksLog Ckpt Interval Redo Blks
      B01103441553303505471852047185207969571
      E03513538326308471852047185207813902

      Back to Advisory Statistics
      Back to Top

      Buffer Pool Advisory

      • Only rows with estimated physical reads >0 are displayed
      • ordered by Block Size, Buffers For Estimate
      PSize for Est (M)Size FactorBuffers for EstimateEst Phys Read FactorEstimated Physical Reads
      D5,4080.10668,9021.3626,102,719
      D10,8160.201,337,8041.2824,407,121
      D16,2240.302,006,7061.2323,627,269
      D21,6320.402,675,6081.2023,016,104
      D27,0400.503,344,5101.0419,975,794
      D32,4480.604,013,4121.0319,731,531
      D37,8560.704,682,3141.0219,559,175
      D43,2640.805,351,2161.0119,359,699
      D48,6720.906,020,1181.0119,253,884
      D54,0801.006,689,0201.0019,143,301
      D54,1601.006,698,9151.0019,141,248
      D59,4881.107,357,9220.9919,005,904
      D64,8961.208,026,8240.9818,847,739
      D70,3041.308,695,7260.9818,710,294
      D75,7121.409,364,6280.9718,583,920
      D81,1201.5010,033,5300.9718,472,448
      D86,5281.6010,702,4320.9618,385,526
      D91,9361.7011,371,3340.9618,320,909
      D97,3441.8012,040,2360.9518,260,316
      D102,7521.9012,709,1380.9518,211,166
      D108,1602.0013,378,0400.9518,155,422

      Back to Advisory Statistics
      Back to Top

      PGA Aggr Summary

      • PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory
      PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
      100.002390

      Back to Advisory Statistics
      Back to Top

      PGA Aggr Target Stats

      • B: Begin snap E: End snap (rows dentified with B or E contain data which is absolute i.e. not diffed over the interval)
      • Auto PGA Target - actual workarea memory target
      • W/A PGA Used - amount of memory used for all Workareas (manual + auto)
      • %PGA W/A Mem - percentage of PGA memory allocated to workareas
      • %Auto W/A Mem - percentage of workarea memory controlled by Auto Mem Mgmt
      • %Man W/A Mem - percentage of workarea memory under manual control
      PGA Aggr Target(M)Auto PGA Target(M)PGA Mem Alloc(M)W/A PGA Used(M)%PGA W/A Mem%Auto W/A Mem%Man W/A MemGlobal Mem Bound(K)
      B4,0962,1462,361.030.000.000.000.00419,430
      E4,0962,4571,793.960.000.000.000.00419,430

      Back to Advisory Statistics
      Back to Top

      PGA Aggr Target Histogram

      • Optimal Executions are purely in-memory operations
      Low OptimalHigh OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
      2K4K6,7356,73500
      64K128K6600
      128K256K121200
      512K1024K313100
      1M2M4400
      16M32M121200

      Back to Advisory Statistics
      Back to Top

      PGA Memory Advisory

      • When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0
      PGA Target Est (MB)Size FactrW/A MB ProcessedEstd Extra W/A MB Read/ Written to DiskEstd PGA Cache Hit %Estd PGA Overalloc Count
      5120.131,349.75146.6590.0047
      1,0240.251,349.75133.5091.0037
      2,0480.501,349.750.00100.000
      3,0720.751,349.750.00100.000
      4,0961.001,349.750.00100.000
      4,9151.201,349.750.00100.000
      5,7341.401,349.750.00100.000
      6,5541.601,349.750.00100.000
      7,3731.801,349.750.00100.000
      8,1922.001,349.750.00100.000
      12,2883.001,349.750.00100.000
      16,3844.001,349.750.00100.000
      24,5766.001,349.750.00100.000
      32,7688.001,349.750.00100.000

      Back to Advisory Statistics
      Back to Top

      Shared Pool Advisory

      • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
      • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.
      Shared Pool Size(M)SP Size FactrEst LC Size (M)Est LC Mem ObjEst LC Time Saved (s)Est LC Time Saved FactrEst LC Load Time (s)Est LC Load Time FactrEst LC Mem Obj Hits
      1,0400.2017419,355967,3841.001281.0024,087,416
      1,5520.3017419,355967,3841.001281.0024,087,416
      2,0640.4017419,355967,3841.001281.0024,087,416
      2,5760.5017419,355967,3841.001281.0024,087,416
      3,0880.6017419,355967,3841.001281.0024,087,416
      3,6000.7017419,355967,3841.001281.0024,087,416
      4,1120.8017419,355967,3841.001281.0024,087,416
      4,6240.9017419,355967,3841.001281.0024,087,416
      5,1361.0017419,355967,3841.001281.0024,087,416
      5,6481.1017419,355967,3841.001281.0024,087,416
      6,1601.2017419,355967,3841.001281.0024,087,416
      6,6721.3017419,355967,3841.001281.0024,087,416
      7,1841.4017419,355967,3841.001281.0024,087,416
      7,6961.5017419,355967,3841.001281.0024,087,416
      8,2081.6017419,355967,3841.001281.0024,087,416
      8,7201.7017419,355967,3841.001281.0024,087,416
      9,2321.8017419,355967,3841.001281.0024,087,416
      9,7441.9017419,355967,3841.001281.0024,087,416
      10,2562.0017419,355967,3841.001281.0024,087,416

      Back to Advisory Statistics
      Back to Top

      SGA Target Advisory

      SGA Target Size (M)SGA Size FactorEst DB Time (s)Est Physical Reads
      15,3600.25218,82324,385,825
      30,7200.50180,25220,117,349
      46,0800.75174,64819,497,175
      61,4401.00171,42219,141,150
      76,8001.25167,58718,716,216
      92,1601.50165,20518,452,069
      107,5201.75163,69818,285,541
      122,8802.00163,69918,285,541

      Back to Advisory Statistics
      Back to Top

      Streams Pool Advisory

      No data exists for this section of the report.Back to Advisory Statistics
      Back to Top

      Java Pool Advisory

      No data exists for this section of the report.Back to Advisory Statistics
      Back to Top

      Wait Statistics

      Back to Top

      Buffer Wait Statistics

      • ordered by wait time desc, waits desc
      ClassWaitsTotal Wait Time (s)Avg Time (ms)
      data block42,6501574
      1st level bmb4,21200
      2nd level bmb2,52900
      undo header2,51000
      undo block1,45000
      segment header706

      Back to Wait Statistics
      Back to Top

      Enqueue Activity

      • only enqueues with waits are shown
      • Enqueue stats gathered prior to 10g should not be compared with 10g data
      • ordered by Wait Time desc, Waits desc
      Enqueue Type (Request Reason)RequestsSucc GetsFailed GetsWaitsWt Time (s)Av Wt Time(ms)
      TX-Transaction (index contention)5,1775,17703,186247.66
      CF-Controlfile Transaction6,0556,0523248344.04
      SQ-Sequence Cache4,7844,784012600.47
      HW-Segment High Water Mark106,8982,762104,136700.57

      Back to Wait Statistics
      Back to Top

      Undo Statistics

      Back to Top

      Undo Segment Summary

      • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
      • STO - Snapshot Too Old count, OOS - Out of Space count
      • Undo segment block stats:
      • uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed
      • eS - expired Stolen, eR - expired Released, eU - expired reUsed
      Undo TS#Num Undo Blocks (K)Number of TransactionsMax Qry Len (s)Max Tx ConcurcyMin/Max TR (mins)STO/ OOSuS/uR/uU/ eS/eR/eU
      11,110.922,492,97614456180/1800/00/0/0/0/0/0

      Back to Undo Statistics
      Back to Top

      Undo Segment Stats

      • Most recent 35 Undostat rows, ordered by Time desc
      End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOSuS/uR/uU/ eS/eR/eU
      12-Jul 23:3117,78890,311123121800/00/0/0/0/0/0
      12-Jul 23:2148,740136,332144461800/00/0/0/0/0/0
      12-Jul 23:11198,508107,535116561800/00/0/0/0/0/0
      12-Jul 23:01257,080312,79897561800/00/0/0/0/0/0
      12-Jul 22:51280,3471,128,043106561800/00/0/0/0/0/0
      12-Jul 22:41141,998608,479106551800/00/0/0/0/0/0
      12-Jul 22:31166,461109,478116491800/00/0/0/0/0/0

      Back to Undo Statistics
      Back to Top

      Latch Statistics

      Back to Top

      Latch Activity

      • "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
      • "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
      • "Pct Misses" for both should be very close to 0.0
      Latch NameGet RequestsPct Get MissAvg Slps /MissWait Time (s)NoWait RequestsPct NoWait Miss
      ASM allocation4,8900.00 00
      ASM db client latch2,4200.00 00
      ASM map headers2,4450.00 00
      ASM map load waiting list6960.00 00
      ASM map operation freelist1,0100.00 00
      ASM map operation hash table27,993,4350.000.0000
      ASM network background latch4,1200.00 00
      AWR Alerted Metric Element list28,6690.00 00
      Consistent RBA1,300,8640.030.0000
      FAL request queue3700.00 00
      FAL subheap alocation3310.00 00
      FIB s.o chain latch9360.00 00
      FOB s.o list latch2,1680.00 00
      In memory undo latch13,234,9920.040.6402,059,7170.00
      JS mem alloc latch180.00 00
      JS queue access latch180.00 00
      JS queue state obj latch25,9140.00 00
      JS slv state obj latch760.00 00
      KFK SGA context latch25,4560.00 00
      KFMD SGA1400.00 00
      KMG MMAN ready and startup request latch1,1960.00 00
      KTF sga latch70.00 01,1940.00
      KWQMN job cache list latch320.00 00
      MQL Tracking Latch0 0720.00
      Memory Management Latch0 01,1960.00
      OS process2850.00 00
      OS process allocation1,3820.00 00
      OS process: request allocation660.00 00
      PL/SQL warning settings8360.00 00
      SGA IO buffer pool latch880,6430.010.000880,6890.00
      SQL memory manager latch10.00 01,1880.00
      SQL memory manager workarea list latch81,8210.00 00
      Shared B-Tree1280.00 00
      active checkpoint queue latch3,416,36117.660.0000
      active service list7,5080.00 01,1970.00
      alert log latch410.00 00
      archive control4600.00 00
      archive process latch1,7340.00 00
      begin backup scn array140.00 00
      cache buffer handles14,9200.010.0000
      cache buffers chains525,816,6670.440.00013,451,5830.10
      cache buffers lru chain11,759,7190.260.00014,568,6560.28
      cache table scan latch0 017,9050.02
      channel handle pool latch740.00 00
      channel operations parent latch17,0960.00 00
      checkpoint queue latch237,676,6040.000.0002,143,7210.03
      client/application info1,5790.00 00
      commit callback allocation120.00 00
      compile environment latch930.00 00
      dml lock allocation6,196,1312.990.0000
      dummy allocation1900.00 00
      enqueue hash chains11,175,0111.260.0004,0650.02
      enqueues2,359,9562.630.0000
      event group latch310.00 00
      file cache latch1,2920.00 00
      hash table column usage latch7550.00 07,1400.00
      hash table modification latch2860.350.0000
      job workq parent latch0 01240.00
      job_queue_processes parameter latch1220.00 00
      kks stats2,5680.120.0000
      kokc descriptor allocation latch8,078,22310.820.0000
      ksuosstats global area2410.00 00
      ktm global data150.00 00
      kwqbsn:qsga1280.00 00
      lgwr LWN SCN1,306,2910.240.0000
      library cache27,034,2212.360.0000
      library cache load lock4,9340.00 00
      library cache lock186,7180.000.0000
      library cache lock allocation10,3920.00 00
      library cache pin16,552,4641.470.0000
      library cache pin allocation8480.00 00
      list of block allocation128,6030.080.0000
      loader state object freelist2,7100.00 00
      message pool operations parent latch80.00 00
      messages13,604,0165.990.0000
      mostly latch-free SCN1,375,9093.730.0000
      msg queue6990.00 06990.00
      multiblock read objects35,8380.010.0000
      ncodef allocation latch570.00 00
      object queue header heap28,293,8460.010.0002,645,0790.07
      object queue header operation59,313,9870.050.0000
      object stats modification8670.00 00
      parallel query alloc buffer4720.00 00
      parameter list240.00 00
      parameter table allocation management990.00 00
      post/wait queue2,274,3780.320.0002,010,1752.30
      process allocation660.00 0310.00
      process group creation660.00 00
      qmn task queue latch5120.00 00
      redo allocation9,580,6170.280.00015,749,6552.03
      redo copy0 015,825,6830.38
      redo writing7,638,2714.530.0000
      resmgr group change latch3190.00 00
      resmgr:active threads1740.00 00
      resmgr:actses change group620.00 00
      resmgr:free threads list1730.00 00
      resmgr:schema config10.00 00
      row cache objects36,564,3025.650.0000
      segmented array pool6990.00 00
      sequence cache288,7520.150.0000
      session allocation4,133,1832.920.0000
      session idle bit153,580,7630.010.0000
      session state list latch2040.00 00
      session switching570.00 00
      session timer1,1980.00 00
      shared pool11,749,9620.030.0000
      shared pool sim alloc1580.00 00
      shared pool simulator13,584,9750.010.0000
      simulator hash latch21,336,1480.000.0000
      simulator lru latch2,474,9720.100.00017,845,7680.69
      slave class2,1060.00 00
      slave class create1,4340.281.0000
      sort extent pool1020.00 00
      state object free list20.00 00
      statistics aggregation5600.00 00
      temp lob duration state obj allocation50.00 00
      threshold alerts latch2910.00 00
      transaction allocation2,9170.00 00
      transaction branch allocation1,1980.00 00
      undo global data13,874,5080.040.00016,3940.02
      user lock1300.00 00

      Back to Latch Statistics
      Back to Top

      Latch Sleep Breakdown

      • ordered by misses desc
      Latch NameGet RequestsMissesSleepsSpin GetsSleep1Sleep2Sleep3
      cache buffers chains525,816,6672,292,0584,5432,287,897000
      row cache objects36,564,3022,064,370312,064,339000
      kokc descriptor allocation latch8,078,223873,794109873,685000
      messages13,604,016814,79013814,777000
      library cache27,034,221638,01351637,962000
      active checkpoint queue latch3,416,361603,36612603,354000
      redo writing7,638,271345,94413345,931000
      library cache pin16,552,464242,63925242,614000
      dml lock allocation6,196,131185,0568185,048000
      enqueue hash chains11,175,011140,7514140,747000
      session allocation4,133,183120,5121120,511000
      mostly latch-free SCN1,375,90951,337651,331000
      cache buffers lru chain11,759,71931,072331,069000
      object queue header operation59,313,98730,66311630,548000
      redo allocation9,580,61726,540226,538000
      session idle bit153,580,7639,431259,406000
      In memory undo latch13,234,9924,8153,1011,782000
      lgwr LWN SCN1,306,2913,19413,193000
      shared pool11,749,9622,95922,957000
      slave class create1,434440000

      Back to Latch Statistics
      Back to Top

      Latch Miss Sources

      • only latches with sleeps are shown
      • ordered by name, sleeps desc
      Latch NameWhereNoWait MissesSleepsWaiter Sleeps
      In memory undo latchktiFlush: child01,661698
      In memory undo latchktichg: child01,4551,197
      In memory undo latchkticmt: child0831141
      active checkpoint queue latchkcbbacq: scan active checkpoints01212
      cache buffers chainskcbgtcr: fast path04,293545
      cache buffers chainskcbgtcr: kslbegin excl03,8943,557
      cache buffers chainskcbchg: kslbegin: bufs not pinned03,4273,670
      cache buffers chainskcbrls: kslbegin09091,969
      cache buffers chainskcbget: pin buffer0642232
      cache buffers chainskcbgcur: kslbegin0621771
      cache buffers chainskcbzgb: scan from tail. nowait03510
      cache buffers chainskcbzwb022491
      cache buffers chainskcbibr04170
      cache buffers chainskcbchg: kslbegin: call CR func0242,018
      cache buffers chainskcbnlc024561
      cache buffers chainskcb_is_private02071
      cache buffers chainskcbbxsv0109
      cache buffers chainskcbnew: new latch again0740
      cache buffers chainskcbget: exchange0612
      cache buffers chainskcbget: exchange rls065
      cache buffers chainskcbgtcr: kslbegin shared054
      cache buffers chainskcb_pre_apply: kcbhq6102487
      cache buffers chainskcb_post_apply: kcbhq620195
      cache buffers chainskcb_post_apply: kcbhq6301105
      cache buffers chainskcbcge01190
      cache buffers chainskcbzib: multi-block read: nowait010
      cache buffers lru chainkcbbxsv: move to being written022
      cache buffers lru chainkcbibr010
      dml lock allocationktaiam078
      dml lock allocationktaidm010
      enqueue hash chainsksqgtl3032
      enqueue hash chainsksqrcl012
      kokc descriptor allocation latchkokcdlt: regular free05840
      kokc descriptor allocation latchkokcdlt: allocation05169
      library cachekglpndl: child: after processing0241
      library cachekglpnp: child01616
      library cachekglobpn: child:01117
      library cache pinkglpndl01411
      library cache pinkglpnp: child069
      library cache pinkglpnal: child: alloc space055
      messagesksaamb: after wakeup066
      messagesksarcv: after wait046
      messagesksarcv032
      mostly latch-free SCNkcslcu3067
      mostly latch-free SCNkcs024010
      object queue header operationkcbw_unlink_q011915
      object queue header operationkcbw_link_q0516
      object queue header operationkcbo_switch_q_bg0218
      object queue header operationkcbo_switch_mq_bg01516
      object queue header operationkcbo_switch_cq01420
      object queue header operationkcbo_link_q:reget094
      object queue header operationkcbo_swap_buf_bg09169
      object queue header operationkcbo_swap_buf011
      redo allocationkcrfw_redo_gen: redo allocation 1020
      redo writingkcrfwcr01113
      redo writingkcrfw_post: rba scn pair020
      row cache objectskqreqd: reget0300
      row cache objectskqrpre: find obj0160
      session allocationksucri010
      session idle bitksupuc: set busy01818
      session idle bitksupuc: clear busy01318
      session idle bitksuxds072
      shared poolkghalo010
      shared poolkghfre013
      slave class createksvsubmit021
      slave class createksvcreate010
      slave class createksvgetwork1012

      Back to Latch Statistics
      Back to Top

      Parent Latch Statistics

      No data exists for this section of the report.Back to Latch Statistics
      Back to Top

      Child Latch Statistics

      No data exists for this section of the report.Back to Latch Statistics
      Back to Top

      Segment Statistics

      Back to Top

      Segments by Logical Reads

      • Total Logical Reads: 267,111,630
      • Captured Segments account for 61.8% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
      T24USRT24INDEXXMLFBNK_CUSTOMER_PK INDEX17,286,6406.47
      T24USRT24INDEXXMLFBNK_LIMIT_LIABILITY_PK INDEX17,174,9126.43
      SYSSYSTEMDUAL TABLE14,120,0165.29
      T24USRT24INDEXXMLFTN0_EB_C004_PK INDEX7,292,9282.73
      T24USRT24DATAXMLLOB_FBNK_CUSTOMER LOB6,598,9122.47

      Back to Segment Statistics
      Back to Top

      Segments by Physical Reads

      • Total Physical Reads: 7,354,234
      • Captured Segments account for 72.8% of Total
      OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
      T24USRT24DATAXMLLOB_FBNK_CUSTOMER LOB2,857,47238.85
      T24USRT24DATAXMLLOB_FTN0_LD_L000 LOB862,12611.72
      T24USRT24DATAXMLFTN0_EB_C004 TABLE305,7694.16
      T24USRT24DATAXMLFTN0_LD_L000 TABLE185,0242.52
      T24USRT24DATAXMLLOB_FAP0_PD_PAYMENT_DUE LOB159,7412.17

      Back to Segment Statistics
      Back to Top

      Segments by Row Lock Waits

      • % of Capture shows % of row lock waits for each top segment compared
      • with total row lock waits for all segments captured by the Snapshot
      OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
      T24USRT24INDEXXMLFAP0_STMT_ENTRY_PK INDEX1803.50

      Back to Segment Statistics
      Back to Top

      Segments by ITL Waits

      No data exists for this section of the report.Back to Segment Statistics
      Back to Top

      Segments by Buffer Busy Waits

      • % of Capture shows % of Buffer Busy Waits for each top segment compared
      • with total Buffer Busy Waits for all segments captured by the Snapshot
      OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
      T24USRT24DATAXMLLOB_FAP0_PD_PAYMENT_DUE LOB5,94728.20
      T24USRT24DATAXMLF_JOB_LIST_33 TABLE1,1325.37
      T24USRT24DATAXMLF_TSA_STATUS TABLE1,0825.13
      T24USRT24INDEXXMLIX_F_BATCH_C1 INDEX1,0705.07
      T24USRT24DATAXMLF_JOB_LIST_28 TABLE9744.62

      Back to Segment Statistics
      Back to Top

      Dictionary Cache Stats

      • "Pct Misses" should be very low (< 2% in most cases)
      • "Final Usage" is the number of cache entries being used
      CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
      dc_awr_control720.000 21
      dc_database_links40.000 01
      dc_global_oids4,039,2990.000 035
      dc_histogram_data2,2500.090 05,139
      dc_histogram_defs4,94423.730 08,969
      dc_object_grants13,8360.000 024
      dc_object_ids4,050,8120.050 09,839
      dc_objects6,49514.430 54,519
      dc_profiles640.000 01
      dc_rollback_segments7,2840.000 0230
      dc_segments6,90036.040 811,745
      dc_sequences4,6570.000 4,6576
      dc_tablespace_quotas40.000 42
      dc_tablespaces8,0090.000 08
      dc_usernames2,1990.000 046
      dc_users4,044,0930.000 064
      outstanding_alerts1120.000 018


      Back to Top

      Library Cache Activity

      • "Pct Misses" should be very low
      NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
      BODY6600.002,020,8710.0000
      CLUSTER10.0010.0000
      INDEX3528.571566.4100
      SQL AREA31,4738.1443,252,284-0.061120
      TABLE/PROCEDURE3,28027.356,252,3910.0490
      TRIGGER120.001980.0000


      Back to Top

      Memory Statistics

      Back to Top

      Process Memory Summary

      • B: Begin snap E: End snap
      • All rows below contain absolute values (i.e. not diffed over the interval)
      • Max Alloc is Maximum PGA Allocation size at snapshot time
      • Hist Max Alloc is the Historical Max Allocation for still-connected processes
      • ordered by Begin/End snapshot, Alloc (MB) desc
      CategoryAlloc (MB)Used (MB)Avg Alloc (MB)Std Dev Alloc (MB)Max Alloc (MB)Hist Max Alloc (MB)Num ProcNum Alloc
      BOther2,088.17 21.5314.1248499797
      SQL208.51125.292.821.949157472
      Freeable62.380.001.163.1216 5454
      PL/SQL1.991.610.020.01009595
      EOther1,322.44 14.229.5948499393
      SQL282.81165.253.982.9110157169
      Freeable186.810.002.632.6116 7171
      PL/SQL1.901.530.020.01009191

      Back to Memory Statistics
      Back to Top

      SGA Memory Summary

      SGA regionsBegin Size (Bytes)End Size (Bytes) (if different)
      Database Buffers58,938,359,808
      Fixed Size2,204,712
      Redo Buffers81,678,336
      Variable Size5,402,266,584

      Back to Memory Statistics
      Back to Top

      SGA breakdown difference

      • ordered by Pool, Name
      • N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
      PoolNameBegin MBEnd MB% Diff
      javafree memory16.0016.000.00
      largeASM map operations hashta0.380.380.00
      largePX msg pool1.031.030.00
      largefree memory14.6014.600.00
      sharedASH buffers67.5067.500.00
      shareddb_block_hash_buckets360.00360.000.00
      sharedfree memory4,299.534,249.56-1.16
      sharedlibrary cache 51.63
      sharedsql area78.9494.6119.85
      buffer_cache56,208.0056,208.000.00
      fixed_sga2.102.100.00
      log_buffer77.8977.890.00

      Back to Memory Statistics
      Back to Top

      Streams Statistics

      Back to Top

       

      Streams CPU/IO Usage

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Streams Capture

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Streams Apply

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Buffered Queues

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Buffered Subscribers

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

      Rule Set

      No data exists for this section of the report.

      Back to Streams Statistics
      Back to Top

       

       

      Resource Limit Stats

      No data exists for this section of the report.


      Back to Top

       

      init.ora Parameters

      Parameter NameBegin valueEnd value (if different)
      archive_lag_target1800 
      audit_file_dest/home/oracle/admin/EQMFPRDB/adump 
      background_dump_dest/home/oracle/admin/EQMFPRDB/bdump 
      compatible10.2.0.3.0 
      control_files+ASMPRDFLASH/eqmfprdb/controlfile/current.261.740682331, +ASMPRDUNDO/eqmfprdb/controlfile/current.261.740682331, +ASMPRDIND/eqmfprdb/controlfile/current.261.740682331 
      core_dump_dest/home/oracle/admin/EQMFPRDB/cdump 
      cursor_sharingSIMILAR 
      db_block_size8192 
      db_cache_size12884901888 
      db_create_file_dest+ASMPRDDB 
      db_create_online_log_dest_1+ASMPRDFLASH 
      db_create_online_log_dest_2+ASMPRDUNDO 
      db_create_online_log_dest_3+ASMPRDIND 
      db_domain   
      db_file_multiblock_read_count32 
      db_flashback_retention_target0 
      db_keep_cache_size2147483648 
      db_nameEQMFPRDB 
      db_recovery_file_dest+ASMPRDFLASH 
      db_recovery_file_dest_size96636764160 
      db_writer_processes8 
      dispatchers(PROTOCOL=TCP) (SERVICE=EQMFPRDBXDB) 
      dml_locks24220 
      event44951 trace name context forever, level 1024 
      filesystemio_optionsSETALL 
      job_queue_processes10 
      log_archive_configdg_config=(EQMFLCDR, EQMFPRDB) 
      log_archive_dest_1LOCATION=/backup/prodarch 
      log_archive_dest_2SERVICE=EQMFLCDR valid_for=(online_logfiles, primary_role) db_unique_name=EQMFLCDR 
      log_archive_dest_state_2ENABLE 
      log_archive_max_processes4 
      log_buffer79202304 
      open_cursors5000 
      optimizer_index_cost_adj1 
      pga_aggregate_target4294967296 
      processes500 
      query_rewrite_integrityTRUSTED 
      remote_login_passwordfileEXCLUSIVE 
      session_cached_cursors5000 
      sessions555 
      sga_max_size64424509440 
      sga_target64424509440 
      shared_pool_size5368709120 
      standby_file_managementAUTO 
      undo_managementAUTO 
      undo_retention10800 
      undo_tablespaceUNDOTBS1 
      user_dump_dest/home/oracle/admin/EQMFPRDB/udump 


      Back to Top

       

      Regards

      Faruk m

        • 1. Re: Performance and tunning
          Nikolay Savvinov

          Hi,

           

          the answer to your problem is given by the OS CPU stats, but some care is necessary when interpreting the results.

           

          BUSY_TIME8,977,590
          IDLE_TIME12,569,276
          OS_CPU_WAIT_TIME9,317,800

           

          If you look at the averages, your system doesn't look CPU starved (only 45% CPU utilization). However, averages can be very deceiving.

          OS_CPU_WAIT_TIME clearly shows that the system spends about the same amount waiting in line to get CPU as doing something useful on CPU.

          The most likely explanation for this is that the activity is probably taking place in short bursts when CPU is extremely busy (close to 100%), followed

          by periods of relative inactivity.

           

          Another possible explanation is CPU scheduling problems (i.e. the database processes could be tied to specific CPUs and thus unable to make use

          of free CPUs on the system).

           

          I suggest that you do the following:

           

          1) Obtain more information about your CPU situation (e.g. by using high-resolution tools and/or OS side tools) -- I'd also recommend that you sit down with your SA to find the best way to approach this

          2) Take top 3 CPU consuming SQL statements and try to tune them (combined, they're responsible for over half of the total CPU usage by the database, which simplifies your task)

          3) Take a close look at the application's design. Judging by text of SQL statements captured by the report, it's more of a data dump than an actual database -- it looks like the application stores everything

          dumped as unstructured XML. If that's the case, then this is clearly a misuse of an Oracle database, because it means using a relational database management system to manage a non-relational database.

           

          Best regards,

          Nikolay