Striving for Optimal Performance

Challenges and Chances of the 11g Query Optimizer is the name of a presentation I gave at several events (e.g. Trivadis Performance Days, Oracle OpenWorld, DOAG Konferenz, UKOUG Conference) throughout 2011. Its abstract is the following:

With every new release, the query optimizer is enhanced. Oracle Database 11g Release 1 and Release 2 are no exception to the rule. Specifically, they introduce key improvements in the following areas: indexing, optimization techniques, object statistics and plan stability. The aim of this presentation is to review the new features from a practical point of view as well as to point out challenges related to them. In other words, to let you know what you can expect from the query optimizer when you upgrade to Oracle Database 11g.

The aim of this short post is to point out that I made available the current version of the slides and all the scripts that go with them here.

The structure of the presentation (incl. a reference to the available scripts) is the following:

Observations
- Number of Query Optimizer Parameters by Release
- Number of Query Optimizer Bugs Fixed by Patchset
Indexing
- Invisible Indexes (ex_invisible_index.sql)
- Index Support for Linguistic LIKE (ex_linguistic_like.sql)
- INDEX REBUILD and Statistics History (ex_index_rebuild.sql)
Optimization Techniques
- Full Outer Join (ex_full_outer_join.sql)
- Join-Filter Pruning (ex_join_filter_pruning.sql)
- Table Expansion (ex_table_expansion.sql)
- Join Factorization (ex_join_factorization.sql)
- OR Expansion (ex_or_expansion.sql)
- Join Elimination (ex_join_elimination.sql)
- Subquery Unnesting (ex_subquery_unnesting.sql)
System and Object Statistics (DBMS_STATS)
- Workload System Statistics
- Object Statistics – Default Preferences
- Object Statistics – Auto Sample Size
- Object Statistics – Pending Statistics (ex_pending_object_statistics.sql)
- Object Statistics – Incremental Statistics (ex_incremental_stats.sql)
- Object Statistics – Extended Statistics on Expressions (ex_extended_statistics1.sql)
- Object Statistics – Extended Statistics on Column Groups (ex_extended_statistics2.sql)
- Object Statistics – Seeding Column Groups
- Object Statistics – Comparing Statistics (ex_comparing_statistics.sql)
- Object Statistics – Locks not Exported
- JOB_QUEUE_PROCESSES
Plan Stability
- CURSOR_SHARING
- SQL Plan Baselines (ex_execution_plan_stability.sql, ex_execution_plan_stability_10g.sql, ex_execution_plan_stability_11g.sql)
- Stored Outlines
- Adaptive Cursor Sharing (ex_bind_peeking.sql, ex_bind_peeking_bind_aware.sql)
- Cardinality Feedback (ex_cardinality_feedback.sql)

Since the introduction of 11.2 I receive on a regular basis questions related to “strange” workload system statistics. The last email on that topic was sent to me yesterday. So, instead to send, again, a private reply, I decided to write this short post.

What’s wrong with 11.2 and workload system statistics?

Let’s have a look to the output of the following query:

SQL> SELECT pname, pval1
  2  FROM sys.aux_stats$
  3  WHERE sname = 'SYSSTATS_MAIN';

PNAME                  PVAL1
--------------- ------------
CPUSPEEDNW            1596.0
IOSEEKTIM                4.0
IOTFRSPEED            4096.0
SREADTIM             10900.3
MREADTIM              4525.8
CPUSPEED              1603.0
MBRC                     7.0
MAXTHR            17391616.0
SLAVETHR            413696.0

As you can see the SREADTIM and MREADTIM times are very high. In this case about three orders of magnitude of what you would expect from a regular system.

I’m not aware of the exact cause of this problem, but to me it seems that the statistics externalized in x$kcfio are broken. Anyway, in MOS there are several bugs related to it (9842771 and 9701256). Hence, it’s not a feature (e.g. a change in the unit of measure), it’s a bug. On my Linux test system I’m able to reproduce it on both 11.2.0.1 and 11.2.0.2. According to the bugs mentioned before, the problem is not limited to Linux.

Since with the DBMS_STATS package we are not able to gather correct statistics, the only advice I can give on that topic is that you have to manually set them to sensible values.

Update 2011-03-23

To fix the problem you can install the patch 9842771. It is available for 11.2.0.1 and 11.2.0.2. By the way, since the patch only provides a new version of the dbms_stats_internal package, the statistics externalized in x$kcfio are not broken… they just have another unit of measure.

Update 2011-10-10

The fix for this problem is included in the 11.2.0.3 patch set.

Posts in category System Statistics

Challenges and Chances of the 11g Query Optimizer

Workload System Statistics Bug in 11.2