本文通过实际测试对比Oracle数据库中表字段扩展与收缩的过程，揭示了这两种操作的不同行为及性能差异。扩展字段快速无判断，而收缩字段则涉及全表扫描判断，耗时与表数据量成正比。
1.环境信息
| 操作系统 | RHEL7.5 |
|---|---|
| 数据库版本 | 19.3 |
| 架构 | 单实例 |
2.测试目的
我们都知道，表扩展字段长度很快，收缩字段时会做判断，耗时与表的数据量大小成正比。
本次测试目的主要是确认收缩字段时为什么慢？oracle通过什么来判断的？
3.测试手段
通过10046跟踪，确认在扩展/收缩字段时oracle内部sql执行情况。
4.测试过程
构造一张测试表t1.
SQL> select count(*) from t1;
COUNT(*)
----------
1476864
SQL> desc t1
Name Null? Type
----------------------------------------- -------- ----------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
4.1扩展字段
--设置10046 trace
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever ,level 12;
Statement processed.
--扩字段
SQL> alter table t1 modify owner VARCHAR2(200);
Table altered.
--显示trace文件
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_14698.trc
--关闭10046 trace
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> exit
--转换trace文件方便读
$ tkprof cdb_ora_14698.trc /tmp/tkprof_10046.txt
--查看trace 发现表被加了独占锁
LOCK TABLE "T1" IN EXCLUSIVE MODE NOWAIT
--查看trace发现消耗很小，很快就完成了扩展字段
cat /tmp/tkprof_trace.txt
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 4 0 0
Execute 1 0.00 0.00 0 52 2 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.01 0 56 2 0
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 3 0.00 0.00
SQL*Net message from client 3 6.94 19.80
Compression analysis 8 0.00 0.00
Disk file operations I/O 3 0.00 0.00
PGA memory operation 10 0.00 0.00
log file sync 1 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 30 0.00 0.00 0 0 0 0
Execute 36 0.00 0.00 0 21 7 4
Fetch 48 0.00 0.00 0 50 0 33
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 114 0.01 0.01 0 71 7 37
4.2收缩字段
--设置10046 trace
SQL> oradebug setmypid;
Statement processed.
SQL> oradebug event 10046 trace name context forever ,level 12;
Statement processed.
--收缩表
SQL> alter table t1 modify owner VARCHAR2(100);
Table altered.
--显示trace
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_14985.trc
--关闭10046 trace
SQL> oradebug event 10046 trace name context off;
Statement processed.
SQL> exit
--转换trace文件方便读
tkprof cdb_ora_14985.trc /tmp/tkprof_10046_2.txt
--查看trace 发现表被加了独占锁
LOCK TABLE "T1" IN EXCLUSIVE MODE NOWAIT
--查看trace
--可以看到总消耗远高于扩展字段时候的消耗
********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 8 0 0
Execute 2 0.01 0.01 0 53 4 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.01 0.01 0 61 4 0
Misses in library cache during parse: 2
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
SQL*Net message from client 6 7.42 37.15
Compression analysis 16 0.00 0.00
Disk file operations I/O 6 0.00 0.00
PGA memory operation 11 0.00 0.00
log file sync 2 0.00 0.00
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 57 0.00 0.00 0 0 0 0
Execute 73 0.01 0.01 0 42 15 8
Fetch 97 0.09 0.09 0 26036 0 66
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 227 0.11 0.11 0 26078 15 74
--从trace中找到消耗比较大的sql
--a2j23dzbcqb1u是其中消耗最大的sql，
几乎等于总消耗，且该sql是在扩字段的trace中没有的
--这是一个判断sql，/*+ first_rows */模式的全表扫描
********************************************************************************
SQL ID: a2j23dzbcqb1u Plan Hash: 3617692013
select /*+ first_rows */ 1
from
"SYS"."T1" where LENGTHB("OWNER") > 100
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.09 0.09 0 25936 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.09 0.09 0 25936 0 0
Misses in library cache during parse: 1
Optimizer mode: FIRST_ROWS
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 TABLE ACCESS FULL T1 (cr=25936 pr=0 pw=0 time=97052 us starts=1 cost=7056 size=6987684 card=105874)
4.3测试结果
两种方式都是对表加上独占锁。
1）oracle表在扩展字段时，很快，内部并不会判断字段长度，所以表的数据量影响不大。
2）oracle表在收缩字段时，内部会通过sql进行一次全表扫，所以表数据量越大越慢。
5.测试总结
1）oracle表在扩展字段时，很快，与表数据量无关。
2）oracle表在收缩字段时，会以FIRST_ROWS优化器模式进行全表扫做一个判断，判断是否有字段值大于收缩设定值，通过如下sql进行check。且整个收缩字段的耗时几乎都在这个check动作上。
select /*+ first_rows */ 1 from "SYS"."T1" where LENGTHB("OWNER") > 100
3）扩展和收缩表字段都会以独占锁锁表。
通过此次试验，如果要评估一张表收缩字段耗时，可通过执行此sql（会进行全表扫，生产大表慎用），此sql的耗时约等于收缩字段耗时。
扫一扫
5306
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
