Tuning idea:
------------------
1. I'm thinking to reorganize hot tables as well as their indexes, but by instinct it seems to not give so much value to avoid the huge row lock wait time.
2. I'm also seeing if I can reduce the number of rows per block, by increasing PCTFREE and diminishing PCTUSED, so the contention will spread over many blocks instead of one heavy block.
Question
As SQL stment related to those locked tables are select ... for update, how could I tune this kind of stment?
Does someone have other idea to come up with this row lock contention?
Ans
Increase the initrans value of that object appearing in the select statement.
------------------------------------------------------------------------------------------------------
But I have first to know how many initrans are allocated in the targetted table.
To do that, I need to do the following:
1. record one block number
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),COUNT(*) NUMBER_ROWS_PER_BLOCK FROM SIMANG_D.INWARD GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) Order by COUNT(*) desc
/
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) NUMBER_ROWS_PER_BLOCK
--------------------------------------------------------------------------------
---------------------
226534 5
226530 5
226504 5
226533 5
226538 5
226535 5
226525 5
226526 5
226532 5
228092 4
2. Dump the contents of a block through the following command
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 226525;
3. see the result
Block header dump: 0x010374dd
Object id on Block? Y
seg/obj: 0xd863 csc: 0x00.cafe8 itc: 169 flg: E typ: 1 – DATA (max itl=169) brn: 1 bdba: 0x10374d1 ver: 0x01 opc: 0
inc: 0 exflg: 0
from this example, the initrans value is 169.
So, I need first to have the data locally then, check the initrans value before change it.
furthermore as I know, the new initrans won't affect current block, but new allocated, so I have to Rebuild the table by changing the initrans value.
Question.
What is the overhead when dumping a contents of a block from the production database as I specified above?
------------------
1. I'm thinking to reorganize hot tables as well as their indexes, but by instinct it seems to not give so much value to avoid the huge row lock wait time.
2. I'm also seeing if I can reduce the number of rows per block, by increasing PCTFREE and diminishing PCTUSED, so the contention will spread over many blocks instead of one heavy block.
Question
As SQL stment related to those locked tables are select ... for update, how could I tune this kind of stment?
Does someone have other idea to come up with this row lock contention?
Ans
Increase the initrans value of that object appearing in the select statement.
------------------------------------------------------------------------------------------------------
But I have first to know how many initrans are allocated in the targetted table.
To do that, I need to do the following:
1. record one block number
SELECT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid),COUNT(*) NUMBER_ROWS_PER_BLOCK FROM SIMANG_D.INWARD GROUP BY DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) Order by COUNT(*) desc
/
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) NUMBER_ROWS_PER_BLOCK
--------------------------------------------------------------------------------
---------------------
226534 5
226530 5
226504 5
226533 5
226538 5
226535 5
226525 5
226526 5
226532 5
228092 4
2. Dump the contents of a block through the following command
ALTER SYSTEM DUMP DATAFILE 4 BLOCK 226525;
3. see the result
Block header dump: 0x010374dd
Object id on Block? Y
seg/obj: 0xd863 csc: 0x00.cafe8 itc: 169 flg: E typ: 1 – DATA (max itl=169) brn: 1 bdba: 0x10374d1 ver: 0x01 opc: 0
inc: 0 exflg: 0
from this example, the initrans value is 169.
So, I need first to have the data locally then, check the initrans value before change it.
furthermore as I know, the new initrans won't affect current block, but new allocated, so I have to Rebuild the table by changing the initrans value.
Question.
What is the overhead when dumping a contents of a block from the production database as I specified above?
No comments:
Post a Comment