基数反馈（Cardinality Feedback）（一）

SQLplusDB

发布于 2020-03-26 10:06:01

7260

发布于 2020-03-26 10:06:01

概述

本文将介绍在11gR2的版本上推出了基数反馈（Cardinality Feedback 以后简称CFB）功能，通过这个特性，对于某些查询在第一次执行时，如果CBO发现根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大的情况发生时，在SQL下次执行时，会根据实际值调整基数，重新生成执行计划。

另外，基数反馈（CFB）在12c版本上得到更进一步的扩展改称为统计反馈（Statistics Feedback），成为12c自动重新优化（Automatic Reoptimization）的一部分。关于这统计反馈（Statistics Feedback）中扩展的内容和12c自动重新优化（Automatic Reoptimization）的内容将在以后的章节中进行介绍。

下面我们将通过2个例子来了解一下CFB功能。

本文是基数反馈（Cardinality Feedback）的第一部分主要介绍当基数反馈（Cardinality Feedback）无效时的状况：

例子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(*)
----------
       665

SQL> 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），因此由于预估基数不准，很有可能导致选择的执行计划不是最优的。

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        
---------- --------------------
      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文依然会利用以前的执行计划（软解析）。在这个情况下，很有可能由于最初优化器没有选择最优的执行计划，在以后的重复执行中也得不到改进而导致效率问题。

总结

本文是基数反馈（Cardinality Feedback）的第一部分，主要介绍基数反馈（Cardinality Feedback）的概述和当CFB无效时的状况例子。

将在第二部分中介绍CFB有效时的状况例子，以及CFB处理流程。

本文参与腾讯云自媒体分享计划，分享自微信公众号。

原始发表：2016-06-12，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

python

编程算法

本文分享自 Oracle数据库技术微信公众号，前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

python

编程算法

登录后参与评论

0 条评论

热度