Oracle 11g的基数反馈（Cardinality Feedback）

DBA小记 2020-10-27

1580

1、Oracle 11g的基数反馈（Cardinality Feedback）

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

Cardinality Feedback，本质上就是一种基于自适应模型的执行计划调整机制。当对一个SQL语句，第一次生成执行计划之后，其计算的成本cost是依据估算出的cardinality得出的。当执行这个计划后，Oracle就会得到真正这个SQL的执行计划结果，并且用真实的结果集合来更新执行计划中的Cardinality。在第二次生成时候，就可以使用更加真实的结果来确定了。

在Oracle 11g中，Cardinality Feedback功能默认开启，控制参数是一个隐含参数“_optimizer_use_feedback”。

查询隐含参数的设置：

SELECT ksppinm, ksppstvl, ksppdesc
  FROM x$ksppi x, x$ksppcv y
 WHERE x.indx = y.indx
   AND ksppinm = '_optimizer_use_feedback';
复制

2、创建测试数据

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE      11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> create table test_cf as select * from dba_objects where 0=1;
Table created.
SQL> create index idx_test_cf on test_cf(owner);
Index created.
SQL> insert into test_cf select * from dba_objects;
74481 rows created.
SQL> commit;
Commit complete.
复制

检查隐含参数“_optimizer_use_feedback”，默认值为true。表示启用cardinality feedback。

SQL> col KSPPINM for a40
SQL> col KSPPSTVL for a8 
SQL> col KSPPDESC for a40
SQL>SELECT ksppinm, ksppstvl, ksppdesc
  FROM x$ksppi x, x$ksppcv y
 WHERE x.indx = y.indx
   AND ksppinm = '_optimizer_use_feedback';
KSPPINM                          KSPPSTVL KSPPDESC
---------------------------------------- -------- ----------------------------------------
_optimizer_use_feedback              TRUE        optimizer use feedback
复制

或：

SQL> col name for a30
SQL> col value for a10
SQL> select
x.ksppinm name,
y.ksppstvl value,
y.ksppstdf isdefault,
decode(bitand(y.ksppstvf, 7), 1, 'MODIFIED', 4, 'SYSTEM_MOD', 'FALSE') ismod,
decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj
from
sys.x$ksppi x,
sys.x$ksppcv y
where
x.inst_id = userenv('Instance') and
y.inst_id = userenv('Instance') and
x.indx = y.indx and
x.ksppinm like '%use_feedback%'
order by
translate(x.ksppinm, ' _', ' ');
NAME                         VALUE        ISDEFAULT ISMOD      ISADJ
------------------------------ ---------- --------- ---------- -----
_optimizer_use_feedback        TRUE        TRUE          FALSE      FALSE
复制

3、执行SQL查看执行计划

Cardinality Feedback起作用的两个时点，首先是没有统计信息，其次是SQL估算Row数值困难。我们先看无统计信息的情况。

SQL> select SAMPLE_SIZE, LAST_ANALYZED from dba_tables where owner='TEST' and table_name='TEST_CF';
SAMPLE_SIZE LAST_ANALYZE
----------- ------------
复制

或：

SQL> select SAMPLE_SIZE, LAST_ANALYZED from user_tables where table_name='TEST_CF';
no rows selected
复制

查看执行计划

SQL> set autotrace on
SQL> set autotrace traceonly
SQL> select *+ test */ * from test_cf where owner='TEST';
37 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 780183337


-------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    37 |  7659 |  7   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_CF    |    37 |  7659 |  7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | IDX_TEST_CF |    37 |    |  1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------
   2 - access("OWNER"='TEST')
Note
-----
   - dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
   10  recursive calls
    1  db block gets
   92  consistent gets
  194  physical reads
    0  redo size
       5417  bytes sent via SQL*Net to client
  542  bytes received via SQL*Net from client
    4  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
   37  rows processed
复制

此时虽然没有统计量，可见基数评估是根据动态采样得出的（dynamic sampling used for this statement (level=2)）。Cardinality Feedback没有出现。

4、关闭动态采样

先清理一下shared Pool，再删除统计信息。

