Oracle性能分析和调整

1、性能分析与调整Oracle的SQL执行打算Auto_trace设置步骤： 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 plu

2、straceSQL 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

3、/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 recurs

4、ive 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 processed设置autotrace的一些选项：set autotrace off：执行打算和统计信息都不显示，这是缺省的设置。set

5、autortrace on explain：只显示执行打算。set autotrace on statistics：只显示统计信息。set autotrace on：执行打算和统计信息都显示。set autotrace traceonly：类似于set autotrace on，只是不显示查询结果。set autotrace traceonly explain：只显示执行打算。set autotrace traceonly statistics：只显示统计结果。How to read a query plan关于statistics的解释recursive calls：高recursive c

6、alls的缘故：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;/函数已创建。SQL select ename,test from emp;ENAME TEST- -SMITH 6ALLEN 6WARD

7、 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

8、 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 (FU

9、LL) 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

10、 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 byt

11、es received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 12 rows processedside effect from modification：由于triggers、基于函数的索引引起。space request：DMT表空间中的表要求空间时，会引起较多的recursive calls，通过使用LMT，能够显著减少这种recursive calls，而LMT中的recursive calls要紧是由于验证quota权限引起。能够通过实验验

12、证上面的讲法(实验步骤见Effective Oracle by Design pp101)。db block gets and consistent getsdb block get是以current mode读取的数据块数，通常是由于数据修改而引起，consistent gets是以consistent mode读取的数据块数，通常由于select操作引起。我们关注的是这两个数量之和，即逻辑I/O的数量，逻辑I/O也代表了对缓存加上latch的数量，逻辑I/O越少，越好。The less logical I/O we can do,the better。我们能够通过设置合适的arraysiz

13、e(许多方法中的一个，适用于sql*plus)来降低逻辑I/O数量，ODBC，JDBC也有类似的设置。Array size：SQL conn system/oraclecatalog已连接。SQL grant dba to scott;授权成功。SQL conn scott/tigercatalog已连接。SQL drop table t;表已丢弃。SQL create table t 2 as 3 select * from all_objects;表已创建。SQL select count(*) from t; COUNT(*)-6219已选择6219行。SQL set autotrace

14、 traceonly statisticsSQL select * from t;已选择6219行。Statistics- 0 recursive calls 0 db block gets 491 consistent gets 0 physical reads 0 redo size 357171 bytes sent via SQL*Net to client 5057 bytes received via SQL*Net from client 416 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6

15、219 rows processedSQL show arraysizearraysize 15SQL set arraysize 2SQL select *from t;已选择6219行。Statistics- 0 recursive calls 0 db block gets 3156 consistent gets 0 physical reads 0 redo size 683239 bytes sent via SQL*Net to client 34702 bytes received via SQL*Net from client 3111 SQL*Net roundtrips

16、to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL set arraysize 4SQL /已选择6219行。Statistics- 0 recursive calls 0 db block gets 1618 consistent gets 0 physical reads 0 redo size 495111 bytes sent via SQL*Net to client 17597 bytes received via SQL*Net from client 1556 SQL*Net roundtr

17、ips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL set arraysize 8SQL /已选择6219行。Statistics- 0 recursive calls 0 db block gets 853 consistent gets 0 physical reads 0 redo size 401094 bytes sent via SQL*Net to client 9050 bytes received via SQL*Net from client 779 SQL*Net roundt

18、rips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL set arraysize 16SQL /已选择6219行。Statistics- 0 recursive calls 0 db block gets 465 consistent gets 0 physical reads 0 redo size 354025 bytes sent via SQL*Net to client 4771 bytes received via SQL*Net from client 390 SQL*Net roun

19、dtrips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedphisical reads：指Oracle把数据从硬盘读到内存的次数，也确实是读取到内存的数据块数，然后我们执行逻辑I/O从内存读取数据，因此，一般情况下，物理I/O都跟随着逻辑I/O。phisical reads分为两种：reading data in from datafiles：对数据文件读取获得索引数据或者表数据，这种I/O赶忙跟随着逻辑I/O。direct reads from temp：当sort area或hash area不能在内存

