oracle数据库性能优化实务第22讲-索引和访问路径

1、Oracle数据库性能优化实务第14讲：索引与访问路径主讲人：白鳝华章培训网、 华章培训网 访问路径和SQL性能访问路径决定了执行计划的性能调整访问路径是SQL优化中最为重要的工作在一般OLTP系统中，超过60%的问题是通过调整访问路径解决的调整访问路径的优化成本最低调整访问路径的方法调整索引调整表连接顺序和连接方式选择访问路径（通过HINT,OUTLINES,表分析等方式）什么是索引如何在图书馆中找到你要的书索引对单表访问的影响根枝页索引单表访问的几种主要方式索引唯一性访问索引范围扫描全索引扫描快速全索引扫描索引跳跃扫描索引访问案例分析(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访问的数据块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感谢您对华章培训网的支持! . 休息一下，下节更精彩问题：怎么访问小表问题：小表是不加索引放到KEEP POOL里好呢还是通过索引访问好呢？答案1：不用索引在KEEP POOL中好答案2：使用索引好答案3：上面都不对，使用索引放到KEEP池更好到底哪个对，还是实验来说话实验环境数据库：任

15、意Oracle 8i以上版本（本测试环境为）操作系统：任意（本测试环境为 centos linux 4.5）其他要求必须设置DB_CACHE_KEEP_SIZE（10M以上，本环境为32M）实验准备（1）创建测试表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);实验准备（）准备数据装载脚本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;/实验准备（）装载少量数据truncate table test_small1;exec load_data1(50);exec dbms_stats.gather_table_stats - (ownname=scott,tabname=test_small1,cascade=true);实验准备（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;/实验准

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; / 检查数据分布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）结果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）结果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）结果py read :0db block get:0CR get :200000执行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查询测试结果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#; 测试结果 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; 阶段性结论对于只占用一个块，只有几十条数据的情况，索引也能提高访问效率索引字段越大，访问开销越大本测试的启示不要轻信索引对小表也是有效的在某些极端情况下，合理使用索引，减少小表的全表扫描也会对性能有所帮助对小表建索引，并将表和索引全部放到KEEP POOL可以提高访问效率空值的访问问题：空值字段的访问能用索

37、引吗？select * from test_small1 where id is null;测试准备（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;/测试准备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测试结果（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