案例一：缺少合适索引导致全表扫描
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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
