ORACLE sql tuning

问题语句：
select  TDATETIME,CONTRACTID,CONTRACTNAME,LASTPX,HIGHPX,LOWPX,CQ,TQ,LASTQTY,INITOPENINTS,OPENINTS,INTSCHG,TURNOVER,RISELIMIT,FALLLIMIT,PRESETTLE,PRECLOSE,S1,B1,SV1,BV1,OPENPX,CLOSEPX,SETTLEMENTPX,LIFELOW,LIFEHIGH,AVGPX,BIDIMPLYQTY,ASKIMPLYQTY,SIDE,MFLXID,S5,S4,S3,S2,B2,B3,B4,B5,SV5,SV4,SV3,SV2,BV2,BV3,BV4,BV5,PREDELTA,CURRDELTA,LOCALTIME,MARKET,DOMICONT,CHG,CHGPCT,VARIETIES
from GTA_QIP.MFL1_TAQ_201008
where contractid in ('l1008', 'm1008', 'p1008','y1008') order by tdatetime;
统计报告：
统计信息
----------------------------------------------------------
       1441  recursive calls
          0  db block gets
     654097  consistent gets
     637232  physical reads
          0  redo size
     311191  bytes sent via SQL*Net to client
       5684  bytes received via SQL*Net from client
        487  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
       7283  rows processed

SQL>
可以看出这个一致读和物理读高到吓死人。

问题现状：在IN里有三个条件的时候就会走索引， 有四个条件的时候就不会走索引。

最初给开发人员提示加 hints:
select  /*+INDEX(MFL1_TAQ_201008 ,INDEX_MFL1_TAQ_201008)*/ TDATETIME,CONTRACTID,CONTRACTNAME,LASTPX,HIGHPX,LOWPX,CQ,TQ,LASTQTY,INITOPENINTS,OPENINTS,INTSCHG,TURNOVER,RISELIMIT,FALLLIMIT,PRESETTLE,PRECLOSE,S1,B1,SV1,BV1,OPENPX,CLOSEPX,SETTLEMENTPX,LIFELOW,LIFEHIGH,AVGPX,BIDIMPLYQTY,ASKIMPLYQTY,SIDE,MFLXID,S5,S4,S3,S2,B2,B3,B4,B5,SV5,SV4,SV3,SV2,BV2,BV3,BV4,BV5,PREDELTA,CURRDELTA,LOCALTIME,MARKET,DOMICONT,CHG,CHGPCT,VARIETIES
from GTA_QIP.MFL1_TAQ_201008
where contractid in ('l1008', 'm1008', 'p1008','y1008') order by tdatetime;
统计报告：
统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       7047  consistent gets
          1  physical reads
          0  redo size
     310871  bytes sent via SQL*Net to client
       5684  bytes received via SQL*Net from client
        487  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       7283  rows processed


可发现速度有明显提高，从几分钟缩到几秒钟。 但存储过程中这个表名是不停变化的， 光靠加HINTS是不太可能的了。

提问： 为了加了一个条件就不会熟索引呢？
分析原因：
1. 统计不是最新的，所以CBO才不走索引。
2. 走INDEX的COST比FULL TABLE SCAN还大， 所以没有走索引。
3. OPTIMIZER_INDEX_COST_ADJ参数是不是设置的。（index 和FULL TABLE的COST的比例）
4. ORACLE 内部缺陷。
5. 看看COLUMN的分布是不是均匀。如果不均匀就收集下直方图。

当时一看到ORACLE 的版本是10G的， 心中大喜， 10G有一个功能叫AUTO_SQL_TUNING。
解决方法：
   declare
  my_task_name varchar2(30);
  mysqltext clob;
  begin
  mysqltext:='SELECT    TDATETIME,CONTRACTID,CONTRACTNAME,LASTPX,HIGHPX,LOWPX,CQ,TQ,LASTQTY,INITOPENINTS,OPENINTS,INTSCHG,TURNOVER,RISELIMIT,FALLLIMIT,PRESETTLE,PRECLOSE,S1,B1,SV1,BV1,OPENPX,CLOSEPX,SETTLEMENTPX,LIFELOW,LIFEHIGH,AVGPX,BIDIMPLYQTY,ASKIMPLYQTY,SIDE,MFLXID,S5,S4,S3,S2,B2,B3,B4,B5,SV5,SV4,SV3,SV2,BV2,BV3,BV4,BV5,PREDELTA,CURRDELTA,LOCALTIME,MARKET,DOMICONT,CHG,CHGPCT,VARIETIES
