仓酷云

标题: MYSQL网页设计Oracle诊断案例----怎样捕捉成绩SQL办理... [打印本页]

作者: 活着的死人 时间: 2015-1-16 22:41
标题: MYSQL网页设计Oracle诊断案例----怎样捕捉成绩SQL办理...
最近由权威调查机构Evans数据公司进行的一项调查显示，MySQL在过去两年已经获得了25%的市场份额。该调查公司还预测，相比其他的开源数据库和闭源数据库。oracle|办理|成绩
Oracle诊断案例----怎样捕捉成绩SQL办理过分CPU损耗成绩

--利用vmstat,top等帮助办理Oracle数据库功能成绩

LastUpdated:Sunday,2004-10-240:37Eygle

成绩形貌:
开辟职员呈报体系运转迟缓，影响用户会见.

1.上岸数据库主机

利用vmstat反省，发明CPU资本已耗尽，大批义务位于运转行列:

bash-2.03$vmstat3procsmemorypagediskfaultscpurbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid0005504232146411200000000110429496719600-84-5-145131005368072151836056691022000100301179182795973013100537732815224648171902200010027668019257796401300053824001524776676820000000003570853433169730134005373616152051212710780220001003838958436239640136005369392151849610792405500000029208573263997301320053649121516224635780000000003358794431199730129005358648151171218912360000000003366103653135955012900535452815113041201194000000040323588642911964012800534684815077049982300000003031899048307496401250053412481504704808430220006103563951433149550133005332744150111279798000000010321888052902973012900532538414973681076430220001403184829728799640126005363144151432081753000000000253374092164973013600535562415105121695667860000001030028600281096401301053514481502936267580182100000000312678122900964012900534725614995681559132220000102225807619419820116005338192149540017711620000000101947778116399730

2.利用Top命令

察看历程CPU耗用,发明没有分明太高CPU利用的历程
$top

lastpid:28313;loadaverages:99.90,117.54,125.7123:28:38296processes:186sleeping,99running,2zombie,9oncpuCPUstates:0.0%idle,96.5%user,3.5%kernel,0.0%iowait,0.0%swapMemory:4096Mreal,1404Mfree,2185Mswapinuse,5114MswapfreePIDUSERNAMETHRPRINICESIZERESSTATETIMECPUCOMMAND27082oracle8i13301328M1309Mrun0:171.29%oracle26719oracle8i15501327M1306Msleep0:291.11%oracle28103oracle8i13501327M1304Mrun0:061.10%oracle28161oracle8i12501327M1305Mrun0:041.10%oracle26199oracle8i14501328M1309Mrun0:421.10%oracle26892oracle8i13301328M1310Mrun0:241.09%oracle27805oracle8i14501327M1306Mcpu/10:101.04%oracle23800oracle8i12301327M1306Mrun1:281.03%oracle25197oracle8i13401328M1309Mrun0:571.03%oracle21593oracle8i13301327M1306Mrun2:121.01%oracle27616oracle8i14501329M1311Mrun0:141.01%oracle27821oracle8i14301327M1306Mrun0:101.00%oracle26517oracle8i13301328M1309Mrun0:330.97%oracle25785oracle8i14401328M1309Mrun0:460.96%oracle26241oracle8i14501327M1306Mrun0:420.96%oracle

3.反省历程数目

bash-2.03$ps-ef|grepora|wc-l258bash-2.03$ps-ef|grepora|wc-l275bash-2.03$ps-ef|grepora|wc-l274bash-2.03$ps-ef|grepora|wc-l278bash-2.03$ps-ef|grepora|wc-l277bash-2.03$ps-ef|grepora|wc-l366

发明体系存在大批Oracle历程,约莫在300摆布，而一般情形下Oracle毗连数应当在100摆布.

4.反省数据库

查询v$session_wait猎取各历程守候事务

SQL>selectsid,event,p1,p1textfromv$session_wait;SIDEVENTP1P1TEXT------------------------------------------------------------------------------------------------------------------124latchfree1.6144E+10address1pmontimer300duration2rdbmsipcmessage300timeout3rdbmsipcmessage300timeout11rdbmsipcmessage30000timeout6rdbmsipcmessage180000timeout4rdbmsipcmessage300timeout134rdbmsipcmessage6000timeout147rdbmsipcmessage6000timeout275rdbmsipcmessage17995timeout274rdbmsipcmessage6000timeoutSIDEVENTP1P1TEXT------------------------------------------------------------------------------------------------------------------118rdbmsipcmessage6000timeout7bufferbusywaits17file#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#5smontimer300sleeptimeSIDEVENTP1P1TEXT------------------------------------------------------------------------------------------------------------------20SQL*Netmessagetoclient1952673792driverid103SQL*Netmessagetoclient1650815232driverid....148SQL*Netmoredatafromclient1952673792driverid291SQL*Netmoredatafromclient1952673792driverid244rowsselected.

