上集回顾

通过上集的学习，我们已经了解了Oracle逻辑结构的理论基础，掌握了块（Block）、区（Extent）、段（Segment）、表空间（Tablespace）等核心概念，并理解了行迁移、行链接等现象的成因机制。理论武装了头脑，但真正的价值在于实战应用。

正如古语所言："纸上得来终觉浅，绝知此事要躬行。"接下来，我们将从理论的殿堂走向实践的战场，通过一系列精心设计的真实案例，深度剖析如何巧妙运用逻辑结构知识来解决实际的SQL性能问题。

3 相关优化案例分析

前面大家对逻辑结构有了一些直观的认识，接下来，我们来看看在现实应用中，如何巧妙地利用逻辑结构的块、段、表空间的一些知识和业务场景来进行 SQL 优化，让 SQL 跑得更快。如下所示：

3.1 块的相关案例

环境准备（分别构造出 BLOCK 为 2KB、4KB、8KB、16KB 的 4 个表空间）：

--启动大小为 2KB 的块新建表空间(Windows 下只能使用 2KB、4KB、8KB 和 16KB)
alter system set db_2k_cache_size=100M;
drop tablespace tbs_ljb_2k including contents and datafiles;
create tablespace TBS_LJB_2k 
blocksize 2K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_2K_01.DBF' size 100M 
autoextend on 
extent management local 
segment space management auto;
create table t_2k tablespace tbs_ljb_2k as select * from dba_objects;
--启动大小为 4KB 的块新建表空间
alter system set db_4k_cache_size=100M;
drop tablespace tbs_ljb_4k including contents and datafiles;
create tablespace TBS_LJB_4k 
blocksize 4K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_4K_01.DBF' size 100M 
autoextend on 
extent management local 
segment space management auto;
create table t_4k tablespace tbs_ljb_4k as select * from dba_objects;
--启动大小为 8KB 的块新建表空间(默认就是 8KB)
drop table t_8k purge;
create table t_8k as select * from dba_objects;
--启动大小为 16KB 的块新建表空间
alter system set db_16k_cache_size=100M;
drop tablespace tbs_ljb_16k including contents and datafiles;
create tablespace TBS_LJB_16k 
blocksize 16K
datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_16K_01.DBF' size 100M 
autoextend on 
extent management local 
segment space management auto;
create table t_16k tablespace tbs_ljb_16k as select * from dba_objects;

结果利用 4 个不同的表空间执行相同数据量的相同 SQL 语句，发现性能有差异，首先是 2KB的表空间情况：

set autotrace on
SQL> select count(*) from t_2k;
COUNT(*)
--------
111208
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 891 (1)| 00:00:11 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_2K | 83292 | 891 (1)| 00:00:11 |
-------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 4511 consistent gets
--4KB 的表空间情况：
set autotrace on
SQL> select count(*) from t_4k;
COUNT(*)
--------
111208
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 480 (1)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_4K | 63139 | 480 (1)| 00:00:06 |
-------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 2137 consistent gets
--8KB 的表空间情况：
set autotrace on
SQL> select count(*) from t_8k;
COUNT(*)
--------
111208
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 291 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_8K | 62320 | 291 (1)| 00:00:04 |
-------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 1043 consistent gets

16KB 的表空间情况：

set autotrace on
SQL> select count(*) from t_16k;
COUNT(*)
--------
111208
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 200 (1)| 00:00:03 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_16K | 80144 | 200 (1)| 00:00:03 |
--------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 517 consistent gets

试验结论：

BLOCK 越大，相同数据量的情况下存储的行就越多，BLOCK 需要的越少，而 Oracle的最小访问单位是 BlOCK，所以访问产生的逻辑读就越小，对应的 consistent gets 就越小，如下图所示：

不过要切记，实际情况并非 BLOCK 越大越好，因为 BLOCK 越大，不同的人访问不同的数据落在同一个 BLOCK 的概率就大大增加，这就容易产生热点块竞争，因此在OLTP 系统里 BLOCK 过大是不适合的，但是在 OLAP 系统里，BLOCK 大还是比较有用的！

3.2 段的相关案例

3.2.1. segment 之分区表的化整为零优化

普通表由单个段组成。可以将分区表理解为人们想把这个单个段切割成多个小段，如果访问能落到具体的某个小段里，则不要去理会别的小段，这样访问路径大幅度减少了，性能自然就增强了。

请看下面的分段情况：

