電商戰場

在實際生產中，經常會遇到業務人員反映系統緩慢的情況，如下為查找某生產系統數據庫緩慢的具體過程實例，僅供參考。

一、系統壓力分析
( 一) 查找壓力較大對象
1 、查看系統主要等待事件
SQL>select * from (select event, total_waits, time_waited from v$system_event where wait_class <> 'Idle' order by 3 desc) where rownum <=20 order by 2 desc

EVENT TOTAL_WAITS TIME_WAITED
----------------------------------------------- --- ----------- -----------
SQL*Net message to client 2.0643E+10 1323522
db file sequential read 235600910 33275975

latch: cache buffers chains 202847583 2840368
gc cr grant 2-way 29085511 1745696
gc current block 2-way 28103729 2507063 gc cr block 2-way 25276675 924545
gc buffer busy 24145520 1355858
read by other session 17640501 817963
log file parallel write 12323631 2457540
log file sync 10734330 3032174
control file sequential read 10176316 797551
db file parallel write 9183288 1861749
db file scattered read 4934995 688562
wait for scn ack 1456277 445701
control file parallel write 1279047 530474
resmgr:cpu quantum 1150179 829006
log file sequential read 680779 478533
Log archive I/O 670148 730909
enq: TX - row lock contention 20917 728991

分析：SQL*Net message to client 指客戶端和數據庫端已建立連接，在等待client 信息，此類等待最多，說明系統總體較閒。db file sequential read 在把數據塊讀入SGA 時發生，基本都是由索引的讀取引起，對於廣泛建立了索引的oltp 系統，此類等待正常。latch: cache buffers chains大量存在說明系統中存在數據熱塊。gc xxxxxx 指RAC 節點之間資源通信等待。

2 、查看引起熱塊爭用的原因
SQL> l
  1* select * from (select addr, child#, gets, sleeps from v$latch_children where name='cache buffers chains'

order by sleeps desc) where rownum <= 20 order by 3 desc

ADDR CHILD# GETS SLEEPS
---------------- ---------- ---------- --------- -
C000000362551948 166066 804093582 35784
C0000003640C43C8 206488 105437366 39527
C000000666B1CAE8 90644 101476708 38913
C000000365943590 242564 98760814 35775
C000000668105988 122910 96515804 38295
C00000036168A4C0 144302 94126690 40158
C000000364196388 207696 93866516 35470
C000000667993228 111943 93715816 35730
C0000006639A0850 17768 93450590 36252
C0000006675E73F0 106536 93360102 37449
C000000361C345F0 152644 91495420 35793
C000000663C7F770 21996 90960197 39332
C00000066664F058 83570 90138407 35438
C000000663ECBB30 25380 88815736 36081
C000000665118E40 52332 88078863 37253
C00000066466D9D8 36620 86177540 35698
C000000363E42B90 202798 85494158 37472
C000000664C4B248 45258 82603677 35361
C000000663C6F4C8 21903 80551198 37328
C000000663AFAC90 19760 79216265 37235
分析：latch 地址為"C000000362551948" 的對象發生嚴重的熱塊爭用。

3 、檢查傾斜嚴重的latch 地址“ C000000362551948 ”對應的熱塊對象
SQL> l
  1* select hladdr, obj, (select object_name from dba_objects where (data_object_id is null and object_id=x.obj) or data_object_id=x.obj and rownum=1) as object_name, dbarfil, dbablk, tch from x$bh x where hladdr='C000000362551948' order by hladdr, obj
SQL> /

HLADDR OBJ OBJECT_NAME DBARFIL DBABLK TCH
---------------- ---------- ------------------ -- ---------- ---------- ----------
C000000362551948 32593 I_FD_GLDTL_INSIDE_TM 14 1661489 0
                        P_2
C000000362551948 32740 GLRGT_FIXEDFEE 48 1474476 0
C000000362551948 32798 LNCTL_INT 75 556438 2
C000000362551948 32817 LNMST_DTL 75 150208 1
C000000362551948 32921 MCRGT_BILL 76 162290 5
C000000362551948 32991 MONTH_GNL_DTL_TOT 51 1266163 0
C000000362551948 32991 MONTH_GNL_DTL_TOT 64 488839 0
C000000362551948 33203 SE_RCV_PKG_INF 80 678046 156
C000000362551948 33203 SE_RCV_PKG_INF 79 1211942 111

