1 2 3 Previous Next 33 Replies Latest reply on Sep 20, 2017 5:41 AM by Quanwen Zhao Go to original post
      • 15. Re: Why spending very long time by impdp data to another Oracle DB?

        Could you also upload excerpts from the alert log showing the sections around the 'Checkpoint not complete' messages following the addition of the extra redo log groups?

        • 16. Re: Why spending very long time by impdp data to another Oracle DB?
          Quanwen Zhao

          Laury 撰写:

           

          Hi Quanwen,

           

          The quantity of "data" you wish to import is pretty "big".

          You can estimate the size of "data" you are exporting at export time.

          If not, you can look at the size of the dump file(s). It gives an idea of how "big" your import will be.

          In such a situation, in the headlines, you could adopt the following strategy:

          - Extract the SQL of constraints from the export file(s).

          - Extract the SQL of the indexes from from the export file(s).

          - Import data only.

          - Disable all triggers and jobs after that import.

          - Create the indexes from the above script (can take some time if not parallelized, but you can split the script in few scripts and run multiple sessions).

          - Create the constraints from the above script (eventually force parallelisation).

          - Re-enable the triggers and the jobs.

          You should be able tp observe a significany improvement in the duration of the whole import.

           

          Can you go further with these (high-level) indications?

           

          Kind Regards

          Hello,Laury!

          Maybe your suggestion is good i feel,operation about IMPORT data and IMPORT other objects are to separate.

          Actually,which step has spent longer time is the key of this problem.Although your these operation have 7 steps totally.

          From see as your experience,where spent longer time?

           

          Kind Regards,

          Quanwen Zhao

          • 17. Re: Why spending very long time by impdp data to another Oracle DB?
            Quanwen Zhao

            The Real Rob the Relic 撰写:

             

            Could you also upload excerpts from the alert log showing the sections around the 'Checkpoint not complete' messages following the addition of the extra redo log groups?

            Ok,no problem! Thanks for your advice.

            • 18. Re: Why spending very long time by impdp data to another Oracle DB?
              Mark D Powell

              Quanwen, as JohnWatson2 posted you need bigger online redo logs!  The "checkpoint incomplete" message is pretty damn clear.  Rather than increase then 10x I suggest you make a more modest increate to 512M and see what result you get.  Be prepared to increase again if necessary.

              - -

              Now back to the export/import process.  Export parallelism was a valid suggestion however datapump cannot parallelize export/import of LOB segments so if a significant amount of the size involved is due to one or more LOB segments you may want to consider changing your export to be schema and/or table specific so that you can run concurrent export/import processes to handle the LOB segments.

              - -

              HTH -- Mark D Powell --

              1 person found this helpful
              • 19. Re: Why spending very long time by impdp data to another Oracle DB?
                jgarry

                See Configuring a Database for Performance - 11g Release 2 (11.2) section 4.3.1 Sizing Redo Log Files.  There is an advisor available.  I'm with John on the redo size estimate.  Mine are around 1.4G.

                1 person found this helpful
                • 20. Re: Why spending very long time by impdp data to another Oracle DB?
                  Quanwen Zhao

                  jgarry 撰写:

                   

                  See Configuring a Database for Performance - 11g Release 2 (11.2) section 4.3.1 Sizing Redo Log Files. There is an advisor available. I'm with John on the redo size estimate. Mine are around 1.4G.

                  Thanks for your advices,jgarry!

                  • 21. Re: Why spending very long time by impdp data to another Oracle DB?
                    Quanwen Zhao

                    JohnWatson2 撰写:

                     

                    My estimate was vary rough (though not too far off, as it turned out) based on your redo per second figure from the AWR report and your log switch frequency in the alert log. If you had posted the whole AWR report, I could have calculated it exactly.

                     

                    You are losing 93.76% of your DB Time on the log file switch (checkpoint incomplete) wait event. That is an event that you should never see, and increasing the size of your logfiles will eliminate it completely. Increasing by a factor of ten is (in my experience) usually enough.

                    Hi,JohnWatson2

                    My full AWR report's content as follows,please note(Snapshot interval time is from 11-9月 -17 16:00:28 to 12-9月 -17 13:00:40,and it's also my IMPDP process completed time)!

                     

                    WORKLOAD REPOSITORY report for

                     

                    DB NameDB IdInstanceInst numStartup TimeReleaseRAC
                    RESBAK3165050927resbak126-4月 -17 16:0411.2.0.3.0NO

                     

                     

                    Host NamePlatformCPUsCoresSocketsMemory (GB)
                    cq-hbk-dn-100Linux x86 64-bit12122125.93

                     

                    Snap IdSnap TimeSessionsCursors/Session
                    Begin Snap:688411-9月 -17 16:00:28753.7
                    End Snap:690512-9月 -17 13:00:40621.8
                    Elapsed: 1,260.21 (mins)
                    DB Time: 3,644.57 (mins)

                     

                    Report Summary

                    Cache Sizes

                     

                    BeginEnd
                    Buffer Cache:34,432M34,432MStd Block Size:8K
                    Shared Pool Size:3,584M3,584MLog Buffer:82,648K

                    Load Profile

                     

                    Per SecondPer TransactionPer ExecPer Call
                    DB Time(s):2.942.10.340.58
                    DB CPU(s):0.00.10.000.00
                    Redo size:1,088,898.615,864,022.1
                    Logical reads:1,124.016,376.0
                    Block changes:783.911,421.2
                    Physical reads:117.61,712.9
                    Physical writes:283.84,134.7
                    User calls:5.072.7
                    Parses:1.015.0
                    Hard parses:0.11.0
                    W/A MB processed:1.015.1
                    Logons:0.10.7
                    Executes:8.4123.0
                    Rollbacks:0.00.0
                    Transactions:0.1

                    Instance Efficiency Percentages (Target 100%)

                     

                    Buffer Nowait %:99.95Redo NoWait %:91.63
                    Buffer Hit %:99.25In-memory Sort %:99.95
                    Library Hit %:98.92Soft Parse %:93.16
                    Execute to Parse %:87.83Latch Hit %:99.71
                    Parse CPU to Parse Elapsd %:4.87% Non-Parse CPU:96.43

                    Shared Pool Statistics

                     

                    BeginEnd
                    Memory Usage %:79.1979.36
                    % SQL with executions>1:94.1897.04
                    % Memory for SQL w/exec>1:87.7788.80

                     

                    Top 5 Timed Foreground Events

                     

                    EventWaitsTime(s)Avg wait (ms)% DB timeWait Class
                    log file switch (checkpoint incomplete)2,604173,6106667179.39Configuration
                    Wait for shrink lock2142,33014,3661016.57Other
                    db file sequential read102,6954,015391.84User I/O
                    log file sync3,8853,6339351.66Commit
                    buffer busy waits5663,04353761.39Concurrency

                    Host CPU (CPUs: 12 Cores: 12 Sockets: 2)

                     

                    Load Average BeginLoad Average End%User%System%WIO%Idle
                    3.820.011.30.713.498.0

                    Instance CPU

                     

                    %Total CPU%Busy CPU%DB time waiting for CPU (Resource Manager)
                    0.942.40.0

                    Memory Statistics

                     

                    BeginEnd
                    Host Mem (MB):128,950.8128,950.8
                    SGA use (MB):38,784.038,784.0
                    PGA use (MB):3,220.3293.9
                    % Host Mem used for SGA+PGA:32.5730.30

                    Main Report

                     

                    Back to Top

                    Back to Top

                    • 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 time196,348.5989.79
                    DB CPU605.120.28
                    parse time elapsed565.170.26
                    hard parse elapsed time560.120.26
                    failed parse elapsed time528.490.24
                    RMAN cpu time (backup/restore)272.190.12
                    PL/SQL execution elapsed time88.610.04
                    sequence load elapsed time22.230.01
                    connection management call elapsed time12.310.01
                    hard parse (sharing criteria) elapsed time7.830.00
                    inbound PL/SQL rpc elapsed time5.640.00
                    PL/SQL compilation elapsed time2.680.00
                    hard parse (bind mismatch) elapsed time0.300.00
                    repeated bind elapsed time0.210.00
                    DB time218,674.35
                    background elapsed time355,386.77
                    background cpu time7,260.62
                    • ordered by statistic type (CPU Use, Virtual Memory, Hardware Config), Name
                    StatisticValueEnd Value
                    BUSY_TIME1,853,575
                    IDLE_TIME88,594,198
                    IOWAIT_TIME12,131,402
                    NICE_TIME159
                    SYS_TIME630,632
                    USER_TIME1,202,855
                    LOAD40
                    RSRC_MGR_CPU_WAIT_TIME3
                    VM_IN_BYTES2,383,872
                    VM_OUT_BYTES3,005,440
                    PHYSICAL_MEMORY_BYTES135,214,731,264
                    NUM_CPUS12
                    NUM_CPU_CORES12
                    NUM_CPU_SOCKETS2
                    GLOBAL_RECEIVE_SIZE_MAX4,194,304
                    GLOBAL_SEND_SIZE_MAX1,048,576
                    TCP_RECEIVE_SIZE_DEFAULT87,380
                    TCP_RECEIVE_SIZE_MAX4,194,304
                    TCP_RECEIVE_SIZE_MIN4,096
                    TCP_SEND_SIZE_DEFAULT16,384
                    TCP_SEND_SIZE_MAX4,194,304
                    TCP_SEND_SIZE_MIN4,096
                    • ordered by wait time desc, waits desc
                    • %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
                    • Captured Time accounts for 92.1% of Total DB time 218,674.35 (s)
                    • Total FG Wait Time: 200,758.13 (s) DB CPU time: 605.12 (s)
                    Wait ClassWaits%Time -outsTotal Wait Time (s)Avg wait (ms)%DB time
                    Configuration10,2971173,8611688579.51
                    Other147,1449814,8861016.81
                    User I/O114,46604,887432.23
                    Commit3,88503,6339351.66
                    Concurrency1,03903,04529301.39
                    DB CPU 605 0.28
                    Application2,7580240870.11
                    Administrative20183913360.08
                    System I/O35,10402110.01
                    Network358,2390300.00
                    • Only events with Total Wait Time (s) >= .001 are shown
                    • ordered by wait time desc, waits desc (idle events last)
                    • %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
                    EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% DB time
                    log file switch (checkpoint incomplete)2,6040173,610666710.5079.39
                    Wait for shrink lock2142,33010014,36610127.426.57
                    db file sequential read102,69504,0153919.791.84
                    log file sync3,88503,6339350.751.66
                    buffer busy waits56603,04353760.111.39
                    db file scattered read4,00107421850.770.34
                    ADR block file read1,49002401610.290.11
                    switch logfile command20183913360.000.08
                    undo segment extension137991309520.030.06
                    enq: KO - fast object checkpoint20123616810.000.06
                    enq: RO - fast object reuse70115163690.000.05
                    read by other session24101084490.050.05
                    rdbms ipc reply49969619580.010.04
                    log buffer space2608331860.010.04
                    enq: CF - contention3606718550.010.03
                    enq: CR - block range reuse ckpt705984140.000.03
                    ADR block file write1050545190.020.02
                    enq: HW - contention2024119690.000.01
                    Disk file operations I/O3,72801640.720.01
                    control file parallel write221012560.040.01
                    log file switch (private strand flush incomplete)60813110.000.00
                    control file sequential read33,5690406.470.00
                    log file switch completion20317040.000.00
                    Log file init write2,3130310.450.00
                    control file single write22031300.000.00
                    SQL*Net more data to client9,4500301.820.00
                    latch: redo writing7,5200201.450.00
                    direct path sync6012420.000.00
                    library cache lock2016970.000.00
                    enq: TM - contention2016060.000.00
                    CSS initialization6012000.000.00
                    Log archive I/O6370120.120.00
                    enq: TX - row lock contention6011400.000.00
                    reliable message1601500.000.00
                    latch: messages5860110.110.00
                    direct path read780170.020.00
                    enq: TQ - DDL contention1015050.000.00
                    SQL*Net message to client348,30800067.110.00
                    latch free7860010.150.00
                    SQL*Net break/reset to client2,7410000.530.00
                    library cache: mutex X3160010.060.00
                    db file single write2001280.000.00
                    log file sequential read6460000.120.00
                    Data file init write1400130.000.00
                    latch: redo allocation2700010.050.00
                    direct path write temp1030010.020.00
                    recovery area: computing obsolete files100980.000.00
                    direct path write160030.000.00
                    process terminate11000500.000.00
                    library cache load lock100340.000.00
                    Parameter File I/O200170.000.00
                    recovery area: computing backed up files100190.000.00
                    utl_file I/O1,2670000.240.00
                    cursor: pin S wait on X100100.000.00
                    SQL*Net more data from client4810000.090.00
                    latch: shared pool630000.010.00
                    latch: cache buffers chains620000.010.00
                    buffer deadlock465100000.090.00
                    recovery area: computing dropped files10030.000.00
                    ADR file lock1260000.020.00
                    asynch descriptor resize832100000.160.00
                    SQL*Net message from client348,31101,857,721533467.11
                    wait for unread message on broadcast channel148,439100148,537100128.60
                    jobq slave wait21,11610010,5735014.07
                    PL/SQL lock timer8871001782000.17
                    class slave wait2,487100000.48
                    • Only events with Total Wait Time (s) >= .001 are shown
                    • %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0
                    EventWaits%Time -outsTotal Wait Time (s)Avg wait (ms)Waits /txn% bg time
                    db file async I/O submit88,9360129,465145617.1436.43
                    log file switch (checkpoint incomplete)930068,643738100.1819.32
                    local write wait69,492031,97846013.399.00
                    buffer busy waits7,907025,58432361.527.20
                    enq: TC - contention2017,65988294530.004.97
                    RMAN backup & recovery I/O271,902017,4696452.394.92
                    rdbms ipc reply32,6861912,8863946.303.63
                    direct path write temp323,858010,1993162.402.87
                    control file parallel write73,20708,27611314.112.33
                    log file parallel write16,78207,4764453.232.10
                    log buffer space1,78703,00216800.340.84
                    direct path read temp372,05802,624771.690.74
                    enq: CR - block range reuse ckpt4901,778362820.010.50
                    Datapump dump file I/O108,27301,4131320.860.40
                    enq: CF - contention2,83301,3994940.550.39
                    enq: HW - contention3201,228383740.010.35
                    enq: CI - contention7701,039134980.010.29
                    db file single write14,0660823582.710.23
                    Disk file operations I/O38,7810798217.470.22
                    enq: RO - fast object reuse490741151200.010.21
                    enq: JD - contention80681851620.000.19
                    db file parallel write1,68404482660.320.13
                    row cache lock70389555630.000.11
                    log file switch completion175031417930.030.09
                    change tracking file synchronous write1,71802751600.330.08
                    control file sequential read4,679,18202720901.580.08
                    direct path write103,6330244219.970.07
                    enq: JS - queue lock408318345820.010.05
                    Data file init write17,567013273.380.04
                    Log archive I/O80,9320116115.590.03
                    db file sequential read29,03008035.590.02
                    log file single write760052690.150.01
                    log file sync470439210.010.01
                    log file sequential read82,863043115.970.01
                    ADR block file read3470421220.070.01
                    os thread startup808021260.160.01
                    change tracking file synchronous read5,82702031.120.01
                    ADR block file write1160171490.020.00
                    log file switch (private strand flush incomplete)1001616220.000.00
                    direct path sync34061910.010.00
                    reliable message1,5960640.310.00
                    control file single write9404430.020.00
                    undo segment extension159332210.000.00
                    enq: CT - global space management10220600.000.00
                    JS kill job wait2100210010.000.00
                    enq: CT - CTWR process start/stop10215160.000.00
                    enq: TQ - DDL contention2015070.000.00
                    enq: CT - local space management2013420.000.00
                    latch free1270030.020.00
                    library cache: mutex X7,9340001.530.00
                    latch: call allocation1740010.030.00
                    latch: redo writing2540000.050.00
                    db file scattered read120060.000.00
                    ADR file lock1480000.030.00
                    LGWR wait for redo copy1,0020000.190.00
                    asynch descriptor resize3,920100000.760.00
                    latch: messages450010.010.00
                    SQL*Net more data to client1,4350000.280.00
                    Parameter File I/O80020.000.00
                    latch: redo allocation400000.010.00
                    SQL*Net message to client7,8270001.510.00
                    SQL*Net more data from client1,4350000.280.00
                    latch: checkpoint queue latch590000.010.00
                    latch: cache buffers chains470000.010.00
                    latch: cache buffers lru chain340000.010.00
                    ARCH wait for archivelog lock4170000.080.00
                    latch: row cache objects50000.000.00
                    rdbms ipc message1,096,137331,197,3351092211.20
                    EMON slave idle wait75,597100378,050500114.57
                    Space Manager: slave idle wait49,17292234,49547699.47
                    Streams AQ: qmn slave idle wait31,9610225,57370586.16
                    DIAG idle wait151,012100151,152100129.10
                    wait for unread message on broadcast channel95,52410095,568100018.41
                    Streams AQ: emn coordinator idle wait7,55910075,613100031.46
                    dispatcher timer1,26010075,612600090.24
                    Streams AQ: qmn coordinator idle wait23,2574675,61132514.48
                    shared server idle wait2,51910075,593300090.49
                    pmon timer90,3162475,47583617.40
                    Streams AQ: waiting for time management or cleanup tasks25075,245376223930.00
                    smon timer8421367,996807560.16
                    VKRM Idle1014,399143991110.00
                    SQL*Net message from client10,61002,8312672.04
                    class slave wait46302996460.09
                    KSV master wait3001620.00
                    • % of Waits: value of .0 indicates value was <.05%; value of null is truly 0
                    • % of Waits: column heading of <=1s is truly <1024ms, >1s is truly >=1024ms
                    • Ordered by Event (idle events last)
                    % of Waits
                    EventTotal Waits<1ms<2ms<4ms<8ms<16ms<32ms<=1s>1s
                    ADR block file read183712.510.91.84.011.116.837.85.1
                    ADR block file write221 5.011.828.149.85.4
                    ADR file lock27499.6 .4
                    ARCH wait for archivelog lock420100.0
                    ASM file metadata operation6100.0
                    CSS initialization6 66.7 33.3
                    Data file init write17.6K7.291.6.1.0.0.1.9.1
                    Datapump dump file I/O108.2K52.48.01.84.812.812.57.6.1
                    Disk file operations I/O42.5K75.3.0.01.76.33.413.0.2
                    JS kill job wait2 100.0
                    LGWR wait for redo copy100299.7.1 .1.1
                    Log archive I/O81.6K2.896.4.0.0.2.3.3
                    Log file init write2313.499.6
                    Parameter File I/O1070.0 20.0 10.0
                    RMAN backup & recovery I/O271.9K10.831.3.61.34.57.743.2.6
                    SQL*Net break/reset to client274198.11.8.1 .0
                    SQL*Net message to client356.1K100.0
                    SQL*Net more data from client1916100.0
                    SQL*Net more data to client10.9K99.9.1
                    Wait for shrink lock2142.3K 100.0
                    asynch descriptor resize4752100.0
                    buffer busy waits84732.5.1.0.716.925.849.24.7
                    buffer deadlock465100.0
                    change tracking file synchronous read585197.6.1.1.2.3.8.8.1
                    change tracking file synchronous write1718 .18.79.526.015.435.64.7
                    control file parallel write73.4K .017.537.842.42.2
                    control file sequential read4712.8K100.0.0.0.0.0.0.0.0
                    control file single write1166.9.93.418.122.418.129.3.9
                    cursor: pin S14100.0
                    cursor: pin S wait on X1 100.0
                    db file async I/O submit88.9K .0.511.921.49.352.34.6
                    db file parallel write1684.7.1.55.315.516.753.47.8
                    db file scattered read40131.3.0.0.1.64.193.5.3
                    db file sequential read131.7K83.8.8.31.23.03.56.41.1
                    db file single write14.1K .73.34.87.810.572.5.3
                    direct path read7875.6 1.39.05.19.0
                    direct path read temp372.1K83.9.81.03.35.73.02.1.1
                    direct path sync4015.0 2.580.02.5
                    direct path write103.6K84.9.71.63.65.13.5.6
                    direct path write temp323.9K.0.01.228.521.812.435.9.1
                    enq: CF - contention28695.1.9.2.6.92.582.67.2
                    enq: CI - contention7724.7 15.659.7
                    enq: CR - block range reuse ckpt55 5.523.643.627.3
                    enq: CT - CTWR process start/stop1 100.0
                    enq: CT - global space management1 100.0
                    enq: CT - local space management2 100.0
                    enq: HW - contention3411.8 2.92.982.4
                    enq: JD - contention8 100.0
                    enq: JS - queue lock405.0 2.5 92.5
                    enq: KO - fast object checkpoint2 100.0
                    enq: RO - fast object reuse5641.1 1.83.6 12.541.1
                    enq: TC - contention2 100.0
                    enq: TM - contention2 50.0 50.0
                    enq: TQ - DDL contention3 100.0
                    enq: TX - row lock contention683.3 16.7
                    latch free91386.19.3.14.2.3
                    latch: In memory undo latch8100.0
                    latch: cache buffers chains109100.0
                    latch: cache buffers lru chain34100.0
                    latch: call allocation17485.19.81.14.0
                    latch: checkpoint queue latch59100.0
                    latch: enqueue hash chains2100.0
                    latch: messages63160.222.817.0
                    latch: object queue header operation1100.0
                    latch: redo allocation31087.112.3 .6
                    latch: redo writing777493.25.01.5.3
                    latch: row cache objects1291.78.3
                    latch: shared pool69100.0
                    library cache load lock1 100.0
                    library cache lock250.0 50.0
                    library cache: mutex X824399.6.0 .3.0
                    local write wait69.5K .68.832.310.646.51.2
                    log buffer space1813.1.1 .4.91.452.145.1
                    log file parallel write16.8K .0.44.712.513.757.011.8
                    log file sequential read83.5K99.8.0.0.0.0.0.1
                    log file single write7691.2 .552.416.527.42.0
                    log file switch (checkpoint incomplete)3532 18.981.1
                    log file switch (private strand flush incomplete)16 56.343.8
                    log file switch completion1771.7 45.253.1
                    log file sync3927 .62.717.415.943.819.6
                    os thread startup808 96.43.5.1
                    process terminate1 100.0
                    rdbms ipc reply32.7K76.8 .0.01.7.71.119.7
                    read by other session2424.51.21.23.36.211.656.215.7
                    recovery area: computing backed up files1 100.0
                    recovery area: computing dropped files1 100.0
                    recovery area: computing obsolete files1 100.0
                    reliable message161265.06.64.62.420.5.4.4
                    row cache lock812.5 12.5 75.0
                    switch logfile command2 100.0
                    undo segment extension1523.9 1.32.092.8
                    utl_file I/O126799.9 .1
                    DIAG idle wait151K 100.0
                    EMON slave idle wait75.6K 100.0
                    KSV master wait3 100.0
                    PL/SQL lock timer887 100.0
                    SQL*Net message from client358.9K9.541.117.22.6.7.415.912.6
                    Space Manager: slave idle wait49.1K.1.0.1.0.1.12.397.3
                    Streams AQ: emn coordinator idle wait7559 100.0
                    Streams AQ: qmn coordinator idle wait23.3K54.1 .0.0.0 .045.9
                    Streams AQ: qmn slave idle wait31.8K 100.0
                    Streams AQ: waiting for time management or cleanup tasks2 50.050.0
                    VKRM Idle1 100.0
                    class slave wait2950100.0 .0
                    dispatcher timer1260 100.0
                    jobq slave wait21.1K.0 100.0
                    pmon timer90.3K8.2.2.4.61.21.662.026.0
                    rdbms ipc message1096.1K16.4.5.4.51.61.960.618.1
                    shared server idle wait2519 100.0
                    smon timer8422.31.1.6.8.81.423.070.0
                    wait for unread message on broadcast channel244K.0.0.0.0.0.0100.0
                    • Units for % of Total Waits: ms is milliseconds s is 1024 milliseconds (approximately 1 second)
                    • % of Total Waits: total waits for all wait classes, including Idle 
                    • % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
                    • Ordered by Event (only non-idle events are displayed)
                    % of Total Waits
                    EventWaits 64ms to 2s<32ms<64ms<1/8s<1/4s<1/2s<1s<2s>=2s
                    ADR block file read75157.017.69.56.42.22.13.02.1
                    ADR block file write11644.818.111.311.37.21.82.72.7
                    ADR file lock199.6.4
                    CSS initialization266.7 16.716.7
                    Data file init write17098.9.4.4.1.0.0.0.1
                    Datapump dump file I/O828392.35.31.5.6.2.1.1.0
                    Disk file operations I/O557886.810.51.8.5.2.1.1.1
                    JS kill job wait2 100.0
                    Log archive I/O20899.7.2.0.0.0
                    Parameter File I/O190.010.0
                    RMAN backup & recovery I/O118.3K56.213.019.48.32.1.5.3.3
                    Wait for shrink lock2142.3K 100.0
                    buffer busy waits420146.032.66.43.6.75.9.34.4
                    change tracking file synchronous read4899.1.5.2.1.0 .0.0
                    change tracking file synchronous write67759.711.110.05.64.44.53.8.9
                    control file parallel write31.9K55.420.111.07.92.41.01.01.2
                    control file sequential read260100.0.0.0.0.0.0.0.0
                    control file single write3569.815.57.82.62.6.9.9
                    db file async I/O submit47K43.233.910.24.62.7.9.64.0
                    db file parallel write97838.716.414.912.46.23.54.63.2
                    db file scattered read37576.25.714.761.611.3.3.1.2
                    db file sequential read928192.52.91.8.7.5.5.6.4
                    db file single write10.2K27.255.514.51.5.8.1.1.2
                    direct path read791.06.42.6
                    direct path read temp808497.81.0.4.3.2.1.1.0
                    direct path sync3317.510.037.515.010.07.52.5
                    direct path write58999.4.4.1.1.0
                    direct path write temp116.5K64.028.56.5.7.3.0.0.1
                    enq: CF - contention243610.27.416.226.822.49.72.34.9
                    enq: CI - contention1524.71.31.35.22.65.23.955.8
                    enq: CR - block range reuse ckpt2529.17.3 7.318.210.91.825.5
                    enq: CT - CTWR process start/stop1 100.0
                    enq: CT - global space management1 100.0
                    enq: CT - local space management2 50.0 50.0
                    enq: HW - contention114.7 2.9 82.4
                    enq: RO - fast object reuse946.4 1.83.67.13.637.5
                    enq: TM - contention150.0 50.0
                    enq: TQ - DDL contention3 66.733.3
                    enq: TX - row lock contention183.3 16.7
                    library cache load lock1 100.0
                    library cache lock150.0 50.0
                    local write wait32.4K52.338.86.11.1.4.1.11.1
                    log buffer space12902.83.15.110.715.917.319.126.0
                    log file parallel write10.6K31.316.114.110.19.37.46.05.8
                    log file sequential read8599.9.0.0.0.0.0
                    log file single write22170.615.09.91.7.5.41.3.7
                    log file switch (checkpoint incomplete)731 .16.95.05.71.31.879.3
                    log file switch (private strand flush incomplete)12 6.318.831.318.825.0
                    log file switch completion1241.7 5.15.614.719.824.928.2
                    log file sync191936.511.310.79.36.85.75.014.6
                    os thread startup2996.41.61.0.7.1 .1
                    process terminate1 100.0
                    rdbms ipc reply679279.3.1.1.2.3.419.7
                    read by other session17128.110.36.69.513.616.114.51.2
                    recovery area: computing obsolete files1 100.0
                    reliable message799.6 .1.1.1.1
                    undo segment extension1417.21.31.31.31.387.5
                    • Units for % of Total Waits: s is 1024 milliseconds (approximately 1 second) m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes)
                    • % of Total Waits: total waits for all wait classes, including Idle 
                    • % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
                    • Ordered by Event (only non-idle events are displayed)
                    % of Total Waits
                    EventWaits 4s to 2m<2s<4s<8s<16s<32s< 1m< 2m>=2m
                    ADR block file read3897.92.1
                    ADR block file write697.31.8 .5.5
                    Data file init write1799.9.0.1.0
                    Datapump dump file I/O53100.0.0
                    Disk file operations I/O3699.9.0.0.0.0 .0
                    RMAN backup & recovery I/O82899.7.3.0
                    buffer busy waits31895.6.2.2.3.61.11.4.6
                    change tracking file synchronous read2100.0.0 .0
                    change tracking file synchronous write1699.1.9
                    control file parallel write86098.81.0.1.0.0
                    control file sequential read30100.0.0.0.0.0
                    db file async I/O submit355996.0.4.3.4.72.1.1.0
                    db file parallel write5496.83.0.2
                    db file scattered read899.8.1.0.0
                    db file sequential read54899.6.4
                    db file single write2899.8.2.0.0
                    direct path read temp182100.0.0
                    direct path write temp21099.9.1.0.0.0.0
                    enq: CF - contention14095.12.31.7.9
                    enq: CI - contention4344.23.97.814.311.716.91.3
                    enq: CR - block range reuse ckpt1074.53.6 3.63.67.37.3
                    enq: HW - contention2717.6 14.75.932.414.711.82.9
                    enq: JD - contention7 12.5 25.050.012.5
                    enq: JS - queue lock377.5 92.5
                    enq: KO - fast object checkpoint2 50.050.0
                    enq: RO - fast object reuse2162.51.819.6 5.410.7
                    local write wait76698.9.1.1.1.2.4.1.0
                    log buffer space47274.017.66.21.8.4
                    log file parallel write96894.24.6.9.3.1
                    log file single write599.3.7
                    log file switch (checkpoint incomplete)225020.72.42.84.810.017.826.015.6
                    log file switch (private strand flush incomplete)475.018.86.3
                    log file switch completion5071.820.35.12.8
                    log file sync57385.49.93.1.7.8.1
                    read by other session398.81.2
                    row cache lock525.0 12.5 25.025.012.5
                    switch logfile command1 50.0 50.0
                    • Units for % of Total Waits: m is 64*1024 milliseconds (approximately 67 seconds or 1.1 minutes) h is 4096*1024 milliseconds (approximately 70 minutes or 1.17 hours)
                    • % of Total Waits: total waits for all wait classes, including Idle 
                    • % of Total Waits: value of .0 indicates value was <.05%; value of null is truly 0
                    • Ordered by Event (only non-idle events are displayed)
                    % of Total Waits
                    EventWaits 4m to 1h<2m<4m<8m<1/4h<1/2h< 1h>=1h
                    buffer busy waits5599.4.6.1
                    db file async I/O submit21100.0.0.0
                    enq: CR - block range reuse ckpt492.75.5 1.8
                    enq: HW - contention197.1 2.9
                    enq: JD - contention187.512.5
                    local write wait8100.0.0
                    log file switch (checkpoint incomplete)55184.414.21.1.3
                    row cache lock187.512.5
                    switch logfile command150.050.0
                    Service NameDB Time (s)DB CPU (s)Physical Reads (K)Logical Reads (K)
                    SYS$USERS218,1725548,88781,192
                    resbak5025112,719
                    SYS$BACKGROUND0021,081
                    resbakXDB0000

                    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 seconds
                    Service NameUser I/O Total WtsUser I/O Wt TimeConcurcy Total WtsConcurcy Wt TimeAdmin Total WtsAdmin Wt TimeNetwork Total WtsNetwork Wt Time
                    SYS$USERS1156404932999304321833568723
                    resbak61352210021930
                    SYS$BACKGROUND10750254824616710259942200

                    Back to Top

                    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
                    • %Total - Elapsed Time as a percentage of Total DB time
                    • %CPU - CPU Time as a percentage of Elapsed Time
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Captured SQL account for 143.1% of Total DB Time (s): 218,674
                    • Captured PL/SQL account for 66.8% of Total DB Time (s): 218,674
                    Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IOSQL IdSQL ModuleSQL Text
                    145,516.68136,4671.0766.540.032.60gtpm09m19whtmJDBC Thin Client select t.year_mark from SD_RES...
                    127,280.70342,426.9058.214.1837.50fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    72,098.630 32.974.3649.22402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    39,667.670 18.144.2925.9150x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    20,264.112,6457.669.270.030.0093qtvzkzbcjazJDBC Thin Client update RBK_RES_LOG t SET res_i...
                    14,381.63114,381.636.580.110.016mcpb06rctk0xDBMS_SCHEDULER call dbms_space.auto_space_adv...
                    10,861.840 4.973.3916.057rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    10,791.540 4.930.090.00c1tr6c5pjscx8DBMS_SCHEDULER alter table "RESBAK"."SD_RES_I...
                    2,745.8212222.511.260.030.00aq8yqxyyb40nn  update sys.job$ set this_date=...
                    2,213.777922.801.010.040.000kkhhb2w93cx0  update seg$ set type#=:4, bloc...
                    • %Total - CPU Time as a percentage of Total DB CPU
                    • %CPU - CPU Time as a percentage of Elapsed Time
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Captured SQL account for 934.1% of Total CPU Time (s): 605
                    • Captured PL/SQL account for 919.9% of Total CPU Time (s): 605
                    CPU Time (s)Executions CPU per Exec (s)%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
                    5,316.2431,772.08878.55127,280.704.1837.50fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    3,143.940 519.5672,098.634.3649.22402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    1,701.790 281.2339,667.674.2925.9150x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    368.560 60.9110,861.843.3916.057rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    72.60172.6012.001,379.735.2653.90b6usrg82hwsa3DBMS_SCHEDULER call dbms_stats.gather_databas...
                    50.29136,4670.008.31145,516.680.032.60gtpm09m19whtmJDBC Thin Client select t.year_mark from SD_RES...
                    47.60630.767.8747.6699.890.001sv9phuj08z8bemagent_SQL_oracle_database DECLARE CURSOR l_sta_cur IS S...
                    47.58630.767.8647.6399.890.000uuczutvk6jqjemagent_SQL_oracle_database SELECT SGA_SIZE_FACTOR AS FACT...
                    40.21140.216.64777.265.1795.3036n0axhp4gvktDBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
                    31.971520.215.2834.8991.620.00g57kbmvd1gqfkEM Realtime Connection select dbms_sqltune.report_sql...
                    31.56830.385.2231.6199.850.00bgkytnb68g9v2emagent_SQL_oracle_database WITH ash_rows AS (SELECT inst...
                    29.23420.704.8329.2499.970.00bunssq950snhf  insert into wrh$_sga_target_ad...
                    19.65119.653.25193.1310.180.335g184bhk666d9  BEGIN SYS.KUPM$MCP.MAIN('SYS_...
                    18.96111.723.1317.56107.990.008c0syt8yv0qvnudi@cq-hbk-dn-100 (TNS V1-V3) BEGIN SYS.DBMS_DATAPUMP.GET_ST...
                    18.6714,8590.003.0815.12123.480.007wn3wubg7gjdsudi@cq-hbk-dn-100 (TNS V1-V3) BEGIN :1 := sys.kupc$que_int.g...
                    18.46118.463.051,379.331.3413.095zruc4v6y32f9DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    18.0014,8660.002.9814.02128.380.00bjf05cwcj5s6pData Pump Master BEGIN :1 := sys.kupc$que_int.r...
                    17.551520.122.9018.2296.340.009kddcyq74sg9mEM Realtime Connection SELECT XMLELEMENT( "sql_monito...
                    16.5028.252.7384.9819.411.60df6hkcusm1q32SQL Developer select output from table(dbms...
                    15.35115.352.5414,381.630.110.016mcpb06rctk0xDBMS_SCHEDULER call dbms_space.auto_space_adv...
                    11.96111.961.98391.503.0537.434add9pqxrcy2gDBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    10.582,1910.001.7510.48100.940.00772s25v1y0x8k  select shared_pool_size_for_es...
                    10.240 1.6910,791.540.090.00c1tr6c5pjscx8DBMS_SCHEDULER alter table "RESBAK"."SD_RES_I...
                    8.956200.011.489.8091.280.00bzgpy0vxtvf63EM Realtime Connection SELECT event#, sql_id, sql_pla...
                    8.54180.471.418.5599.850.003vppx2gj138j7Admin Connection SELECT sql_id, sql_text from v...
                    8.341680.051.389.4688.090.00dbvkky621gqtrDBMS_SCHEDULER SELECT /*+ parallel */ EXTRACT...
                    8.1618.161.35150.975.4095.06dzj0698m8c71b  SELECT /* DS_SVC */ /*+ cursor...
                    7.6317.631.268.0494.862.283actxrs9155pvSQL Developer select output from table(dbms...
                    6.581310.051.096.58100.080.00abr26jpdfxwhyEM Realtime Connection SELECT APPENDCHILDXML(:B2 , '/...
                    6.222,6450.001.0320,264.110.030.0093qtvzkzbcjazJDBC Thin Client update RBK_RES_LOG t SET res_i...
                    • %Total - User I/O Time as a percentage of Total User I/O Wait time 
                    • %CPU - CPU Time as a percentage of Elapsed Time
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Captured SQL account for 98.5% of Total User I/O Wait Time (s): 53,184
                    • Captured PL/SQL account for 91.9% of Total User I/O Wait Time (s): 53,184
                    User I/O Time (s)Executions UIO per Exec (s)%TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
                    47,731.74315,910.5889.75127,280.704.1837.50fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    35,486.370 66.7272,098.634.3649.22402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    10,278.880 19.3339,667.674.2925.9150x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    3,787.22136,4670.037.12145,516.680.032.60gtpm09m19whtmJDBC Thin Client select t.year_mark from SD_RES...
                    1,742.840 3.2810,861.843.3916.057rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    743.611743.611.401,379.735.2653.90b6usrg82hwsa3DBMS_SCHEDULER call dbms_stats.gather_databas...
                    740.711740.711.39777.265.1795.3036n0axhp4gvktDBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
                    180.601180.600.341,379.331.3413.095zruc4v6y32f9DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    146.531146.530.28391.503.0537.434add9pqxrcy2gDBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    143.511143.510.27150.975.4095.06dzj0698m8c71b  SELECT /* DS_SVC */ /*+ cursor...
                    • %Total - Buffer Gets as a percentage of Total Buffer Gets
                    • %CPU - CPU Time as a percentage of Elapsed Time
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Total Buffer Gets: 84,991,184
                    • Captured SQL account for 93.3% of Total
                    Buffer Gets ExecutionsGets per Exec %TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
                    74,217,807324,739,269.0087.32127,280.704.237.5fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    32,257,0270 37.9572,098.634.449.2402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    26,078,5330 30.6839,667.674.325.950x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    10,813,3590 12.7210,861.843.4167rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    2,534,06921,267,034.502.9884.9819.41.6df6hkcusm1q32SQL Developer select output from table(dbms...
                    1,817,94611,817,946.002.141,379.331.313.15zruc4v6y32f9DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    1,596,48411,596,484.001.881,379.735.353.9b6usrg82hwsa3DBMS_SCHEDULER call dbms_stats.gather_databas...
                    1,561,72115210,274.481.8434.8991.60g57kbmvd1gqfkEM Realtime Connection select dbms_sqltune.report_sql...
                    1,362,7253045,424.171.605.4899.6.1c6thn3wsmvf5bDBMS_SCHEDULER /* SQL Analyze(46, 1) */ selec...
                    574,2351523,777.860.6818.2296.309kddcyq74sg9mEM Realtime Connection SELECT XMLELEMENT( "sql_monito...
                    • %CPU - CPU Time as a percentage of Elapsed Time
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Total Disk Reads: 8,890,013
                    • Captured SQL account for 97.2% of Total
                    Physical ReadsExecutionsReads per Exec %TotalElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
                    8,282,66332,760,887.6793.17127,280.704.1837.50fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    4,179,6930 47.0272,098.634.3649.22402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    2,838,7330 31.9339,667.674.2925.9150x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    1,021,9800 11.5010,861.843.3916.057rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    501,6691501,669.005.641,379.735.2653.90b6usrg82hwsa3DBMS_SCHEDULER call dbms_stats.gather_databas...
                    500,9251500,925.005.63777.265.1795.3036n0axhp4gvktDBMS_SCHEDULER /* SQL Analyze(1) */ select /*...
                    45,817145,817.000.521,379.331.3413.095zruc4v6y32f9DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    45,197145,197.000.51391.503.0537.434add9pqxrcy2gDBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    45,094145,094.000.51150.975.4095.06dzj0698m8c71b  SELECT /* DS_SVC */ /*+ cursor...
                    41,466192,182.420.47833.430.3015.3837tjpa7cc8m01DBMS_SCHEDULER /* SQL Analyze(46, 1) */ SELEC...
                    • %Opt - Optimized Reads as percentage of SQL Read Requests
                    • %Total - UnOptimized Read Reqs as a percentage of Total UnOptimized Read Reqs
                    • Total Physical Read Requests: 483,228
                    • Captured SQL account for 144.9% of Total
                    • Total UnOptimized Read Requests: 483,228
                    • Captured SQL account for 144.9% of Total
                    • Total Optimized Read Requests: 1
                    • Captured SQL account for 0.0% of Total
                    UnOptimized Read ReqsPhysical Read ReqsExecutionsUnOptimized Reqs per Exec%Opt%TotalSQL IdSQL ModuleSQL Text
                    604,941604,9413201,647.000.00125.19fxdsc6fs4gvn8  BEGIN SYS.KUPW$WORKER.MAIN('S...
                    331,727331,7270 0.0068.65402y1jxgjsnmnData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    209,735209,7350 0.0043.4050x1zv3xgs6mkData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    51,47651,476151,476.000.0010.655zruc4v6y32f9DBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    48,29048,2900 0.009.997rfxjfcg35pffData Pump Worker INSERT /*+ APPEND PARALLEL("SD...
                    45,24845,248145,248.000.009.364add9pqxrcy2gDBMS_SCHEDULER DECLARE job BINARY_INTEGER := ...
                    45,09445,094145,094.000.009.33dzj0698m8c71b  SELECT /* DS_SVC */ /*+ cursor...
                    42,19842,198192,220.950.008.7337tjpa7cc8m01DBMS_SCHEDULER /* SQL Analyze(46, 1) */ SELEC...
                    4,9804,9808360.000.001.03196mqnmxgxpv1emagent_SQL_oracle_database select sysStat.branchnodespli...
                    4,3684,36831214.000.000.90424h0nf7bhqzdDBMS_SCHEDULER SELECT sqlset_row(sql_id, for...
                    • %IO - User I/O Time as a percentage of Elapsed Time
                    • Total Executions: 638,355
                    • Captured SQL account for 62.1% of Total
                    Executions Rows ProcessedRows per ExecElapsed Time (s)%CPU%IOSQL IdSQL ModuleSQL Text
                    136,46740,2990.30145,516.6802.6gtpm09m19whtmJDBC Thin Client select t.year_mark from SD_RES...
                    97,15950,4730.523.465.90bq13r6h99ddbzJDBC Thin Client select * from RESOURCE_PATH t ...
                    38,45836,8950.963.2313.9063811ut8prwf6JDBC Thin Client select * from rbk_base_resourc...
                    14,866280.0014.02128.40bjf05cwcj5s6pData Pump Master BEGIN :1 := sys.kupc$que_int.r...
                    14,85929,7182.0015.12123.507wn3wubg7gjdsudi@cq-hbk-dn-100 (TNS V1-V3) BEGIN :1 := sys.kupc$que_int.g...
                    12,32712,3271.000.2393.307sx5p1ug5ag12DBMS_SCHEDULER SELECT SPARE4 FROM SYS.OPTSTAT...
                    11,01854,1094.911.791040cm5vu20fhtnq1  select /*+ connect_by_filterin...
                    10,4681000.01681.28005ms6rbzdnq16t  select job, nvl2(last_date, 1,...
                    8,59328,4053.310.9491.50bsa0wjtftg3uw  select file# from file$ where ...
                    6,1695,0980.830.5797.9096g93hntrzjtr  select /*+ rule */ bucket_cnt,...
                    • Captured SQL account for 33.2% of Total
                    Parse CallsExecutions % Total ParsesSQL IdSQL ModuleSQL Text
                    2,8772,8773.700k8522rmdzg4k  select privilege# from sysauth...
                    2,87711,0183.70cm5vu20fhtnq1  select /*+ connect_by_filterin...
                    2,7812,7813.580ws7ahf1d78qa  select SYS_CONTEXT('USERENV', ...
                    2,6902,6903.46459f3z9u4fb3u  select value$ from props$ wher...
                    2,6602,6603.425ur69atw3vfhj  select decode(failover_method,...
                    9823,0271.261w0c0jhzmp1hhJDBC Thin Client SELECT 1 from dual
                    958136,4671.23gtpm09m19whtmJDBC Thin Client select t.year_mark from SD_RES...
                    80638,4581.0463811ut8prwf6JDBC Thin Client select * from rbk_base_resourc...
                    2672,1730.346xvp6nxs4a9n4  select nvl(sum(space), 0) from...
                    2672,1730.34csnp95dz2r8ss  select file#, block# from recy...
                    Sharable Mem (b)Executions % TotalSQL IdSQL ModuleSQL Text
                    13,479,402550.369dhn1b8d88dpf  select OBJOID, CLSOID, RUNTIME...
                    11,366,256210.30317v5hnvvd49h  UPDATE wrh$_seg_stat_obj ob S...
                    10,245,830420.2761tssjb6hj8x7emagent_SQL_oracle_database SELECT SUM(broken), SUM(faile...
                    7,713,146210.216wm3n4d7bnddg  SELECT source, (case when tim...
                    7,483,3602750.20bn4b3vjw2mj3u  SELECT OBJOID, CLSOID, DECODE(...
                    7,328,598210.207yjzq240hcd2c  UPDATE /*+ INDEX(wrh$_seg_sta...
                    5,551,150120.1535y2u24f4rxqm  insert into wrh$_seg_stat_obj ...
                    4,376,713210.122a99kfa1dykuy  update wrh$_seg_stat_obj sso s...
                    3,608,714420.10bunssq950snhf  insert into wrh$_sga_target_ad...
                    2,855,773210.087v80b35m1n54u  insert into wrh$_pga_target_ad...
                    2,670,822170.0786w49kdq48gk9  SELECT STATUS#, HOW_CREATED, L...
                    2,599,7324,0430.073c1kubcdjnppq  update sys.col_usage$ set equa...
                    2,383,937180.069s7b2wt0yxwfm  INSERT INTO WRI$_ADV_ADDM_TASK...
                    2,383,735240.06fvcn4a3p0k3qj  INSERT INTO SYS.WRI$_ADV_EXECU...
                    2,007,906100.057f5pj3jtqavnn  SELECT A.DATABASE_TIME, ( CAST...
                    1,814,51010,4680.055ms6rbzdnq16t  select job, nvl2(last_date, 1,...
                    1,683,890360.04bqfx5q2jas08uemagent_SQL_oracle_database SELECT TASK_LIST.TASK_ID FROM ...

                    Back to SQL Statistics
                    Back to Top

                    SQL ordered by Version Count

                    • Only Statements with Version Count greater than 20 are displayed
                    Version Count Executions SQL IdSQL ModuleSQL Text
                    974,0433c1kubcdjnppq  update sys.col_usage$ set equa...
                    96217v80b35m1n54u  insert into wrh$_pga_target_ad...
                    96217yjzq240hcd2c  UPDATE /*+ INDEX(wrh$_seg_sta...
                    96217yjzq240hcd2c  UPDATE /*+ INDEX(wrh$_seg_sta...
                    95212a99kfa1dykuy  update wrh$_seg_stat_obj sso s...
                    9521317v5hnvvd49h  UPDATE wrh$_seg_stat_obj ob S...
                    941786w49kdq48gk9  SELECT STATUS#, HOW_CREATED, L...
                    931235y2u24f4rxqm  insert into wrh$_seg_stat_obj ...
                    93107f5pj3jtqavnn  SELECT A.DATABASE_TIME, ( CAST...
                    93189s7b2wt0yxwfm  INSERT INTO WRI$_ADV_ADDM_TASK...
                    9324fvcn4a3p0k3qj  INSERT INTO SYS.WRI$_ADV_EXECU...
                    76216wm3n4d7bnddg  SELECT source, (case when tim...
                    65122aq8yqxyyb40nn  update sys.job$ set this_date=...
                    6210,4685ms6rbzdnq16t  select job, nvl2(last_date, 1,...
                    62275bn4b3vjw2mj3u  SELECT OBJOID, CLSOID, DECODE(...
                    61559dhn1b8d88dpf  select OBJOID, CLSOID, RUNTIME...
                    5412mp99nzd9u1qp  delete from histgrm$ where obj...
                    544261tssjb6hj8x7emagent_SQL_oracle_database SELECT SUM(broken), SUM(faile...
                    5336bqfx5q2jas08uemagent_SQL_oracle_database SELECT TASK_LIST.TASK_ID FROM ...
                    5336bqfx5q2jas08uemagent_SQL_oracle_database SELECT TASK_LIST.TASK_ID FROM ...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...
                    46360ctk7jpux5chmemagent_SQL_oracle_database WITH RECINFO AS ( SELECT /*+ N...

                    Back to Top

                    StatisticTotalper Secondper Trans
                    Batched IO (bound) vector count620.000.01
                    Batched IO (full) vector count00.000.00
                    Batched IO block miss count1860.000.04
                    Batched IO buffer defrag count00.000.00
                    Batched IO double miss count10.000.00
                    Batched IO same unit count1240.000.02
                    Batched IO single block count620.000.01
                    Batched IO vector block count00.000.00
                    Batched IO vector read count00.000.00
                    Block Cleanout Optim referenced870.000.02
                    CCursor + sql area evicted640.000.01
                    CPU used by this session612,7458.10118.06
                    CPU used when call started39,2670.527.57
                    CR blocks created4,9320.070.95
                    Cached Commit SCN referenced555,9597.35107.12
                    Commit SCN cached3,6490.050.70
                    DB time28,664,658379.105,523.06
                    DBWR checkpoint buffers written10,249,724135.561,974.90
                    DBWR checkpoints4350.010.08
                    DBWR object drop buffers written330.000.01
                    DBWR parallel query checkpoint buffers written00.000.00
                    DBWR revisited being-written buffer30.000.00
                    DBWR tablespace checkpoint buffers written389,6145.1575.07
                    DBWR thread checkpoint buffers written8,167,975108.021,573.79
                    DBWR transaction table writes1,2610.020.24
                    DBWR undo block writes7,324,95396.871,411.36
                    DDL statements parallelized00.000.00
                    DFO trees parallelized00.000.00
                    HSC Heap Segment Block Changes204,0332.7039.31
                    HSC IDL Compressed Blocks00.000.00
                    Heap Segment Array Inserts3,6080.050.70
                    Heap Segment Array Updates8940.010.17
                    IMU CR rollbacks10.000.00
                    IMU Flushes6,1940.081.19
                    IMU Redo allocation size1,315,32017.40253.43
                    IMU commits2,8960.040.56
                    IMU contention1060.000.02
                    IMU ktichg flush100.000.00
                    IMU pool not allocated25,0650.334.83
                    IMU recursive-transaction flush30.000.00
                    IMU undo allocation size7,360,44097.341,418.20
                    IMU- failed to get a private strand25,0650.334.83
                    LOB table id lookup cache misses00.000.00
                    Misses for writing mapping00.000.00
                    Number of read IOs issued780.000.02
                    PX local messages recv'd00.000.00
                    PX local messages sent00.000.00
                    Parallel operations not downgraded00.000.00
                    Requests to/from client346,9974.5966.86
                    RowCR - row contention50.000.00
                    RowCR attempts40,3580.537.78
                    RowCR hits40,3600.537.78
                    SMON posted for dropping temp segment20.000.00
                    SMON posted for undo segment recovery00.000.00
                    SMON posted for undo segment shrink40.000.00
                    SQL*Net roundtrips to/from client349,1324.6267.27
                    TBS Extension: tasks created3360.000.06
                    active txn count during cleanout2,550,67733.73491.46
                    application wait time98,1181.3018.91
                    auto extends on undo tablespace3360.000.06
                    background checkpoints completed3800.010.07
                    background checkpoints started3800.010.07
                    background timeouts368,7454.8871.05
                    branch node splits9,1040.121.75
                    buffer is not pinned count1,807,75123.91348.31
                    buffer is pinned count2,508,29833.17483.29
                    bytes received via SQL*Net from client38,178,870504.937,356.24
                    bytes sent via SQL*Net to client175,056,4162,315.1833,729.56
                    calls to get snapshot scn: kcmgss4,903,04664.84944.71
                    calls to kcmgas10,066,395133.131,939.58
                    calls to kcmgcs2,723,30636.02524.72
                    cell physical IO interconnect bytes816,623,934,46410,800,127.30157,345,652.11
                    change write time336,7834.4564.89
                    cleanout - number of ktugct calls2,563,69433.91493.97
                    cleanouts and rollbacks - consistent read gets3110.000.06
                    cleanouts only - consistent read gets59,0000.7811.37
                    cluster key scan block gets117,9921.5622.73
                    cluster key scans71,7090.9513.82
                    commit batch performed00.000.00
                    commit batch requested00.000.00
                    commit batch/immediate performed1,9510.030.38
                    commit batch/immediate requested1,9510.030.38
                    commit cleanout failures: block lost115,3551.5322.23
                    commit cleanout failures: buffer being written50.000.00
                    commit cleanout failures: callback failure2540.000.05
                    commit cleanout failures: cannot pin510.000.01
                    commit cleanouts5,705,38375.461,099.30
                    commit cleanouts successfully completed5,589,71873.931,077.02
                    commit immediate performed1,9510.030.38
                    commit immediate requested1,9510.030.38
                    commit txn count during cleanout2,506,49933.15482.95
                    concurrency wait time2,903,89638.41559.52
                    consistent changes1,206,58115.96232.48
                    consistent gets11,387,395150.602,194.10
                    consistent gets - examination4,223,82955.86813.84
                    consistent gets direct1860.000.04
                    consistent gets from cache11,387,209150.602,194.07
                    consistent gets from cache (fastpath)6,923,55191.571,334.02
                    cursor authentications1,5430.020.30
                    data blocks consistent reads - undo records applied12,5410.172.42
                    db block changes59,275,946783.9411,421.18
                    db block gets73,603,788973.4314,181.85
                    db block gets direct3,306,31443.73637.05
                    db block gets from cache70,297,474929.7113,544.79
                    db block gets from cache (fastpath)14,495,392191.712,792.95
                    deferred (CURRENT) block cleanout applications21,0360.284.05
                    dirty buffers inspected185,7642.4635.79
                    enqueue conversions46,8800.629.03
                    enqueue releases4,870,56564.41938.45
                    enqueue requests5,014,28966.32966.14
                    enqueue timeouts143,7321.9027.69
                    enqueue waits3,1580.040.61
                    exchange deadlocks4650.010.09
                    execute count638,3558.44123.00
                    failed probes on index block reclamation00.000.00
                    file io service time24,093,942318.654,642.38
                    file io wait time142,241,984,8571,881,198.2827,406,933.50
                    free buffer inspected7,331,90896.971,412.70
                    free buffer requested10,675,547141.192,056.95
                    global undo segment hints helped240.000.00
                    global undo segment hints were stale20.000.00
                    heap block compress1,7760.020.34
                    hot buffers moved to head of LRU568,1517.51109.47
                    immediate (CR) block cleanout applications59,3110.7811.43
                    immediate (CURRENT) block cleanout applications4,895,69564.75943.29
                    in call idle wait time282,609,6833,737.6154,452.73
                    index crx upgrade (positioned)49,2130.659.48
                    index fast full scans (full)1,1540.020.22
                    index fetch by key1,004,42613.28193.53
                    index scans kdiixs1354,6264.6968.33
                    leaf node 90-10 splits2,437,35332.23469.62
                    leaf node splits2,437,83032.24469.72
                    lob reads359,8934.7669.34
                    lob writes579,6897.67111.69
                    lob writes unaligned579,6897.67111.69
                    logical read bytes from cache669,157,113,8568,849,828.80128,932,006.52
                    logons cumulative3,7630.050.73
                    max cf enq hold time90,9001.2017.51
                    messages received807,57810.68155.60
                    messages sent807,56610.68155.60
                    min active SCN optimization applied on CR2960.000.06
                    no buffer to keep pinned count00.000.00
                    no work - consistent read gets3,534,95646.75681.11
                    non-idle wait count12,834,033169.732,472.84
                    non-idle wait time54,865,382725.6110,571.36
                    opened cursors cumulative296,1593.9257.06
                    parse count (describe)900.000.02
                    parse count (failures)1,6540.020.32
                    parse count (hard)5,3110.071.02
                    parse count (total)77,7021.0314.97
                    parse time cpu2,1610.030.42
                    parse time elapsed44,3820.598.55
                    physical read IO requests483,2286.3993.11
                    physical read bytes72,826,986,496963,161.4914,032,174.66
                    physical read total IO requests5,579,33273.791,075.02
                    physical read total bytes354,308,258,8164,685,846.3768,267,487.25
                    physical read total multi block requests613,4608.11118.20
                    physical reads8,890,013117.571,712.91
                    physical reads cache609,2638.06117.39
                    physical reads cache prefetch498,2796.5996.01
                    physical reads direct8,280,750109.521,595.52
                    physical reads direct (lob)780.000.02
                    physical reads direct temporary tablespace8,280,564109.511,595.48
                    physical reads prefetch warmup00.000.00
                    physical write IO requests8,156,377107.871,571.56
                    physical write bytes175,793,635,3282,324,930.1933,871,606.04
                    physical write total IO requests8,610,446113.881,659.05
                    physical write total bytes462,315,675,6486,114,280.9389,078,164.86
                    physical write total multi block requests741,8219.81142.93
                    physical writes21,459,184283.804,134.72
                    physical writes direct10,888,028144.002,097.89
                    physical writes direct (lob)2330.000.04
                    physical writes direct temporary tablespace7,582,787100.281,461.04
                    physical writes from cache10,571,156139.812,036.83
                    physical writes non checkpoint21,097,527279.024,065.03
                    pinned buffers inspected9400.010.18
                    prefetch clients - default00.000.00
                    prefetch warmup blocks aged out before use00.000.00
                    prefetch warmup blocks flushed out before use00.000.00
                    prefetched blocks aged out before use60,9280.8111.74
                    process last non-idle time75,7141.0014.59
                    recursive calls1,591,98821.05306.74
                    recursive cpu usage639,8488.46123.28
                    redo KB read82,925,6311,096.7215,977.96
                    redo blocks checksummed by FG (exclusive)149,354,0501,975.2628,777.27
                    redo blocks written165,847,2942,193.3931,955.16
                    redo buffer allocation retries27,6400.375.33
                    redo entries29,309,448387.635,647.29
                    redo log space requests2,452,35232.43472.51
                    redo log space wait time24,255,860320.794,673.58
                    redo ordering marks7,474,29198.851,440.13
                    redo size82,334,274,5961,088,898.5915,864,022.08
                    redo size for direct writes24,522,908324.324,725.03
                    redo subscn max counts9,866,531130.491,901.07
                    redo synch long waits2550.000.05
                    redo synch time367,6354.8670.84
                    redo synch time (usec)3,676,332,41948,620.74708,349.21
                    redo synch writes4,0650.050.78
                    redo wastage4,839,82864.01932.53
                    redo write time747,5889.89144.04
                    redo writes16,7820.223.23
                    rollback changes - undo records applied31,3120.416.03
                    rollbacks only - consistent read gets950.000.02
                    root node splits640.000.01
                    rows fetched via callback444,5785.8885.66
                    session connect time11,396,677150.722,195.89
                    session cursor cache hits176,4932.3334.01
                    session logical reads84,991,1841,124.0416,375.95
                    shared hash latch upgrades - no wait133,0181.7625.63
                    shared hash latch upgrades - wait2220.000.04
                    sorts (disk)480.000.01
                    sorts (memory)106,3751.4120.50
                    sorts (rows)893,653,32311,818.87172,187.54
                    sql area evicted2,6850.040.52
                    sql area purged1,8080.020.35
                    summed dirty queue length4,242,83256.11817.50
                    switch current to new buffer5,3380.071.03
                    table fetch by rowid1,663,37122.00320.50
                    table fetch continued row1,2020.020.23
                    table scan blocks gotten2,774,33936.69534.55
                    table scan rows gotten110,864,5341,466.2221,361.18
                    table scans (direct read)10.000.00
                    table scans (long tables)330.000.01
                    table scans (rowid ranges)80.000.00
                    table scans (short tables)77,5631.0314.94
                    total cf enq hold time4,409,09058.31849.54
                    total number of cf enq holders669,8138.86129.06
                    total number of times SMON posted7280.010.14
                    transaction rollbacks1,5020.020.29
                    transaction tables consistent read rollbacks00.000.00
                    transaction tables consistent reads - undo records applied00.000.00
                    undo change vector size38,981,087,400515,538.047,510,806.82
                    user I/O wait time5,318,33070.341,024.73
                    user calls377,3754.9972.71
                    user commits5,1840.071.00
                    user rollbacks60.000.00
                    workarea executions - onepass960.000.02
                    workarea executions - optimal76,6491.0114.77
                    write clones created in background69,7790.9213.44
                    write clones created in foreground1,7530.020.34

                    Back to Instance Activity Statistics
                    Back to Top

                    Instance Activity Stats - Absolute Values

                    • Statistics with absolute values (should not be diffed)
                    StatisticBegin ValueEnd Value
                    logons current7562
                    session uga memory max335,473,396,688337,761,383,072
                    session pga memory48,207,728,65646,541,865,184
                    session pga memory max72,606,143,15269,944,443,104
                    session cursor cache count3,739,2973,767,509
                    session uga memory123,560,730,232,144124,308,125,371,568
                    opened cursors current277110
                    StatisticTotalper Hour
                    log switches (derived)38018.09

                    Back to Instance Activity Statistics
                    Back to Top

                     

                    IO Stats

                    Back to Top

                    • ordered by (Data Read + Write) desc
                    Function NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Tm(ms)
                    Data Pump86.3G8.001.16894100.5G6.831.361501121.4K15.44
                    RMAN89.5G2.501.2121587.5G1.201.18468279K62.57
                    Others82.2G3.501.1132682.7G2.231.11951431.2K34.43
                    LGWR66.5G57.67.90094979.2G1.391.072574402.5K3.52
                    DBWR805M0.68.01064680.6G102.101.09128140.5K921.52
                    Buffer Cache Reads4.6G1.44.0627410M0.000M108.9K43.65
                    Direct Writes0M0.000M28M0.01.0003704311.90
                    Direct Reads1M0.00.0000130M0.000M786.62
                    TOTAL:330G73.794.46872430.5G113.765.829946484K30.74
                    • Small Read and Large Read are average service times, in milliseconds
                    • Ordered by (Data Read + Write) desc
                    Filetype NameReads: DataReqs per secData per secWrites: DataReqs per secData per secSmall ReadLarge Read
                    Data File55.4G2.91.750485105.3G102.621.4261236.5880.21
                    Log File79.1G1.101.0710581.3G1.321.101470.490.13
                    Temp File63.3G5.19.85779558.5G5.34.7916150.269.75
                    Archive Log37.8G1.02.51166179.1G1.071.071010.41116.95
                    Control File72.4G62.33.9804202.3G1.94.0310130.051.23
                    Data File Incremental Backup0M0.000M49.6G0.67.671728
                    Archive Log Backup0M0.000M37.8G0.51.511648
                    Data Pump Dump File21.1G1.15.2863020M0.000M4.0015.81
                    Data File Backup787M0.01.0104080M0.000M17.6097.50
                    Other48M0.08.000634142M0.07.0018773.3212.42
                    TOTAL:330G73.794.46876414G113.555.606490.8935.30
                    • Ordered by (Data Read + Write) desc for each function
                    Function/File NameReads: DataReqs per secData per secWrites: DataReqs per secData per secWaits: CountAvg Tm(ms)
                    Data Pump 86.3G7.951.16892100.5G6.831.36150152.6K4.86
                    Data Pump (Data File) 63.3G5.22.85790199.6G6.011.3487333K22.43
                    Data Pump (Data Pump Dump File) 21.1G1.15.2863020M0.000M0
                    Data Pump (Control File) 1.8G1.58.024718966M0.82.012775119.6K0.01
                    RMAN 89.5G2.501.2115587.4G1.201.1841567160.80
                    RMAN (Data File) 50.7G1.38.6871480M0.000M408.25
                    RMAN (Data File Incremental Backup) 0M0.000M49.6G0.67.6717280
                    RMAN (Archive Log) 37.8G1.02.5116610M0.000M3720.00
                    RMAN (Archive Log Backup) 0M0.000M37.8G0.51.5116480
                    RMAN (Data File Backup) 787M0.01.0104080M0.000M0
                    RMAN (Control File) 177M0.08.00234059M0.02.00078063040.80
                    Others 82.2G3.471.1132482.6G2.161.11816266.2K0.65
                    Others (Log File) 79.1G1.091.071052.3G0.03.03047182.8K0.14
                    Others (Archive Log) 0M0.000M79.1G1.071.071010
                    Others (Control File) 3.1G2.32.0417651.2G1.01.015896175.6K0.10
                    Others (Data File) 32M0.05.00042359M0.05.000780787618.18
                    LGWR 66.5G57.67.90094979.2G1.391.072574361.4K0.06
                    LGWR (Log File) 0M0.010M79.1G1.291.07100152034.46
                    LGWR (Control File) 66.5G57.66.900949119M0.10.0015734359.8K0.05
                    DBWR 805M0.68.01064680.6G102.101.0912851.5K0.28
                    DBWR (Data File) 0M0.000M80.6G102.101.091280
                    DBWR (Control File) 805M0.68.0106460M0.000M51.5K0.28
                    Buffer Cache Reads 4.6G1.44.0627410M0.000M108.9K43.65
                    Buffer Cache Reads (Data File) 4.6G1.44.0627410M0.000M108.9K43.65
                    Direct Writes 0M0.000M28M0.01.0003700
                    Direct Writes (Data File) 0M0.000M28M0.01.0003700
                    Direct Reads 1M0.00.0000130M0.000M0
                    Direct Reads (Data File) 1M0.00.0000130M0.000M0
                    TOTAL: 329.9G73.714.46807430.3G113.695.828064947.4K1.20
                    TablespaceReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)
                    UNDOTBS127,715046.1063.975,672,2457516,127977.85
                    RESBAK138,369281.5939.092,068,951271,604882.59
                    RESBAK_TEMP372,39250.0022.24393,373525,316426.88
                    SYSAUX23,60208.711.9514,867000.00
                    SYSTEM25,53401.571.193,4630859859.60
                    TEMP5301.8920.68113000.00
                    EXAMPLE100.001.000000.00
                    USERS100.001.000000.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/HDB/oracle/app/oracle/oradata/resbak/example01.dbf100.001.000000.00
                    RESBAK/HDB/oracle/app/oracle/oradata/resbak/resbak01.dbf27,237075.0440.52423,225641444.88
                    RESBAK/HDB/oracle/app/oracle/oradata/resbak/resbak02.dbf30,323061.2038.64400,761529390.00
                    RESBAK/HDB/oracle/app/oracle/oradata/resbak/resbak03.dbf26,939062.5038.12409,90551,335969.48
                    RESBAK/HDB/oracle/app/oracle/oradata/resbak/resbak04.dbf27,2100107.5538.27410,109592439.78
                    RESBAK/HDB/oracle/app/oracle/oradata/resbak/resbak05.dbf26,6600104.2439.96424,9516107480.37
                    RESBAK_TEMP/HDB/oracle/app/oracle/oradata/resbak/resbak_temp01.dbf234,67930.0022.14235,873311,618248.60
                    RESBAK_TEMP/HDB/oracle/app/oracle/oradata/resbak/resbak_temp02.dbf137,71320.0022.40157,500213,698578.09
                    SYSAUX/HDB/oracle/app/oracle/oradata/resbak/sysaux01.dbf23,60208.711.9514,867000.00
                    SYSTEM/HDB/oracle/app/oracle/oradata/resbak/system01.dbf25,53401.571.193,4630859859.60
                    TEMP/HDB/oracle/app/oracle/oradata/resbak/temp01.dbf5301.8920.6811300
                    UNDOTBS1/HDB/oracle/app/oracle/oradata/resbak/undotbs01.dbf27,715046.1063.975,672,2457516,127977.85
                    USERS/HDB/oracle/app/oracle/oradata/resbak/users01.dbf100.001.000000.00

                    Back to Top

                    • 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
                    D4,245,3589981,683,930609,26310,571,1580043,906
                    MTTR WritesLog Size WritesLog Ckpt WritesOther Settings WritesAutotune Ckpt WritesThread Ckpt Writes
                    03,936,368001,692,1354,231,607

                    Back to Buffer Pool Statistics
                    Back to Top

                     

                    Advisory Statistics

                    Back to Top

                    Targt MTTR (s) Estd MTTR (s)Recovery Estd IOsActual RedoBlksTarget RedoBlksLog Sz RedoBlksLog Ckpt Timeout RedoBlksLog Ckpt Interval RedoBlksOpt Log Sz(M)Estd RAC Avail Time
                    B01754627216887784928568877
                    E0163041819147925346713511479253

                    Back to Advisory Statistics
                    Back to Top

                    MTTR Advisory

                    No data exists for this section of the report.

                    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 (thousands)Est Phys Read FactorEstimated Phys Reads (thousands)Est Phys Read TimeEst %DBtime for Rds
                    D3,3280.104105.3426,8711127722.00
                    D6,6560.198211.678,394127426.00
                    D9,9840.291,2311.366,832118946.00
                    D13,3120.391,6411.195,992114389.00
                    D16,6400.482,0521.145,730112963.00
                    D19,9680.582,4621.105,518111814.00
                    D23,2960.682,8721.035,196110067.00
                    D26,6240.773,2831.025,15319832.00
                    D29,9520.873,6931.015,09419511.00
                    D33,2800.974,1031.005,03919211.00
                    D34,4321.004,2451.005,03119171.00
                    D36,6081.064,5140.995,00519028.00
                    D39,9361.164,9240.994,98118897.00
                    D43,2641.265,3340.994,97318855.00
                    D46,5921.355,7450.994,97118846.00
                    D49,9201.456,1550.994,96918835.00
                    D53,2481.556,5650.994,96818827.00
                    D56,5761.646,9760.994,96718825.00
                    D59,9041.747,3860.994,96718824.00
                    D63,2321.847,7960.994,96718823.00
                    D66,5601.938,2070.934,68217276.00
                    PGA Cache Hit %W/A MB ProcessedExtra W/A MB Read/Written
                    51.7878,15072,790

                    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)
                    B12,89411,1423,220.351,500.5446.6099.900.101,048,576
                    E12,89411,423293.930.000.000.000.001,048,576
                    Low OptimalHigh OptimalTotal ExecsOptimal Execs1-Pass ExecsM-Pass Execs
                    2K4K70,07670,07600
                    64K128K60460400
                    128K256K37437400
                    256K512K22422400
                    512K1024K4,4434,44300
                    1M2M86086000
                    2M4M121200
                    4M8M484800
                    16M32M2200
                    32M64M3300
                    64M128M4040
                    256M512M320320
                    512M1024M340340
                    1G2G260260

                    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 Disk Estd PGA Cache Hit %Estd PGA Overalloc CountEstd Time
                    1,6120.13530,768.74106,944.1283.0001,405,850,479
                    3,2240.25530,768.7449,360.7291.0001,278,906,763
                    6,4470.50530,768.7448,657.1192.0001,277,355,640
                    9,6710.75530,768.7448,657.1192.0001,277,355,640
                    12,8941.00530,768.7448,657.1192.0001,277,355,640
                    15,4731.20530,768.7433,453.0194.0001,243,837,892
                    18,0521.40530,768.7433,453.0194.0001,243,837,892
                    20,6301.60530,768.7433,453.0194.0001,243,837,892
                    23,2091.80530,768.7433,453.0194.0001,243,837,892
                    25,7882.00530,768.7433,453.0194.0001,243,837,892
                    38,6823.00530,768.7433,453.0194.0001,243,837,892
                    51,5764.00530,768.7433,453.0194.0001,243,837,892
                    77,3646.00530,768.7433,453.0194.0001,243,837,892
                    103,1528.00530,768.7433,453.0194.0001,243,837,892
                    • 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 (K)
                    2,0480.5726210,828464,5730.64267,66985.9099,362
                    2,3040.6451818,408506,4650.69225,77772.46107,266
                    2,4320.6864621,907530,6500.73201,59264.70111,315
                    2,5600.7177426,189554,3600.76177,88257.09115,431
                    2,6880.7590232,010578,8320.79153,41049.23119,632
                    2,8160.791,03035,057603,5270.83128,71541.31123,929
                    2,9440.821,15838,540627,0590.86105,18333.76128,186
                    3,0720.861,28643,835649,6610.8982,58126.50132,536
                    3,2000.891,41447,573671,5150.9260,72719.49136,912
                    3,3280.931,54251,778692,0690.9540,17312.89141,253
                    3,4560.961,67056,825712,1030.9820,1396.46145,478
                    3,5841.001,79861,405729,1261.003,1161.00149,068
                    3,7121.041,92666,407729,1751.003,0670.98149,078
                    3,8401.072,05471,329729,1861.003,0560.98149,081
                    3,9681.112,18276,251729,1971.003,0450.98149,083
                    4,0961.142,31081,173729,2081.003,0340.97149,085
                    4,2241.182,43886,095729,2261.003,0160.97149,087
                    4,3521.212,56691,017729,2381.003,0040.96149,089
                    4,4801.252,69495,939729,2501.002,9920.96149,092
                    4,6081.292,822100,861729,2661.002,9760.96149,094
                    4,7361.322,950105,783729,2801.002,9620.95149,095
                    5,1201.433,334120,551729,3041.002,9380.94149,100
                    5,5041.543,718135,319729,3281.002,9140.94149,103
                    5,8881.644,102150,087729,3351.002,9070.93149,105
                    6,2721.754,486164,855729,3381.002,9040.93149,105
                    6,6561.864,559167,696729,3381.002,9040.93149,105
                    7,0401.964,559167,696729,3381.002,9040.93149,105
                    7,4242.074,559167,696729,3381.002,9040.93149,105

                    Back to Top

                    ClassWaitsTotal Wait Time (s)Avg Time (ms)
                    file header block27,96213,395479
                    undo header13,48013,182978
                    data block2,4632,154875
                    bitmap index block100

                    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)
                    TC-Tablespace Checkpoint660217,6568,828,110.00
                    CR-Reuse Block Range (block range reuse ckpt)9,5299,5280551,77332,244.73
                    CF-Controlfile Transaction1,364,0671,363,8791842,8691,464510.44
                    HW-Segment High Water Mark75,12575,1250341,25236,815.29
                    CI-Cross-Instance Call Invocation20,66820,6680771,03913,495.45
                    RO-Multiple Object Reuse (fast object reuse)51950605685515,274.11
                    JD-Job Queue Date14,94914,9490868185,146.25
                    JS-Job Scheduler (queue lock)269,112269,07933401834,582.00
                    KO-Multiple Object Checkpoint (fast object checkpoint)19180212461,875.00
                    CT-Block Change Tracking (global space management)7127120122,060.00
                    TQ-Queue table enqueue (DDL contention)1111032506.67
                    CT-Block Change Tracking (CTWR process start/stop)10,31010,3100121,520.00
                    TM-DML179,27535,843143,43221610.00
                    TX-Transaction (row lock contention)1515061140.00
                    CT-Block Change Tracking (local space management)2,7202,720021345.00

                    Back to Top

                    • 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
                    27,323.932,459,48747,1271215/595.20/00/0/0/490/970976/0
                    End TimeNum Undo BlocksNumber of TransactionsMax Qry Len (s)Max Tx ConcyTun Ret (mins)STO/ OOSuS/uR/uU/ eS/eR/eU
                    12-9月 12:553951352150/00/0/0/0/0/0
                    12-9月 12:4522,3977,6437374240/00/0/0/2/0/0
                    12-9月 12:3544,52214,8861,3154340/00/0/0/6/0/0
                    12-9月 12:2557,68319,2766794240/00/0/0/8/16/0
                    12-9月 12:15102,66634,4381,2314340/00/0/0/16/272/0
                    12-9月 12:0547,96316,1631,8236440/00/0/0/6/0/0
                    12-9月 11:5547,74615,9631,1414340/00/0/0/10/128/0
                    12-9月 11:4541,64213,9201,7374440/00/0/0/5/0/0
                    12-9月 11:35102,39134,2031,3484340/00/0/0/15/232/0
                    12-9月 11:2532,11210,8697214240/00/0/0/8/280/0
                    12-9月 11:1534,48412,0021,5556400/00/0/0/8/152/0
                    12-9月 11:0557,02919,4181,7897440/00/0/0/21/26736/0
                    12-9月 10:5575,87925,3442,7586610/00/0/0/0/0/0
                    12-9月 10:4556,91019,0721,7585440/00/0/0/0/0/0
                    12-9月 10:3539,88013,3671,5564410/00/0/0/0/0/0
                    12-9月 10:25181,33560,7081,7604440/00/0/0/0/0/0
                    12-9月 10:15132,64644,2801,15910340/00/0/0/0/0/0
                    12-9月 10:0597,47732,7151,7626440/00/0/0/0/0/0
                    12-9月 09:5566,35522,1561,1613340/00/0/0/0/0/0
                    12-9月 09:4589,95830,1541,7643440/00/0/0/0/0/0
                    12-9月 09:3580,06426,9141,1625340/00/0/0/0/0/0
                    12-9月 09:25106,95735,7041684180/00/0/0/0/0/0
                    12-9月 09:1582,17427,6059597310/00/0/0/0/0/0
                    12-9月 09:0575,35825,2711,7685440/00/0/0/0/0/0
                    12-9月 08:5576,55826,0061,16811340/00/0/0/0/0/0
                    12-9月 08:4567,44522,5081,76910440/00/0/0/0/0/0
                    12-9月 08:3573,48725,0021,16811340/00/0/0/0/0/0
                    12-9月 08:2556,80919,4455678240/00/0/0/0/0/0
                    12-9月 08:1578,03626,3551,17112350/00/0/0/0/0/0
                    12-9月 08:0571,79824,2871,77412450/00/0/0/0/0/0
                    12-9月 07:5567,82822,8651,1739350/00/0/0/0/0/0
                    12-9月 07:4578,05826,2221,7769450/00/0/0/0/0/0
                    12-9月 07:3577,22425,8561,1756350/00/0/0/0/0/0
                    12-9月 07:2576,33225,7051,7786450/00/0/0/0/0/0

                    Back to Undo Statistics
                    Back to Top

                     

                    Latch Statistics

                    Back to Top

                    • "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
                    AQ deq hash table latch210.00 00
                    AQ dequeue txn counter latch38,2310.080.0000
                    ASM db client latch48,2310.00 00
                    ASM map operation hash table210.00 00
                    ASM network background latch60.00 00
                    ASM network state latch82,1160.00 00
                    AWR Alerted Metric Element list597,8330.00 00
                    Change Notification Hash table latch21,2220.00 00
                    Consistent RBA17,1730.010.0000
                    DML lock allocation358,9280.000.0000
                    Event Group Locks7,3750.00 00
                    FAL Queue2,7830.00 00
                    FIB s.o chain latch25,6360.00 00
                    FOB s.o list latch37,1970.040.4000
                    File State Object Pool Parent Latch210.00 00
                    I/O Staticstics latch210.00 00
                    IPC stats buffer allocation latch210.00 00
                    In memory undo latch97,8080.010.62039,8140.00
                    JS Sh mem access1630.00 00
                    JS broadcast autostart latch20.00 00
                    JS mem alloc latch1780.00 00
                    JS queue access latch1990.00 00
                    JS queue state obj latch538,1580.00 00
                    JS slv state obj latch1,7380.00 00
                    KFC FX Hash Latch210.00 00
                    KFC Hash Latch210.00 00
                    KFCL LE Freelist210.00 00
                    KGNFS-NFS:SHM structure210.00 00
                    KGNFS-NFS:SVR LIST210.00 00
                    KJC message pool free list210.00 00
                    KJCT flow control latch210.00 00
                    KMG MMAN ready and startup request latch25,1960.00 00
                    KTF sga latch4360.00 022,9930.00
                    KWQMN job cache list latch300.00 00
                    KWQP Prop Status9,3350.00 00
                    KWQS pqsubs latch490.00 00
                    KWQS pqueue ctx latch1330.00 00
                    Locator state objects pool parent latch210.00 00
                    Lsod array latch210.00 00
                    MQL Tracking Latch0 01,5070.00
                    Memory Management Latch210.00 025,1960.00
                    Memory Queue Message Subscriber #1790.00 00
                    Memory Queue Message Subscriber #2210.00 00
                    Memory Queue Message Subscriber #3210.00 00
                    Memory Queue Message Subscriber #4210.00 00
                    MinActiveScn Latch8040.00 00
                    Mutex210.00 00
                    Mutex Stats210.00 00
                    OS process18,4640.00 00
                    OS process allocation160,8970.000.0000
                    OS process: request allocation7,1950.030.0000
                    PL/SQL warning settings63,0610.140.0000
                    PX hash array latch210.00 00
                    QMT210.00 00
                    Real-time plan statistics latch3,987,2730.00 00
                    Reserved Space Latch90.00 00
                    SGA IO buffer pool latch45,9470.010.00064,7910.01
                    SGA blob parent210.00 00
                    SGA bucket locks210.00 00
                    SGA heap locks210.00 00
                    SGA pool locks210.00 00
                    SQL memory manager latch210.00 023,0900.00
                    SQL memory manager workarea list latch1,621,7810.00 00
                    STREAMS Pool Advisor0 030.00
                    Shared B-Tree68,6790.180.0000
                    Streams Generic210.00 00
                    Testing210.00 00
                    Token Manager210.00 00
                    Undo Hint Latch0 07310.00
                    WCR: sync210.00 00
                    Write State Object Pool Parent Latch210.00 00
                    X$KSFQP191,3130.00 00
                    XDB NFS Security Latch210.00 00
                    XDB unused session pool210.00 00
                    XDB used session pool210.00 00
                    active checkpoint queue latch797,9930.000.0000
                    active service list173,3250.020.000218,1380.00
                    alert log latch7600.00 00
                    archive control5,0420.00 00
                    archive process latch8,7430.251.0000
                    begin backup scn array103,6600.00 00
                    bq:time manger info latch5,3080.00 00
                    buffer pool210.00 00
                    bufq statistics19,3300.020.0000
                    business card210.00 00
                    cache buffer handles2,438,7060.000.0000
                    cache buffers chains314,037,3230.000.02012,317,7330.00
                    cache buffers lru chain11,640,3120.220.00024,869,2930.03
                    cache table scan latch4,0130.00 04,0130.00
                    call allocation52,2390.410.9000
                    cas latch210.00 00
                    change notification client cache latch210.00 00
                    channel handle pool latch7,2620.011.0000
                    channel operations parent latch850,5060.050.0000
                    checkpoint queue latch19,389,5030.000.10010,529,7530.00
                    client/application info26,0490.00 00
                    compile environment latch3,7630.00 00
                    corrupted undo seg latch95,4380.00 00
                    cp cmon/server latch210.00 00
                    cp pool latch210.00 00
                    cp server hash latch210.00 00
                    cp sga latch82,1160.00 00
                    cvmap freelist lock210.00 00
                    database property service latch140.00 00
                    datapump attach fixed tables latch3,7200.510.00010.00
                    datapump job fixed tables latch3,7170.00 010.00
                    deferred cleanup latch82,1160.00 00
                    device information300.00 00
                    dml lock allocation82,1760.00 00
                    done queue latch210.00 00
                    dummy allocation7,5600.090.1400
                    enqueue freelist latch210.00 04,743,3440.00
                    enqueue hash chains9,960,3170.000.0108,6080.00
                    enqueues370.00 00
                    fifth spare latch210.00 00
                    file cache latch22,2020.040.0000
                    flashback copy210.00 00
                    fourth Audit Vault latch210.00 00
                    gc element210.00 00
                    gcs commit scn state210.00 00
                    gcs partitioned table hash210.00 00
                    gcs pcm hashed value bucket hash210.00 00
                    gcs resource freelist210.00 00
                    gcs resource hash210.00 00
                    gcs resource scan list210.00 00
                    gcs shadows freelist210.00 00
                    ges domain table210.00 00
                    ges enqueue table freelist210.00 00
                    ges group table210.00 00
                    ges process hash list210.00 00
                    ges process parent latch210.00 00
                    ges resource hash list210.00 00
                    ges resource scan list210.00 00
                    ges resource table freelist210.00 00
                    ges value block free list210.00 00
                    global KZLD latch for mem in SGA2,7510.00 00
                    global ctx hash table latch10.00 00
                    global tx hash mapping210.00 00
                    granule operation210.00 00
                    hash table column usage latch2,8050.00 01,209,3420.00
                    hash table modification latch2,0340.00 00
                    heartbeat check210.00 00
                    image handles of buffered messages latch580.00 00
                    internal temp table object number allocation latch2350.00 00
                    interrupt manipulation1270.00 00
                    intra txn parallel recovery210.00 00
                    io pool granule metadata list210.00 00
                    job workq parent latch2110.00 01900.00
                    job_queue_processes free list latch4860.00 00
                    job_queue_processes parameter latch16,3000.00 00
                    k2q lock allocation210.00 00
                    kcbtsemkid latch3890.00 00
                    kcfis latch80.00 00
                    kdlx hb parent latch210.00 00
                    kgb parent210.00 00
                    kgnfs mount latch210.00 00
                    kmcptab latch60.00 00
                    kmcpvec latch0 020.00
                    kokc descriptor allocation latch5650.00 00
                    krbmrosl5030.00 00
                    ksfv messages210.00 00
                    ksim group membership cache210.00 00
                    kss move lock9450.00 00
                    ksuosstats global area8,7280.00 00
                    ksv allocation latch83,9720.00 00
                    ksv class latch1,9130.050.0000
                    ksv instance latch10.00 00
                    ksv msg queue latch210.00 00
                    ksz_so allocation latch7,1950.070.0000
                    ktm global data3,5220.00 00
                    kupp process latch2,1300.00 00
                    kwqbsgn:msghdr870.00 00
                    kwqbsn:qsga69,8270.440.00030.00
                    kwqbsn:qxl160.00 00
                    lgwr LWN SCN33,7400.010.0000
                    list of block allocation125,4480.00 00
                    loader state object freelist1,0600.00 00
                    lob segment dispenser latch210.00 00
                    lob segment hash table latch2730.00 00
                    lob segment query latch210.00 00
                    lock DBA buffer during media recovery210.00 00
                    logical standby cache210.00 00
                    logminer context allocation420.00 00
                    logminer work area210.00 00
                    longop free list parent199,8600.00 03150.00
                    managed standby latch2,4090.00 00
                    mapped buffers lru chain210.00 00
                    message pool operations parent latch4,9210.00 00
                    messages3,291,7780.580.0310
                    mostly latch-free SCN33,9220.190.0000
                    msg queue latch210.00 00
                    multiblock read objects8,2220.00 00
                    name-service namespace bucket210.00 00
                    ncodef allocation latch82,1160.00 00
                    object queue header heap65,4670.00 062,3740.00
                    object queue header operation50,501,2600.010.0000
                    object stats modification16,4900.00 00
                    parallel query alloc buffer9,6490.00 00
                    parallel query stats210.00 00
                    parameter list2,6280.00 00
                    parameter table management20,9920.100.0000
                    pass worker exception to master1,2410.00 00
                    peshm210.00 00
                    pesom_free_list210.00 00
                    pesom_hash_node210.00 00
                    post/wait queue5,782,8215.790.0005,479,1563.35
                    process allocation8,0030.500.8803,5850.17
                    process group creation7,1950.030.0000
                    process queue210.00 00
                    process queue reference210.00 00
                    qmn state object latch10.00 00
                    qmn task queue latch91,2533.570.0000
                    query server freelists210.00 00
                    queue sender's info. latch111,5061.320.0000
                    queued dump request2520.00 00
                    queuing load statistics210.00 00
                    recovery domain hash list210.00 00
                    redo allocation338,8240.470.20029,331,0950.01
                    redo copy210.00 029,332,6930.02
                    redo writing2,734,17736.900.0120
                    resmgr group change latch66,2260.000.0000
                    resmgr:active threads11,0590.00 07,1580.00
                    resmgr:actses change group3,6410.00 00
                    resmgr:actses change state2470.00 00
                    resmgr:free threads list7,6590.180.0000
                    resmgr:method mem alloc latch200.00 00
                    resmgr:plan CPU method210.00 00
                    resmgr:resource group CPU method650.00 00
                    resmgr:schema config67,9080.040.1102360.00
                    resmgr:session queuing210.00 00
                    resumable state object71,9040.00 00
                    rm cas latch210.00 00
                    row cache objects5,101,9690.030.0109570.21
                    rules engine aggregate statistics1010.00 00
                    rules engine rule set statistics2,3410.00 00
                    rules engine rule statistics290.00 00
                    second Audit Vault latch210.00 00
                    second spare latch210.00 00
                    sequence cache23,5090.060.0000
                    session allocation38,5650.020.00031,8770.00
                    session idle bit792,4800.000.0000
                    session queue latch210.00 00
                    session state list latch8,3040.010.0000
                    session switching85,7400.000.00020.00
                    session timer90,3160.00 00
                    shared pool1,402,9170.120.0400
                    shared pool sim alloc1230.00 00
                    shared pool simulator13,1590.00 00
                    sim partition latch210.00 00
                    simulator hash latch24,143,4850.010.0000
                    simulator lru latch7,417,7660.000.00016,082,0670.00
                    sort extent pool198,5370.020.0000
                    space background state object latch2860.00 00
                    space background task latch152,0061.640.01050,7850.00
                    spilled messages latch40.00 00
                    state object free list420.00 00
                    statistics aggregation11,7600.00 00
                    tablespace key chain3850.00 00
                    temp lob duration state obj allocation750.00 00
                    temporary table state object allocation200.00 00
                    test excl. parent l0210.00 00
                    test excl. parent2 l0210.00 00
                    third spare latch210.00 00
                    threshold alerts latch2,9690.00 00
                    trace latch890.00 00
                    transaction allocation59,9710.00 00
                    undo global data17,476,0560.000.000100.00
                    virtual circuit buffers210.00 00
                    virtual circuit holder210.00 00
                    virtual circuit queues210.00 00
                    Latch NameGet RequestsMissesSleepsSpin Gets
                    redo writing2,734,1771,008,9907,7801,001,276
                    post/wait queue5,782,821335,076826334,250
                    cache buffers lru chain11,640,31225,9433425,909
                    messages3,291,77819,13063118,499
                    object queue header operation50,501,2606,10516,104
                    cache buffers chains314,037,3234,8601134,753
                    qmn task queue latch91,2533,26223,260
                    space background task latch152,0062,490162,474
                    shared pool1,402,9171,734721,677
                    simulator hash latch24,143,4851,73211,731
                    redo allocation338,8241,5763111,279
                    row cache objects5,101,9691,381121,369
                    checkpoint queue latch19,389,50359959540
                    simulator lru latch7,417,7662511250
                    enqueue hash chains9,960,3172392237
                    call allocation52,23921619542
                    process allocation8,00340357
                    resmgr:schema config67,90827324
                    archive process latch8,74322220
                    FOB s.o list latch37,1971569
                    In memory undo latch97,8081385
                    dummy allocation7,560716
                    channel handle pool latch7,262110

                    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
                    FOB s.o list latchksfd_allfob066
                    In memory undo latchktiFlush: child054
                    In memory undo latchkticmt: child053
                    PC and Classifier lists for WLMNo latch020
                    archive process latchkcrrsarc0210
                    archive process latchkcrrgpll0121
                    cache buffers chainskcbgtcr: fast path03814
                    cache buffers chainskcbgtcr: fast path (cr pin)0268
                    cache buffers chainskcbchg1: mod cr pin02121
                    cache buffers chainskcbgcur_20147
                    cache buffers chainskcbgtcr: kslbegin shared01212
                    cache buffers chainskcbzwb074
                    cache buffers chainskcbgtcr_2039
                    cache buffers chainskcbgcur_4020
                    cache buffers chainskcbrls: fast release011
                    cache buffers lru chainkcbzgws0260
                    cache buffers lru chainkcbzgb062
                    cache buffers lru chainkcbbic20242
                    call allocationksuxds0146120
                    call allocationksudlp: top call04774
                    call allocationksudlc : rec call021
                    channel handle pool latchksrchconnect()011
                    checkpoint queue latchkcbbwthc: thread checkpoint buffers0520
                    checkpoint queue latchkcbbwdl: thread checkpoint queue070
                    dummy allocationktcscnfy011
                    enqueue hash chainsksqcmi: get hash chain latch after wait012
                    enqueue hash chainsksqrcl011
                    messagesksaamb: after wakeup0329629
                    messagesksarcv02842
                    messagesksarcv: after wait0180
                    object queue header operationkcbo_switch_cq033
                    post/wait queueksliwat:add:nowait04810
                    post/wait queueksliwat:remove0332817
                    post/wait queueksliwat:add:wait0139
                    process allocationksucrp:10340
                    process allocationksuapc0135
                    qmn task queue latchkwqmndntsk: task done but retry011
                    qmn task queue latchkwqmngettsk: get task0118
                    redo allocationkcrfw_redo_gen: redo allocation 30175305
                    redo allocationkcrfw_redo_gen: redo allocation 20600
                    redo allocationkcrfw_redo_gen: redo allocation 10560
                    redo allocationkcrfw_redo_gen: redo allocation 00164
                    redo allocationkcrfw_redo_write: before write042
                    redo writingkcrfws: in loop07,7797,780
                    redo writingkcrfwcr010
                    resmgr:schema configkgskgasi1010
                    resmgr:schema configkgskgmaxdop1013
                    resmgr:schema configkgskmetricupd010
                    row cache objectskqrpre: find obj076
                    row cache objectskqreqd: reget040
                    row cache objectskqrpup015
                    shared poolkghfre04444
                    shared poolkghalo02826
                    simulator hash latchkcbs_lookup_setid: lookup dba010
                    simulator lru latchkcbs_simulate: simulate set010
                    space background task latchktsj_grab_task01416
                    Mutex TypeLocationSleepsWait Time (ms)
                    Library Cachekglpin1 43,9590
                    Library Cachekglhdgn2 1062,3600
                    Library Cachekglpnal1 908330
                    Library Cachekglpndl1 957010
                    Library Cachekgllkdl1 855320
                    Library Cachekgllkc1 57390
                    Library Cachekglhdgn1 62290
                    Library Cachekglget1 1230
                    Library Cachekglget2 2160
                    Cursor Pinkksfbc [KKSCHLPIN1]60
                    Cursor PinkksLockDelete [KKSCHLPIN6]40
                    Cursor Pinkksxsccmp [KKSCHLPIN5]40
                    Cursor Pinkkslce [KKSCHLPIN2]30

                    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

                    • Captured Segments account for 71.8% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypeLogical Reads%Total
                    RESBAKRESBAKMAP_CASE_ID INDEX2,652,4963.12
                    RESBAKRESBAKSYS_C0011392 INDEX2,551,2163.00
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX2,502,1442.94
                    ** MISSING **TEMP** TRANSIENT: 4197504** MISSING **UNDEFINED2,433,4882.86
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_RES_ME INDEX2,304,5602.71
                    • Captured Segments account for 6.3% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Reads%Total
                    RESBAKRESBAKSD_RES_CASE_MAP TABLE500,9255.63
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX47,9100.54
                    RESBAKRESBAKSD_RES_RESOURCE TABLE7,8710.09
                    SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_3165050927_6879TABLE PARTITION1,7770.02
                    RESBAKRESBAKSD_RES_ID_CASE TABLE9100.01
                    • Captured Segments account for 13.1% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypePhys Read Requests%Total
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX47,9109.91
                    RESBAKRESBAKSD_RES_RESOURCE TABLE7,8711.63
                    RESBAKRESBAKSD_RES_CASE_MAP TABLE3,9370.81
                    SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_3165050927_6879TABLE PARTITION1,7770.37
                    RESBAKRESBAKSD_RES_ID_CASE TABLE9100.19
                    • Captured Segments account for 13.1% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypeUnOptimized Reads%Total
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX47,9109.91
                    RESBAKRESBAKSD_RES_RESOURCE TABLE7,8711.63
                    RESBAKRESBAKSD_RES_CASE_MAP TABLE3,9370.81
                    SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_3165050927_6879TABLE PARTITION1,7770.37
                    RESBAKRESBAKSD_RES_ID_CASE TABLE9100.19
                    • Captured Segments account for 27.0% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypePhysical Writes%Total
                    RESBAKRESBAKSD_RES_ID_CASE TABLE1,780,1228.30
                    RESBAKRESBAKSD_RES_RESOURCE TABLE1,305,3426.08
                    RESBAKRESBAKSD_RES_CASE_MAP TABLE221,4841.03
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX168,9820.79
                    RESBAKRESBAKMAP_CASE_ID INDEX142,7190.67
                    • Captured Segments account for 20.7% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypePhys Write Requests%Total
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX107,8981.32
                    RESBAKRESBAKSYS_C0011392 INDEX93,0291.14
                    RESBAKRESBAKMAP_CASE_ID INDEX86,3281.06
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_RES_ME INDEX75,0170.92
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_CODE INDEX72,2960.89
                    • Captured Segments account for 30.3% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypeDirect Writes%Total
                    RESBAKRESBAKSD_RES_ID_CASE TABLE1,775,79316.31
                    RESBAKRESBAKSD_RES_RESOURCE TABLE1,301,98711.96
                    RESBAKRESBAKSD_RES_CASE_MAP TABLE220,1882.02
                    SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_3165050927_6879TABLE PARTITION1,7650.02
                    SYSSYSAUXWRH$_ACTIVE_SESSION_HISTORYWRH$_ACTIVE_3165050927_6903TABLE PARTITION3150.00
                    • Captured Segments account for 11.0% of Total
                    OwnerTablespace NameObject NameSubobject NameObj. TypeTable Scans%Total
                    SYSSYSTEMI_OBJ2 INDEX958.00
                    SYSSYSTEMI_SYSAUTH1 INDEX60.51
                    SYSSYSAUXWRH$_SEG_STAT_PKWRH$_SEG_ST_3165050927_6879INDEX PARTITION50.42
                    SYSSYSAUXWRH$_FILESTATXS_PKWRH$_FILEST_3165050927_6749INDEX PARTITION40.34
                    SYSSYSAUXWRH$_SQLSTAT_PKWRH$_SQLSTA_3165050927_6749INDEX PARTITION30.25
                    • with total DB Block Changes for all segments captured by the Snapshot
                    OwnerTablespace NameObject NameSubobject NameObj. TypeDB Block Changes% of Capture
                    RESBAKRESBAKMAP_CASE_ID INDEX1,569,0245.21
                    RESBAKRESBAKSYS_C0011392 INDEX1,533,9685.09
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX1,405,4884.67
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_RES_ME INDEX1,330,9284.42
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_CODE INDEX1,237,1844.11
                    • with total row lock waits for all segments captured by the Snapshot
                    OwnerTablespace NameObject NameSubobject NameObj. TypeRow Lock Waits% of Capture
                    RESBAKRESBAKSYS_C0011392 INDEX3,0406.56
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_CODE INDEX2,8146.07
                    RESBAKRESBAKIDX_IDX_SD_RES_RESOURCE_RES_ME INDEX2,4665.32
                    RESBAKRESBAKMAP_RES_ID INDEX2,3024.97
                    RESBAKRESBAKMAP_CASE_ID INDEX2,2844.93
                    • with total ITL waits for all segments captured by the Snapshot
                    OwnerTablespace NameObject NameSubobject NameObj. TypeITL Waits% of Capture
                    SYSSYSAUXI_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX2100.00
                    • with total Buffer Busy Waits for all segments captured by the Snapshot
                    OwnerTablespace NameObject NameSubobject NameObj. TypeBuffer Busy Waits% of Capture
                    RESBAKRESBAKRBK_RES_LOG TABLE82739.31
                    SYSSYSTEMSEG$ TABLE77036.60
                    RESBAKRESBAKDOWNLOAD INDEX46922.29
                    RESBAKRESBAKPRIMARYSD_RES_RESOURCE1 INDEX371.76
                    RESBAKRESBAKRES_ID INDEX10.05
                    • "Final Usage" is the number of cache entries being used
                    CacheGet RequestsPct MissScan ReqsPct MissMod ReqsFinal Usage
                    dc_awr_control1,4370.000 431
                    dc_constraints18933.330 1894
                    dc_files2,1100.000 010
                    dc_global_oids68,8260.010 3327
                    dc_histogram_data89,6874.690 4,37539,217
                    dc_histogram_defs224,9993.180 3,3637,031
                    dc_object_grants12,6150.000 048
                    dc_objects477,3290.170 6844,316
                    dc_profiles4,7080.000 02
                    dc_rollback_segments140,6270.000 256
                    dc_segments51,7840.270 1,0131,789
                    dc_sequences1100.000 11013
                    dc_table_scns19100.000 00
                    dc_tablespaces304,6140.000 013
                    dc_users403,0990.010 0439
                    global database name45,5720.000 01
                    outstanding_alerts6200.000 05
                    sch_lj_objs1921.050 033
                    sch_lj_oids1174.270 078
                    NamespaceGet RequestsPct MissPin RequestsPct MissReloadsInvali- dations
                    ACCOUNT_STATUS5,5020.000 00
                    APP CONTEXT10.0020.0000
                    BODY4,8180.10129,3480.0100
                    CLUSTER5590.006140.0000
                    DBLINK5,5770.000 00
                    DIRECTORY50.0060.0000
                    EDITION3,2760.006,1940.0000
                    INDEX3,4021.883,09216.792030
                    OBJECT ID102100.000 00
                    QUEUE42,9740.00155,917-0.0000
                    RULE560.005680.36450
                    RULESET120.001360.0000
                    SCHEMA3,4340.000 00
                    SQL AREA72,5981.81778,9171.472,4082,678
                    SQL AREA BUILD4,46034.480 00
                    SQL AREA STATS4,48852.854,48852.8500
                    TABLE/PROCEDURE204,6570.11526,6350.418260
                    TEMPORARY INDEX24617.07246100.002040
                    TEMPORARY TABLE6235.30623100.005900
                    TRIGGER1,1100.001,1100.0000


                    Back to Top

                     

                    Memory Statistics

                    Back to Top

                    • Oper Types/Modes: INItializing,GROw,SHRink,STAtic/IMMediate,DEFerred
                    • ordered by Component
                    ComponentBegin Snap Size (Mb)Current Size (Mb)Min Size (Mb)Max Size (Mb)Oper CountLast Op Typ/Mod
                    ASM Buffer Cache0.000.000.000.000STA/
                    DEFAULT 16K buffer cache0.000.000.000.000STA/
                    DEFAULT 2K buffer cache0.000.000.000.000STA/
                    DEFAULT 32K buffer cache0.000.000.000.000STA/
                    DEFAULT 4K buffer cache0.000.000.000.000STA/
                    DEFAULT 8K buffer cache0.000.000.000.000STA/
                    DEFAULT buffer cache34,432.0034,432.0034,432.0034,560.000SHR/DEF
                    KEEP buffer cache0.000.000.000.000STA/
                    PGA Target12,928.0012,928.0012,928.0012,928.000STA/
                    RECYCLE buffer cache0.000.000.000.000STA/
                    SGA Target38,784.0038,784.0038,784.0038,784.000STA/
                    Shared IO Pool0.000.000.000.000STA/
                    java pool128.00128.00128.00128.000STA/
                    large pool128.00128.00128.00128.000STA/
                    shared pool3,584.003,584.003,456.003,584.000GRO/DEF
                    streams pool256.00256.00256.00256.000STA/
                    • 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,078.56 26.99166.881,4411,4417777
                    Freeable1,123.310.0035.10123.49656 3232
                    PL/SQL15.6611.640.210.72557575
                    SQL2.851.590.050.161915746
                    EOther221.25 3.466.2027276464
                    Freeable66.440.002.294.2318 2929
                    PL/SQL5.742.020.090.42346262
                    SQL0.590.140.010.030914535
                    • 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 memory128.00128.000.00
                    largeCTWR dba buffer9.849.840.00
                    largePX msg pool7.817.810.00
                    largefree memory108.37108.370.00
                    largekrcc extent chunk1.981.980.00
                    sharedKGLH0562.57566.930.78
                    sharedKGLHD59.7361.022.16
                    sharedSQLA1,506.741,500.68-0.40
                    shareddb_block_hash_buckets356.00356.000.00
                    sharedfree memory745.84739.66-0.83
                    streamsfree memory255.29255.360.03
                    buffer_cache34,432.0034,432.000.00
                    fixed_sga2.132.130.00
                    log_buffer80.7180.710.00

                    Back to Top

                    Session TypeFirst LogonCPU time(s)User IO Wait time(s)SYS IO Wait time(s)
                    QMON Slaves0426 16:05:494.750.000.00
                    QMON Coordinator0426 16:05:381.630.000.00

                    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 Capture Rate

                    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

                    Streams Apply Rate

                    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 Queue 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

                    Persistent Queues

                    • Ordered by Queue Name
                    • * indicates queue (re)started between Begin/End snaps
                    • %Exp Msgs - % of msgs enqueued with expiry
                    • %Delay Msgs - % of msgs enqueued with delay
                    • %Trasf Time - % of Enqueue time spent in transformation
                    • %Eval Time - % of Enqueue time spent in rule evaluation
                    Queue NameEnq MsgsDeq Msgs%Exp Msgs%Delay MsgsEnq Time(s)Deq Time(s)%Transf Time%Eval Time
                    SYS.ALERT_QUE(12733) 00 0.000.00
                    • * indicates queue (re)started between Begin/End snaps
                    Queue NameEnqueue Msgs/secDequeue Msgs/secAvg Enqueue sec / msgAvg Dequeue sec / msg
                    SYS.ALERT_QUE(12733) 0.000.00
                    • * indicates Subscriber activity (re)started between Begin/End snaps
                    Subscriber/QueueEnqueue MsgsDequeue MsgsExpire MsgsEnqueue Msgs/secDequeue Msgs/secExpire Msgs/sec
                    HAE_SUB(1)/SYS.ALERT_QUE 000
                    NGCQ_HBK_DN__3872_IRESBAK(41)/SYS.ALERT_QUE 0000.000.000.00

                    Back to Top

                    Avg Total ConnectionsAvg Active ConnectionsAvg Total Shared SrvrsAvg Active Shared SrvrsAvg Total DispatchersAvg Active Dispatchers
                    001010

                    Back to Shared Server Statistics
                    Back to Top

                    Shared Servers Rates

                    Common Queue Per SecDisp Queue Per SecServer Msgs/SecServer KB/SecCommon Queue TotalDisp Queue TotalServer Total MsgsServer Total(KB)
                    0000.000000

                    Back to Shared Server Statistics
                    Back to Top

                    Shared Servers Utilization

                    • Statistics are combined for all servers
                    • Incoming and Outgoing Net % are included in %Busy
                    Total Server Time (s)%Busy%IdleIncoming Net %Outgoing Net %
                    75,6010.00100.000.000.00
                    • Total Queued, Total Queue Wait and Avg Queue Wait are for dispatcher queue
                    • Name suffixes: "(N)" - dispatcher started between begin and end snapshots "(R)" - dispatcher re-started between begin and end snapshots
                    NameAvg ConnsTotal Disp Time (s)%Busy%IdleTotal QueuedTotal Queue Wait (s)Avg Queue Wait (ms)
                    D0000.0075,6010.00100.0000

                    Back to Top

                    • '(NULL)' indicates a missing parameter value
                    • A blank in the End Snapshot indicates the same value as the BeginSnapshot
                    Parameter NameBegin valueEnd value (if different)
                    control_files/HDB/oracle/app/oracle/fast_recovery_area/resbak/control02.ctl 
                    /HDB/oracle/app/oracle/oradata/resbak/control01.ctl 

                    Back to init.ora Parameters
                    Back to Top

                     

                     

                     

                     

                     

                     

                    Dynamic Remastering Stats

                    No data exists for this section of the report.


                    Back to Top

                     

                    End of Report

                     

                    Kind Regards,

                    Quanwen Zhao

                    1 person found this helpful
                    • 22. Re: Why spending very long time by impdp data to another Oracle DB?
                      Quanwen Zhao

                      Quanwen Zhao 撰写:

                       

                      The Real Rob the Relic 撰写:

                       

                      Could you also upload excerpts from the alert log showing the sections around the 'Checkpoint not complete' messages following the addition of the extra redo log groups?

                      Ok,no problem! Thanks for your advice.

                      Hi,The Real Rob the Relic.

                      My except part from the alert log as follows(sorry,my redo online logfiles have finally changed to 12 groups not 9 groups which i mentioned preview )

                       

                      5.png

                       

                      Kind Regards,

                      Quanwen Zhao

                      • 23. Re: Why spending very long time by impdp data to another Oracle DB?
                        JohnWatson2

                        You generated about 82GB of redo for 380 log switches, 215MB per switch which fits with your little 250MB logfiles. You have on average a log switch every three minutes, peak times is likely ten times that frequency which is why you need logfiles that are ten times the size to remove all those waits and save 97% of your DB Time. However, you have already ignored my advice on this (as you ignored my advice on tuning the import) so I wonder why I am bothering to write at all.

                         

                        You might want to look at tuning the SQL with sql_id gtpm09m19whtm.

                        • 24. Re: Why spending very long time by impdp data to another Oracle DB?
                          Quanwen Zhao

                          Mark D Powell 撰写:

                           

                          Quanwen, as JohnWatson2 posted you need bigger online redo logs! The "checkpoint incomplete" message is pretty damn clear. Rather than increase then 10x I suggest you make a more modest increate to 512M and see what result you get. Be prepared to increase again if necessary.

                          - -

                          Now back to the export/import process. Export parallelism was a valid suggestion however datapump cannot parallelize export/import of LOB segments so if a significant amount of the size involved is due to one or more LOB segments you may want to consider changing your export to be schema and/or table specific so that you can run concurrent export/import processes to handle the LOB segments.

                          - -

                          HTH -- Mark D Powell --

                          Hello,Mark D Powell

                          My IMPDP process has included about three tables,please see my diagram below,

                          6.png

                          Exactly,as what you might expect,i have checked structure of my three tables and found one table 'SD_RES_RESOURCE' has CLOB field,my snapshot as follows,

                          7.png

                          Regards,

                          Quanwen Zhao

                           

                           

                           

                          • 25. Re: Why spending very long time by impdp data to another Oracle DB?
                            Mark D Powell

                            Quanwen, fix your online redo log size problem then rerun the impdp process to see what kind of performance effect the resizing had.  Once you know that you can then decide if you need to tune the export/import process.  If tuning is still necessary you can try using parallelism which applies only to the two non-LOB tables but with 40% of your data in the LOB table you may need to split the process itself into multiple processes.

                            - -

                            But I have to believe that fixing the online redo log size will result in a major improvement.  500M - 1G redo logs are pretty common.

                            - -

                            HTH -- Mark D Powell --

                            • 26. Re: Why spending very long time by impdp data to another Oracle DB?

                              I'd have to agree with John and Mark regarding the redo log size.

                              I mentioned the option of increasing the number of redo log groups simply because, if, when you're back to 'normal database load' and want to reduce the total amount of redo log space allocated, then dropping groups that are no longer required is an easier option than resizing again.

                              The increase in the number of log groups has resulted in some reduction in wait time, from high 90s % to around 80%, but you'd probably need a ridiculous number of log groups to bring this down substantially, so fewer, larger log groups would seem more appropriate.

                              1 person found this helpful
                              • 27. Re: Why spending very long time by impdp data to another Oracle DB?
                                jgarry

                                If memory serves (and mine often does not, so you might want to research on MOS, and things may have changed too), the "private strand flush not complete" is ignorable if the next log switches very soon (seconds) after the warning.

                                 

                                There have been space issues with LOB's, and using some parallel process techniques may waste some space as a matter of course.  If you see excessive space being used, you should investigate why, this might be a good starting point: https://jonathanlewis.wordpress.com/2016/09/11/space-usage/ (googling for things like Jonathan's name with something like LOB space usage can find some very interesting information - but as with anything on the internet, evaluate whether you can trust it.)

                                 

                                Tuning is an iterative process, once you fix a gross error like redo sizing, you may see other issues.  Just remember to stop when things are "good enough" (though curiosity is a good thing in our line of work).

                                • 28.