Oracle: Grant 对high loading system，bind variable的影响

前几日，海的那边的AP TEAM发来一份script，说是程序的权限控制要大改，请这边的DBA执行一下，我打开看了下，确实对很多objects的grants有很大变动。

先在一个库执行了一下，顺利，10分钟run完。然后在第二个库，开始run。我习惯性的观察TOP状况的变动，忽然发现s012之后的process出现在TOP中，然后竟然发现s30也出现了( 我们用Shared_server, 默认开12个，最大32个)，这可不是好兆头。

[@more@]

赶紧检查session wait, 发现了大量library cache pin，赶紧Cancel掉。恢复正常。

为什么grant会产生大量library cache pin.，我还真没好好思考过。

后来去恶补一下：

来看看Steve Adams的解释：The grant needs to invalidate the cached meta data for DBMS_PIPE( Procedure/Package Name ) in the library cache. The X pin is required for the invalidation.

注意这只是10G之前的状况，10G之后grant有了增强，可以绕过library cache pin的竞争。

所以第一个库是10.2.0.4 没有问题，第二个是9.2.0.8 问题很大…

第一个问题解决，随后又来一个问题，User随后打电话来说 刚刚卡了一下( latch contention) 后就一直很慢…( ??? )。

登入系统，抓一下正在执行的SQL，有一个SQL的COST变成了4000多，这是一个核心程式的SQL，看来execution plan 有变，刚刚只是做了grant而已，statistics没有任何变化。

这个SQL是用了bind variable的，不重新hard parse任何后续都会一直延续使用这个不恰当的Plan.

用DBMS_STATS 重新收集下涉及到的table的statistics后，SQL重新hard parse，观察cost回归正常, user那边也回复程序运行速度正常了。

分析了一下：grant会引起SQL execution plan的invalidation，导致SQL的hard parse，又由于bind variable的peeking特性，刚好peeking到一个诡异的值，于是就为该SQL定下了一个不恰当的PLAN. 总之，还是蛮凑巧的。

后来把这个情况跟AP TEAM聊了下，对方觉得难以想象L…..grant会引起SQL PLAN的变更？因此下面纪录了后来做的演示（ENV： 9.2.0.8）。

1． 构造一个Skew Table.

CREATE TABLE SYS.GRANT_TEST

(

TID NUMBER(10),

TXT1 VARCHAR2(128 BYTE),

TXT2 VARCHAR2(128 BYTE),

TXT3 VARCHAR2(128 BYTE),

TXT4 VARCHAR2(128 BYTE),

TXT5 VARCHAR2(128 BYTE),

TXT6 VARCHAR2(128 BYTE)

)

用了一个Procedure去insert

declare

var1 number(10);

begin

for var1 in 1 .. 10000

Loop

insert into sys.grant_test

values(var1,'Too simple,too naive.','How long I missed You.','SINO has a lot of 5MAO.',

'If you feel like there is no escape.','HITACHI, Inspire The Next.','Microsoft Outlook/Excel/PowerPoint/Visio.');

End loop;

end;

把TID都update成9999，除了5000~5010。然后建立TID的index并gather statistics。

2．

构造一个bind variable的SQL, 这里我随便用一个Procedure来实现。

CREATE OR REPLACE PROCEDURE test_bindvar

( GID IN number,

P_RET OUT VARCHAR2

)

IS

ERROR EXCEPTION;

BEGIN

select txt6 into P_RET from grant_test where TID=GID;

EXCEPTION

WHEN ERROR THEN

P_RET:=1;

END;

2． 执行

declare

outer varchar2(128);

begin

test_bindvar(5005,outer);

dbms_output.put_line(outer);

end;

检查v$sql，会发现COST=2，executions=1，loads=1, invalidations=0

随后执行declare

outer varchar2(128);

begin

test_bindvar(9999,outer);

dbms_output.put_line(outer);

end;

当然会执行不成功，因为too many rows的错误，但是不影响测试。Executions=2,loads=1,invalidations=0, 依然沿用了原来的PLAN，其实对于这个值，FTS显然更好。

如果下面执行一个grant: grant select on grant_test to PT;

然后再观察v$sql, 会发现这个SQL PLAN的sharable_mem减少，executions=0, loads=1,invalidations=1, 其他信息都消失了。(这个时候如果是flush shared pool,这笔SQL的条目还会在，如果是断开session再flush即可清除，因此留下的这部份信息应该是和user session的部分相关，例如指向的cursor之类)。

再次执行

declare

outer varchar2(128);

begin

test_bindvar(9999,outer);

dbms_output.put_line(outer);

end;

会发现executions=1,loads=2, cost=31 à FTS的cost.

以上就演示了grant之后，涉及到的SQL会再次hard parse, 在一个skew table中bind variable下SQL execution变更的例子。

Bind Variable Peeking的这个缺憾，似乎到了11G的时候有了改观，有时间会再用11G作个测试。

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

转载于:http://blog.itpub.net/10856805/viewspace-1033553/