I saw an interesting article on ASSM from Howard the other day. I wanted to comment on it but unless I’m being a bit thick, it doesn’t seem to be a blogpost and therefore I couldn’t and instead, I thought I’d do it here.
As I said, I found the article interesting and informative but I did have a few “But what about…?” issues that popped into my head as I read through it.
It probably won’t surprise anyone who reads my blog that my queries relate to the use of ASSM on a datawarehouse environment given my interest in that area.
Firstly, Howard asserted that ASSM wouldn’t be helpful in scenarios where you recieved sorted data from a warehouse source which you wanted to load up into a target table in order that you could gain a performance benefit from creating an index on that data whilst taking advantage of the NOSORT option – thereby speeding up the index creation considerably. Nothing Howard said was untrue although there are a number of things that could be discussed in relation to this point…
Firstly, in my experience, most warehouse processing uses Oracle parallel execution for performance – but if one were to load up data into a target table using parallel processing, then even if it were sorted in the source, the use of parallel processing would result in the target being unsorted and hence this advantage would be lost. Of course you could resort to serial processing but would that be more efficient than doing the work in parallel and then sorting the data for the index build again using parallel processing techniques – each process is probably different but I guess what I’m saying is that it’s possible that the NOSORT issue is not necessarily relevant in all cases especially those on a DW using parallel execution.
There are a number of caveats with the use of the NOSORT option such as:
You cannot specify REVERSE with this clause.
You cannot use this clause to create a cluster index partitioned or bitmap index.
You cannot specify this clause for a secondary index on an index-organized table.
Check out www.hoax-slayer.com before you send sildenafil tablets in india on any warnings. 7. There are three viagra pharmacies secretworldchronicle.com hormones that the Clomid are designed to work on. The working of blue pill has allowed researchers and clinicians to determine the connection between erectile buy cialis in australia dysfunction and other sexual problems in males. Children s and women are strictly asked to stay away from liquor as it may diminish the viability of published here levitra on line this item Don’t expend with, or simply after a high fat or heavy meal, it may take longer to affect a cure but both are effective.
Read more here
The use of Direct Path operations on a DW might also mean that the space wastage doesn’t occur given Oracle constructs full blocks from clean ones rather than via free list management or the ASSM equivalent.
Howard suggested that tests he has undertaken showed a 1.5% excess space utilisation when ASSM was involved on a 1Tb table. A long time ago I would have been horified to lose 15Gb but these days I probably wouldn’t be as concerned, particularly as I often find ways to save that kind of storage just as easily in other areas, e.g. Ensuring we use compression appropriately for tables and indexes and ensuring we only have indexes we need and which are of the right type and structure. If the solution to save that 1.5% were simple then I’d consider that too – not using ASSM sounds like a simple solution but I guess it depends on what the DBA standards are for the organisation amongst other factors.
Howard mentioned that with ASSM “wasting” space and using more blocks to store the same number of rows, that would mean a full scan would need to read more blocks for a given table and that the numerous, repeatedly accessed and therefore “hot”, ASSM bitmap blocks could lead to other, “warm” data being aged out sooner than would otherwise be the case – It’s a reasonable hypothesis and if we were full scanning a 1Tb table that had 1.5% or 15Gb of “waste” – I’m making a big assumption due to my lack of research here, in guessing that the 15Gb is largely made up of these ASSM bitmap blocks – then yes, 15Gb of blocks could have a considerable impact on a buffer cache…but that’s assuming you’d be reading the whole 1Tb table via a full scan – one would imagine the table is partitioned by some reasonable key (hopefully including time) and that therefore you’d only be reading a much smaller subset with the consequent reduced effect on the buffer cache. The effect is there but it’s impact would be system/query dependent.
Since the read (full scan) of the table itself would result in blocks going on the LRU end of the LRU list for the buffer cache then that wouldn’t affect the caching of hot/warm data at all – it’s just a matter of whether the ASSM bitmap blocks would be aging out other “warm” data to the detriment of overall system performance.
Whilst it raised a number of queries, it’s definitely the case that the article Howard posted has raised quite a debate within the DBA team on the warehouse I’m currently working on…should be fun this week on the warehouse as we contemplate peforming a number of benchmarks to try and ascertain which way we should jump – if any!
On another note, even though he’s moved on to pastures new, I think Doug Burns must have had something to do with the latest Sky TV adverts as the phone number they wanted you to call was 08702 42 42 42 – now that’s what I call leaving a lasting impression!
Firstly, you’re not being thick. Articles can’t be commented on directly. But there is the Forum. And you might have let me know you had thoughts via email.
Secondly, I didn’t “assert” that ASSM disrupts a sort order. I demonstrated it. There’s a difference. Now, if it is true that you seldom create indexes with the NOSORT clause, then great: that particular drawback clearly doesn’t apply to you. But if you do, be warned: your work practices are going to have to change. That’s just plain fact, not mere assertion.
Third, when I had an 8TB SAN to work with, wasting 15GB would have been poor form. The rules, as you say, might be very different where you work, and 15GB might be utterly trivial… and that’s fine -but it doesn’t mean you won’t be wasting 15GB (or having to full scan it).
Fourth, whilst “one would imagine the table is partitioned by some reasonable key”, doing so would miss the point that EVERYONE has ASSM whether they like it or not, including Standard Edition users for which partitioning is not available and Enterprise Edition users who have chosen to forego the pleasure.
You can, in other words, come up with 500 perfectly reasonable reasons why ASSM is just fine and dandy. But it’s on by default for everyone, and your ‘get out’ clauses on its behalf don’t apply to everyone. It’s that disparity which means ASSM shouldn’t be deployed unthinkingly but used appropriately.
As the original article says, though, even that statement has two exceptions: if you’re using ASM and/or RAC, ASSM is a no-brainer.
Howard, thanks for your response and my sincere apologies if I caused offence…it certainly wasn’t my intention.
It did cross my mind to email you but I thought that it would be just as reasonable to create the blog post and publicise your article in order to:
1) Get people to read it – and learn from it
2) Open up debate on this interesting topic
I chose the wrong word there with “assert” and as you say, should have used either the word “demonstrated” or indeed “proved”. Again my apologies.
With regard to NOSORT, I simply wanted to make the point that I’ve not seen that facility used on a DW that I’ve been involved in, mainly because I’ve always used PQ which by it’s nature results in “randomising” the data thus necessitating a sort – if you want it in sorted order. As I said, my focus these days is on DW and as such my issues were relating to that. Of course, as you say, if you rely on NOSORT for your processing and you implement ASSM, it is a fact that you will need to change your approach to processing.
Losing 15Gb is losing 15Gb no matter which way one spins it…and , as I said, if the solution to save that 15Gb were simple then I’d definitely consider implementing it – I’m actively discussing the use of ASSM with our DW DBA team at this time.
As far as my partitioning key comment goes, I guess that again just shows how much focus DW has on my mind and how I kinda assume that if it’s a DW, it’s and enterprise edition system using partitioning.
Thanks again for writing the article and opening up some debate on the subject.
>The use of Direct Path operations
>on a DW might also mean that the
>space wastage doesn’t occur
Direct-path INSERT will bypass ASSM layout of the segment.
Thanks Mr Ed.
I’m fairly confident it would be that way but don’t have an environment to prove it so I used the word might just in case.
Do you know that from a reference in the manuals, experience or both?
Experience first. I saw a slowdown due to ASSM, then I reproduced speedups and slowdowns due to direct-path and conventional INSERTs. Afterwards, I verified by looking at the row layout using information in the Oracle data dictionary.
When I get a chance, I’ll post a new, simple example on my blog about how to verify the row layouts and more.
I look forward to it…sounds interesting.