Skip to content

Cardinality Feedback与_optimizer_use_feedback的使用建议

该参数与Cardinality Feedback特性有关,优化器可以估算基数不正确的原因有很多，如缺少的统计信息，不准确的统计数据，或复杂的谓词,基数统计反馈有助于优化器生成更合理的执行计划.对于此特性我不作科普了,比较详细的资料可以参考以下文档:

1.Tuning-by-Cardinality-Feedback.pdf
2.Statistics (Cardinality) Feedback – Frequently Asked Questions (文档 ID 1344937.1)

该特性其实从10g版本就已经开始引入,只是它的应用受限于optimizer_dynamic_sampling参数,在11g以前的10g版本中,只有在optimizer_dynamic_sampling=4以上的情况下才会被用到该特性.这个在10g版本中可以通过HINT语法
/*+ dynamic_sampling (customer 4) */或者在session/system中将optimizer_dynamic_sampling设置为4进行测试.

判断SQL是否使用了CF可以通过sql的执行计划中的note提示或者视图V$SQL_SHARED_CURSOR的USE_FEEDBACK_STATS字段进行.

执行计划中的note提示

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

 6 - filter(ROWNUM>0)
 8 - access("DATA"."OBJECT_NAME"="T"."COLUMN_VALUE")

Note
-----
- Cardinality Feedback used for this statement

V$SQL_SHARED_CURSOR


column use_feedback_stats format a18
column sql_text format a80

select c.child_number, c.use_feedback_stats , s.sql_text from v$sql_shared_cursor c,v$sql s
where s.sql_id=c.sql_id and c.sql_id = 'an4zdfz0h7513'
and s.child_number= c.child_number;

CHILD_NUMBER USE_FEEDBACK_STATS SQL_TEXT
------------ ------------------ ------------------------------------------------------------
           0 Y                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t
           1 N                   select * from TABLE(cast( str_func('A,B,C' ) as s_type) ) t

对使用了基数统计回馈特性的sql,可以在10053的trace文件中发现sql被转化成带了hint opt_estimate的语法.
CF特性的功能是非常不错的,oracle在使用了cf反馈评估后更准确的统计数据后会根据此数据生成对于的plan,在再次的执行中还会持续评估统计信息是否准确,以此重复解析,参考如下:

When a cursor is found to be a candidate for Statistics Feedback it will be hard parsed again using the new estimates. The child cursor will be marked as not being shareable and USE_FEEDBACK_STATS set to ‘Y’ in V$SQL_SHARED_CURSOR.
Note: As the need for Statistics Feedback was only detected while execution of this cursor, Statistics Feedback will not actually be used for this child. However it will be used for all further child cursors created.

At the next execution, as a result of the cursor being marked as not shareable, a hard parse will again be performed and a new child created with the optimizer having used the new estimates for creating an optimizer plan.

但是由于cf的评估结果数据只存在内存中(重启之后就需要重新来过),在session之间是不可共用的,并且由于在11g中存在了过多的bug,常见的问题就是在第二次执行sql时候性能下降较多.因此我在11g的数据库中往往会对11.2.0.4以下的数据库会将该特性关闭.

部分bug list

feedback bug

关闭CF特性的方法即是将_optimizer_use_feedback设置为false.

alter session set "_optimizer_use_feedback" = false;
或者
alter system set "_optimizer_use_feedback" = false;
Data access tuning Oracle performance tuning 数据技术 | Guang Cai Li | |   Cardinality Feedback

遭遇ORA-29913,KUP-04084

数据库告警日志报错

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04084: The ORACLE_DATAPUMP access driver does not support the ROWID column.

是数据库在收集统计信息时候出现.环境是rac.原因是11g的oracle在访问外部表的时候不支持在dmp格式下以rowid的方式访问.这里数据量不大,把外部表改为堆表后解决.其他方面问题可以参考以下文献.

Get “KUP-04084” error message when viewing an external table in the schema browser’s data tab
说明
I am getting the following error message when trying to view an external table in the schema browser’s data tab.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04084: The ORACLE_DATAPUMP access driver does not support the ROWID column.

原因
The query the schema browser is running in the background to return the data is asking to also return the ROWID, which is not supported by Oracle for external tables.

解决办法
WORKAROUND:
1) Highlight your external table on the left hand side of the schema browser.
2) Select the data tab on the right hand side. (you will get the error)
3) Click OK on the error.
4) Click the ‘View/Edit Query’ button the data tab’s tool bar. The button is the second from the left on the toolbar and looks like a piece of paper with a magnifying glass.
5) Remove ‘ROWID,’ from the statement in the window and click OK.

This statement should stay modified until you close the schema browser, so you will need to do this each time you open a new schema browser and want to view your external table. I will let you know what development says once I hear back from them.

其他信息
This is only an issue in Oracle 11gR2 databases.

Database Oracle Oracle 排障配置与调整 | Guang Cai Li |

11g数据库自带 自动化任务的管理

