研究在表很小时唯一索引为什么可以优化热块争用，但非唯一索引无法优化热块争用...

一、试验目的：模拟热块争用，使用唯一索引优化热块争用。研究在表很小时唯一索引为什么可以优化热块争用，但非唯一索引无法优化热块争用
二、试验环境：
数据库：
SQL> select *  from  v$version;


BANNER
-------------------------------------------------------------------------------------------------
Oracle Database 10g Release 10.2.0.4.0 - 64bit Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production


操作系统：
WIN7  64位旗舰版


三、模拟热块争用
1. 准备测试数据
1.1 创建测试用的表
create table lixia.t5 as select *  from  dba_objects where rownum<101;






2. 模拟热块争用
2.1 找出在同一个数据库的数据行
select object_id,dbms_rowid.rowid_relative_fno(rowid) file#,
         dbms_rowid.rowid_object(rowid) object_number ,
        dbms_rowid.rowid_block_number(rowid) block_number ,
        dbms_rowid.rowid_row_number(rowid) row_number
from  lixia.t5; 


OBJECT_ID      FILE# OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER
--------- ---------- ------------- ------------ ----------
       90          4         52699         2188         88
       91          4         52699         2188         89
       92          4         52699         2188         90
       93          4         52699         2189          0
       94          4         52699         2189          1
       95          4         52699         2189          2
       96          4         52699         2189          3
       97          4         52699         2189          4
       98          4         52699         2189          5
       99          4         52699         2189          6
      100          4         52699         2189          7


OBJECT_ID      FILE# OBJECT_NUMBER BLOCK_NUMBER ROW_NUMBER
--------- ---------- ------------- ------------ ----------
      101          4         52699         2189          8


已选择100行。


2.2 在无索引的情况下执行模拟热块争用的PL/SQL


2.2.1 执行SQL/SQL
--SESSION 1
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=99;
end loop;
end;
/




--SESSION 2
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=100;
end loop;
end;
/


2.2.2 在第三个会话中查看非空闲等待事件
SQL> col event format a50;


SQL> select  sid,event,p1raw,p2raw  from  v$session where wait_class<>'Idle' order by event;


       SID EVENT                                              P1RAW            P2RAW
---------- -------------------------------------------------- ---------------- ----------------
       149 SQL*Net message to client                          0000000042455100 0000000000000001
       135 latch: cache buffers chains                        000007FF213441F8 000000000000007A
       146 latch: cache buffers chains                        000007FF213441F8 000000000000007A




2.3 创建唯一索引，执行模拟热块争用的PL/SQL
2.3.1 创建唯一索引
create unique index  lixia.t5_index on lixia.t5(object_id);


2.3.2 执行模拟热块争用的PL/SQL






--SESSION 1
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=99;
end loop;
end;
/




--SESSION 2
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=100;
end loop;
end;
/


2.3.3 在第三个会话中查看非空闲等待事件


SQL> select  sid,event,p1raw,p2raw  from  v$session where wait_class<>'Idle' order by event;


       SID EVENT                                              P1RAW            P2RAW
---------- -------------------------------------------------- ---------------- ----------------
       149 SQL*Net message to client                          0000000042455100 0000000000000001






2.4 创建非唯一索引，在使用非唯一索引的情况下模拟热块争用
2.4.1 创建非唯一索引


SQL> col owner format a20
SQL> col index_name format a20
SQL> col table_name format a20
SQL> select owner,index_name,table_name,UNIQUENESS  from dba_indexes where index_name='T5_INDEX';


OWNER                INDEX_NAME           TABLE_NAME           UNIQUENESS
-------------------- -------------------- -------------------- ------------------
LIXIA                T5_INDEX             T5                   NONUNIQUE


drop   index  lixia.t5_index;


create  index  lixia.t5_index on lixia.t5(object_id);


2.4.2 执行模拟热块争用的PL/SQL






--SESSION 1
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=99;
end loop;
end;
/




--SESSION 2
declare
type n_type is record
(v_object_id lixia.t5.object_id%type,
 v_OWNER lixia.t5.OWNER%type);


n n_type; 
begin  
for  i in 1..10000000 loop
select  object_id,OWNER into n from  lixia.t5 where object_id=100;
end loop;
end;
/


2.4.3 在第三个会话中查看非空闲等待事件


SQL> col event format a50;


SQL> select  sid,event,p1raw,p2raw  from  v$session where wait_class<>'Idle' order by event;


       SID EVENT                                              P1RAW            P2RAW
---------- -------------------------------------------------- ---------------- ----------------
       149 SQL*Net message to client                          0000000042455100 0000000000000001
       135 latch: cache buffers chains                        000007FF213441F8 000000000000007A
       146 latch: cache buffers chains                        000007FF213441F8 000000000000007A
       
四、通过查看执行计划和分析10046事件分析唯一索引为什么可以优化热块争用       


1. 开启会话10046事件
alter  session  set  tracefile_identifier='latch-buffer_chains_unique_index';


alter  session  set events '10046 trace name context forever,level 8';




2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;
3 关闭10046
alter session set events '10046 trace name context off';


