再上一篇:13.2表分区机制
上一篇:13.3索引分区
主页
下一篇:13.5审计和段空间压缩
再下一篇:13.6小结
文章列表

13.4再论分区和性能

Oracle 9i 10g编程艺术:深入数据库体系结构

我经常听人说:“我对分区真是失望。我们对最大的表执行了分区,但它变得更慢了。难道分区就是 这样吗?它能算一种性能提升特性吗?”
对总体查询性能来说,分区的影响无非有以下三种可能: 使你的查询更快
根本不影响查询的性能 是你的查询更慢,而且与未分区实现相比,会占用多出几倍的资源
在一个数据仓库中,基于数据相关问题的了解,很可能是以上第一种情况。如果查询频繁地全面扫描 很大的数据表,通过消除大段的数据,分区能够对这些查询有很好的影响。假设你有一个100万行的表, 其中有一个时间戳属性。你的查询要从这个表中获取一年的数据(其中有10年的数据)。查询使用了一个 全表扫描来获取这个数据。如果按时间戳分区,例如每个月一个分区,就可以只对1/10的数据进行全面扫 描(假设各年的数据是均匀分布的)。通过分区消除,90%的数据都可以不考虑。你的查询往往会运行得更 快。
现在,再来看如果 OLTP系统中有一个类似的表。在这种应用中,你肯定不会获取100万行表中10% 的数据,因此,尽管数据仓库中可以得到大幅的速度提升,但这种提升在事务性系统中得不到。不同系统 中做的工作是不一样的,所用不可能有同样的改进。因此,一般来说,在OLTP系统中达不到第一种情况(不 会是查询更快),你不会主要因为提供性能而应用分区。就算是要应用分区,也往往是为了提供可用性以及 得到管理上的易用性。但是需要指出,在一个OLTP系统中,即使是要确保达到第二点(也就是说,对查询 的性能没有影响,而不论是负面影响还是正面影响),也并非轻而易举,而需要付出努力。很多时候,你的 目标可能只是应用分区而不影响查询响应时间。
我发现,很多情况下实现团队会看到他们有一个很大的表,例如有1000万行。对现在来说,1000万 听上去像是一个难以置信的大数字(在5年或10年前,这实在是一个很大的数,但是时间可以改变一切)。 所以团队决定将数据分区。但是通过查看数据,却发现没有哪个属性可以用于区间分区(RANGE partitioning)。根本没有合适的属性来执行区间分区。同样,列表分区(LIST partitioning)也不可行。 这个表中没有什么能作为分区的“依据”。所以,实现团队想对主键执行散列分区,而主键恰好填充为一个 Oracle序号。看上去很完美,主键是惟一的,而且易于散列,另外很多查询都有以下形式:SELECT * FROM T WHERE PRIMARY_KEY = :X。
但问题是,对这个对象还有另外一些并非这种形式的查询,为了便于说明,假设当前表实际上是 ALL_OBJECTS字典视图,尽管在内部许多查询的形式都是WHERE OBJECT_ID = :X,但最终用户还会频繁地 对应用发出以下请求:
显 示 SCOTT 中 EMP 表 的 详 细 信 息 ( WHERE OWNER=:0 AND OBJECT_TYPE=:T AND OBJECT_NAME=:N)。
显示SCOTT所拥有的所有表(WHERE OWNER=:0 AND OBJECT_TYPE=:T)。 显示SCOTT所拥有的所有对象(WHERE OWNER=:0).

为了支持这些查询,在(OWNER.OBJECT_TYPE.OBJECT_NAME)上有一个索引。但是你听说“局部索引 更可用”,而你希望系统能更可用,所以将索引实现为局部索引。最后将表重建如下,它有16个散列分区:
ops$tkyte@ORA10G> create table t
2 ( OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID,
3 OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS,
4 TEMPORARY, GENERATED, SECONDARY )

5 partition by hash(object_id)
6 partitions 16
7 as
8 select * from all_objects; Table created.
ops$tkyte@ORA10G> create index t_idx
2 on t(owner,object_type,object_name)
3 LOCAL
4 /
Index created. ops$tkyte@ORA10G> begin
2 dbms_stats.gather_table_stats
3 ( user, 'T', cascade=>true);
4 end;
5 /
PL/SQL procedure successfully completed.

