csh的空间

我们一直在努力....

OLAP数据库

标签: OLAP 分区 索引 数据块

我一直认为OLAP数据库在内存上可优化的余地很小,甚至觉得增加CPU处理速度和磁盘I/O速度是最直接的提高数据库性能的方式,但这将意味着系统成本的增加。实际上,用户对OLAP系统性能的期望远远没有对OLTP性能的期望那么高。

内存的优化,对OLAP来讲影响很小,比如我曾经遇到的一个数据库,每天晚上运行的报表程序,基本上都是对几亿条或者几十亿条数据进行聚合处理,这种海量的数据,全部在内存中操作是很难的,同时也完全没有必要,因为这些数据块很少重用,缓存起来没有实际意义,倒是物理I/O相当大,这种系统的瓶颈往往是在磁盘I/O上面。

对于OLAP系统,SQL的优化显得非常重要,试想,如果一张表中只有几千条数据,无论执行全表扫描或是使用索引,对我们来说差异都很小,几乎感觉不出来,但是当数据量提升到几亿或者几十亿甚至更多的时候,全表扫描、索引可能导致极大的性能差异,因此SQL的优化显得重要起来。

看下面的一个例子,它对比了索引和全表扫描的效率:


  1. 		********************************************
    **************************  
  2. 		 
  3. 		select *   
  4. 		from  
  5. 		 t where object_id<100 
  6. 		 
  7. 		call    count       cpu     elapsed     disk   
    query   current     rows  
  8. 		----    ------      ----    --------    -----  
    -----   -------     -----  
  9. 		Parse       1      0.01       0.00          0    
    0         0          0  
  10. 		Execute     1      0.00       0.00          0      
    0         0          0  
  11. 		Fetch           8      0.00       0.00          0    
    17         0         98  
  12. 		----    ------      ----    --------    -----   ----- 
    -------     -----  
  13. 		total       10         0.01      0.00          0    
    17         0            98  
  14. 		 
  15. 		Misses in library cache during parse: 1  
  16. 		Optimizer mode: ALL_ROWS  
  17. 		Parsing user id: 55    
  18. 		 
  19. 		Rows     Row Source Operation  
  20. 		-------  ---------------------------------------------------  
  21. 		     98  TABLE ACCESS BY INDEX ROWID T (cr=17 
    pr=0 pw=0 time=95 us)  
  22. 		     98   INDEX RANGE SCAN T_INX (cr=9 pr=0 pw=0 
    time=2383 us)(object id 51627)  
  23. 		 
  24. 		**********************************************
    ************************  
  25. 		 
  26. 		select /*+ full(t) */ *   
  27. 		from  
  28. 		 t where object_id<100 
  29. 		 
  30. 		 
  31. 		call    count   cpu     elapsed     disk    query  
    current     rows  
  32. 		----    ------  ------  --------    ------  ----- 
    -------     -----  
  33. 		Parse           1   0.00       0.00         0       
    0           0             0  
  34. 		Execute     1   0.00       0.00         0         0  
    0         0  
  35. 		Fetch       8   0.01       0.00         0       695 
    0         98  
  36. 		----    ------  ------  --------    ------  -----   
    -------     -----  
  37. 		total      10   0.01       0.01         0       695 
    0         98  
  38. 		 
  39. 		Misses in library cache during parse: 1  
  40. 		Optimizer mode: ALL_ROWS  
  41. 		Parsing user id: 55    
  42. 		 
  43. 		Rows     Row Source Operation  
  44. 		-------  ---------------------------------------------------  
  45. 		     98  TABLE ACCESS FULL T (cr=695 pr=0 pw=0 time=116 us)  
  46. 		 
  47. 		************************************************
    ********************** 

我们看到,在这个只有几万条记录的表中,相同的SQL语句,全表扫描扫过的数据块(一致性读)是695个,而索引只扫过了17个,差别还是非常大的。

分区技术在OLAP数据库中很重要,这种重要主要体现在数据管理上,比如数据加载,可以通过分区交换的方式实现,备份可以通过备份分区表空间实现,删除数据可以通过分区进行删除;至于分区在性能上的影响,不能一概而论,认为分区的性能将始终好于非分区,这个结论是不成立的,至少是片面的,我们通过以下几种情况来分析它。

1. 当查询的范围正好落在某个分区的时候

