Table reorgs and statistics

Doug's Oracle Blog

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

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.

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.

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.

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.

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

alter table ... move and table stats
Howard Rogers left a comment on my last blog, showing an example of using alter table ... move on a 10gR2 database on Linux. In his example, unlike mine, the table rebuild did not nullify the table's statistics. I admit I was surprised myself when I ran

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.

"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.

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

...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):

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.

Disclaimer

For the avoidance of any doubt, all views expressed here are my own and not those of past or current employers, clients, friends, Oracle Corporation, my Mum or, indeed, Flatcat. If you want to sue someone, I suggest you pick on Tigger, but I hope you have a good lawyer. Frankly, I doubt any of the former agree with my views or would want to be associated with them in any way.