Today I re-learned a nice option to overcome ORA-60 problems with parallel data loading scenarios.
As most of you will know (from reading note "84348 - Oracle-Deadlocks, ORA-00060" or Michael Braunsteins Blogs Chasing Oracle deadlocks part I - row level locks/Chasing Oracle deadlocks part II - block level locks or some other resource) the problem here is caused by Oracle space management on block level.
There is a specific block level storage setting that need to be changed (INI_TRANS) in order to guarantee multiple parallel transactions in a already used block.
The key point to this change is, that it's not sufficient to run the ALTER TABLE/ALTER INDEX command, since this will only change the setting for new blocks. To also cover the blocks already in use, the table/index needs to be reorganized or rebuild.
Usually this would have been a two step procedure:
1) change the INI_TRANS value
2) perform the reorg with BRSPACE
The thing I re-lerned just five minutes ago is:
BRSPACE has a switch for this - so you can do it all at once.
The following command will reorganize table "/BIC/FMYTESTCUBE" into the same tablespace and set the INI_TRANS parameters for the indexes (-SII) and for the table (-STI) to 20.
brspace -u / -f tbreorg -t "/BIC/FMYTESTCUBE" -STI 20 -SII 20
|
Nice, isn't it?
