《Oracle性能分析和调整》由会员分享，可在线阅读，更多相关《Oracle性能分析和调整（15页珍藏版）》请在金锄头文库上搜索。
1、性能分析与调整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 r
2、ole 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 con
3、n 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
4、 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：执行计划和统计信息都不显示，这是
5、缺省的设置。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 c
6、alls：高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
7、- -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*Ne
8、t 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
9、 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
