This discussion is archived
1 2 Previous Next 24 Replies Latest reply: Nov 2, 2013 2:27 PM by Nikolay Savvinov RSS

Oracle Performance

KSG Explorer
Currently Being Moderated

Hi Friends,

10.2.0.4.0 on Linux

Background:
We have database running on OS cluster.
Faced server performance issue on server A, Cpu spiked to 100% used. On top command found a few query (small queries in general, which run in Daily batch job) were cpu 100% used and the same queries reported in AWR. Due to this, the database failed over to server B, there the queries run as successully without issue.
Server A & B are OS clustered and using same hard disk memory.

After server reboot/failover, the Daily batch job runs successfully without any issue for a few days (~6 to 10 days). The 11th day the batch performs very slow.

we are trying to find fix,
Now we are not suspecting the issue is with Oracle side. The issue may be with OS/hardware side.

Any suggesitions/help will be appreciated.

Thanks
KSG

  • 1. Re: Oracle Performance
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    1) post some evidence to support your analysis -- e.g. key sections of AWR report(s): header, load profile, top wait events, time model statistics, OS statistics, top SQL

    2) be specific of what you're asking from the community

     

    Best regards,

      Nikolay

  • 2. Re: Oracle Performance
    sb92075 Guru
    Currently Being Moderated

    do the SQL involved use bind variables?

  • 3. Re: Oracle Performance
    KSG Explorer
    Currently Being Moderated

    Hi sb,

     

    The query wasn't using bind variable.

     

    I will post the AWR repot.

  • 4. Re: Oracle Performance
    sb92075 Guru
    Currently Being Moderated

    it would be helpful if you could post EXPLAIN PLAN for both the normal SQL behavior & when SQL is long running.

  • 5. Re: Oracle Performance
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    1) what is the duration of the interval for which the report was generated?

    2) average CPU usage by the entire server is around 40% (which doesn't exclude, of course, possibility of short-term CPU spikes)

    3) CPU time as the top "event" with 91% only means that the database wasn't spending much time on things other than CPU

      (like reading data from disks)

    4) the top 3 statements posted are responsible for just about 30% of DB time

     

    I'm not sure what you want us to do with this. If there was a real problem, information posted doesn't tell us anything about it.

     

    Best regards,

      Nikolay


  • 6. Re: Oracle Performance
    jgarry Guru
    Currently Being Moderated

    KSG wrote:

     

    Hi sb,

     

    The query wasn't using bind variable.

     

    You might want to go through some articles mentioned here: OracleBlog: Bind Variables in PL/SQL

  • 7. Re: Oracle Performance
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    jgarry wrote:

     

    KSG wrote:

     

    Hi sb,

     

    The query wasn't using bind variable.

     

    You might want to go through some articles mentioned here: OracleBlog: Bind Variables in PL/SQL

     

    given that the potential benefit here is 0.63%, I would highly recommend to leave the literals alone.

     

    Best regards,

      Nikolay

  • 8. Re: Oracle Performance
    jgarry Guru
    Currently Being Moderated

    Unless I've missed something, I was referring to the fact that without more information (like the sql and plans for the daily batch job and elsewhere that suddenly changes performance), he could be assuming binds aren't used when they are.  Bind peeking is the first thing to look for in the Rainy Monday scenario, wouldn't you think?

  • 9. Re: Oracle Performance
    Nikolay Savvinov Guru
    Currently Being Moderated

    Hi,

     

    So far I don't see any evidence suggesting a "Rainy Monday" issue here. Or any other issue for that matter.

     

    And your previous link didn't mention bind peeking. Basically it's just a collection of banalities like "bind variables are good"

    and "don't bind constants", so I thought you were suggesting the OP to improve his database performance by implementing

    binds.

     

    Since hard parse is only taking 0.63% of his DB time, I suggested that it wasn't worth the trouble.

     

    Best regards,

       Nikolay

  • 10. Re: Oracle Performance
    KSG Explorer
    Currently Being Moderated

    Hi Nikolay,

     

    Thanks you for your time and help.

     

    Below are the 2 complete AWR reports;  First report, on server A ( issue server), Second report, on Server B( where the same batch job ran without issue).

    First report is take for the interval of 9 hours ( the batch was facing issue). Second report for the interval of 3 hours ( the batch run time).

     

    Awr Report 1: Batch job ran with performance issue.

     

    WORKLOAD REPOSITORY report for

     

    Report Summary

    Cache Sizes

    Begin

    End

    Buffer Cache:

    3,408M

    3,232M

    Std Block Size:

    16K

    Shared Pool Size:

    1,328M

    1,504M

    Log Buffer:

    33,792K

    Load Profile

    Per Second

    Per Transaction

    Redo size:

    391,554.90

    38,147.29

    Logical reads:

    14,776.03

    1,439.56

    Block changes:

    2,408.12

    1. 234.61

    Physical reads:

    1. 7.59
    2. 0.74

    Physical writes:

    1. 28.22
    2. 2.75

    User calls:

    1. 111.14
    2. 10.83

    Parses:

    1. 24.35
    2. 2.37

    Hard parses:

    1. 1.18
    2. 0.12

    Sorts:

    1. 315.91
    2. 30.78

    Logons:

    1. 0.49
    2. 0.05

    Executes:

    1. 489.49
    2. 47.69

    Transactions:

    1. 10.26

     

     

    % Blocks changed per Read:

    1. 16.30

    Recursive Call %:

    1. 85.81

    Rollback per transaction %:

    1. 0.00

    Rows per Sort:

    1. 29.43

    Instance Efficiency Percentages (Target 100%)

    Buffer Nowait %:

    1. 99.98

    Redo NoWait %:

    1. 100.00

    Buffer Hit %:

    1. 99.96

    In-memory Sort %:

    1. 100.00

    Library Hit %:

    1. 99.49

    Soft Parse %:

    1. 95.14

    Execute to Parse %:

    1. 95.03

    Latch Hit %:

    1. 99.67

    Parse CPU to Parse Elapsd %:

    1. 89.78

    % Non-Parse CPU:

    1. 99.48

    Shared Pool Statistics

    Begin

    End

    Memory Usage %:

    1. 64.13
    2. 24.06

    % SQL with executions>1:

    1. 58.87
    2. 92.10

    % Memory for SQL w/exec>1:

    1. 46.85
    2. 85.34

    Top 5 Timed Events

    Event

    Waits

    Time(s)

    Avg Wait(ms)

    % Total Call Time

    Wait Class

    CPU time

     

    55,059

     

    1. 91.3

     

    log file sync

    181,042

    2,435

    13

    1. 4.0

    Commit

    log file parallel write

    286,765

    1,410

    5

    1. 2.3

    System I/O

    db file sequential read

    103,182

    648

    6

    1. 1.1

    User I/O

    Backup: sbtwrite2

    40,251

    368

    9

    .6

    Administrative

     

    Back to Top

    Time Model Statistics

    • Total time in database user-calls (DB Time): 60294.1s
    • 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 Name

    Time (s)

    % of DB Time

    sql execute elapsed time

    57,636.55

    1. 95.59

    DB CPU

    55,059.16

    1. 91.32

    PL/SQL execution elapsed time

    1. 838.48
    2. 1.39

    parse time elapsed

    1. 380.93
    2. 0.63

    hard parse elapsed time

    1. 336.57
    2. 0.56

    connection management call elapsed time

    1. 49.51
    2. 0.08

    RMAN cpu time (backup/restore)

    1. 33.40
    2. 0.06

    failed parse elapsed time

    1. 29.35
    2. 0.05

    PL/SQL compilation elapsed time

    1. 8.82
    2. 0.01

    hard parse (sharing criteria) elapsed time

    1. 6.81
    2. 0.01

    repeated bind elapsed time

    1. 2.32
    2. 0.00

    hard parse (bind mismatch) elapsed time

    1. 1.27
    2. 0.00

    sequence load elapsed time

    1. 0.31
    2. 0.00

    inbound PL/SQL rpc elapsed time

    1. 0.30
    2. 0.00

    DB time

    60,294.05

     

    background elapsed time

    3,138.20

     

    background cpu time

    1. 431.46

     

    Back to Wait Events Statistics
    Back to Top

    Wait Class

    • s - second
    • cs - centisecond - 100th of a second
    • ms - millisecond - 1000th of a second
    • us - microsecond - 1000000th of a second
    • ordered by wait time desc, waits desc
      Wait Class

    Waits

    %Time -outs

    Total Wait Time (s)

    Avg wait (ms)

    Waits /txn

    Commit

    181,042

    1. 0.06

    2,435

    13

    1. 0.61

    System I/O

    502,977

    1. 1.13

    2,394

    5

    1. 1.70

    Concurrency

    349,296

    1. 3.01

    764

    2

    1. 1.18

    User I/O

    119,683

    1. 0.00

    715

    6

    1. 0.40

    Administrative

    40,293

    1. 0.00

    372

    9

    1. 0.14

    Other

    19,417

    1. 0.65

    85

    4

    1. 0.07

    Configuration

    621

    1. 10.79

    31

    50

    1. 0.00

    Network

    3,400,860

    1. 0.00

    29

    0

    1. 11.49

    Application

    5,637

    1. 0.00

    21

    4

    1. 0.02

    Back to Wait Events Statistics
    Back to Top

    Wait Events

    • s - second
    • cs - centisecond - 100th of a second
    • ms - millisecond - 1000th of a second
    • us - microsecond - 1000000th of a second
    • ordered by wait time desc, waits desc (idle events last)

    Event

    Waits

    %Time -outs

    Total Wait Time (s)

    Avg wait (ms)

    Waits /txn

    log file sync

    181,042

    1. 0.06

    2,435

    13

    1. 0.61

    log file parallel write

    286,765

    1. 0.00

    1,410

    5

    1. 0.97

    db file sequential read

    103,182

    1. 0.00

    648

    6

    1. 0.35

    Backup: sbtwrite2

    40,251

    1. 0.00

    368

    9

    1. 0.14

    io done

    25,357

    1. 22.37

    355

    14

    1. 0.09

    buffer busy waits

    101,776

    1. 0.15

    254

    2

    1. 0.34

    Log archive I/O

    11,333

    1. 0.00

    241

    21

    1. 0.04

    latch: cache buffers chains

    194,224

    1. 0.00

    186

    1

    1. 0.66

    db file parallel write

    74,105

    1. 0.00

    161

    2

    1. 0.25

    cursor: pin S wait on X

    10,590

    1. 97.72

    137

    13

    1. 0.04

    control file sequential read

    79,866

    1. 0.00

    90

    1

    1. 0.27

    library cache load lock

    1,091

    1. 0.00

    78

    72

    1. 0.00

    log file sequential read

    11,918

    1. 0.00

    78

    7

    1. 0.04

    control file parallel write

    13,025

    1. 0.00

    57

    4

    1. 0.04

    latch: library cache

    2,537

    1. 0.00

    55

    22

    1. 0.01

    latch: In memory undo latch

    35,446

    1. 0.00

    42

    1

    1. 0.12

    db file scattered read

    5,045

    1. 0.00

    39

    8

    1. 0.02

    imm op

    65

    1. 29.23

    38

    589

    1. 0.00

    log file switch completion

    533

    1. 1.69

    31

    58

    1. 0.00

    inactive session

    28

    1. 92.86

    25

    907

    1. 0.00

    enq: TX - row lock contention

    5,267

    1. 0.00

    18

    3

    1. 0.02

    SQL*Net message to client

    2,912,963

    1. 0.00

    14

    0

    1. 9.84

    SQL*Net more data from client

    219,571

    1. 0.00

    10

    0

    1. 0.74

    cursor: pin S

    11,596

    1. 0.00

    9

    1

    1. 0.04

    direct path read temp

    1,765

    1. 0.00

    8

    5

    1. 0.01

    local write wait

    2,332

    1. 0.00

    8

    3

    1. 0.01

    latch: library cache pin

    1,102

    1. 0.00

    8

    7

    1. 0.00

    PX Deq Credit: send blkd

    1,231

    1. 0.00

    5

    4

    1. 0.00

    SQL*Net more data to client

    268,326

    1. 0.00

    5

    0

    1. 0.91

    direct path read

    2,848

    1. 0.00

    5

    2

    1. 0.01

    read by other session

    737

    1. 0.00

    3

    5

    1. 0.00

    direct path write temp

    1,787

    1. 0.00

    3

    2

    1. 0.01

    latch free

    292

    1. 0.00

    2

    7

    1. 0.00

    enq: RO - fast object reuse

    166

    1. 0.00

    2

    12

    1. 0.00

    os thread startup

    35

    1. 0.00

    2

    44

    1. 0.00

    slave exit

    6

    1. 50.00

    1

    245

    1. 0.00

    latch: shared pool

    160

    1. 0.00

    1

    9

    1. 0.00

    RMAN backup & recovery I/O

    240

    1. 0.00

    1

    5

    1. 0.00

    kksfbc child completion

    27

    1. 88.89

    1

    46

    1. 0.00

    log file single write

    324

    1. 0.00

    1

    3

    1. 0.00

    Backup: sbtbackup

    9

    1. 0.00

    1

    112

    1. 0.00

    Backup: sbtinfo2

    10

    1. 0.00

    1

    101

    1. 0.00

    Backup: sbtclose2

    9

    1. 0.00

    1

    75

    1. 0.00

    enq: KO - fast object checkpoint

    4

    1. 0.00

    1

    161

    1. 0.00

    wait list latch free

    52

    1. 0.00

    1

    12

    1. 0.00

    library cache lock

    4

    1. 0.00

    1

    155

    1. 0.00

    LGWR wait for redo copy

    4,719

    1. 0.25

    1

    0

    1. 0.02

    direct path write

    1,984

    1. 0.00

    1

    0

    1. 0.01

    cursor: mutex X

    2,182

    1. 0.00

    1

    0

    1. 0.01

    latch: redo allocation

    754

    1. 0.00

    0

    1

    1. 0.00

    control file single write

    44

    1. 0.00

    0

    10

    1. 0.00

    SQL*Net break/reset to client

    200

    1. 0.00

    0

    2

    1. 0.00

    switch logfile command

    3

    1. 0.00

    0

    113

    1. 0.00

    Backup: sbtremove2

    2

    1. 0.00

    0

    118

    1. 0.00

    log buffer space

    7

    1. 0.00

    0

    20

    1. 0.00

    Backup: sbtinit

    3

    1. 0.00

    0

    30

    1. 0.00

    rdbms ipc reply

    16

    1. 0.00

    0

    6

    1. 0.00

    enq: CF - contention

    10

    1. 0.00

    0

    7

    1. 0.00

    latch: undo global data

    45

    1. 0.00

    0

    1

    1. 0.00

    reliable message

    105

    1. 0.00

    0

    1

    1. 0.00

    db file parallel read

    3

    1. 0.00

    0

    18

    1. 0.00

    latch: session allocation

    193

    1. 0.00

    0

    0

    1. 0.00

    row cache lock

    66

    1. 0.00

    0

    1

    1. 0.00

    enq: HW - contention

    9

    1. 0.00

    0

    3

    1. 0.00

    latch: object queue header operation

    63

    1. 0.00

    0

    0

    1. 0.00

    PX qref latch

    8

    1. 75.00

    0

    3

    1. 0.00

    enq: TX - index contention

    50

    1. 0.00

    0

    0

    1. 0.00

    enq: TX - contention

    3

    1. 0.00

    0

    5

    1. 0.00

    latch: cache buffers lru chain

    70

    1. 0.00

    0

    0

    1. 0.00

    latch: enqueue hash chains

    21

    1. 0.00

    0

    1

    1. 0.00

    latch: row cache objects

    16

    1. 0.00

    0

    1

    1. 0.00

    cursor: mutex S

    6

    1. 0.00

    0

    1

    1. 0.00

    PX Deq: Signal ACK

    45

    1. 8.89

    0

    0

    1. 0.00

    latch: redo writing

    13

    1. 0.00

    0

    0

    1. 0.00

    Backup: sbtend

    3

    1. 0.00

    0

    1

    1. 0.00

    latch: library cache lock

    11

    1. 0.00

    0

    0

    1. 0.00

    enq: FB - contention

    7

    1. 0.00

    0

    0

    1. 0.00

    buffer deadlock

    34

    1. 97.06

    0

    0

    1. 0.00

    enq: PS - contention

    13

    1. 0.00

    0

    0

    1. 0.00

    latch: messages

    6

    1. 0.00

    0

    0

    1. 0.00

    latch: checkpoint queue latch

    8

    1. 0.00

    0

    0

    1. 0.00

    enq: TX - allocate ITL entry

    1

    1. 0.00

    0

    1

    1. 0.00

    undo segment extension

    58

    1. 100.00

    0

    0

    1. 0.00

    Backup: sbtinit2

    3

    1. 0.00

    0

    0

    1. 0.00

    SQL*Net message from client

    2,912,809

    1. 0.00

    3,475,877

    1193

    1. 9.84

    jobq slave wait

    23,172

    1. 49.74

    56,268

    2428

    1. 0.08

    Streams AQ: qmn slave idle wait

    1,943

    1. 0.10

    37,310

    19202

    1. 0.01

    Streams AQ: waiting for messages in the queue

    5,764

    1. 99.95

    28,118

    4878

    1. 0.02

    Streams AQ: qmn coordinator idle wait

    2,802

    1. 36.72

    28,091

    10025

    1. 0.01

    virtual circuit status

    961

    1. 100.00

    28,052

    29190

    1. 0.00

    Streams AQ: waiting for time management or cleanup tasks

    61

    1. 81.97

    21,677

    355364

    1. 0.00

    PX Idle Wait

    2,422

    1. 99.01

    4,697

    1940

    1. 0.01

    i/o slave wait

    39,645

    1. 0.70

    290

    7

    1. 0.13

    PX Deq: Execution Msg

    548

    1. 0.00

    2

    3

    1. 0.00

    PX Deq Credit: need buffer

    294

    1. 0.00

    1

    3

    1. 0.00

    PX Deq: Execute Reply

    562

    1. 0.00

    1

    1

    1. 0.00

    PX Deq: Join ACK

    18

    1. 0.00

    0

    4

    1. 0.00

    PX Deq: Parse Reply

    24

    1. 0.00

    0

    1

    1. 0.00

    SGA: MMAN sleep for component shrink

    26

    1. 0.00

    0

    0

    1. 0.00

    PX Deq: Msg Fragment

    15

    1. 0.00

    0

    0

    1. 0.00

    class slave wait

    14

    1. 0.00

    0

    0

    1. 0.00

    Back to Wait Events Statistics
    Back to Top

    Background Wait Events

    • ordered by wait time desc, waits desc (idle events last)
      Event

    Waits

    %Time -outs

    Total Wait Time (s)

    Avg wait (ms)

    Waits /txn

    log file parallel write

    286,765

    1. 0.00

    1,410

    5

    1. 0.97

    Log archive I/O

    11,233

    1. 0.00

    239

    21

    1. 0.04

    db file parallel write

    74,105

    1. 0.00

    161

    2

    1. 0.25

    log file sequential read

    11,815

    1. 0.00

    77

    7

    1. 0.04

    control file parallel write

    12,357

    1. 0.00

    55

    4

    1. 0.04

    control file sequential read

    23,487

    1. 0.00

    53

    2

    1. 0.08

    direct path read

    1,840

    1. 0.00

    1

    1

    1. 0.01

    log file single write

    324

    1. 0.00

    1

    3

    1. 0.00

    events in waitclass Other

    5,045

    1. 0.24

    1

    0

    1. 0.02

    os thread startup

    16

    1. 0.00

    1

    44

    1. 0.00

    db file sequential read

    105

    1. 0.00

    1

    7

    1. 0.00

    direct path write

    1,840

    1. 0.00

    0

    0

    1. 0.01

    log buffer space

    5

    1. 0.00

    0

    6

    1. 0.00

    latch: cache buffers chains

    33

    1. 0.00

    0

    0

    1. 0.00

    latch: redo writing

    13

    1. 0.00

    0

    0

    1. 0.00

    db file scattered read

    1

    1. 0.00

    0

    2

    1. 0.00

    latch: shared pool

    2

    1. 0.00

    0

    1

    1. 0.00

    latch: library cache

    1

    1. 0.00

    0

    0

    1. 0.00

    buffer busy waits

    7

    1. 0.00

    0

    0

    1. 0.00

    latch: In memory undo latch

    1

    1. 0.00

    0

    0

    1. 0.00

    rdbms ipc message

    447,859

    1. 26.89

    384,738

    859

    1. 1.51

    pmon timer

    10,205

    1. 98.72

    28,137

    2757

    1. 0.03

    Streams AQ: qmn coordinator idle wait

    2,802

    1. 36.72

    28,091

    10025

    1. 0.01

    smon timer

    325

    1. 23.38

    27,106

    83403

    1. 0.00

    Streams AQ: qmn slave idle wait

    962

    1. 0.00

    24,569

    25539

    1. 0.00

    Streams AQ: waiting for time management or cleanup tasks

    61

    1. 81.97

    21,677

    355364

    1. 0.00

    SGA: MMAN sleep for component shrink

    26

    1. 0.00

    0

    0

    1. 0.00

    Back to Wait Events Statistics
    Back to Top

    Operating System Statistics

    Statistic

    Total

    BUSY_TIME

    18,172,314

    IDLE_TIME

    27,956,600

    IOWAIT_TIME

    1,974,547

    NICE_TIME

    1,198

    SYS_TIME

    565,354

    USER_TIME

    17,440,726

    LOAD

    8

    RSRC_MGR_CPU_WAIT_TIME

    0

    PHYSICAL_MEMORY_BYTES

    67,305,512,960

    NUM_CPUS

    16

    NUM_CPU_SOCKETS

    4

    Back to Wait Events Statistics
    Back to Top

    Service Statistics

    • ordered by DB Time

    Service Name

    DB Time (s)

    DB CPU (s)

    Physical Reads

    Logical Reads

    SYS$USERS

    60,293.00

    55,058.60

    214,096

    425,704,031

    SYS$BACKGROUND

    1. 0.00
    2. 0.00

    4,844

    320,665

    db1040p.na.mars

    1. 0.00
    2. 0.00

    0

    0

    db1040pXDB

    1. 0.00
    2. 0.00

    0

    0

    Back to Wait Events Statistics
    Back to Top

    Service Wait Class Stats

    • Wait Class info for services in the Service Statistics section.
    • Total Waits and Time Waited displayed for the following wait classes: User I/O, Concurrency, Administrative, Network
    • Time Waited (Wt Time) in centisecond (100th of a second)
      Service Name

    User I/O Total Wts

    User I/O Wt Time

    Concurcy Total Wts

    Concurcy Wt Time

    Admin Total Wts

    Admin Wt Time

    Network Total Wts

    Network Wt Time

    SYS$USERS

    112689

    68802

    349232

    76280

    3

    34

    3389008

    2837

    SYS$BACKGROUND

    6992

    2695

    61

    72

    40290

    37126

    0

    0

    Back to Wait Events Statistics
    Back to Top

    SQL Statistics

      Back to Top  

    SQL ordered by Elapsed Time

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      Elapsed Time (s)

    CPU Time (s)

    Executions

    Elap per Exec (s)

    % Total DB Time

    SQL Id

    SQL Module

    SQL Text

    12,608

    12,598

    28

    1. 450.29
    2. 20.91

    3t4jx1kp1hxag

    JDBC Thin Client

    ####################

    3,932

    3,827

    6,384

    1. 0.62
    2. 6.52

    fp7j5rdtpvyzr

    1. perl.exe

    ####################

    3,902

    3,360

    991,018

    1. 0.00
    2. 6.47

    ajt6w7yb83y6g

    JDBC Thin Client

    ####################

    3,901

    3,822

    6,384

    1. 0.61
    2. 6.47

    4ayabt154bxny

    1. perl.exe

    ####################

    3,495

    3,472

    0

     

    1. 5.80

    2292hvpq2ps88

    1. perl.exe

    ####################

    3,119

    3,053

    0

     

    1. 5.17

    ft262crty8jwg

    1. perl.exe

    ####################

    2,185

    2,149

    3,537

    1. 0.62
    2. 3.62

    44xsq41m18cm8

    1. perl.exe

    ####################

    2,179

    2,133

    3,537

    1. 0.62
    2. 3.61

    4v6q88hcgkc86

    1. perl.exe

    ####################

    1,309

    1,215

    108,780

    1. 0.01
    2. 2.17

    6wnr6jscfbmk5

    JDBC Thin Client

    ####################

    1,288

    1,285

    2,027

    1. 0.64
    2. 2.14

    4kdvt5v1h6dvq

    1. perl.exe

    ####################

    1,283

    1,278

    2,026

    1. 0.63
    2. 2.13

    gsukpn945b05w

    1. perl.exe

    ####################

    1,207

    1,136

    108,781

    1. 0.01
    2. 2.00

    77szpup3f39r5

    JDBC Thin Client

    ####################

    953

    855

    991,227

    1. 0.00
    2. 1.58

    0h16msq5g0883

    JDBC Thin Client

    ####################

    836

    759

    18,383

    1. 0.05
    2. 1.39

    ddb1wkxqx3nur

    JDBC Thin Client

    ####################

    732

    731

    23

    1. 31.85
    2. 1.21

    5jyd91d6zzpvf

    JDBC Thin Client

    ####################

    Back to SQL Statistics
    Back to Top

    SQL ordered by CPU Time

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • % Total DB Time is the Elapsed Time of the SQL statement divided into the Total Database Time multiplied by 100
      CPU Time (s)

    Elapsed Time (s)

    Executions

    CPU per Exec (s)

    % Total DB Time

    SQL Id

    SQL Module

    SQL Text

    12,598

    12,608

    28

    1. 449.93
    2. 20.91

    3t4jx1kp1hxag

    JDBC Thin Client

    ####################

    3,827

    3,932

    6,384

    1. 0.60
    2. 6.52

    fp7j5rdtpvyzr

    1. perl.exe

    ####################

    3,822

    3,901

    6,384

    1. 0.60
    2. 6.47

    4ayabt154bxny

    1. perl.exe

    ####################

    3,472

    3,495

    0

     

    1. 5.80

    2292hvpq2ps88

    1. perl.exe

    ####################

    3,360

    3,902

    991,018

    1. 0.00
    2. 6.47

    ajt6w7yb83y6g

    JDBC Thin Client

    ####################

    3,053

    3,119

    0

     

    1. 5.17

    ft262crty8jwg

    1. perl.exe

    ####################

    2,149

    2,185

    3,537

    1. 0.61
    2. 3.62

    44xsq41m18cm8

    1. perl.exe

    ####################

    2,133

    2,179

    3,537

    1. 0.60
    2. 3.61

    4v6q88hcgkc86

    1. perl.exe

    ####################

    1,285

    1,288

    2,027

    1. 0.63
    2. 2.14

    4kdvt5v1h6dvq

    1. perl.exe

    ####################

    1,278

    1,283

    2,026

    1. 0.63
    2. 2.13

    gsukpn945b05w

    1. perl.exe

    ####################

    1,215

    1,309

    108,780

    1. 0.01
    2. 2.17

    6wnr6jscfbmk5

    JDBC Thin Client

    ####################

    1,136

    1,207

    108,781

    1. 0.01
    2. 2.00

    77szpup3f39r5

    JDBC Thin Client

    ####################

    855

    953

    991,227

    1. 0.00
    2. 1.58

    0h16msq5g0883

    JDBC Thin Client

    ####################

    759

    836

    18,383

    1. 0.04
    2. 1.39

    ddb1wkxqx3nur

    JDBC Thin Client

    ####################

    731

    732

    23

    1. 31.79
    2. 1.21

    5jyd91d6zzpvf

    JDBC Thin Client

    ####################

    Back to SQL Statistics
    Back to Top

    SQL ordered by Gets

    • Resources reported for PL/SQL code includes the resources used by all SQL statements called by the code.
    • Total Buffer Gets: 426,029,603
    • Captured SQL account for 94.7% of Total
      Buffer Gets

    Executions

    Gets per Exec

    %Total

    CPU Time (s)

    Elapsed Time (s)

    SQL Id

    SQL Module

    SQL Text

    69,528,558

    6,384

    10,891.06

    1. 16.32
    2. 3826.86
    3. 3931.55

    fp7j5rdtpvyzr

    1. perl.exe

    ####################

    69,392,312

    6,384

    10,869.72

    1. 16.29
    2. 3822.09
    3. 3901.37

    4ayabt154bxny

    1. perl.exe

    ####################

    60,018,338

    0

     

    1. 14.09
    2. 3053.02
    3. 3119.08

    ft262crty8jwg

    1. perl.exe

    ####################

    58,085,403

    0

     

    1. 13.63
    2. 3472.12
    3. 3495.13

    2292hvpq2ps88

    1. perl.exe

    ####################

    41,039,600

    991,018

    1. 41.41
    2. 9.63
    3. 3359.86
    4. 3901.57

    ajt6w7yb83y6g

    JDBC Thin Client

    ####################

    38,899,441

    3,537

    10,997.86

    1. 9.13
    2. 2132.51
    3. 2178.89

    4v6q88hcgkc86

    1. perl.exe

    ####################

    38,816,256

    3,537

    10,974.34

    1. 9.11
    2. 2149.09
    3. 2185.30

    44xsq41m18cm8

    1. perl.exe

    ####################

    22,281,987

    2,026

    10,998.02

    1. 5.23
    2. 1277.97
    3. 1282.70

    gsukpn945b05w

    1. perl.exe

    ####################

    22,234,760

    2,027

    10,969.29

    1. 5.22
    2. 1285.28
    3. 1288.21

    4kdvt5v1h6dvq

    1. perl.exe

    ####################

    21,426,458

    1,185

    18,081.40

    1. 5.03
    2. 218.36
    3. 395.22

    5rvpxhx36zv5p

    JDBC Thin Client

    ####################

    16,646,123

    4,732

    3,517.78

    1. 3.91
    2. 103.16
    3. 108.00

    9wcygagbkuz86

    JDBC Thin Client

    ####################

    15,870,028

    991,227

    1. 16.01
    2. 3.73
    3. 855.05
    4. 953.23

    0h16msq5g0883

    JDBC Thin Client

    ####################

    14,797,792

    108,780

    1. 136.03
    2. 3.47
    3. 1214.52
    4. 1308.79

    6wnr6jscfbmk5

    JDBC Thin Client

    ####################

    14,307,142

    108,781

    1. 131.52
    2. 3.36
    3. 1135.93
    4. 1206.59

    77szpup3f39r5

    JDBC Thin Client

    ####################

    12,641,797

    18,383

    1. 687.69
    2. 2.97
    3. 759.34
    4. 835.80

    ddb1wkxqx3nur

    JDBC Thin Client

    ####################

    5,588,688

    1

    5,588,688.00

    1. 1.31
    2. 458.60
    3. 478.00

    9ywzuf68juzgd

    1. perl.exe

    ####################

    5,258,373

    23

    228,624.91

    1. 1.23
    2. 166.83
    3. 167.62

    g5c5k52f2r61z

    JDBC Thin Client

    ####################

    4,810,938

    28,931

    1. 166.29
    2. 1.13
    3. 432.43
    4. 458.02

    dpqsnzvmtnxs1

    1. perl.exe

    ####################

    4,268,605

    1

    4,268,605.00

    1. 1.00
    2. 106.21
    3. 110.38

    ah828dkp4phkb

    1. perl.exe

    ####################

    Back to SQL Statistics
    Back to Top

    SQL ordered by Reads

    • Total Disk Reads: 218,890
    • Captured SQL account for 34.0% of Total
      Physical Reads

    Executions

    Reads per Exec

    %Total

    CPU Time (s)

    Elapsed Time (s)

    SQL Id

    SQL Module

    SQL Text

    29,283

    1,185

    1. 24.71
    2. 13.38
    3. 218.36
    4. 395.22

    5rvpxhx36zv5p

    JDBC Thin Client

    26,133

    1

    26,133.00

    1. 11.94
    2. 12.86
    3. 37.96

    65m048ggg11f0

    1. perl.exe

    23,782

    1

    23,782.00

    1. 10.86
    2. 7.42
    3. 10.33

    5aj6bybktzypp

    TOAD background query session

    6,664

    1

    6,664.00

    1. 3.04
    2. 30.78
    3. 72.09

    7hcf2jznp9zp0

    1. perl.exe

    6,652

    1

    6,652.00

    1. 3.04
    2. 17.02
    3. 21.59

    9s1fxqm8fgr98

    1. perl.exe

    6,643

    2

    3,321.50

    1. 3.03
    2. 29.85
    3. 34.36

    28tqbpr27rhrj

    1. perl.exe

    6,428

    10

    1. 642.80
    2. 2.94
    3. 132.00
    4. 194.02

    39crjr9rcdx41

    JDBC Thin Client

    6,404

    35

    1. 182.97
    2. 2.93
    3. 15.65
    4. 54.77

    7qamuw1rdar9s

    1. perl.exe

    6,144

    1

    6,144.00

    1. 2.81
    2. 17.88
    3. 30.35

    gm5d6um7fz462

    1. perl.exe

    6,133

    1

    6,133.00

    1. 2.80
    2. 17.77
    3. 30.16

    5nvncdpa89g9y

    1. perl.exe

    2,455

    1

    2,455.00

    1. 1.12
    2. 7.38
    3. 10.83

    6kq7kga1jvvvu

    SQL*Plus

    2,306

    1

    2,306.00

    1. 1.05
    2. 106.21
    3. 110.38

    ah828dkp4phkb

    1. perl.exe

    2,279

    1

    2,279.00

    1. 1.04
    2. 0.36
    3. 1.53

    braa44h7jhq28

    1. perl.exe

    2,229

    1

    2,229.00

    1. 1.02
    2. 2.97
    3. 5.57

    f6avbnng64n03

    1. perl.exe

    Back to SQL Statistics
    Back to Top

    SQL ordered by Executions

    • Total Executions: 14,113,143
    • Captured SQL account for 69.0% of Total

    Executions

    Rows Processed

    Rows per Exec

    CPU per Exec (s)

    Elap per Exec (s)

    SQL Id

    SQL Module

    SQL Text

    1,038,752

    1,038,225

    1. 1.00
    2. 0.00
    3. 0.00

    5dxjxn972ppwp

    JDBC Thin Client

    1,014,992

    90,158

    1. 0.09
    2. 0.00
    3. 0.00

    87fzyp4nvktqr

    JDBC Thin Client

    1,014,847

    1,014,593

    1. 1.00
    2. 0.00
    3. 0.00

    5az5ph1341gzf

    JDBC Thin Client

    991,227

    7,929,488

    1. 8.00
    2. 0.00
    3. 0.00

    0h16msq5g0883

    JDBC Thin Client

    991,103

    990,683

    1. 1.00
    2. 0.00
    3. 0.00

    703y5btw73y52

    JDBC Thin Client

    991,090

    990,654

    1. 1.00
    2. 0.00
    3. 0.00

    3qntqb61rht4n

    JDBC Thin Client

    991,018

    991,013

    1. 1.00
    2. 0.00
    3. 0.00

    ajt6w7yb83y6g

    JDBC Thin Client

    182,089

    182,052

    1. 1.00
    2. 0.00
    3. 0.00

    9h7sb6as36bn6

    JDBC Thin Client

    109,020

    108,952

    1. 1.00
    2. 0.00
    3. 0.00

    3h1wcp58vk7yr

    JDBC Thin Client

    108,797

    108,797

    1. 1.00
    2. 0.00
    3. 0.00

    27ady7aus9h6p

    JDBC Thin Client

    Back to SQL Statistics
    Back to Top

    SQL ordered by Parse Calls

    • Total Parse Calls: 702,031
    • Captured SQL account for 60.0% of Total
      Parse Calls

    Executions

    % Total Parses

    SQL Id

    SQL Module

    SQL Text

    86,796

    86,796

    1. 12.36

    fn2102cw3sg7q

    1. perl.exe

    45,195

    45,195

    1. 6.44

    5vw6s1gtrfzcx

    SQL*Plus

    42,050

    42,050

    1. 5.99

    ftj9uawt4wwzb

     

    28,931

    28,931

    1. 4.12

    1xds3av39rjku

    1. perl.exe

    28,931

    28,931

    1. 4.12

    687ma06zf063z

    1. perl.exe

    14,605

    14,605

    1. 2.08

    ftn5wc4tk1mkj

    JDBC Thin Client

    12,768

    12,768

    1. 1.82

    4qunwz0nmxbsu

    1. perl.exe

    12,768

    12,768

    1. 1.82

    77q3yj941gbkt

    1. perl.exe

    8,458

    8,458

    1. 1.20

    a1mbfp580hw3k

     

    7,452

    7,452

    1. 1.06

    f6b71sxqnxs2v

    JDBC Thin Client

    7,380

    7,379

    1. 1.05

    0h6b2sajwb74n

     

    Back to SQL Statistics
    Back to Top

    SQL ordered by Sharable Memory

    No data exists for this section of the report.

    Back to SQL Statistics
    Back to Top

    SQL ordered by Version Count

    No data exists for this section of the report.

    Back to SQL Statistics
    Back to Top

    Back to Top

    Instance Activity Statistics

    Back to Top

    Instance Activity Stats

    Statistic

    Total

    per Second

    per Trans

    CPU used by this session

    5,544,568

    1. 192.30
    2. 18.74

    CPU used when call started

    5,542,827

    1. 192.24
    2. 18.73

    CR blocks created

    158,202

    1. 5.49
    2. 0.53

    Cached Commit SCN referenced

    376,723

    1. 13.07
    2. 1.27

    Commit SCN cached

    58

    1. 0.00
    2. 0.00

    DB time

    14,824,036

    1. 514.14
    2. 50.09

    DBWR checkpoint buffers written

    734,728

    1. 25.48
    2. 2.48

    DBWR checkpoints

    218

    1. 0.01
    2. 0.00

    DBWR object drop buffers written

    2,233

    1. 0.08
    2. 0.01

    DBWR parallel query checkpoint buffers written

    2,105

    1. 0.07
    2. 0.01

    DBWR revisited being-written buffer

    33,013

    1. 1.14
    2. 0.11

    DBWR tablespace checkpoint buffers written

    0

    1. 0.00
    2. 0.00

    DBWR thread checkpoint buffers written

    5,005

    1. 0.17
    2. 0.02

    DBWR transaction table writes

    7,106

    1. 0.25
    2. 0.02

    DBWR undo block writes

    322,996

    1. 11.20
    2. 1.09

    DFO trees parallelized

    5

    1. 0.00
    2. 0.00

    IMU CR rollbacks

    16,587

    1. 0.58
    2. 0.06

    IMU Flushes

    47,211

    1. 1.64
    2. 0.16

    IMU Redo allocation size

    367,659,756

    12,751.58

    1,242.32

    IMU commits

    197,451

    1. 6.85
    2. 0.67

    IMU contention

    13,083

    1. 0.45
    2. 0.04

    IMU ktichg flush

    1

    1. 0.00
    2. 0.00

    IMU pool not allocated

    67,778

    1. 2.35
    2. 0.23

    IMU recursive-transaction flush

    421

    1. 0.01
    2. 0.00

    IMU undo allocation size

    784,119,120

    27,195.69

    2,649.54

    IMU- failed to get a private strand

    67,778

    1. 2.35
    2. 0.23

    OS Block input operations

    53,388,107

    1,851.67

    1. 180.40

    OS Block output operations

    96,073,038

    3,332.11

    1. 324.63

    OS Involuntary context switches

    1,645,184

    1. 57.06
    2. 5.56

    OS Page faults

    48

    1. 0.00
    2. 0.00

    OS Page reclaims

    22,473,227

    1. 779.44
    2. 75.94

    OS System time used

    55,691

    1. 1.93
    2. 0.19

    OS User time used

    5,545,040

    1. 192.32
    2. 18.74

    OS Voluntary context switches

    6,826,223

    1. 236.75
    2. 23.07

    PX local messages recv'd

    3,112

    1. 0.11
    2. 0.01

    PX local messages sent

    3,112

    1. 0.11
    2. 0.01

    Parallel operations downgraded to serial

    0

    1. 0.00
    2. 0.00

    Parallel operations not downgraded

    5

    1. 0.00
    2. 0.00

    SMON posted for undo segment shrink

    3

    1. 0.00
    2. 0.00

    SQL*Net roundtrips to/from client

    2,905,606

    1. 100.78
    2. 9.82

    SQL*Net roundtrips to/from dblink

    0

    1. 0.00
    2. 0.00

    active txn count during cleanout

    203,294

    1. 7.05
    2. 0.69

    application wait time

    2,115

    1. 0.07
    2. 0.01

    background checkpoints completed

    81

    1. 0.00
    2. 0.00

    background checkpoints started

    81

    1. 0.00
    2. 0.00

    background timeouts

    121,503

    1. 4.21
    2. 0.41

    branch node splits

    31

    1. 0.00
    2. 0.00

    buffer is not pinned count

    294,630,902

    10,218.71

    1. 995.56

    buffer is pinned count

    855,263,776

    29,663.20

    2,889.94

    bytes received via SQL*Net from client

    629,995,917

    21,850.22

    2,128.76

    bytes received via SQL*Net from dblink

    0

    1. 0.00
    2. 0.00

    bytes sent via SQL*Net to client

    937,112,164

    32,501.96

    3,166.51

    bytes sent via SQL*Net to dblink

    0

    1. 0.00
    2. 0.00

    calls to get snapshot scn: kcmgss

    15,738,827

    1. 545.87
    2. 53.18

    calls to kcmgas

    974,497

    1. 33.80
    2. 3.29

    calls to kcmgcs

    183,798

    1. 6.37
    2. 0.62

    change write time

    26,108

    1. 0.91
    2. 0.09

    cleanout - number of ktugct calls

    314,508

    1. 10.91
    2. 1.06

    cleanouts and rollbacks - consistent read gets

    66,572

    1. 2.31
    2. 0.22

    cleanouts only - consistent read gets

    52,434

    1. 1.82
    2. 0.18

    cluster key scan block gets

    3,468,198

    1. 120.29
    2. 11.72

    cluster key scans

    1,442,269

    1. 50.02
    2. 4.87

    commit batch performed

    0

    1. 0.00
    2. 0.00

    commit batch requested

    0

    1. 0.00
    2. 0.00

    commit batch/immediate performed

    18

    1. 0.00
    2. 0.00

    commit batch/immediate requested

    18

    1. 0.00
    2. 0.00

    commit cleanout failures: block lost

    5,408

    1. 0.19
    2. 0.02

    commit cleanout failures: buffer being written

    2,308

    1. 0.08
    2. 0.01

    commit cleanout failures: callback failure

    7,650

    1. 0.27
    2. 0.03

    commit cleanout failures: cannot pin

    25,088

    1. 0.87
    2. 0.08

    commit cleanouts

    886,900

    1. 30.76
    2. 3.00

    commit cleanouts successfully completed

    846,446

    1. 29.36
    2. 2.86

    commit immediate performed

    18

    1. 0.00
    2. 0.00

    commit immediate requested

    18

    1. 0.00
    2. 0.00

    commit txn count during cleanout

    151,762

    1. 5.26
    2. 0.51

    concurrency wait time

    76,353

    1. 2.65
    2. 0.26

    consistent changes

    326,513

    1. 11.32
    2. 1.10

    consistent gets

    341,395,232

    11,840.65

    1,153.58

    consistent gets - examination

    201,134,766

    6,975.98

    1. 679.64

    consistent gets direct

    35,944

    1. 1.25
    2. 0.12

    consistent gets from cache

    341,359,288

    11,839.40

    1,153.46

    current blocks converted for CR

    4

    1. 0.00
    2. 0.00

    cursor authentications

    12,826

    1. 0.44
    2. 0.04

    data blocks consistent reads - undo records applied

    247,673

    1. 8.59
    2. 0.84

    db block changes

    69,432,215

    2,408.12

    1. 234.61

    db block gets

    84,634,371

    2,935.38

    1. 285.98

    db block gets direct

    618

    1. 0.02
    2. 0.00

    db block gets from cache

    84,633,753

    2,935.36

    1. 285.98

    deferred (CURRENT) block cleanout applications

    460,792

    1. 15.98
    2. 1.56

    dirty buffers inspected

    39,990

    1. 1.39
    2. 0.14

    enqueue conversions

    36,114

    1. 1.25
    2. 0.12

    enqueue releases

    2,218,050

    1. 76.93
    2. 7.49

    enqueue requests

    2,218,224

    1. 76.93
    2. 7.50

    enqueue timeouts

    121

    1. 0.00
    2. 0.00

    enqueue waits

    4,741

    1. 0.16
    2. 0.02

    exchange deadlocks

    34

    1. 0.00
    2. 0.00

    execute count

    14,113,143

    1. 489.49
    2. 47.69

    failed probes on index block reclamation

    128

    1. 0.00
    2. 0.00

    frame signature mismatch

    0

    1. 0.00
    2. 0.00

    free buffer inspected

    542,240

    1. 18.81
    2. 1.83

    free buffer requested

    852,277

    1. 29.56
    2. 2.88

    heap block compress

    1,376,195

    1. 47.73
    2. 4.65

    hot buffers moved to head of LRU

    215,748

    1. 7.48
    2. 0.73

    immediate (CR) block cleanout applications

    119,006

    1. 4.13
    2. 0.40

    immediate (CURRENT) block cleanout applications

    177,079

    1. 6.14
    2. 0.60

    index crx upgrade (found)

    489

    1. 0.02
    2. 0.00

    index crx upgrade (positioned)

    1,064,464

    1. 36.92
    2. 3.60

    index fast full scans (full)

    89,485

    1. 3.10
    2. 0.30

    index fetch by key

    152,014,980

    5,272.35

    1. 513.66

    index scans kdiixs1

    32,590,424

    1,130.34

    1. 110.12

    leaf node 90-10 splits

    865

    1. 0.03
    2. 0.00

    leaf node splits

    21,720

    1. 0.75
    2. 0.07

    lob reads

    2,670

    1. 0.09
    2. 0.01

    lob writes

    1,545

    1. 0.05
    2. 0.01

    lob writes unaligned

    1,545

    1. 0.05
    2. 0.01

    logons cumulative

    14,185

    1. 0.49
    2. 0.05

    messages received

    434,282

    1. 15.06
    2. 1.47

    messages sent

    434,281

    1. 15.06
    2. 1.47

    no buffer to keep pinned count

    0

    1. 0.00
    2. 0.00

    no work - consistent read gets

    135,935,962

    4,714.68

    1. 459.33

    opened cursors cumulative

    785,833

    1. 27.26
    2. 2.66

    parse count (failures)

    172

    1. 0.01
    2. 0.00

    parse count (hard)

    34,133

    1. 1.18
    2. 0.12

    parse count (total)

    702,031

    1. 24.35
    2. 2.37

    parse time cpu

    28,630

    1. 0.99
    2. 0.10

    parse time elapsed

    31,890

    1. 1.11
    2. 0.11

    physical read IO requests

    111,139

    1. 3.85
    2. 0.38

    physical read bytes

    3,586,293,760

    124,383.81

    12,118.11

    physical read total IO requests

    225,033

    1. 7.80
    2. 0.76

    physical read total bytes

    27,324,811,776

    947,709.35

    92,330.71

    physical read total multi block requests

    39,363

    1. 1.37
    2. 0.13

    physical reads

    218,890

    1. 7.59
    2. 0.74

    physical reads cache

    157,874

    1. 5.48
    2. 0.53

    physical reads cache prefetch

    51,465

    1. 1.78
    2. 0.17

    physical reads direct

    61,016

    1. 2.12
    2. 0.21

    physical reads direct (lob)

    1

    1. 0.00
    2. 0.00

    physical reads direct temporary tablespace

    12,355

    1. 0.43
    2. 0.04

    physical reads prefetch warmup

    0

    1. 0.00
    2. 0.00

    physical write IO requests

    610,176

    1. 21.16
    2. 2.06

    physical write bytes

    13,330,317,312

    462,336.81

    45,043.23

    physical write total IO requests

    992,931

    1. 34.44
    2. 3.36

    physical write total bytes

    49,127,398,400

    1,703,890.78

    166,001.79

    physical write total multi block requests

    693,492

    1. 24.05
    2. 2.34

    physical writes

    813,618

    1. 28.22
    2. 2.75

    physical writes direct

    14,813

    1. 0.51
    2. 0.05

    physical writes direct (lob)

    129

    1. 0.00
    2. 0.00

    physical writes direct temporary tablespace

    12,763

    1. 0.44
    2. 0.04

    physical writes from cache

    798,805

    1. 27.71
    2. 2.70

    physical writes non checkpoint

    456,031

    1. 15.82
    2. 1.54

    pinned buffers inspected

    1,366

    1. 0.05
    2. 0.00

    prefetch warmup blocks aged out before use

    0

    1. 0.00
    2. 0.00

    prefetched blocks aged out before use

    3,862

    1. 0.13
    2. 0.01

    process last non-idle time

    301,439

    1. 10.45
    2. 1.02

    queries parallelized

    5

    1. 0.00
    2. 0.00

    recovery blocks read

    0

    1. 0.00
    2. 0.00

    recursive aborts on index block reclamation

    0

    1. 0.00
    2. 0.00

    recursive calls

    19,377,529

    1. 672.07
    2. 65.48

    recursive cpu usage

    1,663,208

    1. 57.69
    2. 5.62

    redo blocks read for recovery

    0

    1. 0.00
    2. 0.00

    redo blocks written

    23,025,985

    1. 798.61
    2. 77.80

    redo buffer allocation retries

    596

    1. 0.02
    2. 0.00

    redo entries

    34,287,910

    1,189.21

    1. 115.86

    redo log space requests

    845

    1. 0.03
    2. 0.00

    redo log space wait time

    3,126

    1. 0.11
    2. 0.01

    redo ordering marks

    305,710

    1. 10.60
    2. 1.03

    redo size

    11,289,499,268

    391,554.90

    38,147.29

    redo subscn max counts

    474,526

    1. 16.46
    2. 1.60

    redo synch time

    249,419

    1. 8.65
    2. 0.84

    redo synch writes

    238,655

    1. 8.28
    2. 0.81

    redo wastage

    114,057,616

    3,955.87

    1. 385.40

    redo write time

    150,451

    1. 5.22
    2. 0.51

    redo writer latching time

    66

    1. 0.00
    2. 0.00

    redo writes

    286,772

    1. 9.95
    2. 0.97

    rollback changes - undo records applied

    314,254

    1. 10.90
    2. 1.06

    rollbacks only - consistent read gets

    40,477

    1. 1.40
    2. 0.14

    rows fetched via callback

    89,236,526

    3,095.00

    1. 301.53

    session connect time

    0

    1. 0.00
    2. 0.00

    session cursor cache hits

    535,538

    1. 18.57
    2. 1.81

    session logical reads

    426,029,603

    14,776.03

    1,439.56

    session pga memory

    168,628,096

    5,848.55

    1. 569.80

    session pga memory max

    354,784,448

    12,305.03

    1,198.82

    session uga memory

    4,796,059,104

    166,342.23

    16,205.91

    session uga memory max

    5,166,642,880

    179,195.22

    17,458.12

    shared hash latch upgrades - no wait

    1,250,266

    1. 43.36
    2. 4.22

    shared hash latch upgrades - wait

    5,705

    1. 0.20
    2. 0.02

    sorts (disk)

    0

    1. 0.00
    2. 0.00

    sorts (memory)

    9,108,577

    1. 315.91
    2. 30.78

    sorts (rows)

    268,084,766

    9,298.01

    1. 905.86

    sql area evicted

    40,495

    1. 1.40
    2. 0.14

    sql area purged

    310

    1. 0.01
    2. 0.00

    summed dirty queue length

    505,688

    1. 17.54
    2. 1.71

    switch current to new buffer

    170,138

    1. 5.90
    2. 0.57

    table fetch by rowid

    475,491,531

    16,491.52

    1,606.69

    table fetch continued row

    44,009

    1. 1.53
    2. 0.15

    table scan blocks gotten

    31,974,488

    1,108.97

    1. 108.04

    table scan rows gotten

    8,938,439,956

    310,012.86

    30,203.04

    table scans (direct read)

    220

    1. 0.01
    2. 0.00

    table scans (long tables)

    411

    1. 0.01
    2. 0.00

    table scans (rowid ranges)

    404

    1. 0.01
    2. 0.00

    table scans (short tables)

    1,110,617

    1. 38.52
    2. 3.75

    total number of times SMON posted

    249

    1. 0.01
    2. 0.00

    transaction rollbacks

    18

    1. 0.00
    2. 0.00

    transaction tables consistent read rollbacks

    4

    1. 0.00
    2. 0.00

    transaction tables consistent reads - undo records applied

    2,722

    1. 0.09
    2. 0.01

    undo change vector size

    4,634,764,776

    160,748.04

    15,660.90

    user I/O wait time

    71,478

    1. 2.48
    2. 0.24

    user calls

    3,204,505

    1. 111.14
    2. 10.83

    user commits

    295,936

    1. 10.26
    2. 1.00

    user rollbacks

    9

    1. 0.00
    2. 0.00

    workarea executions - onepass

    8

    1. 0.00
    2. 0.00

    workarea executions - optimal

    531,085

    1. 18.42
    2. 1.79

    write clones created in background

    2,557

    1. 0.09
    2. 0.01

    write clones created in foreground

    15,696

    1. 0.54
    2. 0.05

    Back to Instance Activity Statistics
    Back to Top

    Instance Activity Stats - Absolute Values

    • Statistics with absolute values (should not be diffed)
      Statistic

    Begin Value

    End Value

    session cursor cache count

    280,121

    334,711

    opened cursors current

    86

    2,619

    workarea memory allocated

    0

    4,712

    logons current

    26

    145

    Back to Instance Activity Statistics
    Back to Top

    Instance Activity Stats - Thread Activity

    • Statistics identified by '(derived)' come from sources other than SYSSTAT

    Statistic

    Total

    per Hour

    log switches (derived)

    81

    1. 10.11

    Back to Instance Activity Statistics
    Back to Top

    IO Stats

      Back to Top  

    Tablespace IO Stats

    • ordered by IOs (Reads + Writes) desc

    Tablespace

    Reads

    Av Reads/s

    Av Rd(ms)

    Av Blks/Rd

    Writes

    Av Writes/s

    Buffer Waits

    Av Buf Wt(ms)

    52,477

    2

    1. 6.44
    2. 2.60

    199,529

    7

    26,815

    1. 0.18

    106

    0

    1. 3.30
    2. 1.00

    239,291

    8

    16,765

    1. 0.64

    46,715

    2

    1. 6.47
    2. 1.14

    151,241

    5

    1,198

    1. 0.11

    3,119

    0

    1. 8.31
    2. 1.25

    11,297

    0

    0

    1. 0.00

    5,349

    0

    1. 5.31
    2. 1.92

    2,279

    0

    176

    1. 1.65

    1,784

    0

    1. 5.52
    2. 7.15

    1,805

    0

    0

    1. 0.00

    604

    0

    1. 6.97
    2. 1.85

    1,766

    0

    57,559

    1. 4.34

    499

    0

    1. 6.75
    2. 1.18

    1,807

    0

    0

    1. 0.00

    211

    0

    1. 4.93
    2. 1.01

    844

    0

    0

    1. 0.00

    82

    0

    1. 2.80
    2. 1.00

    80

    0

    0

    1. 0.00

    81

    0

    1. 2.47
    2. 1.00

    80

    0

    0

    1. 0.00

    81

    0

    1. 5.80
    2. 1.00

    80

    0

    0

    1. 0.00

    81

    0

    1. 5.31
    2. 1.00

    80

    0

    0

    1. 0.00

    Back to IO Stats
    Back to Top

    File IO Stats

    • ordered by Tablespace, File
      Tablespace

    Filename

    Reads

    Av Reads/s

    Av Rd(ms)

    Av Blks/Rd

    Writes

    Av Writes/s

    Buffer Waits

    Av Buf Wt(ms)

    225

    0

    1. 5.60
    2. 2.05

    254

    0

    24

    1. 6.25

    379

    0

    1. 7.78
    2. 1.73

    1,512

    0

    57,535

    1. 4.34

    499

    0

    1. 6.75
    2. 1.18

    1,807

    0

    0

    1. 0.00

    211

    0

    1. 4.93
    2. 1.01

    844

    0

    0

    1. 0.00

    81

    0

    1. 2.47
    2. 1.00

    80

    0

    0

    1. 0.00

    81

    0

    1. 5.80
    2. 1.00

    80

    0

    0

    1. 0.00

    81

    0

    1. 5.31
    2. 1.00

    80

    0

    0

    1. 0.00

    9,122

    0

    1. 6.32
    2. 2.60

    33,904

    1

    5,142

    1. 0.20

    8,168

    0

    1. 5.69
    2. 2.55

    35,520

    1

    3,466

    1. 0.21

    7,866

    0

    1. 6.39
    2. 2.69

    33,098

    1

    3,879

    1. 0.18

    8,856

    0

    1. 6.34
    2. 2.50

    32,900

    1

    3,664

    1. 0.15

    8,566

    0

    1. 6.38
    2. 2.70

    29,783

    1

    9,967

    1. 0.16

    9,899

    0

    1. 7.34
    2. 2.58

    34,324

    1

    697

    1. 0.17

    8,779

    0

    1. 6.17
    2. 1.15

    25,629

    1

    51

    1. 0.20

    10,137

    0

    1. 6.41
    2. 1.13

    33,474

    1

    162

    1. 0.19

    9,206

    0

    1. 6.11
    2. 1.15

    29,441

    1

    235

    1. 0.04

    9,168

    0

    1. 7.14
    2. 1.14

    29,063

    1

    577

    1. 0.10

    9,425

    0

    1. 6.53
    2. 1.12

    33,634

    1

    173

    1. 0.12

    2,505

    0

    1. 8.40
    2. 1.14

    8,579

    0

    0

    1. 0.00

    614

    0

    1. 7.96
    2. 1.71

    2,718

    0

    0

    1. 0.00

    5,349

    0

    1. 5.31
    2. 1.92

    2,279

    0

    176

    1. 1.65

    1,784

    0

    1. 5.52
    2. 7.15

    1,805

    0

    0

     

    106

    0

    1. 3.30
    2. 1.00

    239,291

    8

    16,765

    1. 0.64

    82

    0

    1. 2.80
    2. 1.00

    80

    0

    0

    1. 0.00

    Back to IO Stats
    Back to Top

    Buffer Pool Statistics

    • Standard block size Pools D: default, K: keep, R: recycle
    • Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k
      P

    Number of Buffers

    Pool Hit%

    Buffer Gets

    Physical Reads

    Physical Writes

    Free Buff Wait

    Writ Comp Wait

    Buffer Busy Waits

    D

    201,928

    100

    425,990,578

    157,874

    798,805

    0

    0

    102,518

    K

    64,320

     

    0

    0

    0

    0

    0

    0


    Back to Top

    Advisory Statistics

      Back to Top  

    Instance Recovery Stats

    • B: Begin snapshot, E: End snapshot

    Targt MTTR (s)

    Estd MTTR (s)

    Recovery Estd IOs

    Actual Redo Blks

    Target Redo Blks

    Log File Size Redo Blks

    Log Ckpt Timeout Redo Blks

    Log Ckpt Interval Redo Blks

    B

    30

    11

    688

    16406

    829440

    829440

     

     

    E

    24

    12

    767

    20379

    829440

    829440

     

     

    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
      P

    Size for Est (M)

    Size Factor

    Buffers for Estimate

    Est Phys Read Factor

    Estimated Physical Reads

    D

    320

    1. 0.10

    20,100

    1. 1.74

    19,169,521

    D

    640

    1. 0.20

    40,200

    1. 1.47

    16,156,392

    D

    960

    1. 0.30

    60,300

    1. 1.30

    14,348,752

    D

    1,280

    1. 0.40

    80,400

    1. 1.20

    13,231,127

    D

    1,600

    1. 0.50

    100,500

    1. 1.13

    12,505,024

    D

    1,920

    1. 0.59

    120,600

    1. 1.09

    12,034,385

    D

    2,240

    1. 0.69

    140,700

    1. 1.06

    11,698,253

    D

    2,560

    1. 0.79

    160,800

    1. 1.04

    11,431,046

    D

    2,880

    1. 0.89

    180,900

    1. 1.02

    11,220,245

    D

    3,200

    1. 0.99

    201,000

    1. 1.00

    11,043,639

    D

    3,232

    1. 1.00

    203,010

    1. 1.00

    11,027,110

    D

    3,520

    1. 1.09

    221,100

    1. 0.99

    10,889,004

    D

    3,840

    1. 1.19

    241,200

    1. 0.97

    10,750,794

    D

    4,160

    1. 1.29

    261,300

    1. 0.96

    10,624,396

    D

    4,480

    1. 1.39

    281,400

    1. 0.95

    10,509,414

    D

    4,800

    1. 1.49

    301,500

    1. 0.94

    10,403,299

    D

    5,120

    1. 1.58

    321,600

    1. 0.93

    10,302,057

    D

    5,440

    1. 1.68

    341,700

    1. 0.93

    10,204,207

    D

    5,760

    1. 1.78

    361,800

    1. 0.92

    10,105,185

    D

    6,080

    1. 1.88

    381,900

    1. 0.91

    10,000,086

    D

    6,400

    1. 1.98

    402,000

    1. 0.90

    9,894,798

    Back to Advisory Statistics
    Back to Top

    PGA Aggr Summary

    • PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory

    PGA Cache Hit %

    W/A MB Processed

    Extra W/A MB Read/Written

    1. 99.03

    46,539

    454

    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 Mem

    Global Mem Bound(K)

    B

    1,536

    1,329

    1. 309.81
    2. 0.00
    3. 0.00
    4. 0.00
    5. 0.00

    157,280

    E

    1,536

    1,223

    1. 490.22
    2. 0.00
    3. 0.00
    4. 0.00
    5. 0.00

    157,280

    Back to Advisory Statistics
    Back to Top

    PGA Aggr Target Histogram

    • Optimal Executions are purely in-memory operations

    Low Optimal

    High Optimal

    Total Execs

    Optimal Execs

    1-Pass Execs

    M-Pass Execs

    2K

    4K

    478,512

    478,512

    0

    0

    64K

    128K

    1,146

    1,146

    0

    0

    128K

    256K

    457

    457

    0

    0

    256K

    512K

    325

    325

    0

    0

    512K

    1024K

    49,498

    49,498

    0

    0

    1M

    2M

    641

    641

    0

    0

    2M

    4M

    209

    209

    0

    0

    4M

    8M

    198

    198

    0

    0

    8M

    16M

    88

    88

    0

    0

    16M

    32M

    8

    6

    2

    0

    32M

    64M

    14

    12

    2

    0

    64M

    128M

    4

    0

    4

    0

    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 Factr

    W/A MB Processed

    Estd Extra W/A MB Read/ Written to Disk

    Estd PGA Cache Hit %

    Estd PGA Overalloc Count

    192

    1. 0.13

    168,223.98

    42,557.55

    1. 80.00

    1,978

    384

    1. 0.25

    168,223.98

    6,602.13

    1. 96.00

    0

    768

    1. 0.50

    168,223.98

    5,941.94

    1. 97.00

    0

    1,152

    1. 0.75

    168,223.98

    5,941.94

    1. 97.00

    0

    1,536

    1. 1.00

    168,223.98

    5,779.09

    1. 97.00

    0

    1,843

    1. 1.20

    168,223.98

    4,226.66

    1. 98.00

    0

    2,150

    1. 1.40

    168,223.98

    4,226.66

    1. 98.00

    0

    2,458

    1. 1.60

    168,223.98

    4,226.66

    1. 98.00

    0

    2,765

    1. 1.80

    168,223.98

    4,226.66

    1. 98.00

    0

    3,072

    1. 2.00

    168,223.98

    4,226.66

    1. 98.00

    0

    4,608

    1. 3.00

    168,223.98

    4,226.66

    1. 98.00

    0

    6,144

    1. 4.00

    168,223.98

    4,226.66

    1. 98.00

    0

    9,216

    1. 6.00

    168,223.98

    4,226.66

    1. 98.00

    0

    12,288

    1. 8.00

    168,223.98

    4,226.66

    1. 98.00

    0

    Back to Advisory Statistics
    Back to Top

    Shared Pool Advisory

    • SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor
    • Note there is often a 1:Many correlation between a single logical object in the Library Cache, and the physical number of memory objects associated with it. Therefore comparing the number of Lib Cache objects (e.g. in v$librarycache), with the number of Lib Cache Memory Objects is invalid.

    Shared Pool Size(M)

    SP Size Factr

    Est LC Size (M)

    Est LC Mem Obj

    Est LC Time Saved (s)

    Est LC Time Saved Factr

    Est LC Load Time (s)

    Est LC Load Time Factr

    Est LC Mem Obj Hits

    384

    1. 0.26

    156

    8,145

    12,514,733

    1. 0.83

    2,502,999

    1. 1147.11

    86,783,048

    544

    1. 0.36

    261

    9,126

    13,096,293

    1. 0.87

    1,921,439

    1. 880.59

    86,805,626

    704

    1. 0.47

    281

    9,337

    13,584,231

    1. 0.90

    1,433,501

    1. 656.97

    86,825,133

    864

    1. 0.57

    281

    9,337

    13,960,839

    1. 0.93

    1,056,893

    1. 484.37

    86,840,627

    1,024

    1. 0.68

    281

    9,339

    14,380,368

    1. 0.96

    637,364

    1. 292.10

    86,852,966

    1,184

    1. 0.79

    300

    9,516

    14,746,963

    1. 0.98

    270,769

    1. 124.09

    86,861,875

    1,344

    1. 0.89

    372

    10,543

    14,925,759

    1. 0.99

    91,973

    1. 42.15

    86,867,839

    1,504

    1. 1.00

    452

    11,346

    15,015,550

    1. 1.00

    2,182

    1. 1.00

    86,871,760

    1,664

    1. 1.11

    611

    13,050

    15,094,669

    1. 1.01

    1

    1. 0.00

    86,874,357

    1,824

    1. 1.21

    771

    21,869

    15,168,501

    1. 1.01

    1

    1. 0.00

    86,876,290

    1,984

    1. 1.32

    931

    29,232

    15,240,179

    1. 1.02

    1

    1. 0.00

    86,877,789

    2,144

    1. 1.43

    1,090

    34,921

    15,307,545

    1. 1.02

    1

    1. 0.00

    86,878,959

    2,304

    1. 1.53

    1,248

    43,123

    15,370,382

    1. 1.02

    1

    1. 0.00

    86,879,965

    2,464

    1. 1.64

    1,407

    57,010

    15,430,835

    1. 1.03

    1

    1. 0.00

    86,880,933

    2,624

    1. 1.74

    1,566

    65,420

    15,490,066

    1. 1.03

    1

    1. 0.00

    86,881,837

    2,784

    1. 1.85

    1,726

    71,197

    15,548,877

    1. 1.04

    1

    1. 0.00

    86,882,644

    2,944

    1. 1.96

    1,901

    83,175

    15,607,602

    1. 1.04

    1

    1. 0.00

    86,883,581

    3,104

    1. 2.06

    2,060

    95,491

    15,666,319

    1. 1.04

    1

    1. 0.00

    86,884,592

    Back to Advisory Statistics
    Back to Top

    SGA Target Advisory

    SGA Target Size (M)

    SGA Size Factor

    Est DB Time (s)

    Est Physical Reads

    3,072

    1. 0.50

    214,938

    19,169,509

    4,608

    1. 0.75

    188,137

    12,504,730

    6,144

    1. 1.00

    182,693

    11,027,099

    7,680

    1. 1.25

    180,062

    10,301,516

    9,216

    1. 1.50

    178,509

    9,894,616

    10,752

    1. 1.75

    178,509

    9,894,616

    12,288

    1. 2.00

    178,509

    9,894,616

    Back to Advisory Statistics
    Back to Top

    Streams Pool Advisory

    Size for Est (MB)

    Size Factor

    Est Spill Count

    Est Spill Time (s)

    Est Unspill Count

    Est Unspill Time (s)

    32

    1. 0.13

    4,139

    41

    366

    3

    64

    1. 0.25

    0

    0

    0

    0

    96

    1. 0.38

    0

    0

    0

    0

    128

    1. 0.50

    0

    0

    0

    0

    160

    1. 0.63

    0

    0

    0

    0

    192

    1. 0.75

    0

    0

    0

    0

    224

    1. 0.88

    0

    0

    0

    0

    256

    1. 1.00

    0

    0

    0

    0

    288

    1. 1.13

    0

    0

    0

    0

    320

    1. 1.25

    0

    0

    0

    0

    352

    1. 1.38

    0

    0

    0

    0

    384

    1. 1.50

    0

    0

    0

    0

    416

    1. 1.63

    0

    0

    0

    0

    448

    1. 1.75

    0

    0

    0

    0

    480

    1. 1.88

    0

    0

    0

    0

    512

    1. 2.00

    0

    0

    0

    0

    544

    1. 2.13

    0

    0

    0

    0

    576

    1. 2.25

    0

    0

    0

    0

    608

    1. 2.38

    0

    0

    0

    0

    640

    1. 2.50

    0

    0

    0

    0

    Back to Advisory Statistics
    Back to Top

    Java Pool Advisory

    Java Pool Size(M)

    JP Size Factr

    Est LC Size (M)

    Est LC Mem Obj

    Est LC Time Saved (s)

    Est LC Time Saved Factr

    Est LC Load Time (s)

    Est LC Load Time Factr

    Est LC Mem Obj Hits

    16

    1. 0.25

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    32

    1. 0.50

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    48

    1. 0.75

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    64

    1. 1.00

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    80

    1. 1.25

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    96

    1. 1.50

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    112

    1. 1.75

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    128

    1. 2.00

    0

    124

    5

    1. 1.00

    2,182

    1. 1.00

    124

    Back to Advisory Statistics
    Back to Top

    Wait Statistics

      Back to Top  

    Buffer Wait Statistics

    • ordered by wait time desc, waits desc

    Class

    Waits

    Total Wait Time (s)

    Avg Time (ms)

    data block

    85,502

    255

    3

    undo header

    16,676

    11

    1

    undo block

    89

    0

    1

    1st level bmb

    234

    0

    0

    2nd level bmb

    9

    0

    1

    segment header

    3

    0

    0

    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)

    Requests

    Succ Gets

    Failed Gets

    Waits

    Wt Time (s)

    Av Wt Time(ms)

    TX-Transaction (row lock contention)

    4,707

    4,707

    0

    4,519

    19

    1. 4.17

    RO-Multiple Object Reuse (fast object reuse)

    1,197

    1,197

    0

    133

    2

    1. 14.29

    KO-Multiple Object Checkpoint (fast object checkpoint)

    36

    36

    0

    4

    1

    1. 165.00

    CF-Controlfile Transaction

    15,670

    15,667

    3

    9

    0

    1. 10.00

    TX-Transaction (index contention)

    89

    89

    0

    50

    0

    1. 0.80

    HW-Segment High Water Mark

    5,907

    5,907

    0

    8

    0

    1. 5.00

    TX-Transaction

    338,987

    338,987

    0

    3

    0

    1. 6.67

    FB-Format Block

    2,893

    2,893

    0

    7

    0

    1. 0.00

    PS-PX Process Reservation

    112

    112

    0

    7

    0

    1. 0.00

    TX-Transaction (allocate ITL entry)

    1

    1

    0

    1

    0

    1. 0.00

    Back to Wait Statistics
    Back to Top

    Undo Statistics

      Back to Top  

    Undo Segment Summary

    • Min/Max TR (mins) - Min and Max Tuned Retention (minutes)
    • STO - Snapshot Too Old count, OOS - Out of Space count