Query Running Slow with Extended Statistics (Doc ID 1130574.1)

Last updated on MAY 11, 2015

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.1.0.6 [Release 11.1]
Information in this document applies to any platform.

Symptoms

In 11.1.0.6 when the extended statistics are used for the column groups, the query takes long time to complete.

CBO is calculating a very large join cardinality and resulting in a suboptimal execution plan.

The same query in 10g has a optimal execution plan.

11.1.0.6 Execution Plan
-------------------------------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | HASH JOIN | | 32 |
|* 3 | TABLE ACCESS BY INDEX ROWID | TABLE_1 | 2114 |
|* 4 | INDEX SKIP SCAN | TAB_1_IND_1 | 21137 |
| 5  | NESTED LOOPS | | 1819K|     <= Huge Join Cardinality
|* 6 | TABLE ACCESS BY INDEX ROWID| TABLE_2 | 1 |
|* 7 | INDEX RANGE SCAN | TAB_2_IND_1 | 1 |
|* 8 | INDEX RANGE SCAN | TAB_2_IND_2 | 20M|
-------------------------------------------------------------------

 

10.2.0.4 Optimal Execution Plan
--------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | NESTED LOOPS | | |
| 3 | NESTED LOOPS | | 1 |
| 4 | NESTED LOOPS | | 1 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TABLE_2 | 1 |
|* 6 | INDEX RANGE SCAN | TAB_2_IND_1 | 1 |
|* 7 | INDEX RANGE SCAN | TAB_2_IND_2 | 1 |
|* 8 | INDEX UNIQUE SCAN | TAB_1_IND_1 | 1 |
|* 9 | TABLE ACCESS BY INDEX ROWID | TABLE_1 | 1 |
-------------------------------------------------------------------
 

With the extended statistics, the join cardinality estimation goes wrong and cause the CBO to use the suboptimal execution plan.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms