Wednesday, May 7, 2008

Overview of new and changed features in 10gR2 Patch Set 3 (10.2.0.4)

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:

Updated 10gR2 documentation "Performance Tuning Guide"

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.

You can find details about the latter two features here:

The blog of the Oracle optimizer group

and here:

Whitepaper about upgrading from Oracle 9i to Oracle 10g, published February 2008!

The native hash full outer join introduced in Oracle 11gR1 has obviously been backported to 10.2.0.4, but it needs to be explicitly enabled using the following internal parameter:

_optimizer_native_full_outer_join =force

This is mentioned in the ReadMe of the 10.2.0.4 Patch set documentation. Use the DocID 316900.1 in MetaLink to access the latest version of the ReadMe document.

Here is a the result of the original 11gR1 test case applied against 10.2.04:

SQL>
SQL> drop table native_full_outer_join_test purge;

Table dropped.

Elapsed: 00:00:03.31
SQL> drop table native_full_outer_join_test2 purge;

Table dropped.

Elapsed: 00:00:00.09
SQL>
SQL> create table native_full_outer_join_test as
2 select
3 trunc(sqrt(rownum-1)) as skewed_data,
4 rownum-1 as id,
5 lpad(rownum-1,10) id_char,
6 rpad('x',50, 'x') as filler
7 from
8 all_objects;

Table created.

Elapsed: 00:00:15.49
SQL>
SQL> create table native_full_outer_join_test2 as
2 select
3 trunc(sqrt(rownum+20000-1)) as skewed_data,
4 rownum+20000-1 as id,
5 lpad(rownum+20000-1,10) id_char,
6 rpad('x',50, 'x') as filler
7 from
8 all_objects;

Table created.

Elapsed: 00:00:06.81
SQL>
SQL> begin
2 dbms_stats.gather_table_stats(ownname=>USER, tabname=>'native_full_outer_join_test');
3 dbms_stats.gather_table_stats(ownname=>USER, tabname=>'native_full_outer_join_test2');
4 end;
5 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.39
SQL>
SQL> set autotrace traceonly
SQL>
SQL> select /*+ opt_param('_optimizer_native_full_outer_join', 'force') */ a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id = b.id
4 order by a.id_char;

70079 rows selected.

Elapsed: 00:00:01.33

Execution Plan
----------------------------------------------------------
Plan hash value: 1136243049

---------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
---------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִִִִ|ִִ2063ִִִ(1)|ִ00:00:25ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִ14M|ִִ2063ִִִ(1)|ִ00:00:25ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִ|ִVW_FOJ_0ִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6210K|ִִִִִִִ|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|*ִִ3ִ|ִִִִHASHִJOINִFULLִOUTER|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ6846K|ִִ4016K|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|ִִִ4ִ|ִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ5ִ|ִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ3ִ-ִaccess("A"."ID"="B"."ID")


Statistics
----------------------------------------------------------
ִִִִִִִִִִ1ִִrecursiveִcalls
ִִִִִִִִִִ0ִִdbִblockִgets
ִִִִִִִ1059ִִconsistentִgets
ִִִִִִִִִִ0ִִphysicalִreads
ִִִִִִִִִִ0ִִredoִsize
ִִִִ2537237ִִbytesִsentִviaִSQL*Netִtoִclient
ִִִִִִ51777ִִbytesִreceivedִviaִSQL*Netִfromִclient
ִִִִִִִ4673ִִSQL*Netִroundtripsִto/fromִclient
ִִִִִִִִִִ1ִִsortsִ(memory)
ִִִִִִִִִִ0ִִsortsִ(disk)
ִִִִִִ70079ִִrowsִprocessed

SQL>
SQL> select a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id = b.id
4 order by a.id_char;

70079 rows selected.

Elapsed: 00:00:01.38

Execution Plan
----------------------------------------------------------
Plan hash value: 4036012045

