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-idlewait 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 .
--
--BRSumit Tyagi+91-7829543355
--
--BRSumit Tyagi+91-7829543355
