Oracle性能分析和調(diào)整

1、性能分析與調(diào)整Oracle的的SQL執(zhí)行行計(jì)劃Auto_trrace設(shè)置步驟：SQL coonn syystem/oraclle已連接。SQL sttart ?rdbmmsadmminuttlxplaan表已創(chuàng)建。SQL crreate publiic synnonym plan_tablee for plan_tablee;同義詞已創(chuàng)建。SQL grrant aall onn plann_tablle to publiic;授權(quán)成功。SQL coonn syys/oraacle aas syssdba已連接。SQL sttart ?sqlpplusaadminplusttrceSQL drr

2、op roole pllustraace;drop roole pllustraaceSQL crreate role plusttrace;角色已創(chuàng)建SQL grrant sselectt on vv_$sessstat to pllustraace;授權(quán)成功。SQL grrant sselectt on vv_$staatnamee to pplustrrace;授權(quán)成功。SQL grrant sselectt on vv_$sesssion to pllustraace;授權(quán)成功。SQL grrant pplustrrace tto dbaa withh admiin opttion;授

3、權(quán)成功。SQL seet echho offfSQL grrant pplustrrace tto pubblic;授權(quán)成功。SQL coonn sccott/ttiger已連接。SQL seet auttotracce onSQL seelect enamee,sal from emp;ENAME SAL- -SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450KING 5000TURNER 1500JAMES 950FORD 3000MILLER 1300已選擇12行。Executiion Pllan-

4、0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 TABLEE ACCEESS (FFULL) OF EEMPStatisttics- 12 recuursivee callls 0 db bblock gets 92 conssistennt getts 0 physsical readss 0 redoo sizee 588 bytees sennt viaa SQL*Net tto cliient 503 bytees recceivedd via SQL*NNet frrom cllient 2 SQL*Net rroundttrips to/fr

5、rom cllient 0 sortts (meemory) 0 sortts (diisk) 12 rowss proccessedd設(shè)置autottrace的的一些選項(xiàng)：set auttotracce offf：執(zhí)行計(jì)劃劃和統(tǒng)計(jì)信息息都不顯示，這這是缺省的設(shè)設(shè)置。set auttortraace onn expllain：只只顯示執(zhí)行計(jì)計(jì)劃。set auttotracce on statiisticss：只顯示統(tǒng)統(tǒng)計(jì)信息。set auttotracce on：執(zhí)行計(jì)劃和和統(tǒng)計(jì)信息都都顯示。set auttotracce traaceonlly：類似于于set aautotrrace oon

6、，只是不不顯示查詢結(jié)結(jié)果。set auttotracce traaceonlly expplain：只顯示執(zhí)行行計(jì)劃。set auttotracce traaceonlly staatistiics：只顯顯示統(tǒng)計(jì)結(jié)果果。How to read a queery pllan關(guān)于statiisticss的解釋recursiive caalls：高recurssive ccalls的的原因：hard paares：第第二次執(zhí)行同同一語(yǔ)句即可可使recuursivee callls降低?？梢酝ㄟ^(guò)兩次同同樣的查詢，驗(yàn)驗(yàn)證上述結(jié)論論。pl/sql functtion ccalls：SQL crreate

7、or reeplacee funcction test returrn nummber 2 ass 3 l_cnnt nummber; 4 beegin 5 seleect coount(*) intto l_ccnt frrom deept; 6 retuurn l_cnt; 7 ennd;/函數(shù)已創(chuàng)建。SQL seelect enamee,testt fromm emp;ENAME TEST- -SMITH 6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已選擇12行。Executi

8、ion Pllan- 0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 TABLEE ACCEESS (FFULL) OF EEMPStatisttics-284 reecursiive caalls 0 db bblock gets 144 conssistennt getts 6 physsical readss 136 redoo sizee 579 bytees sennt viaa SQL*Net tto cliient 503 bytees recceivedd via SQL*NNet frrom cllient 2 SQL*Net rrou

9、ndttrips to/frrom cllient 9 sortts (meemory) 0 sortts (diisk) 12 rowss proccesseddSQL /ENAME TEST- -SMITH 6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已選擇12行。Executiion Pllan- 0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 TABLEE ACCEESS (FFULL) OF EEMPStatisttics-12 re

10、ccursivve callls 0 db bblock gets 92 conssistennt getts 0 physsical readss 0 redoo sizee 579 bytees sennt viaa SQL*Net tto cliient 503 bytees recceivedd via SQL*NNet frrom cllient 2 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 12 rowss proccesseddSQL /ENAME TEST- -SMITH

11、6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已選擇12行。Executiion Pllan- 0 SEELECT STATEEMENT Optimmizer=CHOOSSE 1 0 TABLEE ACCEESS (FFULL) OF EEMPStatisttics-12 reccursivve callls 0 db bblock gets 92 conssistennt getts 0 physsical readss 0 redoo sizee 579 bytees sennt

12、viaa SQL*Net tto cliient 503 bytees recceivedd via SQL*NNet frrom cllient 2 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 12 rowss proccesseddside efffect from modifficatiion：由于于trigggers、基基于函數(shù)的索索引引起。space rrequesst：DMTT表空間中的的表要求空間間時(shí)，會(huì)引起起較多的reecursiive caalls，通通過(guò)使用LMMT，可以

