Oracle – New Food for Performance Junkies

I am used to it with XMLDB, but apparently it is also applied on other Oracle functionality: new features being introduced while “only” applying a patch. Thanks to Doug, I was made aware on the whitepaper published by the Optimizer Development Group called “Upgrading from Oracle database 9i to 10g: What to expect from the optimizer“.

Comparing statistics

In new DBMS_STATS functions have been introduced, that enable you to compare for a table from two different sources.

The statistics can be from:

  • A user statistics table and current statistics in the dictionary
  • A single user statistics table containing two sets of statistics that can be identified using statids
  • Two different user statistics tables
  • Two points in history

The appendix B of the whitepaper is has some examples. I wonder if it will also contain the DIFF_TABLE_STATS_IN_PENDING function as in 11g.

SQL Test Case Builder

Apparently the SQL Test Case Builder should be used to provide the Oracle support team of a decent test case. You can access the SQL Test Case Builder via the package DBMS_SQLDIAG and contains two procedures: EXPORT_SQL_TESTCASE and IMPORT_SQL_TESTCASE.

I hope this package is properly secured though…

The whitepaper is full of great tips and examples. I could have needed this one a year a go while trying to find unexplainable performance problems, in hind site being introduced by a bug with optimizer_secure_view_merging (causing full table scans with views build over multiple schema’s).

Cool stuff and very useful.