Oracle性能分析和调整

1、性能分析与与调整Oraclle的SQQL执行计计划Auto_tracce设置步骤： SQL connn syystemm/oraacle已连接。SQL starrt ?rdbmmsaddminutlxxplann表已创建。SQL creaate ppubliic syynonyym pllan_ttablee forr plaan_taable;同义词已创创建。SQL grannt alll onn plaan_taable to ppubliic;授权成功。SQL connn syss/oraacle as ssysdbba已连接。SQL starrt ?sqlpplusadmiinpllu

2、strrceSQL dropp rolle pllustrrace;drop rolee pluustraaceSQL creaate rrole plusstracce;角色已创建建SQL grannt seelectt on v_$ssessttat tto pllustrrace;授权成功。SQL grannt seelectt on v_$sstatnname to pplusttracee;授权成功。SQL grannt seelectt on v_$ssessiion tto pllustrrace;授权成功。SQL grannt pllustrrace to ddba wwith

3、 admiin opptionn;授权成功。SQL set echoo offfSQL grannt pllustrrace to ppubliic;授权成功。SQL connn scoott/ttigerr已连接。SQL set autootracce onnSQL seleect eenamee,sall froom emmp;ENAMEE SALL- -SMITHH 8000ALLENN 16000WARD 12500JONESS 29755MARTIIN 12500BLAKEE 28500CLARKK 24500KING 50000TURNEER 15000JAMESS 9500FOR

4、D 30000MILLEER 13000已选择122行。Execuutionn Plaan- 0 SELLECT STATTEMENNT Opptimiizer=CHOOOSE 1 00 TTABLEE ACCCESS (FULLL) OOF EEMPStatiisticcs- 12 recuursivve caalls 0 db bblockk getts 92 conssisteent ggets 0 physsicall reaads 0 redoo sizze 5588 bytees seent vvia SSQL*NNet tto cllientt 5503 bytees reece

5、ivved vvia SSQL*NNet ffrom clieent 2 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 12 rowss proocesssed设置auttotraace的一一些选项：set aautottracee offf：执行计计划和统计计信息都不不显示，这这是缺省的的设置。set aautorrtracce onn expplainn：只显示示执行计划划。set aautottracee on stattistiics：只只显示统计计信息。set aautottr

6、acee on：执行计划划和统计信信息都显示示。set aautottracee traaceonnly：类类似于seet auutotrrace on，只只是不显示示查询结果果。set aautottracee traaceonnly eexplaain：只只显示执行行计划。set aautottracee traaceonnly sstatiisticcs：只显显示统计结结果。How tto reead aa queery pplan关于staatisttics的的解释recurrsivee callls：高recuursivve caalls的的原因：hard parees：第二二次执行

7、同同一语句即即可使reecurssive callls降低。可以通过两两次同样的的查询，验验证上述结结论。pl/sqql fuunctiion ccallss：SQL creaate oor reeplacce fuunctiion ttest retuurn nnumbeer 2 as 3 ll_cntt nummber; 4 begiin 5 sselecct coount(*) iinto l_cnnt frrom ddept; 6 rreturrn l_cnt; 7 end;/函数已创建建。SQL seleect eenamee,tesst frrom eemp;ENAMEE TEST

8、T- -SMITHH 66ALLENN 66WARD 66JONESS 66MARTIIN 66BLAKEE 66CLARKK 66KING 66TURNEER 66JAMESS 66FORD 66MILLEER 66已选择122行。Execuutionn Plaan- 0 SELLECT STATTEMENNT Opptimiizer=CHOOOSE 1 00 TTABLEE ACCCESS (FULLL) OOF EEMPStatiisticcs- 2284 recuursivve caalls 0 db bblockk getts 1144 conssisteent ggets 6 ph

