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 10.2.0.4.0 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.
- The EXPORT_SQL_TESTCASE procedure exports the test case for your SQL statement to a directory.
- The IMPORT_SQL_TESTCASE imports the test case from a directory.
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.
😎
