【11g新特性】Cardinality Feedback基数反馈

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

 

Cardinality Feedback2

Cardinality Feedback1

 

但是每一个Oracle新版本引入的新特性 都被一些老外DBA称之为buggy ,Cardinality Feedback基数反馈多少也造成了一些麻烦,典型的情况是测试语句性能时,第一次的性能最好,之后再运行其性能变差。

 

我们来看一下 Cardinality Feedback基数反馈是如何作用的:
注意使用普通用户来测试Cardinality Feedback,sys用户被默认禁用该特性

 

 

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> conn maclean/oracle
已连接。

SQL> show parameter dynamic

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
optimizer_dynamic_sampling           integer                0

SQL> create table test as select * from dba_tables;

表已创建。

SQL> select /*+ gather_plan_statistics */ count(*) from test;

  COUNT(*)
----------
      2873

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
SQL_ID  0p4u1wqwg6t9z, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |   8904 |   2873 |00:00:00.01 |     104 |
-------------------------------------------------------------------------------------

已选择14行。

SQL> select /*+ gather_plan_statistics */ count(*) from test;

  COUNT(*)
----------
      2873

SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  0p4u1wqwg6t9z, child number 1
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from test

Plan hash value: 1950795681

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |     104 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |     104 |
|   2 |   TABLE ACCESS FULL| TEST |      1 |   2873 |   2873 |00:00:00.01 |     104 |
-------------------------------------------------------------------------------------

Note
-----
   - cardinality feedback used for this statement

已选择18行。

 

 

上例中第一次运行时,由于未收集表上的统计信息且optimizer_dynamic_sampling=0 关闭了动态采样所以基数评估值(1)和实际值(2873)有着较大的差距。

 

 

cardinality feedback used for this statement这个信息说明第二次执行时使用了Cardinality Feedback基数反馈,且其基数评估也十分精确了,这是因为第二次执行时考虑到第一次执行时的基数反馈,我们来看看Oracle到底是如何做到的:

 

 

 

SQL> alter system flush shared_pool;

系统已更改。

SQL>
SQL> alter session set events '10053 trace name context forever, level 1';

会话已更改。

SQL>  select /*+ gather_plan_statistics */ count(*) from test;

  COUNT(*)
----------
      2873

SQL>  select /*+ gather_plan_statistics */ count(*) from test;

  COUNT(*)
----------
      2873

10053 trace:

第一次执行:

sql= select /*+ gather_plan_statistics */ count(*) from test
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    31 |           |
| 1   |  SORT AGGREGATE     |         |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | TEST    |  8904 |       |    31 |  00:00:01 |
--------------------------------------+-----------------------------------+

SELECT /*+ OPT_ESTIMATE (TABLE "TEST" ROWS=2873.000000 ) */ COUNT(*) "COUNT(*)" FROM "MACLEAN"."TEST" "TEST"

SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for TEST[TEST] 
  Table: TEST  Alias: TEST
    Card: Original: 8904.000000    >> Single Tab Card adjusted from:8904.000000 to:2873.000000
  Rounded: 2873  Computed: 2873.00  Non Adjusted: 8904.00
  Access Path: TableScan
    Cost:  31.10  Resp: 31.10  Degree: 0
      Cost_io: 31.00  Cost_cpu: 1991217
      Resp_io: 31.00  Resp_cpu: 1991217
  Best:: AccessPath: TableScan
         Cost: 31.10  Degree: 1  Resp: 31.10  Card: 2873.00  Bytes: 0

sql= select /*+ gather_plan_statistics */ count(*) from test
----- Explain Plan Dump -----
----- Plan Table -----

============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id  | Operation           | Name    | Rows  | Bytes | Cost  | Time      |
--------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT    |         |       |       |    31 |           |
| 1   |  SORT AGGREGATE     |         |     1 |       |       |           |
| 2   |   TABLE ACCESS FULL | TEST    |  2873 |       |    31 |  00:00:01 |
--------------------------------------+-----------------------------------+

 

 

 

 

可以看到第二次执行时SQL最终转换加入了 OPT_ESTIMATE (TABLE “TEST” ROWS=2873.000000 )的HINT ,OPT_ESTIMATE HINT一般由 kestsaFinalRound()内核函数生成。该HINT用以纠正各种类型的优化器评估,例如某表上的基数或某个列的最大、最小值。反应出优化的不足或者BUG。

 

可以通过V$SQL_SHARED_CURSOR和来找出现有系统shared pool中仍存在的 使用了Cardinality Feedback基数反馈的子游标:

 

 

SQL> select sql_ID,USE_FEEDBACK_STATS  FROM V$SQL_SHARED_CURSOR where USE_FEEDBACK_STATS ='Y';

SQL_ID                     US
-------------------------- --
159sjt1f6khp2              Y

 

 

 

还可以使用cardinality HINT来强制使用Cardinality Feedback 。

select /*+ cardinality(test,  1) */ count(*) from test;

 

 

如何禁用Cardinality Feedback基数反馈

 

对于这些”惹火”特性,为了stable,往往考虑关闭该特性。

可以通过多种方法禁用该特性

1. 使用 _optimizer_use_feedback 隐藏参数

session 级别

SQL> alter session set “_optimizer_use_feedback”=false;

会话已更改。

system级别

SQL> alter system set “_optimizer_use_feedback”=false;

系统已更改。

 

2. 使用opt_param(‘_optimizer_use_feedback’ ‘false’) HINT

例如:

select /*+ opt_param(‘_optimizer_use_feedback’ ‘false’) cardinality(test,1) */ count(*) from test;

 

 

 

