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視圖。

 

[sql] view plaincopy 

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視圖

 

[sql] view plaincopy 

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：主要是並行查詢一起使用。

 

下面測試正式開始

 

1、使用python指令碼建立隨機數

 

import random

‘‘‘

Created on 2012-3-26

 

@author: jscn-xw

‘‘‘

for j in range(1,10):

   for i in range(1,10000000):

       print random.randint(100000000,999999999),random.randint(100000000,999999999)

 

2、建立測試表

SQL> create table tbim(id1 number,id2varchar2(12)) nologging;

 

3、load進入資料

3.1 建立控制檔案(tbim.ctl)

load data

--infile ‘/home/oracle/bi_logfile.txt‘

into table tbim

append

fields terminated by ‘ ‘

OPTIONALLY ENCLOSED BY ‘"‘

trailing nullcols

(

 id1       ,

  id2

)

3.2 sqlldr進入資料庫

[[email protected]]$ sqlldr userid=security/security control=tbim.ctldata=/home/oracle/tbim.bcp

 

4 測試

SQL> set timing on

SQL> select count(*) from tbim ;

 

 COUNT(*)

----------

 400000000

 

Elapsed: 00:00:06.57

4.1 什麼參數都不加測試建立速度

SQL> create index id1_ind on tbim(id1) tablespace imindex;

 

Index created.

 

Elapsed: 00:16:23.51

 

這個時候注意觀察暫存資料表空間的變化情況，我們注意臨時資料表空間在不斷的增加。還要注意v$session_longops視圖的變化。

主要關注SOFAR、TIME_REMAINING、ELAPSED_SECONDS欄位的變化和值

 

4.2 加上nologing參數

SQL> drop index id1_ind;

 

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging;

 

Index created.

 

Elapsed: 00:16:40.20

 

4.3 加上parallel參數

SQL> drop index id1_ind;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

 

Index created.

 

Elapsed: 00:09:03.74

 

感覺parallel不靠譜，而且nologging效果也不是很明顯，至少對於oracle11gR2來說。

 

4.4 調整sort_area_size

SQL> alter session setworkarea_size_policy=manual;

SQL> alter session setworkarea_size_policy=manual;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session setsort_area_size=2000000000;

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

Index created.

 

Elapsed: 00:08:12.79

這個效果還是比較明顯的

 

 

4.5 修改其他參數

修改全表掃描時一次讀取的block的數量db_file_multiblock_read_count

直接路徑IO的大小，10351 event level 128

禁用block checksum/checking

備選的排序演算法_newsort_type

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session set events ‘10351trace name context forever, level 128‘;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session set"_sort_multiblock_read_count"=128;

SQL> alter session set"_sort_multiblock_read_count"=128;

SQL> alter session enable parallel ddl;

SQL> alter session setdb_block_checking=false;

SQL> alter system setdb_block_checksum=false;

 

SQL> create index id1_ind on tbim(id1)tablespace imindex nologging parallel 4;

 

Index created.

 

Elapsed: 00:07:37.57

5、總結

我可以通過以下手段加快建立索引速度：

1）除此之外，還可以適當的調整並行查詢的數量（一般不超過8）；

2）索引和表分離，單獨的暫存資料表資料表空間；

3）把表調整為nologging狀態，或者建立索引的時候指定nologging；

4）我們可以適當調整資料庫相關參數加快左右建立索引速度，樣本如下：

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session setdb_file_multiblock_read_count=1024;

SQL> alter session set events ‘10351trace name context forever, level 128‘;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session setsort_area_size=2000000000;

SQL> alter session set"_sort_multiblock_read_count"=128;

SQL> alter session set "_sort_multiblock_read_count"=128;

SQL> alter session enable parallel ddl;

SQL> alter session setdb_block_checking=false;

SQL> alter system setdb_block_checksum=false;

 

通過以上調整一般可以加快40%以上的建立速度

oracle海量資料中提升建立索引的速度