性能分析与调整1 Oracle的SQL执行计划2 Auto_trace1) 设置步骤： SQL conn system/oracle已连接。SQL start ?rdbmsadminutlxplan表已创建。SQL create public synonym plan_table for plan_table;同义词已创建。SQL grant all on plan_table to public;授权成功。SQL conn sys/oracle as sysdba已连接。SQL start ?sqlplusadminplustrceSQL drop role plustrace;drop role plustraceSQL create role plustrace;角色已创建SQL grant select on v_$sesstat to plustrace;授权成功。SQL grant select on v_$statname to plustrace;授权成功。SQL grant select on v_$session to plustrace;授权成功。SQL grant plustrace to dba with admin option;授权成功。SQL set echo offSQL grant plustrace to public;授权成功。SQL conn scott/tiger已连接。SQL set autotrace onSQL select ename,sal from emp;ENAME SAL- -SMITH 800ALLEN 1600WARD 1250JONES 2975MARTIN 1250BLAKE 2850CLARK 2450KING 5000TURNER 1500JAMES 950FORD 3000MILLER 1300已选择12行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF EMPStatistics- 12 recursive calls 0 db block gets 92 consistent gets 0 physical reads 0 redo size 588 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processed2) 设置autotrace的一些选项：set autotrace off：执行计划和统计信息都不显示，这是缺省的设置。set autortrace on explain：只显示执行计划。set autotrace on statistics：只显示统计信息。set autotrace on：执行计划和统计信息都显示。set autotrace traceonly：类似于set autotrace on，只是不显示查询结果。set autotrace traceonly explain：只显示执行计划。set autotrace traceonly statistics：只显示统计结果。3) How to read a query plan4) 关于statistics的解释 recursive calls：高recursive calls的原因： hard pares：第二次执行同一语句即可使recursive calls降低。可以通过两次同样的查询，验证上述结论。 pl/sql function calls：SQL create or replace function test return number 2 as 3 l_cnt number; 4 begin 5 select count(*) into l_cnt from dept; 6 return l_cnt; 7 end;8 /函数已创建。SQL select ename,test from emp;ENAME TEST- -SMITH 6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已选择12行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF EMPStatistics- 284 recursive calls 0 db block gets 144 consistent gets 6 physical reads 136 redo size 579 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 9 sorts (memory) 0 sorts (disk) 12 rows processedSQL /ENAME TEST- -SMITH 6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已选择12行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF EMPStatistics- 12 recursive calls 0 db block gets 92 consistent gets 0 physical reads 0 redo size 579 bytes sent via SQL*Net to client 503 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processedSQL /ENAME TEST- -SMITH 6ALLEN 6WARD 6JONES 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已选择12行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF EMPStatistics- 12 recursive calls 0 db block gets 92 consistent ge