----------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
----------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִִִִ|ִִ2875ִִִ(2)|ִ00:00:35ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִ20M|ִִ2875ִִִ(2)|ִ00:00:35ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ70085ִ|ִִ8692K|ִִִִִִִ|ִִִ873ִִִ(2)|ִ00:00:11ִ|
|ִִִ3ִ|ִִִִUNION_ALLִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִִִִִִ|ִִִִִִִִִִ|
|*ִִ4ִ|ִִִִִHASHִJOINִOUTERִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִ6846K|ִִ4016K|ִִִ632ִִִ(2)|ִ00:00:08ִ|
|ִִִ5ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ6ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִִ7ִ|ִִִִִHASHִJOINִRIGHTִANTI|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ20007ִ|ִִ1465K|ִִִִִִִ|ִִִ241ִִִ(3)|ִ00:00:03ִ|
|ִִִ8ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִִ244K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ9ִ|ִִִִִִTABLEִACCESSִFULLִִ|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ4ִ-ִaccess("A"."ID"="B"."ID"(+))
ִִִ7ִ-ִaccess("A"."ID"="B"."ID")


Statistics
----------------------------------------------------------
ִִִִִִִִִִ1ִִrecursiveִcalls
ִִִִִִִִִִ0ִִdbִblockִgets
ִִִִִִִ2118ִִconsistentִgets
ִִִִִִִִִִ0ִִphysicalִreads
ִִִִִִִִִִ0ִִredoִsize
ִִִִ2537569ִִbytesִsentִviaִSQL*Netִtoִclient
ִִִִִִ51777ִִbytesִreceivedִviaִSQL*Netִfromִclient
ִִִִִִִ4673ִִSQL*Netִroundtripsִto/fromִclient
ִִִִִִִִִִ1ִִsortsִ(memory)
ִִִִִִִִִִ0ִִsortsִ(disk)
ִִִִִִ70079ִִrowsִprocessed

SQL>
SQL> set autotrace traceonly explain
SQL>
SQL> select a.*, b.* from native_full_outer_join_test a
2 full outer join native_full_outer_join_test2 b
3 on a.id < b.id
4 order by a.id_char;
Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 4239385412

---------------------------------------------------------------------------------------------------------------
|ִIdִִ|ִOperationִִִִִִִִִִִִִִ|ִNameִִִִִִִִִִִִִִִִִִִִִִִִִ|ִRowsִִ|ִBytesִ|TempSpc|ִCostִ(%CPU)|ִTimeִִִִִ|
---------------------------------------------------------------------------------------------------------------
|ִִִ0ִ|ִSELECTִSTATEMENTִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִִִִ|ִִ4845Kִִ(1)|ִ16:09:04ִ|
|ִִִ1ִ|ִִSORTִORDERִBYִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִ36G|ִִ4845Kִִ(1)|ִ16:09:04ִ|
|ִִִ2ִ|ִִִVIEWִִִִִִִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ14G|ִִִִִִִ|ִִ5294ִִ(42)|ִ00:01:04ִ|
|ִִִ3ִ|ִִִִUNION_ALLִִִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִ|ִִִִִִִִִִִִ|ִִִִִִִִִִ|
|ִִִ4ִ|ִִִִִMERGEִJOINִOUTERִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִִִ125M|ִִִִ16G|ִִִִִִִ|ִִ4059ִִ(54)|ִ00:00:49ִ|
|ִִִ5ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִִ6ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִִ7ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִִ8ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|ִִִ9ִ|ִִִִִMERGEִJOINִANTIִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ47575ִ|ִִ3484K|ִִִִִִִ|ִִ1234ִִִ(2)|ִ00:00:15ִ|
|ִִ10ִ|ִִִִִִSORTִJOINִִִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50079ִ|ִִ3423K|ִִ8280K|ִִִ953ִִִ(2)|ִ00:00:12ִ|
|ִִ11ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TEST2ִ|ִ50079ִ|ִִ3423K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
|*ִ12ִ|ִִִִִִSORTִUNIQUEִִִִִִִ|ִִִִִִִִִִִִִִִִִִִִִִִִִִִִִִ|ִ50078ִ|ִִִ244K|ִִ1192K|ִִִ282ִִִ(4)|ִ00:00:04ִ|
|ִִ13ִ|ִִִִִִִTABLEִACCESSִFULL|ִNATIVE_FULL_OUTER_JOIN_TESTִִ|ִ50078ִ|ִִִ244K|ִִִִִִִ|ִִִ119ִִִ(2)|ִ00:00:02ִ|
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

