Bobby Durrett's DBA Blog

You many wonder why you should care about Oracle Resource Manager wait events.  You should care because every Oracle 11g database uses Resource Manager by default during the maintenance windows.  So, even if you have not intentionally configured RM you may see waits that start with resmgr:  which are RM waits.  It is helpful to understand what the two main RM waits are and what they mean.

The default maintenance windows are M-F 22:00 for four hours and 06:00 for 20 hours on the weekend.  Several times we have had performance complaints and had people say that one of the main waits during the slow time was “resmgr:cpu quantum“.

The resmgr:cpu quantum wait event means that your database server’s CPU is high enough that resource manager has kicked in and is dividing up the CPU cycles among the various sessions that need it.  The plan that runs by default during the maintenance window is called DEFAULT_MAINTENANCE_PLAN.  This plan allocates most of the CPU resources to tasks other than the maintenance tasks.  This is really a helpful feature because it prevents the maintenance tasks like gathering optimizer statistics from eating up a lot of CPU resources that are needed by user processes.  But, if you have a busy system and see a bunch of resmgr:cpu quantum waits and don’t know what they are it can be disconcerting.  The solution is to look at what is using so much CPU and tune that.  Resource Manager actually does a great job of dialing back the CPU of the maintenance jobs so there is no need to disable it.

I’ve seen one situation where resmgr:cpu quantum indicated a real problem.  The active plan had a 0% CPU allocation.  In this case the session was waiting on a resmgr:cpu quantum wait but never got a CPU slice.  This is essentially a hang and needs to be addressed by giving the user associated with the session a > 0% CPU allocation.

The second RM wait that I have seen is “resmgr:become active“.  You will only see this wait if you have configured RM yourself to have limits on the number of active sessions.  When the number of active sessions that are in a given consumer group reaches the maximum then any other sessions that want to become active will wait on resmgr:become active.  I really don’t like active session limits and encourage you to carefully consider whether these limits make sense in your situation.  The sessions waiting on resmgr:become active are essentially hung and eventually will timout.  If you have a hang like situation and see a bunch of resmgr:become active waits then there is cause for concern and you may need to increase or eliminate your active session limits.

So, to summarize:

resmgr:cpu quantum – sessions are sharing CPU, not necessarily a problem

resmgr:become active – sessions are hung , can be a big problem

– Bobby

Found a couple of Oracle blog aggregators.  These sites combine the many Oracle DBA blogs into single sites.

http://www.orafaq.com/

http://orana.info/ – NO LONGER EXISTS

It looks like Oracle has an aggregator for their own employees:

https://blogs.oracle.com/

Pretty cool.

– Bobby

I’ve been using the view DBA_HIST_ACTIVE_SESS_HISTORY to investigate problems lately.  I’ve found this useful for both performance problems and hangs.  I’ve done some more complicated things, but I find that just dumping out every column of the view for a very narrow window of time can help explain what was happening in a way that other tools do not. 

DBA_HIST_ACTIVE_SESS_HISTORY records the list of active sessions every 10 seconds.  It is like looking at your active sessions in Toad except going back in time.  We have been keeping six weeks of history on the AWR so I can go back to any ten second interval over the past six weeks and see what was running.

In the case of a hang or locking you can see the blocking sessions and what they are spending time on.  Here is a recent example I used to debug a hang caused by sessions on two different instances – 1 and 12.  I picked a 10 second interval that I knew was during the hang:

select 
*
from DBA_HIST_ACTIVE_SESS_HISTORY
where 
sample_time 
between 
to_date('18-APR-2012 10:40:00','DD-MON-YYYY HH24:MI:SS')
and 
to_date('18-APR-2012 10:40:10','DD-MON-YYYY HH24:MI:SS')
and instance_number in (1,12)
order by sample_time,instance_number,SESSION_ID;

One key point if you have a hang or other issue is to run DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT before bouncing the database if you have to bounce.  Otherwise you will lose all of the active session history since the last snapshot when the database bounces.

– Bobby

Found a helpful PL/SQL block in this presentation:

http://www.sagelogix.com/idc/groups/public/documents/sagelogix-presentation/sage016048.pdf – NO LONGER EXISTS

This shows you which events are set – i.e. by an alter system set events command.  I modified this to have a larger range of event values – to 29999.

declare
event_level number;
begin
for i in 10000..29999 loop
sys.dbms_system.read_ev(i,event_level);
-- note: requires exec permission for DBMS_SYSTEM
if (event_level > 0) then
dbms_output.put_line
('Event '||i||' set at level '|| event_level);
end if;
end loop;
end;
/

This shows the events that were set dynamically as well as any set in the init parameter event.

Run:

alter system set events
'10503 trace name context forever, level 4000';

Then run the PL/SQL block and you get this:

Event 10503 set at level 4000

Also, Oracle note 160178.1 has a good explanation of setting events dynamically and in an spfile.

– Bobby

