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作個測試。