- 让Oracle跑得更快：Oracle 10g性能分析与优化思路
- 谭怀远
- 2267字
- 2020-08-27 03:48:59
1.2.2 OLAP 数据库
我一直认为OLAP数据库在内存上可优化的余地很小，甚至觉得增加CPU处理速度和磁盘I/O速度是最直接的提高数据库性能的方式，但这将意味着系统成本的增加。实际上，用户对OLAP系统性能的期望远远没有对OLTP性能的期望那么高。
内存的优化，对OLAP来讲影响很小，比如我曾经遇到的一个数据库，每天晚上运行的报表程序，基本上都是对几亿条或者几十亿条数据进行聚合处理，这种海量的数据，全部在内存中操作是很难的，同时也完全没有必要，因为这些数据块很少重用，缓存起来没有实际意义，倒是物理I/O相当大，这种系统的瓶颈往往是在磁盘I/O上面。
对于OLAP系统，SQL的优化显得非常重要，试想，如果一张表中只有几千条数据，无论执行全表扫描或是使用索引，对我们来说差异都很小，几乎感觉不出来，但是当数据量提升到几亿或者几十亿甚至更多的时候，全表扫描、索引可能导致极大的性能差异，因此SQL的优化显得重要起来。
看下面的一个例子，它对比了索引和全表扫描的效率：
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
select ＊
from
t where object_id＜100
call count cpu elapsed disk query current rows
---- ------ ---- -------- ----- ----- ------- -----
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.00 0.00 0 17 0 98
---- ------ ---- -------- ----- ----- ------- -----
total 10 0.01 0.00 0 17 0 98
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
98 TABLE ACCESS BY INDEX ROWID T (cr=17 pr=0 pw=0 time=95 us)
98 INDEX RANGE SCAN T_INX (cr=9 pr=0 pw=0 time=2383 us)(object id 51627)
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
select /＊+ full(t) ＊/ ＊
from
t where object_id＜100
call count cpu elapsed disk query current rows
---- ------ ------ -------- ------ ----- ------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 8 0.01 0.00 0 695 0 98
---- ------ ------ -------- ------ ----- ------- -----
total 10 0.01 0.01 0 695 0 98
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
98 TABLE ACCESS FULL T (cr=695 pr=0 pw=0 time=116 us)
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
我们看到，在这个只有几万条记录的表中，相同的SQL语句，全表扫描扫过的数据块（一致性读）是695个，而索引只扫过了17个，差别还是非常大的。
分区技术在OLAP数据库中很重要，这种重要主要体现在数据管理上，比如数据加载，可以通过分区交换的方式实现，备份可以通过备份分区表空间实现，删除数据可以通过分区进行删除；至于分区在性能上的影响，不能一概而论，认为分区的性能将始终好于非分区，这个结论是不成立的，至少是片面的，我们通过以下几种情况来分析它。
1. 当查询的范围正好落在某个分区的时候
这时候分区的效率自然是高于没有分区的，因为SQL在有分区的表上只扫过一个分区的数据，而对于没有分区，需要扫描整个表，这也是大多数人认为分区会提高性能的一个原因吧，比如下面的例子：
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
select count(＊)
from
t where x＜1000
call count cpu elapsed disk query current rows
---- ------ ------- -------- ---- ----- ------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 23 0 1
---- ------ ------- -------- ---- ----- ------- -----
total 4 0.00 0.00 0 23 0 1
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=23 pr=0 pw=0 time=2495 us)
999 PARTITION RANGE SINGLE PARTITION： 1 1 (cr=23 pr=0 pw=0 time=9085 us)
999 TABLE ACCESS FULL T PARTITION： 1 1 (cr=23 pr=0 pw=0 time=4077 us)
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
select count(＊)
from
t1 where x＜1000
call count cpu elapsed disk query current rows
---- ------ ------- -------- ---- ----- ------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.01 0.00 0 84 0 1
---- ------ ------- -------- ---- ----- ------- -----
total 4 0.01 0.01 0 84 0 1
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=84 pr=0 pw=0 time=9015 us)
999 TABLE ACCESS FULL T1 (cr=84 pr=0 pw=0 time=4077 us)
第一个SQL只扫过了一个分区的数据，扫过的数据块为23个；第二个SQL做了全表扫描，扫过的数据块为84个，这种情况下肯定是分区表的效率要高一些。
2. 当查询的范围跨越几个分区时
这时候分区可能并不绝对是最优的，比如下面的例子，我们把查询的范围扩大到分区表的13个分区，让CBO使用FAST INDEX FULL SCAN的方式扫描索引，另外我们创建另一张非分区表，表结果和数据同分区表完全一样，我们使用同一条SQL，并且也让CBO强制使用FAST INDEX FULL SCAN的方式访问非分区表上的全局索引。我们要验证的一个观点是，分区索引并不一定比全局索引在任何时候都快，有时候它反而会慢。下面是输入的结果：
Select /＊+ index_ffs(t t_ind) ＊/ count(＊)
from
t where x＜13000
call count cpu elapsed disk query current rows
---- ------ ------- -------- ---- ----- ------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 164 0 1
---- ------ ------- -------- ---- ----- ------- -----
total 4 0.03 0.03 0 164 0 1
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=164 pr=0 pw=0 time=29234 us)
12999 PARTITION RANGE ALL PARTITION： 1 13 (cr=164 pr=0 pw=0 time=117074
us)12999 INDEX FAST FULL SCAN T_IND PARTITION： 1 13 (cr=164 pr=0 pw=0
time=52408 us)(object id 51774)
select /＊+ index_ffs(t1 t1_ind) ＊/ count(＊)
from
t1 where x＜13000
call count cpu elapsed disk query current rows
---- ------ ------- -------- ---- ----- ------- -----
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.03 0.02 0 117 0 1
---- ------ ------- -------- ---- ----- ------- -----
total 4 0.03 0.02 0 117 0 1
Misses in library cache during parse： 1
Optimizer mode： ALL_ROWS
Parsing user id： 55
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=117 pr=0 pw=0 time=24755 us)
12999 INDEX FAST FULL SCAN T1_IND (cr=117 pr=0 pw=0 time=52082 us)(object
id 51788)
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
在这个例子里面，分区索引之所以扫过了更多的数据块，是因为分区索引在做FFS（INDEX FAST FULL SCAN）的时候只能够在本地索引上进行，如果涉及其他的分区，还需要按照访问索引的方式去访问其他索引（比如先找到其他分区索引的根数据块，再找到最左边的叶块，然后执行FFS操作），这样，查询跨过的分区越多，这种额外的代价就越大；而在这种情况下，全局索引只需要定位到一个叶块，然后执行一次FFS就能够扫过所有的索引叶块，这样性能就会好于分区索引。
上面的例子是想说明，OLAP环境中，分区主要的功能是管理上的方便性，它并不能绝对保证查询性能的提高，有时候分区会带来性能上的提高，有时候甚至会降低，就像我们在例子中看到的一样。