9、yssicall reaads 1136 redoo sizze 5579 bytees seent vvia SSQL*NNet tto cllientt 5503 bytees reeceivved vvia SSQL*NNet ffrom clieent 2 SQL*Net rounndtriips tto/frrom ccliennt 9 sortts (mmemorry) 0 sortts (ddisk) 12 rowss proocesssedSQL /ENAMEE TESTT- -SMITHH 66ALLENN 66WARD 66JONESS 66MARTIIN 66BLAKEE

10、 66CLARKK 66KING 66TURNEER 66JAMESS 66FORD 66MILLEER 66已选择122行。Execuutionn Plaan- 0 SELLECT STATTEMENNT Opptimiizer=CHOOOSE 1 00 TTABLEE ACCCESS (FULLL) OOF EEMPStatiisticcs- 12 recuursivve caalls 0 db bblockk getts 92 conssisteent ggets 0 physsicall reaads 0 redoo sizze 5579 bytees seent vvia SSQL*

11、NNet tto cllientt 5503 bytees reeceivved vvia SSQL*NNet ffrom clieent 2 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 12 rowss proocesssedSQL /ENAMEE TESTT- -SMITHH 66ALLENN 66WARD 66JONESS 66MARTIIN 66BLAKEE 66CLARKK 66KING 66TURNEER 66JAMESS 66FORD 66MILLEER 66已选择122

12、行。Execuutionn Plaan- 0 SELLECT STATTEMENNT Opptimiizer=CHOOOSE 1 00 TTABLEE ACCCESS (FULLL) OOF EEMPStatiisticcs- 12 recuursivve caalls 0 db bblockk getts 92 conssisteent ggets 0 physsicall reaads 0 redoo sizze 5579 bytees seent vvia SSQL*NNet tto cllientt 5503 bytees reeceivved vvia SSQL*NNet ffrom

13、 clieent 2 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 12 rowss proocesssedside effeect ffrom modiificaationn：由于ttrigggers、基于函数数的索引引引起。spacee reqquestt：DMTT表空间中中的表要求求空间时，会会引起较多多的reccursiive ccallss，通过使使用LMTT，可以显显著减少这这种reccursiive ccallss，而LMMT中的rrecurrsivee callls主要要

14、是由于验验证quoota权限限引起。可可以通过实实验验证上上面的说法法(实验步步骤见Efffecttive Oraccle bby Deesignn pp1101)。db bllock getss andd connsisttent getssdb bllock get是是以currrentt modde读取的的数据块数数，通常是是由于数据据修改而引引起，coonsisstentt getts是以cconsiistennt moode读取取的数据块块数，通常常由于seelectt操作引起起。我们关关注的是这这两个数量量之和，即即逻辑I/O的数量量，逻辑II/O也代代表了对缓缓存加上llatch

15、h的数量，逻逻辑I/OO越少，越越好。Thhe leess llogiccal II/O wwe caan doo,thee bettter。我们可以以通过设置置合适的aarrayysizee(许多方方法中的一一个，适用用于sqll*pluus)来降降低逻辑II/O数量量，ODBBC，JDDBC也有有类似的设设置。Arrayy sizze：SQL connn sysstem/oraccleccatallog已连接。SQL grannt dbba too scoott;授权成功。SQL connn scoott/ttigerrcattalogg已连接。SQL dropp tabble tt;表已

16、丢弃。SQL creaate ttablee t 2 as 3 seleect * froom alll_obbjectts;表已创建。SQL seleect ccountt(*) fromm t; COUUNT(*)-6219已选择62219行。SQL set autootracce trraceoonly stattistiicsSQL seleect * froom t;已选择62219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 4491 conssisteent ggets 0 physsicall reaads 0

17、redoo sizze 3571171 bytees seent vvia SSQL*NNet tto cllientt 50057 bytees reeceivved vvia SSQL*NNet ffrom clieent 4416 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 62219 rowss proocesssedSQL showw arrraysiizearrayysizee 15SQL set arraaysizze 2SQL seleect *fromm t;已选择6

