第一次oracle sql tuning，简直就是乱来，见笑了

很好的参考：
http://www.iteye.com/topic/498902

1.首先在ADDM中看到建议调优的sql，跑一下SQL Tuning Advisor
看到一些建议，在EM这里更多的是建议你建index（毕竟这是最简单的方法）

2.观察需要调整表的结构，看是否适合做index
还是已经做了而因为有函数等原因使得失效

ALTER INDEX index_name MONITORING USAGE;
SELECT * FROM V$OBJECT_USAGE;

3.请程序员检查代码
正如参考里面说的，更改代码的效果往往要比建index要好得多

以下是具体的优化结果，
其实就是在原有基础上缩减了日期范围(acct_date已经做了index)，使得子集大大减少（重180天到1天）

原来:

SQL> SET SERVEROUTPUT ON;
SQL> SET LINESIZE 1000;
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT   q.work_ctr, SUBSTR (q.prod_nbr, 2, 2) AS size1,
  2           q.prod_nbr AS prod_nbr, COALESCE (a.pass_qty, 0) pass_qty,
  3           q.qty AS inspect_qty
  4      FROM (SELECT   main_wc AS work_ctr, prod_nbr, SUM (pass_qty) pass_qty
  5                FROM HMASPAS_SOUTPUT
  6               WHERE fac_id = 'A'
  7                 AND trans_date >= '2009-11-24 07.30.00'
  8                 AND trans_date <= '2009-11-24 19.30.00'
  9            GROUP BY main_wc, prod_nbr) a
 10           RIGHT JOIN
 11           (SELECT   work_ctr, prod_nbr, COUNT (*) AS qty
 12                FROM HMASHIS_WQDH
 13               WHERE fac_id = 'A'
 14                 AND trans_date >= '2009-11-24 07.30.00'
 15                 AND trans_date <= '2009-11-24 19.30.00'
 16            GROUP BY work_ctr, prod_nbr) q
 17           ON q.work_ctr = a.work_ctr AND q.prod_nbr = a.prod_nbr
 18  ORDER BY q.work_ctr, q.prod_nbr, a.pass_qty, q.qty;


37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 544019557

---------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                 |     1 |    56 | 85722   (2)| 00:17:09 |
|   1 |  SORT ORDER BY                  |                 |     1 |    56 | 85722   (2)| 00:17:09 |
|*  2 |   HASH JOIN OUTER               |                 |     1 |    56 | 85721   (2)| 00:17:09 |
|   3 |    VIEW                         |                 |     1 |    28 |     5  (20)| 00:00:01 |
|   4 |     HASH GROUP BY               |                 |     1 |    44 |     5  (20)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| HMASHIS_WQDH    |     1 |    44 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | INDEX2_WQDH     |     1 |       |     3   (0)| 00:00:01 |
|   7 |    VIEW                         |                 |    29 |   812 | 85715   (2)| 00:17:09 |
|   8 |     HASH GROUP BY               |                 |    29 |  1363 | 85715   (2)| 00:17:09 |
|*  9 |      TABLE ACCESS FULL          | HMASPAS_SOUTPUT |    29 |  1363 | 85714   (2)| 00:17:09 |
---------------------------------------------------------------------------------------------------

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

   2 - access("Q"."PROD_NBR"="A"."PROD_NBR"(+) AND "Q"."WORK_CTR"="A"."WORK_CTR"(+))
   5 - filter("FAC_ID"='A')
   6 - access("TRANS_DATE">='2009-11-24 07.30.00' AND "TRANS_DATE"<='2009-11-24 19.30.00')
   9 - filter("TRANS_DATE">='2009-11-24 07.30.00' AND "FAC_ID"='A' AND
              "TRANS_DATE"<='2009-11-24 19.30.00')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
     393981  consistent gets
     324974  physical reads
          0  redo size
       2043  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         37  rows processed

优化后：

