What do they mean?
Row chaining is what happens when a row cannot fit into a single Oracle block ever, because it is just too big. Oracle therefore has to break it up into several smaller row pieces, store each piece in a separate block, and link (or “chain”) from one block to another so that the entire set of pieces can be retrieved when you query the table for the row.
Row migration is what happens when a row grows in size and can no longer fit into its original Oracle block: Oracle will move it (or ‘migrate’ it) into a completely new block into which it can fit.
How to Find them?
Assuming you have latest stats for the tables, run following SQL to find out if you have any tables with high percentage of chained or migrated rows in a table.
SELECT owner, table_name, num_rows, chain_cnt,
round((chain_cnt * 100 / num_rows),2) pct, pct_free, pct_used
FROM dba_tables WHERE chain_cnt > 0
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY 5 desc ;
If you have any tables with large number of chained/migrated rows then follow the below steps to fix it,
How do I fix them?
1. connect to the database as the owner of the table having chained rows run utlchain.sql script which is in your ORACLE_HOME/rdbms/admin. It should create a table called 'chained_rows'.
2. Analyze the table suppose that the table having the issue is called: MBS
SQL>analyze table MBS list chained rows into chained_rows;
3. The above statement will load the chained row information into the table created in step 1
SQL>select * from chained_rows;
4. To fix the rows, put them in a temp table and remove it from original table and copy them from temp table to original table
create table my_tmp AS select * from mbs where rowid IN (select head_rowid from chained_rows where table_name = 'MBS');
DELETE FROM MBS WHERE rowid IN (select head_rowid from chained_rows where table_name = 'MBS');
INSERT INTO MBS SELECT * FROM my_tmp;
COMMIT;
Any Suggestion to avoid it?
Increase PCTFREE to avoid the future problem for those tables.
Subscribe to:
Post Comments (Atom)
Posts
9 comments:
Thank you, that was short and to the point. So much to remember, glad people like you make my job a bit more easier and enjoyable.
Thank you, that was short and to the point. So much to remember, glad people like you make my job a bit more easier and enjoyable.
Thank you for the appreciation! Pleasure is all mine :-)
Carefully disable (enabled triggers, constraints) before deleting rows of the table and enable after inserting. if not you might not be able to start the procedure at all or even have unexpected results by the enabled triggers (for example an insert-trigger might reactivated and affect other tables etc.)
I followed these instructions in 11.2.0.2 and the query still shows the same chained rows after following these steps. Am I missing something? I re-analyzed the tables but still get the same result.
Please check your row size and data block size. It happens when a row cannot fit into a single Oracle block ever, because it is just too big.
Good point P.Katsoulis!
Thanks for your help!
Very helpful. Thanks. Now I know what Toad is doing in the background to fix chained rows.
Post a Comment