SQL> SET LINESIZE 666
SQL> set pagesize 5000
SQL> column segment_name format a20
SQL> column partition_name format a20
SQL> column segment_type format a20
SQL> select segment_name,
 2 partition_name,
 3 segment_type,
 4 bytes / 1024 / 1024 "字节数(M)",
 5 tablespace_name
 6 from user_segments
 7 where segment_name IN('RANGE_PART_TAB','NORM_TAB');
 
SEGMENT_NAME PARTITION_NAME SEGMENT_TYPE 字节数(M) TABLESPACE_NAME
-------------------------------------------------------------------------------------
NORM_TAB TABLE 47 USERS
RANGE_PART_TAB P1 TABLE PARTITION .0625 USERS
RANGE_PART_TAB P10 TABLE PARTITION 5 USERS
RANGE_PART_TAB P11 TABLE PARTITION 4 USERS
RANGE_PART_TAB P12 TABLE PARTITION 5 USERS
RANGE_PART_TAB P2 TABLE PARTITION .1875 USERS
RANGE_PART_TAB P3 TABLE PARTITION 4 USERS
RANGE_PART_TAB P4 TABLE PARTITION 4 USERS
RANGE_PART_TAB P5 TABLE PARTITION 5 USERS
RANGE_PART_TAB P6 TABLE PARTITION 4 USERS
RANGE_PART_TAB P7 TABLE PARTITION 4 USERS
RANGE_PART_TAB P8 TABLE PARTITION 5 USERS
RANGE_PART_TAB P9 TABLE PARTITION 4 USERS
RANGE_PART_TAB P_MAX TABLE PARTITION 8 USERS
已选择 14 行。

接下来具体地查询通过 range_part_tab 表访问的执行计划，发现出现 Pstart 和 Pstop，由此我们就看得一清二楚了，访问只落在了第 9 个段里，也就是第 9 个分区中。

（1）普通表全扫描

SQL> select *
 2 from norm_tab
 3 where deal_date >= TO_DATE('2015-09-04', 'YYYY-MM-DD')
 4 and deal_date <= TO_DATE('2015-09-07', 'YYYY-MM-DD');
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 223 | 443K| 1606 (1)| 00:00:20 |
|* 1 | TABLE ACCESS FULL| NORM_TAB | 223 | 443K| 1606 (1)| 00:00:20 |
------------------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 5923 consistent gets

（2）分区表局部扫描

SQL> select *
 2 from range_part_tab
 3 where deal_date >= TO_DATE('2015-09-04', 'YYYY-MM-DD')
 4 and deal_date <= TO_DATE('2015-09-07', 'YYYY-MM-DD');
----------------------------------------------------------------
| Id |Operation |Name |Rows | Bytes|Cost (%CPU)| Time |Pstart|Pstop |
--------------------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 2037| 2 (0)| 00:00:01| | |
| 1| PARTITION RANGE SINGLE| | 1| 2037| 2 (0)| 00:00:01| 9 | 9 |
|* 2| TABLE ACCESS FULL |RANGE_PART_TAB| 1| 2037| 2 (0)| 00:00:01| 9 | 9 |
--------------------------------------------------------------------------------------------
统计信息
--------
 0 recursive calls
 0 db block gets
 3 consistent gets

数据量相同，SQL 语句相同，就是表结构不一样，逻辑读从 5923 迅速下降到 3，性能大幅度提升。

3.2.2. segment 之高水平位相关排查优化案例

--构造表
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;

（1）未删表记录前逻辑读巨大

SQL> --测试表的大小及语句的效率
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
 264
SQL> select count(*) from t;
 COUNT(*)
---------------
 2332096
统计信息
--------
 0 recursive calls
 0 db block gets
 33350 consistent gets
 0 physical reads

（2）删大量记录后逻辑读不变

接下来删除大量数据，发现 SEGMENT 未见减少，依然是 264，关键是，表记录少了这么多，逻辑读依然不变，还是33350，如下：

SQL> delete from t where rownum<=2000000;
已删除 2000000 行。
SQL> commit;
提交完成。
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
 264
SQL> select count(*) from t;
 COUNT(*)
----------
 332096
统计信息
--------
 0 recursive calls
 0 db block gets
 33350 consistent gets
 0 physical reads

（3）释放高水平位后性能飞跃

这就是高水平位的问题，用 move 重组数据后，高水平位释放，情况即将发生大变：

SQL> alter table t move;
表已更改。
SQL> select bytes/1024/1024 from user_segments where segment_name='T';
BYTES/1024/1024
---------------
 38 
SQL> select count(*) from t;
 COUNT(*)
----------
 332096
统计信息
--------
 0 recursive calls
 0 db block gets
 4742 consistent gets
 0 physical reads

