Oracle培訓(xùn)講義(性能分析與調(diào)整)

1、性能分析與調(diào)整1 Oracle的SQL執(zhí)行計劃2 Auto_trace1) 設(shè)置步驟： SQL> conn system/oracle已連接。SQL> start ?rdbmsadminutlxplan表已創(chuàng)建。SQL> create public synonym plan_table for plan_table;同義詞已創(chuàng)建。SQL> grant all on plan_table to public;授權(quán)成功。SQL> conn sys/oracle as sysdba已連接。SQL> start ?sqlplusadminplustrceSQL>

2、; drop role plustrace;drop role plustraceSQL> create role plustrace;角色已創(chuàng)建SQL> grant select on v_$sesstat to plustrace;授權(quán)成功。SQL> grant select on v_$statname to plustrace;授權(quán)成功。SQL> grant select on v_$session to plustrace;授權(quán)成功。SQL> grant plustrace to dba with admin option;授權(quán)成功。SQL> se

3、t echo offSQL> grant plustrace to public;授權(quán)成功。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) 設(shè)置autotrace的一些選項：set autotrace off：執(zhí)行計劃和統(tǒng)計信息都不顯示，這是缺省的設(shè)置。set autortrace on explain：只顯示執(zhí)行計劃。set autotrace on statistics：只顯示統(tǒng)計信息。set autotrace on：執(zhí)行計劃和統(tǒng)計信息都顯示。set autotrace traceonly：類似于set autotrace on，只是不顯示查詢結(jié)果。set autotrace traceonly explain：只顯示執(zhí)行計劃。set autotrace traceonl

6、y statistics：只顯示統(tǒng)計結(jié)果。3) How to read a query plan4) 關(guān)于statistics的解釋ü recursive calls：高recursive calls的原因：Ø hard pares：第二次執(zhí)行同一語句即可使recursive calls降低?？梢酝ㄟ^兩次同樣的查詢，驗證上述結(jié)論。Ø 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 /函數(shù)已創(chuàng)建。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、基于函數(shù)的索引引起。Ø space request：DMT表空間中的表要求空間時，會引起較多的recursive calls，通過使用LMT，可以顯著減少這種recursive calls，而LMT中的recursive calls主要是由于驗證quota權(quán)限引起?？梢酝ㄟ^實驗驗證上面的說法(實驗步驟見Effective Oracle by Design pp101)。ü db block gets and consistent getsdb block get是以current mode讀取的數(shù)據(jù)塊數(shù)，通常是

13、由于數(shù)據(jù)修改而引起，consistent gets是以consistent mode讀取的數(shù)據(jù)塊數(shù)，通常由于select操作引起。我們關(guān)注的是這兩個數(shù)量之和，即邏輯I/O的數(shù)量，邏輯I/O也代表了對緩存加上latch的數(shù)量，邏輯I/O越少，越好。The less logical I/O we can do,the better。我們可以通過設(shè)置合適的arraysize(許多方法中的一個，適用于sql*plus)來降低邏輯I/O數(shù)量，ODBC，JDBC也有類似的設(shè)置。Array size：SQL> conn system/oraclecatalog已連接。SQL> grant dba

14、 to scott;授權(quán)成功。SQL> conn scott/tigercatalog已連接。SQL> drop table t;表已丟棄。SQL> create table t 2 as 3 select * from all_objects;表已創(chuàng)建。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把數(shù)據(jù)從硬盤讀到內(nèi)存的次數(shù)，也就是讀取到內(nèi)存的數(shù)據(jù)塊數(shù)，然后我們執(zhí)行邏輯I/O從內(nèi)存讀取數(shù)據(jù)，因此，一般情況下，物理I/O都跟隨著邏輯I/O。phisical reads分為兩種：reading data in from datafiles：對數(shù)據(jù)文件讀取獲得索引數(shù)據(jù)或者表數(shù)據(jù)，這種I/O立刻跟隨著邏輯I/O。direct reads from temp：當(dāng)sort area或hash area不能在內(nèi)存中容納sort data或hash data時，Oracle會把部分?jǐn)?shù)據(jù)交換到tem

21、p表空間，然后再讀取，這種讀取會越過buffer cache，不會引發(fā)邏輯I/O。第一種物理I/O，我們不能避免，如果在第一次查詢后，同樣的查詢還需要物理I/O,，則可能是因為data buffer cache太小，在物理內(nèi)存足夠的情況下，可以把data buffer cache增大。對于第二種，我們可以通過設(shè)置合適的sort_area_size和hash_area_size大小，來降低phisical reads，注意，在Oracle9i中，要先把workarea_size_policy參數(shù)設(shè)置為manual，改動sort_area_size及hash_area_size參數(shù)才會生效，8i可

22、以直接設(shè)置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)時需要調(diào)整，一般的merge、insert、delete、update語句我們不能控制其redo大小。在歸檔模式下，要把表設(shè)置為nologging，insert數(shù)據(jù)時，加上append提示。alter table test_redo nologging;insert /*+ append */ into test_redo select * from all_objects;非歸檔模式，不需要把表設(shè)置為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) 設(shè)置步驟：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) 報告內(nèi)容：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