SQL> l
  1* select owner,object_name,object_id,object_type from dba_objects where object_name='SE_RCV_PKG_INF'
SQL> /

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
------------------------------ ---------------- ---- ---------- -------------------
SIM SE_RCV_PKG_INF 33203 TABLE

分析：對象SE_RCV_PKG_INF 存在嚴重的TCH(Touch Count) ，為熱點對象，該對象為SIM 用戶的表。

( 二) 查找壓力較大sql 語句

1 、查看系統中的鎖、閂情況
SQL> select sid,type,lmode,id1,id2,request,block from v$lock where type in ('TM','TX') order by 1,2;
   SID TY LMODE ID1 ID2 REQUEST BLOCK
---------- -- ---------- ---------- ---------- --- ------- ----------
   2478 TM 3 32608 0 0 2
   5458 TM 3 58400 0 0 2
   5458 TM 3 33203 0 0 2
   5458 TX 6 1966109 776530 0 2

說明：上述為系統中經常出現的鎖、閂情況。
2 、查看經常出現的鎖、閂對應的對象
SQL> l
  1* select OWNER,OBJECT_NAME ,OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID=33203
SQL> /

OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE
---------- -------------------- ---------- ------ -------------
SIM SE_RCV_PKG_INF 33203 TABLE

3 、查看鎖、閂對應的sql 語句
SQL> select hash_value,sql_text
  2 from v$sqltext
  3 where hash_value=
  4 (SELECT sql_hash_value FROM V$SESSION WHERE SID=(5458))
  5 order by PIECE;

HASH_VALUE SQL_TEXT
----------- ------------------------------------- -------------------------------------------------- ---------

1980404232 update se_snd_pkg_inf set pkg_state=:b0,mb_no=:b1,node_prc_date
1980404232 =:b2,node_type=:b3,msg_str=:b4 where ((unit_code=:b5 and pkg_ser
1980404232 ial_no= :b6) and pkg_bill_date=:b7)
SQL> /

HASH_VALUE SQL_TEXT
----------- ------------------------------------- -------------------------------------------------- ---------
  526633362 update se_rcv_pkg_inf set add_inf=empty_blob() where (((unit_co
  526633362 de=:b0 and msg_serial_no=:b1) and pkg_bill_date=:b2) and pkg_snd
  526633362 _bkno=:b3)
分析：從sql 角度查找到的壓力較大的對象為se_rcv_pkg_inf 、se_snd_pkg_inf 。

二、索引使用情況分析
( 一) 查看索引建立情況
1 、查看壓力較大對像上建立的索引
SQL> select index_name from dba_indexes where TABLE_NAME='SE_SND_PKG_INF';

INDEX_NAME
-----------------------------
SYS_IL0000033224C00030$$
I_SE_SND_PKG_INF_1
I_SE_SND_PKG_INF_2
I_SE_SND_PKG_INF_3

SQL> l
  1* select OWNER,INDEX_NAME from dba_indexes where table_name='SE_RCV_PKG_INF'
SQL> /
OWNER INDEX_NAME
---------- ------------------ ------------
SIM I_SE_RCV_PKG_INF_IDX_1
SIM SYS_IL0000033203C00028$$
SIM I_SE_RCV_PKG_INF_IDX
SIM I_SE_RCV_PKG_INF_IDX_2
SIM I_SE_RCV_PKG_INF_IDX_3

2 、查看系統最近建立的索引
SQL>select OBJECT_NAME,CREATED from dba_objects where object_type='INDEX' order by 2
SQL>
I_LNRGT_LOAN_2 2012-09-22:08:56:13
I_SE_SND_PKG_INF_3 2013-02-21:19:45: 32
I_SE_RCV_PKG_INF_IDX_2 2013-02-22:14:34:37
I_SE_RCV_PKG_INF_IDX_3 2013-03-12:16:50:03

( 二) 分析索引使用情況

1 、查看壓力較大sql 語句使用的索引
SQL> l
  1* select * from table(dbms_xplan.display_cursor('1980404232'))
SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------- -------------------------------------------------- ---------
HASH_VALUE 1980404232, child number 0
----------------------------------- ---
update se_snd_pkg_inf set pkg_state=:b0,mb_no=:b1,node_prc_date=:b2,nod
e_type=:b3,msg_str=:b4 where ((unit_code=:b5 and pkg_serial_no=:b6) and
pkg_bill_date=:b7)

Plan hash value: 4167090593
--------------------------------------------- --------------
| Id | Operation | Name |
----------------------------- ------------------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | SE_SND_PKG_INF |
|* 2 | TABLE ACCESS BY INDEX ROWID| SE_SND_PKG_INF |
|* 3 | INDEX RANGE SCAN | I_SE_SND_PKG_INF_1 |
---------------------------------- -------------------------
Predicate Information (identified by operation id):
---------------- -----------------------------------
2 - filter(("PKG_BILL_DATE"=:B7 AND "PKG_SERIAL_NO" =:B6))
3 - access("UNIT_CODE"=:B5)
Note------ rule based optimizer used (consider using cbo)
27 rows selected.

SQL> l
  1* select * from table(dbms_xplan.display_cursor('526633362'))
SQL> /

PLAN_TABLE_OUTPUT
------------------------------------------------- -------------------------------------------------- ---------
HASH_VALUE 526633362, child number 0
----------------------------------- --
update se_rcv_pkg_inf set add_inf=empty_blob() where (((unit_code=:b0
and msg_serial_no=:b1) and pkg_bill_date=:b2) and pkg_snd_bkno=:b3)
Plan hash value: 1797383972
--------- -------------------------------------------------- --
| Id | Operation | Name |
----------------------------------------- --------------------
| 0 | UPDATE STATEMENT | |
| 1 | UPDATE | SE_RCV_PKG_INF |
|* 2 | TABLE ACCESS BY INDEX ROWID| SE_RCV_PKG_INF |
|* 3 | INDEX RANGE SCAN | I_SE_RCV_PKG_INF_IDX |
-------------------------------------------- -----------------
Predicate Information (identified by operation id):
------------------------ ---------------------------
2 - filter(("PKG_SND_BKNO"=:B3 AND "PKG_BILL_DATE"=:B2 AND
            "MSG_SERIAL_NO"= :B1))
3 - access("UNIT_CODE"=:B0)
Note------ rule based optimizer used (consider using cbo)
27 rows selected.
分析：壓力較大的sql 語句走的是索引I_SE_SND_PKG_INF_1 、I_SE_RCV_PKG_INF_IDX 。

2 、查看新建索引是否被使用
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USE START_MONITORING
---------------------------- -- ------------------------------ --- --- ------------ -------
I_SE_RCV_PKG_INF_IDX_2 SE_RCV_PKG_INF YES NO 12/25/2012 09:03:08
I_SE_RCV_PKG_INF_IDX_3 SE_RCV_PKG_INF YES YES 12/25/2012 09:58:44
I_SE_SND_PKG_INF_3 SE_SND_PKG_INF YES YES 12/25/2012 10:32:34
分析：索引I_SE_RCV_PKG_INF_IDX_2 沒有被使用，其餘兩個索引在使用。

3 、查看哪個sql 在使用I_SE_RCV_PKG_INF_IDX_3 索引
SQL>select object_name,hash_value from v$sql_plan where object_name='I_SE_RCV_PKG_INF_IDX_3'
SQL> /

OBJECT_NAME HASH_VALUE
------------------------------ ----------
I_SE_RCV_PKG_INF_IDX_3 2804302989

SQL> select * from table(dbms_xplan.display_cursor('2804302989'));
PLAN_TABLE_OUTPUT
-------------------------------- ------------------------------------------------
HASH_VALUE 2804302989 , child number 0
--------------------------------------
select count(*) into :b0 from se_rcv_pkg_inf where ((((unit_code=:b1
and pkg_type=:b2) and pkg_bill_date=:b3) and msg_serial_no=:b4) and
pkg_snd_bkno=:b5)
Plan hash value: 192728261
---------- ------------------------------------------
| Id | Operation | Name |
- -------------------------------------------------- -
PLAN_TABLE_OUTPUT
------------------------------------------------ --------------------------------
| 0 | SELECT STATEMENT | |
| 1 | SORT AGGREGATE | |
|* 2 | INDEX RANGE SCAN| I_SE_RCV_PKG_INF_IDX_3 |
-------------------------------------------- --------
Predicate Information (identified by operation id):
--------------------------------- ------------------
2 - access("UNIT_CODE"=:B1 AND "PKG_TYPE"=:B2 AND
            "PKG_BILL_DATE"=:B3 AND "MSG_SERIAL_NO"=:B4 AND "PKG_SND_BKNO"=:B5)