接连几个客户的数据库自动化处理任务中收集统计信息部分出了问题,客户询问我这方面的事情,我整理了一翻如下的文档,可以了解11g,10g的自动任务构成,以及如何管理数据库自带的管理任务.

FAQ: Automatic Statistics Collection (文档 ID 1233203.1)
New 11g Default Jobs (文档 ID 755838.1)
How to Benefit from Automatic Maintenance Tasks Following the Removal of the GATHER_STATS_JOB in 11g? (文档 ID 743507.1)
DBMS_AUTO_TASK_ADMIN

在11g中,optimizer stats 的收集是通过自动维护任务实现

SQL> select client_name,task_name from dba_autotask_task;

CLIENT_NAME                              TASK_NAME
---------------------------------------- ------------------------------
auto space advisor                       auto_space_advisor_prog
auto optimizer stats collection          gather_stats_prog

SQL> SELECT CLIENT_NAME,
  2         STATUS
  3  FROM   DBA_AUTOTASK_CLIENT
  4  WHERE  CLIENT_NAME = 'auto optimizer stats collection';

CLIENT_NAME                              STATUS
---------------------------------------- --------
auto optimizer stats collection          ENABLED

如不想让这些自动维护任务去自动运行，你可以通过通过DBMS_AUTO_TASK_ADMIN.disable或enable配置.

可以将所有的自动维护任务全部disable或enable,也可以只针对某个task进行设置,比如:

BEGIN
DBMS_AUTO_TASK_ADMIN.disable( client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/

BEGIN
DBMS_AUTO_TASK_ADMIN.enable( client_name => ‘auto space advisor’,
operation => NULL,
window_name => NULL);
END;
/
Database Oracle Oracle 排障配置与调整 | Guang Cai Li |

skip_unusable_indexes参数使用建议

​SKIP_UNUSABLE_INDEXES的使用与索引失效是相关的,该参数10g开始引入,11g默认为TRUE.
当为TRUE时候,如果数据库中存在usable状态的索引,则会自动忽略该索引生成新的执行计划(不走该索引,也不提示该索引的异常);当为False时候,则会报错.我所运维的数据库在一些关键系统中,会将此参数设成False,让系统及时发现索引的异常以便及时去介入修复.
环境各有所异,设置值也可依据实际情况设置.如果sql使用了hint或者涉及到唯一索引的对应DML,该参数会失效.

该参数的一些使用场景可以参考如下的测试:

创建测试表和索引

SQL> conn test/test
已连接。
SQL> drop table a;
表已删除。
SQL> create table a(id number);
表已创建。
SQL> create unique index idx_a_id on a(id);
索引已创建。
SQL> declare
  2  begin
  3  for a in 1..1000 loop
  4  insert into a(id) values(a);
  5  end loop;
  6  end;
  7  /
PL/SQL 过程已成功完成。
SQL> commit;
提交完成。
SQL> show parameter SKIP_UNUSABLE_INDEXES;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
skip_unusable_indexes                boolean     TRUE
SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
        124  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

修改skip_unusable_indexes为false

SQL> alter system set skip_unusable_indexes=false scope=memory;
系统已更改。
将索引修改为不可用
SQL> alter index idx_a_id unusable;
索引已更改。
出现错误提示索引不可用
SQL> select * from a where id=1;
select * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

将skip_unusable_indexes修改为true

SQL> alter system set skip_unusable_indexes=true scope=memory;
系统已更改。

对于查询操作此时该sql能够正常运行，但是此时进行的是全表扫描

SQL> select * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 2248738933
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     4 |    52 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| A    |     4 |    52 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ID"=1)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

使用hint强制使用索引，此时会提示索引无效

SQL> select /*+index(a)*/ * from a where id=1;
select /*+index(a)*/ * from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
--插入操作会出错
SQL> insert into a values(1002);
insert into a values(1002)
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态
SQL> delete from a where id=1;
delete from a where id=1
*
第 1 行出现错误:
ORA-01502: 索引 'TEST.IDX_A_ID' 或这类索引的分区处于不可用状态

SQL>

解决方法，重建索引

SQL> alter index test.idx_a_id rebuild;
索引已更改。
SQL> select /*+index(a)*/ * from a where id=1;

执行计划
----------------------------------------------------------
Plan hash value: 277080427
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| IDX_A_ID |     1 |    13 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID"=1)

统计信息
----------------------------------------------------------
         15  recursive calls
          0  db block gets
          5  consistent gets
          1  physical reads
          0  redo size
        402  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> drop index test.idx_a_id;
索引已删除。
SQL> create index test.idx_a_id on a(id);
索引已创建。
SQL> alter index test.idx_a_id unusable;
索引已更改。
SQL> insert into a values(1002);
已创建 1 行。
SQL> commit;

测试证明SKIP_UNUSABLE_INDEXES对于使用hint强制使用索引的语句和唯一索引的插入、删除语句却不能生效。

该测试摘自互联网,同时做了一些修改.

Data access tuning Index Maintenance Oracle performance tuning 数据技术 | Guang Cai Li | |   skip_unusable_indexes