Original: http://www.oracle.com/technetwork/issue-archive/2013/13-jan/o13asktom-1886639.html
I have a question about joining with collections and cardinality estimation. The optimizer is all estimating that 8,168 rows be coming back from my collection, and because of that, it's using in Efficient plans. The estimate of 8,168 are more than and the orders of magnitude more than the real cardinality. How can I solve this problem?
I have an estimate of the set and cardinality. The optimizer estimates the cardinality of my collection is always 8168, because of this, its teacher uses inefficient planning. 8168 is the actual base of 2 orders of magnitude!
How do I solve this problem?
This was a long-running issue with pipelined functions and collections during optimization. The optimizer in general doesn ' t has any information on the cardinality (the number of rows) being returned by the COL Lection. It has to Guess-and that guess is based on the block size (default statistics is Block-size-driven). So, for a database with an 8 K block size, the guess is about 8,000. And because it is unlikely that your collection have about 8,000 elements (probably the more like 8 or the most cases), you c An definitely arrive at a suboptimal plan.
This is indeed a long-standing question: optimization of pipeline functions and collections. The optimizer generally has no knowledge of the cardinality information of the collection. It has to be guessed! How do you guess? is based on block size.
So, for the 8k size data block database, guess the cardinality is about 8000! However, the cardinality of the collection is usually not 8000, which is more likely to be 8 or 80.
Below, I offer 4 ways to get the correct set cardinality:
1) The cardinality hint (undocumented) Cardinality hint (no file proof)
2) The opt_estimate hint (undocumented) opt_estimate hint (no documented)
3) Dynamic Sampling (Oracle Database 11g Release 1 and later) dynamically sampled (Oracle 11g first Edition or higher)
4) Oracle database ' s cardinality Feedback feature (Oracle database 11g Release 2 and later) Oracle Cardinality feedback feature (Oracle 11g version 2nd or higher)
--1) pipeline functions for string splitting
sql> Create or Replace type Str2tbltype as Table of VARCHAR2 (30)
/
Type created.
Sql> Create or replace
function Str2tbl (p_str in Varchar2, P_delim in varchar2 default ', ')
Return Str2tbltype
pipelined
As
L_str Long default P_str | | P_delim;
L_n number;
Begin
Loop
L_n: = InStr (L_str, P_delim);
Exit when (NVL (l_n,0) = 0);
Pipe row (LTrim (RTrim (substr (l_str,1,l_n-1)));
L_STR: = substr (L_str, l_n+1);
End Loop;
End
/
Function created.
sql> variable x varchar2 (15)
sql> exec:x: = ' 1,2,3,a,b,c '
PL/SQL procedure successfully completed.
Sql> select * FROM table (STR2TBL (: x));
Column_value
——————————————————————————————————————
1
2
3
A
B
C
6 rows selected.
Sql> select * FROM table (dbms_xplan.display_cursor);
Plan_table_output
————————————————————————————————————————————————————————
sql_id ddk1tv9s5pzq5, child number 0
————————————————————————————————————————————————————————
SELECT * FROM Table (STR2TBL (: x))
Plan Hash value:2407808827
———————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
———————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | | 29 (100) | |
| 1| COLLECTION ITERATOR pickler...| str2tbl|8168|16336| 29 (0) |00:00:01|
--Using cardinality tips
Sql> select * FROM table (dbms_xplan.display_cursor);
Plan_table_output
————————————————————————————————————————————————————————
sql_id BD2F8RH30Z3WW, child number 0
————————————————————————————————————————————————————————
Select/*+ Cardinality (SQ) */* FROM table (STR2TBL (: x)) sq
Plan Hash value:2407808827
———————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
———————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | | 29 (100) | |
| 1| COLLECTION ITERATOR pickler...| str2tbl| 10| 20| 29 (0) |00:00:01|
--This place, we give the optimizer a 10 hint that the result base of the pipe function is 10
--2) using opt_estimate hints
---Provide 3 parameters: object type; object name; a scaling factor, the optimizer will multiply this factor by the cardinality it thinks
So, let's figure out the approximate factor.
Sql> select 10/8168 from dual;
10/8168
————————————————
.00122429
Select/*+ opt_estimate (table, sq, scale_rows=0.00122429) */*
From table (STR2TBL (: x)) sq
Plan Hash value:2407808827
———————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
———————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | | 29 (100) | |
| 1| Ollection ITERATOR pickler...| str2tbl| 10| 20| 29 (0) |00:00:01|
--3) using dynamic sampling hints
Select/*+ dynamic_sampling (SQ, 2) */* FROM table (STR2TBL (: X, ', ')) sq
Plan Hash value:2407808827
———————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
———————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | | 11 (100) | |
| 1| COLLECTION ITERATOR pickler...| str2tbl| 6| 12| 11 (0) |00:00:01|
———————————————————————————————————————————————————————————————————————————
Note
———————
Dynamic sampling used for this statement (level=2)
--4) Use cardinality Feedback
--This place we're going to change the query shape:
with sq
As (
Select/*+ Materialize */*
From table (STR2TBL (: x))
)
SELECT *
from sq
Plan Hash value:630596523
—————————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
—————————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | | 32 (100) | |
| 1| TEMP TABLE Transformation | | | | | |
| 2| LOAD as SELECT | | | | | |
| 3| COLLECTION ITERATOR pickler...| str2tbl|8168|16336| 29 (0) |00:00:01|
| 4| VIEW | |8168| 135k| 3 (0) |00:00:01|
| 5| TABLE ACCESS Full | sys_...| 8168|16336| 3 (0) |00:00:01|
—————————————————————————————————————————————————————————————————————————————
Rows selected.
--whoop, the first time hasn't come into effect yet, okay, one more try
with sq as (select/*+ materialize */* FROM table (STR2TBL (: x))
) SELECT * from sq
Plan Hash value:630596523
—————————————————————————————————————————————————————————————————————————————
| id| Operation | Name | rows| bytes| Cost (%CPU) | Time |
—————————————————————————————————————————————————————————————————————————————
| 0| SELECT STATEMENT | | | 32 (100) | |
| 1| TEMP TABLE Transformation | | | | |
| 2| LOAD as SELECT | | | | |
| 3| COLLECTION ITERATOR pickler...| str2tbl|8168|16336| 29 (0) |00:00:01|
| 4| VIEW | 6| 102 | 3 (0) |00:00:01|
| 5| TABLE ACCESS Full | sys_...| 6| 12| 3 (0) |00:00:01|
—————————————————————————————————————————————————————————————————————————————
Note
———————
-Cardinality feedback used for this statement
Rows selected.
--this time it's OK.
--to reveal the principle of base feedback, the following direct reference to Tom's words:
Cardinality Feedback works by have the optimizer change their cardinality estimates after executing a query for the first Time and observing the actual cardinalities were very far off from the estimated cardinalities. That's, the optimizer starts to learn from its mistakes. If it executes a query and discovers that the real row counts is far off from the estimated counts, it'll reoptimize th e query, using the newly discovered values.
For base feedback please refer to: http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50asktom-165477.html
--------------------------------
Dylan presents.
How to get the correct cardinality estimate
