MSSQL网站制作之关于shared pool的深切切磋(五)

优化的SQL查询算法，有效地提高查询速度
关于sharedpool的深切切磋(五)

原文链接:

http://www.eygle.com/internal/shared_pool-5.htm

Oracle利用两种数据布局来举行sharedpool的并发把持:lock和pin.
Lock比pin具有更高的级别.

Lock在handle上取得,在pin一个工具之前,必需起首取得该handle的锁定.
锁定次要有三种形式:Null,share,Exclusive.
在读取会见工具时,一般必要猎取Null(空)形式和share(共享)形式的锁定.
在修正工具时,必要取得Exclusive(排他)锁定.

在锁定了LibraryCache工具今后,一个历程在会见之前必需pin该工具.
一样pin有三种形式,Null,shared和exclusive.
只读形式时取得共享pin,修正形式取得排他pin.

一般我们会见、实行历程、Package时取得的都是共享pin,假如排他pin被持有,那末数据库此时就要发生守候.
在良多statspack的report中,我们大概看到以上等待事务:

Top5WaitEvents
~~~~~~~~~~~~~~~~~Wait%Total
EventWaitsTime(cs)WtTime
---------------------------------------------------------------------------
librarycachelock75,8841,409,50048.44
latchfree34,297,9061,205,63641.43
librarycachepin563142,4914.90
dbfilescatteredread146,28375,8712.61
enqueue2,21113,003.45
-------------------------------------------------------------

这里的librarycachelock和librarycachepin都是我们体贴的.接上去我们就研讨一下这几个守候事务.


(一).LIBRARYCACHEPIN守候事务

Oracle文档上如许先容这个守候事务:
"librarycachepin"是用来办理librarycache的并发会见的,pin一个object会引发响应的heap被
载进内存中(假如此前没有被加载),Pins能够在三个形式下取得:NULL,SHARE,EXCLUSIVE,能够以为pin是一种特定
情势的锁.
当LibraryCachePin守候事务呈现时,一般申明该Pin被其他用户已非兼容形式持有.

"librarycachepin"的守候工夫为3秒钟,个中有1秒钟用于PMON背景历程,即在获得pin之前最多守候3秒钟,不然就超时.
"librarycachepin"的参数以下,有效的次要是P1和P2:
P1-KGLHandleaddress.
P2-Pinaddress
P3-EncodedMode&Namespace

"LIBRARYCACHEPIN"一般是产生在编译或从头编译PL/SQL,VIEW,TYPES等object时.编译一般都是显性的,
如安装使用程序,晋级,安装补钉程序等,别的,"ALTER","GRANT","REVOKE"等操纵也会使object变得有效,
能够经由过程object的"LAST_DDL"察看这些变更.
当object变得有效时,Oracle会在第一次会见此object时试图往从头编译它,假如此时其他session已把此objectpin
到librarycache中,就会呈现成绩,出格时当有大批的举动session而且存在较庞大的dependence时.在某种情形下,从头
编译object大概会花几个小不时间,从而堵塞别的试图往会见此object的历程.

上面让我们经由过程一个例子来摹拟及注释这个守候:

1.创立测试用存储历程

[oracle@jumperudump]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-ProductiononMonSep614:16:572004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.

Connectedtoanidleinstance.

SQL>startup
ORACLEinstancestarted.

TotalSystemGlobalArea47256168bytes
FixedSize451176bytes
VariableSize29360128bytes
DatabaseBuffers16777216bytes
RedoBuffers667648bytes
Databasemounted.
Databaseopened.
SQL>createorreplacePROCEDUREpining
2IS
3BEGIN
4NULL;
5END;
6/

Procedurecreated.

SQL>
SQL>createorreplaceprocedurecalling
2is
3begin
4pining;
5dbms_lock.sleep(3000);
6end;
7/

Procedurecreated.

SQL>

2.摹拟
起首实行calling历程,在calling过程当中挪用pining历程
此时pining历程上取得共享Pin,假如此时实验对pining举行受权或从头编译,将发生LibraryCachePin守候
直到calling实行终了.

session1:

[oracle@jumperoracle]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-ProductiononMonSep616:13:432004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


Connectedto:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>execcalling

此时calling入手下手实行

session2:

[oracle@jumperudump]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-ProductiononMonSep616:14:162004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


Connectedto:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>grantexecuteonpiningtoeygle;

