Monthly Archives: March 2012

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 →

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

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

We are hitting this bug: Bug 6510615  REF CURSOR opened by PLSQL does not use session cursor cache An application I work on does a ton of executions of packages with cursor variables returning ref cursors.  Apparently the session cursor cache … Continue reading →