Wednesday, May 7, 2008

The Patch Set 3 of 10gR2 (Version 10.2.0.4) introduces a couple of interesting new and changed functionality.

10.2.0.4 supports now the "Real Application Testing" functionality (or more precisely the "Workload capture" functionality of "Database Replay") that has been introduced with Oracle 11gR1. This definitely makes sense as the most obvious application of "Real Application Testing" is testing an upgrade from 10gR2 to Oracle 11gR1 and therefore gathering your actual workload in 10gR2 and replaying it in your 11g test environment is a required functionality to be able to perform that. The new package "DBMS_WORKLOAD_CAPTURE" has been introduced in 10.2.0.4 to support the workload capturing. For more details please look here:

In addition the "Test Case Builder" (TCB) introduced in Oracle 11gR1 has also been backported to 10gR2, so that you now have the package "dbms_sqldiag" available that allows to export and import test cases.

Another feature that has been added to 10.2.0.4 are the new DIFF_TABLE_STATS* functions in the DBMS_STATS package that allow you easily to compare statistics which comes in handy if you are looking for reasons why you got different execution plans in different environments.

So apart from the fact that you need to explicitly enable the native full outer join - either at statement, session or instance level - 10.2.0.4 seems to behave exactly like 11.1.0.6.

Furthermore as shown in detail in my other post the optimizer functionality regarding the treatment of subpartition statistics has been changed significantly in 10.2.0.4, a change you should be aware of if you are using range-list subpartitioning and your subpartitions differ in size.

Another notable change in optimizer behaviour introduced in 10.2.0.4 is the treatment of non-existing values in frequency histograms when applying equality predicates. You can find more details here and in the description of the bugs 5483301 and 6082745 on MetaLink.

According to MetaLink document 555579.1 (10.2.0.4 Patch Set - Availability and Known Issues) there is another notable change regarding the usage of bind variable peeking. In 10.2.0.4 a bug is fixed that used to apply bind variable peeking when it was not supposed to happen (so e.g. even if you had set "_optim_peek_user_binds" to FALSE). This means that now there might be situations where the optimizer does no longer have bind variable value information available when determining the execution plan which could lead to execution plan changes.Although it looks like that the new density calculation option introduced in 11gR1 has also been made available in 10.2.0.4 (because the corresponding undocumented parameter is now available), setting the new undocumented parameter "_optimizer_enable_density_improvements" to true didn't have any noticeable effects in my test cases. According to the 10053 optimizer trace still the original density saved in the dictionary was used for unpopular values in case a height based histogram existed.