4 查看执行计划
4.1 
alter session set STATISTICS_LEVEL = ALL;


4.2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;


4.3 查看SQL的ID
SQL> select sql_id,sql_text from v$sqlarea where sql_text like '% from  lixia.t5 where object_id=100';


SQL_ID
--------------------------
SQL_TEXT
--------------------------------------------------------------------------------
497u0qs5qruv5
select  object_id,OWNER from  lixia.t5 where object_id=100


4.4 查看执行计划
select *  from  table(dbms_xplan.display_cursor('497u0qs5qruv5',1,'allstats  advanced'));
SQL> select *  from  table(dbms_xplan.display_cursor('497u0qs5qruv5',1,'allstats  advanced'));


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
SQL_ID  497u0qs5qruv5, child number 1
-------------------------------------
select  object_id,OWNER from  lixia.t5 where object_id=100


Plan hash value: 1100863087


----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T5       |      1 |      1 |     8 |     1   (0)| 00:00:01 |   1 |00:00:00.01 |       2 |
|*  2 |   INDEX UNIQUE SCAN         | T5_INDEX |      1 |      1 |       |     0   (0)|          |   1 |00:00:00.01 |       1 |


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
----------------------------------------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T5@SEL$1
   2 - SEL$1 / T5@SEL$1


Outline Data
-------------




PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T5"@"SEL$1" ("T5"."OBJECT_ID"))
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------


   2 - access("OBJECT_ID"=100)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OWNER"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22]
   2 - "T5".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]




已选择43行。




SQL> select VERSION_COUNT  from v$sqlarea where sql_id='497u0qs5qruv5';


VERSION_COUNT
-------------
            2




alter session  set  statistics_level=typical;     




分析执行计划，看到执行了三次逻辑IO。      
            
5. 分析10046追踪的信息
PARSING IN CURSOR #1 len=58 dep=0 uid=0 oct=3 lid=0 tim=2866467674 hv=191621989 ad='1a939b38'
select  object_id,OWNER from  lixia.t5 where object_id=100
END OF STMT
PARSE #1:c=0,e=37169,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=2866467663
EXEC #1:c=0,e=17714,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=2866489635
WAIT #1: nam='SQL*Net message to client' ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=2866501458
FETCH #1:c=15600,e=11242,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=2866513679
WAIT #1: nam='SQL*Net message from client' ela= 101993 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=2866616366
FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=2866619304
WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=2866620212
WAIT #1: nam='SQL*Net message from client' ela= 7280910 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=2873917359
STAT #1 id=1 cnt=1 pid=0 pos=1 obj=52699 op='TABLE ACCESS BY INDEX ROWID T5 (cr=2 pr=0 pw=0 time=44 us)'
STAT #1 id=2 cnt=1 pid=1 pos=1 obj=52701 op='INDEX UNIQUE SCAN T5_INDEX (cr=1 pr=0 pw=0 time=22 us)'


执行该SQL参生了三次逻辑读，物理读为零。


6 分析总结


执行该SQL参生了三次逻辑读，物理读为零。该SQL的执行计划第一进行唯一索引扫描，第二步使用ROWID进行回表。




=======================================================================================================================


五、 分析非唯一索引为什么无法优化热块争用
1 删除唯一索引，创建非唯一索引
drop  index  lixia.t5_index;


create index  lixia.t5_index on lixia.t5(object_id);


2 执行SQL，并查看执行计划
2.1 
alter session set STATISTICS_LEVEL = ALL;


2.2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;






2.3 查看执行计划






SQL> select *  from  table(dbms_xplan.display_cursor('497u0qs5qruv5',0,'allstats  advanced'));


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
SQL_ID  497u0qs5qruv5, child number 0
-------------------------------------
select  object_id,OWNER from  lixia.t5 where object_id=100


Plan hash value: 3815934559


----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY INDEX ROWID| T5       |      2 |      1 |     8 |     2   (0)| 00:00:01 |   2 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN          | T5_INDEX |      2 |      1 |       |     1   (0)| 00:00:01 |   2 |00:00:00.01 |       4 |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
----------------------------------------------------------------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T5@SEL$1
   2 - SEL$1 / T5@SEL$1


Outline Data
-------------




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "T5"@"SEL$1" ("T5"."OBJECT_ID"))
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
---------------------------------------------------


   2 - access("OBJECT_ID"=100)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OWNER"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22]
   2 - "T5".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]




已选择43行。


SQL>


修改会话统计信息收集等级
alter session  set  statistics_level=typical;




从执行计划看进行索引访问扫描，然后使用ROWID回表，总共进行了10次逻辑IO（这个是估算，后面通过10046 TRACE分析可以发现实际只进行了5次逻辑IO）。


3 开启10046事件，执行SQL
3.1 开启会话10046事件
alter  session  set  tracefile_identifier='latch-buffer_chains_index';


alter  session  set events '10046 trace name context forever,level 8';




3.2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;


3.3 关闭10046
alter session set events '10046 trace name context off';


4 分析10046 TRACE
PARSING IN CURSOR #17 len=58 dep=0 uid=0 oct=3 lid=0 tim=6657228685 hv=191621989 ad='1a939b38'
select  object_id,OWNER from  lixia.t5 where object_id=100
END OF STMT
PARSE #17:c=0,e=1580,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=6657228679
EXEC #17:c=0,e=44,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=6657232660
WAIT #17: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=6657233380
FETCH #17:c=0,e=51,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,tim=6657234140
WAIT #17: nam='SQL*Net message from client' ela= 334 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=6657235268
FETCH #17:c=0,e=12,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=6657236110
WAIT #17: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=6657236898
*** 2015-02-28 12:05:38.702
WAIT #17: nam='SQL*Net message from client' ela= 6108463 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=6663346100
STAT #17 id=1 cnt=1 pid=0 pos=1 obj=52699 op='TABLE ACCESS BY INDEX ROWID T5 (cr=3 pr=0 pw=0 time=43 us)'
STAT #17 id=2 cnt=1 pid=1 pos=1 obj=52718 op='INDEX RANGE SCAN T5_INDEX (cr=2 pr=0 pw=0 time=34 us)'
PARSE #3:c=0,e=13,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=6663350482
EXEC #3:c=0,e=40,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=6663351634


分析10046 TRACE ，进行了5次逻辑IO。
=======================================================================================================================
六、 分析无索引为什么产生热块争用
1 删除索引
drop  index  lixia.t5_index;


2 开启10046事件，执行SQL
2.1 开启会话10046事件
alter  session  set  tracefile_identifier='latch-buffer_chains_no_index';


alter  session  set events '10046 trace name context forever,level 8';




2.2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;


2.3 关闭10046
alter session set events '10046 trace name context off';


2.4 分析10046 TRACE
PARSING IN CURSOR #11 len=58 dep=0 uid=0 oct=3 lid=0 tim=5389316812 hv=191621989 ad='1a939b38'
select  object_id,OWNER from  lixia.t5 where object_id=100
END OF STMT
PARSE #11:c=0,e=1476,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=5389316807
EXEC #11:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=5389321181
WAIT #11: nam='SQL*Net message to client' ela= 3 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=5389321990
FETCH #11:c=0,e=78,p=0,cr=4,cu=0,mis=0,r=1,dep=0,og=1,tim=5389322796
WAIT #11: nam='SQL*Net message from client' ela= 336 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=5389323893
FETCH #11:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=5389324891
WAIT #11: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=5389325751
*** 2015-02-28 11:44:34.595
WAIT #11: nam='SQL*Net message from client' ela= 9947596 driver id=1111838976 #bytes=1 p3=0 obj#=440 tim=5399274145
STAT #11 id=1 cnt=1 pid=0 pos=1 obj=52699 op='TABLE ACCESS FULL T5 (cr=5 pr=0 pw=0 time=66 us)'


使用全表扫描进行了5次逻辑读。


3  执行SQL并查看执行计划
3.1 
alter session set STATISTICS_LEVEL = ALL;


3.2 执行SQL
select  object_id,OWNER from  lixia.t5 where object_id=100;




3.3 查看执行计划


SQL> select *  from  table(dbms_xplan.display_cursor('497u0qs5qruv5',null,'allstats  advanced'));


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
SQL_ID  497u0qs5qruv5, child number 0
-------------------------------------
select  object_id,OWNER from  lixia.t5 where object_id=100


Plan hash value: 2002323537


--------------------------------------------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|*  1 |  TABLE ACCESS FULL| T5   |      1 |      1 |     8 |     3   (0)| 00:00:01 |      1 |00:00:00.01 |       5 |
--------------------------------------------------------------------------------------------------------------------


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
----------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1 / T5@SEL$1


Outline Data
-------------


  /*+
      BEGIN_OUTLINE_DATA


PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      FULL(@"SEL$1" "T5"@"SEL$1")
      END_OUTLINE_DATA
  */


Predicate Information (identified by operation id):
---------------------------------------------------




PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------------
----------------------------------------
   1 - filter("OBJECT_ID"=100)


Column Projection Information (identified by operation id):
-----------------------------------------------------------


   1 - "OWNER"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22]




已选择40行。






alter session  set  statistics_level=typical; 




分析执行计划，可以看到使用全表扫描进行了5次逻辑IO。






七、 结论
   通过上面的试验我们发现，唯一索引扫描再回表的逻辑IO为三次，全表扫描的逻辑IO为五次，使用索引范围扫描再回表的逻辑IO为五次（与全表扫描一样的逻辑IO）。
这就是唯一索引可以优化热块争用，而非唯一索引无法优化热块争用的原因。

    唯一索引逻辑IO次数少就可以优化热块争用的结论是错误的。
   我查了吕海波著的《ORACLE内核技术揭秘》，之所以唯一索引可以优化热块争用，是因为在唯一索引访问路径（INDEX UNIQUE SCAN) 下，从索引根块、枝块、叶块、
表块全是共享 cache buffers chains  latch。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21582653/viewspace-1443636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21582653/viewspace-1443636/