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 下一頁