現象：

前幾天有個客戶反饋說有個應用界面切換慢，經過查看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