SQL调优:带函数的谓词导致CBO Cardinality计算误差

今天处理了这样一问题,where条件中存在函数fun(date)<to_date(‘9999-01-01′,’YYYY-MM-DD’)这样的无实际意义谓词,导致CBO计算基数时cardinality远小于实际情况,导致优化器认为2个源数据集的基数都不大,从而选择了HASH JOIN Right SEMI+SORT ORDER BY的执行计划,但是由于实际基数远大于computed 计算值所以变成了大的数据集做HASH JOIN并全数据排序,而实际该SQL只要求返回几十行数据而已,使用NESTED LOOP SEMI JOIN可以立即返回排序的前20行数据。

这里就需要解释带函数的谓词时CBO如何计算基数,我们通过下面的例子来说明:

 

 

create or replace function check_date( RDATE in date)  return date is 
begin
IF rdate< to_date('2099-01-01','YYYY-MM-DD') then   return rdate;   ELSIF  rdate >=to_date('2099-01-01','YYYY-MM-DD') then 
 return to_date('2000-01-01');
 end if;
 end check_date;
 /

 SQL> select check_date (sysdate) from dual;

CHECK_DAT
---------
06-DEC-12

drop table tab1;

SQL> create table tab1 tablespace users as select * from dba_objects where rownum create view vtab1 as select object_id as id , object_name as name, object_type as type , check_date(created) cdata from tab1;

View created.

SQL> select count(distinct cdata) from vtab1;

COUNT(DISTINCTCDATA)
--------------------
                 130

SQL> exec dbms_stats.gather_table_stats('','TAB1', method_opt=>'FOR ALL COLUMNS SIZE 254');

PL/SQL procedure successfully completed.

 

 

因为我们指定收集了直方图所以若直接以”created”为条件查询时可以获得较好的计算基数

   SQL> select count(*) from tab1 where  created  >= to_date('0001-10-10','YYYY-MM-DD');

  COUNT(*)
----------
     10000

Execution Plan
----------------------------------------------------------
Plan hash value: 1117438016

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     8 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| TAB1 | 10000 | 80000 |    40   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CREATED">=TO_DATE(' 0001-10-10 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 

 

在以上查询中>= to_date(‘0001-10-10′,’YYYY-MM-DD’); 这样的过滤条件实际无意义,在直接使用 “created”列作为谓词的情况下,CBO可以获得很好的基数10000。

 

 

SQL> select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  6zy2k9dy4cv73, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ count(*) from vtab1 where  cdata
>= to_date('0001-10-10','YYYY-MM-DD')

Plan hash value: 1117438016

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.25 |     154 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.25 |     154 |
|*  2 |   TABLE ACCESS FULL| TAB1 |      1 |    500 |  10000 |00:00:00.31 |     154 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("CHECK_DATE"("CREATED")>=TO_DATE(' 0001-10-10 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

21 rows selected.

 

 

通过gather_plan_statistics HINT,我们得到E-Rows 即CBO评估的基数,和 A-Rows实际的基数,可以看到这里E-Rows=500, 即在谓词左边存在使用内部函数或隐身装换的情况下,CBO无法通过现有统计信息的DISTINCT、DENSITY和HISTOGRAM获得较好的Cardinality,其基数总是统计信息中表的总行数/20,如上例中的 10000/20=500。

这就会引入不少的麻烦,因为开发人员有时候为了方便会在视图字段中嵌入自定义的函数,之后若在查询中使用该字段作为谓词条件,则可能导致CBO为相应表计算的基数偏少,是本身应当成本非常高的执行计划的COST变低,而容易被优化器选择。

对于上述问题可选的常见方案是若有这样问题的SQL较少则考虑加HINT或者SQL PROFILE,若较多还是需要考虑减少这种谓词左边有函数的现象。

implicit data_type conversion functions in Filter Predicates. Review Execution Plans.
If Filter Predicatesinclude unexpected INTERNAL_FUNCTION to perform an implicit data_type conversion,
be sure it is not preventing a column from being used as an Access Predicate.

11g Multi-Column Correlation Stats and Dynamic Sampling

Oracle CBO优化模式中列的统计信息是一个十分重要的概念,但在11g之前我们所讨论的都是基于单列的统计信息或直方图,也就是说基于成本的优化器总是假设where子句后的谓词中列与列之间不存在联系。但是有的查询包含一个表的多个列,而每个列又都与不同的选择度。这些列中有的是相关的,但优化器并不知道这些关系。在这种情况下,优化器如果要估计出真实的基数(card),必须要了解增加另一列到某个给定列是否会引起结果集的减少。多列上的相关统计数据能提供比单列统计数据或直方图更好的基数估计。当2个列紧密相关时,增加额外的谓词可以减少结果集。Oracle database 11g中引入了扩展统计(也叫多列统计,multicolumn statistics),可以收集一组列上的统计数据,从而让优化器能准确地计算多个单列谓词的选择性。因为把紧密相关的列作为一个组才能正确地放映其组合选择性,所以把相关列作为一组,在其上(列祖)收集统计数据,这些信息足以让优化器能准确地进行选择性估计,在包含使用相关列的谓词查询中,这是我们实际关心的问题。多列统计的引入意味着,在11g中cbo优化器可以对具有多列复杂谓词判断的SQL语句做出更准确的成本估算,许多原本”误用”全表扫描的查询现在可以使用索引扫描的执行计划,语句将运行地更快速。

我们试看下例:
[Read more…]

沪ICP备14014813号

沪公网安备 31010802001379号