ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 在线增加列长度(DML lock and cursor pin S and library cache pin)

在线增加列长度(DML lock and cursor pin S and library cache pin)

原创 Linux操作系统 作者:redhouser 时间:2011-08-11 09:53:15 0 删除 编辑

问题:
1,生产上某个表需要增大列长度,是否可以在线修改?
2,如果有依赖于该表的存储过程正在运行,是否影响正在运行的作业?以后运行前是否需要重新编译?

1,问题1
1.1,问题1分析
在线意味着该表有大量dml操作,在表上有共享TM锁;而增加列长度需要获取表上的排他锁,二者有冲突,看起来不能在线更改。
事实上,更改列长度的时候是以nowait方式请求锁;增加列长度只需要修改数据字典,不需要访问数据,所以,只要能获取表上排他锁,会在很短时间内完成。
因此,可以多尝试几次,就会在线完成。
需要注意的是,更改表结构的后果:
(1)会使所有与该表相关的cursor无效,需要重新编译,会给系统CPU带来一定的压力;
(2)相关依赖对象肯能状态变为无效,如存储过程,不过,存储过程会在下次运行时自动重新编译。

1.2 问题1测试:
drop table t;
create table t(x int,c varchar2(10));

会话1:
begin
  for x in 1..1000000 loop
    insert into t values(x,'a');
    commit;
  end loop;
end;
/  


会话2:
begin
  for x in 1..1000000 loop
    insert into t values(x,'a');
    commit;
  end loop;
end;
/  

会话3:
alter table t modify c varchar2(60);
尝试6次后成功;前5次报错:
ORA-00054,资源正忙,但指定以NOWAIT方式获取资源.

select sid,event from v$session where terminal='MYPC';
998 cursor: pin S
1001 cursor: pin S

通过查询处于cursor: pin S等待事件的参数P1='773186313':
select * from v$sql where hash_value='773186313';
可以得到:
INSERT INTO T VALUES(:B1 ,'a')
表明两个会话高频率执行同一个sql,导致cursor: pin S等待。

结论:
可以通过多次尝试,实现在线增加列长度。


1.3测试cursor: pin S解决方案
参考附录,针对cursor: pin S,可以通过更改sql语句,避免两个会话争用同一个sql。

测试:
drop table t;
create table t(x int,c varchar2(10));

会话1:
begin
  for x in 1..1000000 loop
    insert /* session 1*/ into t values(x,'a');
    commit;
  end loop;
end;
/  


会话2:
begin
  for x in 1..1000000 loop
    insert /* session 2*/ into t values(x,'a');
    commit;
  end loop;
end;
/  

会话3:
select sid,event,p1,sql_id from v$session where terminal='MYPC';
1016 cursor: pin S 773186313 9g6bbdhr1bss9
1070 cursor: pin S 773186313 2dqfdr8kkbprj

再次查询:
1016 cursor: pin S 773186313 9g6bbdhr1bss9
1070 cursor: pin S 773186313 9g6bbdhr1bss9

SELECT sql_id, sql_text
  FROM v$sql
 WHERE sql_id IN ('9g6bbdhr1bss9', 'g7h4g8tqhft72', '2dqfdr8kkbprj');
g7h4g8tqhft72 begin   for x in 1..1000000 loop     insert /* session 2*/ into t values(x,'a');     commit;   end loop; end;
2dqfdr8kkbprj begin   for x in 1..1000000 loop     insert /* session 1*/ into t values(x,'a');     commit;   end loop; end;
9g6bbdhr1bss9 INSERT INTO T VALUES(:B1 ,'a')

select sql_text,sql_id from v$sql where hash_value='773186313';
INSERT INTO T VALUES(:B1 ,'a') 9g6bbdhr1bss9

结论:
(1)匿名存储过程中sql语句被改写:INSERT INTO T VALUES(:B1 ,'a'),这样两个会话会对同一个cursor争用,产生cursor: pin S事件。
(2)不清楚v$session.sql_id在什么情况下为匿名存储过程的sql_id,什么情况下为匿名存储过程中某个sql的sql_id。

2,问题2
2.1,问题2分析
依赖于表的存储过程在运行时会获取library cache pin,该锁并不能阻止依赖对象结构的变更,只会阻止对存储过程的重新编译;
存储过程在运行时,表结构变更会导致存储过程状态为无效,但该存储过程仍可以继续运行;当前运行过程通过加载到内存的过程执行;

2.2 问题2测试:
drop table t;
create table t(x int,c varchar2(10));

create or replace procedure p as
begin
  for x in 1..1000000 loop
    insert into t values(x,'a');
    commit;
  end loop;
end;
/     

select status from user_objects where object_name='P';
VALID

会话1:
begin
   p;
end;
/  


会话2:
begin
   p;
end;
/  

会话3:
alter table t modify c varchar2(60);
尝试8次后成功;前6次报错:
ORA-00054,资源正忙,但指定以NOWAIT方式获取资源.

select status from user_objects where object_name='P';
INVALID

select sid,event from v$session where terminal='MYPC';
998 cursor: pin S
1001 cursor: pin S

通过查询处于cursor: pin S等待事件的参数P1='773186313':
select * from v$sql where hash_value='773186313';
可以得到:
INSERT INTO T VALUES(:B1 ,'a')
表明两个会话高频率执行同一个sql,导致cursor: pin S等待。

会话4:
begin
   p;
end;
/  

会话3:
select status from user_objects where object_name='P';
INVALID
因为无效,会话4处于等待状态library cache pin

