How to obtain the correct base estimation and base Estimation

How to obtain the correct base estimation and base Estimation
Original article: 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 always estimating that 8,168 rows are coming back from my collection, and because of that, it is using inefficient plans. the estimate of 8,168 is more than two orders of magn?more than the real cardinality. how can I solve this problem?
I have a set and a base estimate. The optimizer always estimates the base number of my set by 8168. Because of this, the optimizer uses an inefficient plan. 8168 is the actual base of 2 orders of magnitude!
How can I solve this problem?


This is a long-running issue with pipelined functions and collections during optimization. the optimizer in general doesn't have any information about the cardinality (the number of rows) being returned by the collection. it has to guess-and that guess is based on the block size (default statistics are 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 has about 8,000 elements (probably more like 8 or 80 in most cases), you can definitely arrive at a suboptimal plan.
This is indeed a long-term question: optimization of pipeline functions and collections. The optimizer generally does not know the set base information. It must be guessed! How to guess? Is based on the block size.
Therefore, for databases with 8 K data blocks, the estimated base is about 8000! However, the base number of a set is usually not 8000, which is more likely to be 8 or 80.


Below, I provide four methods to obtain the correct set base:
1) The cardinality hint (unauthenticated ented) Base prompt (no proof of file)


2) The OPT_ESTIMATE hint (unauthenticated ented) OPT_ESTIMATE prompt (no proof of file)


3) Dynamic sampling (Oracle Database 11g Release 1 and later) Dynamic sampling (Oracle 11g first or higher)


4) Oracle Database's Cardinality Feedback feature (Oracle Database 11g Release 2 and later) Oracle base Feedback features (Oracle 11g 2nd or higher)






-- 1) pipeline function used for string segmentation
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
Rochelle STR long default p_str | p_delim;
Rochelle N number;
Begin
Loop
Rochelle 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 |


-- Use CARDINALITY prompt
SQL> select * from table (dbms_xplan.display_cursor );


PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL _ID bd2f8rh30z3ww, child number 0
--------------------------------------------------------
Select/* + cardinality (sq 10) */* 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 |


-- In this case, we will prompt the optimizer a 10 result so that it considers the result base of the pipeline function as 10.


-- 2) Prompt with OPT_ESTIMATE
-- Three parameters are provided: object type, object name, and a scaling factor. The optimizer will multiply the base value it considers as this factor.
-- So first calculate 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) Prompt with DYNAMIC SAMPLING
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
-- Here we will change the query to the following:
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 |
-----------------------------------------------------------------------------


18 rows selected.


-- Whoop: it hasn't taken effect for the first time. It doesn't matter. 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


22 rows selected.




-- This time OK
-- Reveal the principle of base feedback. The following is a direct reference to TOM:
Cardinality Feedback works by having the optimizer change its cardinality estimates after executing a query for the first time and observing that the actual cardinalities were very far off from the estimated cardinalities. that is, the optimizer starts to learn from its mistakes. if it executes a query and discovers that the real row counts are far off from the estimated counts, it will reoptimize the query, using the newly discovered values.

For base feedback, see: http://www.oracle.com/technetwork/issue-archive/2010/10-sep/o50asktom-165477.html

--------------------------------

Dylan Presents.