oracle 11g中，同一条sql，第一次执行很快，紧接着执行就比较慢 ？ -m6米乐安卓版下载

现象：

前几天有个客户反馈说有个应用界面切换慢，经过查看sql发现在数据库执行查询的时候，第一次查询是1s，后面最快都要7s。 过一段时间再去查询，第一次又很快，后面还是很慢。

环境：

red hat 7
oracle 11.2.0.4

处理

想到了一个11g特性：基数反馈
当时是在数据库级别关闭了这个特性，又引发了一些问题。
经验教训是：先去加hint去测试，如果是这个原因，同样用hint去处理。

1.默认为true，查看参数值
select ksppinm, ksppstvl, ksppdesc from x$ksppi x, x$ksppcv y
where x.indx = y.indx
and ksppinm = '_optimizer_use_feedback';
2.怎么关闭特性 ？
--关闭特性（session、system、hint）
alter session set "_optimizer_use_feedback"=false;
alter system set "_optimizer_use_feedback"=false;
--或者在hint中提示
select /*  opt_param('_optimizer_use_feedback' 'false') */ * from test where owner='scott';
3.什么时候关闭 ？什么时候使用 ？
--什么情况下使用cardinality feedback特性 ？
--没有收集表的统计信息，并且动态采样（dynamic sampling）也没有开启。
--查询条件复杂（比如条件有函数）或者涉及多列，但却没有收集扩展的统计信息（extended statistics）。

--什么情况下关闭cardinality feedback特性 ？
--一条sql，第一次执行很快，之后就比较慢，影响到业务。
实验步骤如下：
--创建测试表
conn scott/oracle
create table test as select * from dba_objects where 0=1;
create index idx_test on test(owner);
insert into test select * from dba_objects;
commit;
--查看表的统计信息
select sample_size, last_analyzed from dba_tables where owner='scott' and table_name='test_cf';
sql> select sample_size, last_analyzed from dba_tables where owner='scott' and table_name='test_cf';
no rows selected
--执行sql查看执行计划
set autot trace
select  * from test where owner='scott';
sql> set autot trace
sql> select  * from test where owner='scott';
15 rows selected.
execution plan
----------------------------------------------------------
plan hash value: 2473784974
---------------------------------------------------------------------------
| id  | operation     | name     | rows  | bytes | cost (%cpu)| time    |
|   0 | select statement   |      |    15 |  3105 |     1   (0)| 00:00:01 |
|   1 |  table access by index rowid| test  15 |  3105 |1   (0)| 00:00:01 |
|*  2 |   index range scan | idx_test |    15 |   |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
   2 - access("owner"='scott')
note
-----
   - dynamic sampling used for this statement (level=2)
statistics
----------------------------------------------------------
         11  recursive calls
          1  db block gets
        122  consistent gets
          0  physical reads
        256  redo size
       2446  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)
         15  rows processed
sql>
--从执行计划可以看出，此时没有统计信息，采用了动态采样。dynamic sampling used for this statement
--关闭动态采样
alter system flush shared_pool;
alter system flush buffer_cache;
exec dbms_stats.delete_table_stats(user,'test',cascade_columns => true,cascade_indexes => true);
alter session set optimizer_dynamic_sampling=0;
--再次执行，查看执行计划
set autot trace
select  * from test where owner='scott';
sql> set autot trace
sql> select  * from test where owner='scott';
15 rows selected.
execution plan
---------------------------------------------------------
plan hash value: 2473784974
---------------------------------------------------------------------------
| id  | operation    | name     | rows  | bytes | cost (%cpu)| time     |
---------------------------------------------------------------------------
|   0 | select statement  |    |  1023 |   206k|     5   (0)| 00:00:01 |
|   1 |  table access by index rowid| test  | 1023 |206k|5 (0)| 00:00:01 |
|*  2 |   index range scan  | idx_test | 409 | |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
   2 - access("owner"='scott')
