本文总结了三个Oracle数据库性能优化案例：
1、分区键缺失导致全表扫描：一个UPDATE语句因未使用分区键导致扫描67个月分区，优化后通过添加分区键条件和创建本地索引，逻辑读从127万降至61次，执行时间从5秒降至0.35毫秒。
2、数据倾斜引发索引失效：一个SELECT语句因字段值分布不均导致全表扫描，优化后建立本地索引，逻辑读从百万级降至万级，查询时间从分钟级降至秒级。
3、表碎片严重影响性能：一个频繁DML操作的表仅剩38行数据但占用29GB空间，通过表收缩和索引重建，表大小降至4MB，逻辑读从5万次降至5次，查询耗时从15毫秒降至0.005毫秒。
案例一：分区键未使用导致全表扫描
1. 问题 SQL
一个update更新语句，每次执行5160ms，每次逻辑读1273632块次，平均每分钟10次
UPDATE T_OJ C SET STATUS =1 WHERE C.STATUS =0 AND C.CODE ='0';
优化前执行计划走TABLE ACCESS FULL，表 T_OJ 为按月分区表，历史数据已累计 67 个月，无清理策略而且SQL 未包含分区键 SDATE 条件，走的全分区扫描，每次执行逻辑读超过 1百万，性能消耗极大。
UPDATE T_OJ C SET STATUS = 1 WHERE C.STATUS = 0 AND C.CODE = '0';---------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |---------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | | | 43196 (100)| | | || 1 | UPDATE | T_OJ | | | | | | || 2 | PARTITION RANGE ALL| | 1604 | 49724 | 43196 (1)| 00:00:02 | 1 | 286 || 3 | TABLE ACCESS FULL | T_OJ | 1604 | 49724 | 43196 (1)| 00:00:02 | 1 | 286 |----------------------------------------------------------------------------------------------------SQL> @tab PANDA.T_OJ"Show tables matching condition "%PANDA.T_OJ" (if schema is not specified then current user tables only are shown)...OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS------ ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- --------PANDA T_OJ PTAB 6548664 403662 0 0 311 2022-07-20 17:27:21 1SQL> @partkeys PANDA.T_OJOBJECT_TYPE OWNER NAME LEVEL COLUMN_NAME COLUMN_POSITION----------- -------- ------------------------------ ------ ----------- ---------------TABLE PANDA T_OJ 1_TOP ZONE 1TABLE PANDA T_OJ 1_TOP SDATE 2Enter the SQL_ID: 0sg6jv5u27cxbEnter number of days (backwards from this hour) to report (default: ALL):+--------------------------------------------------------------------------------------------------+|Plan HV Min Snap Max Snap Execs LIO PIO CPU Elapsed |+--------------------------------------------------------------------------------------------------+|2536496317 68345 68823 137,279 156,148,288,7495,710 851,491.65 853,891.68 |+--------------------------------------------------------------------------------------------------+.========== PHV = 2536496317==========First seen from "05/27/25 09:30:31" (snap #68345)Last seen from "06/06/25 08:30:30" (snap #68823).Execs LIO PIO CPU Elapsed===== === === === =======137,279 156,148,288,7495,710 851,491.65 853,891.68.Plan hash value: 2536496317-----------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | | | 43196 (100)| | | || 1 | UPDATE | T_OJ | | | | | | || 2 | PARTITION RANGE ALL| | 1604 | 49724 | 43196 (1)| 00:00:02 | 1 | 286 || 3 | TABLE ACCESS FULL | T_OJ | 1604 | 49724 | 43196 (1)| 00:00:02 | 1 | 286 |-----------------------------------------------------------------------------------------------Summary Execution Statistics Over Time of SQL_ID:0sg6jv5u27cxbAvg AvgSnapshot Avg LIO Avg PIO CPU (secs) Elapsed (secs)Time INSTANCE_NUMBER Execs Per Exec Per Exec Per Exec Per Exec------------ --------------- -------- ------------------- ------------------- ------------------- -------------------27-MAY 09:30 2 276 1,233,983.39 0.00 6.44 6.4627-MAY 10:00 2 293 1,235,158.00 0.00 6.20 6.2127-MAY 10:30 2 287 1,237,384.08 0.00 6.13 6.1427-MAY 11:00 2 290 1,236,961.55 0.00 6.18 6.2027-MAY 11:30 2 297 1,234,940.25 0.00 5.96 5.9827-MAY 12:00 2 286 1,233,832.12 0.00 6.26 6.2827-MAY 12:30 2 288 1,233,733.81 0.00 6.27 6.2927-MAY 13:00 2 277 1,236,165.01 0.00 6.34 6.3727-MAY 13:30 2 285 0.00 0.00 6.32 6.3427-MAY 14:00 2 280 1,234,984.58 0.00 6.50 6.5127-MAY 14:30 2 270 1,236,381.24 0.00 6.46 6.4727-MAY 15:00 2 273 1,232,917.53 0.00 6.61 6.6427-MAY 15:30 2 271 1,232,847.85 0.00 6.62 6.6427-MAY 16:00 2 270 1,236,693.28 0.00 6.69 6.7127-MAY 16:30 2 272 1,234,060.75 0.00 6.63 6.6527-MAY 17:00 2 274 1,236,472.58 0.00 6.51 6.5227-MAY 17:30 2 286 1,234,390.04 0.00 6.36 6.3727-MAY 18:00 2 275 1,237,903.65 0.00 6.37 6.3827-MAY 18:30 2 282 1,236,582.12 0.00 6.36 6.3727-MAY 19:00 2 287 1,231,865.97 0.00 6.25 6.2727-MAY 19:30 2 293 1,238,625.39 0.00 6.11 6.1127-MAY 20:00 2 272 0.00 0.00 6.59 6.6027-MAY 20:30 2 274 1,239,438.93 0.00 6.44 6.4427-MAY 21:00 2 277 1,231,498.21 0.00 6.54 6.5527-MAY 21:30 2 284 1,238,808.62 0.00 6.39 6.4027-MAY 22:00 2 279 1,233,273.92 0.00 6.26 6.2727-MAY 22:30 2 286 1,235,459.92 0.00 6.26 6.2727-MAY 23:00 2 283 1,235,320.71 0.00 6.34 6.3527-MAY 23:30 2 280 1,236,948.74 0.00 6.38 6.4028-MAY 00:00 2 283 1,234,178.55 0.00 6.42 6.4428-MAY 00:30 2 288 1,235,478.62 0.00 6.14 6.1528-MAY 01:00 2 262 1,235,503.11 0.00 6.40 6.41
SQL分析建议：
1、未使用分区键
OLTP系统，GB级别超千万行的表，虽然已经分区但是SQL 条件没有分区键SDATE，导致优化器无法裁剪分区，直接扫描 全部 877 个分区，观察表是一个按照（ZONE，SDATE）范围分区结构，带有时间字段可能用于历史数据剥离，可以加上分区键SDATE，只更新一天、一周、一个月甚至一年内的的数据，走上动态裁剪。
2、列数据倾斜严重
STATUS=0 数据量占比高达 2175万，选择性较差，但是STATUS=0 AND CODE=0 实际上无数据，但缺少有效索引，仍然进行全表扫描，建议建立STATUS, CODE 的组合索引
-- status=0 列倾斜SQL> select status,count(*) from PANDA.T_OJ group by status;STATUS COUNT(*)---------- ----------0 21754711 <<<<<1 39543074 433 rows selected.-- 没有 status,CODE 都等于 0的列SQL> select status,CODE,count(*) from PANDA.T_OJ group by status,CODE;STATUS CODE COUNT(*)---------- -------------------------------- ----------0 320 20480 321 1514970 333 129407900 444 86594280 -9999 9641 0 39518111 320 91 321 11101 -9999 13774 444 4310 rows selected.SQL> select status,CODE from PANDA.T_OJ where status=0 and CODE=0;no rows selected
2. 优化措施
（1）改写 SQL，增加分区键过滤
经发对比后没必要每次走TABLE ACCESS FULL，只更新最近一天的数据即可，改写SQL如下：
UPDATE T_OJ CSET STATUS = 1WHERE C.STATUS = 0AND C.CODE = '0'AND C.SDATE > TRUNC(SYSDATE,'DD');
（2）建立本地复合索引
对高频条件列 STATUS, CODE 建立本地索引：
create index PANDA.idx_AOPEN_API_CDR_ERR_HIS_2on PANDA.T_OJ(STATUS,CODE) local parallel 10;ALTER INDEX PANDA.idx_AOPEN_API_CDR_ERR_HIS_2 PARALLEL 1;--- 优化后create index PANDA.idx_AOPEN_API_CDR_ERR_HIS_2 on PANDA.T_OJ(STATUS,CODE) local parallel 10;FLAG SNAP_ID INST_ID PHV EXECS READS READS_PER GETS GETS_PER ROWS_PROCESSED ROWS_PER ELAP_MS ELAP_PER_MS--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -------------- ---------- ---------- -----------GV$SQL 0 2 1057303683 6308 1165 .184686113 387878 61.4898542 0 0 2255.578 .357574192SQL_ID 43yfh06c0x0v4, child number 0-------------------------------------UPDATE T_OJ C SET STATUS = 1 WHERE C.STATUS = 0AND C.CODE = '0' AND SDATE > TRUNC(SYSDATE,'DD')UPDATE T_OJ C SET STATUS = 1 WHERE C.STATUS = 0 AND C.CODE = '0' AND SDATE > TRUNC(SYSDATE,'DD')Plan hash value: 1057303683-------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | Cost (%CPU)| Pstart| Pstop |-------------------------------------------------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | | 7387 (100)| | || 1 | UPDATE | T_OJ | | | | || 2 | PARTITION RANGE MULTI-COLUMN | | 1 | 7387 (1)|KEY(MC)|KEY(MC)||* 3 | TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T_OJ | 1 | 7387 (1)|KEY(MC)|KEY(MC)||* 4 | INDEX RANGE SCAN | IDX_AOPEN_API_CDR_ERR_HIS_2 | 393K| 488 (1)|KEY(MC)|KEY(MC)|-------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------3 - filter("SDATE">TRUNC(SYSDATE@!,'fmdd'))4 - access("C"."STATUS"=0 AND "C"."CODE"='0')Note------ Warning: basic plan statistics not available. These are only collected when:* hint 'gather_plan_statistics' is used for the statement or* parameter 'statistics_level' is set to 'ALL', at session or system level29 rows selected.
3. 优化效果
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
案例二：数据倾斜导致索引失效和全表扫描
1. 问题 SQL
PANDA.T_TABLE为分区表，大小约 80G，近 2.3 亿行数据）SQL为在表里随机抽取一条记录。
SELECT *FROM (SELECT objectFROM PANDA.T_TABLEWHERE t_type = :1AND menuid = :2ORDER BY dbms_random.value) TWHERE ROWNUM <= 1;
SQL执行计划走TABLE ACCESS FULL，跨所有分区。逻辑读1,011 万次，执行耗时约 1 分钟，查看where字段数据倾斜严重，t_type='banana' 占比 0.04%，t_type='apple' 占比 0.008%，Oracle 优化器低估了选择率，错误地选择了全表扫。
--优化前执行计划SQL> SELECT *2 FROM (SELECT object3 FROM T_TABLE4 WHERE t_type = 'banana'5 AND menuid = 'illQuery'6 ORDER BY dbms_random.value) T7 WHERE ROWNUM <= 1;Elapsed: 00:01:02.20Execution Plan----------------------------------------------------------Plan hash value: 4246316864-----------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |-----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 18 | 718 (1)| 00:00:01 | | ||* 1 | COUNT STOPKEY | | | | | | | || 2 | VIEW | | 1 | 18 | 718 (1)| 00:00:01 | | ||* 3 | SORT ORDER BY STOPKEY| | 1 | 34 | 718 (1)| 00:00:01 | | || 4 | PARTITION RANGE ALL | | 1 | 34 | 718 (1)| 00:00:01 | 1 | 91 ||* 5 | TABLE ACCESS FULL | T_TABLE | 1 | 34 | 718 (1)| 00:00:01 | 1 | 91 |-----------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------1 - filter(ROWNUM<=1)3 - filter(ROWNUM<=1)5 - filter("t_type"='banana' AND "MENUID"='illQuery')Statistics----------------------------------------------------------56 recursive calls3 db block gets10117484 consistent gets3 physical reads944 redo size565 bytes sent via SQL*Net to client591 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1 rows processedSQL> select * from(select t_type,menuid,count(*) from PANDA.T_TABLE group by t_type,menuid order by count(*) desc) where rownum<=20;t_type MENUID COUNT(*)--------------------------------- ------------------------ ----------xxx xxx 179808204xxx xxx 84085721xxx xxx 33463594xxx xxx 24956479xxx xxx 23180243xxx xxx 16787766xxx xxx 9546022xxx xxx 6517955xxx xxx 6079137xxx xxx 6079058xxx xxx 4627071xxx xxx 4026662xxx xxx 2871057xxx xxx 2668214xxx xxx 2118282xxx xxx 2107433xxx xxx 1876312xxx xxx 1769267xxx xxx 1516184xxx xxx 148527120 rows selected.
面对这个数据分布极度倾斜的SQL，我们可以根据不通的传参生成不通的执行计划，比如选择性表较好的用一个执行计划，适合全表的时候判断一下参数在通过在文本里加入空格、注释、大小写的方式重新硬解析，这个需要改动代码？那还有没有其他方法呢？
其实Oracle早在9i的时候为了应对这种场景就出现了一种特性-绑定变量窥视。
绑定变量是为了解决硬解析耗费资源的问题，但是他忽略了实际的数据分布，如果绑定变量第一次生成的执行计划适合全表扫，而后续的传参更适合走索引，这时Oracle不会硬解析，而是直接重用shared pool的执行计划。为了解决这一问题Oracle9i引入了绑定变量窥视(Bind Variable Peeking),在解析含有绑定变量的语句时，会“窥视”其具体数值以获取最优的执行计划。但是听说效果不是很好，有很多缺陷生产需要禁用_optim_peek_user_binds尤其是在直方图出现之后，但是生产中还没遇到过具体案例，我也不是很清楚，11g之后又出了一个自适应游标共享（Adatpive Cursor Sharing），可以对比不同绑定变量的执行计划选择执行效率最高的执行计划。
2. 优化实施
建立本地索引
CREATE INDEX PANDA.IDX_BUSIACT_T_TYPEON PANDA.T_TABLE (t_type) LOCAL PARALLEL 16;ALTER INDEX PANDA.IDX_BUSIACT_T_TYPE NOPARALLEL;-- d6hxtu74hcmsr 80G 的分区表，每次执行全表扫，1min左右耗时，近千万的逻辑读，每次执行将近80G左右的逻辑读，0.04%为'banana' ，查询条件为'banana' ，改写后预期性能提升100倍以上。-- 确认一下带入的变量是不是只有'banana'SELECT POSITION,VALUE_STRING,count(*) FROM DBA_HIST_SQLBINDWHERE SQL_ID='d6hxtu74hcmsr' group by value_string,POSITION order by count(*) desc;1 banana 6042 AA 36121234561222null612 BB 312 CC 82 DD 52 WW 32 DDf 32 SS 22 vv 22 hj 12 cd 12 llj 12 bnm 12 df 12 my 1同时还有 0ss0hnst58w20 a37py49s406pw-- 0ss0hnst58w20执行计划走的全表扫描，3百万逻辑读，t_type 存在数据倾斜,0.04%为'banana' ，查询条件为'banana' ，改写后预期性能提升100倍以上。-- a37py49s406pw同上 数据倾斜，传参一样，上一个倾斜的是 t_type='banana'执行计划走的全表扫描，逻辑读35w，t_type 字段存在数据倾斜，0.008%是'appple'，查询条件为'appple'，改写后预期性能提升100倍以上。select count(*) from PANDA.T_TABLE where t_type='appple';COUNT(*)----------369411 row selected.select 36941/431947027*100 SELECTIVITY from dual;SELECTIVITY-----------.008552206
3. 优化效果
这是一个典型的数据倾斜案例，生产上是给移到了容灾库，没有实施，在测试库测试效果如下：
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
案例三：表碎片过多导致性能下降
1. 问题 SQL
前面说过一个表碎片的案例，是在刚刚Zcloud上纳管Oracle库展现的一个TOP SQL
通过这两张图我们可以发现这个SQL每次执行非常耗费CPU，在AAS中占比达到64%，在最近15天中的TOP 5 SQL中遥遥领先。执行 SQL 如下：
SELECT OID,ZONE,OBJTYPE,OBJID,IDU,PID,CREATEDATE,STATUS,STATUSDATEFROM PANDA.T_TABLEWHERE ZONE = :1AND ROWNUM <= :2AND PID BETWEEN :3 AND :4AND OBJTYPE = :5ORDER BY CREATEDATE;SQL> SQL> @tab %.T_TABLEShow tables matching condition "%%.T_TABLE%" (if schema is not specified then current users tables only are shown)...OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS-------------------- ------------------------------ ---- ------------ ------------- --------- ------ ------ ------------------- ---------- --------PANDA T_TABLE TAB 1 DISABLEDSQL> @seg PANDA.T_TABLESEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK---------- -------------------- ------------ --------------- -------------------- ------------------------------ ---------- ---------- ----------30112 PANDA T_TABLE TABLE DATA 3854336 1064 1998985SQL> @ind PANDA.T_TABLEDisplay indexes where table or index name matches %PANDA.T_TABLE%...TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC--------------- -------------------- ------------------------------ ---- ------------------------------ ----PANDA T_TABLE IDX_T_TABLE_OBJTYPE 1 OBJTYPE2 PID3 ZONEIDX_T_TABLE_PID 1 PID2 ZONEPK_T_TABLE 1 OID2 ZONEINDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT-------------------- -------------------- ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------PANDA T_TABLE IDX_T_TABLE_OBJTYPE NORMAL NO VALID NO N 1 0 0 0 0 2024-05-24 07:21:54 1 VISIBLET_TABLE IDX_T_TABLE_PID NORMAL NO VALID NO N 1 0 0 0 0 2024-05-24 07:21:54 1 VISIBLET_TABLE PK_T_TABLE NORMAL YES VALID NO N 1 0 0 0 0 2024-05-24 07:21:54 1 VISIBLE-- 生产38条数据SQL> SQL> SQL> SELECT COUNT(*) AS cnt2 FROM PANDA.T_TABLE;CNT----------38SQL> select sum(bytes/1024/1024/1024) gb from dba_segments where segment_name='T_TABLE' and owner ='PANDA';GB----------29.406251 row selected.select TABLE_OWNER,TABLE_NAME,INSERTS,UPDATES,DELETES from dba_tab_modifications where TABLE_NAME='T_TABLE' and TABLE_OWNER='PANDA';TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES------------ -------------------- ---------- ---------- ----------PANDA T_TABLE 438999405 0 4239602481 row selected.
SQL分析建议，执行计划走索引，但逻辑读异常偏高，而且每分钟执行2,425次，经检查表数据量生产上仅 38 行，表段大小：29.4 GB，表存在频繁 DML 操作导致段空间碎片极度严重，表历史上有 约 4.38 亿次 INSERT 和 约 4.24 亿次 DELETE，虽然表只剩几十条数据，但 segment 大小占用近 30GB
2. 优化实施
方案一：行迁移收缩
收缩过程中可执行 DML（COMPACT 阶段）， SHRINK SPACE 会锁表，需要在业务低峰期执行。
ALTER TABLE PANDA.T_TABLE ENABLE ROW MOVEMENT;-- 在线收缩，DML 可继续执行，但速度较慢ALTER TABLE PANDA.T_TABLE SHRINK SPACE COMPACT; -- 10h 能执行DML-- 真正降低高水位，锁表执行ALTER TABLE PANDA.T_TABLE SHRINK SPACE; -- 3h 左右 会锁表ALTER TABLE PANDA.T_TABLE DISABLE ROW MOVEMENT;
方案二：表 MOVE + 重建索引（推荐）
MOVE 会重建 segment，高水位线被重置，需重建所有失效索引（本来就需要重建索引整理碎片呢）
ALTER TABLE PANDA.T_TABLE MOVE PARALLEL 20;ALTER TABLE PANDA.T_TABLE NOPARALLEL;-- 重建失效索引，整理碎片（停机可以drop掉重建，在线可以加上online，或者调整参数加快索引建立速度）ALTER INDEX PANDA.IDX_T_TABLE_OBJTYPE REBUILD PARALLEL 20;ALTER INDEX PANDA.IDX_T_TABLE_OBJTYPE NOPARALLEL;
测试环境测试
SQL> SELECT OID,2 ZONE,3 OBJTYPE,4 OBJTYPE,5 OBJID,6 IDU,7 PID,8 CREATEDATE,9 STATUS,10 STATUSDATE11 FROM PANDA.T_TABLE12 WHERE ZONE = 31413 AND ROWNUM <= 100014 AND PID BETWEEN 0 AND 9915 AND OBJTYPE = 'CUSTOMER'16 ORDER BY CREATEDATE;5 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3766318825------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 79 | 26781 | 2 (50)| 00:00:01 || 1 | SORT ORDER BY | | 79 | 26781 | 2 (50)| 00:00:01 ||* 2 | COUNT STOPKEY | | | | | || 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TABLE | 79 | 26781 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_T_TABLE_OBJTYPE | 23 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter(ROWNUM<=1000)4 - access("OBJTYPE"='CUSTOMER' AND "PID">=0 AND "ZONE"=314 AND "PID"<=99)filter("ZONE"=314)Statistics----------------------------------------------------------24 recursive calls3 db block gets6860 consistent gets8 physical reads824 redo size1669 bytes sent via SQL*Net to client668 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)5 rows processed--降低表的高水位线 shrink 表空间，索引同理alter table PANDA.T_TABLE enable ROW MOVEMENT;alter table PANDA.T_TABLE shrink space compact; -- 10h 能执行DMLalter table PANDA.T_TABLE shrink space; -- 3h 左右 会锁表alter table PANDA.T_TABLE disable ROW MOVEMENT;-- 测试库估算有1亿行数据收缩表后从29GB降到8G，生产只有不到100行数据。SQL> select round(sum(bytes)/1024/1024/1024,2) GB from dba_segments where owner='PANDA' and segment_name='T_TABLE';GB----------8.35OWNER TABLE_NAME TYPE NUM_ROWS BLOCKS EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED DEGREE COMPRESS-------------------- ------------------ ---- ------------ ------------- --------- ------ ------ ----------------- ---------- --------PANDA T_TABLE TAB 99094733 1093107 0 0 74 20250604 17:02:20 1 DISABLED-- 收缩表后SELECT OID,ZONE,OBJTYPE,OBJTYPE,OBJID,IDU,PID,CREATEDATE,STATUS,STATUSDATEFROM PANDA.T_TABLE -- 走索引不会扫描高水位，那判断哪sql在这个表上没走索引WHERE ZONE = 314AND ROWNUM <= 1000AND PID BETWEEN 0 AND 99AND OBJTYPE = 'CUSTOMER'ORDER BY CREATEDATE;1000 rows selected.Elapsed: 00:00:00.57Execution Plan----------------------------------------------------------Plan hash value: 3766318825------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 79 | 26781 | 2 (50)| 00:00:01 || 1 | SORT ORDER BY | | 79 | 26781 | 2 (50)| 00:00:01 ||* 2 | COUNT STOPKEY | | | | | || 3 | TABLE ACCESS BY INDEX ROWID BATCHED| T_TABLE | 79 | 26781 | 1 (0)| 00:00:01 ||* 4 | INDEX RANGE SCAN | IDX_T_TABLE_OBJTYPE | 23 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter(ROWNUM<=1000)4 - access("OBJTYPE"='CUSTOMER' AND "PID">=0 AND "ZONE"=314 AND "PID"<=99)filter("ZONE"=314)Statistics----------------------------------------------------------30 recursive calls3 db block gets612 consistent gets543 physical reads1160 redo size56247 bytes sent via SQL*Net to client679 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)1000 rows processed-- 测试库上只优化了表的碎片逻辑读已经降了10倍。 生产实施之后降到5块次。Avg AvgPlan Snapshot Avg Rows Avg LIO Avg PIO CPU (secs) Elapsed (secs)Hash Value Time INSTANCE_NUMBER Execs Per Exec Per Exec Per Exec Per Exec Per Exec---------- ------------ --------------- -------- ------------------- ------------------- ------------------- ------------------- -------------------1014029349 14-JUL 04:00 1 104,569 3.31 5.11 0.00 0.00 0.0014-JUL 04:30 1 103,711 3.29 4.58 0.00 0.00 0.0014-JUL 05:00 1 105,174 3.31 4.69 0.00 0.00 0.0014-JUL 05:30 1 105,479 3.30 4.99 0.00 0.00 0.0014-JUL 06:00 1 104,570 3.29 5.79 0.00 0.00 0.0014-JUL 06:30 1 106,537 3.30 5.18 0.00 0.00 0.0014-JUL 07:00 1 104,785 3.27 5.30 0.00 0.00 0.0014-JUL 07:30 1 105,018 3.29 5.56 0.00 0.00 0.0014-JUL 08:00 1 105,194 3.28 9.31 0.00 0.00 0.00********** -------- ------------------- ------------------- ------------------- ------------------- -------------------avg 3.11 3,167.96 0.04 0.08 0.08sum
3. 优化效果
后面迁移到OB建议设置成buffer表，生产实施之后，效果如下
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|