数据库性能优化一例之Hash Join

登陆操作系统,发现有一个Oracle进程CPU占用率达到99%.

Memory: 2617704K (860220K) real, 2774560K (942392K) virtual, 38440908K free  Page# 1/19

CPU TTY     PID USERNAME PRI NI   SIZE    RES STATE    TIME %WCPU  %CPU COMMAND
 2   ?    20219 oracle   234 20  6390M  5384K run     31:02 100.13 99.96 oracletest1
--

根据Pid查会话的SID:
SQL>select * from v$session where paddr in (select addr from v$process where spid = &Pid)--20219
--
SQL>select * from v$session_wait where sid = 50
1 50 383 db file sequential read file# 119 0000000000000077 block# 35698 0000000000008B72 blocks 1 0000000000000001 -1 9 WAITED KNOWN TIME
-------------------------------------------------
通过v$sqltext关联之后发现如下语句:
--------------------------------------------------------------------------------------------------
select a.Approve_Serial_Nbr,
       a.Approve_Type,
       a.approve_flag,
       a.acct_id,
       a.Approve_charge,
       a.Approve_Staff_Id,
       a.Approve_Site_Id,
       a.Approve_User_Name,
       a.Approve_reason,
       to_char(a.approve_date, 'mm/dd hh24:mi'),
       a.Used_flag,
       b.acc_nbr,
       b.approve_value,
       b.acct_month,
       b.acct_item_type_id
  from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
 where a.approve_serial_nbr = b.approve_serial_nbr
   and a.Partition_id_region between 110101 and 110199
   and b.Partition_id_region between 110101 and 110199
   and a.Approve_Staff_Id = 'sys110101'
   and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
       '2007/05/30'
   and a.Approve_Type = 'DERATE'
   and a.Approve_Type = b.Approve_Type
 order by a.approve_serial_nbr,
          a.ACCT_ID,
          b.acct_month,
          b.acct_item_type_id
---------------------------------------------------------------------------------
1 
2 --------------------------------------------------------------------------------------------------------------
3 | Id  | Operation                             |  Name                | Rows  | Bytes | Cost  | Pstart| Pstop |
4 --------------------------------------------------------------------------------------------------------------
5 |   0 | SELECT STATEMENT                      |                      |     1 |   336 |    11 |       |       |
6 |   1 |  SORT ORDER BY                        |                      |     1 |   336 |    11 |       |       |
7 |   2 |   TABLE ACCESS BY LOCAL INDEX ROWID   | APPROVE_ITEM_T       |     1 |    82 |     1 |       |       |
8 |   3 |    NESTED LOOPS                       |                      |     1 |   336 |     2 |       |       |
9 |   4 |     PARTITION RANGE ALL               |                      |       |       |       |     1 |     2 |
10 |   5 |      TABLE ACCESS BY LOCAL INDEX ROWID| APPROVE_SERIAL_T     |     1 |   254 |     1 |     1 |     2 |
11 |   6 |       INDEX RANGE SCAN                | I1_APPROVE_SERIAL_T  |     1 |       |     3 |     1 |     2 |
12 |   7 |     PARTITION RANGE ALL               |                      |       |       |       |     1 |     2 |
13 |   8 |      INDEX RANGE SCAN                 | I1_APPROVE_ITEM_T    |     1 |       |     2 |     1 |     2 |
14 --------------------------------------------------------------------------------------------------------------
15 
16 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------------------------------------------
检查各表数据:
select count(*) from bill_cc.approve_serial_t --1353163
select count(*) from  bill_cc.approve_item_t --1306518
--一般来说当2个大表进行连接涉及数据量较大时hash_join比nested loops更为合适!
  如果两个表都走索引的话后果很可能是恶梦。
                 
------------------------------------
下面强制执行全表扫描模式:
------------------------------------
select                                                  //用时17s
       /*+ full(a) full(b) */ *
  from bill_cc.approve_serial_t a, bill_cc.approve_item_t b
 where a.approve_serial_nbr = b.approve_serial_nbr
   and a.Partition_id_region between 110101 and 110199
   and b.Partition_id_region between 110101 and 110199
   and a.Approve_Staff_Id = 'sys110101'
   and to_char(a.Approve_Date, 'yyyy/mm/dd') between '2007/05/01' and
       '2007/05/30'
   and a.Approve_Type = 'DERATE'
   and a.Approve_Type = b.Approve_Type
 order by a.approve_serial_nbr,
          a.ACCT_ID,
          b.acct_month,
          b.acct_item_type_id