发明存在大批dbfilescatteredread及dbfilesequentialread守候.

5.捕捉相干SQL

这里用到了我的以下剧本getsqlbysid.sql:

SELECTsql_textFROMv$sqltextaWHEREa.hash_value=(SELECTsql_hash_valueFROMv$sessionbWHEREb.SID=&sid)ORDERBYpieceASC/

SQL>@getsqlEntervalueforsid:18old5:whereb.sid=&sidnew5:whereb.sid=18SQL_TEXT----------------------------------------------------------------selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenabledflag=1andi.intpublishstate=1andi.datpublishdate<=sysdateandi.numcatalogguid=2047orderbyi.datpublishdatedesc,i.numorderdescSQL>/Entervalueforsid:54old5:whereb.sid=&sidnew5:whereb.sid=54SQL_TEXT----------------------------------------------------------------selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenabledflag=1andi.intpublishstate=1andi.datpublishdate<=sysdateandi.numcatalogguid=33orderbyi.datpublishdatedesc,i.numorderdescSQL>/Entervalueforsid:49old5:whereb.sid=&sidnew5:whereb.sid=49SQL_TEXT----------------------------------------------------------------selecti.vc2title,i.numinfoguidfromhs_infoiwherei.intenabledflag=1andi.intpublishstate=1andi.datpublishdate<=sysdateandi.numcatalogguid=26orderbyi.datpublishdatedesc,i.numorderdesc

对几个全表扫描历程跟踪今后，失掉以上SQL语句.
以上语句假如优秀编码应当利用绑定变量.可是如今这个不是我们体贴的.

利用该使用用户毗连，反省其实行企图:

SQL>setautotracetraceexplainSQL>selecti.vc2title,i.numinfoguid2fromhs_infoiwherei.intenabledflag=13andi.intpublishstate=1andi.datpublishdate<=sysdate4andi.numcatalogguid=34755orderbyi.datpublishdatedesc,i.numorderdesc;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=228Card=1Bytes=106)10SORT(ORDERBY)(Cost=228Card=1Bytes=106)21TABLEACCESS(FULL)OFHS_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-BASEDNORMALHSIDX_INFO_SEARCHKEYDOMAINPK_HS_INFONORMAL反省索引键值:SQL>selectindex_name,column_namefromuser_ind_columnswheretable_name=HS_INFO;INDEX_NAMECOLUMN_NAME--------------------------------------------------HSIDX_INFO1NUMORDERHSIDX_INFO1SYS_NC00024$HSIDX_INFO_SEARCHKEYVC2INDEXWORDSPK_HS_INFONUMINFOGUIDSQL>deschs_infoNameNull?Type----------------------------------------------------------------------------------------------------NUMINFOGUIDNOTNULLNUMBER(15)NUMCATALOGGUIDNOTNULLNUMBER(15)INTTEXTTYPENOTNULLNUMBER(38)VC2TITLENOTNULLVARCHAR2(60)VC2AUTHORVARCHAR2(100)NUMPREVINFOGUIDNUMBER(15)NUMNEXTINFOGUIDNUMBER(15)NUMORDERNOTNULLNUMBER(15)DATPUBLISHDATENOTNULLDATEINTPUBLISHSTATENOTNULLNUMBER(38)VC2PUBLISHERIDVARCHAR2(30)VC2INDEXWORDSVARCHAR2(200)VC2WAPPREVPATHVARCHAR2(200)VC2WEBPREVPATHVARCHAR2(200)VC2WAP2PREVPATHVARCHAR2(200)NUMVISITEDNOTNULLNUMBER(15)INTENABLEDFLAGNOTNULLNUMBER(38)DATCREATETIMENOTNULLDATEDATMODIFYTIMENOTNULLDATEVC2NOTESVARCHAR2(1000)INTINFOTYPENOTNULLNUMBER(38)VC2PRIZEFLAGVARCHAR2(1)VC2DESCVARCHAR2(1000)

6.决意创立新的索引以打消全表扫描

SQL>createindexhs_info_NUMCATALOGGUIDonhs_info(NUMCATALOGGUID);Indexcreated.SQL>setautotracetraceexplainSQL>selecti.vc2title,i.numinfoguid2fromhs_infoiwherei.intenabledflag=13andi.intpublishstate=1andi.datpublishdate<=sysdate4andi.numcatalogguid=34755orderbyi.datpublishdatedesc,i.numorderdesc;ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE(Cost=12Card=1Bytes=106)10SORT(ORDERBY)(Cost=12Card=1Bytes=106)21TABLEACCESS(BYINDEXROWID)OFHS_INFO(Cost=2Card=1Bytes=106)32INDEX(RANGESCAN)OFHS_INFO_NUMCATALOGGUID(NON-UNIQUE)(Cost=1Card=1)