13、顯顯著減少這種種recurrsive callss，而LMTT中的reccursivve callls主要是是由于驗(yàn)證qquota權(quán)權(quán)限引起?？煽梢酝ㄟ^(guò)實(shí)驗(yàn)驗(yàn)驗(yàn)證上面的的說(shuō)法(實(shí)驗(yàn)驗(yàn)步驟見(jiàn)Efffectiive Orracle by Deesign pp1011)。db blocck getts andd conssistennt gettsdb blocck gett是以currrent mode讀讀取的數(shù)據(jù)塊塊數(shù)，通常是是由于數(shù)據(jù)修修改而引起，cconsisstent gets是是以conssistennt modde讀取的數(shù)數(shù)據(jù)塊數(shù)，通通常由于seelect操操作引起。我我們關(guān)注的是是這

14、兩個(gè)數(shù)量量之和，即邏邏輯I/O的的數(shù)量，邏輯輯I/O也代代表了對(duì)緩存存加上lattch的數(shù)量量，邏輯I/O越少，越越好。Thee lesss logiical II/O wee can do,thhe bettter。我我們可以通過(guò)過(guò)設(shè)置合適的的arrayysize(許多方法中中的一個(gè)，適適用于sqll*pluss)來(lái)降低邏邏輯I/O數(shù)數(shù)量，ODBBC，JDBBC也有類似似的設(shè)置。Array ssize：SQL coonn syystem/oracllecattalog已連接。SQL grrant ddba too scottt;授權(quán)成功。SQL coonn sccott/ttigercata

15、llog已連接。SQL drrop taable tt;表已丟棄。SQL crreate tablee t 2 ass 3 seelect * froom alll_objeects;表已創(chuàng)建。SQL seelect countt(*) ffrom tt; COUNTT(*)-6219已選擇62199行。SQL seet auttotracce traaceonlly staatistiicsSQL seelect * froom t;已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock gets 491 conssistennt ge

16、tts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 5057 bytees recceivedd via SQL*NNet frrom cllient 416 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 6219 rowss proccesseddSQL shhow arrraysiizearraysiize 155SQL seet arrraysizze 2SQL seelect *fromm

17、 t;已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock gets 3156 conssistennt getts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 334702 bytees recceivedd via SQL*NNet frrom cllient 3111 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 6219 rowss

18、 proccesseddSQL seet arrraysizze 4SQL /已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock gets 1618 conssistennt getts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 117597 bytees recceivedd via SQL*NNet frrom cllient 1556 SQL*Net rroundttrips to/frrom cllient 0 sortts (

19、meemory) 0 sortts (diisk) 6219 rowss proccesseddSQL seet arrraysizze 8SQL /已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock gets 853 conssistennt getts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 9050 bytees recceivedd via SQL*NNet frrom cllient 779 SQL*Net rroundtt

20、rips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 6219 rowss proccesseddSQL seet arrraysizze 16SQL /已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock gets 465 conssistennt getts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 4771 bytees recceivedd via SQL*NNet f

21、rrom cllient 390 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 0 sortts (diisk) 6219 rowss proccesseddphisicaal reaads：指OOraclee把數(shù)據(jù)從硬硬盤讀到內(nèi)存存的次數(shù)，也也就是讀取到到內(nèi)存的數(shù)據(jù)據(jù)塊數(shù)，然后后我們執(zhí)行邏邏輯I/O從從內(nèi)存讀取數(shù)數(shù)據(jù)，因此，一一般情況下，物物理I/O都都跟隨著邏輯輯I/O。phisicaal reaads分為兩兩種：readingg dataa in ffrom ddatafiiles：對(duì)對(duì)數(shù)據(jù)文件讀讀取獲得索引引數(shù)據(jù)或者表

22、表數(shù)據(jù)，這種種I/O立刻刻跟隨著邏輯輯I/O。direct readss fromm tempp：當(dāng)sorrt areea或hassh areea不能在內(nèi)內(nèi)存中容納ssort ddata或hhash ddata時(shí)，OOraclee會(huì)把部分?jǐn)?shù)數(shù)據(jù)交換到ttemp表空空間，然后再再讀取，這種種讀取會(huì)越過(guò)過(guò)buffeer cacche，不會(huì)會(huì)引發(fā)邏輯II/O。第一種物理I/O，我們不不能避免，如如果在第一次次查詢后，同同樣的查詢還還需要物理II/O,，則則可能是因?yàn)闉閐ata buffeer cacche太小，在在物理內(nèi)存足足夠的情況下下，可以把ddata bbufferr cachhe增大。對(duì)于第

23、二種，我我們可以通過(guò)過(guò)設(shè)置合適的的sort_area_size和和hash_area_size大大小，來(lái)降低低phisiical rreads，注注意，在Orracle99i中，要先先把workkarea_size_policcy參數(shù)設(shè)置置為manuual，改動(dòng)動(dòng)sort_area_size及及hash_area_size參參數(shù)才會(huì)生效效，8i可以以直接設(shè)置ssort_aarea_ssize。SQL coonn sccott/ttigercatallog已連接。SQL shhow paarametter woorkNAME TYPE VALLUE- - -workareea_sizze_pol