逻辑读从原先的 33350 降低为 4742，性能大幅度提升！

3.2.3. segment 之高水平位情况监控

接上面的问题，你在优化 SQL 的时候，你是如何知道系统中存在大表记录删除后，高水平位依然没释放的问题呢？

（1）num_rows 和 blocks 的正常比例

我们来构造一组试验，看看正常情况下从 user_tab_statistics 获取到的 num_rows 和 blocks的正常比例，如下：

--构造表
drop table t purge;
create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 
10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; 
select num_rows,blocks from user_tab_statistics where table_name='T';
NUM_ROWS BLOCKS
--------- ----------
 2320250 33583

这里看出，num_rows 是 2320250，而 blocks 是 33583。2320250/33583 大致为 70 左右，表示 70 行装一个块，还算合理。

（2）num_rows 和 blocks 的异常比例

当删除了大量数据之后，情况又是如何呢？请继续看试验，如下：

set autotrace off
delete from t where rownum<=2300000;
commit;
exec dbms_stats.gather_table_stats(ownname => 'LJB',tabname => 'T',estimate_percent => 
10,method_opt=> 'for all indexed columns',cascade=>TRUE) ; 
select num_rows,blocks from user_tab_statistics where table_name='T';
 NUM_ROWS BLOCKS
---------- ----------
 32780 33583

现在 num_rows 是 32780，而 blocks 是 33583，大致一行装一个块，这显然是不太可能的！

3.3 表空间的案例

3.3.1. 表空间查询慢与回收站关系

有一个案例，查询表空间语句非常慢，后来被查是由于回收站对象过多却没有释放的原因。这其实是 Oracle 执行计划中的一些 Bug，在 Oracle 查询表空间时，基表的查询主要是通过 NL 连接，

对象过多则导致性能缓慢。具体细节就不说了，我们构造一个试验，让有兴趣的读者自行去体会。

首先是先建 1000 张表，如下：

sqlplus "/ as sysdba"
grant create any table to ljb;
connect ljb/ljb
drop table test purge;
create table test as select * from dba_objects where rownum<=2;
create or replace procedure p_create_tab
as
l_sql VARCHAR2(32767);
BEGIN
 FOR i IN 1..10000
 LOOP
 l_sql := 'CREATE TABLE TEST_LJB_' || i ||' as select * from test';
 -- dbms_output.put_line(l_sql);
 EXECUTE IMMEDIATE l_sql;
 END LOOP; 
END p_create_tab;
/
create or replace procedure p_drop_tab
as
l_sql VARCHAR2(32767);
BEGIN
 FOR i IN 1..10000
 LOOP
 l_sql := 'DROP TABLE TEST_LJB_' ||i;
 -- dbms_output.put_line(l_sql);
 EXECUTE IMMEDIATE l_sql;
 END LOOP; 
END p_drop_tab;
/

接下来通过执行 exec p_create_tab 完成建表 1000 张，并执行查询表空间语句，发现速度非

常快，仅 0.12s，如下：

exec p_create_tab;
set timing on
SELECT A.TABLESPACE_NAME "表空间名",
 A.TOTAL_SPACE "总空间(G)",
 NVL(B.FREE_SPACE, 0) "剩余空间(G)",
 A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
 CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE 