SQL> alter system flush shared_pool;
System altered
SQL> alter system flush buffer_cache;
System altered
SQL> exec dbms_stats.delete_table_stats(user,'TEST_CF',cascade_columns => true,cascade_indexes => true);
PL/SQL procedure successfully completed
SQL> select SAMPLE_SIZE, LAST_ANALYZED from user_tables where table_name='TEST_CF';
SAMPLE_SIZE LAST_ANALYZE
----------- ------------
复制

--关闭动态统计功能。

SQL> alter session set optimizer_dynamic_sampling=0;
Session altered.
复制

执行新的SQL语句，查看执行计划情况。

--第一次执行

SQL> select *+ test-2 */ * from test_cf where owner='TEST';
37 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 780183337
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time        |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |   920 |   185K|      5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_CF        |   920 |   185K|      5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_CF |   368 |        |      1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST')
Statistics
----------------------------------------------------------
       43  recursive calls
        0  db block gets
       75  consistent gets
       10  physical reads
        0  redo size
       5417  bytes sent via SQL*Net to client
      542  bytes received via SQL*Net from client
        4  SQL*Net roundtrips to/from client
        5  sorts (memory)
        0  sorts (disk)
       37  rows processed
复制

注意：此时没有dynamic sampling used for this statement的信息。

从shared pool中可以看到SQL的游标记录一次。

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select *+ test-2 */%';
SQL_ID            VERSION_COUNT
------------- -------------
5652h1h0u3au6              1
复制

--第二次执行

SQL> select *+ test-2 */ * from test_cf where owner='TEST';
37 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 780183337
-------------------------------------------------------------------------------------------
| Id  | Operation                | Name        | Rows  | Bytes | Cost (%CPU)| Time        |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |              |   920 |   185K|      5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_CF        |   920 |   185K|      5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_TEST_CF |   368 |        |      1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST')
Statistics
----------------------------------------------------------
        1  recursive calls
        0  db block gets
       15  consistent gets
        0  physical reads
        0  redo size
       5417  bytes sent via SQL*Net to client
      542  bytes received via SQL*Net from client
        4  SQL*Net roundtrips to/from client
        0  sorts (memory)
        0  sorts (disk)
       37  rows processed
复制

从autotrace中，我们没有看到任何不同。再次查询shared pool中，看到了不同情况。

SQL> select sql_id,version_count from v$sqlarea where sql_text like 'select *+ test-2 */%';
SQL_ID            VERSION_COUNT
------------- -------------
5652h1h0u3au6              2
复制

出现了两个子游标。在相同的父游标下，存在了两个子游标。说明生成了两个执行计划。我们直接从shared pool中抽取出来，如下：

--子游标0

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '5652h1h0u3au6',cursor_child_no => 0,format => 'advanced'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5652h1h0u3au6, child number 0
-------------------------------------
select *+ test-2 */ * from test_cf where owner='TEST'
Plan hash value: 780183337
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    |    |  5 (100)|    |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_CF    |   920 |   185K|  5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | IDX_TEST_CF |   368 |    |  1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TEST_CF@SEL$1
   2 - SEL$1 / TEST_CF@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TEST_CF"@"SEL$1" ("TEST_CF"."OWNER"))
      END_OUTLINE_DATA
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST')


Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "OWNER"[VARCHAR2,30], "TEST_CF"."OBJECT_NAME"[VARCHAR2,128],
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
       "TEST_CF"."SUBOBJECT_NAME"[VARCHAR2,30], "TEST_CF"."OBJECT_ID"[NUMBER,22],
       "TEST_CF"."DATA_OBJECT_ID"[NUMBER,22], "TEST_CF"."OBJECT_TYPE"[VARCHAR2,19],
       "TEST_CF"."CREATED"[DATE,7], "TEST_CF"."LAST_DDL_TIME"[DATE,7],
       "TEST_CF"."TIMESTAMP"[VARCHAR2,19], "TEST_CF"."STATUS"[VARCHAR2,7],
       "TEST_CF"."TEMPORARY"[VARCHAR2,1], "TEST_CF"."GENERATED"[VARCHAR2,1],
       "TEST_CF"."SECONDARY"[VARCHAR2,1], "TEST_CF"."NAMESPACE"[NUMBER,22],
       "TEST_CF"."EDITION_NAME"[VARCHAR2,30]
   2 - "TEST_CF".ROWID[ROWID,10], "OWNER"[VARCHAR2,30]
