【Oracle 优化器】基数反馈 (Cardinality Feedback)功能
来源:互联网 发布:ubuntu mono 编辑:程序博客网 时间:2024/05/18 08:34
概述
在Oracle 11gR2的版本上推出了基数反馈(Cardinality Feedback 以后简称CFB)功能,通过这个特性,对于某些查询在第一次执行时,如果CBO发现根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大的情况发生时,在SQL下次执行时,会根据实际值调整基数,重新生成执行计划。
另外,基数反馈 (CFB)在12c版本上得到更进一步的扩展改称为统计反馈(Statistics Feedback),成为12c自动重新优化(Automatic Reoptimization)的一部分。
关于这统计反馈(Statistics Feedback)中扩展的内容和12c自动重新优化(Automatic Reoptimization)的内容,将在以后的章节中进行介绍。
下面我们将通过几个例子来了解一下CFB功能。
例子1(CFB无效)
首先我们在10.2.0.5的环境中也就是CFB无效的情况下,看看执行的情况:
(我们使用了Oracle数据库提供的样例Schema OE 及其表PRODUCT_INFORMATION和ORDER_ITEMS进行测试。)
1.首先确认相关表的统计信息和表的数据量。(基于10.2.0.5版本测试)
--统计信息能够反映出表中的数据量。SQL> select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables where TABLE_NAME in ('PRODUCT_INFORMATION','ORDER_ITEMS');TABLE_NAME NUM_ROWS BLOCKS -------------------- ---------- ---------- ORDER_ITEMS 665 5 PRODUCT_INFORMATION 288 13 SQL> select count(*) from ORDER_ITEMS; COUNT(*)---------- 665SQL> select count(*) from PRODUCT_INFORMATION; COUNT(*)---------- 288
2.设定环境参数statistics_level为ALL,以便能够通过dbms_xplan.display_cursor函数查看SQL文根据统计信息估算出的访问数据行数和SQL执行时的实际值。
SQL> alter session set statistics_level=all;Session altered.
3.第一次执行SQL文
SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2354 Sound Card STD ... 2457 Graphics - DIK+ 269 rows selected.
4.查看第一次执行后的执行计划。
SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL,'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 0-------------------------------------SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 1906736282 ---------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers | Reads |---------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.44 | 9189 | 20 || 1 | NESTED LOOPS | | 1 | 1 | 00:00:01 | 269 |00:00:00.44 | 9189 | 20 || 2 | MERGE JOIN CARTESIAN| | 1 | 4 | 00:00:01 | 9135 |00:00:00.17 | 35 | 15 ||* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 1 ★| 00:00:01 | 87 ★|00:00:00.01 | 34 | 14 || 4 | BUFFER SORT | | 87 | 105 | 00:00:01 | 9135 |00:00:00.07 | 1 | 1 || 5 | INDEX FULL SCAN | ORDER_PK | 1 | 105 | 00:00:01 | 105 |00:00:00.01 | 1 | 1 ||* 6 | INDEX UNIQUE SCAN | ORDER_ITEMS_UK | 9135 | 1 | | 269 |00:00:00.18 | 9154 | 5 |---------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID") 26 rows selected.SQL> ---查看V$SQL的统计信息。SQL> select sql_id,child_number, executions, buffer_gets,plan_hash_value 2 from v$sql 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE------------- ------------ ---------- ----------- ---------------bmh5hb8331u33 0 1 9701 1906736282
我们发现由于访问条件(“MIN_PRICE”<40 AND “LIST_PRICE”<50)的影响,优化器认为PRODUCT_INFORMATION表的预估行数(E-Rows)为1,优化器基于预估基数在选择表PRODUCT_INFORMATION和ORDER_ITEMS结合的最优执行计划时,选择了MERGE JOIN CARTESIAN的结合方式。
但实际实际访问行数(A-Time:87),因此由于预估基数不准,很有可能导致选择的执行计划不是最优的。
5.我们再多次执行相同的SQL文。
---第二次执行SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2354 Sound Card STD ... 2457 Graphics - DIK+ 269 rows selected.SQL> ---第二次执行的执行计划SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL,'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 0-------------------------------------SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 1906736282 ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.45 | 9189 || 1 | NESTED LOOPS | | 1 | 1 | 00:00:01 | 269 |00:00:00.45 | 9189 || 2 | MERGE JOIN CARTESIAN| | 1 | 4 | 00:00:01 | 9135 |00:00:00.17 | 35 ||* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 1 | 00:00:01 | 87 |00:00:00.01 | 34 || 4 | BUFFER SORT | | 87 | 105 | 00:00:01 | 9135 |00:00:00.06 | 1 || 5 | INDEX FULL SCAN | ORDER_PK | 1 | 105 | 00:00:01 | 105 |00:00:00.01 | 1 ||* 6 | INDEX UNIQUE SCAN | ORDER_ITEMS_UK | 9135 | 1 | | 269 |00:00:00.18 | 9154 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID") 26 rows selected.---第三次执行的执行计划 SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2354 Sound Card STD ... 2457 Graphics - DIK+ 269 rows selected.--第三次执行的执行计划 SQL> select * from table(dbms_xplan.display_cursor(NULL, NULL,'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 0-------------------------------------SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 1906736282 ------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers |------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.45 | 9189 || 1 | NESTED LOOPS | | 1 | 1 | 00:00:01 | 269 |00:00:00.45 | 9189 || 2 | MERGE JOIN CARTESIAN| | 1 | 4 | 00:00:01 | 9135 |00:00:00.16 | 35 ||* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 1 | 00:00:01 | 87 |00:00:00.01 | 34 || 4 | BUFFER SORT | | 87 | 105 | 00:00:01 | 9135 |00:00:00.07 | 1 || 5 | INDEX FULL SCAN | ORDER_PK | 1 | 105 | 00:00:01 | 105 |00:00:00.01 | 1 ||* 6 | INDEX UNIQUE SCAN | ORDER_ITEMS_UK | 9135 | 1 | | 269 |00:00:00.19 | 9154 |------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID") 26 rows selected.
我们发现,虽然根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值不同,但是以后的执行过程中,SQL文依然会利用以前的执行计划(软解析)。
在这个情况下,很有可能由于最初优化器没有选择最优的执行计划,在以后的重复执行中也得不到改进而导致效率问题。
例子2(CFB有效)
下面我们在11.2.0.4的环境中也就是CFB有效的情况下,看看执行的情况:
(我们依然使用Oracle数据库提供的样例Schema OE 及其表PRODUCT_INFORMATION和ORDER_ITEMS进行测试。)
1.首先确认相关表的统计信息和表的数据量。(基于11.2.0.4版本测试)
--统计信息能够反映出表中的数据量。 SQL> select TABLE_NAME,NUM_ROWS,BLOCKS from user_tables where TABLE_NAME in ('PRODUCT_INFORMATION','ORDER_ITEMS');TABLE_NAME NUM_ROWS BLOCKS -------------------- ---------- ---------- ORDER_ITEMS 665 5 PRODUCT_INFORMATION 288 13 Elapsed: 00:00:00.04SQL> select count(*) from ORDER_ITEMS; COUNT(*)---------- 665Elapsed: 00:00:00.02SQL> select count(*) from PRODUCT_INFORMATION; COUNT(*)---------- 288Elapsed: 00:00:00.01SQL>
2.设定环境参数statistics_level为ALL,以便能够通过dbms_xplan.display_cursor函数查看SQL文根据统计信息估算出的访问数据行数和SQL执行时的实际值。
SQL> alter session set statistics_level=all;Session altered.Elapsed: 00:00:00.01
3.第一次执行SQL文
SQL> SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2403 Battery - EL ... 2450 Plastic Stock - W/HD 269 rows selected.Elapsed: 00:00:00.22SQL>
4.查看第一次执行后的执行计划。
SQL> select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 0 ------------------------------------- SELECT o.order_id, v.product_name FROM orders o, ( SELECT order_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 1906736282 --------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.17 | 1337 | 20 | | 1 | NESTED LOOPS | | 1 | 1 | 00:00:01 | 269 |00:00:00.17 | 1337 | 20 | | 2 | MERGE JOIN CARTESIAN| | 1 | 4 | 00:00:01 | 9135 |00:00:00.06 | 33 | 15 | |* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | ★ 1 | 00:00:01 | ★ 87 |00:00:00.01 | 32 | 14 | | 4 | BUFFER SORT | | 87 | 105 | 00:00:01 | 9135 |00:00:00.02 | 1 | 1 | | 5 | INDEX FULL SCAN | ORDER_PK | 1 | 105 | 00:00:01 | 105 |00:00:00.01 | 1 | 1 | |* 6 | INDEX UNIQUE SCAN | ORDER_ITEMS_UK | 9135 | 1 | | 269 |00:00:00.05 | 1304 | 5 | --------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 6 - access("O"."ORDER_ID"="ORDER_ID" AND "P"."PRODUCT_ID"="O"."PRODUCT_ID") 28 rows selected.Elapsed: 00:00:00.19
我们发现和10.2.0.5环境一样,由于访问条件(“MIN_PRICE”<40 AND “LIST_PRICE”<50)的影响,优化器认为PRODUCT_INFORMATION表的预估行数(E-Rows)为1,优化器基于预估基数在选择表PRODUCT_INFORMATION和ORDER_ITEMS结合的最优执行计划时,选择了MERGE JOIN CARTESIAN的结合方式。
5.查看动态视图V
SQL> ---sql_id:bmh5hb8331u33SQL> select sql_id,child_number, executions, buffer_gets,plan_hash_value 2 from v$sql 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE------------- ------------ ---------- ----------- ---------------bmh5hb8331u33 0 1 1604 1906736282Elapsed: 00:00:00.01SQL> SQL> select sql_id, child_number, USE_FEEDBACK_STATS 2 from V$SQL_SHARED_CURSOR 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER U ------------- ------------ - bmh5hb8331u33 0 Y Elapsed: 00:00:00.04SQL>
我们发现V$SQL_SHARED_CURSOR的USE_FEEDBACK_STATS列标记为Y。
(USE_FEEDBACK_STATS列是在11.2.0.4 的版本上新追加的列,用于标示当根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大时,下次执行时重新生成执行计划)
6.我们再次次执行相同的SQL文
---第二次执行SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2403 Battery - EL ... 2401 SPNIX3.3 AU 269 rows selected.Elapsed: 00:00:00.03★SQL>
我们发现执行时间变短了。
7.再次查看执行计划
SQL> select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 1 ------------------------------------- SELECT o.order_id, v.product_name FROM orders o, ( SELECTorder_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHEREo.order_id = v.order_id Plan hash value: 35479787 ---------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers | Reads | ---------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.01 | 61 | 1 | | 1 | NESTED LOOPS | | 1 | 313 | 00:00:01 | 269 |00:00:00.01 | 61 | 1 | |* 2 | HASH JOIN | | 1 | 313 | 00:00:01 | 269 |00:00:00.01 | 40 | 1 | |* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | ★87 | 00:00:01 | ★87 |00:00:00.01 | 15 | 0 | | 4 | INDEX FAST FULL SCAN| ORDER_ITEMS_UK | 1 | 665 | 00:00:01 | 665 |00:00:00.01 | 25 | 1 | |* 5 | INDEX UNIQUE SCAN | ORDER_PK | 269 | 1 | | 269 |00:00:00.01 | 21 | 0 | ---------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 5 - access("O"."ORDER_ID"="ORDER_ID") Note ----- - cardinality feedback used for this statement ★ 32 rows selected.Elapsed: 00:00:00.03
我们发现SQL文进行了硬解析,并且表PRODUCT_INFORMATION的预估信息(E-Rows)调整为第一次执行时收集的实际值(87),用于优化器选择执行计划。因此,优化器基于调整后预估基数在选择表PRODUCT_INFORMATION和ORDER_ITEMS结合的最优执行计划时,选择了HASH JOIN的结合方式,从而更有效的执行了SQL文。
8.再次查看动态视图V
SQL> ---sql_id:bmh5hb8331u33SQL> select sql_id,child_number, executions, buffer_gets,plan_hash_value,is_shareable 2 from v$sql 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE I------------- ------------ ---------- ----------- --------------- -bmh5hb8331u33 0 1 1604 1906736282 N★bmh5hb8331u33 1 1 61 35479787 Y★Elapsed: 00:00:00.02SQL> SQL> select sql_id, child_number, USE_FEEDBACK_STATS 2 from V$SQL_SHARED_CURSOR 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER U------------- ------------ -bmh5hb8331u33 0 Ybmh5hb8331u33 1 NElapsed: 00:00:00.00
通过视图V$SQL我们发现,新生成的游标CHILD#1比以前的游标CHILD#1会使用更少的BUFFER_GETS,效率更高。并且以前游标CHILD#0的is_shareable列标记为N,不在被共享。
新生成的游标CHILD#1的is_shareable列标记为Y,供以后的执行重用。
9.再多次执行SQL文
--第三次执行SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2403 Battery - EL ... 2401 SPNIX3.3 AU 269 rows selected.Elapsed: 00:00:00.06SQL> --查看执行计划SQL> set line 200SQL> set pagesize 9999SQL> SQL> select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 1 ------------------------------------- SELECT o.order_id, v.product_name FROM orders o, ( SELECTorder_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 35479787 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.03 | 61 | | 1 | NESTED LOOPS | | 1 | 313 | 00:00:01 | 269 |00:00:00.03 | 61 | |* 2 | HASH JOIN | | 1 | 313 | 00:00:01 | 269 |00:00:00.02 | 40 | |* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 87 | 00:00:01 | 87 |00:00:00.01 | 15 | | 4 | INDEX FAST FULL SCAN| ORDER_ITEMS_UK | 1 | 665 | 00:00:01 | 665 |00:00:00.01 | 25 | |* 5 | INDEX UNIQUE SCAN | ORDER_PK | 269 | 1 | | 269 |00:00:00.01 | 21 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 5 - access("O"."ORDER_ID"="ORDER_ID") Note ----- - cardinality feedback used for this statement 32 rows selected.Elapsed: 00:00:00.07--查看动态视图SQL> ---sql_id:bmh5hb8331u33SQL> select sql_id,child_number, executions, buffer_gets,plan_hash_value,is_shareable 2 from v$sql 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE I------------- ------------ ---------- ----------- --------------- -bmh5hb8331u33 0 1 1604 1906736282 Nbmh5hb8331u33 1 2 122 35479787 YElapsed: 00:00:00.00SQL> SQL> select sql_id, child_number, USE_FEEDBACK_STATS 2 from V$SQL_SHARED_CURSOR 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER U------------- ------------ -bmh5hb8331u33 0 Ybmh5hb8331u33 1 NElapsed: 00:00:00.00--第四次执行SQL> SELECT o.order_id, v.product_name 2 FROM orders o, 3 ( SELECT order_id, product_name 4 FROM order_items o, product_information p 5 WHERE p.product_id = o.product_id 6 AND list_price < 50 7 AND min_price < 40 ) v 8 WHERE o.order_id = v.order_id 9 ; ORDER_ID PRODUCT_NAME ---------- -------------------- 2403 Battery - EL ... 2401 SPNIX3.3 AU 269 rows selected.Elapsed: 00:00:00.05SQL> --查看执行计划SQL> set line 200SQL> set pagesize 9999SQL> SQL> select * from table(dbms_xplan.display_cursor(format=>'typical iostats last -cost -bytes'));PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------SQL_ID bmh5hb8331u33, child number 1 ------------------------------------- SELECT o.order_id, v.product_name FROM orders o, ( SELECTorder_id, product_name FROM order_items o, product_information p WHERE p.product_id = o.product_id AND list_price < 50 AND min_price < 40 ) v WHERE o.order_id = v.order_id Plan hash value: 35479787 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | E-Time | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | | 269 |00:00:00.02 | 61 | | 1 | NESTED LOOPS | | 1 | 313 | 00:00:01 | 269 |00:00:00.02 | 61 | |* 2 | HASH JOIN | | 1 | 313 | 00:00:01 | 269 |00:00:00.01 | 40 | |* 3 | TABLE ACCESS FULL | PRODUCT_INFORMATION | 1 | 87 | 00:00:01 | 87 |00:00:00.01 | 15 | | 4 | INDEX FAST FULL SCAN| ORDER_ITEMS_UK | 1 | 665 | 00:00:01 | 665 |00:00:00.01 | 25 | |* 5 | INDEX UNIQUE SCAN | ORDER_PK | 269 | 1 | | 269 |00:00:00.01 | 21 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("P"."PRODUCT_ID"="O"."PRODUCT_ID") 3 - filter(("MIN_PRICE"<40 AND "LIST_PRICE"<50)) 5 - access("O"."ORDER_ID"="ORDER_ID") Note ----- - cardinality feedback used for this statement 32 rows selected.Elapsed: 00:00:00.02SQL> --查看动态视图SQL> SQL> ---sql_id:bmh5hb8331u33SQL> select sql_id,child_number, executions, buffer_gets,plan_hash_value,is_shareable 2 from v$sql 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER EXECUTIONS BUFFER_GETS PLAN_HASH_VALUE I------------- ------------ ---------- ----------- --------------- -bmh5hb8331u33 0 1 1604 1906736282 Nbmh5hb8331u33 1 3 183 35479787 YElapsed: 00:00:00.00SQL> SQL> select sql_id, child_number, USE_FEEDBACK_STATS 2 from V$SQL_SHARED_CURSOR 3 where sql_id = 'bmh5hb8331u33';SQL_ID CHILD_NUMBER U------------- ------------ -bmh5hb8331u33 0 Ybmh5hb8331u33 1 NElapsed: 00:00:00.00
我们发现以后的执行都会变成软解析,使用第二次产生的执行计划。
通过CFB功能使优化器能够在以后的执行中选择更优的执行计划,从得到更好的执行效率。
CFB的处理流程
下面通过以下流程图来总体的回顾一下CFB的处理过程。
在下列情况CBO可能无法估算出准确的Cardinality,Oracle会启用CFB功能:
・没有收集表的统计信息,并且dynamic sampling 也没有开启; ・ 一个表的查询条件涉及多列,但却没有收集扩展的统计信息(extended statistics) ・ 查询条件复杂(比如条件有函数)
针对上述情况,Oracle会采取如下的CFB流程处理:
1. SQL文第一次执行时,Oracle会监控操作的实际行数(A-Row),然后对比CBO估算的行数(E-Row)。2. 如果两个值相差很大,就记录实际行数(A-Row),做上标记。 下次执行时再次进行硬解析,根据实际行数来重新生成执行计划。3. 如果两个值相差不大,CBO就不再监控这条SQL语句。
针对版本12c的一些情况我们将在以后的章节中进行介绍。
版权声明:本文为博主原创文章,转载请注明出处,谢谢。http://blog.csdn.net/lukeunique
- 【Oracle 优化器】基数反馈 (Cardinality Feedback)功能
- 11gR2新特性,Cardinality Feedback基数反馈
- Oracle Database Cardinality Feedback
- oracle的cardinality feedback
- Oracle Optimizer Cardinality Feedback
- Oracle 执行计划(2)-基数 cardinality
- ORACLE 11gR2 Cardinality Feedback 的优化案例
- cbo优化中的基数(cardinality)介绍
- cbo优化中的基数(cardinality)介绍
- oracle 查询计划中的基数cardinality概念
- 关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)
- 反馈(Feedback)
- Oracle 执行计划(2)-基数 cardinality【即估算一个操作返回的行数】
- Touch Feedback(触摸反馈):水波纹
- 基数(cardinality)排序算法
- 传输反馈 transfrom feedback
- oracle 11201 基数反馈导致主机cpu 开销过高处理
- 关于mysql 索引自动优化机制: 索引选择性(Cardinality:索引基数)
- windows编程,服务器与客户端
- iOS 动画
- SICP 练习2.40 unique-pairs
- 1.逐帧动画shader
- MongoDB - 连接
- 【Oracle 优化器】基数反馈 (Cardinality Feedback)功能
- @ResponseBody返回JSON数据时遇到406错误
- 逻辑回归
- TCP与UDP协议
- golang执行时不显示DOS窗口
- 安卓日记——跨组件通信神器RxBus
- java集成开发环境eclipse/MyEclipse
- leetcode 31. Next Permutation
- C++经典书籍推荐