Updated 24th November 2005
I wouldn’t normally update an existing entry but there are some very interesting and important comments on this entry, particularly from Jonathan Lewis and Howard Rogers, that I’d recommend you take the time to read. Jonathan also mentions it on his website. There’s much more in the book of course 😉 My thanks to Howard and Jonathan.
I certainly wouldn’t want to reproduce the whole of Jonathan Lewis’ new book in a blog but I think it’s worth one last posting for a couple of reasons.
- I think it’s a good way to highlight the existence of a free sample chapter online as pointed out by Radoslav Rusinov. If you haven’t ordered the book, have a read of that for free and decide if you’re interested in 13 more chapters of the same quality.
- As someone who is predominantly a DBA I’m very keen on Jonathan pointing out how what might seem a simple DBA action could unwittingly impact an application in unusual ways. However (and this is important) as I’ve re-read bits of the book I can also think of dozens of ways that a developer could cause problems for the DBA 😉 In fact, I’ve started to get a teensy bit worried that I’m going to have more and more developers rolling up at my desk with ‘bright ideas’ because they’ve read the bits of this book that they want to, conveniently missing the numerous caveats that Jonathan is always careful to include. As long as people take the book as a whole and use some common sense …
Anyway, here’s the excerpt, which I’ll consider worth posting if it stops one poor DBA doing this by accident!
“Oracle 10g has introduced a mechanism to compact a table online. This
only works for a table with row movement enabled that is stored in a tablespace using ASSM.You might use a sequence of commands like the following to compact a table:
- alter table x enable row movement;
- alter table x shrink space compact; — moves rows around
- alter table x shrink space; — drops the high water mark
Before you rush into using this feature, just remember that it allows you to reclaim space by filling holes at the start of the table with data moved from the end of the table. In other words, any natural clustering of data based on arrival time could be lost as data is moved one row at a time from one end of the table to the other. Be careful about the effect this could have on the clustering_factor and desirability of the indexes on such a table.”
I can see this happening. You think – ‘Cool, look at that nice feature for reclaiming some space. Let’s use that!’ – so, you do. A week passes. A Saturday night statistics gathering job kicks in and the clustering factor of several indexes changes to reflect the new distribution of the data. Then, on Monday morning the performance of a couple of queries plummets and no-one can quite work out why. Trust me that it would be a nightmare. This is the way many sites work and this change wouldn’t be identified as high risk. Let me reiterate – you change where data is stored and so your application starts to work differently, but only after your statistics have been refreshed so could you tie it to the reorganisation activity? Of course, it might be a sensible behaviour change from the point of view of the optimiser but could you have predicted it? Well, if you buy the book, you’d at least be aware of the issue.
* I think it’s worth noting that when I bumped into Jonathan at the UKOUG conference just as the book was being published, he was very pre-occupied by a small error in the book (which funnily enough he’d introduced himself, having got it right the first time!). If you look at this page on his website, you’ll notice how committed he is to correcting the tiniest of errors. That should tell you all you need to know about the quality of his work.
I’m afraid that I think Jonathan is simply wrong on this one.
Shrinking segments can only be done to segments which are stored in tablespaces which are locally managed and which use ASSM, and as I’ve previously posted in my own blog, it is ASSM’s job to disrupt the ordering of things in a table. By scattering a table’s rows around, it ensures there is no contention for its blocks, after all. And if you have already scattered rows around, it is highly unlikely that you will have any meaningful clustering factor for any of your indexes. Shrinking the segment is therefore not going to disrupt anything very meaningful much further, therefore.
Specifically, if you expect a table’s rows to be ordered by their time of insertion, then in ASSM tablespace, they won’t be.
I will confess to not having purchased Jonathan’s book yet, but that’s not because I won’t be, just because I have purchased quite a lot of other things of late, and there has to be a queue for things now! So I may be missing a subtlety or three of his argument, and am barking up the wrong tree of a suggestion quoted out of context. But just looking at the words you cite, and the example you are quoting, it can’t be like that.
Which doesn’t mean the point about ‘don’t rush to implement cool new features’ isn’t an entirely sound one, though.
Howard,
You know it’s funny, I was thinking about this when I was away from the PC thinking ‘I shouldn’t have just quoted that without trying it out and showing it’. So that’s what I’ll do and update this.
It just jumped out at me as being interesting and I might have quoted it out of context.
Cheers,
Doug
Howard,
“I’m afraid that I think Jonathan is simply wrong on this one.”
I feel a bit bad about this because what I didn’t show was the section heading – ‘Loose Ends’ – and the few words immediately prior to the quotation that says ‘… here’s a thought for the future’. I think those two things should highlight that this was a little interesting disussion point at the end of a chapter. Most of his points are backed to the hilt with worked examples, but occasionally he’ll say ‘here’s something to have a think about …’ and this was one of those things.
Fortunately, because I picked the online sample chapter, everyone can go and see it for themselves. The link to the chapter is in the main blog entry and the code examples are at http://www.apress.com/book/supplementDownload.html?bID=10081&sID=3124. When you do, you’ll see that Jonathan shows very clearly that he understands the point of ASSM and the implications on the clustering factor. I think you know that already anyway, but he has a good ASSM section in there.
“Shrinking segments can only be done to segments which are stored in tablespaces which are locally managed and which use ASSM, and as I’ve previously posted in my own blog, it is ASSM’s job to disrupt the ordering of things in a table. By scattering a table’s rows around, it ensures there is no contention for its blocks, after all. And if you have already scattered rows around, it is highly unlikely that you will have any meaningful clustering factor for any of your indexes. Shrinking the segment is therefore not going to disrupt anything very meaningful much further, therefore.”
Excellent point that Jonathan also makes in his ASSM section. I think my question would be – could it make a poor clustering factor even worse? Or better? Or who knows?
“Specifically, if you expect a table’s rows to be ordered by their time of insertion, then in ASSM tablespace, they won’t be.”
Sure, so maybe all Jonathan is saying is that the clustering factor will *change* when you shrink the table – no more than that? I probably made this seem a lot more important than I intended and than Jonathan has!
“I will confess to not having purchased Jonathan’s book yet, but that’s not because I won’t be, just because I have purchased quite a lot of other things of late, and there has to be a queue for things now!”
Tell me about it! I should really be working on other things just now and have delayed getting Tom Kyte’s new book for the moment because I have a couple on the go already. I don’t know you beyond the web, but I’m sure it will be right up your street.
“So I may be missing a subtlety or three of his argument, and am barking up the wrong tree of a suggestion quoted out of context. But just looking at the words you cite, and the example you are quoting, it can’t be like that.”
If there is a misunderstanding here, I suspect I’ve created it. I’m playing around with some of the example code from the book just now and will probably do a follow-up blog because it’s too much for a comment and not visible enough.
“Which doesn’t mean the point about ‘don’t rush to implement cool new features’ isn’t an entirely sound one, though.”
Definitely. I also think it’s important that what may seem mundane DBA tasks could have far-reaching effects on the application. That’s a side of the book I’ve enjoyed in particular.
P.S. My apologies that you’ll have to track this by visiting the comments here, rather than getting an email notification. It’s rubbish, I know, but I dread moving blog hosts twice in one year!
You can direct-path load a table _in sorted order_ in an ASSM tablespace. Data warehouses might have that kind of table. Such a table would have its clustering_factor disrupted by a segment shrink. That’s seriously bad for a data warehouse query.
Same thing _can_ happen for a conventional-path table that is in sorted order in an ASSM tablespace. But, as Mr. Rogers pointed out, in an OLTP database, a regular table in an ASSM tablespace probably isn’t built in sorted order (since small INSERTs are done all the time, and blocks (not rows) are spread out by the ASSM mechanism). And you can’t reorg such an OLTP table in sorted order and rely on the clustering_factor to stay the same.
There’s a serious issue going on with Mr. Ed’s observations, which, as far as they go, are entirely correct: any direct path operation (CTAS, for example) will circumvent ASSM’s randomisation of inserts.
But it would be more accurate all round to say, “If Oracle ever ignores the existence of ASSM, then clearly ASSM’s side-effects won’t kick in. Direct-path operations persuades Oracle to ignore the existence of ASSM.”
That’s as opposed to saying , “Oh, ASSM is alright provided you do direct path operations”.
There’s a very different emphasis in the two statements, though the practical outcome might be the same. From my point of view, in other words, the presence *and operation* of ASSM means that row order is randomised. End of story. Period. Full stop. Are there things that stop ASSM operating? Yup. Are they common? Nah, certainly (as Mr. Ed again mentions) not in an OLTP environment.
In short, it would be useful to get the emphasis right. And the emphasis should be that ASSM disrupts row ordering, not that under particular conditions, its disruptive effects can be vitiated. Describe the generality (and note the exceptions), not the exceptions (as if the generality didn’t exist).
The serious point here is that the clustering factor in 10g becomes something of a joke as far as the DBA is concerned. The old statement that it ‘can vary between BLOCKS and NUM_ROWS’ becomes deeply suspect, because ASSM is switched on by default in 10g, and the presence and operation of ASSM will mean that no-one will routinely achieve a clustering factor anywhere near BLOCKS. Trying to achieve such a CF, as Mr. Ed. says, is a hopeless cause.
Of course, the optimiser still continues to assess *relative* clustering factors, and it’s entirely right to point that out in a book about the cost-based optimiser. But the days of chasing a particular CF close to BLOCKS are surely behind us in 10g, and (back to the point of my original comment) to think that a particular SQL command (“shrink space compact”) might cause trouble when a structural factor is anyway in place that has already caused that trouble is to concentrate on the wrong sort of issuese!
Howard,
First, I mentioned this discussion to Jonathan and I think he’ll probably join in over the next couple of days.
I agree with most of what you’re saying here. In fact I don’t really disagree with any of it but perhaps I don’t agree with your reading of what I was trying to say.
“In short, it would be useful to get the emphasis right. And the emphasis should be that ASSM disrupts row ordering, not that under particular conditions, its disruptive effects can be vitiated. “
I know you’re talking about mr. ed’s comments here, but I think you’re right that the emphasis needs to be right. In fairness, that’s precisely what Jonathan does in the book. There’s a significant ASSM example in the book that shows the disruption and the effect on the clustering factor. It comes before the small section I quoted that was only meant to be an interesting issue that people should think about – not a complete treatment of how the clustering factor could be affected.
“The serious point here is that the clustering factor in 10g becomes something of a joke as far as the DBA is concerned. The old statement that it ‘can vary between BLOCKS and NUM_ROWS’ becomes deeply suspect, because ASSM is switched on by default in 10g, and the presence and operation of ASSM will mean that no-one will routinely achieve a clustering factor anywhere near BLOCKS. Trying to achieve such a CF, as Mr. Ed. says, is a hopeless cause.”
I don’t think anyone was saying that although if it comes across that way, point it out and I’ll change it. All I was talking about was that the clustering factor might change (which in turn might change whether an index was used or not) based on a space maintenance activity and that the activity and the consequence were decoupled. In fact, that’s a pretty general point but I’m not sure that everyone would realise it.
I’m still not convinced that there won’t be sites that wouldn’t hit this sort of problem because they do use direct path operations. But as I can’t prove that, it’s just a suspicion.
“Of course, the optimiser still continues to assess *relative* clustering factors, and it’s entirely right to point that out in a book about the cost-based optimiser. But the days of chasing a particular CF close to BLOCKS are surely behind us in 10g, and (back to the point of my original comment) to think that a particular SQL command (“shrink space compact”) might cause trouble when a structural factor is anyway in place that has already caused that trouble is to concentrate on the wrong sort of issuese!”
Again, I hold my hands up as the person who concentrated on this by pulling it out and putting it in a blog entry. It’s not what Jonathan concentrates on in the book. He concentrates on ASSM as a source of problems in the first place.
Surely I can mention this, though, without having to put all of the background information in too? I really don’t see where I’ve said anything about chasing a CF close to the number of blocks either. All I said was that the behaviour of the app could change.
I definitely wouldn’t want to mislead people and perhaps I’ve picked out a poor and very unusual example. In my defence, I’ve been consistent in suggesting that people buy the book so that they can learn how the CBO works properly. This was only meant to be a teaser.
Cheers,
Doug
P.S. I’ve not forgotten trying to produce a test-case but it takes time as you can imagine and my other half seems to think that weekends are for shopping and the like.
Doug: could we drop the defensive stuff about Jonathan? First, because I wasn’t criticising *him*, and second because he has no trouble arguing facts?!
So, you know “In fairness, that’s precisely what Jonathan does in the book” is really unnecessary, because (a) I hope I wasn’t being unfair, and (b) because I wouldn’t think for one minute that Jonathan would dive off the deep end without a very good cause and stack-loads of evidence to support his point. I thought I’d made that clear in my original comments (‘I may be missing a subtlety or three’).
The phrase of mine you quote was 100% directed at Mr. Ed’s comments. He has a long history of posting that ‘you can load sorted data in ASSM’, and I have a long history of arguing that ‘you can load sorted data in ASSM only provided you perform a direct load operation that effectively means you have switched ASSM off. If ASSM is in operation, you have randomised row order, period’
My original comment was intended merely as a small aside that to worry about shrink space compact disrupting a clustering factor is to concentrate on the wrong things. That’s not a ‘go’ at Jonathan. It’s not a go at you. It’s not a go at anyone. Just an observation.
“I don’t think anyone was saying that although if it comes across that way, point it out and I’ll change it.”
OK -look. If you’re going to take things in that way, I’ll just stop posting here. Mr. Ed did suggest that in an OLTP environment, a re-organisation of a table in ASSM tablespace will not help achieve a good clustering factor. That’s fact, acknowledged by us (Mr. Ed and me) both. I paraphrase that as
‘reorganisation is a hopeless cause’, and suddenly you seem to think it involves something you wrote. It didn’t. You don’t need to change anything, and if you keep thinking that everything I say to Mr. Ed via your blog means I am commenting on your comments, then, as I say, I’ll stop commenting on things here. It’s confusing enough as it is, without defensiveness kicking in and making it a whole lot more confusing.
I don’t CARE what Jonathan ‘concentrates’ on in his book. That’s not the point, that’s not what I was commenting about, that’s not even what Mr. Ed was writing about. The simple facts are that ASSM disrupts row ordering. That’s fact. Row ordering in a table affects an index’s clustering factor. That’s fact too. Therefore, ASSM affects clustering factor. Fact. It’s not something to dispute or feel guilty about, merely something to bear in mind. It’s no big deal, and if anyone does what I do all the time, which is to sometimes forget what ASSM does, then that’s perfectly understandable, too -as is the inevitable reminder.
I simply don’t understand why you feel the need to write ‘Surely I can mention this without all the background stuff’. Are you suggesting that my pointing out that ASSM intrinsically disrupts the clustering factor is somehow an unjustified attack on Jonathan? It’s not an attack on anyone, for a start! And the context is irrelevant when it comes to determining technical fact.
I don’t know how else to say to you that I don’t care what Jonathan wrote, or what you wrote. It was the generic suggestion that ‘shrink space compact’ added woes to the clustering factor that ASSM hadn’t already added that I was addresseing. It could have been a completely anonymous comment for all I care. It’s the facts of the case I thought I’d mention, not whether the facts were a big, small or miniscule part of any particular person’s grand or otherwise argument.
So there’s no need to be “in my defence”. No-one is being got at here. The point is generic and of interest, that’s all. Your original blog gave me occasion to write what I’ve already written about several times before. That it happened to be a blog in praise of Jonathan’s book is entirely incidental and of zero difference. I will avidly read anything Jonathan writes, and my only regret is that it’s taken him so long to write a follow-up to “Practical 8i”.
What I think I’m trying to say is: forget you. Forget your blog entry. Forget Jonathan. Forget Jonathan’s book.
Instead: I want to make the statement that ‘shrink space compact’ does no more damage to the clustering factor than has probably already been done because the table was sitting in ASSM tablespace already. Period. Fullstop.
I’m not interested in why that statement needs to be made, particularly. Just that it ought to be made because it happens to be factually true (as far as I can determine).
If it’s going to cause continuing grief to say it here in this context, then as I say, I am happy to stop saying it here altogether.
That’s not me getting defensive or weird about it either. Just that a simple statement of fact should not have this degree of second-guessed motivation attached to it.
And I really hope we’re not going to get into the ‘I am sure some sites will experience CF degradation by shrinking segments’ stuff! Because I didn’t suggest it *couldn’t* happen, merely that it would be “highly unlikely” to happen. OLTP environments won’t see it happen. And not every data warehouse will load via direct path *all* the time, so not every data warehouse will see it happen. Which means most sites will not have to worry about it. Most, but not all. I’ve also already said that it’s the relative CF that is important to the optimiser, so I have no problem accepting that shrinking a segment can make the relative position of CFs worse than before. But if we are discussing the relative position of things over which we can have no practical control, then the issue becomes of no practical significance. It’s very different from someone thinking, for example, that shrinking a segment will likely or even probably worsen a clustering factor.
Or, as Tom sometimes phrases similar sorts of situations: sometimes shrinking a segment will make the CF worse, sometimes it will make it better, sometimes nothing will change.
I’m not suggesting shrinking a segment will have no effect, in other words. Just that in an architecture where the row order has already been randomised, adding another level of randomisation is not something *especially* to worry about.
Howard,
Wow! Erm, I’m very loathe to make this turn into an unneccesary argument so I’ll just try to make a few points clear.
“Doug: could we drop the defensive stuff about Jonathan? First, because I wasn’t criticising *him*, and second because he has no trouble arguing facts?!”
Maybe I seem more defensive than I intended but the first line of your original comment was
‘I’m afraid I think Jonathan is simply wrong on this one’
I happen to think he isn’t but that’s all open to argument, which is what you and Jonathan are quite capable of doing yourselves if you disagree, as you’ve pointed out. However, what’s wrong with me thinking ‘Oh, dear, I hope the fact that I’ve quoted a small section of a large chapter isn’t misleading?’ I don’t see that as particularly defensive and that’s where I’m coming from.
“So, you know “In fairness, that’s precisely what Jonathan does in the book” is really unnecessary, because (a) I hope I wasn’t being unfair, and (b) because I wouldn’t think for one minute that Jonathan would dive off the deep end without a very good cause and stack-loads of evidence to support his point. I thought I’d made that clear in my original comments (‘I may be missing a subtlety or three’).”
You did make that clear and when I say ‘in fairness’ I didn’t mean *you* were being unfair I meant ‘have I represented the context of his argument fairly?’. The word fairness wasn’t about you in other words.
“My original comment was intended merely as a small aside that to worry about shrink space compact disrupting a clustering factor is to concentrate on the wrong things. That’s not a ‘go’ at Jonathan. It’s not a go at you. It’s not a go at anyone. Just an observation.”
And I think you have a very good point that it might not be the best thing to concentrate on. But you started the comment with the ‘Jonathan is simply wrong’ line and maybe I’ve overplayed the importance of that in my own mind.
“OK -look. If you’re going to take things in that way, I’ll just stop posting here …
…
It’s confusing enough as it is, without defensiveness kicking in and making it a whole lot more confusing.”
I don’t think I was feeling particularly defensive when I made that comment but I apologise if I was.
“I don’t CARE what Jonathan ‘concentrates’ on in his book. That’s not the point, that’s not what I was commenting about, that’s not even what Mr. Ed was writing about.”
But the blog was about Jonathan’s book and if I feel I’ve misrepresented him in some way by lifting something out of context I’d like to put that right. What on earth could be wrong with that??
“What I think I’m trying to say is: forget you. Forget your blog entry. Forget Jonathan. Forget Jonathan’s book.”
Why would I? The blog was about Jonathan’s book and what I thought of something in it. Maybe my comment shouldn’t have been addressed directly to you now that I think of it because a lot of it was based on ‘Mmmm, I hope I’m right on this one’, not ‘Howard’s wrong and he’s having a dig at me/Jonathan’. Really, you just have to trust me on that (well, you don’t have to actually, but I’m not going to justify myself when I know why I write something)
“Or, as Tom sometimes phrases similar sorts of situations: sometimes shrinking a segment will make the CF worse, sometimes it will make it better, sometimes nothing will change.”
Wasn’t that what I was trying to say here in a previous comment?
“I think my question would be – could it make a poor clustering factor even worse? Or better? Or who knows?”
“I’m not suggesting shrinking a segment will have no effect, in other words. Just that in an architecture where the row order has already been randomised, adding another level of randomisation is not something *especially* to worry about.”
I think I agree with you on that.
Really, I didn’t feel defensive and was just trying to be honest in how I was representing someone else’s work.
I certainly don’t want to argue with you about this, believe me. Personally I’m glad that you commented because you’ve made the whole entry more useful.
For now it’s probably best if I just shut my mouth and let Jonathan speak for himself on this one because I wasn’t trying to speak for him.
I was just being polite in replying to your comments I thought.
Cheers,
Doug
Since Doug sent me an email about the point made by Howard, I thought I’d add a short note to address the issue he has raised.
Correct – ASSM does make a complete mess of the clustering_factor under typical concurrent operations. However there are two other points to consider.
First, ASSM formats 16 blocks at a time – so even though data is scattered by the ASSM mechanism it is only slightly scattered and you will still see a clustering effect for some classes of data, even though the effect may now operate at a slightly larger scale than the single block.
Secondly, the mechanism used by Oracle to generate a clustering_factor suggests that there is ongoing development to introduce a “corrected” clustering_factor that caters for the localised scatter introduced by ASSM (or multiple freelists or multiple freelist groups).
It is in the context of a mechanism for distinguishing between loosely clustered and randomly scattered data that compaction could become a threat. A mechanism that caters for a little localised noise is unlikely to cater for smearing the most recent 5% of the data across the previous 95% of the table.
It’s a particularly irritating problem because it will be hard to predict whether it will be the optimizer arithmetic, or the run-time activity, that will cause the bigger problem in any particular system.
Jonathan Lewis
Whatever, Doug. I was commenting on the effects of ASSM, not on the merits of a book which I self-confessedly haven’t even read yet.
Right: and now down to the facts as Jonathan goes on to describe them: I agree 100%. It’s a matter for future development, and things *might* happen in the future that will make shrinking segments problematic for a new, improved clustering factor. But we don’t have such a thing right now, and the clustering factor we do have is indeed “made a complete mess of under typical concurrent operations”.
Which means, I hope, that the message can go out that for 10g, right now, shrinking space in a segment does no more dreadful scrambling of the clustering factor than has probably already taken place simply by adopting ASSM in the first place.
And perhaps a related message: in 10g, by default, absolute clustering factors are a thing of the past. The days of ever achieving one close to BLOCKS by a bit of table re-organisation are behind us, in other words.
Howard,
Based on your last comments, it seems likely that you have never had to use the dbms_stats.set_XXX_stats procedures to correct an unsuitable estimate generated by dbms_stats.gather_XXX_stats. It also appears that you haven’t yet had time to read the download chapter that Doug mentioned earlier on in the blog.
You may change your mind after reading the chapter.
Regards
Jonathan Lewis
I have read the download chapter, and I enjoyed it. And I have set statistics in my time.
But I don’t see how any of that means it’s OK to suggest that the clustering factor is inherently at risk of being ruined by a shrink space compact operation, when the CF has already been “made a complete mess of” by ASSM, as you yourself put it.
If you are suggesting that various statistics, the CF amongst them, might need to be tweaked as part of a tuning exercise, I won’t disagree (though I must have missed the bit where ADDM suggests to do this! This is fairly specialised stuff, in other words). And if you are then suggesting that all sorts of DBA-inspired activities, shrinking segments among them, might screw up the carefully-tweaked statistics, I won’t disagree there, either.
But that’s a rather different beast from the original ‘any natural clustering of data based on arrival time could be lost as data is moved one row at a time from one end of the table to the other’, don’t you think?
Howard,
Just for the sake of clarity, consider a system which inserts about 500 rows per day. By happy coincidence, 500 rows happens to be a good fit in 16 blocks – especially when ASSM does its little trick of making best use of space.
Because of the way ASSM formats 16 blocks at a time, 1st Jan is in one group of 16 blocks, 2nd Jan is in another group of 16 blocks, 3rd Jan is in a third group of 16 blocks.
Do you think that the data is exhibiting any natural clustering based on time of arrival ? If not, then clearly it is the concept of clustering (not, I hasten to add “clustering_factor”) that is causing the difficulty.
I happen to think that the data is exhibiting a natural clustering based on time of arrival. So if someone takes the data for 21st to 31st December and moves one row into each of the earlier blocks in the table, then the natural clustering has been lost – which is EXACTLY the point made in the text you have quoted.
However, in view of your comments – I will try to adjust the line that follows your quote to say something like “If you have used the technique discussed in the previous section for correcting the effect ASSM has on the clustering_factor, then compacting tables may well nullify the effort”.
Regards
Jonathan Lewis
I think that’s the issue, Jonathan. It’s why at one point I suggested not posting about it here any more… it really requires/ed a venue to elaborate on these distinctions (such as between clustering and CF) properly.
I also think that we are talking at cross-purposes to some extent because I have one audience in mind and the statement that was quoted had (I believe) another.
When I read that freebie PDF chapter, for example, and your discussion of fine-tweaking the CF to deal with ASSM, I see you using words like ‘probably’, ‘I think’, ‘almost certainly’ and so on. You’re using an internal procedure whose function you have to surmise from its name, at one point. OK -you test your surmising, and from observation of the results, you make thoughtful deductions, not wild guesses. But we’re in the research field here (in my opinion, of course), not necessarily in practical DBA territory. It’s bloody clever rocket-science stuff -but I have to deal with people who think a pretty graph in Enterprise Manager is the height of performance tuning genius.
What concerns me above all is that (depsite your explicit advice to the contrary) people will treat that as gospel and apply it to things without discrimination. And views you deduce from your experiments are then similarly treated as bold assertions rather than the results of speculative experimentation.
It’s why I blanched when Doug drew the conclusion that your advice was worth quoting “if it stops one poor DBA doing this by accident!” That means your ‘poking around’ (sorry: that sounds derogatory, but it’s not meant to be. I can’t think of another phrase right now) inside the internals has already become the basis of
a firm opinion about the merits of shrink space compact, and I don’t think that (in this specific and peculiar case) that is warranted.
In sum: it is apparent you are talking about clustering, not the clustering factor. Accepted. And you’re talking about an adjusted clustering factor, not the ‘normal’ one most users of DBA_INDEXES would see. You’re talking about a table that “by happy coincidence” happens to have a good degree of clustering (not CF) even though it’s in ASSM. And about procedures that might work as suspected, and might produce good results with a mysterious parameter set to 16, though not if actual concurrency is less than that…
If we pile up all the ifs, buts and caveats, I am merely hoping we aren’t left with a sweeping statement in any reader’s mind that shrink space compact is somehow more ‘dodgy’ than many another DBA activity. Your proposed revision is entirely to the point in that regard, and makes it much clearer (to my mind, at least) what the scope of the issue actually is. Thanks.
Um… I only skimmed the discussion, but wouldn’t a shrink only be effective if some old rows had been deleted in the first place? The clustering factor after a shrink might only get really messed up if there were deletes of random rows. And if you don’t shrink, future conventional inserts could fill those holes anyway and leave you in a similar boat eventually.