Getting AWR-type data at less than hourly granularity

[former_member219198]

Our FVP DB/CI has high CPU spikes in the 2000 hour on Saturdays. Since the SAP-related CPU reported in ST03 is negligible on FVP, I assumed
the high CPU was driven by Oracle. But when I looked at the 2000-2100 hour on Saturday, July 20, I found that some of the AWR data didn't
appear to make sense, given the CPU data. Details are in the attachment. 

Basically, AWR seems to be giving inconsistent data on how much of the CPU is being used for database work.  Is the strange result I'm seeing in the AWR report due to a hourly interval being insufficiently granular for what I'm measuring? Is there any way to get more granular performance data from Oracle?  Or am I just interpreting the report incorrectly?

Thanks very much.

[former_member219198]

Stefan,

I got your response via e-mail and reviewed it.  Thank you very much for explaining the data in detail. I now understand how the AWR data points in question relate to one another.  I'll look at glance in the 2000 hour to find what processes are hogging up the CPU.

                                                  Gordon

[stefan_koehler]

Hi Gordon,

you can manually create short time snapshots with PL/SQL procedure DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT or change the general snapshot interval with procedure DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS.

> Basically, AWR seems to be giving inconsistent data on how much of the CPU is being used for database work.

I don't think so please provide the corresponding full AWR report. The provided snippet is much too less information, because of a lot of metadata for these metrics is missing. You can also drop me an e-mail (check out my SCN profile) with the full AWR report, if you don't want to provide it in public.

> Is there any way to get more granular performance data from Oracle?

You can also query the AWR and ASH samples manually (to get more detailed and granular information), but at first i would interpret and crosscheck the full AWR report.

> Or am I just interpreting the report incorrectly?

That's mostly the issue, because of usually just a few metrics are looked at, but not at the scale basis as well or the data basis (how and when are the values or metrics incremented / measured and populated) is not known.

Regards

Stefan

[Former Member]

Gordon, i suggest you also look at a 3hr report (one hour before 2000 and one hour after 2100, if you still can). I think that for long running  operations that overlap hour boundaries a one hour report might be misleading. But i don't think this is the case here.

Have a look at http://savvinov.com/2012/04/06/awr-reports-interpreting-cpu-usage/

Unfortunately, AWR reports only provide CPU time estimates either in absolute units or as a percentage of DB time, but not in terms of the overall capacity. It’s not wrong: you need to know what percentage of user calls falls on CPU time to see whether or not it’s contributing appreciably to response times. But it’s not complete, because when talking about resource usage you need to know what % of total resource available is being used. Fortunately, it’s quite simple to calculate that:

DB CPU usage (% of CPU power available) = CPU time / NUM_CPUS / elapsed time

Cheers Michael

[stefan_koehler]

Hi Michael,

that's exactly what i have mentioned by "or the data basis (how and when are the values or metrics incremented / measured and populated) is not known."

However you can calculate the correct CPU usage with other metrics (even for a one hour snapshot) - for example:

The CPU time recorded in the "Top 5" as accumulated at the end of a database call. The DB CPU reported in the "Time Model" stats is incremented every few seconds.
...
You can see that the total CPU used over the two reports is about 181 seconds, but the "Top 5" sees none of that until the query completes. The "Time Model" stats report the split of CPU usage across the sessions.

Source: https://forums.oracle.com/message/2573383#2573383

The problem with a larger AWR report time scale is, that you can not break it down to the mentioned time frame properly (it will become very incorrect by increasing the time scale) - you just can calculate averages over 3 hours or so (by using the AWR report).

Manual queries on ASH are always possible for getting the details :-))

Regards

Stefan

[former_member219198]

Stefan,

I will send you the full AWR report via e-mail.

Thanks very much.

                         Gordon