仓酷云

标题: MYSQL网页编程之Oracle中捕捉成绩SQL办理CPU过渡损耗 [打印本页]

作者: 金色的骷髅 时间: 2015-1-16 22:33
标题: MYSQL网页编程之Oracle中捕捉成绩SQL办理CPU过渡损耗
列举选择MySQL的理由的最困难的地方在于，如何对这些理由进行排序。MySQL学习教程这就如同我们经常争论的故事：先有鸡还是先有蛋?oracle|办理|成绩本文经由过程实践营业体系中调剂的一个案例，试图给出一个罕见CPU损耗成绩的一个诊断办法.年夜多半情形下，体系的功能成绩都是由不良SQL代码引发的，那末作为DBA，如何发明息争决这些SQL成绩就显得尤其主要.　　本案例平台为UNIX，以是不成制止的使用了一些Unix下经常使用的工具.如vmstat,top等.
　　本文合适读者局限:中初级.
　　体系情况:
　　　OS:Solaris8
　　　Oracle:8.1.7.4
　　成绩形貌:
　　开辟职员呈报体系运转迟缓,已影响营业体系一般利用.哀求帮忙诊断.
　　1.上岸数据库主机
　　利用vmstat反省，发明CPU资本已耗尽，大批义务位于运转行列:
bash-2.03$vmstat3
procsmemorypagediskfaultscpu
rbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid
0005504232146411200000000110429496719600-84-5-145
1310053680721518360566910220001003011791827959730
1310053773281522464817190220001002766801925779640
1300053824001524776676820000000003570853433169730
134005373616152051212710780220001003838958436239640
13600536939215184961079240550000002920857326399730
1320053649121516224635780000000003358794431199730
1290053586481511712189123600000000033661036531359550
129005354528151130412011940000000403235886429119640
1280053468481507704998230000000303189904830749640
1250053412481504704808430220006103563951433149550
1330053327441501112797980000000103218880529029730
12900532538414973681076430220001403184829728799640
1260053631441514320817530000000002533740921649730
1360053556241510512169566786000000103002860028109640
13010535144815029362675801821000000003126781229009640
12900534725614995681559132220000102225807619419820
116005338192149540017711620000000101947778116399730
　　2.利用Top命令
　　察看历程CPU耗用,发明没有分明太高CPU利用的历程
$top
lastpid:28313;loadaverages:99.90,117.54,125.7123:28:38
296processes:186sleeping,99running,2zombie,9oncpu
CPUstates:0.0%idle,96.5%user,3.5%kernel,0.0%iowait,0.0%swap
Memory:4096Mreal,1404Mfree,2185Mswapinuse,5114Mswapfree
PIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND
27082oracle8i13301328M1309Mrun0:171.29%oracle
26719oracle8i15501327M1306Msleep0:291.11%oracle
28103oracle8i13501327M1304Mrun0:061.10%oracle
28161oracle8i12501327M1305Mrun0:041.10%oracle
26199oracle8i14501328M1309Mrun0:421.10%oracle
26892oracle8i13301328M1310Mrun0:241.09%oracle
27805oracle8i14501327M1306Mcpu/10:101.04%oracle
23800oracle8i12301327M1306Mrun1:281.03%oracle
25197oracle8i13401328M1309Mrun0:571.03%oracle
21593oracle8i13301327M1306Mrun2:121.01%oracle
27616oracle8i14501329M1311Mrun0:141.01%oracle
27821oracle8i14301327M1306Mrun0:101.00%oracle
26517oracle8i13301328M1309Mrun0:330.97%oracle
25785oracle8i14401328M1309Mrun0:460.96%oracle
26241oracle8i14501327M1306Mrun0:420.96%oracle
　　3.反省历程数目
bash-2.03$ps-ef|grepora|wc-l
258
bash-2.03$ps-ef|grepora|wc-l
275
bash-2.03$ps-ef|grepora|wc-l
274
bash-2.03$ps-ef|grepora|wc-l
278
bash-2.03$ps-ef|grepora|wc-l
277
bash-2.03$ps-ef|grepora|wc-l
366
　　发明体系存在大批Oracle历程,约莫在300摆布，大批历程损耗了几近一切CPU资本,而一般情形下Oracle毗连数应当在100摆布.
　　4.反省数据库
　　查询v$session_wait猎取各历程守候事务
SQL>selectsid,event,p1,p1textfromv$session_wait;
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
124latchfree1.6144E+10address
1pmontimer300duration
2rdbmsipcmessage300timeout
3rdbmsipcmessage300timeout
11rdbmsipcmessage30000timeout
6rdbmsipcmessage180000timeout
4rdbmsipcmessage300timeout
134rdbmsipcmessage6000timeout
147rdbmsipcmessage6000timeout
275rdbmsipcmessage17995timeout
274rdbmsipcmessage6000timeout
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
118rdbmsipcmessage6000timeout
7bufferbusywaits17file#
56bufferbusywaits17file#
161bufferbusywaits17file#
195bufferbusywaits17file#
311bufferbusywaits17file#
314bufferbusywaits17file#
205bufferbusywaits17file#
269bufferbusywaits17file#
200bufferbusywaits17file#
164bufferbusywaits17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
140bufferbusywaits17file#
66bufferbusywaits17file#
10dbfilesequentialread17file#
18dbfilesequentialread17file#
54dbfilesequentialread17file#
49dbfilesequentialread17file#
48dbfilesequentialread17file#
46dbfilesequentialread17file#
45dbfilesequentialread17file#
35dbfilesequentialread17file#
30dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
29dbfilesequentialread17file#
22dbfilesequentialread17file#
178dbfilesequentialread17file#
175dbfilesequentialread17file#
171dbfilesequentialread17file#
123dbfilesequentialread17file#
121dbfilesequentialread17file#
120dbfilesequentialread17file#
117dbfilesequentialread17file#
114dbfilesequentialread17file#
113dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
111dbfilesequentialread17file#
107dbfilesequentialread17file#
80dbfilesequentialread17file#
222dbfilesequentialread17file#
218dbfilesequentialread17file#
216dbfilesequentialread17file#
213dbfilesequentialread17file#
199dbfilesequentialread17file#
198dbfilesequentialread17file#
194dbfilesequentialread17file#
192dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
188dbfilesequentialread17file#
249dbfilesequentialread17file#
242dbfilesequentialread17file#
239dbfilesequentialread17file#
236dbfilesequentialread17file#
235dbfilesequentialread17file#
234dbfilesequentialread17file#
233dbfilesequentialread17file#
230dbfilesequentialread17file#
227dbfilesequentialread17file#
336dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
333dbfilesequentialread17file#
331dbfilesequentialread17file#
329dbfilesequentialread17file#
327dbfilesequentialread17file#
325dbfilesequentialread17file#
324dbfilesequentialread17file#
320dbfilesequentialread17file#
318dbfilesequentialread17file#
317dbfilesequentialread17file#
316dbfilesequentialread17file#
313dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
305dbfilesequentialread17file#
303dbfilesequentialread17file#
301dbfilesequentialread17file#
293dbfilesequentialread17file#
290dbfilesequentialread17file#
288dbfilesequentialread17file#
287dbfilesequentialread17file#
273dbfilesequentialread17file#
271dbfilesequentialread17file#
257dbfilesequentialread17file#
256dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
254dbfilesequentialread17file#
252dbfilesequentialread17file#
159dbfilesequentialread17file#
153dbfilesequentialread17file#
146dbfilesequentialread17file#
142dbfilesequentialread17file#
135dbfilesequentialread17file#
133dbfilesequentialread17file#
132dbfilesequentialread17file#
126dbfilesequentialread17file#
79dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
77dbfilesequentialread17file#
72dbfilesequentialread17file#
70dbfilesequentialread17file#
69dbfilesequentialread17file#
67dbfilesequentialread17file#
63dbfilesequentialread17file#
55dbfilesequentialread17file#
102dbfilesequentialread17file#
96dbfilesequentialread17file#
95dbfilesequentialread17file#
91dbfilesequentialread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
81dbfilesequentialread17file#
15dbfilesequentialread17file#
19dbfilescatteredread17file#
50dbfilescatteredread17file#
285dbfilescatteredread17file#
279dbfilescatteredread17file#
255dbfilescatteredread17file#
243dbfilescatteredread17file#
196dbfilescatteredread17file#
187dbfilescatteredread17file#
170dbfilescatteredread17file#
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
162dbfilescatteredread17file#
138dbfilescatteredread17file#
110dbfilescatteredread17file#
108dbfilescatteredread17file#
92dbfilescatteredread17file#
330dbfilescatteredread17file#
310dbfilescatteredread17file#
302dbfilescatteredread17file#
299dbfilescatteredread17file#
89dbfilescatteredread17file#
5smontimer300sleeptime
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
20SQL*Netmessagetoclient1952673792driverid
103SQL*Netmessagetoclient1650815232driverid
....
148SQL*Netmoredatafromclient1952673792driverid
291SQL*Netmoredatafromclient1952673792driverid
244rowsselected.
　　发明存在大批dbfilescatteredread及dbfilesequentialread守候.明显全表扫描等操纵成为体系最严峻的功能影响要素.
　　5.捕捉相干SQL
　　这里用到了我的以下剧本getsqlbysid:
SELECTsql_text
FROMv$sqltexta
WHEREa.hash_value=(SELECTsql_hash_value
FROMv$sessionb
WHEREb.SID=’&sid’)
ORDERBYpieceASC
/
　　该剧本依据用户sid,分离v$session和v$sqltext视图,取得用户sql语句的完全文本。用该剧本,经由过程从v$session_wait中取得的守候全表或索引扫描的历程SID，捕捉成绩sql:
SQL>@getsql
Entervalueforsid:18
old5:whereb.sid=’&sid’
new5:whereb.sid=’18’
SQL_TEXT
----------------------------------------------------------------
selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenab
ledflag=1andi.intpublishstate=1andi.datpublishdate<=
sysdateandi.numcatalogguid=2047orderbyi.datpublishdated
esc,i.numorderdesc
SQL>/
Entervalueforsid:54
old5:whereb.sid=’&sid’
new5:whereb.sid=’54’
SQL_TEXT
----------------------------------------------------------------
selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenab
ledflag=1andi.intpublishstate=1andi.datpublishdate<=
sysdateandi.numcatalogguid=33orderbyi.datpublishdatedes
c,i.numorderdesc
SQL>/
Entervalueforsid:49
old5:whereb.sid=’&sid’
new5:whereb.sid=’49’
SQL_TEXT
----------------------------------------------------------------
selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenab
ledflag=1andi.intpublishstate=1andi.datpublishdate<=
sysdateandi.numcatalogguid=26orderbyi.datpublishdatedes
c,i.numorderdesc

