Performance analysis
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Performance analysis

Hybrid View

  1. #1
    Join Date
    Nov 2000
    Location
    London
    Posts
    94

    Performance analysis

    I've run the sp_systime_9i.sql script that picks up information on statistics collected by statspack. I would like to know where to look to drill down to find the causes of the latch free waits on the server.
    They are consuming 1/3 of the resources.

    Code:
                 Service                                                  Seconds    % of    % of
    Day    Hour  or Wait  Name                                              Spent   Total   Total
    ------ ----- -------- ----------------------------------- ------------------- ------- -------
    17-MAR 07:00 Wait     library cache pin                                948.10   13.11   89.83
                 Service  Parsing SQL                                      272.19    3.76   93.60
                 Wait     log file sync                                    205.43    2.84   96.44
           08:00 Wait     latch free                                     4,611.64   33.95   33.95
                 Service  SQL execution                                  4,564.23   33.60   67.54
                 Wait     db file sequential read                        1,494.64   11.00   78.55
                 Wait     library cache pin                              1,285.44    9.46   88.01
                 Wait     log file sync                                    666.68    4.91   92.92
                 Service  Parsing SQL                                      581.89    4.28   97.20
           09:00 Service  SQL execution                                  5,905.88   36.13   36.13
                 Wait     latch free                                     5,288.69   32.36   68.49
                 Wait     db file sequential read                        1,812.97   11.09   79.58
                 Wait     library cache pin                              1,131.06    6.92   86.50
                 Wait     log file sync                                    858.40    5.25   91.75
                 Service  Parsing SQL                                      699.98    4.28   96.04
           10:00 Service  SQL execution                                  6,790.22   37.44   37.44
                 Wait     latch free                                     6,213.64   34.26   71.70
                 Wait     db file sequential read                        1,499.27    8.27   79.97
                 Wait     library cache pin                              1,270.15    7.00   86.97
                 Wait     log file sync                                  1,012.01    5.58   92.55
                 Service  Parsing SQL                                      897.07    4.95   97.50
           11:00 Service  SQL execution                                  7,305.83   36.42   36.42
                 Wait     latch free                                     6,662.61   33.21   69.63
                 Wait     db file sequential read                        1,902.22    9.48   79.12
                 Wait     library cache pin                              1,499.84    7.48   86.59
                 Service  Parsing SQL                                      989.27    4.93   91.52
                 Wait     log file sync                                    974.23    4.86   96.38

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    are you using bind variables

  3. #3
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    The application doesnt use bind variables and the DB is flooded with different sql statements. Thinking about changing the cursor_sharing parameter from EXACT to SIMILAR/FORCE and also setting session_cached_cursors. Just wondering whether there is anything else I can look at before I go down that route.

  4. #4
    Join Date
    Mar 2006
    Posts
    40
    you cannot do nothing except change the DB is used of consider using cursor_sharing to force or similar - but be carefull as this might make some problems with the Oracle Cost based optimizer - sibce if you use cursor_sharing = force a query like:

    select * from table where id = 123

    it will make it:

    select * from table where id = :b1

    and the optimizer will not know wheter to use index or not.
    go to the oracle clinic
    Quick and easy Oracle database support and consulting

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    well your latch free and library cache hits will be becuase of that then plus if you are waiting on log file sync then you are probably committing too often as well

  6. #6
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    It is a very read intensive system with lots of parses.....Would setting session_cached_cursors > Curs/Sess alongside changing cursor_sharing improve performance?


    Code:
              Snap Id     Snap Time      Sessions Curs/Sess Comment
                ------- ------------------ -------- --------- -------------------
    Begin Snap:   18358 14-Mar-06 00:00:06       78     122.4
      End Snap:   18588 14-Mar-06 23:00:06       89     148.9
       Elapsed:            1,380.00 (mins)
    
    Cache Sizes (end)
    ~~~~~~~~~~~~~~~~~
                   Buffer Cache:     1,024M      Std Block Size:         8K
               Shared Pool Size:       304M          Log Buffer:     3,072K
    
    Load Profile ~~~~~~~~~~~~                            Per Second       Per Transaction
                                       ---------------       ---------------
                      Redo size:            115,890.83              3,893.61
                  Logical reads:            150,697.82              5,063.03
                  Block changes:                705.33                 23.70
                 Physical reads:                928.52                 31.20
                Physical writes:                 26.02                  0.87
                     User calls:              1,340.17                 45.03
                         Parses:                460.56                 15.47
                    Hard parses:                106.40                  3.57
                          Sorts:                 20.65                  0.69
                         Logons:                  0.03                  0.00
                       Executes:                471.45                 15.84
                   Transactions:                 29.76
    
      % Blocks changed per Read:    0.47    Recursive Call %:    30.59
     Rollback per transaction %:    4.54       Rows per Sort:    57.87
    
    Instance Efficiency Percentages (Target 100%)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
                Buffer Nowait %:   99.99       Redo NoWait %:  100.00
                Buffer  Hit   %:   99.39    In-memory Sort %:  100.00
                Library Hit   %:   88.96        Soft Parse %:   76.90
             Execute to Parse %:    2.31         Latch Hit %:   99.58
    Parse CPU to Parse Elapsd %:   29.37     % Non-Parse CPU:   88.77
    
     Shared Pool Statistics        Begin   End
                                   ------  ------
                 Memory Usage %:   68.51   55.64
        % SQL with executions>1:   35.69   37.35
      % Memory for SQL w/exec>1:   33.20   33.06
    
    Top 5 Timed Events
    ~~~~~~~~~~~~~~~~~~                                                     % Total
    Event                                               Waits    Time (s) Ela Time
    -------------------------------------------- ------------ ----------- --------
    CPU time                                                      156,641    43.46
    latch free                                     13,844,248     111,607    30.97
    db file sequential read                        28,582,231      34,784     9.65
    library cache pin                                  32,490      23,542     6.53
    log file sync                                   2,352,339      19,986     5.55

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    my.god

    106 hard parses per second??????? you need to fix the app, no two ways about that

  8. #8
    Join Date
    Nov 2000
    Location
    London
    Posts
    94
    ha ha! I know its truly awful.
    Ok what would you look at in the meatime?

  9. #9
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    find a new job to get away from it.

    Fix the app - that is what to look at

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width