24、licy strinng AUTTOSQL allter ssessioon sett workkarea_size_policcy=mannual;會(huì)話已更改。SQL allter ssessioon sett sortt_areaa_sizee=0;會(huì)話已更改。SQL seet auttotracce traaceonlly staatistiicsSQL seelect * froom t oorder by obbject_id;已選擇62199行。Statisttics- 0 recuursivee callls 24 db bblock gets 80 conssistennt g

25、etts 214 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 1 sortts (diisk) 6219 rowss proccesseddSQL /已選擇62199行。Statisttics- 0 recuursivee callls 22 db bblock gets 80 cons

26、sistennt getts 212 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 1 sortts (diisk) 6219 rowss proccesseddSQL allter ssessioon sett sortt_areaa_sizee=10244;會(huì)話已更改。SQL seel

27、ect * froom t oorder by obbject_id;已選擇62199行。Statisttics- 0 recuursivee callls 59 db bblock gets 80 conssistennt getts 435 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory)

28、 1 sortts (diisk) 6219 rowss proccesseddSQL /已選擇62199行。Statisttics- 0 recuursivee callls 59 db bblock gets 80 conssistennt getts 437 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts

29、 (meemory) 1 sortts (diisk) 6219 rowss proccesseddSQL allter ssessioon sett sortt_areaa_sizee=;會(huì)話已更改。SQL seelect * froom t oorder by obbject_id;已選擇62199行。Statisttics- 0 recuursivee callls 19 db bblock gets 80 conssistennt getts 198 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent

30、 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts (meemory) 1 sortts (diisk) 6219 rowss proccesseddSQL allter ssessioon sett sortt_areaa_sizee=;會(huì)話已更改。SQL seelect * froom t oorder by obbject_id;已選擇62199行。Statisttics- 0 recuursivee callls 0 db bblock g

31、ets 80 conssistennt getts 0 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 1 sortts (meemory) 0 sortts (diisk) 6219 rowss proccesseddSQL allter ssessioon sett sortt_areaa_sizee=0;會(huì)話已更改。SQL

32、 seelect * froom t oorder by obbject_id;已選擇62199行。Statisttics- 0 recuursivee callls 22 db bblock gets 80 conssistennt getts 212 physsical readss 0 redoo sizee bytess sentt via SQL*NNet too clieent 767 bytees recceivedd via SQL*NNet frrom cllient 26 SQL*Net rroundttrips to/frrom cllient 0 sortts (mee

33、mory) 1 sortts (diisk) 6219 rowss proccesseddredo siize主要在bulkk inseert操作(CTAS及及inserrt seleect)時(shí)需需要調(diào)整，一一般的merrge、innsert、delette、upddate語(yǔ)句句我們不能控控制其reddo大小。在在歸檔模式下下，要把表設(shè)設(shè)置為nollogginng，inssert數(shù)據(jù)據(jù)時(shí)，加上aappendd提示。alter ttable test_redo nologgging;insert /*+ aappendd */ iinto ttest_rredoseelect * froom a

34、lll_objeects;非歸檔模式，不不需要把表設(shè)設(shè)置為nollogginng，只要加加上appeend提示，也也會(huì)降低reedo siize的大小小。如果表建有索引引，則要把索索引停用，否否則依然有大大量的reddo：alter iindex idx_ttest uunusabble;alter ssessioon sett skipp_unussable_indexxes=trrue;alter iindex idx_ttest rrebuilld nollogginng;sql*nett stattisticcssorts aand roows prrocesssedSQL_TRAAC

35、E的使用用方法設(shè)置步驟：SQL coonn syystem/oraclleSQL allter ssystemm set sql_ttrace=true scopee=spfiile;SQL allter ssystemm set timedd_stattisticcs=truue;SQL coonn syys/oraacle aas syssdbaSQL sttartupp forcceSQL grrant ddba too scooott;SQL coonn sccott/ttigerSQL seelect * froom deppt;SQL seelect a.spiid froom v

36、$pprocesss a,vv$sesssion bb 2 whhere aa.addrr=b.paaddr 3 annd b.aaudsidd=userrenv(sessiionid) 4 /SPID-2756C:tkpprof ee:oraacleaadminoemreepuduumpoeemrep_ora_22756.ttrc c:repoort.txxt報(bào)告內(nèi)容：TKPROF: Releease 99.2.0.1.0 - Prodductioon on 星期二 22月 17 21:122:16 22004Copyrigght (cc) 19882, 20002, OOraclee Corpporatiion. All rrightss reseerved.Trace ffile: e:orracleadminnoemrrepuddumpooemrepp_ora_2756.trcSort opptionss: deffault*count = nnumberr of ttimes OCI pproceddure wwas exxecuteedcpu = ccpu tiime inn secoonds eexecutting elapsedd = eelapseed timme in seconnds e