When Oracle executes a SQL statement, it parses it to determine whether the syntax and its contents are correct.
This process can consume significant overhead. Once parsed, Oracle does not parse the statement again unless
the parsing information is aged from the memory cache and is no longer available. Ineffective memory sharing
among SQL statements can result in reparsing.

This tip helps you detect the general response time on parsing. The more your application is parsing, the more
contention exists, and the more time your system spends waiting. If parse time CPU represents a large percentage
of the CPU time, then time is being spent parsing instead of executing statements. If this is the case, then it is likely
that either the application is using literal SQL and not sharing it, or the shared pool is poorly configured. If Other is
the greatest percentage, look for SQL statements that are performing the most buffer gets and physical reads.

We can calculate 3 data values for this

1. Parse CPU time : This represents the percentage of CPU time spent parsing SQL statements. Parse time CPU can be a strong indication that an application has not been well tuned. High parse time CPU usually indicates that the application may be spending too much time opening and closing cursors or is not using bind variables.

2. Recursive CPU time : Sometimes, to execute a SQL statement issued by a user, the Oracle Server must issue additional statements. Such statements are called recursive calls or recursive SQL statements. For example, if you insert a row into a table that does not have enough space to hold that row, the Oracle Server makes recursive calls to allocate the space dynamically if dictionary managed tablespaces are being used.

Recursive calls are also generated due to the inavailability of dictionary info in the dictionary cache, firing of database triggers, execution of DDL, execution of SQL within PL/SQL blocks, functions or stored procedures and enforcement of referential integrity constraints

3. Other CPU time : This represents the percentage of time spent looking for buffers, fetching rows or index keys, etc. Generally, \"Other\" CPU should represent the highest percentage of CPU time out of the total CPU time used.

Calculations :

ParseCpuTime% = (deltaParseTime / deltaTotalTime)*100

RecursiveCpuTime% = (deltaRecursiveTime/deltaTotalTime)*100

OtherCpuTime% = (deltaTotalTime-(deltaParseTime+deltaRecursiveTime))/deltaTotalTime*100

deltaParseTime = select value from v$sysstat where name = \'parse time cpu\' ; between sample end and start

deltaRecursiveTime = select value from v$sysstat where name = \'recursive cpu usage\'; between sample end and start

deltaTotalTime = select value from v$sysstat where name = \'CPU used by this session\' between sample end and start

If you see a lot of ParseTime, refer to http://www.dbasupport.com/forums/sho...?threadid=9017

If the Oracle Server appears to be making excessive recursive calls while your application is running, determine
what activity is causing these recursive calls. If you determine that the recursive calls are caused by dynamic
extension, either reduce the frequency of extension by allocating larger extents or, if you are using Oracle 8.I,
considering taking advantage of locally managed tablespaces.

It may be possible to reduce \"Other\" CPU time by investigating SQL statements that have a high number of buffer_gets and/or a high number of physical reads.

[Edited by uday on 05-10-2001 at 01:52 PM]