我们知道,在9i对数据库进行DDl操作在高并发时或数据量大时会影响DML操作,比如添加,删除字段时,
必须等到DDL完成时,DML操作才开始 ,以下例子为高并发时测试修改字段长度严重影响到生产性能:
session 1;
C:\Documents and Settings\Paul Yi>sqlplus "/as sysdba"
SQL*Plus: Release 9.2.0.4.0 - Production on Tue Apr 1 09:50:09
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
SQL> drop table test;
Table dropped.
SQL> create table test (a char(500),b char(500), c char(500));
Table created.
SQL> alter table test nologging;
Table altered.
SQL> insert /*+ append */ into test select 'a','b','c' from dba_objects;
6174 rows created.
SQL> commit;
Commit complete.
SQL> insert into test select * from test;
6174 rows created.
SQL> /
12348 rows created.
SQL> /
24696 rows created.
SQL> /
49392 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from test;
COUNT(*)
----------
98784
SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.
SQL> alter table test modify b char(1000);
Table altered.
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
session 2: --在修改字段的同时执行session 2的查询sql语句
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)
