RE: dbms_space.space_usage and effectiveness of purge operations

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Tue, 5 Nov 2013 14:23:58 -0500
Message-ID: <04a301ceda5c$9477f920$bd67eb60$_at_rsiz.com>



You're probably right since the index name does not start with oe_. (I just had E-biz on the brain.)  

I do repeat my question about what his goal is. Densely packed leaf blocks not being a good thing for OLTP near random index insertion, I *hope* densely packing leaf blocks is not his goal.  

Perhaps your nice explanation of the usual utilization pattern will aid understanding on that point: you definitely don't want to gratuitously increase the frequency of leaf block splits to save a little space.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, November 05, 2013 1:30 PM To: mwf_at_rsiz.com; stalinsk_at_gmail.com; 'oracle-l' Subject: RE: dbms_space.space_usage and effectiveness of purge operations    

Mark,  

I read that as table order_line, column item_id, foreign key to products - and therefore assumed it wasn't monotonic. The pattern I would expect, based on the classic "popularity" profile of items (a few very popular, many quite popular, and a long tail of less popular) would be that for one day a few leaf blocks for the very popular items might become empty, a couple of leaf blocks for the popular become empty, and a little more space gets freed in leaf blocks that hold less popular items.  

As a side effect of the way Oracle does 50/50 splits, I'd expect the index leaf blocks of the very popular items to run at a fairly steady 50% utilisation most of the time, though, which doesn't match the figures Stalin showed - so I'm a little wary of assuming that the classic pattern fits this case.    

Regards

Jonathan Lewis    


From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Mark W. Farnham [mwf_at_rsiz.com] Sent: 05 November 2013 18:21
To: stalinsk_at_gmail.com; 'oracle-l'
Subject: RE: dbms_space.space_usage and effectiveness of purge operations

I don't quite understand your abbreviations.  

One thing I see immediately that seems incorrect is your expectation that leaf block deletions would be "even" when you are doing a time based purge on an index that is highly correlated with time. (order_line_item_id sequence was monotonically increasing last time I looked, if you're talking E-biz.) Also, that should be the exact opposite of an obstacle: entire leaf blocks should free nearly together because of that correlation. Remember that leaf blocks of a regular index are going to be storing index column set values that are very near to each other, sort of like a page of a telephone book if the index were on lastname, firstname.  

What, exactly, is your goal?  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stalin
Sent: Tuesday, November 05, 2013 12:35 PM To: oracle-l
Subject: dbms_space.space_usage and effectiveness of purge operations  

All,

I'm Trying to analyze the need to coalesce indexes after purge operations. Since our purge operations deletes rows from tables that are older than 6 months, we expect to see clean-up of leaf blocks evenly, thereby making the blocks candidate for new inserts.

As an example, i used dbms_space.space_usage package to study the output. This is run on a fk index on order_line_item_id sequence based, where purge operation are performed everyday to delete rows beyond 6 months.

Note: FSn are all blocks.  

Before Coalesce

UNF = 1224, FS4 = 0, FS3=0, FS2=9525, FS1=0, FULL=149334 With Coalesce run on the same index.

UNF = 1208, FS4 = 0, FS3=0, FS2=73548, FS1=0, FULL=85327 Questions :

  1. Why does after coalesce FS2 free blocks increased? From the nature of the deletes we do, i would have expected blocks to join the freelist as and when they get empty for future inserts. That way, coalesce can be completely avoided.
  2. Looking at post coalesce numbers, does this mean we have 73548 blocks available in freelist for future inserts.
  3. Does this mean we ought to coalesce these type of indexes cause almost 50% of the blocks were released to FS2 bucket.

Note: The index is housed in ASSM tablespace with auto allocate.  

--

Thanks,

Stalin

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 05 2013 - 20:23:58 CET

Original text of this message