[20140301]直方图与优化.txt

SCOTT@test01p> @ver
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SCOTT@test01p> create table t(id number,name varchar2(10),status varchar2(1));
Table created.

insert into t select rownum,'testtest12','Y' from dual connect by level<=9999;
insert into t values(10000,'TTTTTTTT34','N');
commit ;

create index i_t_status on t(status);

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

select * from t where status='N';

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |      1 |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--直方图给出了准确的估计。

--假设白天的业务更新了数据，不存在status='N'的记录，晚上做了分析后呢？

SCOTT@test01p> update t set status='Y' where id=10000;
1 row updated.
SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |     2 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |      1 |     2   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |      1 |     1   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--执行计划选择了索引。修改一些数据模型。加入5000条status＝'A'的记录。

insert into t select 10000+rownum ,'AAAA123456','A' from dual connect by level<=5000;
commit ;

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 3549365421
--------------------------------------------------------------------------------
| Id  | Operation                           | Name       | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |        |    14 (100)|
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T          |   2500 |    14   (0)|
|*  2 |   INDEX RANGE SCAN                  | I_T_STATUS |   2500 |     6   (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("STATUS"='N')

--执行计划选择索引，但是E_rows=2500，明显估计偏大。再增加一些数据呢?

insert into t select 15000+rownum ,'BAAA123456','A' from dual connect by level<=5000;
commit ;

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,
method_OPT=>'FOR ALL COLUMNS SIZE 1 for columns status size 254') ;
PL/SQL procedure successfully completed.

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    19 (100)|
|*  1 |  TABLE ACCESS FULL| T    |   5000 |    19   (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"='N')

--实际上这次选择了全表扫描。实际上我们一条'N'的数据都没有。
--再次执行相同语句，执行计划没有变化。

SYS@test> @hide feedback
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%feedback%')
NAME                             DESCRIPTION                                DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
-------------------------------- ------------------------------------------ -------------- -------------- -------------
_optimizer_feedback_control      controls the optimizer feedback framework  TRUE
_optimizer_gather_feedback       optimizer gather feedback                  TRUE           TRUE           TRUE
_optimizer_performance_feedback  controls the performance feedback          TRUE           OFF            OFF
_optimizer_use_feedback          optimizer use feedback                     TRUE           TRUE           TRUE

SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,method_OPT=>'FOR ALL COLUMNS SIZE 1') ;
PL/SQL procedure successfully completed.

--删除直方图。

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  9zpyxn85rpbkw, child number 0
-------------------------------------
select * from t where status='N'
Plan hash value: 1601196873
--------------------------------------------------------
| Id  | Operation         | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
|   0 | SELECT STATEMENT  |      |        |    19 (100)|
|*  1 |  TABLE ACCESS FULL| T    |  10000 |    19   (0)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"='N')

--可以发现估计值更多。10000，占50%。多次执行执行计划不变。

--修改为绑定变量呢？

SCOTT@test01p> variable a varchar2(1);
SCOTT@test01p> exec :a := 'N';
PL/SQL procedure successfully completed.

SCOTT@test01p> select * from t where status=:a;
no rows selected

SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2m42nhwqx52s9, child number 0
-------------------------------------
select * from t where status=:a
Plan hash value: 1601196873
-------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |    19 (100)|      0 |00:00:00.01 |      68 |
|*  1 |  TABLE ACCESS FULL| T    |      1 |  10000 |    19   (0)|      0 |00:00:00.01 |      68 |
-------------------------------------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
   1 - :1 (VARCHAR2(30), CSID=852): 'N'
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("STATUS"=:A)

--依旧这样。注意 A-Rows＝0. 按照道理，应该会使用12c的statistics feedback。但是依旧没有出现。
--真不知道选择statistics feedback的依据是什么？

如何解决这个问题呢？

1.使用SPM或者sql profile。
2.或者伪造直方图信息，只要统计信息存在status='N'的少量的记录。执行计划会选择使用索引。
3.删除统计，使用动态取样，不过感觉如果表很大，可能存在一些问题。
4.修改统计分析表的时间。
5.总之，即使像12c，这样的问题oracle的cbo依旧存在一些问题。