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@xxxxxxxxxxx>
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@xxxxxxxxx
Date: Sun, 22 May 2016 16:30:43 +0530
Subject: Re: DB TIME in AWR
To: iggy_fernandez@xxxxxxxxxxx
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxx> 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@xxxxxxxxx
Date: Sun, 22 May 2016 15:21:58 +0530
Subject: DB TIME in AWR
To: oracle-l@xxxxxxxxxxxxx
*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>*
