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.
|
Nice, isn't it?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
34 | |
25 | |
12 | |
7 | |
7 | |
6 | |
6 | |
6 | |
5 | |
4 |