RE: DB TIME in AWR
Date: Sun, 22 May 2016 05:05:41 -0700
Message-ID: <BLU179-W674644693AB15B47B1A9E0EB4D0_at_phx.gbl>
Thanks to those who corrected me offline. For example, see https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2166700202625 Also, see the default value of sessions at https://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams220.htm That will teach me not to do interviews in public. Iggy
From: iggy_fernandez_at_hotmail.com
To: dba.tyagisumit_at_gmail.com
CC: oracle-l_at_freelists.org
Subject: RE: DB TIME in AWR
Date: Sun, 22 May 2016 04:22:23 -0700
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-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_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/
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 .
--
--BRSumit Tyagi+91-7829543355
--
--BRSumit Tyagi+91-7829543355
--
http://www.freelists.org/webpage/oracle-l
Received on Sun May 22 2016 - 14:05:41 CEST