PLAN_TABLE_OUTPUT
------------------------------------------ --------------------------------------
Note----- rule based optimizer used (consider using cbo)

分析：使用I_SE_RCV_PKG_INF_IDX_3 索引的語句是：
select count(*) into :b0 from se_rcv_pkg_inf where ((((unit_code=:b1
and pkg_type=:b2) and pkg_bill_date=:b3) and msg_serial_no=:b4) and pkg_snd_bkno=: b5)

4 、查看哪個sql 在使用I_SE_SND_PKG_INF_3 索引
SQL> l
  1 select object_name,hash_value from v$sql_plan where object_name='I_SE_SND_PKG_INF_3'
OBJECT_NAME HASH_VALUE
--------------------- --------- ----------
I_SE_SND_PKG_INF_3 1930826778
I_SE_SND_PKG_INF_3 1852212556
I_SE_SND_PKG_INF_3 1656403754
I_SE_SND_PKG_INF_3 1850656125
I_SE_SND_PKG_INF_3 1992237047
I_SE_SND_PKG_INF_3 2737515688
I_SE_SND_PKG_INF_3 3976299320
I_SE_SND_PKG_INF_3 2632968900

查找方法同上，略。
分析：使用I_SE_SND_PKG_INF_3 索引的語句是：
select count(*) into :b0 from se_snd_pkg_inf where (pkg_state='2b' and pkg_bill_date=:b1)
select count(*) into :b0 from se_snd_pkg_inf where (pkg_state='00' and pkg_bill_date=:b1)
select count(*) into :b0 from se_snd_pkg_inf where (pkg_state='1e' and pkg_bill_date=:b1)
select count(*) into :b0 from se_snd_pkg_inf where (pkg_state='1s' and pkg_bill_date=:b1 )
select count(*) into :b0 from se_snd_pkg_inf where (pkg_state='0s' and pkg_bill_date=:b1)

update se_snd_pkg_inf a set pkg_state=:b0 where ((pkg_bill_date<(select sys_date from se_unit_pmt where unit_code=a.unit_code) and pkg_busi_num<:b1) and pkg_state='0g')

select * from se_snd_pkg_inf where pkg_state='00' and pkg_type!='004' and pkg_type!='010' and pkg_type!='323' and unit_code not in ('620000101') and rownum<6 for update

select * from se_snd_pkg_inf where pkg_state='00' and pkg_type!='004' and pkg_type!='010' and pkg_type!='323' and rownum<6 for update

5 、分析SE_RCV_PKG_INF 表的索引

SQL> select dbms_metadata.get_ddl('TABLE','SE_RCV_PKG_INF','SIM') from dual;
DBMS_METADATA.GET_DDL('TABLE','SE_RCV_PKG_INF','SIM')
------------ -------------------------------------------------- ------------------
  CREATE TABLE "SIM"."SE_RCV_PKG_INF"
