Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to specify INITRANS for a partitioned LOB index?

I have a partitioned table with a BLOB column. When multiple rows per transaction is deleted from this table by multiple users, we often run into ORA-00060 deadlock-s, which, according the trc files, are caused by ITL deadlocks in the LOB index partitions. So I want to recreate the table with higher LOB index INITRANS than the default 2. I can do that for a non-partitioned table with create table ... ( ... lob (...) store as ... ( ... index ... (... initrans 10 ... ) ) ), but for the partitioned table Oracle says "ORA-22879: cannot use the LOB INDEX clause for partitioned tables". Is there any other way I can set INITRANS for a partitioned LOB index?

(Update: Note that I don't need to ALTER the existing table, I can recreate it from scratch. That's usually easier.)

jonearles's edit follows:

Here's an example of how the normal method does not work:

create table test1(a number, b clob)
partition by range (a) (partition p1 values less than (1));

select * from dba_indexes where owner = user and table_name = 'TEST1';

--ORA-22864: cannot ALTER or DROP LOB indexes
alter index SYS_IL0000111806C00002$$ initrans 3;

It's interesting to note that the index DDL looks really odd. The code below would not run - there's no expression in the index and the parenthesis is not even closed.

select dbms_metadata.get_ddl('INDEX', 'SYS_IL0000111806C00002$$') from dual;

  CREATE UNIQUE INDEX "JHELLER"."SYS_IL0000111806C00002$$" ON "JHELLER"."TEST1" (
  PCTFREE 10 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) LOCAL
 (PARTITION "SYS_IL_P19289" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE( INITIAL 65536 NEXT 1048576 MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" , 
 PARTITION "SYS_IL_P19290" 
  PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING 
  STORAGE( INITIAL 65536 NEXT 1048576 MAXEXTENTS 2147483645
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ) 
  PARALLEL (DEGREE 0 INSTANCES 0) 
like image 675
ddekany Avatar asked Nov 10 '22 04:11

ddekany


1 Answers

Oracle technical support said that I can't specify the INITRANS for a partitioned LOB index. However, they have also noticed that using SECUREFILE option together with DEDUPLICATE LOB option (which we did) might be the cause the problem. So I have given up the DEDUPLICATE LOB (just to be sure, replaced it with KEEP_DUPLICATES) and now the LOB index deadlocks are gone! (I have written a little stress test, and with that you can cause dozens of dead lock errors in minutes with DEDUPLICATE LOB, but none with KEEP_DUPLICATES.)

like image 171
ddekany Avatar answered Nov 15 '22 05:11

ddekany