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

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>

2、; 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> se

3、t 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 STA

4、TEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP'Statistics- 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

5、(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 traceonl

6、y 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 cou

7、nt(*) 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 'EMP'Statistics

8、- 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 6JONE

9、S 6MARTIN 6BLAKE 6CLARK 6KING 6TURNER 6JAMES 6FORD 6MILLER 6已选择12行。Execution Plan- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'EMP'Statistics- 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

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

11、 (FULL) OF 'EMP'Statistics- 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 processedØ side effe

12、ct from modification：由于triggers、基于函数的索引引起。Ø space request：DMT表空间中的表要求空间时，会引起较多的recursive calls，通过使用LMT，可以显著减少这种recursive calls，而LMT中的recursive calls主要是由于验证quota权限引起。可以通过实验验证上面的说法(实验步骤见Effective Oracle by Design pp101)。ü db block gets and consistent getsdb block get是以current mode读取的数据块数，通常是

13、由于数据修改而引起，consistent gets是以consistent mode读取的数据块数，通常由于select操作引起。我们关注的是这两个数量之和，即逻辑I/O的数量，逻辑I/O也代表了对缓存加上latch的数量，逻辑I/O越少，越好。The less logical I/O we can do,the better。我们可以通过设置合适的arraysize(许多方法中的一个，适用于sql*plus)来降低逻辑I/O数量，ODBC，JDBC也有类似的设置。Array size：SQL> conn system/oraclecatalog已连接。SQL> grant dba

14、 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 traceonly statisticsSQL> select * from t;已选择6219行。Statistics- 0 recursive calls 0 db

15、 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) 6219 rows processedSQL> show arraysizearraysize 15SQL> set arraysize 2SQL> s

16、elect *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 to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows processedSQL>

17、; 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 roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6219 rows pro

18、cessedSQL> 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 roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 6219

19、 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 roundtrips to/from client 0 sorts (memory) 0 sorts (

20、disk) 6219 rows processedü phisical 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不能在内存中容纳sort data或hash data时，Oracle会把部分数据交换到tem

21、p表空间，然后再读取，这种读取会越过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参数设置为manual，改动sort_area_size及hash_area_size参数才会生效，8i可

22、以直接设置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 statisticsSQL> select

23、* 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 (disk) 6219 rows proc

24、essedSQL> /已选择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 processedSQL> a

25、lter 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 roundtrips to/

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

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

28、s received 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 ph

29、ysical 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 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行。Sta

30、tistics- 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 processedü redo size主要在bulk insert操作(CT

31、AS及insert select)时需要调整，一般的merge、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

32、 idx_test unusable;alter session set skip_unusable_indexes=true;alter index idx_test rebuild nologging;ü sql*net statisticsü sorts and rows processed3 SQL_TRACE的使用方法1) 设置步骤：SQL> conn system/oracleSQL> alter system set sql_trace=true scope=spfile;SQL> alter system set timed_statist

33、ics=true;SQL> conn sys/oracle as sysdbaSQL> startup forceSQL> 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:oracleadminoe

34、mrepudumpoemrep_ora_2756.trc c:report.txt2) 报告内容：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