oracle构建一致性读

对于实际的业务系统,通常有一些热点的表,insert和delete的量非常大,这个时候就会发现一些查询语句的逻辑读比较偏高,
这时可能就是oracle在构建一致性块的进行的consistent read。
下面做一个测试看下:
第一步准备数据:

create table test(
col1 varchar2(12)
col2 number
ext    varchar2(4000)
);
create index test_ind on test(user_id, col2);
create sequence seq_test cache 200;

这样这样的表,我们假设有频繁的插入和删除操作,那么下面来测试一下select的逻辑读的情况。

开启两个session:
1,创建表保存snapshot

在session1:
create table prefix_stats  tablespace IW_ACCOUNT_LOG_01 as select * from v$sesstat where sid=&1;

2,在session2查询

select  * from (select  * from test t where col1 = xpchild001 order by trans_log_id) where rownum <= 200;

3,在session1监控session2的统计信息

  

select *
  from (select t.name,
               pre.value as pre,
               suf.value as suf,
               (suf.value - pre.value) as diff
          from prefix_stats pre, v$sesstat suf, v$statname t
         where pre.sid = suf.sid
           and pre.STATISTIC# = suf.STATISTIC#
           and pre.STATISTIC# = t.STATISTIC#) tmp
 where tmp.diff > 0
 order by tmp.diff desc

Name                                                               PRE        SUF       DIFF
---------------------------------------------------------------------- ---------- ---------- ----------
session pga memory max                                                     957208    1153816     196608
session pga memory                                                         957208    1153816     196608
bytes sent via SQL*Net to client                                             6692      37013      30321
redo size                                                                       0       8256       8256
session logical reads                                                          52       1508       1456
consistent gets from cache                                                     52       1508       1456
consistent gets                                                                52       1508       1456
bytes received via SQL*Net from client                                       4385       5639       1254
consistent gets - examination                                                  21       1253       1232
data blocks consistent reads - undo records applied                             0        920        920
consistent changes                                                              0        920        920
buffer is not pinned count                                                     17        222        205
table fetch by rowid                                                            6        206        200
buffer is pinned count                                                          0        197        197
CR blocks created                                                               0        160        160
calls to kcmgas                                                                 0        160        160
db block changes                                                                0        120        120
redo entries                                                                    0        120        120
cleanout - number of ktugct calls                                               0        120        120
cleanouts and rollbacks - consistent read gets                                  0        120        120
immediate (CR) block cleanout applications                                      0        120        120
no work - consistent read gets                                                 19         83         64
heap block compress                                                             0         51         51
rollbacks only - consistent read gets                                           0         40         40
shared hash latch upgrades - no wait                                            0          5          5
user calls                                                                     28         33          5
execute count                                                                  21         23          2
DB time                                                                         0          2          2
parse count (total)                                                            22         24          2
session cursor cache count                                                     16         17          1
CPU used when call started                                                      0          1          1
recursive calls                                                                92         93          1
parse count (hard)                                                              0          1          1
session cursor cache hits                                                       4          5          1
CPU used by this session                                                        0          1          1

这一次的查询,返回记录200条。table fetch by rowid=200;

1,逻辑读session logical reads=consistent gets(一致读)+db block gets(当前读);
这个sql只有一致性读session logical reads=consistent gets=1456

2,构建一致性读应用回滚记录统计:data blocks consistent reads(undo records applied):920
等价于consistent changes。

3,需要回滚或者块清除产生的一致性读(这里边没有回滚,只可能有块清除)cleanouts and rollbacks - consistent read gets:120
跟db block changes=120一致,这里进行了块清楚,从而改变了db block。

4,构建一致性读clone的块数:CR blocks created=160
5,块清除产生的redo:redo size 8256

验证了开始的猜测:大量的构建一致性读。

对于这样的热点表,通常有几种手动去调整,但核心都是要分散热点,减少争用。

  1. hash表,分散热点
  2. 调整pctfree,增加pctfred的大小。使用块中的记录数变少,减少构建一致性读的问题。

未完待续。。。

oracle构建一致性读,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。