Oracle性能分析和調(diào)整

1、性能分析與調(diào)整Oracle的SQL執(zhí)行打算Auto_trace設(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 drop role plustrace;drop role plu

2、straceSQL 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 set echo offSQL grant plustrace to public;授權(quán)成功。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設(shè)置autotrace的一些選項：set autotrace off：執(zhí)行打算和統(tǒng)計信息都不顯示，這是缺省的設(shè)置。set

5、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 traceonly statistics：只顯示統(tǒng)計結(jié)果。How to read a query plan關(guān)于statistics的解釋recursive calls：高recursive c

6、alls的緣故：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 count(*) into l_cnt from dept; 6 return l_cnt; 7 end;/函數(shù)已創(chuàng)建。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、基于函數(shù)的索引引起。space request：DMT表空間中的表要求空間時，會引起較多的recursive calls，通過使用LMT，能夠顯著減少這種recursive calls，而LMT中的recursive calls要緊是由于驗證quota權(quán)限引起。能夠通過實驗驗

12、證上面的講法(實驗步驟見Effective Oracle by Design pp101)。db block gets and consistent getsdb block get是以current mode讀取的數(shù)據(jù)塊數(shù)，通常是由于數(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è)置合適的arraysiz

13、e(許多方法中的一個，適用于sql*plus)來降低邏輯I/O數(shù)量，ODBC，JDBC也有類似的設(shè)置。Array size：SQL conn system/oraclecatalog已連接。SQL grant dba 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

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把數(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：當sort area或hash area不能在內(nèi)存

20、中容納sort data或hash data時，Oracle會把部分數(shù)據(jù)交換到temp表空間，然后再讀取，這種讀取會越過buffer cache，可不能引發(fā)邏輯I/O。第一種物理I/O，我們不能幸免，假如在第一次查詢后，同樣的查詢還需要物理I/O,，則可能是因為data buffer cache太小，在物理內(nèi)存足夠的情況下，能夠把data buffer cache增大。關(guān)于第二種，我們能夠通過設(shè)置合適的sort_area_size和hash_area_size大小，來降低phisical reads，注意，在Oracle9i中，要先把workarea_size_policy參數(shù)設(shè)置為manua

21、l，改動sort_area_size及hash_area_size參數(shù)才會生效，8i能夠直接設(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 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)時需要調(diào)整，一般的me

30、rge、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 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的使用方法設(shè)置步驟：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報告內(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 seconds executing elapsed = ela