0 Replies Latest reply: Jan 22, 2014 4:59 AM by DBA2013 RSS

db file sequential read event appearing frequently...Need help

DBA2013 Newbie
Currently Being Moderated

Hi,

 

We have been experiencing from last one week, huge change in performance almost every batch job taking double the time.

 

From top events, I could see huge IO issue frequently appearing...(db file sequential read)...Agreed the load has increased a lot, yet getting jobs executed double/thrice the time making us really worried.


Instance Efficiency Indicators

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

            Buffer Nowait %:   99.96       Redo NoWait %:  100.00

            Buffer  Hit   %:   99.54  Optimal W/A Exec %:  100.00

            Library Hit   %:   99.83        Soft Parse %:   93.69

         Execute to Parse %:   99.50         Latch Hit %:   99.71

Parse CPU to Parse Elapsd %:   41.26     % Non-Parse CPU:   98.24

 

Shared Pool Statistics        Begin   End

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

             Memory Usage %:   76.46   67.92

    % SQL with executions>1:   86.95   85.44

 

 

Top 5 Timed Events                                                    Avg %Total

~~~~~~~~~~~~~~~~~~                                                   wait   Call

Event                                            Waits    Time (s)   (ms)   Time

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

db file sequential read                        820,736       8,259     10   68.4

CPU time                                                     1,786          14.8

read by other session                           67,085         707     11    5.9

Backup: MML write backup piece                  16,608         448     27    3.7

control file sequential read                    69,362         242      3    2.0

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

Host CPU  (CPUs: 8  Cores: 4  Sockets: 0)

~~~~~~~~              Load Average

                      Begin     End      User  System    Idle     WIO     WCPU

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

                      12.40   23.08     59.03   39.24    1.73    0.87  194.82


=================


Can some one help please...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points