[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