Found a cool package and procedure while working on a problem today.

DBMS_SPACE.SPACE_USAGE

This takes a segment and shows how many blocks are free or partially filled.

In our case we hit a bug which resulting in tons of unformatted blocks and this was the recommended fix.

Oracle note 729149.1 describes the issue and shows an example use of DBMS_SPACE.SPACE_USAGE.

Here is a simple script and log:  https://www.bobbydurrettdba.com/uploads/space_usage.zip

Here is the output of the PL/SQL block:

Total number of blocks that are unformatted: 0
Number of blocks that has at least 0 to 25% free space: 0
Number of blocks that has at least 25 to 50% free space: 0
Number of blocks that has at least 50 to 75% free space: 0
Number of blocks that has at least 75 to 100% free space: 5
Total number of blocks that are full in the segment: 0

The example is a small table with the minimum 5 blocks and only 1 row.   The output says that the 5 blocks all have at least 75% of their space free.

– Bobby

See this testcase run on 11.2.0.3:

https://www.bobbydurrettdba.com/uploads/emptystats.zip

If you have a subpartitioned table – at least of the type that I tested – the Oracle delivered stats job will leave the statistics empty (NULL) for empty subpartitions instead of setting them to zero.  If you manually gather statistics on the same table you will have zero stats on the empty subpartitions.

I can’t see any evidence of this causing a problem but it is disconcerting to see NULL stats when you are expecting the job to gather them.

– Bobby

Here is a new script and example output:

https://www.bobbydurrettdba.com/uploads/maxactive.zip

Use this query if you are using resource manager and have set a maximum number of active sessions.  This SQL reports the instance number and resource consumer group that have exceeded the maximum.

Here is the text of the query:

select
act.inst_id,act.resource_consumer_group,act.num_active,
pd.active_sess_pool_p1 max_active
from
(select inst_id,resource_consumer_group,count(*) num_active
from gv$session 
where status='ACTIVE' and
type='USER' and
program not like '%(P%)'
group by inst_id,resource_consumer_group) act,
DBA_RSRC_PLAN_DIRECTIVES pd,
gv$rsrc_plan pl
where
pd.plan=pl.name and
act.inst_id=pl.inst_id and
pl.is_top_plan='TRUE' and
act.resource_consumer_group=pd.group_or_subplan and
act.num_active > pd.active_sess_pool_p1;

– Bobby

P.S.  Modified query on 4/3/2012 – wasn’t excluding parallel query slaves correctly

Discovered these columns on the v$session and gv$session views:

FINAL_BLOCKING_INSTANCE and FINAL_BLOCKING_SESSION

These appear to be new in 11.2.  I checked an 11.1.0.7 instance and they weren’t there.  They were there in an 11.2.0.1 instance.  Interestingly an early version of the 11.2 documentation didn’t have them.  I have Oracle document E10820-03 on my hard drive and it didn’t have a reference to these columns, but the most current 11.2 docs do.

Anyway, I used these to determine which Oracle session was holding library cache locks.  In earlier versions I don’t believe that the v$/gv$session views were that helpful with library cache locks.  They worked with regular locks only.  So, check these out when you have a locking situation and don’t know which SID is blocking the others.

Current v$session documentation here:

https://docs.oracle.com/cd/E11882_01/server.112/e40402/dynviews_3016.htm#REFRN30223

– Bobby

I attended an Oracle sponsored day yesterday on Oracle’s engineered systems.  The main speaker was Tom Kyte who is a widely known Oracle performance expert.  After his talk was complete he graciously agreed to talk with me about performance issues with a web site that has hundreds of sessions to the database in a connection pool.  Tom said that the connection pool should be roughly twice the number of CPU cores and not many times that amount as they are in our case.  He recommended this Oracle produced youtube video that demonstrates that fewer sessions in a session pool produce better throughput:

http://www.youtube.com/watch?v=xNDnVOCdvQ0&feature=youtube_gdata_player

It isn’t immediately obvious to me why this is the case.

Tom Kyte recommended changing the application to use a smaller pool of connections or if that’s not possible to use shared servers.  In my case shared servers seems like a better option because it would take more effort to modify the application tier.  We could set the number of real connections to twice the number of cpus and allow the web servers to spin up hundereds of shared server connections instead of decidated server connections.  We need to test it to prove it out of course.  No guarantees until you test it in your own environment.

– Bobby

If you use DBMS_STATS.GATHER_SCHEMA_STATS or DBMS_STATS.GATHER_DATABASE_STATS with options=> ‘GATHER AUTO’ then the preferences for the tables whose statistics are gathered are ignored.

See this testcase which includes a SQL*Plus script and log demonstrating that GATHER_SCHEMA_STATS  ignores a preference.  I’ve seen the same thing with GATHER_DATABASE_STATS but it wasn’t as quick to build a test case.

So, if you depend on table preferences don’t use GATHER AUTO.

– Bobby