53 rows selected.
复制

--子游标1

SQL> select * from table(dbms_xplan.display_cursor(sql_id => '5652h1h0u3au6',cursor_child_no => 1,format => 'advanced'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5652h1h0u3au6, child number 1
-------------------------------------
select /*+ test-2 */ * from test_cf where owner='TEST'
Plan hash value: 780183337
-------------------------------------------------------------------------------------------
| Id  | Operation        | Name    | Rows  | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |    |    |  2 (100)|    |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_CF    |    37 |  7659 |  2   (0)| 00:00:01 |


|*  2 |   INDEX RANGE SCAN      | IDX_TEST_CF |    37 |    |  1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TEST_CF@SEL$1
   2 - SEL$1 / TEST_CF@SEL$1
Outline Data
-------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
      DB_VERSION('11.2.0.3')
      OPT_PARAM('optimizer_dynamic_sampling' 0)
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "TEST_CF"@"SEL$1" ("TEST_CF"."OWNER"))
      END_OUTLINE_DATA


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
  */
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("OWNER"='TEST')
Column Projection Information (identified by operation id):
-----------------------------------------------------------
   1 - "OWNER"[VARCHAR2,30], "TEST_CF"."OBJECT_NAME"[VARCHAR2,128],
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
       "TEST_CF"."SUBOBJECT_NAME"[VARCHAR2,30], "TEST_CF"."OBJECT_ID"[NUMBER,22],
       "TEST_CF"."DATA_OBJECT_ID"[NUMBER,22], "TEST_CF"."OBJECT_TYPE"[VARCHAR2,19],
       "TEST_CF"."CREATED"[DATE,7], "TEST_CF"."LAST_DDL_TIME"[DATE,7],
       "TEST_CF"."TIMESTAMP"[VARCHAR2,19], "TEST_CF"."STATUS"[VARCHAR2,7],
       "TEST_CF"."TEMPORARY"[VARCHAR2,1], "TEST_CF"."GENERATED"[VARCHAR2,1],
       "TEST_CF"."SECONDARY"[VARCHAR2,1], "TEST_CF"."NAMESPACE"[NUMBER,22],
       "TEST_CF"."EDITION_NAME"[VARCHAR2,30]
   2 - "TEST_CF".ROWID[ROWID,10], "OWNER"[VARCHAR2,30]
Note
-----
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
   - cardinality feedback used for this statement
57 rows selected.
复制

‍

两次执行的都是索引路径，但是执行计划中的Rows（Cardinality）进行了调整，进而cost也发生了变化。

两个执行计划都明确的写清楚Dynamic Sampling没有使用。在第二个执行计划中，存在有“cardinality feedback used for this statement”的字样。说明第二个执行计划是使用了Cardinality Feedback产生的。

5、关闭Cardinality Feedback

可能需要关闭的情况：

（1）一条诡异的SQL，从执行第二次开始，执行计划发生变化，导致执行效率极低，最终影响核心业务系统的正常运行。

（2）一条SQL第一遍查询1秒左右能出来，不改任何地方，再次查询却需要50秒左右，随便修改条件值，或者换个注释，再查还是1秒能查出来，再次执行又是50秒左右才出来。

如果通过从shared pool中抽取出来的执行计划是不一样的，而且性能低下的使用了Cardinality Feedback可以考虑关闭特性。

SELECT * FROM TABLE(dbms_xplan.display_cursor('&SQL_ID',&cursor_child_no));
复制

这种应该是Oracle11g Cardinality Feedback的bug，11.2.0.4好像已经得到改善。

关闭参数：

alter system set "_optimizer_use_feedback"=false scope=both;
复制

oracle

文章转载自DBA小记，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

Oracle 11g的基数反馈（Cardinality Feedback）

评论

相关阅读