修改欄位長度應用會影響到生產效能

我們知道,在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 2008

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)