发现自4月10日左右，数据库在夜间出现读取峰值，大大高出常规数据访问量，做了个AWR采样报告，发现有个极其耗时的SQL，是导致大量逻辑读的根本原因，该SQL在AWR报告的SQL ordered by Gets指标数据如下：

Buffer Gets 1,159,725,189
Executions 1
Gets per Exec 1,159,725,189.00
%Total 99.94
CPU Time (s) 11181.33
Elapsed Time (s) 11348.06
SQL Id 8cvwwtrhz1x0m
SQL Module oracleerpdv@pa2 (TNS V1-V3)

可见该SQL是远程主机pa2发起的，占用CPU时间高达3个多小时。
而SQL TEXT的内容如下：

SELECT "A1"."IHCODE", "A1"."HCODE" FROM "CONTRACTTBL" "A1" WHERE "A1"."RMB">=2000000 AND "A1"."HSTATUS"='70' AND TO_DATE(TO_CHAR("A1"."RATIFYDATE", 'yyyy-mm-dd'), 'yyyy-mm-dd')>=TO_DATE('2006-01-01', 'yyyy-mm-dd') AND "A1"."IHCODE"<>ALL (SELECT "A3"."IHCODE" FROM "CONTRACTMANAGE" "A3") AND "A1"."IHCODE"=ANY (SELECT "A2"."IICODE" FROM "RATIFYTASK" "A2" WHERE "A2"."TBLNAME"='contracttbl' AND "A2"."RWCODE"='0745')

其中竟然有很少见的ANY和ALL函数，而检查pa2上的应用程序发现原来的SQL语句是这样写的：

