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

性能分析与调整1．Oracle的SQL执行计划2．Auto_trace1)设置步骤：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)12rowsprocessed2)设置autotrace的一些选项：setautotraceoff：执行计划和统计信息都不显示，这是缺省的设置。setautortraceonexplain：只显示执行计划。setautotraceonstatistics：只显示统计信息。setautotraceon：执行计划和统计信息都显示。setautotracetraceonly：类似于setautotraceon，只是不显示查询结果。setautotracetraceonlyexplain：只显示执行计划。setautotracetraceonlystatistics：只显示统计结果。3)Howtoreadaqueryplan4)关于statistics的解释recursivecalls：高recursivecalls的原因：hardpares：第二次执行同一语句即可使recursivecalls降低。可以通过两次同样的查询，验证上述结论。pl/sqlfunctioncalls：SQL>createorreplacefunctiontestreturnnumber2as3l_cntnumber;4begin5selectcount(*)intol_cntfromdept;6returnl_cnt;7end;8/函数已创建。SQL>selectename,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--------------------------------------------------------12recursivec...