7.察看体系情况

原大批守候消散

SQL>selectsid,event,p1,p1textfromv$session_waitwhereeventnotlikeSQL%;SIDEVENTP1P1TEXT------------------------------------------------------------------------------------------------------------------1pmontimer300duration2rdbmsipcmessage300timeout3rdbmsipcmessage300timeout6rdbmsipcmessage180000timeout59rdbmsipcmessage6000timeout118rdbmsipcmessage6000timeout275rdbmsipcmessage30000timeout147rdbmsipcmessage6000timeout62rdbmsipcmessage6000timeout11rdbmsipcmessage30000timeout4rdbmsipcmessage300timeoutSIDEVENTP1P1TEXT------------------------------------------------------------------------------------------------------------------305dbfilesequentialread17file#356dbfilesequentialread17file#19dbfilescatteredread17file#5smontimer300sleeptime15rowsselected.

延续察看的CPU利用情形

bash-2.03$vmstat3procsmemorypagediskfaultscpurbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid200054217921503488384341360000002029317795262291902310541608015006329573456000000002949805725988911026005412016149848021011702155000210330196473116901002500539491214901602421606560000001031339318285089110400053902001488112162139366000000002848908025029010040005377120148179213611801202200011028469099259392803600536321614751681341169530000032028717989262188120390053489361469160157144821000000000366010062348088120350053445521466472715560000000028857663263592803400534301614654164438677000000003197848629029280310053315681459696178149112200000030323794613005891103100531779214530087671980000000003292873630259370312053111441449552235126369220001003473953533578812025005300240144392010875718220001102377787622749550190052959041441840503770000000101915659815999810----以上为创立索引之前部分----以下为创立索引以后部分，CPU利用率恢复一般procsmemorypagediskfaultscpurbwswapfreeremfpipofrdesrs6s9s1sdinsycsussyid40105290040143920831538948220001603631134145206619300105237192141474473167494500000270326413558494152143400051636321380608747658510000000102617122913901461241100509022413481527126079290000006028251241641785012391005023672131729671461832400000050316612424474547134000049558721287136737625816000000302890117774432441244100488788812564648096234822000020280912066424745124300048289121228200312236413550002102410681634923865700048568161240168813800000010023144026323234462000487417612477120860000000002298393033243526320049260881270824345600000000002192469426122916550005427320151295253694000000320244350853340331255000550912015531360370000000002309390833213516400055620481577000162340000000002507518734333585700056656721623848252189682200010020916548293934561000565475216182085173160000000022264218305135460000572702416511202825400000000021264224298238260000572318416488809356282200011023715140343238359000573074416525127177262200010024654442357536361

至此，此成绩得以办理.

本文作者:
eygle,Oracle手艺存眷者,来自中国最年夜的Oracle手艺论坛itpub.
www.eygle.com是作者的团体站点.你可经由过程Guoqiang.Gai@gmail.com来接洽作者.接待手艺切磋交换和链接互换.

原文出处:

http://www.eygle.com/case/How.To.Capture.Problem.SQL.htm
甚至一个有经验的Windows管理者也可以轻松部署并开始学习它，而你不需投入一分钱来了解这个数据库。

作者: 仓酷云 时间: 2015-1-25 09:34
如果你是从“学习某一种数据库应用软件，从而获得应聘的资本和工作机会”的角度来问的话。

作者: admin 时间: 2015-2-2 19:11
而写到本地,我又考虑到效率问题.大家来讨论讨论吧,分数不打紧,就给10分,十全十美,没啥对错,各抒己见,但是要有说服力的哦~

作者: 兰色精灵 时间: 2015-2-8 04:23
原来的计算字段其实和虚拟字段很像。只是管理方面好了而已，性能方面提高不多。但是SQL2005提供了计算字段的持久化，这就提高了查询的性能，但是会加重insert和update的负担。OLTP慎用。OLAP可以大规模使用。

作者: 简单生活 时间: 2015-2-24 05:49
相信各位对数据库和怎么样学习数据库都有一些经验和看法，也会有人走了一些弯路总结出自己的经验来，希望大家能把各自的看法和经验拿出来分享，给别人一份帮助，给自己一份快乐

作者: 谁可相欹 时间: 2015-3-7 11:33
我个人认为就是孜孜不懈的学习

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

作者: 柔情似水 时间: 2015-3-21 17:15
需要注意的一点，也是我使用过程中发现的一个问题。在建立function->schema->table后，如果在现有的分区表上建立没有显式声明的聚集索引时，分区表会自动变为非分区表。这一点很让我纳闷。

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