[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

时间: 2024-09-19 23:32:38

[20130305]Cardinality Feedback on 11gR2.txt的相关文章

[20150428]11G SPM与cardinality feedback

[20150428]11G SPM与cardinality feedback问题.txt 1.问题说明: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- --------------------------------------------------------------------------------

[20150706]11G cardinality feedback问题

[20150706]11G cardinality feedback问题.txt --今天做一个测试例子,第1次遇到cardinality feedback的问题 1.建立测试环境: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -----------------------------------------

那些语句使用cardinality feedback

[20140122]那些语句使用cardinality feedback.txt cardinality feedback是11G的新特性,昨天别人问一个问题,在11G下如何知道那些sql语句使用了cardinality feedback. 实际上这些信息保存在v$sql_plan或者DBA_HIST_SQL_PLAN的other_xml里面,里面的信息是XML格式的,对这些语句如何写我自. 己从来记不住. 简单一点: select * from v$sql_plan where other_x

oracle中Cardinality Feedback与_optimizer_use_feedback的使用建议

该参数与Cardinality Feedback特性有关,优化器可以估算基数不正确的原因有很多,如缺少的统计信息,不准确的统计数据,或复杂的谓词,基数统计反馈有助于优化器生成更合理的执行计划.对于此特性我不作科普了,比较详细的资料可以参考以下文档: 1.Tuning-by-Cardinality-Feedback.pdf 2.Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1) 该特性其实

基数反馈(Cardinality Feedback)

基数反馈(Cardinality Feedback) Cardinality Feedback Cardinality Feedback基数反馈是版本11.2(11.2.0.1及以后)中引入的关于SQL 性能优化的新特性,该特性主要针对 统计信息陈旧.无直方图或虽然有直方图但仍基数计算不准确的情况,Cardinality基数的计算直接影响到后续的JOIN COST等重要的成本计算评估,造成CBO选择不当的执行计划.以上是Cardinality Feedback特性引入的初衷. 发生情景: 在普通

[20150430]11G SPM与cardinality feedback2

[20150430]11G SPM与cardinality feedback问题2.txt 1.问题说明: SCOTT@test> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------

[20140108]12c新特性_Statistics Feedback.txt

[20140108]12c新特性_Statistics Feedback.txt 摘自:Apress.Pro.Oracle.SQL.2nd.Edition.Nov.2013.pdf Statistics feedback, known as cardinality feedback prior to Oracle 12c, is a mechanism used by the optimizer to improve automatically plans' repeated query exe

[20120104]稳定一条sql语句的执行计划.txt

[20120104]稳定一条sql语句的执行计划.txt http://www.itpub.net/thread-1495845-1-1.htmlhttp://space.itpub.net/267265/viewspace-723066 ORACLE8I升级11G R2后,查询系统视图特别慢 我的测试版本:SQL> select * from v$version where rownumBANNER------------------------------------------------

[20141014]11G长时间分析问题.txt

[20141014]11G长时间分析问题.txt http://www.itpub.net/thread-1495845-1-1.html http://space.itpub.net/267265/viewspace-723066 http://blog.itpub.net/267265/viewspace-752117/ --我以前看到这条sql语句,我做了一些简单修改. 1. 问题提出: SCOTT@test> @ver1 PORT_STRING                    VE