现象和版本
系统版本：
linux
oracle 11.2.0.4.0
现象：
最近同事在测试环境扩展一个数据表的char(9)到char(20)，致数据库hang住，查看了数据表的数量有1000多万行。
故总结下char和varchar2的区别，以及修改跟踪t扩展char的具体的trace动作
char和varchar2的区别
char的长度是固定的，varchar2的长度是变化的。
char(20)，如果数据是"aaaa"，则剩余的16个需要用空格补齐。
varchar2(20)， 如果数据是”aaaa"，则是按照实际长度来计算的。
同样最大长度设定下， varchar2按照实际长度存储，而char按照补齐的操作。所以， varchar2占用空间小。但是效率会低。
varchar2以时间换空间
测试char和varchar2长度
# t1表使用char测试
SQL> create table t1(name char(10));
Table created.
SQL> insert into t1 values('aaa');
1 row created.
SQL> commit;
Commit complete.
SQL> select name,dump(name) from t1;
NAME
----------
DUMP(NAME)
--------------------------------------------------------------------------------
aaa
Typ=96 Len=10: 97,97,97,32,32,32,32,32,32,32 # 三个字符 之后以空格补齐
SQL> select length(name) from t1 where rownum < 2;
LENGTH(NAME)
------------
10 # 长度为10
## t2表以varchar2来测试
SQL> create table t2(name varchar2(10));
Table created.
SQL> insert into t2 values('aaa');
1 row created.
SQL> commit;
Commit complete.
SQL> select name,dump(name) from t2;
NAME
----------
DUMP(NAME)
--------------------------------------------------------------------------------
aaa
Typ=1 Len=3: 97,97,97 # 此处是实际的长度，并没有用空格补齐
SQL> select length(name) from t2 where rownum < 2;
LENGTH(NAME)
------------
3
修改char类型长度跟踪
我们使用10045事件， level 12级别进行跟踪
# 构造测试表，并进行10046 trace
SQL> select count(*) from test01;
COUNT(*)
----------
999
SQL> desc test01;
Name Null? Type
----------------------------------------- -------- ----------------------------
COMPID NOT NULL CHAR(10)
CUSTNO CHAR(10) # char(10)的字段
# 修改至char(20), 过程通过10046事件跟踪
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.
SQL> alter session set tracefile_identifier='10046';
Session altered.
SQL> alter table test01 modify custno CHAR(20);
Table altered.
SQL> commit;
SQL> alter session set events '10046 trace name context off';
# 通过v$diag_info查看到的trace file文件
# /u01/app/oracle/diag/rdbms/dog/DOG/trace/DOG_ora_32506086_10046.trc
分析DOG_ora_32506086_10046.trc文件：
tkprof DOG_ora_32506086_10046.trc DOG_ora_32506086_10046.txt
vi DOG_ora_32506086_10046.txt
分析格式化之前的文件：
SQL ID: g8vhr1nz6uc4j Plan Hash: 3691610890
update "TEST01" set "CUSTNO"=sys_op_trtb("CUSTNO", 9, 20, 20) # 进行了修改表，并将信息补齐空格
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.01 0.03 0 486 2677 999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.03 0 487 2677 999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 101 (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE TEST01 (cr=489 pr=0 pw=0 time=39310 us)
999 999 999 TABLE ACCESS FULL TEST01 (cr=6 pr=0 pw=0 time=1516 us cost=4 size=
21978 card=999) # 修改了999行数据
Elapsed times include waiting on following events: # 写入了磁盘文件 ，进行了相应的DISK IO操作
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 52 0.00 0.00
********************************************************************************
# 这里是更新的seg$信息表，记录segment相关的信息
SQL ID: 0kkhhb2w93cx0 Plan Hash: 1283625304
update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17),scanhint=:18, bitmapranges=:19
where
ts#=:1 and file#=:2 and block#=:3
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 3 1 1
Fetch 0 0.00 0.00 0 0 0 0
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation # 修改这个表TEST01的seg$信息
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE SEG$ (cr=3 pr=0 pw=0 time=205 us)
1 1 1 TABLE ACCESS CLUSTER SEG$ (cr=3 pr=0 pw=0 time=24 us cost=2 size=6
8 card=1)
1 1 1 INDEX UNIQUE SCAN I_FILE#_BLOCK# (cr=2 pr=0 pw=0 time=10 us cost=
1 size=0 card=1)(object id 9)
………………
这中间经历了一些其它信息，之后会更新 tab$ col$ obj$ 表的信息，记录这个表的基本信息
………………
SQL ID: b5cr4hhndmbuf Plan Hash: 2583366266
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35
where
obj#=:1
省略统计信息
SQL ID: 6vqvn8ya0xybh Plan Hash: 1687011458
update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
deflength=decode(:19,0,null,:19),default$=:20
where
obj#=:1 and name=:2
省略统计信息
SQL ID: 4yyb4104skrwj Plan Hash: 3272417377
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
where
owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname
is null and subname is null
修改varchar2长度跟踪
修改varchar2长度跟踪trace
create table test02(compid char(10),custno varchar2(10));
alter session set tracefile_identifier='100462';
alter session set events '10046 trace name context forever, level 12';
alter table test02 modify custno varchar2(20);
alter session set events '10046 trace name context off';
# trace文件分析 ：
分析trace文件时
我们只看到了通过传入的参数修改最后的tab$ col$ obj$信息表，而并没有UPDATE test02及分析数据segment的信息
delete from tab_stats$
where
obj#=:1
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 2 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 0 0
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
SQL ID: b5cr4hhndmbuf Plan Hash: 2583366266
update tab$ set ts#=:2,file#=:3,block#=:4,bobj#=decode(:5,0,null,:5),tab#=
decode(:6,0,null,:6),intcols=:7,kernelcols=:8,clucols=decode(:9,0,null,:9),
audit$=:10,flags=:11,pctfree$=:12,pctused$=:13,initrans=:14,maxtrans=:15,
rowcnt=:16,blkcnt=:17,empcnt=:18,avgspc=:19,chncnt=:20,avgrln=:21,
analyzetime=:22,samplesize=:23,cols=:24,property=:25,degree=decode(:26,1,
null,:26),instances=decode(:27,1,null,:27),dataobj#=:28,avgspc_flb=:29,
flbcnt=:30,trigflag=:31,spare1=:32,spare2=decode(:33,0,null,:33),spare4=:34,
spare6=:35
where
obj#=:1
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 3 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 2 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
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 UPDATE TAB$ (cr=3 pr=0 pw=0 time=208 us)
1 1 1 TABLE ACCESS CLUSTER TAB$ (cr=3 pr=0 pw=0 time=25 us cost=2 size=1
46 card=1)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us cost=1 size=0
card=1)(object id 3)
********************************************************************************
SQL ID: 6vqvn8ya0xybh Plan Hash: 1687011458
update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=decode(:5,
182/*DTYIYM*/,:7,183/*DTYIDS*/,:7,decode(:7,0,null,:7)),scale=decode(:5,2,
decode(:8,-127/*MAXSB1MINAL*/,null,:8),178,:8,179,:8,180,:8,181,:8,182,:8,
183,:8,231,:8,null),null$=:9,fixedstorage=:10,segcollength=:11,col#=:12,
property=:13,charsetid=:14,charsetform=:15,spare1=:16,spare2=:17,spare3=:18,
deflength=decode(:19,0,null,:19),default$=:20
where
obj#=:1 and name=:2
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 3 2 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 2 1
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Number of plan statistics captured: 1
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 UPDATE COL$ (cr=3 pr=0 pw=0 time=136 us)
1 1 1 TABLE ACCESS CLUSTER COL$ (cr=3 pr=0 pw=0 time=22 us cost=2 size=6
0 card=1)
1 1 1 INDEX UNIQUE SCAN I_OBJ# (cr=2 pr=0 pw=0 time=10 us cost=1 size=0
card=1)(object id 3)
********************************************************************************
SQL ID: 4yyb4104skrwj Plan Hash: 3272417377
update obj$ set obj#=:4, type#=:5,ctime=:6,mtime=:7,stime=:8,status=:9,
dataobj#=:10,flags=:11,oid$=:12,spare1=:13, spare2=:14
where
owner#=:1 and name=:2 and namespace=:3 and remoteowner is null and linkname
is null and subname is null
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 3 1 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 3 1 1
Misses in library cache during parse: 0
Optimizer mode: CHOOSE
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 UPDATE OBJ$ (cr=3 pr=0 pw=0 time=54 us)
1 1 1 INDEX RANGE SCAN I_OBJ2 (cr=3 pr=0 pw=0 time=12 us cost=3 size=85
card=1)(object id 37) # I_OBJ2的索引的信息
总结
1、故当我们修改扩展char字段长度时，如果表的数据非常大，实际上是对表中的每一行数据做了update操作，相关索引也会重建，操作慢，容易引发数据库IO问题。最好在停机窗口操作。谨慎操作。
2、在使用char，需要符合此字段的特性。最好是固定长度的数据值，如果不足，用空格补齐，则在写SQL时用trim函数格式化，会引起SQL性能问题。 一个处理思路是： SQL中使用空格补齐的迂回战术 。
根本解决是：数据库表设计时，就符合使用这个char类型的特性。好的设计是成功的一半。
扫一扫
2869
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
