Solved

Oracle index is growing uncontrollably... why?

Posted on 2011-09-28
18
787 Views
Last Modified: 2012-06-27
BACKGROUND

Oracle v11.2, Solaris 10, 64-bit, Locally managed tablespaces on regular old filesystem datafiles.

I have a lookup table that I refresh daily with a process like:

1. Populate a temporary table TEMP_TABLE with the new data

2. Delete the main table with 'delete from MAIN_TABLE;'

3. Copy in the new data with 'insert into MAIN_TABLE select * from TEMP_TABLE;'

4. commit;


The table itself is about 330M in size and has 4.2mil rows.  The reason for the delete/insert, instead of a truncate/drop-index/insert/create-index process is that the data MUST always be there, as either the old or the new set... but never as an empty set, or partial set.  Up to now we have avoided having two tables and flip-flopping with a synonym, but it may come to that if I can't sort this problem out.

PROBLEM

There are 5 indexes on this table, and they are constantly growing.  One of them ended up at 24G! (in spite of the table only being 330M)

I can reset the problem by rebuilding the indexes online, but that seems like only a temporary fix.

QUESTION

Can anyone explain why Oracle is keeping old index blocks when the entire table has been deleted?

Alternatively, is there a better process to use instead of delete/insert that will maintain my "always needed" requirement?
0
Comment
Question by:Sophia Paterakis
  • 9
  • 6
  • 2
  • +1
18 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Your answer on the size is explained here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1396006300346456969

"space in an index cannot be reused in the SAME transaction"

I'll see if I can think of a way around your 'always available' situation.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 450 total points
Comment Utility
Hi,

I see no reason for the index to be constantly growing if you insert the same data.
The index should remain with 50% free space because the deleted rows are not really freed until the transaction ends. exactely as if it were created with pctfree 50.

If data is different, you may have a pattern that lead to that behavior - even if I can't think of one of them... In order to investigate, you can analyze index validate structure and check index_stats after each run.

Regards,
Franck.
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
Thanks slightvw for the tip... and if the index grew to double something "normal" (say 200M x 2)... then I'd be OK with that.  The problem is that it grows each and every day, so I presume that the empty blocks from yesterday don't seem to get re-used tomorrow, even though they are separated by two transactions.

Our index seems to just grow, and grow, and grow...  I was shocked when I found it at over 24,000M, compared to the 330M base table.

Oracle said:

Truncate and then re-insert.... <me>Ummmm, I can't as I always need the data there, like I said in the SR. </me> :-(

OK, then that's just the way it works... old empty blocks won't get re-used even when you delete EVERY ROW, so your only option is to drop/create the indexes.

Personally I've been burnt by a lot of Oracle-ism's over the years, but even I find this completely out-of-whack.  I'd verge on calling this a bug, but I'd like to understand why Oracle claims it's working as designed before I press this further.

This quote from the AskTom article you linked to seems to support my theory, that completely empty leaf blocks should migrate to the right and be re-used:

Followup   March 3, 2009 - 9am Central time zone:

do you delete MOST but not ALL of the old rows? If so, you might need to coalesce that index.

do you delete large range of contigous rows? for example: where id between 100 and 10000. Then the index will take care of itself over time. You would expect to see deleted leaf rows after a delete, but the subsequent inserts will cause fully empty leaf blocks to move over from the left to the right and get reused over time.
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
Hi Franck,  I am analyzing our Test database (indexes rebuilt) and our Dev database (original 24G indexes) now and I'll post the data here when it finishes.
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 50 total points
Comment Utility
>>empty blocks from yesterday don't seem to get re-used tomorrow,

Good point.  I missed that.  I'll see if I can find anything that might explain this.

I agree that it is likely a bug.

>>why Oracle claims it's working as designed

Keep pressing.  I opened an SR a week ago and am getting a run-around as well.  Maybe there was some major shakeup in their Support organization?
0
 
LVL 15

Expert Comment

by:Franck Pachot
Comment Utility
what is strange is that you say that all indexes are in this case, so it seems that it does not depend on a specific data pattern. I don't know if it is a bug, but i'm sure it is not the normal behaviour, or refreshing materialized view would have that issue.

