[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