Vishal desai’s Oracle Blog

August 21, 2017

Instance caging

Filed under: Oracle Database, Performance Tuning — vishaldesai @ 1:54 pm

To determine maximum CPU threads utilized we can look at top activity in Grid control, but the top activity view is limited to 24 hours. What if we want to look at weekly or monthly trends (example figure 1 below)? It will be tedious and boring task to view top activity one day at a time and then determine the maximum CPU threads utilized.


Figure 1

“CPU usage per sec” metric from dba_hist_sysmetric_summary can be converted to seconds and joined with other views to get Grid Control type visual.  Figure 2 is from Grid control and Figure 3 is created using maxthreadused alias from attached script.


Figure 2 – Grid control view


Figure 3 – dba_hist_sysmetric_summary view

There are additional columns in script such as average and maximum cpu threads utilized, standard deviation (lower the value meaning higher confidence in average compared to maximum). “Sum of the squared deviations from the mean” is generally used for comparison. I have not added that metric in script but can be easily modified to add that column and used for comparison of two periods if necessary. If you want to look at trends of multiple instance you can quickly add pivot/decode and plot in excel to look at trends of multiple instances.

Script attached: awr_instance_caging_new.sql

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at

%d bloggers like this: