Oracle 11g中，同一条sql，第一次执行很快，紧接着执行就比较慢？

原创大柏树 2022-10-26

2476

现象：

前几天有个客户反馈说有个应用界面切换慢，经过查看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