接下来执行经典的OLTP查询(你知道这些查询会频繁地运行):
variable o varchar2(30)
variable t varchar2(30)
variable n varchar2(30)
exec :o := 'SCOTT'; :t := 'TABLE'; :n := 'EMP';

select *
from t
where owner = :o
and object_type = :t and object_name = :n
/
select *
from t
where owner = :o
and object_type = :t
/
select *
from t
where owner = :o
/
但是可以注意到,如果SQL_TRACE=TRUE成立,运行以上代码时,查看所得到的TKPROF报告会有以下性能

特征:
select * from t where owner = :o and object_type = :t and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
-
total 4 0.00 0.00 0 34 0
1
Rows Row Source Operation
------- ---------------------------------------------------

1 PARTITION HASH ALL PARTITION: 1 16 (cr=34 pr=0 pw=0 time=359 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=34 pr=0
1 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0 time=250
与未实现分区的同一个表相比较,会发现以下结果:
select * from t where owner = :o and object_type = :t and object_name = :n
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
-
total 4 0.00 0.00 0 5 0
1
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=5 pr=0 pw=0 time=62 us)
1 INDEX RANGE SCAN T_IDX (cr=4 pr=0 pw=0 time=63 us)

你可能会立即得出(错误的)结论,分区会导致I/O 次数增加为未分区时的7 倍:未分区时只有 5