* 100, 2) END "剩余百分比%" --避免分母为 0
 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) A,
 (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
已用时间： 00:00:00.12

但是再执行 exec p_drop_tab;drop 这 1000 张表后，速度一下子就慢了，花了 8s 多，如下：

exec p_drop_tab;
set timing on 
SELECT A.TABLESPACE_NAME "表空间名",
 A.TOTAL_SPACE "总空间(G)",
 NVL(B.FREE_SPACE, 0) "剩余空间(G)",
 A.TOTAL_SPACE - NVL(B.FREE_SPACE, 0) "使用空间(G)",
 CASE WHEN A.TOTAL_SPACE=0 THEN 0 ELSE trunc(NVL(B.FREE_SPACE, 0) / A.TOTAL_SPACE 
* 100, 2) END "剩余百分比%" --避免分母为 0
 FROM (SELECT TABLESPACE_NAME, trunc(SUM(BYTES) / 1024 / 1024/1024 ,2) TOTAL_SPACE
 FROM DBA_DATA_FILES
 GROUP BY TABLESPACE_NAME) A,
 (SELECT TABLESPACE_NAME, trunc(SUM(BYTES / 1024 / 1024/1024 ),2) FREE_SPACE
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+)
ORDER BY 5;
已用时间： 00:00:08.12

3.3.2. 表空间频繁扩展与插入性能

环境搭建:分别建 2 个表空间，一个是固定尺寸的，另一个是自动扩展的，再分别在两个表空间

上建相同结构的表，如下：

--- 分别建固定尺寸和自动扩展的两个表空间
set timing on
drop tablespace tbs_ljb_a including contents and datafiles;
drop tablespace tbs_ljb_b including contents and datafiles;
create tablespace TBS_LJB_A datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_A.DBF' size 1M 
autoextend on uniform size 64k;
create tablespace TBS_LJB_B datafile 'D:\ORACLE\ORADATA\TEST11G\TBS_LJB_B.DBF' size 2G ;
---分别在两个不同表空间建表
connect ljb/ljb
set timing on
CREATE TABLE t_a (id int,contents varchar2(1000)) tablespace TBS_LJB_A; 
CREATE TABLE t_b (id int,contents varchar2(1000)) tablespace TBS_LJB_B;

开始试验，分别往这两个表中插入数据：

---往可自动扩展表空间的表中插入数据
SQL> insert into t_a select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建 200000 行。
已用时间: 00: 00: 52.41
---往固定大小的表空间的表中插入数据
SQL> insert into t_b select rownum,LPAD('1', 1000, '*') from dual connect by level<=200000;
已创建 200000 行。
已用时间: 00: 00: 15.17

我们惊奇地发现，往固定尺寸的表空间的表中插入数据花了 15s，而往自动扩展的表空间的表

中插入数据居然花了 52s！其本质原因就在于表空间初始分配的空间很小，而且每次新增的尺寸也

很小。导致系统不断分配空间，自动扩展，多做事了。通过接下来的查询也可以一目了然地看出这

一点，如下：

SQL> select count(*) from user_extents where segment_name='T_A';
 COUNT(*)
------------------
 3610
SQL> select count(*) from user_extents where segment_name='T_B';
 COUNT(*)
-----------------
 100

T_A 表申请空间分配有 3610 次之多，而 T_B 表仅 100 次，性能差异能不大吗？

## 3.4 rowid

环境搭建，比较三种扫描方式的性能差异。

drop table t purge;
create table t as select * from dba_objects;
create index idx_object_id on t(object_id);
set linesize 1000
set autotrace traceonly

3.4.1. 方法 1（全表扫描）

SQL> select /*+full(t)*/ * from t where object_id=2;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 291 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| T | 1 | 207 | 291 (1)| 00:00:04 |
--------------------------------------------------------------------------
统计信息
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 1044 consistent gets

3.4.2. 方法 2（索引扫描）

SQL> select * from t where object_id=2;

-------------------------------------------------------------------------------------

-------

| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 207 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | IDX_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |

-------------------------------------------------------------------------------------

-------

统计信息

----------------------------------------------------------

 0 recursive calls

 0 db block gets

 4 consistent gets

3.4.3. 方法 3（rowid 扫描）

SQL> select * from t where object_id=2 and rowid='AAAYiZAALAAAADLAAw';
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 219 | 1 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY USER ROWID| T | 1 | 219 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
 0 recursive calls
 0 db block gets
 1 consistent gets

在这里插入图片描述

4 总结

在这里插入图片描述

本文从Oracle数据库的底层逻辑结构出发，构建了一套完整的性能优化体系。通过块、段、表空间、rowid四个层级的递进分析，展现了从微观到宏观的系统性优化思路。在基础层面，不同块大小的表空间对比展现了块级设置对查询效率的直接影响；分区表的"化整为零"策略更是让同一查询的逻辑读从数千骤降至个位数，效果显著。同时，文中深入分析了高水平位问题，提供了通过num_rows与blocks比例识别异常的监控方法，避免了删除大量数据后仍需扫描全部块的性能浪费。

在更高层级的优化中，表空间管理的重要性得到充分体现。回收站对象的及时清理能避免表空间查询性能急剧下降，而合理的扩展策略则直接影响DML操作的执行效率。最终，通过rowid访问方式的演示，完整呈现了从全表扫描到索引访问再到直接定位的性能递进路径。这种分层递进的优化框架体现了Oracle性能调优的整体思维——从底层存储结构到顶层访问策略的全方位考量，确保每个环节都能发挥最佳效能。

希望这套体系化的优化方法能为数据库管理人员在生产环境中诊断和解决性能问题提供系统性的实操指导。

全文完

众号：收获不止数据库

微信：ljbyxl1213

系列回顾

ITPub博客

Oracle 逻辑结构与性能优化（下）