oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第22講-索引和訪問路徑

1、Oracle數(shù)據(jù)庫性能優(yōu)化實(shí)務(wù)第14講：索引與訪問路徑主講人：白鱔華章培訓(xùn)網(wǎng)、 華章培訓(xùn)網(wǎng) 訪問路徑和SQL性能訪問路徑?jīng)Q定了執(zhí)行計(jì)劃的性能調(diào)整訪問路徑是SQL優(yōu)化中最為重要的工作在一般OLTP系統(tǒng)中，超過60%的問題是通過調(diào)整訪問路徑解決的調(diào)整訪問路徑的優(yōu)化成本最低調(diào)整訪問路徑的方法調(diào)整索引調(diào)整表連接順序和連接方式選擇訪問路徑（通過HINT,OUTLINES,表分析等方式）什么是索引如何在圖書館中找到你要的書索引對(duì)單表訪問的影響根枝頁索引單表訪問的幾種主要方式索引唯一性訪問索引范圍掃描全索引掃描快速全索引掃描索引跳躍掃描索引訪問案例分析(1)SQL select object_id,obje

2、ct_name from ts2 where object_id=5295;-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 79 | 2 (0)| 00:00:01 | 1 | TABLE ACCESS BY INDEX ROWID| TS2 | 1 | 79 | 2 (0)| 00:00:01 |* 2 | INDEX UNIQUE SCAN | IDX_TS2 | 1 | | 1 (0)| 00:00:01 |-Statistics- 0 recursi

3、ve calls 0 db block gets 3 consistent gets 3 physical reads 0 redo size 486 bytes sent via SQL*Net to client 400 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed索引訪問案例分析（2）通過10046事件分析SQL訪問的數(shù)據(jù)塊SQL ALTER SESSION SET EVENTS 1004

4、6 TRACE NANE CONTEXT FOREVER,LEVEL 12;SQLSELECT OBJECT_ID,OBJECT_NAME FROM TS2 WHERE OBJECT_ID=5295SQLALTER SESSION SET EVENTS 10046 TRACE NAME CONTEXT OFF;索引訪問案例分析（3）select object_id,object_name from ts2 where object_id=5295END OF STMTPARSE #2:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1270717

5、957248561BINDS #2:EXEC #2:c=0,e=58,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1270717957248679WAIT #2: nam=SQL*Net message to client ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=10209 tim=1270717957248711WAIT #2: nam=db file sequential read ela= 21 file#=4 block#=124484 blocks=1 obj#=135967 tim=127071

6、7957248897WAIT #2: nam=db file sequential read ela= 18 file#=10 block#=50359 blocks=1 obj#=135967 tim=1270717957249032WAIT #2: nam=db file sequential read ela= 13 file#=4 block#=124455 blocks=1 obj#=135965 tim=1270717957249097SQL col object_name format a30 truncSQL select object_id,object_name from

7、dba_objects where object_id in (135965,135967); OBJECT_ID OBJECT_NAME- - 135965 TS2 135967 IDX_TS2索引范圍掃描SQL select object_id,object_name from ts2 where object_id=5293 and object_id select object_id from ts2;53147 rows selected.Execution Plan-Plan hash value: 1208602780-| Id | Operation | Name | Rows

8、 | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 53147 | 259K| 9 (12)| 00:00:01 | 1 | INDEX FAST FULL SCAN| IDX_TS2 | 53147 | 259K| 9 (12)| 00:00:01 |-全索引快速掃描的例子（2）WAIT #2: nam=db file sequential read ela= 19 file#=13 block#=779 blocks=1 obj#=135969 tim=1270728725384584WAIT #2: nam=db file

9、scattered read ela= 67 file#=13 block#=780 blocks=5 obj#=135969 tim=1270728725384827WAIT #2: nam=db file scattered read ela= 85 file#=13 block#=785 blocks=8 obj#=135969 tim=1270728725669783WAIT #2: nam=db file scattered read ela= 89 file#=13 block#=794 blocks=7 obj#=135969 tim=1270728727064985WAIT #

10、2: nam=db file scattered read ela= 94 file#=13 block#=801 blocks=8 obj#=135969 tim=1270728728098677全索引掃描根枝頁全索引掃描的例子SQL select object_id from ts2 order by object_id;53147 rows selected.Execution Plan-Plan hash value: 2548674868-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT

