| hai 的个人资料Oracle & Starcraft日志列表 |  工具  帮助 |
|
3月22日 pctversion in lob segment在oracle的官方文档上有这么一段话来解释pctversion
PCTVERSION integerSpecify the maximum percentage of overall LOB storage space used for maintaining old versions of the LOB. The default value is 10, meaning that older versions of the LOB data are not overwritten until they consume 10% of the overall LOB storage space. You can specify the 由于lob segment采用了自己特殊的一致性读的实现，不是使用undo tablespace来保留前映象，而是当发生update时在lob segment内分配一个chunk去插入一条新的记录。这样的话，如果一条记录修改了多次，那么它就存在多个版本，对于很大的lob对象来说，这是十分浪费空间的。所以oracle需要有一个办法来控制这个保留前映象的空间的大小，pctversion就是为了实现这个功能的。pctversion是一个百分比的值，就是当前所有lob空间中用来存放前映象的百分比，如果前映象的空间大于pctversion了，那么oracle将会重用这些前映象空间而不去扩展。
对比一下不同pctversion对空间占用的影响
SQL 10G>select TABLE_NAME,SEGMENT_NAME,CHUNK,PCTVERSION,RETENTION from user_lobs where segment_name='TEXT_LOB';
TABLE_NAME SEGMENT_NAME CHUNK PCTVERSION RETENTION
------------------------------ ------------------------------ ---------- ---------- ---------- TESTLOB TEXT_LOB 8192 1 SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>select segment_name,bytes/1024/1024||'M' from user_segments where segment_name='TEXT_LOB';
SEGMENT_NAME BYTES/1024/1024||'M'
--------------------------------------------------------------------------------- ----------------------------------------- TEXT_LOB .0625M SQL 10G>insert into testlob values(1,rpad('a',4000)); 1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
------------------------ 4000 SQL 10G>begin
2 for i in 1..10 loop 3 update testlob set text=text||text; 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||'M' from user_segments where segment_name='TEXT_LOB';
SEGMENT_NAME BYTES/1024/1024||'M'
--------------------------------------------------------------------------------- ----------------------------------------- TEXT_LOB 13M SQL 10G>truncate table testlob;
Table truncated.
SQL 10G>alter table testlob modify lob(text) (pctversion 99);
Table altered.
SQL 10G>insert into testlob values(1,rpad('a',4000));
1 row created.
SQL 10G>commit;
Commit complete.
SQL 10G>select segment_name,bytes/1024/1024||'M' from user_segments where segment_name='TEXT_LOB';
SEGMENT_NAME BYTES/1024/1024||'M'
--------------------------------------------------------------------------------- ----------------------------------------- TEXT_LOB .0625M SQL 10G>begin
2 for i in 1..10 loop 3 update testlob set text=text||text; 4 commit; 5 end loop; 6 end; 7 / PL/SQL procedure successfully completed.
SQL 10G>select segment_name,bytes/1024/1024||'M' from user_segments where segment_name='TEXT_LOB';
SEGMENT_NAME BYTES/1024/1024||'M'
--------------------------------------------------------------------------------- ----------------------------------------- TEXT_LOB 17M SQL 10G>select dbms_lob.getlength(text) from testlob;
DBMS_LOB.GETLENGTH(TEXT)
------------------------ 4096000 可以看到pctversion的设置对lob空间大小还是有影响的，在很大的lob segment中这种影响会表现的十分明显，有兴趣的人可以对大lob段做测试。 3月16日 parallel query in partition table上一篇文章里面提到parallel query相关的checkpoint,也了解了direct read会对系统性能带来的潜在影响，这次让我们看一下parallel query在partition table上的表现。
创建2种分区表
CREATE TABLE TESTHASH(A NUMBER) PARTITION BY HASH (A) (PARTITION P1, PARTITION P2, PARTITION P3, PARTITION P4) CREATE TABLE TESTLIST(A NUMBER) PARTITION BY LIST (A) (PARTITION P1 VALUES(1), PARTITION P2 VALUES(2), PARTITION P3 VALUES(3), PARTITION P4 VALUES(4)) SQL 10G>select distinct a from testhash partition (p1); 6 SQL 10G>select distinct a from testhash partition (p2);
9 SQL 10G>select distinct a from testhash partition (p3);
2 SQL 10G>select distinct a from testhash partition (p4);
1 SQL 10G>select count(*) from testhash partition (p1);
1048623 SQL 10G>select count(*) from testhash partition (p2);
1048623 SQL 10G>select count(*) from testhash partition (p3);
1048623 SQL 10G>select count(*) from testhash partition (p4);
1048623 SQL 10G>select distinct a from testlist partition (p1); 1 SQL 10G>select distinct a from testlist partition (p2); 2 SQL 10G>select distinct a from testlist partition (p3); 3 SQL 10G>select distinct a from testlist partition (p4); 4 SQL 10G>select count(*) from testlist partition (p1); 1048623 SQL 10G>select count(*) from testlist partition (p2); 1048623 SQL 10G>select count(*) from testlist partition (p3); 1048623 SQL 10G>select count(*) from testlist partition (p4); 1048623 两张分区表都是4个分区，每个分区包含1048623条记录 另外还有几个考查direct read与否的指标 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 137327 consistent gets direct 574 physical reads direct 574 table scans (direct read) 0 我们运行几个sql来看parallel query在分区表上的表现 SQL 10G>select /*+ parallel(testlist,4)*/count(*) from testlist; COUNT(*) ---------- 4194492 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 137449 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) SQL 10G>select 137449-137327 "consistent gets from cache",6934-574 "consistent gets direct",6934-574 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 122 6360 6360 可以看到当parallel query包含所有分区的时候将会去做direct read 看看一个分区的情况 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 137449 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) 55 SQL 10G>select /*+ parallel(testlist,4)*/count(*) from testlist where a=1; COUNT(*) ---------- 1048623 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE
---------------------------------------------------------------- ---------- consistent gets from cache 139307 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) 55 SQL 10G>select 139307-137449 "consistent gets from cache",6934-6934 "consistent gets direct",6934-6934 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 1858 0 0 当查询一个分区时可以看到并没有采用direct read，而是采用普通的consistent get 两个分区的情况 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 139307 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) 55 SQL 10G>select /*+ parallel(testlist,4)*/count(*) from testlist where a in(1,2);
COUNT(*) ---------- 2097246 SQL 10G>SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 142788 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) 55 SQL 10G>select 142788-139307"consistent gets from cache",6934-6934 "consistent gets direct",6934-6934 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 3481 0 0 同样没有发生direct read 三个分区呢？ SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 142788 consistent gets direct 6934 physical reads direct 6934 table scans (direct read) 55 SQL 10G>select /*+ parallel(testlist,4)*/count(*) from testlist where a in(1,2,3); COUNT(*) ---------- 3145869 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE
---------------------------------------------------------------- ---------- consistent gets from cache 142907 consistent gets direct 11704 physical reads direct 11704 table scans (direct read) 110 SQL 10G>select 142907-142788 "consistent gets from cache",11704-6934 "consistent gets direct",11704-6934 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 119 4770 4770 可以看到发生了3个分区的direct read 如果我们查询不存在的分区值呢 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#; NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 142907 consistent gets direct 11704 physical reads direct 11704 table scans (direct read) 110 SQL 10G>select /*+ parallel(testlist,4)*/count(*) from testlist where a in(5,6,7);
COUNT(*) ---------- 0 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(56,248,53,51) and a.statistic#=b.statistic#;
NAME VALUE ---------------------------------------------------------------- ---------- consistent gets from cache 142907 consistent gets direct 11704 physical reads direct 11704 table scans (direct read) 110 SQL 10G>select 142907-142907 "consistent gets from cache",11704-11704"consistent gets direct",11704-11704"physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 0 0 0 很好，没有读testlist表的任何block 再来看一下hash partition table的表现 select /*+ parallel(testhash,4)*/count(*) from testhash; SQL 10G>select 149410-149287 "consistent gets from cache",18064-11704 "consistent gets direct",18064-11704 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 123 6360 6360 select /*+ parallel(testhash,4)*/count(*) from testhash where a in(1); SQL 10G>select 151269-149410 "consistent gets from cache",18064-18064 "consistent gets direct",18064-18064 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 1859 0 0 select /*+ parallel(testhash,4)*/count(*) from testhash where a in(1,2); SQL 10G>select 154751-151269 "consistent gets from cache",18064-18064 "consistent gets direct",18064-18064 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 3482 0 0 select /*+ parallel(testhash,4)*/count(*) from testhash where a in(1,2,6); SQL 10G>select 154871-154751 "consistent gets from cache",22834-18064 "consistent gets direct",22834-18064 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 120 4770 4770 select /*+ parallel(testhash,4)*/count(*) from testhash where a in(3); SQL 10G>select 160572-158713 "consistent gets from cache",37144-37144 "consistent gets direct",37144-37144 "physical reads direct" from dual; consistent gets from cache consistent gets direct physical reads direct -------------------------- ---------------------- --------------------- 1859 0 0 可以看到hash partition table在其他情况都和list partition table一样，但是在查询不存在的分区值时还是会去读取testhash表的block，当然这是由于hash partition的机制决定的。 总结来说，parallel query在partition table上并不是时时在做direct read,它是和查询包含的partition number有关的，至于如何利用好parallel query的优势那又是另外的话题了，大家看完这2篇文章后可以自己去找寻正确的结论。 3月15日 DBWR parallel query checkpoint buffers writtenDBWR parallel query checkpoint buffers written是oracle的一种特殊的checkpoint的产物，这种checkpoint在oracle8 以前称作Extent-based Checkpoint，当发生parallel query时oracle必须做checkpoint把脏数据写入磁盘，因为parallel query走的时direct read，直接从文件读入pga，如果有脏数据没有被写回磁盘，那么读出来的结果将会不一致。在oracle8以前，如果发生direct read，那么当每一个extent被读入的时候oracle会先去检查data buffer中有没和这个extent相关的dirty block，如果有就会驱动dbwr来写出，同时增加一次checkpoint的次数。这样的话如果这个表有N个extent都存在dirty block,那么将会发生N次checkpoint,这样的话将会使parallel query的执行时间大大超过normal query。在oracle8中这种情况有了改进，Extent-based Checkpoint变成了object-based checkpoint,不再会对单独的extent做checkpoint。
让我们再回到主题，DBWR parallel query checkpoint buffers written
首先来看一下v$statname
SQL 10G>select statistic#,name from v$statname where name like '%DBWR%';
STATISTIC# NAME
---------- ---------------------------------------------------------------- 69 DBWR checkpoint buffers written 70 DBWR thread checkpoint buffers written 71 DBWR tablespace checkpoint buffers written 72 DBWR parallel query checkpoint buffers written 73 DBWR object drop buffers written 74 DBWR transaction table writes 75 DBWR undo block writes 76 DBWR revisited being-written buffer 77 DBWR make free requests 78 DBWR lru scans 79 DBWR checkpoints STATISTIC# NAME
---------- ---------------------------------------------------------------- 80 DBWR fusion writes 再来创建一张测试表
create table test(a number)
SQL 10G>select count(*) from test;
COUNT(*)
---------- 4194496 SQL 10G>select distinct a from test;
A
---------- 1 这个测试表包含了4194496条值为1的记录
先刷新data buffer
SQL 10G>ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
Session altered.
我们来更新其中一条记录
SQL 10G>update test set a=2 where rownum<2;
1 row updated.
SQL 10G>commit;
Commit complete.
执行parallel query
SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 28615 DBWR checkpoints 552 SQL 10G>select/*+ parallel(test,4)*/distinct a from test;
A
---------- 2 1 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#; NAME VALUE
---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 28616 DBWR checkpoints 553 checkpoint和buffer written都增加1 再来更新分布在多个extent上的block来证实一下是不是现在的oracle版本用的是object-based checkpoint
SQL 10G>update test set a=10 where mod(dbms_rowid.ROWID_BLOCK_NUMBER(rowid),1000)=0 and dbms_rowid.ROWID_ROW_NUMBER(rowid)=0;
6 rows updated.
SQL 10G>commit;
Commit complete.
SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 28616 DBWR checkpoints 553 SQL 10G>select/*+ parallel(test,4)*/distinct a from test;
A
---------- 10 2 1 SQL 10G>select b.name,a.value from v$sysstat a,v$statname b where a.statistic# in(72,79) and a.statistic#=b.statistic#;
NAME VALUE
---------------------------------------------------------------- ---------- DBWR parallel query checkpoint buffers written 28622 DBWR checkpoints 554 可以看到写出了6个块，但是只发生一次checkpoint，所以我们可以知道oracle已经采用了object-based checkpoint。
总结来说，DBWR parallel query checkpoint buffers written就是当发生parallel query时导致checkpoint而写出的块数。
|
|
|