Maybe you can provide an index treedump and a few index block dump (see http://dioncho.wordpress.com/2009/06/24/simple-formatting-on-index-tree-dump/)
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
I managed to get the analyze's done.  The first one is from Test where I rebuilt the index 2 weeks ago, an the second one is from Dev which hasn't been touched.  I'm guessing the main problem is the PCT_USED in DEV has dropped to 11%.

I'll start the other analysis that you have requested, and post the result when it's available:

HEIGHT                 BLOCKS                 NAME                           PARTITION_NAME                 LF_ROWS                LF_BLKS                LF_ROWS_LEN            LF_BLK_LEN             BR_ROWS                BR_BLKS                BR_ROWS_LEN            BR_BLK_LEN             DEL_LF_ROWS            DEL_LF_ROWS_LEN        DISTINCT_KEYS          MOST_REPEATED_KEY      BTREE_SPACE            USED_SPACE             PCT_USED               ROWS_PER_KEY           BLKS_GETS_PER_ACCESS   PRE_ROWS               PRE_ROWS_LEN           OPT_CMPR_COUNT         OPT_CMPR_PCTSAVE       
---------------------- ---------------------- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
3                      65280                  MY_INDEX                                                      11839991               64380                  315471837              7996                   64379                  316                    1692554                8028                   7759196                206948577              1080025                14678                  517319328              317164391              62                     10.9627008634059396773222842063841114789 8.98135043170296983866114210319205573945 0                      0                      5                      51                     

HEIGHT                 BLOCKS                 NAME                           PARTITION_NAME                 LF_ROWS                LF_BLKS                LF_ROWS_LEN            LF_BLK_LEN             BR_ROWS                BR_BLKS                BR_ROWS_LEN            BR_BLK_LEN             DEL_LF_ROWS            DEL_LF_ROWS_LEN        DISTINCT_KEYS          MOST_REPEATED_KEY      BTREE_SPACE            USED_SPACE             PCT_USED               ROWS_PER_KEY           BLKS_GETS_PER_ACCESS   PRE_ROWS               PRE_ROWS_LEN           OPT_CMPR_COUNT         OPT_CMPR_PCTSAVE       
---------------------- ---------------------- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
4                      3020800                MY_INDEX                                                      90127027               3004458                2395819059             7996                   3004457                16131                  82016996               8028                   86070265               2287946447             1548532                57572                  24153145836            2477836055             11                     58.20159157188873074628099387032363554644 33.60079578594436537314049693516181777322 0                      0                      5                      55                     

Open in new window

0
 
LVL 15

Expert Comment

by:Franck Pachot
Comment Utility
Hi, the idea was to compare analyze result before and after a new refresh.
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
There are many topics in your question.

1. Often Unix doesn't show correctly freed files. Restart needed. You can find details in the net.

2. Clearing the unused blocks is a duty of SMON. But it doesn't activate properly for unknown reason (it has to wake every 5 minutes). You can do this manually using some "trace event". This is also explained in the net

3. Oracle uses so called "lazy" or "logical" delete in the index structure. So many not used entries are simply marked as "unused", but physically the stay on the disk. To get rid of them the best way is to recreate the index.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
After a few overnight batch jobs one specific index I listed above has gone from 330M to 648M.  Using the previous numbers from the run just after the rebuild, vs. the analyze from today we get:

ANALYZE INDEX MY_INDEX VALIDATE STRUCTURE OFFLINE;
select * from INDEX_STATS WHERE NAME = 'MY_INDEX';

Open in new window


HEIGHT                 BLOCKS                 NAME                           PARTITION_NAME                 LF_ROWS                LF_BLKS                LF_ROWS_LEN            LF_BLK_LEN             BR_ROWS                BR_BLKS                BR_ROWS_LEN            BR_BLK_LEN             DEL_LF_ROWS            DEL_LF_ROWS_LEN        DISTINCT_KEYS          MOST_REPEATED_KEY      BTREE_SPACE            USED_SPACE             PCT_USED               ROWS_PER_KEY           BLKS_GETS_PER_ACCESS   PRE_ROWS               PRE_ROWS_LEN           OPT_CMPR_COUNT         OPT_CMPR_PCTSAVE       
---------------------- ---------------------- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
3                      65280                  MY_INDEX                                                      11839991               64380                  315471837              7996                   64379                  316                    1692554                8028                   7759196                206948577              1080025                14678                  517319328              317164391              62                     10.9627008634059396773222842063841114789 8.98135043170296983866114210319205573945 0                      0                      5                      51                     

HEIGHT                 BLOCKS                 NAME                           PARTITION_NAME                 LF_ROWS                LF_BLKS                LF_ROWS_LEN            LF_BLK_LEN             BR_ROWS                BR_BLKS                BR_ROWS_LEN            BR_BLK_LEN             DEL_LF_ROWS            DEL_LF_ROWS_LEN        DISTINCT_KEYS          MOST_REPEATED_KEY      BTREE_SPACE            USED_SPACE             PCT_USED               ROWS_PER_KEY           BLKS_GETS_PER_ACCESS   PRE_ROWS               PRE_ROWS_LEN           OPT_CMPR_COUNT         OPT_CMPR_PCTSAVE       
---------------------- ---------------------- ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
4                      84992                  MY_INDEX                                                      14513430               84344                  387435775              7996                   84343                  415                    2238670                8028                   10437238               279034103              1026867                14678                  677746244              389674445              58                     14.1336998851847415488081708731510507203 11.56684994259237077440408543657552536015 0                      0                      5                      52                     

Open in new window



franckpachot - I've looked at dumping some of the index tree, but found the link a little confusing.  I'll keep working on that to get you the info you need to help.


schwertner - To address your points:

1. The space I'm worried about is being reported from DBA_SEGMENTS, not Unix.

2. I don't think these are being treated as unused blocks, as they are still a part of the index, just marked as empty. They should be available for re-use during any index operation that needs a new block, but apparently they are being abandoned.

3. Lazy delete makes sense, but what confuses me is why my index keeps growing after nightly delete/insert cycles.  Shouldn't those blocks marked as deleted but still in the index get re-used?
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
Attached are the index treedumps you wanted... and I think having this row repeated a LOT in the index could be the sign of something amiss:

         leaf: 0x352cc0a 55757834 (64: nrow: 0 rrow: 0)
         leaf: 0x352cb1a 55757594 (65: nrow: 0 rrow: 0)
         leaf: 0x352ca7d 55757437 (66: nrow: 0 rrow: 0)
         leaf: 0x352ca7c 55757436 (67: nrow: 0 rrow: 0)
         leaf: 0x352ca7b 55757435 (68: nrow: 163 rrow: 0)
         leaf: 0x352e0ef 55763183 (69: nrow: 0 rrow: 0)
         leaf: 0x352e0f0 55763184 (70: nrow: 0 rrow: 0)
         leaf: 0x352e0f1 55763185 (71: nrow: 0 rrow: 0)
         leaf: 0x352e0f2 55763186 (72: nrow: 0 rrow: 0)
         leaf: 0x352e0f3 55763187 (73: nrow: 0 rrow: 0)
         leaf: 0x352e0f4 55763188 (74: nrow: 0 rrow: 0)
         leaf: 0x352e0f5 55763189 (75: nrow: 0 rrow: 0)
         leaf: 0x352e0f6 55763190 (76: nrow: 0 rrow: 0)
         leaf: 0x352e0f7 55763191 (77: nrow: 0 rrow: 0)
         leaf: 0x352e0f8 55763192 (78: nrow: 0 rrow: 0)
         leaf: 0x352e0f9 55763193 (79: nrow: 0 rrow: 0)
         leaf: 0x352e0fa 55763194 (80: nrow: 0 rrow: 0)
         leaf: 0x352e0fb 55763195 (81: nrow: 0 rrow: 0)
         leaf: 0x352e0fc 55763196 (82: nrow: 0 rrow: 0)
         leaf: 0x352e0fd 55763197 (83: nrow: 0 rrow: 0)
         leaf: 0x352e0fe 55763198 (84: nrow: 0 rrow: 0)
         leaf: 0x352e0ff 55763199 (85: nrow: 0 rrow: 0)
         leaf: 0x352e100 55763200 (86: nrow: 0 rrow: 0)

Open in new window


That means I've got loads of totally empty leaf blocks, right?  Shouldn't they get re-assigned to the right-side of the B-Tree when I insert new rows?

MYDATABASE.before-rebuild.txt
MYDATABASE.after-rebuild.txt
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
Oh, one interesting titbit, this table has an ID column that's populated by a sequence.  A while back the sequence incremented past 2^32, which broke the Java that reads from it.  I fixed this by resetting the sequence back down to 1, and the next cron run deleted the high numbers and re-populated the table starting at 1, 2, 3, etc.

This does mean that the index has, at some time in the dim-dark past, already had all of these ID rows in this table before... but it was at least a year before the sequence was reset.  The index itself doesn't include the ID column, and whilst the other 4 indexes on the table grew a bit, it's only this one index that is GROSSLY out of proportion.
0
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 450 total points
Comment Utility
Hi,

From the treedump, we see that before rebuild there are a lot of leaf blocks that are nearly empty, but not fully empty. so those blocs can only be used by values that are in the same range. Only fully empty blocks can be reused for other values (i.e allocated elsewhere in the index tree). Coalesce should be a better solution than rebuild then: it will pack those blocks into fewer ones. And it can be done online.

Because of the sequence reset, an index starting with ID will not be as optimal as before. This is because adding a value that is greater than all the others will go at the end of the index. When inserting increasing values in the middle of the index does 50/50 block splits when space is needed, and this leaves half empty blocks.

Doing an alter index coalesce at the end of the refresh should be a good solution. But you will have to do it each time, because when all is packed, the refresh will double the size.

Or you may prefer to rebuild the index once with PCTFREE=50 or even a bit less (if there is more rows at each refresh) so the index will always allocate twice the size of real data - needed during the transactional refresh, but should not increase after that.

Regards,
Franck.
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
Thanks for the tips.  I will attempt a coalesce and see what that does to the index, but I did think that:

nrow: 0,  rrow: 0

meant that it was a totally empty leaf block with 0 index entries (nrow), and 0 deleted entries (rrow).  Can you tell me how you determined they were nearly empty, but not fully empty?  I thought with a full delete/insert each night, that either the leaf blocks would be almost full, or at worst 50/50.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 450 total points
Comment Utility
>>  Can you tell me how you determined they were nearly empty, but not fully empty?
Some of them were nearly empty. But you're right, those with (nrow: 0,  rrow: 0) are totally empty but not yet delallocated from the tree structure. Only a new update on the block can deallocate them, or a coalesce.
>>  I thought with a full delete/insert each night, that either the leaf blocks would be almost full, or at worst 50/50
That's right, if the new rows have the same values as the previous ones, and because until the end of the transaction both previous and new must be there, at the end you will have 50/50. With your refresh method, you can't expect better than 50/50. So either you accept 50/50 or you coalesce each time.

But the point from the beginning is that you said that it increases each time you refresh. If you delete and insert the same data, then it should stay at 50/50. But it seems that the behavior is different here.
0
 
LVL 1

Author Comment

by:Sophia Paterakis
Comment Utility
That all makes sense, but from what I can see most (> 90%) of the blocks are empty.  Here is a quick scan on the truncated trace file, but as a random sample it's pretty damning:

$ grep 'nrow: 0 rrow: 0)' MYDATABASE_ora_27519.trc | wc -l
81658
$ wc -l MYDATABASE_ora_27519.trc
92814

I'd be very happy if it stayed somewhat static at 50/50, but as you can see it doesn't. If the solution is "Oracle shouldn't do that, log a bug"... then I'm OK with that, and will press further with my stalled SR.
0
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 450 total points
Comment Utility
I can't say that "Oracle shouldn't do that, log a bug" because I don't know exactly your data pattern (new values vs previous values) .
 If you have that growing behavior (further than the first time) when inserting exactly the same values, then yes it is probably a bug. If its with different values, then there can be some rare patterns where space is not re-used.
But coalesce should work and it is not something that is too 'weird' for you refresh strategy: allocate more space for atomicity purpose, and then coalse. All done online, always showing consistent data.
0
 
LVL 1

Author Closing Comment

by:Sophia Paterakis
Comment Utility
Thanks for all of your help on a sticky problem.  Rather than do an extended analysis here I'll push it back to Oracle Support, and see what they can make of it.

As a bonus I've confirmed my understanding of Index leaf blocks, and learnt about tree dumping.

Cheers.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now