from GTA_QIP.MFL1_TAQ_201008
where contractid in (''l1008'', ''m1008'', ''p1008'',''y1008'') order by tdatetime';
  my_task_name:=dbms_sqltune.create_tuning_task
  (sql_text=>mysqltext,
  user_name=>'SYS',
  scope=>'COMPREHENSIVE',
  task_name=>'sql_tuning_test'
  );
  end;

BEGIN
     dbms_sqltune.execute_tuning_task('sql_tuning_test');
          --dbms_sqltune.drop_tuning_task('sql_tuning_test');
     END;

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
 SQL>   SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
 
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : sql_tuning_test
Tuning Task Owner                 : SYS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 05/25/2012 17:09:41
Completed at                      : 05/25/2012 17:10:52
Number of SQL Profile Findings    : 1
 
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID     : c3vt7arcnc8p8
SQL Text   : SELECT    TDATETIME,CONTRACTID,CONTRACTNAME,LASTPX,HIGHPX,LOWPX,C
             Q,TQ,LASTQTY,INITOPENINTS,OPENINTS,INTSCHG,TURNOVER,RISELIMIT,FAL
             LLIMIT,PRESETTLE,PRECLOSE,S1,B1,SV1,BV1,OPENPX,CLOSEPX,SETTLEMENT
             PX,LIFELOW,LIFEHIGH,AVGPX,BIDIMPLYQTY,ASKIMPLYQTY,SIDE,MFLXID,S5,
             S4,S3,S2,B2,B3,B4,B5,SV5,SV4,SV3,SV2,BV2,BV3,BV4,BV5,PREDELTA,CUR
             RDELTA,LOCALTIME,MARKET,DOMICONT,CHG,CHGPCT,VARIETIES
             from GTA_QIP.MFL1_TAQ_201008
             where contractid in ('l1008', 'm1008', 'p1008','y1008') order by
             tdatetime
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  为此语句找到了性能更好的执行计划。
 
  Recommendation (estimated benefit: 97.78%)
  ------------------------------------------
  - 考虑接受推荐的 SQL 概要文件。
   execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_test',
            replace => TRUE);
 
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
 
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2802143541
 
--------------------------------------------------------------------------------
| Id  | Operation          | Name            | Rows  | Bytes |TempSpc| Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                 |  3269 |   648K|       |   143K  (
|   1 |  SORT ORDER BY     |                 |  3269 |   648K|  1056K|   143K  (
|*  2 |   TABLE ACCESS FULL| MFL1_TAQ_201008 |  3269 |   648K|       |   143K  (
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("CONTRACTID"='l1008' OR "CONTRACTID"='m1008' OR "CONTRACTID"='p100
              "CONTRACTID"='y1008')
 
2- Using SQL Profile
--------------------
Plan hash value: 1571978519
 
--------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes |T
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |  3269 |   648K|
|   1 |  SORT ORDER BY                |                       |  3269 |   648K|
|   2 |   INLIST ITERATOR             |                       |       |       |
|   3 |    TABLE ACCESS BY INDEX ROWID| MFL1_TAQ_201008       |  3269 |   648K|
|*  4 |     INDEX RANGE SCAN          | INDEX_MFL1_TAQ_201008 |  7282 |       |
--------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("CONTRACTID"='l1008' OR "CONTRACTID"='m1008' OR "CONTRACTID"='p100
              "CONTRACTID"='y1008')
 
-------------------------------------------------------------------------------

从以上报告可以得出，ORACLE建议执行以下语句：
execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_test',
            replace => TRUE);
然后再执行查询就可以发现查询走索引了，速度也优化到了2秒钟。    但遗憾的是最终问题还是没有解决， 表名是动态的，有一百多张表，总不可能每张表都这样做一次吧，望高人能够给些建议。

小知识：
查询表统计时间：dba_tables中的Last analyst 字段。
动态HINTS（高人提到的， 不解， 查不到相关的东西，请高人指点。）：  
 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22740983/viewspace-734641/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22740983/viewspace-734641/