Oracle海量数据加快创建索引速度


基本信息情况：

数据库版本：Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

操作系统版本：CentOS release 5.6

加快创建索引速度主要从一下角度考虑：

  • 使用nologging 参数
  • 使用parallel 并行参数
  • 在session级别使用manual pga，手动调整sort_area_size
  • 修改其他参数

注意：我们这里不手动调整hash_area_size，hash_area_size 默认情况下会自动根据sort_area_size*2来调，导致sort_area_size不能超过1G。所以我们这里直接调整sort_area_size参数。

这里首先记录pga使用的情况，9i以后查询pga分配和使用可以查询v$pgastat视图。

  1. SQL> desc v$pgastat;  
  2. 名称   
  3. --------------------------------   
  4. NAME 名称   
  5. VALUE 值   
  6. UNIT 单位   
  7. -------------------统计项    
  8. select * from v$pgastat  
  9. NAME                                          VALUE UNIT  
  10. ---------------------------------------- ---------- ----------   
  11. aggregate PGA target parameter            150994944 bytes   
  12. aggregate PGA auto target                  93579264 bytes  
  13. global memory bound                        30198784 bytes  
  14. total PGA inuse                            47017984 bytes  
  15. total PGA allocated                        56666112 bytes  
  16. maximum PGA allocated                      58632192 bytes  
  17. total freeable PGA memory                   2883584 bytes  
  18. process count                                    23  
  19. max processes count                              48  
  20. PGA memory freed back to OS                 5177344 bytes  
  21. total PGA used for auto workareas                 0 bytes  
  22. maximum PGA used for auto workareas               0 bytes  
  23. total PGA used for manual workareas               0 bytes  
  24. maximum PGA used for manual workareas             0 bytes  
  25. over allocation count                             0  
  26. bytes processed                             6438912 bytes  
  27. extra bytes read/written                          0 bytes  
  28. cache hit percentage                            100 percent  
  29. recompute count (total)                         123  

对于上面的解释如下

1 aggregate PGA target parameter 150994944 bytes ： pga_aggregate_target
2 aggregate PGA auto target 93579264 bytes : 剩余的能被工作区使用的内存。
3 global memory bound 30198784 bytes :单个SQL最大能用到的内存
4 total PGA inuse 47017984 bytes ：正被耗用的pga(包括workare pl/sql等所有占用的pga)
5 total PGA allocated 56666112 bytes ：当前实例已分配的PGA内存总量。
一般来说，这个值应该小于 PGA_AGGREGATE_TARGET ，
但是如果进程需求的PGA快速增长，它可以在超过PGA_AGGREGATE_TARGET的限定值
6 maximum PGA allocated 58632192 bytes :pga曾经扩张到的最大值
7 total freeable PGA memory 2883584 bytes ：可释放的pga
8 process count 23 :当前process
9 max processes count 48 :最大时候的process
10 PGA memory freed back to OS 5177344 bytes
11 total PGA used for auto workareas 0 bytes :当前auto模式下占用的workara size 大小
12 maximum PGA used for auto workareas 0 bytes :auto模式下占用的workara size最大 大小
13 total PGA used for manual workareas 0 bytes ：当前manual模式下占用的workara size 大小
14 maximum PGA used for manual workareas 0 bytes :manual模式下占用的workara size最大 大小
15 over allocation count 0 :使用量超过pga大小的次数
16 bytes processed 6438912 bytes :pga使用的字节
17 extra bytes read/written 0 bytes ：向临时段写的字节
18 cache hit percentage 100 percent ：bytes processed/(bytes processed+extra bytes read/written)
19 recompute count (total) 123

global memory bound:一个串行操作能用到的最大内存
=min(5%*pga_aggregate_target,50%*_pga_max_size,_smm_max_size)，
当你修改参数pga_aggregate_target的值时，Oracle系统会根据pga_aggregate_target和_pga_max_size
这两个值来自动修改参数_smm_max_size。具体修改的规则是：
如果_pga_max_size大于5%*pga_aggregate_target，则_smm_max_size为5%*pga_aggregate_target。
如果_pga_max_size小于等于5%*pga_aggregate_target，则_smm_max_size为50%*_pga_max_size。

total PGA in used:当前正在使用的PGA，可以从v$process的pga_used_mem字段中获取
select sum(a.PGA_USED_MEM),sum(a.PGA_ALLOC_MEM),sum(a.PGA_MAX_MEM) from v$process a
v$pgastat 中的 total PGA in used、total PGA allocated、maximum PGA allocated
这3个值差不多


在执行创建索引前，我们还要介绍一个视图v$session_longops视图

  1. SQL> desc v$session_longops  
  2. 名称 是否为空? 类型  
  3. ----------------------------------------- -------- ----------------   
  4.   
  5. SID NUMBER  
  6. SERIAL# NUMBER  
  7. OPNAME VARCHAR2(64)  
  8. TARGET VARCHAR2(64)  
  9. TARGET_DESC VARCHAR2(32)  
  10. SOFAR NUMBER  
  11. TOTALWORK NUMBER  
  12. UNITS VARCHAR2(32)  
  13. START_TIME DATE  
  14. LAST_UPDATE_TIME DATE  
  15. TIME_REMAINING NUMBER  
  16. ELAPSED_SECONDS NUMBER  
  17. CONTEXT NUMBER  
  18. MESSAGE VARCHAR2(512)  
  19. USERNAME VARCHAR2(30)  
  20. SQL_ADDRESS RAW(4)  
  21. SQL_HASH_VALUE NUMBER  
  22. QCSID NUMBER  

其中SID和SERIAL#是与v$session中的匹配的，
OPNAME：指长时间执行的操作名.如：Table Scan
TARGET：被操作的object_name. 如：tableA
TARGET_DESC：描述target的内容
SOFAR：这个是需要着重去关注的，表示已要完成的工作数，如扫描了多少个块。
TOTALWORK：指目标对象一共有多少数量（预计）。如块的数量。
UNITS：
START_TIME：进程的开始时间
LAST_UPDATE_TIM：最后一次调用set_session_longops的时间
TIME_REMAINING： 估计还需要多少时间完成，单位为秒
ELAPSED_SECONDS：指从开始操作时间到最后更新时间
CONTEXT：
MESSAGE：对于操作的完整描述，包括进度和操作内容。
USERNAME：与v$session中的一样。
SQL_ADDRESS：关联v$sql
SQL_HASH_VALUE：关联v$sql
QCSID：主要是并行查询一起使用。

更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12

  • 1
  • 2
  • 3
  • 下一页

相关内容