Hint: Cardinality

This hint specifies the estimated cardinality returned by a query or portions of the query.
Note if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:
SELECT /*+ CARDINALITY ( [tablename] card ) */

In my observations, many of bad execution plans due to the stale or inappropriate statistics. Using dbms_stats, most of the sql can be tune by gathering the updated statistics(num_rows, num_distinct, num_nulls, density, histogram) or manually set the statistics, to relect the data distribution from application view.

Occassionally, however, there is no way for the optimizer to figure out the correct cardinality, for example corelation-predicate, cardinality of join result, pipeline function. In such case, the cardinality may come into play. In this blog, I’ll present a basics example how to spefify the cardinality hint for the pipeline table and subquery, to order to produced the execution we want.

The table CUT have around 240k rows, while table V and S have over 1 million rows. The current exeuction plan full table scan CUT first, as the driving table, then filter loop the exist subquery. It take 1.5m CR to delete 223 rows, thus another bad execution plan. You may wonder why TABLE_V disappears in the execution plan? :-)

--the sql workload
sql> @sql_hist 9hy2t55wxmyma

         SQL_ID       PLAN EXEC_DELTA   CR_DELTA ROWS_DELTA elapse(ms)    cr/exec  rows/exec elapse/exec(ms)    cr/rows
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- --------------- ----------
9hy2t55wxmyma   1496490203         42   62712015       9383     625255    1493143        223        14887024       6683

--the example sql
DELETE 
FROM    TABLE_A CUT
WHERE    EXISTS
      (
            SELECT  1
            FROM    TABLE_V V,
                    TABLE_V_S S
            WHERE   V.KEY1   = S.KEY1
                    AND S.KEY2 = CUT.KEY
                    AND V.KEY1 IN
                    (
                            SELECT * FROM  
            TABLE(CAST(:B1 AS  TYPE_NUMBER)) R
                    )
      )
/

--current execution plan
---------------------------------------------------------------------
| Id  | Operation                            | Name         | Rows  |
---------------------------------------------------------------------
|   0 | DELETE STATEMENT                     |              |     1 |
|   1 |  DELETE                              | TABLE_A      |       |
|*  2 |   FILTER                             |              |       |
|   3 |    TABLE ACCESS FULL                 | TABLE_A      |   239K|
|   4 |    NESTED LOOPS                      |              |     1 |
|   5 |     TABLE ACCESS BY INDEX ROWID      | TABLE_V_S    |     1 |
|*  6 |      INDEX UNIQUE SCAN               | TABLE_V_S_PK |     1 |
|*  7 |     COLLECTION ITERATOR PICKLER FETCH|              |     1 |
---------------------------------------------------------------------

For this Case, it’ll be better to unnest the sub query, and select the pipeline function as driving table. Nested loop will more efficient than the hash join. The problem is how to provide the cardinality info to optimizer, where the cardinality comes into play. Simply add the cardinality hint in subquery and the optimizer choose the execution plan as exeptected.

Be noted that the cardinality of the pipeline table and the merged view VW_SQ_1 is 1, just as the hint specify.

--sql added cardinality hint
explain plan for 
DELETE 
FROM    TABLE_A CUT
WHERE    EXISTS
    (
       SELECT  /*+ unnest cardinality(1)*/1
       FROM    TABLE_V V,
               TABLE_V_S S
       WHERE   V.KEY1   = S.KEY1
               AND S.KEY2 = CUT.KEY
               AND V.KEY1     IN
               (
                       SELECT /*+ cardinality(r 1)*/* 
					   FROM  TABLE(CAST(:B1 AS  TYPE_NUMBER)) R
               )
    )
/
--expected execution plan.
-------------------------------------------------------------------------
| Id  | Operation                                | Name         | Rows  |
-------------------------------------------------------------------------
|   0 | DELETE STATEMENT                         |              |     1 |
|   1 |  DELETE                                  | TABLE_A      |       |
|   2 |   NESTED LOOPS                           |              |       |
|   3 |    NESTED LOOPS                          |              |     1 |
|   4 |     VIEW                                 | VW_SQ_1      |     1 |
|   5 |      SORT UNIQUE                         |              |     1 |
|   6 |       NESTED LOOPS                       |              |       |
|   7 |        NESTED LOOPS                      |              |     9 |
|   8 |         COLLECTION ITERATOR PICKLER FETCH|              |     1 |
|*  9 |         INDEX RANGE SCAN                 | TABLE_V_S_I2 |     9 |
|  10 |        TABLE ACCESS BY INDEX ROWID       | TABLE_V_S    |     9 |
|* 11 |     INDEX RANGE SCAN                     | TABLE_A_I1   |     1 |
|  12 |    TABLE ACCESS BY INDEX ROWID           | TABLE_A      |     1 |
-------------------------------------------------------------------------

This is a peice of example that how the hint cardinality can be applied. There are other scenario that Cardinality may come into play, and also noted that there are other possible solutions

Data Skew: Gather approriate histgram, adaptive cursor sharing in 11g, or disable the bind variable for different data charateristics sql, or apply hint cardinality.
Out-of-range data: gather approriate statistics, apply cardinality.
The cardinality of join: hint dynamic_sampling, opt_estimate, cardinality;
Corelated predicates: extended statistics in 11g, or hint dynamic_sampling and cardinality.
Pipeline funciton: hint cardinality

I’ll post for other sql tuning scenarios. If you want get an overview of sql tuning, i’ll recommend a blog from Greg Rahn.

发表评论

电子邮件地址不会被公开。 必填项已用*标注

您可以使用这些HTML标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>