11、 STATEMENT | | 53147 | 259K| 120 (1)| 00:00:02 | 1 | INDEX FULL SCAN | IDX_TS2 | 53147 | 259K| 120 (1)| 00:00:02 |-全索引掃描的例子SELECT OBJECT_ID FROM TS2 ORDER BY OBJECT_IDWAIT #6: nam=db file scattered read ela= 66 file#=13 block#=777 blocks=8 obj#=135976 tim=1270729802699001WAIT #6: nam=db file scatter

12、ed read ela= 61 file#=13 block#=785 blocks=8 obj#=135976 tim=1270729803088893WAIT #6: nam=db file scattered read ela= 90 file#=13 block#=793 blocks=8 obj#=135976 tim=1270729804339874WAIT #6: nam=db file scattered read ela= 81 file#=13 block#=801 blocks=8 obj#=135976 tim=1270729804571606SELECT OBJECT

13、_ID FROM TS2 ORDER BY OBJECT_ID DESCWAIT #1: nam=db file sequential read ela= 8 file#=13 block#=897 blocks=1 obj#=135976 tim=1270729917271376WAIT #1: nam=db file scattered read ela= 68 file#=13 block#=889 blocks=8 obj#=135976 tim=1270729917277919WAIT #1: nam=db file scattered read ela= 84 file#=13 b

14、lock#=881 blocks=8 obj#=135976 tim=1270729918143853WAIT #1: nam=db file scattered read ela= 81 file#=13 block#=873 blocks=8 obj#=135976 tim=1270729919154423感謝您對(duì)華章培訓(xùn)網(wǎng)的支持! . 休息一下，下節(jié)更精彩問題：怎么訪問小表問題：小表是不加索引放到KEEP POOL里好呢還是通過索引訪問好呢？答案1：不用索引在KEEP POOL中好答案2：使用索引好答案3：上面都不對(duì)，使用索引放到KEEP池更好到底哪個(gè)對(duì)，還是實(shí)驗(yàn)來說話實(shí)驗(yàn)環(huán)境數(shù)據(jù)庫：任

15、意Oracle 8i以上版本（本測試環(huán)境為）操作系統(tǒng)：任意（本測試環(huán)境為 centos linux 4.5）其他要求必須設(shè)置DB_CACHE_KEEP_SIZE（10M以上，本環(huán)境為32M）實(shí)驗(yàn)準(zhǔn)備（1）創(chuàng)建測試表drop table test_small1;create table test_small1 ( id integer,code varchar(100) ) storage (buffer_pool keep);create unique index idx_small1 on test_small1(id) storage (buffer_pool keep);

16、create unique index idx_small12 on test_small1(code) storage (buffer_pool keep);實(shí)驗(yàn)準(zhǔn)備（）準(zhǔn)備數(shù)據(jù)裝載腳本create or replace procedure load_data1( rows number) isvname varchar2(100);begin for i in 1. rows loop vname:=name_head_of_name_just_for_test_case1; vname:=vname|_repeat_more_times_just_for_index; vname:=vn

17、ame|_test_only:; vname:=vname|to_char(i,0 99999999); insert into test_small1 values(i,vname); end loop; commit;end;/實(shí)驗(yàn)準(zhǔn)備（）裝載少量數(shù)據(jù)truncate table test_small1;exec load_data1(50);exec dbms_stats.gather_table_stats - (ownname=scott,tabname=test_small1,cascade=true);實(shí)驗(yàn)準(zhǔn)備（4）編寫測試腳本create or replace procedur

