Oracle SQL 语句优化

１．前言
日常工作中我们常遇到各种与数据库有关的问题，我处理的案例中，尤以ORACLE语句优化居多，说明语句优化在电信计费系统中较常见。任何工作都有章可循，语句优化也不例外。现结合实践整出优化思路与大家分享。
２．优化步骤总结
2.1找到需要调优的SQL语句
查看等待事件是找出需调优语句的极好办法。
2.2查看其执行计划
了解SQL运行方式，如全表读、索引读、HASH连接、分区扫描等，还可看出大致代价及物理读逻辑读。
2.3检查表和索引的分析情况
检查关键谓词是否有索引，及表和索引是否有分析过。
2.4检查表内部块的情况
检测是否存在行迁移及高水平位问题，目的是为了避免访问过多不必要的块。
2.5减少IO
有时由于涉及的表记录太大，以上的优化还不能达到要求，可考虑如下几个方案来减少IO。
2.5.1 cache表和索引
将表和索引CACHE到内存KEEP区中，将物理IO降低到0。
2.5.2重新组织表结构
取部分有用字段，且物理的将表进行排序，从而充分提高索引的效率，或者考虑索引组织表、族。
2.5.3考虑能否不回表操作
2.6改写sql
以上步骤还不能满足要求，可考虑其他方式：如将自连接改为分析函数、利用分区特性、利用ROWID和ROWNUM特性、利用中间表转化、利用物化视图等，这些因为需改写SQL显的更复杂。但在我的sql优化经历中，一般前5步做完后就收效甚佳，改动代码进一步优化的案例并不多见。
以上是在工作中总结出SQL优化步骤，让我们从一个案例来说说如何利用以上思路进行调优。
３．案例背景
在计费泉州上线准备期间，割接组同事希望提升工程环境中一存储过程的执行速度，当时大致12小时跑完数据。该存储过程用途是生成将来泉州上线数据的中间表，进而将中间表迁移到生产库，最终完成泉州数据迁移。此步骤执行太长将影响泉州割接上线。
４．案例处理
首先想到定位该存储过程在执行中哪出现瓶颈？我从数据库等待事件入手分析，因为只要SQL语句在执行，未结束就有等待，而且还有等待时长可协助判断。
4.1首先获取等待事件信息及SQL语句
执行如下脚本查询数据库目前的非空闲等待及其他详细信息：
select 'kill -9 ' || pr.spid || ';',
    'alter system kill session ' || '''' || s.sid || ',' || s.serial# || '''' || ';',
    s.username,s.serial#,s.machine,s.status,pr.spid,sw.*,sq.sql_text,sq.address
  from v$session s, v$session_wait sw, v$process pr, v$sqlarea sq
  where s.username is not null and sw.sid = s.sid and s.paddr = pr.ADDR
  and (s.sql_hash_value = sq.hash_value or s.prev_hash_value = sq.hash_value)
  and (s.sql_address = sq.address or s.prev_sql_addr = sq.address) and sw.event not like '%SQL*Net%'
  order by s.username;
执行以上脚本，结合机器名及SQL字段，定位出该过程的SID=28，为查看详细语句，执行如下脚本：
SELECT   sql_text   FROM v$sqltext a
    WHERE (a.hash_value, a.address) IN (
        SELECT DECODE (sql_hash_value,0, prev_hash_value,sql_hash_value ),
            DECODE (sql_hash_value, 0, prev_sql_addr, sql_address)
        FROM v$session b WHERE b.sid= '&sid')
  ORDER BY piece ASC
发现代入sid查询，每次跟踪到的都是如下两条语句
语句1：
select a.PROD_ID,a.prod_spec_id,b.mdse_spec_id, decode(c.region_id, null, 59501, c.region_id) region_id,a.pay_type,a.EXCH_ID
  from prod_his_crm_qz a, mdse_his_crm_qz b, manage_region_ibssf c
  where a.prod_id = b.prod_id and a.region = c.region_code(+) and a.prod_id = 20000
      and b.mdse_type in ('101', '107', '108') and a.attr_sort <> '101'
and a.state NOT in ('70N', '70R') and b.state NOT in ('70N', '70R');
语句2：
  select MAX(AGREEMENT_CODE) from t_v_telecom_account2_qz where tel_acct_id = v_n_acct_id;