　　对几个历程举行跟踪,分离失掉以上SQL语句，这些SQL大概就是成绩发生的本源。以上语句假如优秀编码应当利用绑定变量.可是如今这个不是我们体贴的。利用该使用用户毗连，反省以上SQL的实行企图:
SQL>setautotracetraceexplain
SQL>selecti.vc2title,i.numinfoguid
2fromhs_infoiwherei.intenabledflag=1
3andi.intpublishstate=1andi.datpublishdate<=sysdate
4andi.numcatalogguid=3475
5orderbyi.datpublishdatedesc,i.numorderdesc;
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=228Card=1Bytes=106)
10SORT(ORDERBY)(Cost=228Card=1Bytes=106)
21TABLEACCESS(FULL)OF’HS_INFO’(Cost=218Card=1Bytes=106)
SQL>selectcount(*)fromhs_info;
COUNT(*)
----------
227404
　　以上查询利用了全表扫描,该表这里有22万纪录，全表扫描已不再合适。
　　反省该表，存在以下索引:
SQL>selectindex_name,index_typefromuser_indexeswheretable_name=’HS_INFO’;
INDEX_NAMEINDEX_TYPE
---------------------------------------------------------
HSIDX_INFO1FUNCTION-BASEDNORMAL
HSIDX_INFO_SEARCHKEYDOMAIN
PK_HS_INFONORMAL
　　反省索引键值:
SQL>selectindex_name,column_name
2fromuser_ind_columnswheretable_name=’HS_INFO’;
INDEX_NAMECOLUMN_NAME
--------------------------------------------------
HSIDX_INFO1NUMORDER
HSIDX_INFO1SYS_NC00024$
HSIDX_INFO_SEARCHKEYVC2INDEXWORDS
PK_HS_INFONUMINFOGUID
SQL>deschs_info
NameNull?Type
-------------------------------------------------------------------------------------
NUMINFOGUIDNOTNULLNUMBER(15)
NUMCATALOGGUIDNOTNULLNUMBER(15)
INTTEXTTYPENOTNULLNUMBER(38)
VC2TITLENOTNULLVARCHAR2(60)
VC2AUTHORVARCHAR2(100)
NUMPREVINFOGUIDNUMBER(15)
NUMNEXTINFOGUIDNUMBER(15)
NUMORDERNOTNULLNUMBER(15)
DATPUBLISHDATENOTNULLDATE
INTPUBLISHSTATENOTNULLNUMBER(38)
VC2PUBLISHERIDVARCHAR2(30)
VC2INDEXWORDSVARCHAR2(200)
VC2WAPPREVPATHVARCHAR2(200)
VC2WEBPREVPATHVARCHAR2(200)
VC2WAP2PREVPATHVARCHAR2(200)
NUMVISITEDNOTNULLNUMBER(15)
INTENABLEDFLAGNOTNULLNUMBER(38)
DATCREATETIMENOTNULLDATE
DATMODIFYTIMENOTNULLDATE
VC2NOTESVARCHAR2(1000)
INTINFOTYPENOTNULLNUMBER(38)
VC2PRIZEFLAGVARCHAR2(1)
VC2DESCVARCHAR2(1000)
　　6.决意创立新的索引以打消全表扫描
　　反省发明在numcatalogguid字段上并没有索引,该字段具有很好的辨别度，思索在该字段创立索引以打消全表扫描。
SQL>createindexhs_info_NUMCATALOGGUIDonhs_info(NUMCATALOGGUID);
Indexcreated.
SQL>setautotracetraceexplain
SQL>selecti.vc2title,i.numinfoguid
2fromhs_infoiwherei.intenabledflag=1
3andi.intpublishstate=1andi.datpublishdate<=sysdate
4andi.numcatalogguid=3475
5orderbyi.datpublishdatedesc,i.numorderdesc;
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=1Bytes=106)
10SORT(ORDERBY)(Cost=12Card=1Bytes=106)
21TABLEACCESS(BYINDEXROWID)OF’HS_INFO’(Cost=2Card=1
Bytes=106)
32INDEX(RANGESCAN)OF’HS_INFO_NUMCATALOGGUID’
(NON-UNIQUE)(Cost=1Card=1)
　　7.察看体系情况
　　原大批守候消散
SQL>selectsid,event,p1,p1textfromv$session_waitwhereeventnotlike’SQL%’;
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
1pmontimer300duration
2rdbmsipcmessage300timeout
3rdbmsipcmessage300timeout
6rdbmsipcmessage180000timeout
59rdbmsipcmessage6000timeout
118rdbmsipcmessage6000timeout
275rdbmsipcmessage30000timeout
147rdbmsipcmessage6000timeout
62rdbmsipcmessage6000timeout
11rdbmsipcmessage30000timeout
4rdbmsipcmessage300timeout
SIDEVENTP1P1TEXT
------------------------------------------------------------------------------------------------------------------
305dbfilesequentialread17file#
356dbfilesequentialread17file#
19dbfilescatteredread17file#
5smontimer300sleeptime
15rowsselected.
　　延续察看的CPU利用情形
bash-2.03$vmstat3
procsmemorypagediskfaultscpu
rbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid
20005421792150348838434136000000202931779526229190
23105416080150063295734560000000029498057259889110
2600541201614984802101170215500021033019647311690100
2500539491214901602421606560000001031339318285089110
4000539020014881121621393660000000028489080250290100
4000537712014817921361180120220001102846909925939280
3600536321614751681341169530000032028717989262188120
390053489361469160157144821000000000366010062348088120
35005344552146647271556000000002885766326359280
3400534301614654164438677000000003197848629029280
31005331568145969617814911220000003032379461300589110
3100531779214530087671980000000003292873630259370
3120531114414495522351263692200010034739535335788120
25005300240144392010875718220001102377787622749550
190052959041441840503770000000101915659815999810
----以上为创立索引之前部分
----以下为创立索引以后部分，CPU利用率恢复一般
procsmemorypagediskfaultscpu
rbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid
4010529004014392083153894822000160363113414520661930
01052371921414744731674945000002703264135584941521434
00051636321380608747658510000000102617122913901461241
10050902241348152712607929000000602825124164178501239
10050236721317296714618324000000503166124244745471340
00049558721287136737625816000000302890117774432441244
1004887888125646480962348220000202809120664247451243
000482891212282003122364135500021024106816349238657
00048568161240168813800000010023144026323234462
0004874176124771208600000000022983930332435263
2004926088127082434560000000000219246942612291655
0005427320151295253694000000320244350853340331255
0005509120155313603700000000023093908332135164
000556204815770001623400000000025075187343335857
00056656721623848252189682200010020916548293934561
000565475216182085173160000000022264218305135460
000572702416511202825400000000021264224298238260
000572318416488809356282200011023715140343238359
000573074416525127177262200010024654442357536361
　　至此，此成绩得以办理.
　　8.功能何故进步?
　　回覆这个成绩仿佛是过剩的,我只想重申一点:
　　无效的下降SQL的逻辑读是SQL优化的基础准绳之一，我们来对照一下前后两种实行体例的读取及功能差别。
　　全表扫描的功能：
SQL>selecti.vc2title,i.numinfoguid
2fromhs_infoiwherei.intenabledflag=1
3andi.intpublishstate=1andi.datpublishdate<=sysdate
4andi.numcatalogguid=3475
5orderbyi.datpublishdatedesc,i.numorderdesc;
352rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=541Card=1Bytes=106)
10SORT(ORDERBY)(Cost=541Card=1Bytes=106)
21TABLEACCESS(FULL)OF’HS_INFO’(Cost=531Card=1Bytes=106)
Statistics
----------------------------------------------------------
0recursivecalls
25dbblockgets
3499consistentgets
258physicalreads
0redosize
14279bytessentviaSQL*Nettoclient
2222bytesreceivedviaSQL*Netfromclient
25SQL*Netroundtripsto/fromclient
2sorts(memory)
0sorts(disk)
352rowsprocessed
　　利用索引的功能：
SQL>selecti.vc2title,i.numinfoguid
2fromhs_infoiwherei.intenabledflag=1
3andi.intpublishstate=1andi.datpublishdate<=sysdate
4andi.numcatalogguid=3475
5orderbyi.datpublishdatedesc,i.numorderdesc;
352rowsselected.
ExecutionPlan
----------------------------------------------------------
0SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=1Bytes=106)
10SORT(ORDERBY)(Cost=12Card=1Bytes=106)
21TABLEACCESS(BYINDEXROWID)OF’HS_INFO’(Cost=2Card=1
Bytes=106)
32INDEX(RANGESCAN)OF’HS_INFO_NUMCATALOGGUID’
(NON-UNIQUE)(Cost=1Card=1)
Statistics
----------------------------------------------------------
0recursivecalls
0dbblockgets
89consistentgets
0physicalreads
0redosize
14279bytessentviaSQL*Nettoclient
2222bytesreceivedviaSQL*Netfromclient
25SQL*Netroundtripsto/fromclient
1sorts(memory)
0sorts(disk)
352rowsprocessed
　　consistentgets从3499到89,我们看到功能失掉了伟大的进步。
　　停止语:
　　一般,开辟职员很少注重SQL代码的效力,他们更着眼于功效的完成.至于功能成绩一般被以为是主要的,并且在使用体系开辟早期,因为数据库数据量较少,关于查询SQL语句等,不简单体味出各类SQL句法的功能差别.
　　可是一旦这些使用作为临盆体系上线运转,跟着数据库中数据量的增添,大批并发会见,体系的呼应速率大概就会成为体系必要办理的最次要的成绩之一.
　　在大批用户下功能能够承受的SQL,大概在大批用户并发的前提下就会成为功能瓶颈。在我这个案例中,开辟职员很难信任仅只一条SQL语句就招致了全部数据库的功能下落。
　　但是现实就是云云,一条低效的SQL语句便可能毁失落你的数据库,以是在体系计划及开辟过程当中,你必需思索到诸多细节,严厉的测试也是延迟发明成绩的无效办法。
　　假如不幸以上环节都被疏忽,那末,DBA(大概就是你)就是最初的一环,你必需可以疾速的诊断并办理各类庞大成绩。
解决方案提供商应记住DBaaS通常仅仅是解决方案的一部分。客户之所以与他们的解决方案提供商协同工作，不仅是因为他们出售的产品，而且还因为他们所提供的服务。