此时session2挂起

ok,我们入手下手我们的研讨:

从v$session_wait动手,我们能够失掉哪些session正在履历librarycachepin的守候

SQL>selectsid,seq#,event,p1,p1raw,p2,p2raw,p3,p3raw,state
2fromv$session_waitwhereeventlikelibrary%;


SIDSEQ#EVENTP1P1RAWP2P2RAWP3WAIT_TIMESECONDS_IN_WAITSTATE
---------------------------------------------------------------------------------------------------------------------------
8268librarycachepin138978586852D6730C138743931252B2A4D030102WAITING

守候3秒就超时,seq#会产生变更

SQL>

SIDSEQ#EVENTP1P1RAWP2P2RAWP3WAIT_TIMESECONDS_IN_WAITSTATE
---------------------------------------------------------------------------------------------------------------------------
8269librarycachepin138978586852D6730C138743931252B2A4D030102WAITING

SQL>

SIDSEQ#EVENTP1P1RAWP2P2RAWP3WAIT_TIMESECONDS_IN_WAITSTATE
---------------------------------------------------------------------------------------------------------------------------
8270librarycachepin138978586852D6730C138743931252B2A4D030100WAITING


在这个输入中,P1列是LibraryCacheHandleAddress,Pn字段是10进制暗示,PnRaw字段是16进制暗示

我们看到,librarycachepin守候的工具的handle地点为:52D6730C
经由过程这个地点,我们查询X$KGLOB视图就能够失掉工具的详细信息:

Note:X$KGLOB--[K]ernel[G]eneric[L]ibraryCacheManager[OB]ject

colKGLNAOWNfora10
colKGLNAOBJfora20
selectADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
fromX$KGLOB
whereKGLHDADR=52D6730C
/


ADDRKGLHDADRKGLHDPARKGLNAOWNKGLNAOBJKGLNAHSHKGLHDOBJ
------------------------------------------------------------------------
404F9FF052D6730C52D6730CSYSPINING230025031852D65BA4

这里KGLNAHSH代表该工具的HashValue

由此我们晓得,在PINING工具上正派历librarycachepin的守候.

然后我们引进别的一个外部视图X$KGLPN:

Note:X$KGLPN--[K]ernel[G]eneric[L]ibraryCacheManagerobject[P]i[N]s

selecta.sid,a.username,a.program,b.addr,b.KGLPNADR,b.KGLPNUSE,b.KGLPNSES,b.KGLPNHDL,
b.kGLPNLCK,b.KGLPNMOD,b.KGLPNREQ
fromv$sessiona,x$kglpnb
wherea.saddr=b.kglpnuseandb.kglpnhdl=52D6730Candb.KGLPNMOD0
/

SIDUSERNAMEPROGRAMADDRKGLPNADRKGLPNUSEKGLPNSESKGLPNHDLKGLPNLCKKGLPNMODKGLPNREQ
----------------------------------------------------------------------------------------------------------------------------
13SYSsqlplus@jumper.hurray.com.cn(TNSV1-V3)404FA03452B2A51851E2013C51E2013C52D6730C52B294C820


经由过程团结v$session,能够取得以后持有该handle的用户信息.
关于我们的测试sid=13的用户正持有该handle

那末这个用户正在等甚么呢?

SQL>select*fromv$session_waitwheresid=13;

SIDSEQ#EVENTP1TEXTP1P1RAWP2TEXTP2P2RAWP3TEXTP3P3RAWWAIT_TIMESECONDS_IN_WAITSTATE
----------------------------------------------------------------------------------------------------------------------------------------------------
1325PL/SQLlocktimerduration1200000001D4C000000001200WAITING

Ok,这个用户正在守候一次PL/SQLlocktimer计时.

失掉了sid,我们就能够经由过程v$session.SQL_HASH_VALUE,v$session.SQL_ADDRESS等字段联系关系v$sqltext,v$sqlarea等视图取得以后session正在实行的操纵.

SQL>selectsql_textfromv$sqlareawherev$sqlarea.hash_value=3045375777;

SQL_TEXT
--------------------------------------------------------------------------------
BEGINcalling;END;

这里我们失掉这个用户正在实行calling这个存储历程,接上去的事情就应当往反省calling在作甚么了.

