上一篇文章里面提到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篇文章后可以自己去找寻正确的结论。