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.
Printable View
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.
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.
:p
Hi,
See the quote from Metalink Note: 228913.1
Another quote:Quote:
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.
Hope this helps!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.
Someone at Oracle was having a bad day when they thought that one up.Quote:
The Execute to Parse ratio is determined by the following formula:
100 * (1 - Parses/Executions) = Execute to Parse