我们这个calling作的事情是dbms_lock.sleep(3000)
也就是PL/SQLlocktimer正在守候的缘故原由

至此就找到了LibraryCachePin的缘故原由.

简化一下以上查询:

1.取得LibraryCachePin守候的工具

SELECTaddr,kglhdadr,kglhdpar,kglnaown,kglnaobj,kglnahsh,kglhdobj
FROMx$kglob
WHEREkglhdadrIN(SELECTp1raw
FROMv$session_wait
WHEREeventLIKElibrary%)
/

ADDRKGLHDADRKGLHDPARKGLNAOWNKGLNAOBJKGLNAHSHKGLHDOBJ
------------------------------------------------------------------------
404F217852D6730C52D6730CSYSPINING230025031852D65BA4

2.取得持有守候工具的session信息

SELECTa.SID,a.username,a.program,b.addr,b.kglpnadr,b.kglpnuse,
b.kglpnses,b.kglpnhdl,b.kglpnlck,b.kglpnmod,b.kglpnreq
FROMv$sessiona,x$kglpnb
WHEREa.saddr=b.kglpnuse
ANDb.kglpnmod0
ANDb.kglpnhdlIN(SELECTp1raw
FROMv$session_wait
WHEREeventLIKElibrary%)
/
SQL>

SIDUSERNAMEPROGRAMADDRKGLPNADRKGLPNUSEKGLPNSESKGLPNHDLKGLPNLCKKGLPNMODKGLPNREQ
----------------------------------------------------------------------------------------------------------------------------------------
13SYSsqlplus@jumper.hurray.com.cn(TNSV1-V3)404F6CA452B2A51851E2013C51E2013C52D6730C52B294C820

3.取得持有工具用户实行的代码

SELECTsql_text
FROMv$sqlarea
WHERE(v$sqlarea.address,v$sqlarea.hash_value)IN(
SELECTsql_address,sql_hash_value
FROMv$session
WHERESIDIN(
SELECTSID
FROMv$sessiona,x$kglpnb
WHEREa.saddr=b.kglpnuse
ANDb.kglpnmod0
ANDb.kglpnhdlIN(SELECTp1raw
FROMv$session_wait
WHEREeventLIKElibrary%)))
/

SQL_TEXT
--------------------------------------------------------------------------------
BEGINcalling;END;


在grant之前和以后我们能够转储一下sharedpool的内容察看对照一下:

SQL>ALTERSESSIONSETEVENTSimmediatetracenameLIBRARY_CACHElevel32;

Sessionaltered.


在grant之前:

夙昔面的查询取得pining的Handle是52D6730C:

******************************************************
BUCKET67790:
LIBRARYOBJECTHANDLE:handle=52d6730c
name=SYS.PINING
hash=891b08cetimestamp=09-06-200416:43:51
namespace=TABL/PRCD/TYPEflags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011lock=Npin=Slatch#=1
--在Object上存在共享pin
--在handle上存在Null形式锁定,此形式同意其他用户持续以Null/shared形式锁定该工具
lwt=0x52d67324[0x52d67324,0x52d67324]ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8]ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314,0x52d67314]lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARYOBJECT:object=52d65ba4
type=PRCDflags=EXS/LOC[0005]pflags=NST[01]status=VALDload=0
DATABLOCKS:
data#heappointerstatuspinschangealloc(K)size(K)
-----------------------------------------------------
052d65dac52d65c90I/P/A0NONE0.300.55
452d65c4052d67c08I/P/A1NONE0.440.48


在收回grant命令后:

******************************************************
BUCKET67790:
LIBRARYOBJECTHANDLE:handle=52d6730c
name=SYS.PINING
hash=891b08cetimestamp=09-06-200416:43:51
namespace=TABL/PRCD/TYPEflags=KGHP/TIM/SML/[02000000]
kkkk-dddd-llll=0000-0011-0011lock=Xpin=Slatch#=1
--因为calling实行未完成,在object上仍让坚持共享pin
--因为grant会招致从头编译该工具,以是在handle上的排他锁已被持有
--进一步的必要取得object上的Exclusivepin,因为sharedpin被calling持有,以是librarycachepin守候呈现.
lwt=0x52d67324[0x52d67324,0x52d67324]ltm=0x52d6732c[0x52d6732c,0x52d6732c]
pwt=0x52d6733c[0x52b2a4e8,0x52b2a4e8]ptm=0x52d67394[0x52d67394,0x52d67394]
ref=0x52d67314[0x52d67314,0x52d67314]lnd=0x52d673a0[0x52d67040,0x52d6afcc]
LIBRARYOBJECT:object=52d65ba4
type=PRCDflags=EXS/LOC[0005]pflags=NST[01]status=VALDload=0
DATABLOCKS:
data#heappointerstatuspinschangealloc(K)size(K)
-----------------------------------------------------
052d65dac52d65c90I/P/A0NONE0.300.55
452d65c4052d67c08I/P/A1NONE0.440.48


