案例一：缺少合適索引導致全表掃描
1. 問題 SQL
SQL 用于根據一組手機號查詢通話記錄，條件包括 STATUS=1 以及多達 25 個 PHONE_NO。
SELECT PHONE_NO,
OUT_NET,
IN_NET,
OWN_NET,
TO_CHAR(STARTTIME, 'YYYY-MM-DD HH24:MI:SS') STARTTIME,
TRANSTYPE,
ZONE
FROM LOG_REC_CALL
WHERE STATUS = 1
AND (PHONE_NO IN ('19216811172','19216811173','19216811174', ... , '19216811175'));優化前執行計劃走TABLE ACCESS FULL SQL執行時間接近 1830ms，邏輯讀97,033塊次，每分鐘10次。
Elapsed: 00:00:03.19
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5sjhxscqduqf8, child number 0
-------------------------------------
SELECT PHONE_NO,OUT_NET,IN_NET,OWN_NET,TO_CHAR(STARTTIME,'YYYY-MM
-DD HH24:MI:SS') STARTTIME,TRANSTYPE,ZONE FROM LOG_REC_CALL WHERE
STATUS=1 and ((PHONE_NO=:1 )or(PHONE_NO=:2 )or(PHONE_NO=:3 )or(PHONE_NO=:4
)or(PHONE_NO=:5 )or(PHONE_NO=:6 )or(PHONE_NO=:7 )or(PHONE_NO=:8 )or(PHONE_NO=:9
)or(PHONE_NO=:10 )or(PHONE_NO=:11 )or(PHONE_NO=:12 )or(PHONE_NO=:13
)or(PHONE_NO=:14 )or(PHONE_NO=:15 )or(PHONE_NO=:16 )or(PHONE_NO=:17
)or(PHONE_NO=:18 )or(PHONE_NO=:19 )or(PHONE_NO=:20 )or(PHONE_NO=:21
)or(PHONE_NO=:22 )or(PHONE_NO=:23 )or(PHONE_NO=:24 )or(PHONE_NO=:25 ))
Plan hash value: 654706584
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11842 (100)| | | |
| 1 | PARTITION RANGE ALL| | 13 | 715 | 11842 (2)| 00:00:01 | 1 | 13 |
|* 2 | TABLE ACCESS FULL | LOG_REC_CALL | 13 | 715 | 11842 (2)| 00:00:01 | 1 | 13 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter((TO_NUMBER("STATUS")=1 AND INTERNAL_FUNCTION("PHONE_NO")))
Statistics
----------------------------------------------------------
13 recursive calls
3 db block gets
97611 consistent gets
0 physical reads
832 redo size
1414 bytes sent via SQL*Net to client
1148 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed電話號碼INTERNAL_FUNCTION("PHONE_NO")選擇性很好的列走全表掃描，查看索引信息PK_LOG_REC_CALL索引第二例上存在PHONE_NO字段，使用該索引也可以會走INDEX SKIP SCAN，并且前導列ZONE選擇性不是很好，一定會產生很多IO操作，建議在PHONE_NO和STATUS上建立聯合索引。
TABLE TABLE Index COLUMN Col
OWNER NAME Name UCPTDVS NAME Pos DESC
--------------- ----------------------------------- ------------------------------ ------- ------------------------- ---- ----
PANDA LOG_REC_CALL IDX_LOG_REC_CALL_ENDTIME NNNNNVY ENDTIME 1 ASC
IDX_REC_LNPDB_STARTTIME NNYNNVO STARTTIME 1 ASC
PK_LOG_REC_CALL UNYNNVO ZONE 1 ASC
UNYNNVO PHONE_NO 2 ASC
UNYNNVO STARTTIME 3 ASC
--建索引
CREATE INDEX PANDA.IDX_REC_LNPDB_PHONE_NO_STATUS ON PANDA.LOG_REC_CALL(PHONE_NO, STATUS) local parallel 20;
alter index PANDA.IDX_REC_LNPDB_PHONE_NO_STATUS noparallel; 2. 優化措施
1.收集統計信息
EXECUTE DBMS_STATS.GATHER_TABLE_STATS(
'PANDA', 'LOG_REC_CALL',
method_opt => 'FOR ALL COLUMNS SIZE 1',
estimate_percent => 10,
degree => 15,
no_invalidate => FALSE,
cascade => TRUE
);
2.建立索引
create index IDX_NP_REC_LNPDB_TELNUM on IDX_LOG_REC_CALL_PHONE_NO (PHONE_NO) local parallel 20;
alter index IDX_NP_REC_LNPDB_TELNUM noparallel;最后只建了PHONE_NO字段的索引，執行計劃訪問成功走建立的索引IDX_LOG_REC_CALL_PHONE_NO方式變為走INDEX RANGE SCAN
create index IDX_NP_REC_LNPDB_TELNUM on IDX_LOG_REC_CALL_PHONE_NO (PHONE_NO) local parallel 20;
alter index IDX_NP_REC_LNPDB_TELNUM noparallel;
-- 測試環境測試
SQL> SELECT PHONE_NO,OUT_NET,IN_NET,OWN_NET,TO_CHAR(STARTTIME,'YYYY-MM-DD HH24:MI:SS') STARTTIME,TRANSTYPE,ZONE FROM LOG_REC_CALL WHERE STATUS=1 and ((PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172'
2 )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172'
3 )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' )or(PHONE_NO='19216811172' ));
-- 手機號已經脫敏
6 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 548623128
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 1430 | 26 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE ALL | | 26 | 1430 | 26 (0)| 00:00:01 | 1 | 13 |
| 2 | INLIST ITERATOR | | | | | | | |
| 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| LOG_REC_CALL | 26 | 1430 | 26 (0)| 00:00:01 | 1 | 13 |
|* 4 | INDEX RANGE SCAN | IDX_LOG_REC_CALL_PHONE_NO | 12 | | 20 (0)| 00:00:01 | 1 | 13 |
---------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR
"PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172'
OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR
"PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172'
OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR
"PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172' OR "PHONE_NO"='19216811172')
filter(TO_NUMBER("STATUS")=1)
Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
600 consistent gets
360 physical reads
0 redo size
1434 bytes sent via SQL*Net to client
1148 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
-- 生產實施后
FLAG SNAP_ID INST_ID Hash Value Execs READS READS_PER GETS GETS_PER ROWS_PROCESSED ROWS_PER ELAP_MS ELAP_PER_MS
--------------- ---------- ---------- ---------- -------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- -----------
07 15:00--15:30 71967 1 654706584 177 0 0 17491467 98821.8475 4419 24.9661017 326520.581 1844.74905
07 16:00--16:30 71969 1 654706584 315 0 0 31023869 98488.473 7950 25.2380952 575538.227 1827.10548
07 17:00--17:30 71971 1 654706584 419 0 0 41308911 98589.2864 10600 25.2983294 778587.712 1858.20456
07 18:00--18:30 71973 1 654706584 327 0 0 32039263 97979.3976 8200 25.0764526 599218.593 1832.47276
07 19:00--19:30 71975 1 654706584 396 0 0 38757595 97872.7146 5119 12.9267677 716738.308 1809.94522
07 20:00--20:30 71977 1 654706584 123 0 0 12159324 98856.2927 3089 25.1138211 222450.552 1808.54107
GV$SQL 0 1 654706584 72,230 292829 4.05411879 2744663660 37998.9431 1682732 23.2968573 131756541 1824.1249
7 rows selected. 3. 優化效果對比
執行時間從：1830ms → 1824ms，邏輯讀：97,033 → 37998，執行計劃走 INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID，預期效果和測試有些差距，后面再看看。
指標 | 優化前 | 測試 | 優化后 |
執行時間 | 1830 ms | 3.82ms | 1824ms |
邏輯讀 | 97,033 | 600 | 37998 |
執行計劃 | TABLE ACCESS FULL | INDEX RANGE SCAN | INDEX RANGE SCAN |
案例二：索引設計不合理 - 使用 INDEX SKIP SCAN，效率低
1.問題SQL
該SQL執行時間接近 17.86s，邏輯讀790w塊次
SELECT ZONE,
TASK_ID,
PHONE_NO,
MEMBER_ID,
TEMPLATE_ID,
CREATOR_ID,
CREATOR_DEPT_ID,
CONTACT_MODE,
CURRENT_USER_ID,
CURRENT_DEPT_ID,
CURRENT_ROLE_ID,
CURRENT_GRID_ID,
TASK_STATUS,
IS_GROUP,
TO_CHAR(CREATION_TIME, 'YYYY-MM-DD HH24:MI:SS') CREATION_TIME,
TO_CHAR(STATUS_TIME, 'YYYY-MM-DD HH24:MI:SS') STATUS_TIME,
TO_CHAR(FIRST_CITY_TIME, 'YYYY-MM-DD HH24:MI:SS') FIRST_CITY_TIME
FROM TASK_RETENTION_ORDER T
WHERE T.ZONE = 316
AND MOD(T.PHONE_NO, :1) = :2
AND T.TEMPLATE_ID = :3
AND NOT EXISTS (SELECT 1
FROM TASK_RETENTION_PROPERTY P
WHERE P.TASK_ID = T.TASK_ID
AND P.PROPERTY_ID = 'callBackStatus')分析執行計劃
1、TASK_RETENTION_PROPERTY表的在子查詢中且過濾列TASK_ID和PROPERTY_ID選擇性都很好可以建立復合索引。
2、驅動表上存在索引跳躍掃描，電話號本來是比較有選擇率的,但是 MOD(T.PHONE_NO, :1) 函數在謂詞中導致了 INDEX SKIP SCAN
3、當前執行計劃為嵌套循環關聯 Nested Loops Anti Join ,每次執行被驅動表都要 全分區掃描。
10 rows selected.
Elapsed: 00:00:17.15
Execution Plan
----------------------------------------------------------
Plan hash value: 487581917
------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117 | 22230 | 181 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS ANTI | | 117 | 22230 | 181 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ITERATOR | | 118 | 16284 | 180 (0)| 00:00:01 | 85 | 99 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TASK_RETENTION_ORDER | 118 | 16284 | 180 (0)| 00:00:01 | 85 | 99 |
|* 4 | INDEX SKIP SCAN | IDX_REVERSERETENT_PHONE_NO | 100 | | 104 (0)| 00:00:01 | 85 | 99 |
| 5 | PARTITION RANGE ALL | | 1 | 52 | 1 (0)| 00:00:01 | 1 | 182 |
|* 6 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TASK_RETENTION_PROPERTY | 1 | 52 | 1 (0)| 00:00:01 | 1 | 182 |
|* 7 | INDEX RANGE SCAN | IDX_PERTYID_PROPERTY | 1 | | 1 (0)| 00:00:01 | 1 | 182 |
------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."TEMPLATE_ID"='89166069805870')
4 - access("T"."ZONE"=316)
filter("T"."ZONE"=316 AND MOD(TO_NUMBER("T"."PHONE_NO"),5)=0)
6 - filter("P"."TASK_ID"="T"."TASK_ID") <<<<<
7 - access("P"."PROPERTY_ID"='callBackStatus')
Statistics
----------------------------------------------------------
2 recursive calls
0 db block gets
8989202 consistent gets
0 physical reads
0 redo size
3102 bytes sent via SQL*Net to client
1093 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed 2.優化措施
2.1. 被驅動表索引優化
原來 TASK_RETENTION_PROPERTY 沒有覆蓋 (TASK_ID, PROPERTY_ID),在被驅動表 TASK_RETENTION_PROPERTY 建立索引 IDX_LRP_TASK_ID_PROPERTY_ID。新建索引后：邏輯讀從 790w -> 3w，執行時間從 17.86s -> 0.18s
SQL> select * from (select TASK_ID,property_id,count(*) from PANDA.TASK_RETENTION_PROPERTY group by TASK_ID,property_id order by count(*) desc) where rownum<=10;
TASK_ID PROPERTY_ID COUNT(*)
-------------------------------- -------------------------
314250329036661767 NoTouchSms 6
314250604858165369 NoTouchSms 4
316250617192426223 NoTouchSms 4
316250529269872620 NoTouchSms 4
314250329885935884 NoTouchSms 3
314250423971646441 NoTouchSms 3
314241230792713178 downOther 2
314250418655167445 NoTouchSms 2
314250331721223897 NoTouchSms 2
314250126254387170 downOther 2
10 rows selected.
CREATE INDEX PANDA.IDX_LRP_TASK_ID_PROPERTY_ID ON PANDA.TASK_RETENTION_PROPERTY(TASK_ID,PROPERTY_ID) PARALLEL 10;
ALTER INDEX PANDA.IDX_LRP_TASK_ID_PROPERTY_ID NOPARALLEL;
SQL> /
10 rows selected.
Elapsed: 00:00:00.18
Execution Plan
----------------------------------------------------------
Plan hash value: 861330978
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 117 | 22230 | 274 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS ANTI | | 117 | 22230 | 274 (0)| 00:00:01 | | |
| 2 | PARTITION RANGE ITERATOR | | 118 | 16284 | 180 (0)| 00:00:01 | 85 | 99 |
|* 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| TASK_RETENTION_ORDER | 118 | 16284 | 180 (0)| 00:00:01 | 85 | 99 |
|* 4 | INDEX SKIP SCAN | IDX_REVERSERETENT_PHONE_NO | 100 | | 104 (0)| 00:00:01 | 85 | 99 |
|* 5 | INDEX RANGE SCAN | IDX_LRP_TASK_ID_PROPERTY_ID | 1 | 52 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T"."TEMPLATE_ID"='89166069805870')
4 - access("T"."ZONE"=316)
filter("T"."ZONE"=316 AND MOD(TO_NUMBER("T"."PHONE_NO"),5)=0)
5 - access("P"."TASK_ID"="T"."TASK_ID" AND "P"."PROPERTY_ID"='callBackStatus')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
34046 consistent gets
0 physical reads
0 redo size
3102 bytes sent via SQL*Net to client
1132 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed思考？
如果索引建成PANDA.TASK_RETENTION_PROPERTY(PROPERTY_ID,TASK_ID)前導列和之前的IDX_PERTYID_PROPERTY重復了，需不需要刪除IDX_PERTYID_PROPERTY索引？為什么？如何操作？
2.2 驅動表索引優化
其實到上面已經可以結束了，但是還有優化空間，電話號碼因為使用的函數導致前導列失效走了INDEX SKIP SCAN，觀察謂詞，可以在上面建立一個函數索引，邏輯讀從 3.4 萬下降到 5 千，執行時間下降到 0.03 秒
3 - filter("T"."TEMPLATE_ID"='89166069805870')
4 - access("T"."ZONE"=316)
filter("T"."ZONE"=316 AND MOD(TO_NUMBER("T"."PHONE_NO"),5)=0)
-- 新建索引
CREATE INDEX PANDA.IDX_LRO_REG_OMT_PHONE_NO
ON PANDA.TASK_RETENTION_ORDER(ZONE,TEMPLATE_ID,MOD(PHONE_NO, 5));
-- 執行計劃如下：
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 1698682204
-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 345 | 65550 | 295 (0)| 00:00:01 | | |
| 1 | NESTED LOOPS ANTI | | 345 | 65550 | 295 (0)| 00:00:01 | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| TASK_RETENTION_ORDER | 346 | 47748 | 18 (0)| 00:00:01 | ROWID | ROWID |
|* 3 | INDEX RANGE SCAN | IDX_LRO_REG_OMT_PHONE_NO | 47 | | 1 (0)| 00:00:01 | | |
|* 4 | INDEX RANGE SCAN | IDX_LRP_TASK_ID_PROPERTY_ID | 1 | 52 | 1 (0)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("T"."ZONE"=316 AND "T"."TEMPLATE_ID"='89166069805870' AND MOD(TO_NUMBER("PHONE_NO"),5)=3)
4 - access("P"."TASK_ID"="T"."TASK_ID" AND "P"."PROPERTY_ID"='callBackStatus')
Statistics
----------------------------------------------------------
3 recursive calls
0 db block gets
5676 consistent gets
0 physical reads
0 redo size
3329 bytes sent via SQL*Net to client
1143 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
12 rows processed 2.3 統計信息更新
exec DBMS_STATS.GATHER_TABLE_STATS(
ownname => '&TOWN',
tabname => '&TNAME',
cascade => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT',
degree => 8,
no_invalidate=>false
);3. 優化前后對比
指標 | 原始 | 被驅動表建立索引 TASK_RETENTION_PROPERTY | 驅動表新增函數索引 TASK_RETENTION_ORDER |
執行時間 | 17.86s | 0.18s | 0.03s |
邏輯讀 | 790w | 3.4w | 5k |
7.24號實施后再不出現在TOP SQL里面，優化前平均每天需要讀43.97TB的數據，節點1每天產生的邏輯讀大概在1500TB左右，降低了 (43.97 *（7900000-5000）/7900000)/1500 ≈ 2.9%
SQL> SELECT AVG(BUFFER_GETS) * 24 / 10 ,ROUND((AVG(BUFFER_GETS) * 24 / 10 * 8192)/ POWER(1024, 4), 2 ) AS LOGICAL_READ_TB_24H
2 FROM DBMT.GETS_STAT_HIST WHERE
3 SNAP_TIME <= TO_DATE('20250723', 'yyyymmdd')
4 --AND INSTANCE_NUMBER = 1
5 AND sql_id = '07rsrdcyvxwkv';
AVG(BUFFER_GETS)*24/10 LOGICAL_READ_TB_24H
---------------------- -------------------
5902079738 43.97
4.舉一反三
索引跳躍掃不是一個很好的訪問路徑，通常會在過濾列上再創建一個索引將INDEX SKIP SCAN變成INDEX RANGE SCAN，也可以查查是不是少算了工作量，上面的索引對 954jp1qch7mum 也起到了優化作用 邏輯讀從60w -> 2k 執行時間從0.58s->0.01s
-- 查找跳躍掃描的sql
set lines 3000
COL sql_id FOR A15
COL child_number FOR 9999
COL plan_hash_value FOR 9999999999
COL executions FOR 999999
COL etime_ms_perexe FOR 999,990.99
COL gets_per_exe_gb FOR 999,990.99
COL operation FOR A20
COL options FOR A20
COL object_owner FOR A30
COL object_name FOR A30
COL depth FOR 999
COL cost FOR 999999
SELECT * FROM (
select
sq.sql_id,
sq.child_number,
sq.plan_hash_value,
sq.executions,
ROUND(elapsed_time / DECODE(sq.executions, 0, 1, sq.executions) / 1000000, 2) AS etime_ms_perexe,
ROUND(buffer_gets / DECODE(sq.executions, 0, 1, sq.executions) * 8 / 1024 / 1024, 2) AS gets_per_exe_gb,
operation,options,object_owner,object_name, depth,cost from v$sql sq,v$sql_plan sp where sq.sql_id=sp.sql_id and sp.plan_hash_value=sq.plan_hash_value and
sq.executions > 6 and OBJECT_OWNER='PANDA' and
sp.OPTIONS like 'SKIP%' ORDER BY gets_per_exe_gb DESC) WHERE ROWNUM<=50;案例三：索引碎片過多
1. 問題SQL
SQL 邏輯讀達到 38471塊次，每分鐘執行 66 次，每次耗時 135ms
SELECT ROWIDTOCHAR(ROWID) ROW_ADDR,
TO_CHAR(SUBMIT_TS, 'YYYYMMDDHH24MISS') SUBMIT_TS,
PHONE_NO,
CHANNEL,
MESSAGE_ID,
TO_CHAR(FINISH_TS, 'YYYYMMDDHH24MISS') FINISH_TS,
STATUS_FLAG,
TO_CHAR(RECEIVE_TS, 'YYYYMMDDHH24MISS') RECEIVE_TS
FROM MSG_NOTIFY_LOG
WHERE ROWNUM < 100
AND (CHANNEL LIKE '10086660%')
AND (CHANNEL LIKE '10086888%' OR CHANNEL LIKE '10086988%' OR
CHANNEL LIKE '10086900%' OR CHANNEL LIKE '10086660%' OR
CHANNEL LIKE '10086660700%' OR CHANNEL LIKE '10086333%' OR
CHANNEL LIKE '10086%' OR CHANNEL LIKE '10086910%' OR
CHANNEL LIKE '10086400%')表 MSG_NOTIFY_LOG 只有 2,482條記錄，但表大小達 789M，明顯存在空間碎片。INDEX RANGE SCAN每次產生的邏輯讀4w*8192/1024/1024=212.5M，索引也需要重建。
-- 段大小
SEGMENT
OWNER NAME SEGMENT_TYPE S_SIZE
--------------- ----------------- ------------- -------
PANDA ***MSG_NOTIFY_LOG TABLE PARTITION 892
INX_SMINOTIFYREP_CHANNEL INDEX 716
TABLE TABLE Index COLUMN Col
OWNER NAME Name UCPTDVS NAME Pos DESC
--------------- ----------------------------------- ------------------------------ ------- ------------------------- ---- ----
PANDA MSG_NOTIFY_LOG INX_SMINOTIFYREP_CHANNEL NNNNNVY CHANNEL 1 ASC
INX_SMINOTIFYREP_TEL NNNNNVY PHONE_NO 1 ASC
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 339ju0930gt48, child number 0
-------------------------------------
SELECT ROWIDTOCHAR(ROWID) ROW_ADDR,TO_CHAR(SUBMIT_TS,'YYYYMMDDHH24MISS'
) SUBMIT_TS,PHONE_NO,CHANNEL,MESSAGE_ID, TO_CHAR(FINISH_TS,'YYYYMMDDHH24MISS')
FINISH_TS,STATUS_FLAG,TO_CHAR(RECEIVE_TS,'YYYYMMDDHH24MISS') RECEIVE_TS FROM
MSG_NOTIFY_LOG WHERE ROWNUM <100 AND (CHANNEL LIKE '10086660%') AND (
CHANNEL LIKE '10086888%' OR CHANNEL LIKE '10086988%' OR CHANNEL LIKE
'10086900%' OR CHANNEL LIKE '10086660%' OR CHANNEL LIKE
'10086660700%' OR CHANNEL LIKE '10086333%' OR CHANNEL LIKE '10086%'
OR CHANNEL LIKE '10086910%' OR CHANNEL LIKE '10086400%' )
Plan hash value: 739392244
----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 11 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| MSG_NOTIFY_LOG | 100 | 7700 | 11 (0)| 00:00:01 | 1 | 1 |
|* 3 | INDEX RANGE SCAN | INX_SMINOTIFYREP_CHANNEL | 344 | | 1 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<100)
3 - access("CHANNEL" LIKE '10086660%')
filter("CHANNEL" LIKE '10086660%')
29 rows selected.2. 優化方案
（1）表和索引碎片整理
-- 在線整理表
ALTER TABLE PANDA.MSG_NOTIFY_LOG MOVE ONLINE;
-- 重建索引
ALTER INDEX PANDA.INX_SMINOTIFYREP_CHANNEL REBUILD ONLINE;
ALTER INDEX PANDA.INX_SMINOTIFYREP_TEL REBUILD ONLINE;（2）分區 + 并行處理
如果表大、訪問頻繁，可 分區表 + 并行索引創建：
ALTER TABLE MSG_NOTIFY_LOG MOVE PARTITION PART_MAX PARALLEL 20;
DROP INDEX PANDA.INX_SMINOTIFYREP_CHANNEL;
DROP INDEX PANDA.INX_SMINOTIFYREP_TEL;
CREATE INDEX PANDA.INX_SMINOTIFYREP_CHANNEL ON MSG_NOTIFY_LOG (CHANNEL) LOCAL PARALLEL 20;
CREATE INDEX PANDA.INX_SMINOTIFYREP_TEL ON MSG_NOTIFY_LOG (PHONE_NO) LOCAL PARALLEL 20;如果還要更進一步參考大神文章：https://www.anbob.com/archives/7180.html
（3）收集統計信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'PANDA',
tabname => 'MSG_NOTIFY_LOG',
method_opt => 'FOR ALL COLUMNS SIZE 1',
estimate_percent => 100,
degree => 15,
no_invalidate => FALSE,
cascade => TRUE
);cascade=>TRUE 會同時更新索引統計信息，完整統計信息讓優化器更準確選擇執行計劃。
3. 優化效果
階段 | 執行時間(ms) | 邏輯讀 |
優化前 | 135.97 | 38,471 |
優化后 | 0.55 | 5 |
邏輯讀下降 7000倍，執行時間下降 200倍以上，可以看出碎片整理和索引重建效果顯著。
-- 生產對比
FLAG SNAP_ID INST_ID PHV EXECS READS READS_PER GETS GETS_PER ROWS_PROCESSED ROWS_PER ELAP_MS ELAP_PER_MS
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- -----------
07 16:30--17:00 71970 1 739392244 1882 0 0 72406113 38472.9612 4352 2.31243358 255913.734 135.979667
07 17:00--17:30 71971 1 739392244 1837 0 0 70674834 38472.9635 4139 2.2531301 252807.422 137.619718
07 17:30--18:00 71972 1 739392244 1794 0 0 69020187 38472.791 3653 2.03623188 242881.648 135.385534
07 18:00--18:30 71973 1 739392244 1812 0 0 69712963 38472.9376 3944 2.17660044 244660.554 135.022381
07 18:30--19:00 71974 1 739392244 1830 0 0 70405210 38472.7923 3778 2.06448087 248405.155 135.740522
07 19:00--19:30 71975 1 739392244 1685 0 0 64826913 38472.9454 3638 2.15905045 231561.528 137.425239
07 19:30--20:00 71976 1 739392244 1768 0 0 68019973 38472.8354 3638 2.05769231 236856.197 133.968437
07 20:00--20:30 71977 1 739392244 1581 0 0 60825503 38472.8039 3292 2.08222644 213054.888 134.759575
07 20:30--21:00 71978 1 739392244 1771 0 0 68135250 38472.7555 3328 1.87916431 231164.913 130.527901
07 21:00--21:30 71979 1 739392244 1704 0 0 65557294 38472.5904 2904 1.70422535 224546.158 131.775914
07 21:30--22:00 71980 1 739392244 1665 0 0 64056595 38472.4294 2612 1.56876877 218211.453 131.05793
07 22:00--22:30 71981 1 739392244 1421 0 0 54668427 38471.7994 1223 .860661506 184232.648 129.649999
07 22:30--23:00 71982 1 739392244 1492 2 .001340483 57400075 38471.9001 1415 .948391421 190628.153 127.766859
07 23:00--23:30 71983 1 739392244 1105 0 0 42510783 38471.2968 342 .309502262 134642.817 121.848703
GV$SQL 0 1 249032900 42713 0 0 221446 5.18451057 87771 2.05490132 23835.667 .558042446
GV$SQL 0 1 249032900 71 0 0 159 2.23943662 16 .225352113 17.773 .250323944
65 rows selected.8.7號實施后再不出現在TOP SQL里面，優化前平均每天需要讀19.66TB的數據
SQL> SELECT AVG(BUFFER_GETS) * 24 / 10 ,ROUND((AVG(BUFFER_GETS) * 24 / 10 * 8192)/ POWER(1024, 4), 2 ) AS LOGICAL_READ_TB_24H
2 FROM DBMT.GETS_STAT_HIST WHERE
3 SNAP_TIME <= TO_DATE('20250806', 'yyyymmdd')
4 --AND INSTANCE_NUMBER = 1
5 AND sql_id = '339ju0930gt48';
AVG(BUFFER_GETS)*24/10 LOGICAL_READ_TB_24H
---------------------- -------------------
2639097078 19.66
最后修改時間：2025-08-21 09:45:37
「喜歡這篇文章，您的關注和贊賞是給作者最好的鼓勵」
關注作者
【版權聲明】本文為墨天輪用戶原創內容，轉載時必須標注文章的來源（墨天輪），文章鏈接，文章作者等基本信息，否則作者和墨天輪有權追究責任。如果您發現墨天輪中有涉嫌抄襲或者侵權的內容，歡迎發送郵件至：contact@modb.pro進行舉報，并提供相關證據，一經查實，墨天輪將立刻刪除相關內容。
