1 2 Previous Next 24 Replies Latest reply on Nov 2, 2013 2:27 PM by Nikolay Savvinov

    Oracle Performance

    KSG

      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

          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

            do the SQL involved use bind variables?

            • 3. Re: Oracle Performance
              KSG

              Hi sb,

               

              The query wasn't using bind variable.

               

              I will post the AWR repot.

              • 4. Re: Oracle Performance
                sb92075

                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

                  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

                    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

                      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

                        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

                          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

                            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
                            • 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 Transactions

                            Max Qry Len (s)

                            Max Tx Concurcy

                            Min/Max TR (mins)

                            STO/ OOS

                            uS/uR/uU/ eS/eR/eUError encountered in Undo Segment Summary While executing SQL statement: Len = 834 select undotsn , sum(undoblks)/1000 undob , sum(txncount) txcnt , max(maxquerylen) maxq , max(maxconcurrency) maxc , min(tuned_undoretention)/60 ||'/'|| max(tuned_undor etention)/60 mintun , sum(ssolderrcnt) ||'/'|| sum(nospaceerrcnt) snolno , sum (unxpstealcnt) ||'/'|| sum(unxpblkrelcnt) ||'/'| | sum(unxpblkreucnt) ||'/'|| sum(expstealcnt) || '/'|| sum(expblkrelcnt) ||'/'|| sum(expblkreucnt) blkst from dba_hist_undostat where dbid = :dbid a nd instance_number = :inst_num and end_time > :b_eitvtim e and begin_time < :e_eitvtime group by undotsn Encountered error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small Continuing to Next Section...

                             


                             

                             

                            Back to Undo Statistics Back to Top

                            • Most recent 35 Undostat rows, ordered by Time desc

                             

                            End Time

                            Num Undo Blocks

                            Number of Transactions

                            Max Qry Len (s)

                            Max Tx Concy

                            Tun Ret (mins)

                            STO/ OOS

                            uS/uR/uU/ eS/eR/eU

                            45

                            4,471

                            0

                            2

                            3,800

                            0/0

                            0/0/0/0/0/0

                            17,789

                            26,470

                            39

                            8

                            3,791

                            0/0

                            0/0/0/0/0/0

                            66

                            6,573

                            598

                            3

                            3,896

                            0/0

                            0/0/0/0/0/0

                            61

                            6,496

                            929

                            3

                            3,886

                            0/0

                            0/0/0/0/0/0

                            15,457

                            17,047

                            328

                            7

                            3,877

                            0/0

                            0/0/0/0/0/0

                            3,335

                            11,465

                            118

                            8

                            3,900

                            0/0

                            0/0/0/0/0/0

                            8,442

                            7,092

                            157

                            4

                            3,889

                            0/0

                            0/0/0/0/0/0

                            5,268

                            6,099

                            322

                            4

                            3,733

                            0/0

                            0/0/0/0/0/0

                            4,432

                            10,496

                            541

                            3

                            3,756

                            0/0

                            0/0/0/0/0/0

                            18,654

                            14,172

                            805

                            4

                            3,774

                            0/0

                            0/0/0/0/0/0

                            4,659

                            6,103

                            114

                            4

                            3,850

                            0/0

                            0/0/0/0/0/0

                            4,032

                            6,614

                            98

                            7

                            3,871

                            0/0

                            0/0/0/0/0/0

                            11,146

                            10,007

                            1,451

                            5

                            3,731

                            0/0

                            0/0/0/0/0/0

                            13,476

                            13,297

                            851

                            6

                            3,792

                            0/0

                            0/0/0/0/0/0

                            3,371

                            11,634

                            250

                            5

                            3,687

                            0/0

                            0/0/0/0/0/0

                            710

                            5,242

                            20

                            5

                            3,697

                            0/0

                            0/0/0/0/0/0

                            10,258

                            9,675

                            463

                            6

                            3,692

                            0/0

                            0/0/0/0/0/0

                            14,741

                            7,805

                            251

                            6

                            3,746

                            0/0

                            0/0/0/0/0/0

                            12,278

                            10,327

                            23

                            6

                            3,850

                            0/0

                            0/0/0/0/0/0

                            5,126

                            8,572

                            578

                            6

                            3,848

                            0/0

                            0/0/0/0/0/0

                            359

                            6,163

                            0

                            4

                            3,872

                            0/0

                            0/0/0/0/0/0

                            1,511

                            6,398

                            0

                            6

                            3,864

                            0/0

                            0/0/0/0/0/0

                            207

                            6,855

                            205

                            5

                            3,864

                            0/0

                            0/0/0/0/0/0

                            1,894

                            7,246

                            244

                            5

                            3,856

                            0/0

                            0/0/0/0/0/0

                            36,383

                            10,168

                            309

                            6

                            3,858

                            0/0

                            0/0/0/0/0/0

                            7,766

                            6,390

                            5,694

                            5

                            3,701

                            0/0

                            0/0/0/0/0/0

                            123

                            5,102

                            5,094

                            4

                            3,739

                            0/0

                            0/0/0/0/0/0

                            91

                            4,866

                            4,492

                            5

                            3,730

                            0/0

                            0/0/0/0/0/0

                            87

                            4,699

                            3,888

                            4

                            3,721

                            0/0

                            0/0/0/0/0/0

                            419

                            5,229

                            3,284

                            5

                            3,712

                            0/0

                            0/0/0/0/0/0

                            123

                            4,925

                            2,683

                            5

                            3,704

                            0/0

                            0/0/0/0/0/0

                            114

                            4,945

                            2,080

                            2

                            3,696

                            0/0

                            0/0/0/0/0/0

                            136

                            5,065

                            1,478

                            3

                            3,687

                            0/0

                            0/0/0/0/0/0

                            132

                            5,044

                            877

                            3

                            3,678

                            0/0

                            0/0/0/0/0/0

                            Back to Undo Statistics
                            Back to Top

                            Latch Statistics

                              Back to Top  

                            Latch Activity

                            • "Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are statistics for willing-to-wait latch get requests
                            • "NoWait Requests", "Pct NoWait Miss" are for no-wait latch get requests
                            • "Pct Misses" for both should be very close to 0.0

                            Latch Name

                            Get Requests

                            Pct Get Miss

                            Avg Slps /Miss

                            Wait Time (s)

                            NoWait Requests

                            Pct NoWait Miss

                            ASM db client latch

                            19,530

                            1. 0.00

                             

                            0

                            0

                             

                            AWR Alerted Metric Element list

                            219,988

                            1. 0.00

                             

                            0

                            0

                             

                            Consistent RBA

                            286,984

                            1. 0.01
                            2. 0.00

                            0

                            0

                             

                            FAL request queue

                            739

                            1. 0.00

                             

                            0

                            0

                             

                            FAL subheap alocation

                            739

                            1. 0.00

                             

                            0

                            0

                             

                            FIB s.o chain latch

                            1,514

                            1. 0.00

                             

                            0

                            0

                             

                            FOB s.o list latch

                            5,789

                            1. 0.02
                            2. 0.00

                            0

                            0

                             

                            In memory undo latch

                            1,650,414

                            1. 2.38
                            2. 0.90

                            42

                            356,394

                            1. 0.01

                            JS mem alloc latch

                            2

                            1. 0.00

                             

                            0

                            0

                             

                            JS queue access latch

                            2

                            1. 0.00

                             

                            0

                            0

                             

                            JS queue state obj latch

                            207,556

                            1. 0.00

                             

                            0

                            0

                             

                            JS slv state obj latch

                            5

                            1. 0.00

                             

                            0

                            0

                             

                            KMG MMAN ready and startup request latch

                            9,631

                            1. 0.00

                             

                            0

                            0

                             

                            KMG resize request state object freelist

                            22

                            1. 0.00

                             

                            0

                            0

                             

                            KSFQ

                            6

                            1. 0.00

                             

                            0

                            0