Re: DB time from awr

Replies in line:

On 10/11/2017 12:56 AM, Cee Pee wrote:
> List,
>
> I was recently looking to understand an AWR report after reading about
> learning AWRs.
>
> Here is a sample output from a prod DB:
>
> Inline image 1
>
>
> I am looking at the TMS (time model stats) section and I have 2 qns
> about it:
>
> Inline image 2
>
> 1) Why is total % well above 100.

Because the machine has multiple CPU resources. It's a problem with the measurement.

>
> 2) 'sql execute elapsed time' is only 44%! what was the system doing
> rest of the time.

The system was probably not doing anything. If that is a Unix or Linux system, compare the sar report for the same period. That should tell you what the CPU consumption really was. Be careful, sar is not perfect, either. There is an item in sar report which is called "IO wait", which doesn't make much sense. When process calls IO, it relinquishes the CPU. That is how process scheduling works in time sharing systems. There is no such thing as "waiting for CPU". Also, interrupt processing is normally not charged to the process on behalf of which the IO interrupt is done. The reason is the fact that disk interrupt handler does not run in the process context. The situation in newer kernels is slightly better, but the old kernels used to spend up to 5% of time on the interrupt stack. Unfortunately, there are very few accurate utilities which show the exact amount of CPU time spent by a process.

> Is it right to assume that 44% of sql exec elapsed time is bad?

Define bad. Do you have a performance problem? Gaja Krishna Vaidyanatha, very esteemed former member of this list, has coined an acronym CTD, which stands for "Compulsory Tuning Disease" and applies to the situations in which DBA is attempting to "tune" the database, without actually having a problem. First of all, you cannot tune the database. You can only tune the application accessing the database. That is the same like attempting to tune a warehouse. Warehouse is just an empty space into which the stuff is stored or from which the stuff is taken. You can't tune the warehouse, you can tune the process of accessing the warehouse. Your database is just a method of storing data. Second, as Cary Millsap will tell you, you actually need application traces to tune the application. There is something called "Oracle application tuning method" and is based on analyzing the trace files, to find out where the time is spent. It's based on the wait event interface. AWR reports are definitely not sufficient for tuning the application. I see a lot of AWR superstition, people trying to use AWR reports as a definitive method for "tuning the database", whatever that may mean.

>
>
> Same server, 1 hour before:
>
> Inline image 3
>
>
> And I was looking at the AWR for the same DB from a week later for a
> period of 3 hours, the total % of DB time only add up to 82%, 83% and
> 86% respectively during three consecutive hours. That was the time
> when things were 'real bad' for the users.
>
> Can someone help me understand this? v11.2

Actually no. Jonathan Lewis and Christian Antognini have written several articles about the exact meaning of those statistics, but the actual meaning is still unclear and version dependent. I usually use it just as an orientation and say that parsing has consumed 36.52 CPU seconds, out of 3600 x number of processors. SQL Execute has consumed around 10 minutes. Also, please note that some of these items are not exclusive. Hard parse time is also being reported as parse time elapsed and parse time is being reported as sql execute time. Interpreting those quantities is like interpreting the Jabberwocky poem by Lewis Carroll. Is someone wants to try, the poem is here:

https://www.poetryfoundation.org/poems/42916/jabberwocky

It should have been named "The AWR Poem". Here is an OTN discussion with the same level of clarity:

https://community.oracle.com/thread/3905168?start=15&tstart=0

Here is the list of articles about AWR/Statspack:

https://jonathanlewis.wordpress.com/statspack-examples/

You can also try with this:
https://savvinov.com/2012/04/06/awr-reports-interpreting-cpu-usage/ http://dboptimizer.com/2011/07/21/oracle-cpu-time/

Personally, I find this topic eerily similar to the discussion about the number of angels that can dance on the head of a pin.

-- 
Mladen Gogala
Oracle DBA
Tel: (347) 321-1217


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