[20130305]Cardinality Feedback on 11gR2.txt

[20130305]Cardinality Feedback on 11gR2.txt

http://blogs.oracle.com/optimizer/entry/cardinality_feedback

Cardinality Feedback是11G的新特性,它可以自动调整执行计划.但是具体如何实现的呢?按照上面的链接的例子做一些测试看看.

1.测试环境以及问题提出:

SQL> select * from v$version where rownum

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SELECT /*+ gather_plan_statistics */

       product_name

  FROM order_items o, product_information p

 WHERE o.unit_price = 15 AND quantity > 1 AND p.product_id = o.product_id;

PLAN_TABLE_OUTPUT

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

SQL_ID  fas8yuqm8xqk9, child number 0

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

SELECT /*+ gather_plan_statistics */        product_name   FROM

order_items o, product_information p  WHERE o.unit_price = 15 AND

quantity > 1 AND p.product_id = o.product_id

Plan hash value: 1255158658

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

| Id  | Operation                    | Name                   | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT             |                        |      1 |        |     7 (100)|     13 |00:00:00.01 |      25 |

|   1 |  NESTED LOOPS                |                        |      1 |        |            |     13 |00:00:00.01 |      25 |

|   2 |   NESTED LOOPS               |                        |      1 |      4 |     7   (0)|     13 |00:00:00.01 |      12 |

|*  3 |    TABLE ACCESS FULL         | ORDER_ITEMS            |      1 |      4 |     3   (0)|     13 |00:00:00.01 |       8 |

|*  4 |    INDEX UNIQUE SCAN         | PRODUCT_INFORMATION_PK |     13 |      1 |     0   (0)|     13 |00:00:00.01 |       4 |

|   5 |   TABLE ACCESS BY INDEX ROWID| PRODUCT_INFORMATION    |     13 |      1 |     1   (0)|     13 |00:00:00.01 |      13 |

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

Predicate Information (identified by operation id):

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

   3 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))

   4 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

--看ID=5,可以发现A-ROWS=13,而估计仅仅E-Rows=1,存在很大的差异! 第2次执行,情况如何呢?

PLAN_TABLE_OUTPUT

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

SQL_ID  fas8yuqm8xqk9, child number 1

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

SELECT /*+ gather_plan_statistics */        product_name   FROM

order_items o, product_information p  WHERE o.unit_price = 15 AND

quantity > 1 AND p.product_id = o.product_id

Plan hash value: 1553478007

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

| Id  | Operation          | Name                | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT   |                     |      1 |        |     9 (100)|     13 |00:00:00.01 |      24 |       |       |          |

|*  1 |  HASH JOIN         |                     |      1 |     13 |     9  (12)|     13 |00:00:00.01 |      24 |  1452K|  1452K|  430K (0)|

|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |      1 |     13 |     3   (0)|     13 |00:00:00.01 |       7 |       |       |          |

|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |      1 |    288 |     5   (0)|    288 |00:00:00.01 |      17 |       |       |          |

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

Predicate Information (identified by operation id):

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

   2 - filter(("O"."UNIT_PRICE"=15 AND "QUANTITY">1))

Note

-----

   - cardinality feedback used for this statement

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

可以发现生成了新的子光标,执行计划发生了变化.

SQL> @ share fas8yuqm8xqk9

SQL_TEXT                       = SELECT /*+ gather_plan_statistics */     ....

AND p.product_id = o.product_id

SQL_ID                         = fas8yuqm8xqk9

ADDRESS                        = 00000000BE387280

CHILD_ADDRESS                  = 00000000BE9644A8

CHILD_NUMBER                   = 0

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

SQL_TEXT                       = SELECT /*+ gather_plan_statistics */    ....

AND p.product_id = o.product_id

SQL_ID                         = fas8yuqm8xqk9

ADDRESS                        = 00000000BE387280

CHILD_ADDRESS                  = 00000000BE50C868

CHILD_NUMBER                   = 1

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

PL/SQL procedure successfully completed.

--看不到任何原因,从v$sql_shared_cursor视图.

--从10053跟踪看看,因为按照上面的执行方式第2次执行相当于有进行了1次硬分析,跟踪文件应该有所记录.否则不能使用10053来跟踪分
--析执行计划.

2.10053分析:
SQL> alter system flush shared_pool;
System altered.

--先执行1次上述sql语句.

SQL> alter session set events '10053 trace name context forever, level 12';
--再执行上述sql语句.

SQL> alter session set events '10053 trace name context off';

3.检查跟踪文件:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=13.000000 ) OPT_ESTIMATE (INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 )
OPT_ESTIMATE (INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE (TABLE "O" ROWS=13.000000 ) */
"P"."PRODUCT_NAME" "PRODUCT_NAME" FROM "OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE "O"."UNIT_PRICE"=15
AND "O"."QUANTITY">1 AND "P"."PRODUCT_ID"="O"."PRODUCT_ID";

--最终可以发现语句有点像sql profile那样加入特定返回信息的的提示,来控制执行计划.
--如果拿跟踪的sql语句直接执行,可以发现执行计划与上述的第2次执行使用cardinality feedback的一致.

PLAN_TABLE_OUTPUT

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

SQL_ID  7cy21d5hwyzhw, child number 0

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

SELECT /*+ OPT_ESTIMATE (TABLE "P" MIN=13.000000 ) OPT_ESTIMATE

(INDEX_SCAN "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE

(INDEX_FILTER "P" "PRODUCT_INFORMATION_PK" MIN=13.000000 ) OPT_ESTIMATE

(TABLE "O" ROWS=13.000000 ) */ "P"."PRODUCT_NAME" "PRODUCT_NAME" FROM

"OE"."ORDER_ITEMS" "O","OE"."PRODUCT_INFORMATION" "P" WHERE

"O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1 AND

"P"."PRODUCT_ID"="O"."PRODUCT_ID"

Plan hash value: 1553478007

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

| Id  | Operation          | Name                | E-Rows | Cost (%CPU)|  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT   |                     |        |     9 (100)|       |       |          |

|*  1 |  HASH JOIN         |                     |     13 |     9  (12)|  1452K|  1452K|  399K (0)|

|*  2 |   TABLE ACCESS FULL| ORDER_ITEMS         |     13 |     3   (0)|       |       |          |

|   3 |   TABLE ACCESS FULL| PRODUCT_INFORMATION |    288 |     5   (0)|       |       |          |

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

Predicate Information (identified by operation id):

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

   1 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID")

   2 - filter(("O"."UNIT_PRICE"=15 AND "O"."QUANTITY">1))

Note

-----

   - Warning: basic plan statistics not available. These are only collected when:

       * hint 'gather_plan_statistics' is used for the statement or

       * parameter 'statistics_level' is set to 'ALL', at session or system level

3.总结:
cardinality feedback采用sql profile相似的东西,在估计与实际的返回行差距很大的情况下,通过提示来选择更优的执行计划.

http://blogs.oracle.com/optimizer/entry/cardinality_feedback

    In Oracle Database 11gR2, cardinality feedback monitors and feeds back the following kinds of cardinalities:

    Single table cardinality (after filter predicates are applied)
    Index cardinality (after index filters are applied)
    Cardinality produced by a group by or distinct operator