18、2219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 31156 conssisteent ggets 0 physsicall reaads 0 redoo sizze 6832239 bytees seent vvia SSQL*NNet tto cllientt 347702 bytees reeceivved vvia SSQL*NNet ffrom clieent 31111 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (dd

19、isk) 62219 rowss proocesssedSQL set arraaysizze 4SQL /已选择62219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 16618 conssisteent ggets 0 physsicall reaads 0 redoo sizze 4951111 bytees seent vvia SSQL*NNet tto cllientt 175597 bytees reeceivved vvia SSQL*NNet ffrom clieent 15556 SQL*Net rounnd

20、triips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 62219 rowss proocesssedSQL set arraaysizze 8SQL /已选择62219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 8853 conssisteent ggets 0 physsicall reaads 0 redoo sizze 4010094 bytees seent vvia SSQL*NNet tto cllientt 90050 bytees reec

21、eivved vvia SSQL*NNet ffrom clieent 7779 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 62219 rowss proocesssedSQL set arraaysizze 166SQL /已选择62219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 4465 conssisteent ggets 0 physsicall reaads 0 redoo sizze 3540025 b

22、ytees seent vvia SSQL*NNet tto cllientt 47771 bytees reeceivved vvia SSQL*NNet ffrom clieent 3390 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 0 sortts (ddisk) 62219 rowss proocesssedphisiical readds：指OOraclle把数据据从硬盘读读到内存的的次数，也也就是读取取到内存的的数据块数数，然后我我们执行逻逻辑I/OO从内存读读取数据，因因此，一般般情况下，物物理I/OO

23、都跟随着着逻辑I/O。phisiical readds分为两两种：readiing ddata in ffrom dataafilees：对数数据文件读读取获得索索引数据或或者表数据据，这种II/O立刻刻跟随着逻逻辑I/OO。direcct reeads fromm temmp：当ssort areaa或hassh arrea不能能在内存中中容纳soort ddata或或hashh datta时，OOraclle会把部部分数据交交换到teemp表空空间，然后后再读取，这这种读取会会越过buufferr cacche，不不会引发逻逻辑I/OO。第一种物理理I/O，我我们不能避避免，如果果在第一次

24、次查询后，同同样的查询询还需要物物理I/OO,，则可可能是因为为dataa bufffer cachhe太小，在在物理内存存足够的情情况下，可可以把daata bbuffeer caache增增大。对于第二种种，我们可可以通过设设置合适的的sortt_areea_siize和hhash_areaa_sizze大小，来来降低phhisiccal rreadss，注意，在在Oraccle9ii中，要先先把worrkareea_siize_ppoliccy参数设设置为maanuall，改动ssort_areaa_sizze及haash_aarea_sizee参数才会会生效，88i可以直直接设置sso

25、rt_areaa_sizze。SQL connn scoott/ttigerrcattalogg已连接。SQL showw parrametter wworkNAME TYPEE VALUUE- - -workaarea_sizee_pollicy striing AUTOOSQL alteer seessioon seet woorkarrea_ssize_poliicy=mmanuaal;会话已更改改。SQL alteer seessioon seet soort_aarea_sizee=0;会话已更改改。SQL set autootracce trraceoonly stattistii

26、csSQL seleect * froom t ordeer byy objject_id;已选择62219行。Statiisticcs- 0 recuursivve caalls 24 db bblockk getts 80 conssisteent ggets 2214 physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26 SQL*Net rounndtriips tto/f

27、rrom ccliennt 0 sortts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedSQL /已选择62219行。Statiisticcs- 0 recuursivve caalls 22 db bblockk getts 80 conssisteent ggets 2212 physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clie

28、ent 26 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedSQL alteer seessioon seet soort_aarea_sizee=10224;会话已更改改。SQL seleect * froom t ordeer byy objject_id;已选择62219行。Statiisticcs- 0 recuursivve caalls 59 db bblockk getts 80 conssisteent ggets 4435

