熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

如何高效online創建大表的索引

2013-11-13 15:44:49  來源: Oracle 

  Microsoft Windows [版本 ]

  (C) 版權所有 Microsoft Corp

  C:\Documents and Settings\Administrator>systeminfo

  主機名:           ORACLETEST

  OS 名稱:          Microsoft(R) Windows(R) Server Enterprise Edition

  OS 版本:          Service Pack Build

  系統類型:         Xbased PC

  處理器:           安裝了 個處理器

  []: x Family Model Stepping GenuineIntel ~ Mhz

  []: x Family Model Stepping GenuineIntel ~ Mhz

  物理內存總量:     MB

  可用的物理內存:   MB

  頁面文件: 最大值: MB

  頁面文件: 可用:   MB

  頁面文件: 使用中: MB

  頁面文件位置:     c:\pagefilesys

  d:\pagefilesys

  e:\pagefilesys

  f:\pagefilesys

  環境其實就是一台很普通的PC機

  雙核的CPU+G內存

  G的表

  W row的量

  目的:測試如何以最快的方式online建立索引

  C:\Documents and Settings\Administrator>sqlplus sys@FUTEST_ as sysdba

  SQL*Plus: Release Production on 星期五 ::

  Copyright (c) Oracle All rights reserved

  輸入口令:

  連接到:

  Oracle Database g Enterprise Edition Release Production

  With the Partitioning OLAP Data Mining and Real Application Testing options

  SYS TEST SQL> set linesize

  SYS TEST SQL> set timing on

  SYS TEST SQL> set autot on

  SYS TEST SQL> alter session SET workarea_size_policy=MANUAL;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_retained_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set db_file_multiblock_read_count=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING;

  索引已創建

  已用時間: : :

  SYS TEST SQL>

  

  

  上面的測試用了分鐘

   對比下面的測試很暈

  設置的參數為

  sort_area_size=;

  sort_area_retained_size=;

  db_file_multiblock_read_count=;

  還有並行系數

  

  

  查看當前的並行狀況

  select decode(aQCSERIAL# null PARENT CHILD) stmt_level

  aSID

  aSERIAL#

  bUSERNAME

  bOSUSER

  bSQL_HASH_VALUE

  bSQL_ADDRESS

  aDEGREE

  aREQ_DEGREE

  from v$px_session a v$session b

  where aSID = bSID

  order by aQCSID stmt_level desc;

  C:\Documents and Settings\Administrator>sqlplus sys@futest_ as sysdba

  SQL*Plus: Release Production on 星期一 ::

  Copyright (c) Oracle All rights reserved

  輸入口令:

  連接到:

  Oracle Database g Enterprise Edition Release Production

  With the Partitioning OLAP Data Mining and Real Application Testing options

  SYS TEST SQL> set timing on

  SYS TEST SQL> set linesize on

  SP: linesize 選項的編號無效

  SYS TEST SQL> set linesize

  SYS TEST SQL> set autot on

  SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;

  索引已創建

  已用時間: : :

  什麼都不設置的情況下僅僅是online+nologging 才耗用了分鐘 當然 這是測試環境整個DB沒有模擬生產壓力的

  SYS TEST SQL> drop index IX_ALOTPRM_MTRL_LOT_ID;

  索引已刪除

  已用時間: : :

  SYS TEST SQL> alter session SET workarea_size_policy=MANUAL;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_retained_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set db_file_multiblock_read_count=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING;

  索引已創建

  已用時間: : :

  這次又用了分鐘

  sort_area_size=;

  sort_area_retained_size=;

  db_file_multiblock_read_count=;

  並行系數;

  這測試結果太不穩定了如果只要分鐘的話 !那直接用就是了還整這麼費勁干啥?

  咳咳多測試幾次吧刨根問底~

  SYS TEST SQL> drop index IX_ALOTPRM_MTRL_LOT_ID;

  索引已刪除

  我決定drop掉INDEX然後回收tablespace再測試一次

  SYS TEST SQL> select sum(bytes)/// sumbytes

   from dba_extents a

   where tablespace_name=USERS

   /

  SUMBYTES

  

  

  SYS TEST SQL> select nameround(to_number(bytes///)) as bytes from v$datafile;

  NAME                                                    BYTES

  

  E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_SYSTEM   

  _TJTPGN_DBF

  E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_UNDOTB    

  S_TJTPL_DBF

  E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_SYSAUX    

  _TJTPJ_DBF

  E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_USERS_        

  TJTPL_DBF

  NAME                                                    BYTES

  

  E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_EXAMPL    

  E_TJTRPY_DBF

  收縮之~

  SYS TEST SQL> ALTER DATABASE DATAFILE E:\ORACLE_DATA\FUTEST\FUTEST\DATAFILE\O_MF_USERS_TJTPL_DBF

   RESIZE M;

  數據庫已更改

  接著繼續~

  我想了想會不會與這個測試DB的內存分配有關系?

  SYS TEST SQL> show sga

  Total System Global Area bytes

  Fixed Size                  bytes

  Variable Size             bytes

  Database Buffers          bytes

  Redo Buffers                bytes

  SYS TEST SQL> show parameter pga

  NAME                                 TYPE        VALUE

  

  pga_aggregate_target                 big integer M

  SYS TEST SQL>

  把參數改小點

  M+ DB_file_multiblock_read_count+並行

  sort_area_size=;

  sort_area_retained_size=;

  db_file_multiblock_read_count=;

  還有並行系數

  把DB的內存分配得稍微大一點

  SYS TEST SQL> show sga

  Total System Global Area bytes

  Fixed Size                  bytes

  Variable Size             bytes

  Database Buffers          bytes

  Redo Buffers                bytes

  SYS TEST SQL> show parameter sga

  NAME                                 TYPE        VALUE

  

  lock_sga                             boolean     FALSE

  pre_page_sga                         boolean     TRUE

  sga_max_size                         big integer M

  sga_target                           big integer M

  SYS TEST SQL> show parameter pga

  SYS TEST SQL> CREATE INDEX FU_DBIX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) parallel(degree ) ONLINE NOLOGGING;

  索引已創建

  已用時間: : :

  搞來搞去還是整了分鐘

  再來一次不並行看看如何~

  sort_area_size=;

  sort_area_retained_size=;

  db_file_multiblock_read_count=;

  SYS TEST SQL> alter session SET workarea_size_policy=MANUAL;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set sort_area_retained_size=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> alter session set db_file_multiblock_read_count=;

  會話已更改

  已用時間: : :

  SYS TEST SQL> CREATE INDEX FU_DBIX_ALOTPRM_MTRL_LOT_ID on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;

  索引已創建

  已用時間: : :

  再多測一次再去吃飯

  刪索引

  回收tablespace

  總結一下

  我很暈了為什麼不並行反而更快?

  我估計和具體的場景 硬件條件 系統並發負載 還是有所聯系不然大牛們一直都說大表建索引必須

  session級別增大sort_area_size + nologging + 並行

  看樣子過段時間 找台 CPU的再試試吧

  條件                                                                                                   時間

  

  手動G+Multi block read count +並行

  workarea_size_policy=MANUAL;

  alter session set sort_area_size=;

  alter session set sort_area_retained_size=;                    : :

  alter session set db_file_multiblock_read_count=;

  parallel(degree ) ONLINE NOLOGGING;

  

  啥事不干直接建

  CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID

  on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;                 : :

  

  我手多又測試了一遍第一種情況

  workarea_size_policy=MANUAL;

  alter session set sort_area_size=;

  alter session set sort_area_retained_size=;                    : :

  alter session set db_file_multiblock_read_count=;

  parallel(degree ) ONLINE NOLOGGING;

  

  手動M+Multi block read count +並行

  workarea_size_policy=MANUAL;

  sort_area_size=;

  sort_area_retained_size=;                              : :

  db_file_multiblock_read_count=;

  parallel(degree ) ONLINE NOLOGGING;

  

  手動M+Multi block read count +無並行 試試

  workarea_size_policy=MANUAL;

  sort_area_size=;

  sort_area_retained_size=;                                          : :

  db_file_multiblock_read_count=;

  無並行

  

  再來一次手動M+Multi block read count +無並行

  workarea_size_policy=MANUAL;

  sort_area_size=;

  sort_area_retained_size=;                                          : :

  db_file_multiblock_read_count=;

  無並行

  

  再來一次啥事不干直接建

  CREATE INDEX IX_ALOTPRM_MTRL_LOT_ID

  on FU_DBALOTPRM(MTRL_LOT_ID) ONLINE NOLOGGING;                       : :

  

  若是不並行的話Process 裡會看到一個 CPU使用蠻高的TID而且只有一個核在激烈的WORK

  

  

  若是並行的話這裡會看到好幾個PID在work

  

  


From:http://tw.wingwit.com/Article/program/Oracle/201311/17238.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.