个查询模式获取,而有分区时却有34个。如果你的系统本身就存在一致获取过多的问题(以前是逻辑I/O), 现在情况就会更糟糕。就算是原来没有这个问题,现在也很可能会遭遇到它。对另外两个查询也可能观察 到同样的情况。在下面的输出中,整个第一行对应分区表,第二行则对应未分区表:
select * from t where owner = :o and object_type = :t
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
-
total 5 0.01 0.01 0 47 0
16
total 5 0.00 0.00 0 16 0
16
select * from t where owner = :o

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ---------
-
total 5 0.00 0.00 0 51 0
25
total 5 0.00 0.00 0 23 0
25
各个查询返回的答案是一样的,但是分别用了500%、300%或200%的 I/O,这可不太好。根本原因是
什么呢?就在于索引分区机制。注意在前面的计划中,最后一行所列的分区是1~16.
1 PARTITION HASH ALL PARTITION: 1 16 (cr=34 pr=0 pw=0 time=359 us)
1 TABLE ACCESS BY LOCAL INDEX ROWID T PARTITION: 1 16 (cr=34 pr=0
1 INDEX RANGE SCAN T_IDX PARTITION: 1 16 (cr=33 pr=0 pw=0 time=250

这个查询必须查看每一个索引分区,因为对应SCOTT 的条目可以(实际上也很可能)在每一个索引分
区中。索引按 OBJECT_ID 执行逻辑散列分区,所以如果查询使用了这个索引,但在谓词中没有引用 OBJECT_ID,所有这样的查询都必须考虑每一个索引分区!
解决方案是对索引执行全局分区。例如,继续看这个 T_IDX例子,可以选择对索引进行散列分区(这 是Oracle 10g的新特性):
注意 索引的散列分区是一个新的Oracle 10g特性,这在Oracle9i中是没有的。对于散列分区的索 引,有关区间扫描还有一些问题需要考虑,这一节后面就会讨论。
ops$tkyte@ORA10G> create index t_idx
2 on t(owner,object_type,object_name)
3 global
4 partition by hash(owner)
5 partitions 16
6 /
Index created.

与前面分析的散列分区表非常相似,Oracle会取OWNER值,将其散列到1~16之间的一个分区,并

把索引条目放在其中。现在,再次查看这3 个查询的TKPROF信息:
call count cpu elapsed disk query current rows

------- ------ -------- ---------- ---------- ---------- ---------- ---------
-
total 4 0.00 0.00 0 4 0
1
total 5 0.00 0.00 0 19 0
16
total 5 0.01 0.00 0 28 0
25
可以看到,与先前未分区表所执行的工作很相近,也就是说,我们不会负面地影响查询执行的工作。
不过,需要指出,散列分区索引无法执行区间扫描。一般来说,它最适于完全相等性比较(是否相等或是 否在列表中)。如果想使用前面的索引来查询WHERE OWNER > :X,就无法使用分区消除来执行一个简单的 区间扫描,你必须退回去检查全部的16个散列分区。
这是不是说分区对OLTP性能完全没有正面影响呢?不完全如此,要换个场合来看。一般来讲,对于 OLTP中的数据获取,分区确实没有正面的影响;相反,我们还必须小心地保证数据获取不要受到负面的影 响。但是对于高度并发环境中的数据修改,分区则可能提供显著的好处。
考虑一个相当简单的例子,有一个表,而且只有一个索引,在这个表中再增加一个主键。如果没有分 区,实际上这里只有一个表:所有插入都会插入到这个表中。对这个表的freelist可能存在竞争。另外, OBJECT_ID列上的主键索引是一个相当“重”的右侧索引,如第11章所讨论的那样。假设主键列由一个序 列来填充;因此,所有插入都会放到最右边的块中,这就会导致缓冲区等待。另外只有一个要竞争的索引 结构T_IDX。目前看来,“单个”的项目太多了(只有一个表,一个索引等)。
再来看分区的情况。按OBJECT_ID将表散列分区为16个分区。现在就会竞争16个“表”,而且只会 有1/16个“右侧”,每个索引结构只会接收以前1/16的工作负载,等等。也就是说,在一个高度并发环境 中可以使用分区来减少竞争,这与第11章使用反向键索引减少缓冲区忙等待是一样的。不过必须注意,与 没有分区相比,数据的分区处理本身会占用更多的CPU时间。也就是说,如果没有分区,数只有一个去处, 但有了分区后,则需要用更多的CPU时间来查明要把数据放在哪里。

因此,与以往一样,对系统应用分区来“提供性能”之前,先要确保自己真正了解系统需要什么。如 果系统目前是CPU密集的(占用大量CPU时间),但是 CPU的使用并不是因为竞争和闩等待,那么引入分区 并不能使问题好转,而只会让情况变得更糟糕!
使用ORDER BY
这 个例子引出一个关系不大但很重要的事实。查看散列分区索引时,可以发现另一个情况:使用索
引来获取数据时,并不会自动地获取有序的数据。有些人认为,如果 查询计划显示使用了一个索引来获取 数据,那么获取的数据就会是有序的。并不是这样的。要以某种有序顺序来获取数据,惟一的办法就是在 查询上使用ORDER BY。如果查询不包含ORDER BY语句,就不能对数据的有序顺序做任何假设。
可以用一个小例子来说明。我们创建了一个小表(ALL_USERS的一个副本),并创建一个散列分区索 引,在USER_ID列上有4 个分区:

ops$tkyte@ORA10G> create table t
2 as
3 select *
4 from all_users
5 /
Table created.
ops$tkyte@ORA10G> create index t_idx
2 on t(user_id)
3 global
4 partition by hash(user_id)
5 partitions 4
6 /
Index created.
现在,我们要查询这个表,这里使用了一个提示,要求Oracle使用这个索引。注意数据的顺序(实 际上,可以注意到数据是无序的):
ops$tkyte@ORA10G> set autotrace on explain ops$tkyte@ORA10G> select /*+ index( t t_idx ) */ user_id
2 from t
3 where user_id > 0
4 / USER_ID
----------

11
34
...
81
157
19
22
...
139
161
5
23
...
163
167
35
37
...
75
160
38 rows selected. Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=38 Bytes=494)

1 0 PARTITION HASH (ALL) (Cost=4 Card=38 Bytes=494)
2 1 INDEX (RANGE SCAN) OF 'T_IDX' (INDEX) (Cost=4 Card=38
Bytes=494)
ops$tkyte@ORA10G> set autotrace off
所以,即使Oracle在区间扫描中使用了索引,数据显然也不是有序的。实际上,可以观察到这个数 据存在一个模式。这里有“4个有序”的结果:……替代的都是按值递增的数据;在USER_ID=34和81的 行之间,输出的值是递增的。然后出现了一个USER_ID=19行。我们观察到的结果是:Oracle会从4个散 列分区一个接一个地返回“有序的数据”。
在此只是一个警告:除非你的查询中有一个ORDER BY,否则不要指望返回的数据会按某种顺序排序。
(另外,GROUP BY也不会执行排序!ORDER BY 是无可替代的)。