【博客文章2014】Oracle 10gR2 RAC数据库中的Master实例、Owner实例、Past Image和Current Image的概念

POOL

NAME

BYTES

1

shared pool

KCL buffer header

9664

2

shared pool

KCL instance cache transf

262144

3

shared pool

KCL lock contexts

14420

4

shared pool

KCL lock state

3584

5

shared pool

KCL name table

262144

6

shared pool

KCL offline scn array

1608

7

shared pool

KCL partition table

720896

8

shared pool

KCL region array

8

9

shared pool

gcs I/O statistics struct

32

10

shared pool

gcs affinity 

4108

11

shared pool

gcs close obj

4104

12

shared pool

gcs commit sga state

67596

13

shared pool

gcs mastership buckets

4608

14

shared pool

gcs opaque in

4028

15

shared pool

gcs res hash bucket

32768

16

shared pool

gcs res latch table

15360

17

shared pool

gcs resource freelist arr

272

18

shared pool

gcs resource freelist dyn

32

19

shared pool

gcs resources

4812504

20

shared pool

gcs scan queue array

216

21

shared pool

gcs shadow locks dyn seg 

32

22

shared pool

gcs shadow locks freelist

272

23

shared pool

gcs shadows

3418328

select * from v$ges_resource where resource_name like '%BL%';

select * from v$ges_enqueue where resource_name2 like '%BL%';

注：v$ges_resource和v$dlm_ress是相同的。

实验第1步：

查看实验用表t04209_uname的定义

在任一个实例上执行:

select t.TABLE_NAME, t.COLUMN_NAME, t.DATA_LENGTH, t.DATA_PRECISION

from dba_tab_columns t

where t.owner = 'HR'

and t.table_name = 'T04209_UNAME';

实验第2步：

查看实验用表t04209_uname的内容

在任一个实例上执行:

select * from hr.t04209_uname;

共10万行

实验第3步：

查看实验用表t04209_uname的数据块个数

在任一个实例上执行:

select s.segment_name, s.blocks

from dba_segments s

where s.owner = 'HR'

and s.segment_name = 'T04209_UNAME';

实验第4步：

查看实验用表t04209_uname的object_id

在任一个实例上执行:

select o.object_name, o.object_id

from dba_objects o

where o.owner = 'HR'

and o.object_name = 'T04209_UNAME';

实验第5步：

在任一个实例上执行:

select * from x$OBJECT_AFFINITY_STATISTICS where object = 52533;

无输出

实验第6步：

在实例1上update这个表t04209_uname：

update hr.t04209_uname set uvalue = 2 where uname = 'a1';

update hr.t04209_uname set uvalue = 3 where uname = 'a2';

update hr.t04209_uname set uvalue = 4 where uname = 'a3';

update hr.t04209_uname set uvalue = 5 where uname = 'a4';

update hr.t04209_uname set uvalue = 6 where uname = 'a5';

update hr.t04209_uname set uvalue = 7 where uname = 'a6';

update hr.t04209_uname set uvalue = 8 where uname = 'a7';

……

update hr.t04209_uname set uvalue = 99996 where uname = 'a99995';

update hr.t04209_uname set uvalue = 99997 where uname = 'a99996';

update hr.t04209_uname set uvalue = 99998 where uname = 'a99997';

update hr.t04209_uname set uvalue = 99999 where uname = 'a99998';

update hr.t04209_uname set uvalue = 100000 where uname = 'a99999';

update hr.t04209_uname set uvalue = 100001 where uname = 'a100000';

共10万行

实验第7步：

在任一个实例上执行:

select * from x$OBJECT_AFFINITY_STATISTICS where object = 52533;

实验第8步：

等实例1update结束后，再查x$OBJECT_AFFINITY_STATISTICS没有输出。

重新在实例1上做 update，x$OBJECT_AFFINITY_STATISTICS仍然没有输出。

这就说明：如果缓存中的数据块已经处于适当的模式，就没有必要在数据块上再打开BL锁。所以如果会话反复存取同一个数据块而不请求额外的BL锁，那么在x$OBJECT_AFFINITY_STATISTICS中BL锁的Open计数就不会增加。

实验第9步：

在实例1上alter system flush buffer_cache;后，再做同样的update。

实验第10步：

在任一个实例上执行:

select * from x$OBJECT_AFFINITY_STATISTICS where object = 52533;

实验第11步：

为了使情况更复杂点,在实例2同时进行（保证实例1还在执行刚才的update）：

update hr.t04209_uname set uvalue = 9 where uname = 'a8';

update hr.t04209_uname set uvalue = 99 where uname = 'a98';

update hr.t04209_uname set uvalue = 999 where uname = 'a998';

update hr.t04209_uname set uvalue = 9999 where uname = 'a9998';

update hr.t04209_uname set uvalue = 99999 where uname = 'a99998';

update hr.t04209_uname set uvalue = 99998 where uname = 'a99997';

update hr.t04209_uname set uvalue = 99997 where uname = 'a99996';

update hr.t04209_uname set uvalue = 99996 where uname = 'a99995';

update hr.t04209_uname set uvalue = 99995 where uname = 'a99994';

……

update hr.t04209_uname set uvalue = 10006 where uname = 'a10005';

update hr.t04209_uname set uvalue = 10005 where uname = 'a10004';

update hr.t04209_uname set uvalue = 10004 where uname = 'a10003';

update hr.t04209_uname set uvalue = 10003 where uname = 'a10002';

update hr.t04209_uname set uvalue = 10002 where uname = 'a10001';

update hr.t04209_uname set uvalue = 10001 where uname = 'a10000';

update hr.t04209_uname set uvalue = 10000 where uname = 'a9999';

update hr.t04209_uname set uvalue = 100001 where uname = 'a100000';

update hr.t04209_uname set uvalue = 100000 where uname = 'a99999';

共10万行，这10万行update是不按顺序打乱后的组合。

实验第12步：

在任一个实例上执行:

select * from x$OBJECT_AFFINITY_STATISTICS where object = 52533;

实验第13步：

在任一个实例上执行:

select to_char(52533, 'xxxxxxxx') from dual;

实验第14步：

在任一个实例上执行: 查看两个实例此时的资源状态

select inst_id,

resource_name,

on_convert_q,

on_grant_q,

master_node,

next_cvt_level

from gv$ges_resource

where resource_name like '[0xcd35]%';

实验第15步：

在任一个实例上执行: 查看两个实例此时的锁状态

select inst_id,

grant_level,

request_level,

resource_name2,

PID,

TRANSACTION_ID0,

TRANSACTION_ID1

from gv$ges_enqueue

where resource_name2 like '52533%';

KJUSERPR是BL锁，KJUSERCW是表级共享锁，KJUSERTM是行级独占锁，KJUSERNL是空锁，KJUSEREX是lock table之类命令产生的独占锁