Category Archives: Uncategorized

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →

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 … Continue reading →