Bobby Durrett's DBA Blog

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

These are two Oracle Database usergroup presentations that I have done related to Oracle performance tuning.  Each has a Powerpoint presentation and a Word document.

First usergroup presentation – Profile of waitsand cpu

First usergroup paper – Profile of waits and cpu

Second usergroup paper – SQL tuning and cardinality

Second usergroup presentation – SQL tuning and cardinality

– Bobby

We starting seeing a ton of these waits on an Exadata system:

cell single block physical read

Normally the predominant wait is

cell smart table scan

Simple table full scans were doing the single block reads, whereas they normally would do smart scans.

Oracle support had us run this command to identify cell servers with “quarantined” plans:

cellcli -e list quarantine

They identified a cell server with several of these plans and had us run this command in cellcli:

drop quarantine all

Ideally you would patch up your cell servers, etc to the current release and this would reduce the number of quarantined plans.

Oracle note 1349167.1 contains an example of why a cell server would crash and cause a plan to be quarantined.

Evidently if you have a certain number of quarantined plans (6 I think) this fact becomes “visible” to the database servers and they start using single block reads instead of the multiblock smart scans.  Dropping the quarantined plans reduces the number of plans below the threshhold and the database goes back to doing smart scans.

– Bobby