performance issue

[maswien]

If the response of a dataabse getting slow, what is the steps we need to check it?

I looked the OS using vmstat, I can see that the CPU idle is 0 when the database is being used.

kthr     memory             page              faults        cpu     
----- ----------- ------------------------ ------------ -----------
 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa 
 3  3 1687294  4128   0   0   0 401  106   0 1092 19587 2889 13  5 74  8
 1  6 1684831  1759   0   0   0 183 2728   0 1337 34812 2553 18  4  2 75
 1  5 1684900   120   0   0   0 801 6352   0 1314 22256 2732  8  7  5 80
 7  9 1685702   121   0   0   0 6411 51140   0 3784 124763 12836 46 26  0 28
 7 11 1689982   128   0   1   0 16451 154285   0 4765 107906 12032 43 42  0 15
 9  8 1691846   125   0   3   0 20399 156782   0 5669 80596 12552 40 47  0 13
 5 13 1692512   127   0   2   0 25084 225824   0 6735 30551 14432 25 54  0 22
 6 11 1692907   124   0   1   0 15059 149699   0 7127 106382 14174 39 47  0 14
 8 11 1694424   127   0   7   0 11394 102636   0 8554 138788 17269 41 44  0 15
 7 17 1693803   123   0   0   0 11234 106712   0 9006 55413 18844 25 42  0 33
 9 17 1695205   122   0   2   0 14157 133724   0 8644 63346 18025 25 46  0 29
 8 17 1697434   126   0   2   0 16363 125590   0 7954 89473 18248 43 52  0  6
 5 19 1697550   118   0   2   0 14362 146180   0 9442 59242 19861 24 52  0 24
 7 12 1694345  3788   0   2   0 9879 111763   0 7462 84454 17384 43 36  0 21
 2 12 1691774   123   0   0   0 4492 16316   0 5185 20175 13783 21 20  0 58
 5 19 1691868   126   0   2   0 15452 142113   0 7742 40740 17429 25 48  0 27
 5 15 1691218   128   0   1   0 13651 111620   0 9200 44856 27855 31 51  0 18
 1  8 1689647  1935   0   0   0 3011 47945   0 2485 73016 27840 27 29  0 45
 3  9 1690159   127   0   0   0 4468 40180   0 2941 103943 43393 43 46  0 11
 4  5 1692537   122   0   0   0 1798 10227   0 1353 63305 44024 37 47  0 16

In this case, what we need look at first?

Thanks,

 

[LKBrwnDBA]

Install statspack and run the reports.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[maswien]

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              4,013.86             40,584.61
              Logical reads:                341.51              3,453.06
              Block changes:                 17.72                179.17
             Physical reads:                121.53              1,228.78
            Physical writes:                  1.87                 18.88
                 User calls:                 46.49                470.10
                     Parses:                 29.29                296.19
                Hard parses:                  1.86                 18.82
                      Sorts:                  7.90                 79.92
                     Logons:                  6.98                 70.60
                   Executes:                 49.32                498.65
               Transactions:                  0.10

  % Blocks changed per Read:    5.19    Recursive Call %:    84.13
 Rollback per transaction %:    0.00       Rows per Sort:    12.42

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   92.95    In-memory Sort %:  100.00
            Library Hit   %:   94.43        Soft Parse %:   93.65
         Execute to Parse %:   40.60         Latch Hit %:   98.95
Parse CPU to Parse Elapsd %:    5.03     % Non-Parse CPU:   92.89

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   94.38   91.89
    % SQL with executions>1:   66.07   68.39
  % Memory for SQL w/exec>1:   70.99   78.58

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
latch: library cache                               643         172    268   38.7
CPU time                                                       108          24.3
direct path read                                12,522          38      3    8.6
db file sequential read                         11,314          26      2    5.8
PX Deq: Parse Reply                              2,953          26      9    5.8
          -------------------------------------------------------------
^LHost CPU  (CPUs: 2)

Here is the first page frm the report, any idea?

Thanks

 

[LKBrwnDBA]

You could start by checking out these:

- Top 5 Wait Events

Remember: All computers wait at the same time, check what is Oracle waiting for the most.

- SQL ordered by Gets for DB
- SQL ordered by Executions for DB

Run explain plan for the above "highest" sql, find out if "tunning" is needed.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

-- OR --

Check out the oraperf.com web site.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

Another:

http://www.statspackanalyzer.com/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb