Oracle 41億資料量表建立索引記錄

背景

    生產系統一個流水錶，41億資料，有一列原先開發建立了bitmap index，由於該表為流水錶，有大量插入，alert日誌中一直報

dead lock，死鎖，由於點陣圖索引特殊性，即使在沒有任何約束情況下，由於該列的distinct值非常低，41億，只有170左右的distinct value，所以造成大量的dead lock，需要刪除bitmap index，改為global normal index。

    該表為按天分割槽。

建索引語句

alter session set workarea_size_policy=MANUAL;              
alter session set db_file_multiblock_read_count=512;
alter session set events ‘10351 trace name context forever, level 128’;
alter session set sort_area_size=2147483648;
alter session set “_sort_multiblock_read_count”=128;
alter session enable parallel ddl;
alter session enable parallel dml;
set timing on
create index idx_data_02 on data(xx) parallel 8 nologging [local];

大約耗時3個小時左右。

需要注意

temp表空間原先為60g，由於一開始開16個並行，所以導致報錯無法在temp擴充套件，臨時加大temp表空間到120g，順利建立索引。

參考

[oracle@rh2 ~]$ oerr ora 10351
10351, 00000, "size of slots"
// *Cause:
// *Action:  sets the size of slots to use
// *Comment: a slot is a unit of I/O and this factor controls the size
// *Comment: of the IO.
alter session set events '10351 trace name context forever, level 128';
level 128 -> direct path write max block 128
I generated a new run of the big testcase with event 10357, Patch 4417285
applied, manual workarea_size_policy, sort_area_size=50000000,
db_file_multiblock_read_count=16 and event 10351 with level 128.
I tried it with disk_asynch_io=TRUE and FALSE just to be certain this is not
something related to the async.
In the trace files I see something very peculiar. The slots size is 128 as
expected and I see many writes of 128 blocks but not all of them are and they
look like the they come in clusters. A few 128 writes, then a lot smaller of
different sizes but mainly less than 16 blocks and then another cluster of
big ones and so on.
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c990, sz=128, blks=1, st=3, idx=0
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca39, sz=128, blks=1, st=3, idx=4
but
it is possible that there are other factor out of our control that forces
Oracle to stop adding blocks to the slot and write small batches.
In conclusion, in order to have the least ammount of direct operations and
have the maximum possible read/write batches these are the parameters to set
:
alter session set events '10351 trace name context forever, level 128';
alter session set workarea_size_policy=manual;
alter session set sort_area_size=50000000;