修改字段长度应用会影响到生产性能

我们知道,在9i对数据库进行DDl操作在高并发时或数据量大时会影响DML操作,比如添加,删除字段时,
必须等到DDL完成时,DML操作才开始 ,以下例子为高并发时测试修改字段长度严重影响到生产性能:
session 1;
[color=#009999]C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
[color=#009999]SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 1 09:50:09 2008
[color=#009999]Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

[color=#009999]Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
[color=#009999]SQL> drop table test;
[color=#009999]Table dropped.
[color=#009999]SQL> create table test (a char(500),b char(500), c char(500));
[color=#009999]Table created.
[color=#009999]SQL> alter table test nologging;
[color=#009999]Table altered.
[color=#009999]SQL> insert /*+ append */ into test select 'a','b','c' from dba_objects;
[color=#009999]6174 rows created.
[color=#009999]SQL> commit;
[color=#009999]Commit complete.
[color=#009999]SQL> insert into test select * from test;
[color=#009999]6174 rows created.
[color=#009999]SQL> /
[color=#009999]12348 rows created.
[color=#009999]SQL> /
[color=#009999]24696 rows created.
[color=#009999]SQL> /
[color=#009999]49392 rows created.
[color=#009999]SQL> commit;
[color=#009999]Commit complete.
[color=#009999]SQL> select count(*) from test;
[color=#009999]COUNT(*)
----------
98784
[color=#009999]SQL> alter session set events '10046 trace name context forever,level 12';
[color=#009999]Session altered.
[color=#009999]SQL> alter table test modify b char(1000);
[color=#009999]Table altered.
[color=#009999]SQL> alter session set events '10046 trace name context off';
[color=#009999]Session altered.
[color=#009999]SQL>

session 2: --在修改字段的同时执行session 2的查询sql语句
[color=#000066]sql> select * from test ; --此时阻塞
session 3:
SQL> select sid,event from v$session_wait;
SID EVENT
---------- -------------------------------------------------------------
1 pmon timer
2 rdbms ipc message
3 rdbms ipc message
6 rdbms ipc message
8 rdbms ipc message
7 rdbms ipc message
4 rdbms ipc message
9 db file scattered read
5 smon timer
10 library cache lock --等待事件
13 SQL*Net message to client
SID EVENT
---------- -------------------------------------------------------------
14 SQL*Net message from client
15 SQL*Net message from client
13 rows selected.
SQL> select sid,sql_hash_value from v$session where sid=10;
SID SQL_HASH_VALUE
---------- --------------
10 171085072
SQL> select sql_text from v$sqlarea where hash_value='171085072';
SQL_TEXT
------------------------------------------------------------------------
select * from test --可以查到阻塞的sql

通过session 1跟踪10046事件可以看到,对以前数据也要修改长度和修改数据字典,所以主要等待时间在这里
update "TEST" set "B"=sys_op_trtb("B", 9, 1000, 1000)
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

update col$ set intcol#=:3,segcol#=:4,type#=:5,length=:6,precision#=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

update obj$ set obj#=:6,type#=:7,ctime=:8,mtime=:9,stime=:10,status=:11,dataobj#=:13,flags=:14,oid$=:15,spare1=:16, spare2=:17 where owner#=:1 and name=:2 and namespace=:3 and(remoteowner=:4 or remoteowner is null and :4 is null)and(linkname=:5 or linkname is null and :5 is null)and(subname=:12 or subname is null and :12 is null)