Oracle性能分析和调整.docx

性能分析与调整Oracle的SQL执行计划Auto_trace设置步骤：SQL>connsystem/oracle已连接。SQL>start?\rdbms\admin\utlxplan表已创建。SQL>createpublicsynonymplan_tableforplan_table;同义词已创建。SQL>grantallonplan_tabletopublic;授权成功。SQL>connsys/oracleassysdba已连接。SQL>start?\sqlplus\admin\plustrceSQL>droproleplustrace;droproleplustraceSQL>createroleplustrace;角色已创建SQL>grantselectonv_$sesstattoplustrace;授权成功。SQL>grantselectonv_$statnametoplustrace;授权成功。SQL>grantselectonv_$sessiontoplustrace;授权成功。SQL>grantplustracetodbawithadminoption;授权成功。SQL>setechooffSQL>grantplustracetopublic;授权成功。SQL>connscott/tiger已连接。SQL>setautotraceonSQL>selectename,salfromemp;ENAMESAL--------------------SMITH800ALLEN1600WARD1250JONES2975MARTIN1250BLAKE2850CLARK2450KING5000TURNER1500JAMES950FORD3000MILLER1300已选择12行。ExecutionPlan----------------------------------------------------------0SELECTSTATEMENTOptimizer=CHOOSE10TABLEACCESS(FULL)OF'EMP'Statistics----------------------------------------------------------12recursivecalls0dbblockgets92consistentgets0physicalreads0redosize588bytessentviaSQL*Nettoclient503bytesreceivedviaSQL*Netfromclient2SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)12rowsprocessed设置autotrace的一些选项：setautotraceoff：执行计划和统计信息都不显示，这是缺省的设置。setautortraceonexplain：只显示执行计划。setautotraceonstatistics：只显示统计信息。setautotraceon：执行计划和统计信息都显示。setautotracetraceonly：类似于setautotraceon，只是不显示查询结果。setautotracetraceonlyexplain：只显示执行计划。setautotracetraceonlystatistics：只显示统计结果。Howtoreadaqueryplan关于statistics的解释recursivecalls：高r