Analyzing Instance Wide CPU Usage
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Analyzing Instance Wide CPU Usage

Hybrid View

  1. #1
    Join Date
    Feb 2001
    Posts
    163
    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]

  2. #2
    Join Date
    May 2001
    Posts
    2
    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.


    In one of your articles you indicated that if you have a high recurssive call
    select value from v$sysstat where name = 'recursive
    cpu usage';
    then determine what activity is causing these recursive calls.
    How do I find what is causing these recursive calls?
    The above paragraph was from you article.

    By the way - Whats up UDAY... Hope your doing good.

  3. #3
    Join Date
    May 2001
    Posts
    2
    How do you determine the processes / sql statements /procedures that are causing the excissive recursive calls.

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