While working on the ITL deadlock problem (which looks like it’s been fixed by the initrans increase and table rebuild), the developers highlighted another table as hitting this problem in the past. When I investigated, I found that initrans had already been set to 6 so this had obviously happened in the past on the other table. I went back to development and explained that initrans was already set, but they highlighted the fact that the value of CHAIN_CNT was over 4 million on an 11 million row table. They questioned whether it had been reorganised. I suspected not, so I thought I’d put together a simple test case to look at the effects of alter table move. This is on version 9.2.0.7
SQL> create user test identified by test
2 default tablespace users
3 temporary tablespace temp;
User created.
SQL> grant connect, resource to test;
Grant succeeded.
SQL> alter user test quota unlimited on users;
User altered.
SQL> connect test/test
Connected.
SQL> create table test (long_string varchar2(2000));
Table created.
So I have a table and I want to force rows to be migrated into other blocks. There are tons of methods I could have used, but this quick and dirty approach worked. First a little PL/SQL block will insert 10,000 rows with short values in the long_string column.
SQL> begin
2 for ctr in 1 .. 10000 loop
3 insert into test values (to_char(ctr));
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
I’m going to analyze the table to see if there are any chained or migrated rows in the table. N.B. I have to use ANALYZE TABLE here because DBMS_STATS doesn’t populate the CHAIN_CNT column in dba_tables. We use DBMS_STATS exclusively to gather optimiser statistics, but the table under investigation had obviously been analysed at some point in the past, otherwise the CHAIN_CNT column wouldn’t have been populated. Anyway, let’s have a look at the results.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name, num_rows, chain_cnt from user_tables;
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEST 10000 0
There are no migrated rows at this point, but I can force rows to be migrated by increasing significantly the length of every newly-inserted row. I’ll use the LPAD function to increase the length to 500 characters.
SQL> update test set long_string = lpad(long_string, 500);
10000 rows updated.
SQL> commit;
Commit complete.
Now I need to check that rows have been migrated.
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name, num_rows, chain_cnt from user_tables;
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEST 10000 9983
The test table now contains lots of migrated rows, just like the production table that we’re investigating. If I use alter table move to rebuild the table and change initrans to 6 while I’m at it, there should be no more migrated rows but, more important for the purposes of my test, I can see how dba_tables looks afterwards.
SQL> alter table test move initrans 6;
Table altered.
SQL> select table_name, num_rows, chain_cnt from user_tables;
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEST
The ALTER TABLE MOVE command has cleared out the statistics for the table. This makes sense because the reorg invalidates the statistics to a large extent, but it does lead to a few important points
In our case, the table can’t have been rebuilt or the CHAIN_CNT value would be either null or 0, because we run DBMS_STATS exclusively. Well, at least we can’t tell if the table was rebuilt when initrans was increased to 6. We’re going to rebuild it, to be sure.
When rebuilding objects using ALTER TABLE MOVE, you need to remember to regather statistics using your normal procedures.
Remember that DBMS_STATS does not populate CHAIN_CNT properly, so you need to use ANALYZE. This is a bit frustrating and I’ll probably blog about that again later.
Finally I’ll check that there are no migrated rows remaining in the table now that it’s been rebuilt
SQL> analyze table test compute statistics;
Table analyzed.
SQL> select table_name, num_rows, chain_cnt from user_tables;
TABLE_NAME NUM_ROWS CHAIN_CNT
------------------------------ ---------- ----------
TEST 10000 0
Remember that moving a table can cause more of a mess than it solves. In your test case, you started with very small rows, and grew them to be very large.
Imagine the same effect in a live environment: after some time, you might have a years worth of data with long rows, and 2 week’s worth of data with short rows. When you move the table, what are you going to do with pctfree ?
Set it to a value that will allow new rows (and the existing short rows) to grow without chaining, and you need a large pctfree – which means any blocks with full-size rows will inevitably have a lot of unusable empty space. Set it to zero (to cater for the large rows that are not going to grow any more) and the current small rows are going to migrate very soon.
There’s a description in my (old 8i) book about using minimize_records_per_block before an insert from one table to another so that the large rows can be optimally packed whilst leaving enough space for the short rows to grow.
Jonathan,
“Remember that moving a table can cause more of a mess than it solves. “
Well, it would have been difficult to create more of a mess in the real-world example I faced because the batch job wouldn’t run at all so we had to reorganise at some level. We could have run it with fewer threads, but that would have needed more re-testing.
However, I understand that it could cause a bigger mess in the long run. That’s why the next step (and this also related to an earlier comment by Pete Scott) is to monitor what’s going on with this table more carefully for the next few months. This is still quite a new system and although you’d hope these issues would be picked up in volume testing, they weren’t 🙁
We don’t know yet whether these rows were originally small because of some initial data load activity or because that’s the normal profile of the insert/update activity, but this gives us some breathing space to find out. The fact that there is a value set for chain_cnt when we (supposedly) always use dbms_stats would indicate this schema has had an interesting history. Apparently this isn’t the first deadlock problem they’ve encountered.
I’ll have a read through the book again. I do remember that bit, though.
Cheers,
Doug
having a blond moment here… what does the alter move command do to the indexes? and what if you had a lot of indexes on a table? does it lock them while it executes?
Bill,
SQL> create index test_ix on test(long_string);
Index created.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
TEST_IX VALID
SQL> alter table test move;
Table altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——–
TEST_IX UNUSABLE
SQL> alter index test_ix rebuild online;
Index altered.
SQL> select index_name, status from user_indexes;
INDEX_NAME STATUS
—————————— ——-
TEST_IX VALID
Jonathan’s point was the very same one that made me choose to increase initrans over pctfree for interim fix of our problem. The real problem is that the application inserted a short row then updated it in the batch over doubleing the row length. But once fullsized the rows never shrink again. A big PCTFREE is wrong for the 99% of legacy data.
The real soultion is to get the devlopers to insert fullsized rows in the firstplace – guess the DBA could do it for them by supplying column defaults
I can’t help it: Shameless Plug No. 34 for
http://dizwell.com/main/content/view/89/196/
…which discusses row migration and chaining, shows ‘move’ fixing it… and then points out that moving an entire 11 million row table to fix up 4 million bits of chaining or migration is perhaps a case of sledgehammer and nut, and why the ‘analyze table list chained rows’ command is your friend.
I am also profoundly intrigued as to why your statistics on the table disappeared when you moved it, because that simply doesn’t normally happen (not in 10g Release 2 on Linux, at any rate):
Howard,
First off, sorry about the shitty code formatting in your comment. I hit the same problem and I’m still trying to get to the bottom of it (along with a number of other formatting issues). For now, I’ll fix it up so that it’s just about readable – hope that’s okay.
Second, I *almost* included a link to your article as it happens.
Third, I think it’s important to read this blog in context with the ITL deadlock blog. The point is that this will never wash as along term solution if this is an ongoing row migration problem, but we had to perform some sort of reorganisation to get enough ITL slots allocated to make the parallel batch job work.
Finally, I was profoundly intrigued by the lost stats, too, but there was no trickery or missing bits of code as far as I’m aware, and I played around with it for a bit, but I’ll try it again in some other environments myself.
Howard,
More later, because I’m at work, but I’ve just run an identical script on 9i and 10g at work and 10g behaves just as you illustrated, but 9i doesn’t. Weird.
Cheers
I see your blog’s handling of the BBCode “code” tags is not exactly stellar. Try this, then:
SQL> select num_rows, chain_cnt from dba_tables where table_name=’E’;
NUM_ROWS CHAIN_CNT
———- ———-
14 0
SQL> alter table e move initrans 6;
Table altered.
SQL> select num_rows, chain_cnt from dba_tables where table_name=’E’;
NUM_ROWS CHAIN_CNT
———- ———-
14 0
Ah, you beat me to it 😉
It’s taking a while to sort out this new blog. I think it’s going to be worth it though because there’s more flexibility, so I suppose the additional complexity is a given.