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

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

2022-06-13   來源: 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
    《如何高效online創建大表的索引》本文是由Oracle免費提供，內容來源於互聯網,本文歸原作者所有。
  • 上一篇文章：

  • 下一篇文章：
    • 相關文章
    主板 CPU 硬盤 內存 顯卡 聲卡 顯示器 光驅 死機
    推薦文章
    Copyright © 2005-2022 電腦知識網 Computer Knowledge   All rights reserved.