select sid,event from v$session where terminal='MYPC';
971 library cache pin 5.04403168454758E17 aknx7j6ky1d4q
1016 buffer busy waits 46 9g6bbdhr1bss9
1070 buffer busy waits 2 aknx7j6ky1d4q

再次执行:
971 library cache pin 5.04403168454758E17 aknx7j6ky1d4q
1016 cursor: pin S 773186313 aknx7j6ky1d4q
1070 cursor: pin S 773186313 aknx7j6ky1d4q

select count(*) from t;
不断执行,在结果超过2000000后,会话1和2执行完毕,

select status from user_objects where object_name='P';
VALID

select sid,event from v$session where terminal='MYPC';
971 gc current multi block request 22 9g6bbdhr1bss9
1016 SQL*Net message from client 1413697536 
1070 SQL*Net message from client 1413697536 
会话4开始执行。


SELECT sql_id, hash_value,sql_text
  FROM v$sql
 WHERE sql_id IN ('9g6bbdhr1bss9', 'aknx7j6ky1d4q');
aknx7j6ky1d4q 2782966934 begin    p; end;
9g6bbdhr1bss9 773186313 INSERT INTO T VALUES(:B1 ,'a')

结论:
即使有依赖于某表的存储过程正在运行,也可以更改该表结构;
更改表结构会使存储过程无效,但不影响正在运行的存储过程;
无效的存储过程在下次运行时会自动重新编译。


摘录:玉面飞龙的BLOG
http://yumianfeilong.com/html/2008/11/01/254.html
cursor: pin SNovember 1st, 2008 | Categories: Boring | Tags: Mutex, Oracle Leave a comment | Trackback OTN的解释,

cursor: pin SA session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object. This wait event should rarely be seen because a shared mutex pin update is very fast.(Wait Time: Microseconds)
Parameter Description

P1 Hash value of cursor
P2 Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3 Mutex where (an internal code locator) OR’d with Mutex Sleeps
Oracle10g中引用的mutexes机制一定程度的替代了library cache pin,其结构更简单,get&set的原子操作更快捷。

它相当于,每个child cursor下面都有一个mutexes这样的简单内存结构,当有session要执行该SQL而需要pin cursor操作的时候,session只需要以shared模式set这个内存位+1,表示session获得该mutex的shared mode lock.可以有很多session同时具有这个mutex的shared mode lock;但在同一时间,只能有一个session在操作这个mutext +1或者-1。+1 -1的操作是排它性的原子操作。如果因为session并行太多,而导致某个session在等待其他session的mutext +1/-1操作,则该session要等待cursor: pin S等待事件。

当看到系统有很多session等待cursor: pin S事件的时候,要么是CPU不够快,要么是某个SQL的并行执行次数太多了而导致在child cursor上的mutex操作争用。如果是Capacity的问题,则可以升级硬件。如果是因为SQL的并行太多,则要么想办法降低该SQL执行次数,要么将该SQL复制成N个其它的SQL。

select /*SQL 1*/object_name from t where object_id=?
select /*SQL 2*/object_name from t where object_id=?
select /*SQL …*/object_name from t where object_id=?
select /*SQL N*/object_name from t where object_id=?
这样就有了N个SQL Cursor,N个Mutex内存结构,就将争用分散开来,类似partition的作用了。

实际测试效果很明显,当仅一个SQL Cursor的时候,并行执行等待cursor: pin S较高。

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
—————————————–
cursor: pin S 460,124 223 0 63.9
CPU time 121 34.6
latch free 173 5 29 1.5
db file sequential read 54 0 2 .0
control file parallel write 27 0 2 .0
——————————————

当分解为5个SQL再次测试同样的压力,cursor: pin S 等待大大减少。

Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
——————————–
CPU time 75 60.4
cursor: pin S 115,159 33 0 26.2
latch free 175 16 90 12.7
cursor: pin S wait on X 25 1 29 .6
db file parallel write 38 0 2 .0
——————————–

如果使用SQLPLUS测试,则无上述效果。拆分SQL后仍然要等待很多cursor: pin S。因为sqlplus在返回纪录的时候默认调用BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;而导致在该SQL上的mutex 竞争。


如果配合上cursor_space_for_time,则效果更好。

Same work load, same parallel degree, cursor_space_for_time=TRUE  and only 1 SQL statement

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

—————————————– ———— ———– —— ——

CPU time 62 75.5

latch free 171 9 54 11.4

cursor: pin S wait on X 162 4 23 4.6

db file sequential read 1,184 3 2 3.5

os thread startup 2 1 584 1.4

————————————————————-

Same work load, same parallel degree, cursor_space_for_time=TRUE and 5 different SQL statement

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) Time

—————————————– ———— ———– —— ——

CPU time 58 99.4

latch free 145 0 2 .4

db file sequential read 11 0 4 .1

control file parallel write 20 0 2 .1

log file sync 1 0 9 .0

————————————————————-

可见mutex和cursor_space_for_time有互补性,在execution特别高的系统中或许值得考虑。

cursor_space_for_time is not frequently used,as it
is only necessary for extremely high_concurrency OLTP system.

In 10gR2,it is not necessary to use cursor_space_for_time to
reduce contention for frequently executed cursors,
as a new serialization mechanism which is smaller and faster
than latches is available.
This has the additional benefits of not locking down the cursor memory i
the shared pool,and not requiring the cursor to remain open.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/18922393/viewspace-704643/,如需转载,请注明出处,否则将追究法律责任。

上一篇: db2_查询锁方法
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    810011