【Oracle 优化器】基数反馈 （Cardinality Feedback）功能

已于 2023-12-31 09:41:35 修改
阅读量8.7k 收藏 2
点赞数 1
分类专栏： Oracle知识解析和案例分享 文章标签： oracle CFB Optimizer sql tuning
于 2016-06-29 19:48:39 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lukeUnique/article/details/51785460
版权

##概述

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

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.04
SQL> select count(*) from ORDER_ITEMS;

  COUNT(*)
----------
       665

Elapsed: 00:00:00.02
SQL> select count(*) from PRODUCT_INFORMATION;

  COUNT(*)
----------
       288

Elapsed: 00:00:00.01
SQL>

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.22
SQL>

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 S Q L 和 V SQL和V SQL和VSQL_SHARED_CURSOR

SQL> ---sql_id:bmh5hb8331u33
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        1604      1906736282

Elapsed: 00:00:00.01
SQL> 
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.04
SQL>

我们发现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,        ( 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: 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 S Q L 和 V SQL和V SQL和VSQL_SHARED_CURSOR

SQL> ---sql_id:bmh5hb8331u33
SQL> 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.02
SQL> 
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
bmh5hb8331u33            1 N

Elapsed: 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.06
SQL> 

--查看执行计划

SQL> set line 200
SQL> set pagesize 9999
SQL> 
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,        ( 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: 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:bmh5hb8331u33
SQL> 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          2         122        35479787 Y

Elapsed: 00:00:00.00
SQL> 
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
bmh5hb8331u33            1 N

Elapsed: 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.05
SQL> 
 --查看执行计划
SQL> set line 200
SQL> set pagesize 9999
SQL> 
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,        ( 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: 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.02
SQL> 
--查看动态视图
SQL> 
SQL> ---sql_id:bmh5hb8331u33
SQL> 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          3         183        35479787 Y

Elapsed: 00:00:00.00
SQL> 
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
bmh5hb8331u33            1 N

Elapsed: 00:00:00.00

我们发现以后的执行都会变成软解析，使用第二次产生的执行计划。
通过CFB功能使优化器能够在以后的执行中选择更优的执行计划，从得到更好的执行效率。

##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

SQLplusDB
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
Oracle优化器 Cardinality基数与Selectivity选择性
12-14
优化器（optimizer）是oracle数据库内置的一个核心子系统。优化器的目的是按照一定的判断原则来得到它认为的目标SQL在当前的情形下的最高效的执行路径，也就是为了得到目标SQL的最佳执行计划。依据所选择执行计划时所用的判断原则，oracle数据库里的优化器又分为RBO和CBO两种。 一、基于规则的优化器。《RBO: Rule-Based Optimization》      Oracle会在代码里事先为各种类型的执行路径定一个等级，一共15个等级，从等级1到等级15，oracle认为等级1的执行路径是效率最高的，等级15是执行效率最差的。对于等级相同的执行计划，oracle根据目标对象
oracle 11g 基数反馈造成同一sql执行产生不同的执行计划
10-17 369
ardinality Feedback基数反馈是版本11.2中引入的关于SQL 性能优化的新特性，该特性主要针对 统计信息陈旧、无直方图或虽然有直方图但仍基数计算不准确的情况， Cardinality基数的计算直接影响到后续的J...
High Version Count(高版本游标)数目过多诊断的方法
weixin_34409822的博客
05-31 714
High Version Count(高版本游标)数目过多诊断的方法 什么是high version cursor(高版本游标)? 对于一个特定的游标有多少个版本就属于高版本游标是没有明确定义的.对于不同的系统有不同的数量界定.然而在awr报告中对于一个父游标超过20个子游标个数时就会被报告出...
oracle 2个以上游标,诊断Oracle high version count(高版本游标)问题
weixin_39828198的博客
04-03 353
为了查看子游标:对于Oracle 9.2.x.x及以下版本SQL>select * from v$sql_shared_cursor where kglhdpar = '0000000386BC2E58';对于oracle 10.0.x.x及以上版本SQL> select * from v$sql_shared_cursor where address = '0000000386BC2...
Oracle 11gR2 新技术 Cardinality Feedback
Hehuyi_In的博客
12-26 2168
有很多地方可以设置定时任务，比如：Windows的计划任务，Linux下的crontab，各种开发工具里的timer组件。SQL Server也有它的定时任务组件 SQL Server Agent，基于它可以方便的部署各种数据库相关的作业(job)。 一、作业历史记录 作业的历史记录按时间采用FIFO原则，当累积的作业历史记录达到上限时，就会删除最老的记录。 1. 作业历史记录数配置 所有作业总计记录条数默认为1000，最多为999999条。单个作业总计记录条数默认为100，最多为99999..
Oracle 执行计划（2）-基数 cardinality【即估算一个操作返回的行数】
haiross的专栏
12-10 9301
Oracle 执行计划（2）-基数 cardinality 执行计划的cardinality列。基数是说从数据表，结果集或者索引返回多少行数据。 基数=表行数据*选择率。所以讲基数重点是讲选择率，选择率的计算！ 版本10G 默认 表空间信息： TABLESPACE_NAME USERS BLOCK_SIZE 8192
oracle cardinality简析
u011165335的博客
08-19 3794
此hint的作用 向优化器提供对某个查询语句的整体或部分的预测基数值，并通过参考该基数值来为查询语句制定执行计划。如果在该提示中没有指定表的名称，则该基数值将被视为从该查询语句所获得的最终结果行数。 针对单个目标表而言: 生效路径: 全表扫; 索引范围扫,索引全扫描fs,索快速扫描ffs create index emp_ename_idx on emp(ename); --全表扫...
Oracle数据库11G新特性-Cardinality Feedback
oradbm的博客
06-29 598
ORALCE11G新特性之Cardinality Feedback 概述 在Oracle11gR2的版本上推出了基数反馈（Cardinality Feedback 以后简称CFB）功能，通过这个特性，对于某些查询在第一次执行时，如果CBO发现根据统计信息估算出的基数(Computed cardinality)和SQL执行时的实际值差距很大的情况发生时，在SQL下次执行时，会根据实际值调整基数，重新生成执行计划。 Cardinality Feedback基数反馈是版本11.2（11.2.0.1及以后）中.
oracle执行计划中的基数,oracle执行计划基数(cardinality)计算方式
weixin_32432759的博客
04-03 886
num_rows: dba_tables.num_rowsnum_nulls: dba_tab_cols.num_nullsnum_distinct:dba_tab_cols.num_distinctCard:cardinality基数oracle执行计划基数(cardinality)计算方式1.单列无直方图计算方式Card=(1/num_distinct)*(num_rows-num_nulls...
基数反馈（Cardinality Feedback）
coco3600的博客
06-02 389
基数反馈（Cardinality Feedback） Cardinality Feedback Cardinality Feedback基数反馈是版本11.2（11.2.0...
让Oracle跑得更快 Oracle 10g性能分析与优化思路.part2.rar
12-05
5.1 cardinality (基数) 85 5.2 sql的执行计划 94 第6章 hint 109 6.1 和优化器相关的hint 115 6.1.1 all_rows和first_rows(cbo) 115 6.1.2 rule hint 117 6.2 访问路径相关的hint 117 6.2.1 full hint 118 6.2.2 ...
浅析MySQL的基数统计
01-19
一、基数是啥？ Cardinality指的就是MySQL表中某一列的不同值的数量。 如果这一类是唯一索引，那基数 = 行数。 如果这一列是sex，枚举类型只有男女，那它是基数就是2 Cardinality越高，列就越有成为索引的价值。...
让Oracle跑得更快 Oracle 10g性能分析与优化思路.part1.rar
12-05
5.1 cardinality (基数) 85 5.2 sql的执行计划 94 第6章 hint 109 6.1 和优化器相关的hint 115 6.1.1 all_rows和first_rows(cbo) 115 6.1.2 rule hint 117 6.2 访问路径相关的hint 117 6.2.1 full hint 118 6.2.2 ...
【DBMS_STATS】并行和并发收集统计信息
SQLplusDB的小院
06-18 9739
本文针对并行和并发收集统计信息的相关知识内容以及部分案例进行介绍，并重点介绍并发统计信息收集。
【Oracle 优化器】自适应游标共享(Adaptive Cursor Sharing)功能
SQLplusDB的小院
06-30 8391
本文主要介绍Oracle优化器的自适应游标共享(Adaptive Cursor Sharing)功能和处理流程。
【Oracle 优化器】自适应执行计划(Adaptive Execution Plans)
SQLplusDB的小院
07-09 4424
本文介绍Oracle 12c 推出的自适应连接方法（Adaptive Join Methods）和自适应并行分布方法（Adaptive Parallel Distribution Methods APDM）
【Oracle 优化器】动态统计（Dynamic Statistics）
SQLplusDB的小院
07-25 2762
本文动态统计Dynamic Statistics（动态采样Dynamic Sampling）相关知识
【Table Compression】OLTP压缩(OLTP Compression)
SQLplusDB的小院
07-03 2398
本文介绍从Oracle 11g版本开始推出的一种高级行压缩技术--OLTP 压缩(OLTP Compression)功能。
oracle性能优化篇之执行计划详解.pdf
最新发布
09-24
《Oracle性能优化篇之执行计划详解》是一篇介绍Oracle数据库性能优化中的关键概念和技术的文章。通过深入讲解Oracle执行计划的相关内容，帮助读者更好地理解和优化数据库查询性能。 在Oracle数据库中，执行计划是查询优化器生成的一种执行方案，用于指导数据库在执行SQL语句时的操作步骤和顺序。执行计划会根据表、索引、统计信息等内容，通过优化器的选择逻辑来生成，从而提高查询效率和性能。 《Oracle性能优化篇之执行计划详解》首先介绍了执行计划的基本组成结构，包括操作、访问方法、操作对象等。然后深入解析了执行计划的生成过程，包括Cost-Based Optimizer(CBO)和Rule-Based Optimizer(RBO)两种优化器的工作原理和区别。 接着，文章详细讲解了执行计划的查看方法和解读技巧。通过使用Oracle提供的多种工具和命令，如EXPLAIN PLAN、AUTOTRACE等，可以获取和分析执行计划，并根据结果进行性能优化。同时，文章还解释了执行计划中的关键指标和信息，如Cost、Cardinality、Bytes等，以及其对性能的影响和解决方法。 除此之外，《Oracle性能优化篇之执行计划详解》还介绍了一些常见的执行计划优化技术和策略。如索引的优化、统计信息的收集、SQL语句的调整等，通过优化执行计划中的关键因素，可以提高数据库查询效率和性能。 通过阅读《Oracle性能优化篇之执行计划详解》，读者可以深入了解Oracle数据库性能优化中的执行计划概念和技术，掌握相关工具和方法，从而更好地优化数据库查询性能。该文章对于数据库开发人员、DBA等有着重要的参考价值，帮助他们解决实际问题，提高数据库系统的性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

SQLplusDB

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值