这两个语句反复在等待事件中出现，说明该过程这两个语句有明显等待出现，可重点关注。
找到问题SQL，从优化和处理问题的角度来说是向成功迈进了一大步，让我们继续进行如下：
4.2获取需优化语句的执行计划
首先分析语句1的执行计划
SQL> explain plan for 将语句1代入此处（略去该语句）
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
| Id | Operation         | Name           | Rows | Bytes | Cost |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |   1 |  89 | 21381 |
|* 1 | HASH JOIN OUTER     |               |   1 |   89 | 21381 |
|   2 |   MERGE JOIN CARTESIAN|              |   1 |   70 | 21373 |
|* 3 |   TABLE ACCESS FULL | PROD_HIS_CRM_QZ    |   1 |   43 | 12479 |
|   4 |   BUFFER SORT     |               |   1 |   27 | 8894 |
|* 5 |   TABLE ACCESS FULL | MDSE_HIS_CRM_QZ    |   1 |   27 | 8894 |
|   6 |   TABLE ACCESS FULL   | MANAGE_REGION_IBSSF| 6174 |   114K|   7 |
Statistics
    1808 recursive calls
      0 db block gets
    631 consistent gets
      766 physical reads
      0 redo size
    2216 bytes sent via SQL*Net to client
    666 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      68 sorts (memory)
      0 sorts (disk)
      24 rows processed
发现语句1根本不走任何索引，执行以下语句查询，却发现索引都有。
SQL> select index_owner,index_name,table_name column_name from dba_ind_columns where table_name in ('PROD_HIS_CRM_QZ', 'MDSE_HIS_CRM_QZ','MANAGE_REGION_IBSSF');
INDEX_OWNER             INDEX_NAME               COLUMN_NAME
------------------------------ ------------------------------
IBSGJ                PROD_HIS_CRM$PID          PROD_HIS_CRM_QZ
IBSGJ                MDSE_HIS_CRM$PID          MDSE_HIS_CRM_QZ
IBSGJ                MDSE_HIS_CRM$TYPE         MDSE_HIS_CRM_QZ
IBSGJ                PROD_HIS_CRM$REGION         PROD_HIS_CRM_QZ
IBSGJ              MANAGE_REGION_IBSSF$REG     MANAGE_REGION_IBSSF
怀疑是否索引失效，执行如下查询发现索引状态正常。
SQL> select index_name,table_name,last_analyzed,status from dba_indexes where table_name in ('PROD_HIS_CRM_QZ','MDSE_HIS_CRM_QZ','MANAGE_REGION_IBSSF');
INDEX_NAME               TABLE_NAME               LAST_ANALYZED       STATUS
------------------------------ ------------------------------ ------------------- ------
PROD_HIS_CRM$PID         PROD_HIS_CRM_QZ                            VALID
PROD_HIS_CRM$REGION       PROD_HIS_CRM_QZ                          VALID
MDSE_HIS_CRM$PID           MDSE_HIS_CRM_QZ                          VALID
MDSE_HIS_CRM$TYPE         MDSE_HIS_CRM_QZ                          VALID
MANAGE_REGION_IBSSF$REG     MANAGE_REGION_IBSSF                       VALID
不过发现LAST_ANALYZED字段都为空，明显索引没分析过，继续如下查询，发现表也没分析！初步确定ORALCE没分析表和索引，得不到正确统计信息，执行错误计划从而查询缓慢。
SQL> select table_name,last_analyzed,partitioned from dba_tables where table_name in ('PROD_HIS_CRM_QZ','MDSE_HIS_CRM_QZ','MANAGE_REGION_IBSSF');
TABLE_NAME               LAST_ANALYZED     PAR
------------------------------ ------------------- ---
MANAGE_REGION_IBSSF                      NO
PROD_HIS_CRM_QZ                         NO
NOMDSE_HIS_CRM_QZ                        NO
  找到问题语句并分析其执行计划是ORACLE语句优化的前提，接着可利用各种知识，进行如下多次优化：

