Found a couple of Oracle blog aggregators. These sites combine the many Oracle DBA blogs into single sites.
http://orana.info/ – NO LONGER EXISTS
It looks like Oracle has an aggregator for their own employees:
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
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
