Re: DB TIME in AWR

CPU Time is reported as DB CPU in the Time Model

DB Time = DB CPU + Wait Time
So you can derive DB Time by looking at DB CPU and the wait time. If 5 sessions are active for 15 minutes then the DB Time by those 5 is 75 minutes. There might be time waiting for CPU, please look Kyley's blog

http://datavirtualizer.com/oracle-cpu-time/

Thanks

On Sun, May 22, 2016 at 2:06 PM, sumit Tyagi <dba.tyagisumit_at_gmail.com> wrote:

> Hi Iggy / Ls,
>
> I think i got it now for DB time . Need some clarification for CPU time .
> how can we calculate CPU time
>
> Suppose 5 foreground sessions are active and executing long queries
> running for entire 15 minutes . CPU core is 4 .
>
> In this case by definition :
>
> DB Time = Total time in database calls by foreground sessions
> DB Time = 15 Minutes * 5 Active sessions
> DB Time = 75 Minutes
>
>
>
>
>
> On Sun, May 22, 2016 at 4:52 PM, Iggy Fernandez <
> iggy_fernandez_at_hotmail.com> wrote:
>
>> I believe that sessions does not include parallel processes which also
>> contribute to DB time.
>>
>> However, the processes setting includes some processes that do not
>> contribute to DB time.
>>
>> So sessions * elapsed time may be an underestimate whereas processes *
>> elapsed time is an overestimate.
>>
>> re: CPU cores. You may be confusing "CPU time" and "DB time". DB Time is
>> the "sum of the elapsed times of database calls by foreground sessions"
>> (definition in the webinar) and is more than just CPU time. Therefore it is
>> no related to cores or threads.
>>
>>
>> ------------------------------
>> From: dba.tyagisumit_at_gmail.com
>> Date: Sun, 22 May 2016 16:30:43 +0530
>> Subject: Re: DB TIME in AWR
>> To: iggy_fernandez_at_hotmail.com
>> CC: oracle-l_at_freelists.org
>>
>>
>> Not an interview question :) I was going through the attached webinar *Who
>> Ate My DB Time? Advanced Techniques For Database Performance Analysis*,
>> got this question from one of the slide :
>>
>> *As per the doc answer is Number of ‘sessions’ * elapsed time . but want
>> to know why cpu cores are not considered ?*
>>
>> *DB Time: Quiz*
>>
>> *Question: On a four CPU system, for one hour elapsed time, what **is
>> the maximum amount of DB Time that can be accumulated? **Hint: How many
>> sessions can wait in a queue?*
>>
>> *Answer: Number of ‘sessions’ * elapsed time*
>> *.*
>> *The maximum number of sessions that are on CPU or in a non-idle*
>> *wait is the init.ora parameter ‘sessions’. If ‘sessions’ is 300 then:*
>> *300 * 1 hour = 300 hours of DB Time*
>>
>> *Copyright © 2011, Oracle and/or its affiliates. All rights **reserved*
>>
>>
>> On Sun, May 22, 2016 at 4:06 PM, Iggy Fernandez <
>> iggy_fernandez_at_hotmail.com> wrote:
>>
>> There are many processes than there are sessions.
>>
>> My answer is processes * elapsed time
>>
>> From the documentation: "PROCESSES specifies the maximum number of
>> operating system user processes that can simultaneously connect to Oracle.
>> Its value should allow for all background processes such as locks, job
>> queue processes, and parallel execution processes." (
>> https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams169.htm#REFRN10175
>> )
>>
>> Your system will choke before it gets anywhere close to this theoretical
>> maximum. A rule of thumb is that you should be concerned if your db time
>> exceeds elapsed time * CPU threads; that is, if average active sessions
>> (AAS) is greater than the number of CPU threads (threads not cores).
>>
>> P.S. I hope that is not an interview question. I usually fail DBA
>> interviews because of questions like that.
>>
>>
>> https://iggyfernandez.wordpress.com/2010/10/31/the-worst-interview-of-my-life/
>>
>>
>>
>> https://iggyfernandez.wordpress.com/2012/07/16/how-not-to-interview-a-database-administrator-part-i-the-google-way/
>>
>>
>>
>>
>>
>>
>>
>>
>> ------------------------------
>> From: dba.tyagisumit_at_gmail.com
>> Date: Sun, 22 May 2016 15:21:58 +0530
>> Subject: DB TIME in AWR
>> To: oracle-l_at_freelists.org
>>
>>
>> *On a four CPU system ( 4 CPU CORE ) , for one hour elapsed time, what is
>> the maximum amount of DB Time that can be accumulated? *
>>
>> Answer: Number of ‘sessions’ in int.ora * elapsed time = 300 * 1 hours
>> = 300 Hours
>>
>> *OR *
>>
>> Answer : Number of ‘sessions’ in int.ora * elapsed time * CPU Cores =
>> 300 * 1 * 4 = 1200 Hours
>>
>>
>> Which one is correct any why . Little confusion here .
>>
>>
>>
>> --
>>
>> *--*
>> *BR*
>> *Sumit Tyagi*
>> *+91-7829543355*
>>
>>
>>
>>
>> --
>>
>> *--*
>> *BR*
>> *Sumit Tyagi*
>> *+91-7829543355 <%2B91-7829543355>*
>>
>
>
>
> --
>
> *--*
> *BR*
> *Sumit Tyagi*
> *+91-7829543355 <%2B91-7829543355>*
>

--
http://www.freelists.org/webpage/oracle-l