DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Statspack Report

  1. #1
    Join Date
    Aug 2005
    Posts
    17

    Statspack Report

    Could any one plz explian me in detail what is-
    1. Latch Hit
    2. Parse CPU to Parse Elapsd
    3. Non-Parse CPU
    These are in statspack report.

  2. #2
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191
    Latches are simple, low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the list of users currently accessing the database, and protect the data structures describing the blocks in the buffer cache. A server or background process must acquire the accompanying latch to start manipulating or selecting a shared data structure and must release the accompanying latch when finished.
    Latches are managed by Oracle internally. It cannot be tuned, but latch information can be used to tune the SGA.

    There is no information in the Oracle 9i Performance Tuning Press regarding

    2. Parse CPU to Parse Elapsd
    3. Non-Parse CPU

    Is it really that important, I dont think so, otherwise they wound have included it in oracle press.

    Able was I ere I saw Elba

  3. #3
    Join Date
    Aug 2002
    Location
    Sofia, Bulgaria
    Posts
    84
    Hi,
    See the quote from Metalink Note: 228913.1
    It is possible for both the 'buffer hit ratio' and the 'execute to parse' ratios to be negative. In the case of the buffer hit ratio, the buffer cache is too small and the data in is being aged out before it can be used so it must be retrieved again. This is a form of thrashing which degrades performance immensely.
    The execute to parse ratio can be negative when the number of parses is larger than the number of executions. The Execute to Parse ratio is determined by the following formula:

    100 * (1 - Parses/Executions) = Execute to Parse
    Here this becomes:
    100 * (1 - 42,757 / 42,023 ) = 100 * (1 - 1.0175) = 100* -0.0175 = -1.75

    This can be caused by the snapshot boundary occurring during a period of high parsing so that the executions have not occurred before the end of the snapshot. Check the next snapshot to see if there are enough executes to account for the parses in this report.
    Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed. This is another form of thrashing which also degrades performance tremendously.
    Another quote:
    - Execute to parse ratio compares the number of executions with the number of parses. For each statement ideally only one parse is required. But in your case you are parsing statements but not executing them. Over-parsing is a pure overhead and therefore your “Non-Parse CPU” is quite low - usually one would expect here only 1-2% of CPU time to be used for parsing. Query v$sql or v$sqlarea to find out statements that are parsed too many times and check your application code!!!!
    - "Parse CPU to Parse Elapsd %" compares the pure CPU time used for parsing with the overall time used for parsing known also as the “parse elapsed time”. One would expect that only CPU time is used for parsing, but Oracle has to populate dictionary cache to be able to parse as fast as possible. Obviously in your case during the parse operation there are some wait events occurring (some time is lost) which is reported as parse elapsed time that contains parse CPU time + “some other time”. As you are parsing too much this may be some latch problems (library cache latch, maybe also shared pool latch) although "latch free" wait event is not reported among top five wit events. In your case I would first focus to reduce parsing overhead.
    Hope this helps!
    Radoslav Rusinov
    OCP 8i,9i,10g DBA
    http://dba-blog.blogspot.com

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    The Execute to Parse ratio is determined by the following formula:

    100 * (1 - Parses/Executions) = Execute to Parse
    Someone at Oracle was having a bad day when they thought that one up.
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

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