29、physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedSQL /已选择62219行。Statiisticcs- 0 recuursivve caalls 59 db bblockk

30、getts 80 conssisteent ggets 4437 physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26 SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedSQL alteer seessioon seet soort

31、_aarea_sizee=1022400;会话已更改改。SQL seleect * froom t ordeer byy objject_id;已选择62219行。Statiisticcs- 0 recuursivve caalls 19 db bblockk getts 80 conssisteent ggets 1198 physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26

32、SQL*Net rounndtriips tto/frrom ccliennt 0 sortts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedSQL alteer seessioon seet soort_aarea_sizee=1022400000;会话已更改改。SQL seleect * froom t ordeer byy objject_id;已选择62219行。Statiisticcs- 0 recuursivve caalls 0 db bblockk getts 80 conssisteent ggets 0 physsi

33、call reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26 SQL*Net rounndtriips tto/frrom ccliennt 1 sortts (mmemorry) 0 sortts (ddisk) 62219 rowss proocesssedSQL alteer seessioon seet soort_aarea_sizee=0;会话已更改改。SQL seleect * fro

34、om t ordeer byy objject_id;已选择62219行。Statiisticcs- 0 recuursivve caalls 22 db bblockk getts 80 conssisteent ggets 2212 physsicall reaads 0 redoo sizze 2192216 bytees seent vvia SSQL*NNet tto cllientt 7767 bytees reeceivved vvia SSQL*NNet ffrom clieent 26 SQL*Net rounndtriips tto/frrom ccliennt 0 sor

35、tts (mmemorry) 1 sortts (ddisk) 62219 rowss proocesssedredo sizee主要在buulk iinserrt操作(CTASS及inssert sellect)时需要调调整，一般般的merrge、iinserrt、deeletee、upddate语语句我们不不能控制其其redoo大小。在在归档模式式下，要把把表设置为为noloogginng，innsertt数据时，加加上apppend提提示。alterr tabble ttest_redoo nolloggiing;inserrt /*+ apppendd */ intoo tesst_r

36、eedo seleect * froom alll_obbjectts;非归档模式式，不需要要把表设置置为nolloggiing，只只要加上aappennd提示，也也会降低rredo sizee的大小。如果表建有有索引，则则要把索引引停用，否否则依然有有大量的rredo：alterr inddex iidx_ttest unussablee;alterr sesssionn sett skiip_unnusabble_iindexxes=ttrue;alterr inddex iidx_ttest rebuuild noloogginng;sql*nnet sstatiisticcssorts

37、s andd rowws prrocesssedSQL_TTRACEE的使用方方法设置步骤：SQL connn sysstem/oraccleSQL alteer syystemm sett sqll_traace=ttrue scoppe=sppfilee;SQL alteer syystemm sett timmed_sstatiisticcs=trrue;SQL connn syss/oraacle as ssysdbbaSQL starrtup forcceSQL grannt dbba too scooott;SQL connn scoott/ttigerrSQL seleect *

38、 froom deept;SQL seleect aa.spiid frrom vv$proocesss a,vv$sesssionn b 2 wherre a.addrr=b.ppaddrr 3 and b.auudsidd=useerenvv(seessioonid) 4 /SPID-2756C:ttkproof e:oraacleadmiinoeemreppuduumpooemreep_orra_27756.ttrc cc:reeportt.txtt报告内容：TKPROOF: RReleaase 99.2.00.1.00 - PProduuctioon onn 星期二二 2月 17 22

39、1:122:16 20044Copyrrightt (c) 19882, 22002, Oraacle Corpporattion. Alll riightss resserveed.Tracee fille: ee:orracleeadmminooemreepuddumpoemrrep_oora_22756.trcSort optiions: deffaultt*countt = nuumberr of timees OCCI prroceddure was execcuteddcpu = cppu tiime iin seecondds exxecutting elapssed = ell