SQL> SET SERVEROUTPUT ON;
SQL> SET LINESIZE 1000;
SQL> SET AUTOTRACE TRACEONLY;
SELECT   q.work_ctr, SUBSTR (q.prod_nbr, 2, 2) AS size1,
SQL>   2           q.prod_nbr AS prod_nbr, COALESCE (A.pass_qty, 0) pass_qty,
  3           q.qty AS inspect_qty
  4      FROM (SELECT   main_wc AS work_ctr, prod_nbr, SUM (pass_qty) pass_qty
  5                FROM HMASPAS_SOUTPUT
  6               WHERE fac_id = 'A'
  7                 AND acct_date >=
  8                        TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') - 1,
  9                                 'YYYY-MM-DD'
 10                                )
 11                 AND acct_date <=
 12                        TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') + 1,
 13                                 'YYYY-MM-DD'
 14                                )
 15                 AND trans_date >= '2009-11-24 07.30.00'
 16                 AND trans_date <= '2009-11-24 19.30.00'
 17            GROUP BY main_wc, prod_nbr) A
 18           RIGHT JOIN
 19           (SELECT   work_ctr, prod_nbr, COUNT (*) AS qty
 20                FROM HMASHIS_WQDH
 21               WHERE fac_id = 'A'
 22                 AND acct_date >=
 23                        TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') - 1,
 24                                 'YYYY-MM-DD'
 25                                )
 26                 AND acct_date <=
 27                        TO_CHAR (TO_DATE ('2009-11-24', 'YYYY-MM-DD') + 1,
 28                                 'YYYY-MM-DD'
 29                                )
 30                 AND trans_date >= '2009-11-24 07.30.00'
 31                 AND trans_date <= '2009-11-24 19.30.00'
 32            GROUP BY work_ctr, prod_nbr) q
 33           ON q.work_ctr = A.work_ctr AND q.prod_nbr = A.prod_nbr
 34  ORDER BY q.work_ctr, q.prod_nbr, A.pass_qty, q.qty;

37 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2416477161

-------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |     1 |    56 |  1010   (1)| 00:00:13 |
|   1 |  SORT ORDER BY                  |                     |     1 |    56 |  1010   (1)| 00:00:13 |
|*  2 |   HASH JOIN OUTER               |                     |     1 |    56 |  1009   (1)| 00:00:13 |
|   3 |    VIEW                         |                     |     1 |    28 |     5  (20)| 00:00:01 |
|   4 |     HASH GROUP BY               |                     |     1 |    55 |     5  (20)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| HMASHIS_WQDH        |     1 |    55 |     4   (0)| 00:00:01 |
|*  6 |       INDEX RANGE SCAN          | INDEX2_WQDH         |     1 |       |     3   (0)| 00:00:01 |
|   7 |    VIEW                         |                     |     1 |    28 |  1003   (1)| 00:00:13 |
|   8 |     HASH GROUP BY               |                     |     1 |    58 |  1003   (1)| 00:00:13 |
|*  9 |      TABLE ACCESS BY INDEX ROWID| HMASPAS_SOUTPUT     |     1 |    58 |  1002   (1)| 00:00:13 |
|* 10 |       INDEX RANGE SCAN          | IDX_HMASPAS_SOUTPUT | 31455 |       |   102   (1)| 00:00:02 |
-------------------------------------------------------------------------------------------------------

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

   2 - access("Q"."PROD_NBR"="A"."PROD_NBR"(+) AND "Q"."WORK_CTR"="A"."WORK_CTR"(+))
   5 - filter("ACCT_DATE">='2009-11-23' AND "FAC_ID"='A' AND "ACCT_DATE"<='2009-11-25')
   6 - access("TRANS_DATE">='2009-11-24 07.30.00' AND "TRANS_DATE"<='2009-11-24 19.30.00')
   9 - filter("TRANS_DATE">='2009-11-24 07.30.00' AND "FAC_ID"='A' AND
              "TRANS_DATE"<='2009-11-24 19.30.00')
  10 - access("ACCT_DATE">='2009-11-23' AND "ACCT_DATE"<='2009-11-25')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1726  consistent gets
          0  physical reads
          0  redo size
       2043  bytes sent via SQL*Net to client
        509  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         37  rows processed

写在最后：
觉得SQL tuning的东西DBA的组织力、执行力比纯技术要重要得多！！