Oracle培训讲义(性能分析与调整)

性能分析与调整1．Oracle的SQL执行计划2．Auto_trace1)设置步骤：SQLconnsystem/oracle已连接。SQLstart?\rdbms\admin\utlxplan表已创建。SQLcreatepublicsynonymplan_tableforplan_table;同义词已创建。SQLgrantallonplan_tabletopublic;授权成功。SQLconnsys/oracleassysdba已连接。SQLstart?\sqlplus\admin\plustrceSQLdroproleplustrace;droproleplustraceSQLcreateroleplustrace;角色已创建SQLgrantselectonv_$sesstattoplustrace;授权成功。SQLgrantselectonv_$statnametoplustrace;授权成功。SQLgrantselectonv_$sessiontoplustrace;授权成功。SQLgrantplustracetodbawithadminoption;授权成功。SQLsetechooffSQLgrantplustracetopublic;授权成功。SQLconnscott/tiger已连接。SQLsetautotraceonSQLselectename,salfromemp;ENAMESAL--------------------SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450KING5000TURNER1500JAMES950FORD3000MILLER1300已选择12行。ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'EMP'Statistics----------------------------------------------------------12recursivecalls0dbblockgets92consistentgets0physicalreads0redosize588bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)12rowsprocessed2)设置autotrace的一些选项：setautotraceoff：执行计划和统计信息都不显示，这是缺省的设置。setautortraceonexplain：只显示执行计划。setautotraceonstatistics：只显示统计信息。setautotraceon：执行计划和统计信息都显示。setautotracetraceonly：类似于setautotraceon，只是不显示查询结果。setautotracetraceonlyexplain：只显示执行计划。setautotracetraceonlystatistics：只显示统计结果。3)Howtoreadaqueryplan4)关于statistics的解释recursivecalls：高recursivecalls的原因：hardpares：第二次执行同一语句即可使recursivecalls降低。可以通过两次同样的查询，验证上述结论。pl/sqlfunctioncalls：SQLcreateorreplacefunctiontestreturnnumber2as3l_cntnumber;4begin5selectcount(*)intol_cntfromdept;6returnl_cnt;7end;8/函数已创建。SQLselectename,testfromemp;ENAMETEST--------------------SMITH6ALLEN6WARD6JONES6MARTIN6BLAKE6CLARK6KING6TURNER6JAMES6FORD6MILLER6已选择12行。ExecutionPlan--------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'EMP'Statistics--------------------------------------------------------284recursivecalls0dbblockgets144consistentgets6physicalreads136redosize579bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient9sorts(memory)0sorts(disk)12rowsprocessedSQL/ENAMETEST--------------------SMITH6ALLEN6WARD6JONES6MARTIN6BLAKE6CLARK6KING6TURNER6JAMES6FORD6MILLER6已选择12行。ExecutionPlan--------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'EMP'Statistics--------------------------------------------------------12recursivecalls0dbblockgets92consistentgets0physicalreads0redosize579bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)12rowsprocessedSQL/ENAMETEST--------------------SMITH6ALLEN6WARD6JONES6MARTIN6BLAKE6CLARK6KING6TURNER6JAMES6FORD6MILLER6已选择12行。ExecutionPlan--------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'EMP'Statistics--------------------------------------------------------12recursivecalls0dbblockgets92consistentgets0physicalreads0redosize579bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)12rowsprocessedsideeffectfrommodification：由于triggers、基于函数的索引引起。spacerequest：DMT表空间中的表要求空间时，会引起较多的recursivecalls，通过使用LMT，可以显著减少这种recursivecalls，而LMT中的recursivecalls主要是由于验证quota权限引起。可以通过实验验证上面的说法(实验步骤见EffectiveOraclebyDesignpp101)。dbblockgetsandconsistentgetsdbblockget是以currentmode读取的数据块数，通常是由于数据修改而引起，consistentgets是以consistentmode读取的数据块数，通常由于select操作引起。我们关注的是这两个数量之和，即逻辑I/O的数量，逻辑I/O也代表了对缓存加上latch的数量，逻辑I/O越少，越好。ThelesslogicalI/Owecando,thebetter。我们可以通过设置合适的arraysize(许多方法中的一个，适用于sql*plus)来降低逻辑I/O数量，ODBC，JDBC也有类似的设置。Arraysize：SQLconnsystem/oracle@catalog已连接。SQLgrantdbatoscott;授权成功。SQLconnscott/tiger@catalog已连接。SQLdroptablet;表已丢弃。SQLcreatetablet2as3select*fromall_objects;表已创建。SQLselectcount(*)fromt;COUNT(*)----------6219已选择6219行。SQLsetautotracetraceonlystatisticsSQLselect*fromt;已选择6219行。Statistics------------------------------------------------------0recursivecalls0dbblockgets491consistentgets0physicalreads0redosize357171bytessentviaSQL*Nettoclient5057bytesreceivedviaSQL*Netfromclient416SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)6219rowsprocessedSQLshowarraysizearraysize15SQLsetarraysize2SQLselect*fromt;已选择6219行。Statistics----------------------------------------------------0recursivecalls0dbblockgets3156consistentgets0physicalreads0redosize683239bytessentviaSQL*Nettoclient34702bytesreceivedviaSQL*Netfromclient3111SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)6219rowsprocessedSQLsetarraysize4SQL/已选择6219行。Statistics----------------------------------------------------0recursivecalls0dbblockgets1618consistentgets0physicalreads0redosize495111bytessentviaSQL*Nettoclient17597bytesreceivedviaSQL*Netfromclient1556SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)6219rowsprocessedSQL