Oracle & Starcraft

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