MSSQL网页设计关于shared pool的深切切磋(六)

仓酷云

php本地模拟的prepare底层就是mysql_real_escape_string，所以必须得用mysql_set_character_set去设置mysql->charset，否则就存在字符集问题。
关于sharedpool的深切切磋(六)

原文链接:

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

研讨了几天sharedpool,没想到溘然就撞到成绩下去了.
作为一个案例写出来给人人参考一下吧.

成绩原因是公司做短信群发,就是谁人18万买的4000字的短信小说.
群发的时分每隔一段工夫就会产生一次动静行列拥堵的情形
在数据库外部实践上是向一个数据表中纪录发送日记.

我们参与来反省数据库的成绩,在一个拥堵时段我入手下手诊断:

SQL>selectsid,event,p1,p1rawfromv$session_wait;

SIDEVENTP1P1RAW
--------------------------------------------------------------------------------------------
76latchfree21475358248000CBD0
83latchfree21475358248000CBD0
148latchfree3415346832CB920E90
288latchfree21475358248000CBD0
285latchfree21475358248000CBD0
196latchfree21475358248000CBD0
317latchfree21475358248000CBD0
2pmontimer3000000012C
1rdbmsipcmessage3000000012C
4rdbmsipcmessage3000000012C
6rdbmsipcmessage1800000002BF20

SIDEVENTP1P1RAW
--------------------------------------------------------------------------------------------
18rdbmsipcmessage600000001770
102rdbmsipcmessage600000001770
311rdbmsipcmessage600000001770
194rdbmsipcmessage600000001770
178rdbmsipcmessage600000001770
3logfileparallelwrite100000001
13logfilesync270500000A91
16logfilesync269900000A8B
104logfilesync269900000A8B
308logfilesync269400000A86
262logfilesync270500000A91

SIDEVENTP1P1RAW
--------------------------------------------------------------------------------------------
172logfilesync268900000A81
169logfilesync270500000A91
108logfilesync269400000A86
38logfilesync270700000A93
34dbfilescatteredread630000003F
5smontimer3000000012C
27SQL*Netmessagetoclient141369753654435000
60SQL*Netmessagetoclient141369753654435000
239SQL*Netmessagetoclient141369753654435000
...ignoresomeidlewaitinghere...
11SQL*Netmessagefromclient67556283528444553
12SQL*Netmessagefromclient141369753654435000

170rowsselected.

在此次查询中,我发明大批的latchfree守候,再次查询时这些守候消散,使用也恢复了一般.

SQL>selectsid,event,p1,p1rawfromv$session_waitwhereeventnotlikeSQL*Net%;

SIDEVENTP1P1RAW
--------------------------------------------------------------------------------------------
2pmontimer3000000012C
1rdbmsipcmessage3000000012C
4rdbmsipcmessage3000000012C
6rdbmsipcmessage1800000002BF20
18rdbmsipcmessage600000001770
102rdbmsipcmessage600000001770
178rdbmsipcmessage600000001770
194rdbmsipcmessage600000001770
311rdbmsipcmessage600000001770
3logfileparallelwrite100000001
148logfilesync2547000009F3

SIDEVENTP1P1RAW
--------------------------------------------------------------------------------------------
273logfilesync2544000009F0
190logfilesync2545000009F1
5smontimer3000000012C

14rowsselected.

接上去我们来看这些latchfree守候的是哪些latch