实践上recompile历程包括以下步骤,我们看一下lock和pin是怎样瓜代发扬感化的:
1.存储历程的librarycacheobject以排他形式被锁定,这个锁定是在handle上取得的
exclusive锁定能够避免其他用户实行一样的操纵,同时避免其他用户创立新的援用此历程的工具.
2.以shared形式pin该工具,以实行平安和毛病反省.
3.共享pin被开释,从头以排他形式pin该工具,实行重编译.
4.使一切依附该历程的工具生效
5.开释exclusivelock和exclusivepin


(二).LIBRARYCACHELOCK守候事务

假如此时我们再收回一条grant或compile的命令,那末librarycachelock守候事务将会呈现:

session3:

[oracle@jumperoracle]$sqlplus"/assysdba"

SQL*Plus:Release9.2.0.3.0-ProductiononTueSep717:05:252004

Copyright(c)1982,2002,OracleCorporation.Allrightsreserved.


Connectedto:
Oracle9iEnterpriseEditionRelease9.2.0.3.0-Production
WiththePartitioning,OLAPandOracleDataMiningoptions
JServerRelease9.2.0.3.0-Production

SQL>alterprocedurepiningcompile;

此历程挂起,我们查询v$session_wait视图能够取得以下信息:

SQL>select*fromv$session_wait;

SIDSEQ#EVENTP1TEXTP1P1RAWP2TEXTP2P2RAWP3TEXTP3P3RAWWAIT_TIMESECONDSSTATE
-------------------------------------------------------------------------------------------------------------------------------------------
11143librarycachepinhandleaddress139023971652DD5FE4pinaddress138761745652B55CB0100*mode+namespace3010000012D06WAITING
1318librarycachelockhandleaddress139023971652DD5FE4lockaddress138743398452B29000100*mode+namespace3010000012D03WAITING
8415PL/SQLlocktimerduration1200000001D4C0000000063WAITING
....

13rowsselected

因为handle上的lock已被session2以exclusive形式持有,以是session3发生了守候.

我们能够看到,在临盆数据库中权限的授与、工具的从头编译都大概会招致librarycachepin守候的呈现.
以是应当只管制止在岑岭期举行以上操纵.

别的我们测试的案例自己就申明:假如Package或过程当中存在庞大的、交互的依附以来干系极易招致librarycachepin的呈现.
以是在使用开辟的过程当中,我们也应当注重这方面的内容.


MySQL最初的开发者的意图是用mSQL和他们自己的快速低级例程(ISAM)去连接表格。经过一些测试后，开发者得出结论：mSQL并没有他们需要的那么快和灵活。

其实可以做一下类比，Oracle等数据库产品老早就支持了java编程，而且提供了java池参数作为用户配置接口。但是现在有哪些系统大批使用了java存储过程？！连Oracle自己的应用都不用为什么？！

习惯敲命令行的朋友可能会爽一些。但是功能有限。适合机器跑不动SQLServerManagementStudio的朋友使用。

入门没那么困难，精通没那么容易

另一个是把SQL语句写到服务器端,就是所谓的SP(存储过程);

但换公司用MSSQL2K感觉自己好像根本就不了解MSSQL。什么DTS触发器以前根本没用过。

始终遗憾SQLServer的登陆无法分配CPU/内存占用等指标数。如果你的SQLServer给别人分配了一个只可以读几个表的权限，而这个家伙疯狂的死循环进行连接查询，会给你的系统带来很大的负担。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

分区表是个亮点！从分区表也能看出微软要做大作强SQLServer的信心。资料很多，这里不详细说。但是重点了解的是：现在的SQLServer2005的表，都是默认为分区表的。因为它要支持滑动窗口的这个特性。这种特性对历史数据和实时数据的处理是很有帮助的。