一個缺乏索引和統計資訊的優化過程
案例描述：一張表缺乏統計資訊，且缺乏索引導致正常業務無法正常完成，以至於影響生產。
問題描述：本人接到問題通知，遂前往發現前端操作的確是很慢，但是該系統開發商無法提供該模組的具體sql，而客戶端又反應該問題已經持續有半年之久，現已經開始籌劃系統遷移事宜。（由於系統是win server 2003 2GB記憶體 9i的庫）
問題關鍵就是，無法精確確認該模組牽扯到的sql，遂只能在該業務執行期間進行收集相關資訊。
/*分析處理過程*/
--資料庫版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
--業務執行期間發現當前活動會話很少，也就下邊這個sql可疑，遂進行分析
select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
--檢視該sql執行計劃，發現該sql執行時間長約13mins，執行計劃也比較異常（極為不正常）
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
已選擇8503行。
已用時間: 00: 13: 32.60
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE'
2 1 INDEX (FULL SCAN) OF 'IDX_RES_IMSI_DISC_IMSI' (NON-UNIQU
E)
統計資訊
----------------------------------------------------------
103 recursive calls
0 db block gets
6058144 consistent gets
155448 physical reads
0 redo size
215190 bytes sent via SQL*Net to client
6613 bytes received via SQL*Net from client
568 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8503 rows processed
--檢視該表大小
SQL> select segment_name,sum(bytes)/1024/1024 mb from dba_segments where lower(segment_name)='res_imsi_discrete' group by segment_name;
SEGMENT_NAME MB
-------------------- ----------
RES_IMSI_DISCRETE 462
發現在列task_rec_id無索引
檢視錶上次分析時間，發現該表沒有統計資訊
在業務期間我們進行了statspack報告的抓取
對其中的sql相關分析如下：
SQL ordered by Gets for DB: PIMDB Instance: pimdb Snaps: 4 -5
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,016,124 1,156 879.0 3.5 44.17 107.11 1717596217
select count(recycle_id) c from recycle_info where oper_id =:1 a
nd status<>'001' and notified = 0
449,795 23,147 19.4 1.6 3.20 7.93 1500762665
UPDATE res_imsi_discrete t SET t.res_stat = :b4,
t.task_rec_id = :b3, t.modify
_date = SYSDATE WHERE t.imsi = :b2 AND t
.res_stat = :b1
431,448 23,147 18.6 1.5 2.70 8.70 4289263526
UPDATE res_imsi_discrete t SET t.res_stat = :b3, t.mo
dify_date = SYSDATE WHERE t.imsi = :b2 AND t
.res_stat = :b1
151,621 8 18,952.6 0.5 0.95 2.70 2991408070
SELECT t2.imsi, t2.res_stat FROM (SELECT t.imsi, t.res_s
tat FROM res_imsi_discrete t WHER
E t.city_code = :b4 AND t.msisdn_para = :b3
AND t.hlr_code = :b2 AND t.res_st
at = :b1 ORDER BY t.imsi) t2 WHERE rownum
107,116 4 26,779.0 0.4 0.64 7.52 2526016603
SELECT COUNT(*) FROM res_imsi_discrete t WHERE t.
city_code = :b4 AND t.hlr_code = :b3 AND t.msi
sdn_para = :b2 AND t.res_stat = :b1
99,003 4 24,750.8 0.3 0.72 1.09 3154251665
SELECT COUNT(*) FROM res_imsi_discrete t WHERE t.
city_code = :b3 AND t.hlr_code = :b2 AND t.res
_stat = :b1
從以上發現問題sql也基本都和表res_imsi_discrete相關。
具體實施過程如下：
SQL> select count(*) from CDMA_CARDMANAGE_USER.res_imsi_discrete;
COUNT(*)
----------
6341031
SQL> select count(distinct task_rec_id) from CDMA_CARDMANAGE_USER.res_imsi_discrete;
COUNT(DISTINCTTASK_REC_ID)
--------------------------
2463
發現該列的可選性還是很高的。
--建立索引
create index CDMA_CARDMANAGE_USER.idx_res_imsi_disc_tsk_rec_id on CDMA_CARDMANAGE_USER.res_imsi_discrete(task_rec_id) tablespace CDMA_CARDMANAGE_DATA nologging parallel 2;
--收集表統計資訊
begin
DBMS_STATS.GATHER_TABLE_STATS (
wnname => 'CDMA_CARDMANAGE_USER',
tabname => 'RES_IMSI_DISCRETE',
estimate_percent => 100,
degree => 2,
cascade => true);
end;
/
優化後效果
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
已選擇8503行。
已用時間: 00: 00: 00.79
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=129 Card=2030 Bytes=
42630)
1 0 SORT (ORDER BY) (Cost=129 Card=2030 Bytes=42630)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE' (Co
st=119 Card=2030 Bytes=42630)
3 2 INDEX (RANGE SCAN) OF 'IDX_RES_IMSI_DISC_TASKID' (NON-
UNIQUE) (Cost=6 Card=2030)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1218 consistent gets
4 physical reads
0 redo size
215190 bytes sent via SQL*Net to client
6613 bytes received via SQL*Net from client
568 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8503 rows processed
在新增相關索引並收集統計資訊後，之前發現的問題sql執行效率得到大幅提高，從之前的13mins多提高到1s之內，整個業務也從之前的3個小時縮短到20mins之內。可見表的索引和統計資訊是很重要的。
問題描述：本人接到問題通知，遂前往發現前端操作的確是很慢，但是該系統開發商無法提供該模組的具體sql，而客戶端又反應該問題已經持續有半年之久，現已經開始籌劃系統遷移事宜。（由於系統是win server 2003 2GB記憶體 9i的庫）
問題關鍵就是，無法精確確認該模組牽扯到的sql，遂只能在該業務執行期間進行收集相關資訊。
/*分析處理過程*/
--資料庫版本
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
--業務執行期間發現當前活動會話很少，也就下邊這個sql可疑，遂進行分析
select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
--檢視該sql執行計劃，發現該sql執行時間長約13mins，執行計劃也比較異常（極為不正常）
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
已選擇8503行。
已用時間: 00: 13: 32.60
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE'
2 1 INDEX (FULL SCAN) OF 'IDX_RES_IMSI_DISC_IMSI' (NON-UNIQU
E)
統計資訊
----------------------------------------------------------
103 recursive calls
0 db block gets
6058144 consistent gets
155448 physical reads
0 redo size
215190 bytes sent via SQL*Net to client
6613 bytes received via SQL*Net from client
568 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
8503 rows processed
--檢視該表大小
SQL> select segment_name,sum(bytes)/1024/1024 mb from dba_segments where lower(segment_name)='res_imsi_discrete' group by segment_name;
SEGMENT_NAME MB
-------------------- ----------
RES_IMSI_DISCRETE 462
發現在列task_rec_id無索引
檢視錶上次分析時間，發現該表沒有統計資訊
在業務期間我們進行了statspack報告的抓取
對其中的sql相關分析如下：
SQL ordered by Gets for DB: PIMDB Instance: pimdb Snaps: 4 -5
-> End Buffer Gets Threshold: 10000
-> Note that resources reported for PL/SQL includes the resources used by
all SQL statements called within the PL/SQL code. As individual SQL
statements are also reported, it is possible and valid for the summed
total % to exceed 100
CPU Elapsd
Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
1,016,124 1,156 879.0 3.5 44.17 107.11 1717596217
select count(recycle_id) c from recycle_info where oper_id =:1 a
nd status<>'001' and notified = 0
449,795 23,147 19.4 1.6 3.20 7.93 1500762665
UPDATE res_imsi_discrete t SET t.res_stat = :b4,
t.task_rec_id = :b3, t.modify
_date = SYSDATE WHERE t.imsi = :b2 AND t
.res_stat = :b1
431,448 23,147 18.6 1.5 2.70 8.70 4289263526
UPDATE res_imsi_discrete t SET t.res_stat = :b3, t.mo
dify_date = SYSDATE WHERE t.imsi = :b2 AND t
.res_stat = :b1
151,621 8 18,952.6 0.5 0.95 2.70 2991408070
SELECT t2.imsi, t2.res_stat FROM (SELECT t.imsi, t.res_s
tat FROM res_imsi_discrete t WHER
E t.city_code = :b4 AND t.msisdn_para = :b3
AND t.hlr_code = :b2 AND t.res_st
at = :b1 ORDER BY t.imsi) t2 WHERE rownum
107,116 4 26,779.0 0.4 0.64 7.52 2526016603
SELECT COUNT(*) FROM res_imsi_discrete t WHERE t.
city_code = :b4 AND t.hlr_code = :b3 AND t.msi
sdn_para = :b2 AND t.res_stat = :b1
99,003 4 24,750.8 0.3 0.72 1.09 3154251665
SELECT COUNT(*) FROM res_imsi_discrete t WHERE t.
city_code = :b3 AND t.hlr_code = :b2 AND t.res
_stat = :b1
從以上發現問題sql也基本都和表res_imsi_discrete相關。
具體實施過程如下：
SQL> select count(*) from CDMA_CARDMANAGE_USER.res_imsi_discrete;
COUNT(*)
----------
6341031
SQL> select count(distinct task_rec_id) from CDMA_CARDMANAGE_USER.res_imsi_discrete;
COUNT(DISTINCTTASK_REC_ID)
--------------------------
2463
發現該列的可選性還是很高的。
--建立索引
create index CDMA_CARDMANAGE_USER.idx_res_imsi_disc_tsk_rec_id on CDMA_CARDMANAGE_USER.res_imsi_discrete(task_rec_id) tablespace CDMA_CARDMANAGE_DATA nologging parallel 2;
--收集表統計資訊
begin
DBMS_STATS.GATHER_TABLE_STATS (
wnname => 'CDMA_CARDMANAGE_USER',
tabname => 'RES_IMSI_DISCRETE',
estimate_percent => 100,
degree => 2,
cascade => true);
end;
/
優化後效果
SQL> select IMSI from CDMA_CARDMANAGE_USER.res_imsi_discrete where task_rec_id = 24958 order by IMSI;
已選擇8503行。
已用時間: 00: 00: 00.79
執行計劃
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=129 Card=2030 Bytes=
42630)
1 0 SORT (ORDER BY) (Cost=129 Card=2030 Bytes=42630)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'RES_IMSI_DISCRETE' (Co
st=119 Card=2030 Bytes=42630)
3 2 INDEX (RANGE SCAN) OF 'IDX_RES_IMSI_DISC_TASKID' (NON-
UNIQUE) (Cost=6 Card=2030)
統計資訊
----------------------------------------------------------
0 recursive calls
0 db block gets
1218 consistent gets
4 physical reads
0 redo size
215190 bytes sent via SQL*Net to client
6613 bytes received via SQL*Net from client
568 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
8503 rows processed
在新增相關索引並收集統計資訊後，之前發現的問題sql執行效率得到大幅提高，從之前的13mins多提高到1s之內，整個業務也從之前的3個小時縮短到20mins之內。可見表的索引和統計資訊是很重要的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26143577/viewspace-748592/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵匯出索引資料和統計資訊嗎索引
- SQL優化之統計資訊和索引SQL優化索引
- 增加複合索引優化SQL的簡單過程索引優化SQL
- 一條sql的優化過程SQL優化
- 一個複合索引的優化案例索引優化
- 一條SQL語句的優化過程SQL優化
- Mysql索引優化（一）MySql索引優化
- 資料庫索引設計與優化讀書筆記--《三》SQL處理過程資料庫索引優化筆記SQL
- 一次效能優化調整過程.優化
- 效能優化的過程學習優化
- 關於索引聚簇因子（CLUSTERING_FACTOR）引起的sql優化過程索引SQL優化
- 優化同事發過來的一個sql優化SQL
- 分享工作中一次優化程式的過程優化
- pl/sql儲存過程優化一例SQL儲存過程優化
- 字首索引，一種優化索引大小的解決方案索引優化
- 資訊圖：程式語言的進化過程
- 理解索引：索引優化索引優化
- Oracle 索引的優化Oracle索引優化
- Oracle優化案例-儲存過程的優化思路（二十三）Oracle優化儲存過程
- MySQL效能優化之索引設計MySql優化索引
- 4個優化方法，讓你能瞭解join計算過程更透徹優化
- Laravel 專案深度優化過程Laravel優化
- iOS效能優化過程淺析iOS優化
- 企業資訊化管理優化的幾個方面優化
- MSSQL優化之索引優化SQL優化索引
- oracle索引核心過程Oracle索引
- 記錄一個sql最佳化的全過程.txtSQL
- Mysql索引優化之索引的分類MySql索引優化
- 【MySQL】MySQL的執行計劃及索引優化MySql索引優化
- 又一個複合索引的SQL調優索引SQL
- 前端效能優化之http請求的過程前端優化HTTP
- 分析執行計劃優化SQLSQL語句處理的過程(轉)優化SQL
- Elasitcsearch索引優化索引優化
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- SQL優化-索引SQL優化索引
- 一個sql的優化SQL優化
- 複合索引與函式索引優化一例索引函式優化