statistics
----------------------------------------------------------
         21  recursive calls
          0  db block gets
         47  consistent gets
         26  physical reads
          0  redo size
       2446  bytes sent via sql*net to client
        523  bytes received via sql*net from client
          2  sql*net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
         15  rows processed
sql>
--第二次执行
sql> set autot trace
sql> select  * from test where owner='scott';
15 rows selected.
execution plan
----------------------------------------------------------
plan hash value: 2473784974
---------------------------------------------------------------------------
| id  | operation    | name     | rows  | bytes | cost (%cpu)| time     |
---------------------------------------------------------------------------
|   0 | select statement    |  |  1023 |   206k|     5   (0)| 00:00:01 |
|   1 |  table access by index rowid| test | 1023 | 206k| 5 (0)| 00:00:01 |
|*  2 |   index range scan| idx_test |   409 ||     1   (0)| 00:00:01 |
---------------------------------------------------------------------------
predicate information (identified by operation id):
--------------------------------------------------
   2 - access("owner"='scott')
statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          6  consistent gets
          0  physical reads
          0  redo size
       2446  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)
         15  rows processed
sql>
--通过autotrace看执行计划都一样
--我们通过sql_id查看
select sql_id from v$sql where sql_text like '%from test where owner%';
7xadg5467u1su
--我们查看执行计划
select * from table(dbms_xplan.display_cursor(to_char('7xadg5467u1su'),null));
sql> select * from table(dbms_xplan.display_cursor(to_char('7xadg5467u1su'),null));
plan_table_output
---------------------------------------------------------------------------
sql_id  7xadg5467u1su, child number 0
-------------------------------------
select  * from test where owner='scott'
plan hash value: 2473784974
---------------------------------------------------------------------------
| id  | operation   | name     | rows  | bytes | cost (%cpu)| time     |
plan_table_output
---------------------------------------------------------------------------
| 0 | select statement         |          |       |       |     5 (100)||
| 1 | table access by index rowid| test | 1023 |  06k|  5   (0)| 00:00:01 |
|*  2 |   index range scan | idx_test |   409 |    |   1   (0)| 00:00:01 |
---------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
   2 - access("owner"='scott')
plan_table_output
--------------------------------------------------------------------------
sql_id  7xadg5467u1su, child number 1
-------------------------------------
select  * from test where owner='scott'
plan hash value: 2473784974
---------------------------------------------------------------------------
| id  | operation   | name     | rows  | bytes | cost (%cpu)| time     |
---------------------------------------------------------------------------
| 0 | select statement  |       |       |       |     2 (100)||
|   1 |  table access by index rowid| test  | 15 |  3105 |  2   (0)|00:00:01 |
|*  2 |   index range scan          | idx_test |    15 |       |     1   (0)| 00
---------------------------------------------------------------------------
predicate information (identified by operation id):
--------------------------------------------------
   2 - access("owner"='scott')
plan_table_output
---------------------------------------------------------------------------
note
-----
   - cardinality feedback used for this statement
42 rows selected.
sql>
--我们可以看到，其实生成了两个执行计划，其中rows发生了变化。并且有 cardinality feedback used for this statement
--结论
--实验可以说明
--cardinality feedback特性在没有统计信息的时候且动态采样没有开启，会根据同一个sql第一次执行之后的结果去重新估计第二次的基数，
--从而重新评估和生成第二次的执行计划。
--进一步可以得出
--因为我们现场客户遇到的问题也是如此，但是有统计信息，只是统计信息不准，一年之前的统计信息。所以我们可以说，
--在没有统计信息或者统计信息陈旧的时候也会使用cardinality feedback
--在oracle 11g中，cardinality feedback功能默认开启
--oracle 11gr2针对此特性，也专门在v$sql_shared_cursor中增加了use_feedback_stats列来记录sql是否使用了基数反馈。
select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id='7xadg5467u1su';
sql> select sql_id,child_number,use_feedback_stats from v$sql_shared_cursor where sql_id='7xadg5467u1su';
sql_id                                  child_number use
--------------------------------------- ------------ ---
7xadg5467u1su                                      0 y
7xadg5467u1su                                      1 n