20、中容纳sort data或hash data时，Oracle会把部分数据交换到temp表空间，然后再读取，这种读取会越过buffer cache，可不能引发逻辑I/O。第一种物理I/O，我们不能幸免，假如在第一次查询后，同样的查询还需要物理I/O,，则可能是因为data buffer cache太小，在物理内存足够的情况下，能够把data buffer cache增大。关于第二种，我们能够通过设置合适的sort_area_size和hash_area_size大小，来降低phisical reads，注意，在Oracle9i中，要先把workarea_size_policy参数设置为manua

21、l，改动sort_area_size及hash_area_size参数才会生效，8i能够直接设置sort_area_size。SQL conn scott/tigercatalog已连接。SQL show parameter workNAME TYPE VALUE- - -workarea_size_policy string AUTOSQL alter session set workarea_size_policy=manual;会话已更改。SQL alter session set sort_area_size=0;会话已更改。SQL set autotrace traceonly st

22、atisticsSQL select * from t order by object_id;已选择6219行。Statistics- 0 recursive calls 24 db block gets 80 consistent gets 214 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (d

23、isk) 6219 rows processedSQL /已选择6219行。Statistics- 0 recursive calls 22 db block gets 80 consistent gets 212 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 6219 rows pro

24、cessedSQL alter session set sort_area_size=1024;会话已更改。SQL select * from t order by object_id;已选择6219行。Statistics- 0 recursive calls 59 db block gets 80 consistent gets 435 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtr

25、ips to/from client 0 sorts (memory) 1 sorts (disk) 6219 rows processedSQL /已选择6219行。Statistics- 0 recursive calls 59 db block gets 80 consistent gets 437 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client

26、 0 sorts (memory) 1 sorts (disk) 6219 rows processedSQL alter session set sort_area_size=102400;会话已更改。SQL select * from t order by object_id;已选择6219行。Statistics- 0 recursive calls 19 db block gets 80 consistent gets 198 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes rec

27、eived via SQL*Net from client 26 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 6219 rows processedSQL alter session set sort_area_size=10240000;会话已更改。SQL select * from t order by object_id;已选择6219行。Statistics- 0 recursive calls 0 db block gets 80 consistent gets 0 physical reads

28、0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 6219 rows processedSQL alter session set sort_area_size=0;会话已更改。SQL select * from t order by object_id;已选择6219行。Statistics- 0 recursive

29、calls 22 db block gets 80 consistent gets 212 physical reads 0 redo size 219216 bytes sent via SQL*Net to client 767 bytes received via SQL*Net from client 26 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 6219 rows processedredo size要紧在bulk insert操作(CTAS及insert select)时需要调整，一般的me

30、rge、insert、delete、update语句我们不能操纵其redo大小。在归档模式下，要把表设置为nologging，insert数据时，加上append提示。alter table test_redo nologging;insert /*+ append */ into test_redo select * from all_objects;非归档模式，不需要把表设置为nologging，只要加上append提示，也会降低redo size的大小。假如表建有索引，则要把索引停用，否则依旧有大量的redo：alter index idx_test unusable;alter ses

31、sion set skip_unusable_indexes=true;alter index idx_test rebuild nologging;sql*net statisticssorts and rows processedSQL_TRACE的使用方法设置步骤：SQL conn system/oracleSQL alter system set sql_trace=true scope=spfile;SQL alter system set timed_statistics=true;SQL conn sys/oracle as sysdbaSQL startup forceSQL

32、grant dba to scoott;SQL conn scott/tigerSQL select * from dept;SQL select a.spid from v$process a,v$session b 2 where a.addr=b.paddr 3 and b.audsid=userenv(sessionid) 4 /SPID-2756C:tkprof e:oracleadminoemrepudumpoemrep_ora_2756.trc c:report.txt报告内容：TKPROF: Release 9.2.0.1.0 - Production on 星期二 2月 17 21:12:16 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Trace file: e:oracleadminoemrepudumpoemrep_ora_2756.trcSort options: default*count = number of times OCI procedure was executedcpu = cpu time in seconds executing elapsed = ela