ִִִ7ִ-ִaccess("A"."ID"<"B"."ID"(+))
ִִִִִִִfilter("A"."ID"<"B"."ID"(+))
ִִ12ִ-ִaccess(INTERNAL_FUNCTION("A"."ID")<INTERNAL_FUNCTION("B"."ID"))
ִִִִִִִfilter(INTERNAL_FUNCTION("A"."ID")<INTERNAL_FUNCTION("B"."ID"))

SQL>
SQL> spool off

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.

You can find the details here.

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.

You can find more details about this new 11g feature here.

3 comments:

Anonymous said...

Nice Post!. Quick question on 11g full join optimization. I'm trying to tune a query in 11.1.0.7 where it uses full join and i see that the plan uses the native full join optimization but it slows down in a pagination query. for e.g. our query looks like
SELECT * FROM ( SELECT tmp.*, rownum rnum FROM ( SELECT c.col1, d.col2
FROM tabled d FULL JOIN tablec c ON d.colx = c.colx ORDER BY d.col2 ASC ) tmp
WHERE rownum <= 50) WHERE rnum >= 1

Though the d.col2 is a not null column which is indexed, they don't get used cause of full join as they are forced to look up data than index. If i remove the sort, query returns in sub seconds. Any optimization to workaround this issue.

Randolf said...

Interesting question.

I'm not sure what your last paragraph

"Though the d.col2 is a not null column which is indexed, they don't get used cause of full join as they are forced to look up data than index"

is supposed to mean.

I think the optimizer here decides that it can't use the index on D.COL2, because due to the full outer join D.COL2 can be blank for all rows from TABLEC that don't have a match in TABLED.

You can verify that by changing the full outer join to an INNER JOIN or a LEFT OUTER JOIN. This should change the plan and use the index on D.COL2.

You should add an index on C.COLX and D.COLX to support an efficient join in this particular case for these potential workarounds:

- What do you get if you disable the native full outer join?

SELECT /*+ first_rows(50) opt_param('_optimizer_native_full_outer_join', 'off') */ * FROM ( SELECT tmp.*
...

- Rewrite the statement manually to the pre-native full outer join transformation:

SELECT /*+ first_rows(50) */ * FROM ( SELECT tmp.*, rownum rnum FROM (
SELECT c.col1 as col1, d.col2 as col2
FROM tabled d LEFT OUTER JOIN tablec c ON d.colx = c.colx
UNION ALL
SELECT c.col1, null as col2
FROM tablec c WHERE NOT EXISTS (
SELECT null
FROM tabled d
where d.colx = c.colx
)
ORDER BY col2 ASC ) tmp
WHERE rownum <= 50) WHERE rnum >= 1

It looks like due to the outer pagination query (...where rnum >= 1) in combination with the UNION ALL Oracle loses the FIRST_ROWS_n optimization, therefore I've added an explicit FIRST_ROWS(n) hint.

If you run only the inner query (...where rownum <= 50) or run only the first part of the UNION ALL then the FIRST_ROWS(n) hint might not be required.

Randolf

sap support packs said...

Each version comes up with some new interesting features and utilities that makes things even more simpler. I do like the new features that are added, both of which you have explained in the blog using proper examples. Thanks for providing the details.