这时候分区的效率自然是高于没有分区的,因为SQL在有分区的表上只扫过一个分区的数据,而对于没有分区,需要扫描整个表,这也是大多数人认为分区会提高性能的一个原因吧,比如下面的例子:


  1. 		**********************************************************************  
  2. 		 
  3. 		select count(*)   
  4. 		from  
  5. 		 t where x<1000 
  6. 		 
  7. 		 
  8. 		call    count       cpu     elapsed     disk   
    query   current     rows  
  9. 		----    ------  -------     --------    ----   
    -----   -------     -----  
  10. 		Parse       1      0.00       0.00          0 
    0          0           0  
  11. 		Execute     1      0.00       0.00          0 
    0          0           0  
  12. 		Fetch       2      0.00       0.00          0 
    23          0           1  
  13. 		----    ------  -------     --------    ----   
    -----   -------     -----  
  14. 		total        4     0.00       0.00          0   
    23          0           1  
  15. 		 
  16. 		Misses in library cache during parse: 1  
  17. 		Optimizer mode: ALL_ROWS  
  18. 		Parsing user id: 55    
  19. 		 
  20. 		Rows     Row Source Operation  
  21. 		-------  ---------------------------------------------------  
  22. 		      1  SORT AGGREGATE (cr=23 pr=0 pw=0 time=2495 us)  
  23. 		    999   PARTITION RANGE SINGLE PARTITION: 1 1 
    (cr=23 pr=0 pw=0 time=9085 us)  
  24. 		    999    TABLE ACCESS FULL T PARTITION: 1 1 
    (cr=23 pr=0 pw=0 time=4077 us)  
  25. 		 
  26. 		**********************************************
    ************************  
  27. 		 
  28. 		select count(*)   
  29. 		from  
  30. 		 t1 where x<1000 
  31. 		 
  32. 		 
  33. 		call    count       cpu     elapsed     disk   
    query   current     rows  
  34. 		----    ------  -------     --------    ----   
    -----   -------     -----  
  35. 		Parse       1      0.00       0.00          0    
    0          0           0  
  36. 		Execute     1      0.00       0.00          0      
    0          0           0  
  37. 		Fetch           2      0.01       0.00        
    0         84          0           1  
  38. 		----    ------  -------     --------    ----    
    -----   -------     -----  
  39. 		total        4      0.01       0.01          0  
    84          0           1  
  40. 		 
  41. 		Misses in library cache during parse: 1  
  42. 		Optimizer mode: ALL_ROWS  
  43. 		Parsing user id: 55    
  44. 		 
  45. 		Rows     Row Source Operation  
  46. 		-------  ---------------------------------------------------  
  47. 		      1  SORT AGGREGATE (cr=84 pr=0 pw=0 time=9015 us)  
  48. 		    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的方式访问非分区表上的全局索引。我们要验证的一个观点是,分区索引并不一定比全局索引在任何时候都快,有时候它反而会慢。下面是输入的结果:


  1. 		Select  /*+ index_ffs(t t_ind) */  count(*)   
  2. 		from  
  3. 		 t where x<13000 
  4. 		 
  5. 		 
  6. 		call    count       cpu     elapsed     disk   
    query   current     rows  
  7. 		----    ------  -------     --------    ----   
    -----   -------     -----  
  8. 		Parse       1      0.00       0.00          0  
    0          0           0  
  9. 		Execute     1      0.00       0.00          0   
    0          0           0  
  10. 		Fetch       2      0.03       0.02          0  
    164          0           1  
  11. 		----    ------  -------     --------    ----    
    -----   -------     -----  
  12. 		total        4      0.03       0.03          0  
    164          0           1  
  13. 		 
  14. 		Misses in library cache during parse: 1  
  15. 		Optimizer mode: ALL_ROWS  
  16. 		Parsing user id: 55    
  17. 		 
  18. 		Rows     Row Source Operation  
  19. 		-------  ---------------------------------------------------  
  20. 		      1  SORT AGGREGATE (cr=164 pr=0 pw=0 
    time=29234 us)  
  21. 		  12999   PARTITION RANGE ALL PARTITION: 1 13 
    (cr=164 pr=0 pw=0 time=117074 us)  
  22. 		  12999    INDEX FAST FULL SCAN T_IND PARTITION: 
    1 13 (cr=164 pr=0 pw=0 time=52408 us)(object id 51774)  
  23. 		 
  24. 		select /*+ index_ffs(t1 t1_ind) */ count(*)   
  25. 		from  
  26. 		 t1 where x<13000 
  27. 		 
  28. 		 
  29. 		call    count       cpu     elapsed     disk   
    query   current     rows  
  30. 		----    ------  -------     --------    ----   
    -----   -------     -----  
  31. 		Parse       1      0.00       0.00          0   
    0          0         0  
  32. 		Execute     1      0.00       0.00          0   
    0          0         0  
  33. 		Fetch       2      0.03       0.02          0   
    117          0         1  
  34. 		----    ------  -------     --------    ----    
    -----   -------     -----  
  35. 		total       4      0.03       0.02          0  
    117          0         1  
  36. 		 
  37. 		Misses in library cache during parse: 1  
  38. 		Optimizer mode: ALL_ROWS  
  39. 		Parsing user id: 55    
  40. 		 
  41. 		Rows     Row Source Operation  
  42. 		-------  ---------------------------------------------------  
  43. 		      1  SORT AGGREGATE (cr=117 pr=0 pw=0 time=24755 us)  
  44. 		  12999   INDEX FAST FULL SCAN T1_IND (cr=117 pr=0
     pw=0 time=52082 us)(object id 51788)  
  45. 		 
  46. 		*******************************************
    *************************** 

在这个例子里面,分区索引之所以扫过了更多的数据块,是因为分区索引在做FFS(INDEX FAST FULL SCAN)的时候只能够在本地索引上进行,如果涉及其他的分区,还需要按照访问索引的方式去访问其他索引(比如先找到其他分区索引的根数据块,再找到最左边的叶块,然后执行FFS操作),这样,查询跨过的分区越多,这种额外的代价就越大;而在这种情况下,全局索引只需要定位到一个叶块,然后执行一次FFS就能够扫过所有的索引叶块,这样性能就会好于分区索引。

上面的例子是想说明,OLAP环境中,分区主要的功能是管理上的方便性,它并不能绝对保证查询性能的提高,有时候分区会带来性能上的提高,有时候甚至会降低,就像我们在例子中看到的一样。


    评分: 请先登录再投票,同一篇博客一月只能投票一次!
    无人投票

相关博客:


评论


发表评论

关注此文的人们还关注