18、e test_case1 (ct integer) is vid integer; vname varchar2(200); vpid integer; vpname varchar2(100); begin for i in 1.ct loop vpid:=trunc(dbms_random.value(0.01,0.5)*100); vpname:=name_head_of_name_just_for_test_case1; vpname:=vpname|_repeat_more_times_just_for_index; vpname:=vpname|trim(to_char(vpid,

19、0 99999999); select /*+ full(a) */ id,code into vid,vname from test_small1 a where id=vpid; select /*+ index( a,idx_smaill1) */ id,code into vid,vname from test_small1 a where id=vpid; select /*+ index( a,idx_smaill12) */ id,code into vid,vname from test_small1 a where code=vpname; end loop;end;/實(shí)驗(yàn)準(zhǔn)

20、備（5）編寫PROFILER腳本create or replace procedure exec_test(ct integer) is err number; begin err:=DBMS_PROFILER.START_PROFILER (small tab test case1-1); dbms_output.put_line(runid is:|to_char(err); test_case1(ct); err:=DBMS_PROFILER.STOP_PROFILER ; end; / 檢查數(shù)據(jù)分布SQL select distinct dbms_rowid.rowid_block_n

21、umber(rowid) from test_small1;DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)- 920測試邏輯讀（1）declare vid integer; vname varchar2(200); vpy1 integer; vgets1 integer; vcr1 integer; vpy2 integer; vgets2 integer; vcr2 integer; vpid integer; begin select value into vpy1 from v$mystat where STATISTIC#=54; select value

22、into vgets1 from v$mystat where STATISTIC#=47; select value into vcr1 from v$mystat where STATISTIC#=50; for i in 1.100000 loop vpid:=trunc(dbms_random.value(0.01,0.5)*100); select /*+ index( a,idx_smaill1) */ id,code into vid,vname from test_small1 a where id=vpid; end loop; select value into vpy2

23、from v$mystat where STATISTIC#=54; select value into vgets2 from v$mystat where STATISTIC#=47; select value into vcr2 from v$mystat where STATISTIC#=50; dbms_output.put_line(py read :|to_char(vpy2-vpy1); dbms_output.put_line(db block get:|to_char(vgets2-vgets1); dbms_output.put_line(CR get :|to_char

24、(vcr2-vcr1);end;測試邏輯讀（1）結(jié)果py read :0db block get:0CR get :200000測試邏輯讀（2）declare vid integer; vname varchar2(200); vpy1 integer; vgets1 integer; vcr1 integer; vpy2 integer; vgets2 integer; vcr2 integer; vpid integer; begin select value into vpy1 from v$mystat where STATISTIC#=54; select value into vg

25、ets1 from v$mystat where STATISTIC#=47; select value into vcr1 from v$mystat where STATISTIC#=50; for i in 1.100000 loop vpid:=trunc(dbms_random.value(0.01,0.5)*100); select /*+ full(a) */ id,code into vid,vname from test_small1 a where id=vpid; end loop; select value into vpy2 from v$mystat where S

26、TATISTIC#=54; select value into vgets2 from v$mystat where STATISTIC#=47; select value into vcr2 from v$mystat where STATISTIC#=50; dbms_output.put_line(py read :|to_char(vpy2-vpy1); dbms_output.put_line(db block get:|to_char(vgets2-vgets1); dbms_output.put_line(CR get :|to_char(vcr2-vcr1);end;測試邏輯讀

27、（2）結(jié)果py read :0db block get:0CR get :700000測試邏輯讀（3）declare vid integer; vname varchar2(200); vpy1 integer; vgets1 integer; vcr1 integer; vpy2 integer; vgets2 integer; vcr2 integer; vpid integer; vpname varchar2(100); begin select value into vpy1 from v$mystat where STATISTIC#=54; select value into v

28、gets1 from v$mystat where STATISTIC#=47; select value into vcr1 from v$mystat where STATISTIC#=50; for i in 1.100000 loop vpid:=trunc(dbms_random.value(0.01,0.5)*100); vpname:=name_head_of_name_just_for_test_case1; vpname:=vpname|_repeat_more_times_just_for_index; vpname:=vpname|trim(to_char(vpid,0

29、99999999); select /*+ index(a idx_small12) */ id,code into vid,vname from test_small1 a where code=vpname; end loop; select value into vpy2 from v$mystat where STATISTIC#=54; select value into vgets2 from v$mystat where STATISTIC#=47; select value into vcr2 from v$mystat where STATISTIC#=50; dbms_ou