4.3 优化SQL语句
4.3.1第一次优化
至此我们明白现在优化首要的就是分析表和索引，让ORACLE得到最佳执行计划。
执行如下分析语句命令（当然也可考虑用dbms_stats性能包）：
analyze table mdse_his_crm_qz compute statistics for table for all indexes for all indexed columns;
再查执行计划，发现MDSE_HIS_CRM_QZ表走索引，且consistent gets也大大减少。真正执行时间也由13秒变为3秒，由于过程多次执行此SQL语句，所以可预计定能大幅减少运行时间。
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation              | Name           | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |   1 |   89 |   60 |
|* 1 | HASH JOIN OUTER          |              |   1 |   89 |   60 |
|   2 |   MERGE JOIN CARTESIAN      |              |   1 |   70 |   42 |
|* 3 |   TABLE ACCESS BY INDEX ROWID | PROD_HIS_CRM_QZ    |   1 |   43 |   6 |
|* 4 |   INDEX RANGE SCAN      | PROD_HIS_CRM$PID   |   2 |    |   3 |
|   5 |   BUFFER SORT          |              |   1 |   27 |   6 |
|* 6 |   TABLE ACCESS BY INDEX ROWID| MDSE_HIS_CRM_QZ    |   1 |   27 |   6 |
|* 7 |     INDEX RANGE SCAN       | MDSE_HIS_CRM$PID   |   4 |     |   2 |
|   8 |   TABLE ACCESS FULL         | MANAGE_REGION_IBSSF | 6174 |   114K|   7 |
  Statistics
      68 recursive calls
      0 db block gets
      63 consistent gets
      54 physical reads
      0 redo size
    2485 bytes sent via SQL*Net to client
    666 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      13 sorts (memory)
      0 sorts (disk)
      26 rows processed  
再观察第2个SQL语句：
select MAX(AGREEMENT_CODE) from t_v_telecom_account2_qz where tel_acct_id = 200012;
经分析，发现存在和第1条语句同样问题，同样方式处理后执行计划如下，查看较为满意。
PLAN_TABLE_OUTPUT
| Id | Operation            | Name                | Rows | Bytes | Cost|
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                     |   1 |   18 |   4 |
|   1 | SORT AGGREGATE        |                    |   1 |   18 |     |
|   2 |   TABLE ACCESS BY INDEX ROWID| T_V_TELECOM_ACCOUNT2_QZ    |   1 |   18 |   4 |
|* 3 |   INDEX RANGE SCAN       | T_V_TELECOM_ACCOUNT2_QZ$TID |   1 |    |   3 |
Statistics
      13 recursive calls
      0 db block gets
      18 consistent gets
      0 physical reads
      0 redo size
    1731 bytes sent via SQL*Net to client
    666 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      9 sorts (memory)
      0 sorts (disk)
      16 rows processed
从执行计划来看，收效明显。
接着检查该存储过程的所有sql语句，发现还有不少语句涉及的表和索引没分析，甚至还有语句在选择性很高的列上没索引。于是补上各个该建索引的地方，并对未分析的表和索引进行分析。
实际执行效果：处理时间由12小时缩短为4小时。

4.3.2第二次优化
前面的优化效果已经不错，能否继续提升？想到有可能存在行链接及高水平位问题导致ORACLE访问过多块，之前我也有过重新组织表消除这种问题后让数据库性能大幅提升的经历。但和割接组人员交流后得知这些表都是新建的，凭此可排除该因素。
再次细查执行计划，发现每个语句都存在不少物理读，由此想到将这些表和索引CACHE进内存，将物理读控制到0，毕竟磁盘读比内存读要慢许多。立即操作如下：
将prod_his_crm_qz表keep进内存区 
alter table prod_his_crm_qz storage(buffer_pool keep);
固定表数据
select count(*) from prod_his_crm_qz a
将prod_his_crm_qz表的索引keep进内存区
alter index PROD_HIS_CRM$PID storage(buffer_pool,keep);
固定索引数据
select count(*) from prod_his_crm_qz
至此prod_his_crm_qz表的索引及数据完全CACHE进内存，其他表操作类似。查看该语句执行计划，发现COST成本大幅下降，且物理读降为0。
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
| Id | Operation              | Name           | Rows | Bytes | Cost |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |   1 |   89 |   20 |
|* 1 | HASH JOIN OUTER          |              |   1 |   89 |   20 |
|   2 |   MERGE JOIN CARTESIAN      |              |   1 |   70 |   12 |
|* 3 |   TABLE ACCESS BY INDEX ROWID | PROD_HIS_CRM_QZ    |   1 |   43 |   6 |
|* 4 |   INDEX RANGE SCAN      | PROD_HIS_CRM$PID   |   2 |    |   3 |
|   5 |   BUFFER SORT          |              |   1 |   27 |   6 |
|* 6 |   TABLE ACCESS BY INDEX ROWID| MDSE_HIS_CRM_QZ    |   1 |   27 |   6 |
|* 7 |     INDEX RANGE SCAN       | MDSE_HIS_CRM$PID   |   4 |     |   2 |
|   8 |   TABLE ACCESS FULL         | MANAGE_REGION_IBSSF | 4174 |   84K|   7 |
Statistics
      18 recursive calls
      0 db block gets
      33 consistent gets
      0 physical reads
      0 redo size
    2485 bytes sent via SQL*Net to client
    666 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      13 sorts (memory)
      0 sorts (disk)
      26 rows processed