( "UNIT_CODE" VARCHAR2(9) NOT NULL ENABLE,
      "PKG_SND_BKNO" VARCHAR2(12) NOT NULL ENABLE,
      "PKG_SERIAL_NO" NUMBER(12,0) NOT NULL ENABLE,
      "PKG_BILL_DATE" VARCHAR2(8) NOT NULL ENABLE,
      "PKG_TYPE" VARCHAR2(3) NOT NULL ENABLE,
      "CMT_PKG_TYPE" CHAR(1) NOT NULL ENABLE,
      "OLD_PKG_SERIAL_NO" NUMBER (12,0),
      "OLD_PKG_BILL_DATE" VARCHAR2(8),
      "OLD_PKG_TYPE" VARCHAR2(3),
      "OLD_PKG_SND_BKNO" VARCHAR2(12),
      "PKG_RCV_BKNO" VARCHAR2(12) NOT NULL ENABLE,
      "PKG_SECRET" VARCHAR2(40),
      " DETAIL_FLAG" CHAR(1) NOT NULL ENABLE,
      "PKG_BUSI_NUM" NUMBER(6,0),
      "PKG_BUSI_AMT" NUMBER(16,2),
      "PKG_SUCC_NUM" NUMBER(6,0),
      "PKG_SUCC_AMT" NUMBER(16,2),
      "PKG_BACK_DATE" VARCHAR2(8),
      "NODE_TYPE" CHAR(1),
      "NODE_PRC_DATE" VARCHAR2(8),
      "MB_NO" NUMBER(6,0),
      "RSND_FLAG" CHAR(1) NOT NULL ENABLE,
      "PKG_CLR_DATE" VARCHAR2( 8) NOT NULL ENABLE,
      "PKG_BUSI_TYPE" VARCHAR2(5),
      "PKG_STATE" VARCHAR2(2) NOT NULL ENABLE,
      "MSG_SERIAL_NO" VARCHAR2(20) NOT NULL ENABLE,
      "MSG_REF_SERIAL" VARCHAR2(20),
      "ADD_INF" BLOB,
      " CHK_FLAG" CHAR(1) NOT NULL ENABLE,
      "ACCOUNT" VARCHAR2(32)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE " TS_TAB_PES_01"
LOB ("ADD_INF") STORE AS (
  TABLESPACE "TS_TAB_PES_01" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
  NOCACHE LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

SQL> select count(*) from SIM.SE_RCV_PKG_INF;
  COUNT(*)
----------
6313917
SQL> l
  1* select dbms_metadata.get_ddl('INDEX','I_SE_RCV_PKG_INF_IDX','SIM') from dual
SQL> /

DBMS_METADATA.GET_DDL('INDEX','I_SE_RCV_PKG_INF_IDX','SIM')
---------------------------------- ----------------------------------------------
  CREATE INDEX "SIM "."I_SE_RCV_PKG_INF_IDX" ON "SIM"."SE_RCV_PKG_INF" ("UNIT_CODE")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_TAB_JNLBK_01"

SQL> select count(distinct unit_code) from SIM.SE_RCV_PKG_INF;
COUNT(DISTINCTUNIT_CODE)
------------------------
                  33

6 、分析SE_SND_PKG_INF 表的索引
SQL> select count(*) from SIM.SE_SND_PKG_INF;
  COUNT(*)
----------
2317812
SQL> select count(distinct unit_code) from SIM.SE_SND_PKG_INF;
COUNT(DISTINCTUNIT_CODE )
------------------------
                  33

SQL> select dbms_metadata.get_ddl('INDEX','I_SE_SND_PKG_INF_1','SIM') from dual;

DBMS_METADATA.GET_DDL('INDEX','I_SE_SND_PKG_INF_1','SIM')
---------------------------------- --------------------------------------------
  CREATE UNIQUE INDEX "SIM" ."I_SE_SND_PKG_INF_1" ON "SIM"."SE_SND_PKG_IN
F" ("UNIT_CODE", "MSG_SERIAL_NO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "TS_IDX_BIG_01"

三、綜合分析及建議
( 一) 分析
1 、索引I_SE_RCV_PKG_INF_IDX_2 沒有使用，索引I_SE_RCV_PKG_INF_IDX_3 、I_SE_SND_PKG_INF_3 在用。
2 、壓力較大的sql 語句涉及到的表使用的是B-tree 類型的索引，這兩個表建立索引的列重複值多，可選擇性不好，B-tree 類型索引效率較低。又因為這兩個表上存在大量dml 操作，也不建議使用開銷較大的位圖索引。

( 二) 建議
1 、建議刪除沒有使用的I_SE_RCV_PKG_INF_IDX_2 索引。因為對錶進行dml 操作會對索引進行更新，增加系統開銷。
2 、建議對存在熱點的表進行優化，優化思路可以考慮對該表調整為分區表，並在分區表上建立本地分區索引。另外還可以考慮調整該表的pctfree 參數，或將該表建立在block 值較低的表空間上，減少熱塊的爭用。

電商戰場

2014年1月20日星期一

人為什麼要跟著你混?

2013年10月30日星期三

實例分析某生產系統數據庫運行緩慢原因

2014年1月20日 星期一

人為什麼要跟著你混?

2013年10月30日 星期三

實例分析某生產系統數據庫運行緩慢原因

2014年1月20日星期一

2013年10月30日星期三