FAQ
Hi -
This is just a curious question that popped up my mind by looking at
someone's AWR.. I dont have access to database and wont be able to run any
related query or anything.

The TimeModel statistics have been pasted here: http://pastebin.com/YZ7SRX0M

The question is related to "DB CPU" metric ..

Here is what I know or my thought process.. please correct me, if I am
missing anything or incorrectly ruling out

the SQL elapsed time accounts only to less than 50% (if there was a
culprit sql or API, it should hv been counted against this.)
parsing is near zero (so its not parsing cpu)
there could have been no PL/SQL (looping with dbms_lock.sleep or anything
similar) because PLSQL exec time is almost zero

what could be causing this DB CPU.. ?? if the db is spending 83% on total
db time cpu but doesnt correspond to sql or plsql elapsed time, is it a good
or bad indicator?

Thanks in advance,
Venkat

PS: DB version : 11.2.0.2 running on Solaris

Search Discussions

  • Niall Litchfield at Aug 16, 2011 at 5:53 am
    On a machine with more than one 'CPU' you can spend more than 1 second on
    cpu time for each second of elapsed time.
    On Mon, Aug 15, 2011 at 9:08 PM, Venkat Krish wrote:

    Hi -
    This is just a curious question that popped up my mind by looking at
    someone's AWR.. I dont have access to database and wont be able to run any
    related query or anything.

    The TimeModel statistics have been pasted here:
    http://pastebin.com/YZ7SRX0M


    The question is related to "DB CPU" metric ..

    Here is what I know or my thought process.. please correct me, if I am
    missing anything or incorrectly ruling out

    -- the SQL elapsed time accounts only to less than 50% (if there was a
    culprit sql or API, it should hv been counted against this.)
    -- parsing is near zero (so its not parsing cpu)
    -- there could have been no PL/SQL (looping with dbms_lock.sleep or
    anything similar) because PLSQL exec time is almost zero


    what could be causing this DB CPU.. ?? if the db is spending 83% on total
    db time cpu but doesnt correspond to sql or plsql elapsed time, is it a good
    or bad indicator?

    Thanks in advance,
    Venkat

    PS: DB version : 11.2.0.2 running on Solaris

    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info

    --
    http://www.freelists.org/webpage/oracle-l
  • Venkat Krish at Aug 16, 2011 at 12:59 pm
    Thanks Niall.. I understand CPU seconds is depedent on # of cpus..

    Usually if the cpus were consumed by SQLs (hash-joins,sorts, or I/Os),
    shouldn't the sql elapsed time also be approximately equal or more than DB
    CPU % (83% in this case).. I am lost on why sql elapsed time reports only
    46% and there is nothing else (pl/sql elapsed time, parse time) that is
    significantly higher..

    I guess, my question is.. can there be "DB CPU" slice thats not part of
    sql,pl/sql & parsing ?

    Thanks
    Venkat

    On Tue, Aug 16, 2011 at 1:53 AM, Niall Litchfield <
    niall.litchfield_at_gmail.com> wrote:
    On a machine with more than one 'CPU' you can spend more than 1 second on
    cpu time for each second of elapsed time.

    On Mon, Aug 15, 2011 at 9:08 PM, Venkat Krish wrote:

    Hi -
    This is just a curious question that popped up my mind by looking at
    someone's AWR.. I dont have access to database and wont be able to run any
    related query or anything.

    The TimeModel statistics have been pasted here:
    http://pastebin.com/YZ7SRX0M


    The question is related to "DB CPU" metric ..

    Here is what I know or my thought process.. please correct me, if I am
    missing anything or incorrectly ruling out

    -- the SQL elapsed time accounts only to less than 50% (if there was a
    culprit sql or API, it should hv been counted against this.)
    -- parsing is near zero (so its not parsing cpu)
    -- there could have been no PL/SQL (looping with dbms_lock.sleep or
    anything similar) because PLSQL exec time is almost zero


    what could be causing this DB CPU.. ?? if the db is spending 83% on total
    db time cpu but doesnt correspond to sql or plsql elapsed time, is it a good
    or bad indicator?

    Thanks in advance,
    Venkat

    PS: DB version : 11.2.0.2 running on Solaris


    --
    Niall Litchfield
    Oracle DBA
    http://www.orawin.info
    --
    http://www.freelists.org/webpage/oracle-l
  • Niall Litchfield at Aug 16, 2011 at 1:19 pm
    Any sql statement that executes in parallel can consume more CPU than
    elapsed time - indeed that is rather the point.

    On 16 Aug 2011 13:59, "Venkat Krish" wrote:

    Thanks Niall.. I understand CPU seconds is depedent on # of cpus..

    Usually if the cpus were consumed by SQLs (hash-joins,sorts, or I/Os),
    shouldn't the sql elapsed time also be approximately equal or more than DB
    CPU % (83% in this case).. I am lost on why sql elapsed time reports only
    46% and there is nothing else (pl/sql elapsed time, parse time) that is
    significantly higher..

    I guess, my question is.. can there be "DB CPU" slice thats not part of
    sql,pl/sql & parsing ?

    Thanks
    Venkat

    On Tue, Aug 16, 2011 at 1:53 AM, Niall Litchfield <
    niall.litchfield_at_gmail.com> wrote:
    On a machi...

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 15, '11 at 8:08p
activeAug 16, '11 at 1:19p
posts4
users2
websiteoracle.com

2 users in discussion

Niall Litchfield: 2 posts Venkat Krish: 2 posts

People

Translate

site design / logo © 2022 Grokbase