oracle扩字段长度有什么影响，修改字段长度应⽤会影响到⽣产
性能
我们知道，在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;update是什么
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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。