[转]修改字段长度应用会影响到生产性能
我们知道,在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 2008Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.Connected to:Oracle9i Enterprise Edition Release 9.2.0.4.0 - ProductionWith the Partitioning, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0 - ProductionSQL> 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(*)---------- 98784SQL> 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 client13 rows selected.SQL> select sid,sql_hash_value from v$session where sid=10; SID SQL_HASH_VALUE---------- -------------- 10 171085072SQL> 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#=:1update 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=:2update 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)
原文地址：http://space.itpub.net/7199859/viewspace-220511
页:
[1]