Oracle性能分析和调整

1、性能分析与调整Oracle的的SQL执行行计划Auto_trrace设置步骤：SQL coonn syystem/oraclle已连接。SQL sttart ?rdbmmsadmminuttlxplaan表已创建。SQL crreate publiic synnonym plan_tablee for plan_tablee;同义词已创建。SQL grrant aall onn plann_tablle to publiic;授权成功。SQL coonn syys/oraacle aas syssdba已连接。SQL sttart ?sqlpplusaadminplusttrceSQL drr

2、op roole pllustraace;drop roole pllustraaceSQL crreate role plusttrace;角色已创建SQL grrant sselectt on vv_$sessstat to pllustraace;授权成功。SQL grrant sselectt on vv_$staatnamee to pplustrrace;授权成功。SQL grrant sselectt on vv_$sesssion to pllustraace;授权成功。SQL grrant pplustrrace tto dbaa withh admiin opttion;授

3、权成功。SQL seet echho offfSQL grrant pplustrrace tto pubblic;授权成功。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设置autottrace的的一些选项：set auttotracce offf：执行计划划和统计信息息都不显示，这这是缺省的设设置。set auttortraace onn expllain：只只显示执行计计划。set auttotracce on statiisticss：只显示统统计信息。set auttotracce on：执行计划和和统计信息都都显示。set auttotracce traaceonlly：类似于于set aautotrrace oon

6、，只是不不显示查询结结果。set auttotracce traaceonlly expplain：只显示执行行计划。set auttotracce traaceonlly staatistiics：只显显示统计结果果。How to read a queery pllan关于statiisticss的解释recursiive caalls：高recurssive ccalls的的原因：hard paares：第第二次执行同同一语句即可可使recuursivee callls降低。可以通过两次同同样的查询，验验证上述结论论。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;/函数已创建。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、基基于函数的索索引引起。space rrequesst：DMTT表空间中的的表要求空间间时，会引起起较多的reecursiive caalls，通通过使用LMMT，可以

13、显显著减少这种种recurrsive callss，而LMTT中的reccursivve callls主要是是由于验证qquota权权限引起。可可以通过实验验验证上面的的说法(实验验步骤见Efffectiive Orracle by Deesign pp1011)。db blocck getts andd conssistennt gettsdb blocck gett是以currrent mode读读取的数据块块数，通常是是由于数据修修改而引起，cconsisstent gets是是以conssistennt modde读取的数数据块数，通通常由于seelect操操作引起。我我们关注的是是这

14、两个数量量之和，即逻逻辑I/O的的数量，逻辑辑I/O也代代表了对缓存存加上lattch的数量量，逻辑I/O越少，越越好。Thee lesss logiical II/O wee can do,thhe bettter。我我们可以通过过设置合适的的arrayysize(许多方法中中的一个，适适用于sqll*pluss)来降低逻逻辑I/O数数量，ODBBC，JDBBC也有类似似的设置。Array ssize：SQL coonn syystem/oracllecattalog已连接。SQL grrant ddba too scottt;授权成功。SQL coonn sccott/ttigercata

15、llog已连接。SQL drrop taable tt;表已丢弃。SQL crreate tablee t 2 ass 3 seelect * froom alll_objeects;表已创建。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把数据从硬硬盘读到内存存的次数，也也就是读取到到内存的数据据块数，然后后我们执行逻逻辑I/O从从内存读取数数据，因此，一一般情况下，物物理I/O都都跟随着逻辑辑I/O。phisicaal reaads分为两两种：readingg dataa in ffrom ddatafiiles：对对数据文件读读取获得索引引数据或者表

22、表数据，这种种I/O立刻刻跟随着逻辑辑I/O。direct readss fromm tempp：当sorrt areea或hassh areea不能在内内存中容纳ssort ddata或hhash ddata时，OOraclee会把部分数数据交换到ttemp表空空间，然后再再读取，这种种读取会越过过buffeer cacche，不会会引发逻辑II/O。第一种物理I/O，我们不不能避免，如如果在第一次次查询后，同同样的查询还还需要物理II/O,，则则可能是因为为data buffeer cacche太小，在在物理内存足足够的情况下下，可以把ddata bbufferr cachhe增大。对于第

23、二种，我我们可以通过过设置合适的的sort_area_size和和hash_area_size大大小，来降低低phisiical rreads，注注意，在Orracle99i中，要先先把workkarea_size_policcy参数设置置为manuual，改动动sort_area_size及及hash_area_size参参数才会生效效，8i可以以直接设置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;会话已更改。SQL allter ssessioon sett sortt_areaa_sizee=0;会话已更改。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;会话已更改。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=;会话已更改。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=;会话已更改。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;会话已更改。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)时需需要调整，一一般的merrge、innsert、delette、upddate语句句我们不能控控制其reddo大小。在在归档模式下下，要把表设设置为nollogginng，inssert数据据时，加上aappendd提示。alter ttable test_redo nologgging;insert /*+ aappendd */ iinto ttest_rredoseelect * froom a

34、lll_objeects;非归档模式，不不需要把表设设置为nollogginng，只要加加上appeend提示，也也会降低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的使用用方法设置步骤：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报告内容：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