作者: 乐观 时间: 2015-1-18 17:20
多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油

作者: 海妖 时间: 2015-1-22 16:51
如果，某一版本可以提供强大的并发响应，但是没有Oracle的相应版本稳定，或者价格较贵，那么，它就是不适合的。

作者: 飘飘悠悠 时间: 2015-1-31 08:12
SP4是一个累积性的ServicePack，包含自以前的ServicePack发布以来所有的修补程序（包括MS03-031安全公告）。

作者: 小魔女 时间: 2015-2-6 18:47
如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

作者: 再见西城 时间: 2015-2-18 06:58
一个百万级别的基本信息表A，一个百万级别的详细记录表B，A中有个身份证id，B中也有身份id；先要找出A中在B的详细记录。

作者: 愤怒的大鸟 时间: 2015-3-6 01:29
个人感觉没有case直观。而且默认的第三字段（还可能更多）作为groupby字段很容易造成新手的错误。

作者: 柔情似水 时间: 2015-3-12 18:26
作了些试验，发现使用CLR的存储过程或函数在达到一定的阀值的时候，系统性能会呈指数级下滑！这是非常危险的！只使用几个可能没有问题，当一旦大规模使用会造成严重的系统性能问题！

作者: 分手快乐 时间: 2015-3-20 01:32
我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

欢迎光临仓酷云 (http://ckuyun.com/)