select ihcode,hcode from contracttbl where rmb>=2000000 and hstatus='70' and to_date(to_char(ratifydate,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('2006-01-01','yyyy-mm-dd') and ihcode not in (select ihcode from contractmanage) and ihcode in (select iicode from ratifytask where tblname='contracttbl' and rwcode='0745')

这说明是Oracle在解析SQL语句的过程中，优化器对该SQL进行了查询重写，改写了原来的写法，但结果是等效的。

本来是怀疑最近在RATIFYTASK表上改动的索引造成的问题，但经过在测试环境试验，改动的索引并不会影响到这个查询。

还清除了ratifytask表没用的记录大概28万条，目前该表有373万条记录。然后用dbms_stats.gather_table_stats分析过，但是没用。而contracttbl表也有大概80万条记录，而且字段非常多。

接下来当然要看看其执行计划了，可以直接利用AWR的结果查看：

SQL> set linesize 120
SQL> select * from table(dbms_xplan.display_awr('8cvwwtrhz1x0m'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 8cvwwtrhz1x0m
--------------------
SELECT "A1"."IHCODE","A1"."HCODE" FROM "CONTRACTTBL" "A1" WHERE "A1"."RMB">=2000000 AND
"A1"."HSTATUS"='70' AND TO_DATE(TO_CHAR("A1"."RATIFYDATE",'yyyy-mm-dd'),'yyyy-mm-dd')>=TO_DATE('2006-
01-01','yyyy-mm-dd') AND "A1"."IHCODE"<>ALL (SELECT "A3"."IHCODE" FROM "CONTRACTMANAGE" "A3") AND
"A1"."IHCODE"=ANY (SELECT "A2"."IICODE" FROM "RATIFYTASK" "A2" WHERE "A2"."TBLNAME"='contracttbl'
AND "A2"."RWCODE"='0745')

Plan hash value: 1463762878

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 52760 (100)| |
| 1 | NESTED LOOPS SEMI | | 689 | 65455 | 52760 (1)| 00:10:34 |
| 2 | NESTED LOOPS ANTI | | 696 | 42456 | 17233 (2)| 00:03:27 |
| 3 | TABLE ACCESS FULL | CONTRACTTBL | 4882 | 209K| 17233 (2)| 00:03:27 |
| 4 | INDEX UNIQUE SCAN | CONTRACTMANAGE_PRIMARYKEY | 7432 | 123K| 0 (0)| |
| 5 | TABLE ACCESS BY INDEX ROWID| RATIFYTASK | 54720 | 1816K| 51 (0)| 00:00:01 |
| 6 | INDEX RANGE SCAN | RATIFYTASK_RW_PASS_VER_BKVER | 68 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

22 rows selected.

可见这里有一个对CONTRACTTBL表的全表扫描，这里看到的执行计划有限，可以看更加详细的信息，而且会发现是否查询重写，这里的执行计划都是一样的：

SQL> explain plan for select ihcode,hcode
2 from contracttbl where rmb>=2000000 and hstatus='70' and
3 to_date(to_char(ratifydate,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('2006-01-01','yyyy-mm-dd')
4 and ihcode not in (select ihcode from contractmanage)
5 and ihcode in (select iicode from ratifytask where tblname='contracttbl' and rwcode='0745')
6 /

Explained.
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1463762878

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 229 | 22900 | 31137 (2)| 00:06:14 |
| 1 | NESTED LOOPS SEMI | | 229 | 22900 | 31137 (2)| 00:06:14 |
| 2 | NESTED LOOPS ANTI | | 231 | 15246 | 17265 (2)| 00:03:28 |
|* 3 | TABLE ACCESS FULL | CONTRACTTBL | 1622 | 79478 | 17265 (2)| 00:03:28 |
|* 4 | INDEX UNIQUE SCAN | CONTRACTMANAGE_PRIMARYKEY | 7743 | 128K| 0 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| RATIFYTASK | 43489 | 1443K| 60 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | RATIFYTASK_RW_PASS_VER_BKVER | 85 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - filter("RMB">=2000000 AND "HSTATUS"='70' AND
TO_DATE(TO_CHAR(INTERNAL_FUNCTION("RATIFYDATE"),'yyyy-mm-dd'),'yyyy-mm-dd')>=TO_DATE('2006-01-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
4 - access("IHCODE"="IHCODE")
5 - filter("TBLNAME"='contracttbl' AND "IHCODE"="IICODE")
6 - access("RWCODE"='0745')

23 rows selected.

而我们发现这里的NESTED LOOPS ANTI，优化器都选择了CONTRACTTBL作为驱动表，而且是全表扫描，一般NL选错驱动表，都会导致非常严重的后果。

于是考虑加HINTS来消除这个全表扫描：

SQL> explain plan for select /*+ index(contracttbl CONTRACTTBL_PRIMARYKEY)*/ ihcode,hcode
2 from contracttbl where rmb>=2000000 and hstatus='70' and
3 to_date(to_char(ratifydate,'yyyy-mm-dd'),'yyyy-mm-dd') >= to_date('2006-01-01','yyyy-mm-dd')
4 and ihcode not in (select ihcode from contractmanage)
5 and ihcode in (select iicode from ratifytask where tblname='contracttbl' and rwcode='0745')
6 /

Explained.

SQL> select * from table(dbms_xplan.display)
2 /

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1617436633

---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 4900 | 119K (1)| 00:24:00 |
| 1 | NESTED LOOPS ANTI | | 49 | 4900 | 119K (1)| 00:24:00 |
| 2 | NESTED LOOPS | | 341 | 28303 | 119K (1)| 00:24:00 |
| 3 | SORT UNIQUE | | 43906 | 1457K| 101K (1)| 00:20:18 |
|* 4 | TABLE ACCESS BY INDEX ROWID| RATIFYTASK | 43906 | 1457K| 101K (1)| 00:20:18 |
|* 5 | INDEX RANGE SCAN | RATIFYTASK_RW_PASS_VER_BKVER | 148K| | 551 (1)| 00:00:07 |
|* 6 | TABLE ACCESS BY INDEX ROWID | CONTRACTTBL | 1 | 49 | 2 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | CONTRACTTBL_PRIMARYKEY | 1 | | 1 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | CONTRACTMANAGE_PRIMARYKEY | 7743 | 128K| 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - filter("TBLNAME"='contracttbl')
5 - access("RWCODE"='0745')
6 - filter("RMB">=2000000 AND "HSTATUS"='70' AND
TO_DATE(TO_CHAR(INTERNAL_FUNCTION("RATIFYDATE"),'yyyy-mm-dd'),'yyyy-mm-dd')>=TO_DATE('2006-01-01
00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
7 - access("IHCODE"="IICODE")
8 - access("IHCODE"="IHCODE")

26 rows selected.

可见已经消除了CONTRACTTBL表的全表扫描，实际测试后，该SQL很快就执行完毕了。

如何使用TRACE功能：

在SQLPLUS中得到语句总的执行时间
SQL> set timing on
只显示执行计划--（会同时执行语句得到结果）
SQL>set autotrace on explain
只显示统计信息---（会同时执行语句得到结果）
SQL>set autotrace on statistics
显示执行计划，屏蔽执行结果--（但语句实质还执行的）
SQL> set autotrace traceonly
（备注：同SET AUTOTRACE ON; 只不过不显示结果，显示计划和统计）
仅仅显示执行计划，屏蔽其他一切结果--(语句还是执行了)
SQL>set autotrace traceonly explain
对于仅仅查看大表的Explain Plan非常管用。
关闭：
SQL>set autotrace off;

引用Oracle文档：

Statistics

The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.