1 
2 -------------------------------------------------------------------------------------------
3 | Id  | Operation             |  Name             | Rows  | Bytes | Cost  | Pstart| Pstop |
4 -------------------------------------------------------------------------------------------
5 |   0 | SELECT STATEMENT      |                   |     1 |   734 |    14 |       |       |
6 |   1 |  SORT ORDER BY        |                   |     1 |   734 |    14 |       |       |
7 |   2 |   HASH JOIN           |                   |     1 |   734 |     5 |       |       |
8 |   3 |    PARTITION RANGE ALL|                   |       |       |       |     1 |     2 |
9 |   4 |     TABLE ACCESS FULL | APPROVE_SERIAL_T  |     1 |   544 |     2 |     1 |     2 |
10 |   5 |    PARTITION RANGE ALL|                   |       |       |       |     1 |     2 |
11 |   6 |     TABLE ACCESS FULL | APPROVE_ITEM_T    |     1 |   190 |     2 |     1 |     2 |
12 -------------------------------------------------------------------------------------------
13 
14 Note: cpu costing is off, PLAN_TABLE' is old version

--------------------
察看原来的索引如下:
create index BILL_CC.I1_APPROVE_ITEM_T on BILL_CC.APPROVE_ITEM_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);
create index BILL_CC.I1_APPROVE_SERIAL_T on BILL_CC.APPROVE_SERIAL_T (APPROVE_TYPE, ACCT_ID, APPROVE_SERIAL_NBR);

分析索引的创建应该是不合理的:
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;

create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);

--考虑acct_id被经常用:
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.approve_serial_t(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

--最后确认一下其他情形是否有对原来索引有所依赖?
--最终计划如下:
--------------------------------------------------------
1 
2 -----------------------------------------------------------------------------------------------------------
3 | Id  | Operation                            |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 |   0 | SELECT STATEMENT                     |                    |     1 |   336 |  1623 |       |       |
6 |   1 |  SORT ORDER BY                       |                    |     1 |   336 |  1623 |       |       |
7 |   2 |   HASH JOIN                          |                    |     1 |   336 |  1614 |       |       |
8 |   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T   |     1 |   254 |     2 | ROWID | ROW L |
9 |   4 |     INDEX RANGE SCAN                 | I1_APPROVE_ITEM_T  |    43 |       |     1 |       |       |
10 |   5 |    PARTITION RANGE ALL               |                    |       |       |       |     1 |     2 |
11 |   6 |     TABLE ACCESS FULL                | APPROVE_ITEM_T     |    34 |  2788 |  1611 |     1 |     2 |
12 -----------------------------------------------------------------------------------------------------------
13 
14 Note: cpu costing is off, PLAN_TABLE' is old version

--进一步考虑前台查询组合繁多,approve_type类型不断变化,所以这种
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(Approve_Staff_Id,Approve_Type);
是不能通用的，为什么没走skip index还是值得考虑的？

应该更改为:[ysdb]
drop index BILL_CC.I1_APPROVE_ITEM_T;
drop index BILL_CC.I1_APPROVE_SERIAL_T;
--
create index BILL_CC.I1_APPROVE_ITEM_T on
bill_cc.approve_serial_t(pprove_Type);
--
acct_id索引的建立方式不变.
create index BILL_CC.I2_APPROVE_ITEM_T on
bill_cc.I2_APPROVE_ITEM_T(acct_id);

create index BILL_CC.I1_APPROVE_SERIAL_T on
bill_cc.approve_serial_t(ACCT_ID);

1 
2 -----------------------------------------------------------------------------------------------------------
3 | Id  | Operation                            |  Name              | Rows  | Bytes | Cost  | Pstart| Pstop |
4 -----------------------------------------------------------------------------------------------------------
5 |   0 | SELECT STATEMENT                     |                    |     1 |   336 |  1623 |       |       |
6 |   1 |  SORT ORDER BY                       |                    |     1 |   336 |  1623 |       |       |
7 |   2 |   HASH JOIN                          |                    |     1 |   336 |  1614 |       |       |
8 |   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| APPROVE_SERIAL_T   |     1 |   254 |     2 | ROWID | ROW L |
9 |   4 |     INDEX RANGE SCAN                 | I1_APPROVE_ITEM_T  | 10831 |       |     1 |       |       |
10 |   5 |    PARTITION RANGE ALL               |                    |       |       |       |     1 |     2 |
11 |   6 |     TABLE ACCESS FULL                | APPROVE_ITEM_T     |    34 |  2788 |  1611 |     1 |     2 |
12 -----------------------------------------------------------------------------------------------------------
13 
14 Note: cpu costing is off, PLAN_TABLE' is old version

补充总结:根据v$session_wait可以看到等待事件是db file sequential read，通常来说索引读是好的,会很快，但是如果过多的索引读就令人畏惧的。