SQL>selectaddr,latch#,name,gets,spin_getsfromv$latchorderbyspin_gets;

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
800013983sessionswitching1119370
800020106longopfreelist372140
800023A07cachedattrlist00
8000262810eventgrouplatch23916680
.....
80003F3C28messagepooloperationsparentlatch30
.....
8000603060mostlylatch-freeSCN190
80005F8C59filenumbertranslationtable680
80005F1458dlmcrbastqueuelatch00
80005E8C57name-servicerequest00
80005E1456name-servicememoryobjects00
80005DA055name-servicenamespacebucket00

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
80005D2C54name-servicependingqueue00
80005CB453name-servicerequestqueue00
80004E0852name-serviceentry00
80008AB076KCLlockelementparentlatch00
80008A4875KCLinstancelatch00
80007F1873redocopy8160
80007BBC71archiveprocesslatch00
80007B5470archivecontrol10
80006A1068Activecheckpointqueuelatch20033080
800064B066largememorylatch00
8000644865cacheprotectionlatch00

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
800060EC61batchingSCNs00
8000CAB096globaltransaction68338070
8000CA4895globaltxfreelist582580
8000C23893costfunction00
80009FCC91templobdurationstateobjallocation00
8000995C87ktmglobaldata81180
8000922884transactionbranchallocation2823880
80008EC480beginbackupscnarray69680
80008D5479loaderstateobjectfreelist427120
80008B8078KCLfreelistlatch00
80008B1877KCLnametablelatch00

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
8000D484118presentationlist00
8000D41C117sessiontimer8559440
.....
8000E9D0129processqueue440
8000E900127queryserverfreelists660
8000FC84140AQPropagationSchedulingSystemLoad00
8000E898126queryserverprocess100
8000E27C125job_queue_processesparameterlatch1119370
8000DA1C124NLSdataobjects20

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
8000D95C123ncodefallocationlatch1119370
8000D674122virtualcircuits00
8000D60C121virtualcircuitqueues1598770
8000D5A4120virtualcircuitbuffers00
8000D4EC119addresslist20
.....
8000CD70102DirectI/OAdaptor20
.....
800024088GDSlatch300
800092E485sortextentpool698341
8000EC38132parallelqueryallocbuffer801
8000E968128errormessagelists221
800014004processgroupcreation26155422
8000EAA0131parallelquerystats142

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
8000CD08101TokenManager11511072
8000CB1897globaltxhashmapping5078462
8000637863cachebufferhandles3159244
8000EA38130processqueuereference1909935
80003E3C26channelhandlepoollatch239168018
80003EAC27channeloperationsparentlatch478342524
80009B9089intratxnparallelrecovery3265433
8000FCF8141fixedtablerowsforx$hs_session16136841
800012C81processallocation2391688154
80009B2888paralleltxnrecolatch174519271
8000CCA0100librarycacheloadlock149475455958

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
8000C8D094userlock130864126078
8000914C82listofblockallocation12065035712024
80006A7869Checkpointqueuelatch15436175117686
80009D3490sequencecache6461172032027
8000909081dmllockallocation23446502445351
800091C083transactionallocation21422764848345
800096AC86undoglobaldata18827124449641
800028A013enqueuehashchains373244264131322
80007E0472redoallocation439389808201498
800014685sessionidlebit2039097976204969
8000283812enqueues471338482273695

ADDRLATCH#NAMEGETSSPIN_GETS
------------------------------------------------------------------------------------------------------
800013302sessionallocation261826230428312
800063E064multiblockreadobjects13806149231366278
800026B811messages2079357581372606
800012180latchwaitlist2034795691445342
8000631062cachebufferschains3.8472E+102521699
8000A17C92rowcacheobjects12575867142555872
80007F8074redowriting2647229324458044
8000670067cachebufferslruchain566431376930046921
8000CBD098sharedpool12243368859070585
8000CC3899librarycache44145337961037032730

142rowsselected.

SQL>selectstartup_timefromv$instance;

STARTUP_T
---------
13-AUG-04

反省数据库启动工夫


我们注重到,在以后数据库中合作最严峻的两个latch是sharedpool和librarycache.
明显这极有多是SQL的过分剖析酿成的.

进一步我们反省v$sqlarea发明:
SQL>selectsql_text,VERSION_COUNT,INVALIDATIONS,PARSE_CALLS,OPTIMIZER_MODE,PARSING_USER_ID,PARSING_SCHEMA_ID,ADDRESS,HASH_VALUE
fromv$sqlareawhereversion_count>1000;
2


SQL_TEXT
------------------------------------------------------------------------------------------------------------------------
VERSION_COUNTINVALIDATIONSPARSE_CALLSOPTIMIZER_MODEPARSING_USER_IDPARSING_SCHEMA_IDADDRESSHASH_VALUE
----------------------------------------------------------------------------------------------------------------
insertintosms_log(MSGDATE,MSGTI提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。

个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。

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

语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！

换言之，只有在不断的失败中尝试成功，而关于失败的总结却是很少的

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。

你觉得我的非分区索引无法对起子分区，你可以提醒我一下呀！没有任何的提醒，直接就变成了非分区表。不知道这算不算一个bug。大家也可以试试。

varchar(max)\\\\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。