30、tput.put_line(py read :|to_char(vpy2-vpy1); dbms_output.put_line(db block get:|to_char(vgets2-vgets1); dbms_output.put_line(CR get :|to_char(vcr2-vcr1);end;測試邏輯讀（3）結(jié)果py read :0db block get:0CR get :200000執(zhí)行PROFILER測試set serveroutput on;exec exec_test(100000);/查看RUNIDSQL column MENT format a40 trunca

31、te;SQL select runid, run_date, MENT from plsql_profiler_runs order by runid; RUNID RUN_DATE MENT- - - 1 05-DEC-10 SEQ CACHE IS 1k 2 05-DEC-10 SEQ CACHE IS 10k 3 05-MAY-11 index test case1 4 05-MAY-11 small tab test case1-1 5 05-MAY-11 small tab test case1-1 6 05-MAY-11 small tab test case1-1 7 05-MA

32、Y-11 small tab test case1-1 8 05-MAY-11 index_test_case_1查詢測試結(jié)果column unit_name format a15 truncate;column occured format 999999 ;column line# format 99999 ;column tot_time format 999999.999999 ;col text format a60 truncselect p.unit_name, p.occured, p.tot_time, p.line# line, substr(s.text, 1,75) te

33、xt from (select u.unit_name, d.TOTAL_OCCUR occured, (d.TOTAL_TIME/1000000000) tot_time, d.line# from plsql_profiler_units u, plsql_profiler_data d where d.RUNID=u.runid and d.UNIT_NUMBER = u.unit_number and d.TOTAL_OCCUR 0 and u.runid= &RUN_ID) p, user_source s where p.unit_name = (+) and p.li

34、ne# = s.line (+)order by p.unit_name, p.line#; 測試結(jié)果 TOT_TIME LINE TEXT - - - .000008 5 test_case1(ct); .000003 6 err:=DBMS_PROFILER.STOP_PROFILER ; .000004 1 procedure test_case1 (ct integer) is .000056 7 for i in 1.50 loop .001658 8 select id,code into vid,vname from test_small1 where i .100517 10

35、for i in 1.ct loop .349403 11 vpid:=trunc(dbms_random.value(0.01,0.5)*100); .113059 12 vpname:=name_head_of_name_just_for_test_case1; .139251 13 vpname:=vpname|_repeat_more_times_just_for_index; .443862 14 vpname:=vpname|trim(to_char(vpid,0 4.869005 15 select /*+ full(a) */ id,code into vid,vname 3.

36、281354 17 select /*+ index( a,idx_smaill1) */ id,code into vid, 3.431544 19 select /*+ index( a,idx_smaill12) */ id,code into vid .000003 22 end; 階段性結(jié)論對(duì)于只占用一個(gè)塊，只有幾十條數(shù)據(jù)的情況，索引也能提高訪問效率索引字段越大，訪問開銷越大本測試的啟示不要輕信索引對(duì)小表也是有效的在某些極端情況下，合理使用索引，減少小表的全表掃描也會(huì)對(duì)性能有所幫助對(duì)小表建索引，并將表和索引全部放到KEEP POOL可以提高訪問效率空值的訪問問題：空值字段的訪問能用索

37、引嗎？select * from test_small1 where id is null;測試準(zhǔn)備（1）create or replace procedure load_data2( rows number) isvname varchar2(100); vid integer;begin for i in 1. rows loop vname:=name_head_of_name_just_for_test_case1; vname:=vname|_repeat_more_times_just_for_index; vname:=vname|trim(to_char(i,0 9999999

38、9); if(mod(i,100)=0) then vid:=null ; else vid:=i; end if; insert into test_small1 values(vid,vname); end loop; commit;end;/測試準(zhǔn)備truncate table test_small1;exec load_data2(100000);SQL select count(*) from test_small1; COUNT(*)- 100000SQL select count(*) from test_small1 where id is null; COUNT(*)- 20

39、0測試結(jié)果（1）SQL select * from test_small1 where id is null;200 rows selected.-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 101 | 2 (0)| 00:00:01 |* 1 | TABLE ACCESS FULL| TEST_SMALL1 | 1 | 101 | 2 (0)| 00:00:01 |-Statistics 1 recursive calls 0 db block gets 1523 consistent gets 0 physical reads 0 redo size 22734 bytes sent via SQL*Net to client 543 bytes received via SQL*Net from client 15 SQL*Net roundtrips to/from clien