实际执行效果：处理时间进一步缩短为1.5小时。

4.3.3第三次优化
到此优化工作基本完成，不过我发现selectMAX(aggreement_code) from t_v_telecom_account2_qz where tel_acct_id = 200012;这个语句出现的频率还是很高。说明该存储过程中这条语句是相对最耗时。再次细查执行计划，发现TABLE ACCESS BY INDEX ROWID，这表示回表查询，而该语句只涉及AGREEMENT_CODE和TEL_ACCT_ID两字段，完全可考虑让索引直接答题而减少IO，新思路出现了。
Create index idx_acct_agre on it_v_telecom_account2_qz(tel_acct_id ,agreement_code) nologging;
建完以上联合索引后，又有新惊喜，TABLE ACCESS BY INDEX ROWID关键字消失了，代价由4变2。
PLAN_TABLE_OUTPUT
| Id | Operation            | Name       | Rows| Bytes | Cost |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   1 |  18 |   2 |
|   1 | SORT AGGREGATE        |           |   1 |   18 |     |
|   2 |   FIRST ROW            |          |   1 |   18 |   2 |
|* 3 |   INDEX RANGE SCAN (MIN/MAX)| IDX_ACCT_AGRE |  12|     |   2 |
实际执行效果：处理时间缩短为40分钟。

继续查看该SESSION等待事件，发现此max语句不再一直出现，由于该过程每条sql语句执行都很快，所以出现的机会均等，且等待时长几乎都是0,这是我们最愿意看。
至此本次调优工作圆满结束，5月31日泉州计费系统上线，真正演练的时候到了，果然，这个当初优化前认为运行时间最长的步骤，在经过我们上述描述的3次优化后，在40分钟内执行完毕，成功生成了中间数据！
迁移结束后，为了看是否还能改进，我在另一环境模拟了泉州情况，对该过程进行了进一步改进，具体方法就是物理的按照索引顺序重建表，让索引能最高效。
仍以前文中的第2条SQL语句为例
该表t_v_telecom_account2_qz其实是从CRM远程建到本地的表（目的是为了消除网络开销及可在本地对该表进行自由发挥）。建表时如考虑对tel_acct_id和aggreement_code进行排序，这样该表在物理上对这两字段做排序，在使用该索引时，就会极大减少逻辑IO。
以下是按索引顺序建好表后，索引更高效的情况，Cost由2变为1。
PLAN_TABLE_OUTPUT
| Id | Operation            | Name       | Rows| Bytes | Cost |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |   1 |  18 |   1 |
|   1 | SORT AGGREGATE        |           |   1 |   18 |     |
|   2 |   FIRST ROW            |          |   1 |   18 |   1 |
|* 3 |   INDEX RANGE SCAN (MIN/MAX)| IDX_ACCT_AGRE |  2|     |   1 |
实际执行效果：处理时间缩短为25分钟
５．总结
限于篇幅，本文描述无法详尽，略去许多细节只说大致思路。优化其实要考虑更多，比如我没提到并行度，这会提升性能，但也会对别的应用造成影响，我担心带并行的脚本在泉州计费上线执行时占用了机器大量CPU从而影响他人。文中的CACHE内存也是牺牲并发的方法，占用大量内存而可能导致别人内存不足运行缓慢，我暂无此担忧是因为事先规划过，这些都要考虑。再有此次并无修改代码，因无需修改就超出预期效果。如实际需修改sql调优，那将又是一个宽广的主题，需更多知识支撑，难度也更大。总之，调优是值得钻研的领域，这方面我还知之不多需不断努力。此外本次优化是在知道存储过程名的特例下进行，因此直接通过v$sql和v$open_cursor定位有问题的sql更方便，而本案例仍用观察等待事件方式仅是因为该方法通用。
写这篇优化浅谈希望抛砖引玉，给大家带来一定帮助，也让自己在总结中提升。服务于电信IT企业，大家平时都频繁的和数据库打交道，这促成我很想写些心得与大家分享。由于本人水平有限，文中多有不足及欠虑之处，望大家批评指正，谢谢！