A problem cropped up at work this week and, whilst it wasn’t particularly tricky and didn’t take long to solve, it struck me that the story of tracking it down might be useful and you might hit the problem yourself one day.
A recently-deployed Data Warehouse database at work has a series of ‘LANDING’ tables. Data is inserted into these tables on a regular basis as part of an ETL process. The database hasn’t been live for long, but the tables kept growing constantly, contrary to the developer’s expectations and the original space estimates. As this is just a staging area, the developers would delete old rows using a script but the tables never seemed to get much smaller. There were about 15-20 tables and they ranged in size from a few hundred MB to a couple of GB.
The reason I’m a little vague on the details is that this wasn’t a database I was working on and it wasn’t me who fixed the problem in the end. However, one of our other DBAs described the situation and asked if I had any ideas. My first question was whether this was in an ASSM or non-ASSM tablespace because Oracle uses completely different mechanisms for deciding whether a block is free to accept new rows or not. I just felt it was information I should know before looking about the problem. The answer was non-ASSM. It actually turned out later that ASSM was being used in error, probably because it’s the default on 10g. On a side-note, we’re in the process of discussing ASSM implementation at work, and I’ve been able to contribute quite a lot to that debate, with the assistance of some of Howard Roger’s articles on the subject and sections of Jonathan Lewis‘ CBO book.
I suppose I should mention this is 10.2.0.2 on AIX 5.3 (I think) but, as I’m afraid to say I often find (because I think it’s a minority view) this was one of the many problems which are utterly generic in nature across all versions that I’m aware of.
The first possible cause I thought of was – are they using INSERT /*+ APPEND */ when they insert the data, i.e. Direct Path Inserts? The simplest way to identify this would be to see the code so, after a little digging around, I came across several PL/SQL packages with MAP_ prefixed. Mmm, they looked like Oracle Warehouse Builder Mappings. Sure enough, there were several INSERT … SELECT statements in there for the relevant tables, all of which contained APPEND hints and were performing Direct Path Inserts.
In summary, the INSERT … SELECT statements would always use space above the High Water Mark so, regardless of how much space the DELETE operations freed from blocks below the HWM, those INSERTs were never going to use it. As an experiment, I thought I’d see what the Segment Advisor made of this situation and, sure enough, it recognised that there was tons of space to be reclaimed. The problem was solved in the end (by the original DBA) by using segment shrink operations which I mentioned briefly in another blog (there’s more on ASSM there, too). Oh, to give you a rough idea how bad the situation had become, the shrink operations took between 20 and 45 minutes per table. When the process was repeated a day later, on the recently-shrunk segments, it was about a minute per table. Not very scientific, but it was clear that the situation has improved, not to mention that the segments were only a couple of percent of the size before the shrink operations!
The longer term solution will be either to eliminate the Direct Path operations (do we need the performance benefit) or to modify the code so that it performs some space management operations itself – maybe the LANDING tables can be truncated when the data’s been used? At least we can pass some sensible suggestions back to the developers and Dev DBA now.
Just bear in mind that if all of your INSERTs into a table have APPEND hints on them, there are space management issues to consider.
Doug, I had been through exactly similar situation. The only difference in our case was datab loading was done through SQL LOADER. Deleting the data and loading data using direct insert option. This problem not only leads space issue (database growth) also the batch time increased from couple of hrs. some times never ending(specially on week-ends.)
One fine day, I determine myself to dig more into this by looking into the code and I was too happy to resolve the issue, simply using trace table in place of delete table.
Once the change is done, I have defragmented the schema, which size reduced from 40gb to 3gb and now the batch runs hardly for one hr.
Jaffar
Thanks Doug,
insightful topic especially the extra info obtained via posts, links and discussion s that were going on in 2005.
(I don’t dare to say anything anymore b.t.w., also Howard might pick it up, and English is not my native language…)
LOL
Just bear in mind that if all of your INSERTs into a table have APPEND hints on them, there are space management issues to consider
Which could be translated “if you use an ETL product (any, and not just OWB) then find out the default load technique – I’ve been caught out with a default APPEND PARALLEL on a merge and not only insert above the high water mark I used multiple new blocks each time I add a few new rows
They were doing a selective DELETE on the staging table?
As I understand it, yes.
However, the description ‘staging table’ should be taken to mean no more than a table where data exists for a short period of time. That period might run to days for all I know but clearly they aren’t truncating the table in the current design or they wouldn’t be running into this problem. The Dev DBA is back in the office tomorrow after a week’s holiday, so I’m sure we’ll find out more then.
If the deletion wasn’t selective, then truncating it would be one option, as I mentioned above already.
maybe the LANDING tables can be truncated when the data’s been used?
I have seen this problem at several client sites. There are really two issues that should be addressed in the organization.
1) Lack of knowledge in how delete and insert append operate.
2) Lack of involvement by production/operations personnel in the design/architecture.
These two issues are rather common and require non-technical solutions.
Agreed. I think this situation is probably slightly complicated by the fact that the code is OWB mapping packages, generated by the tool. So, by using a GUI-based code generator, awareness of the underlying code is restricted. If I recall correctly, it would just be a case of checking or unchecking a box, but it’s a while since I used OWB myself.
just be a case of checking or unchecking a box but then you have to know to look for it 🙂 and for OWB 9.2 you had to open up a ‘+’ option on a property inspector to do this. Still with OWB it creates PL/SQL packages in the target database so the keen DBA could scan the code for inappropriate APPENDS, PARALLELS and the like – but there again that could be the wrong way to go about the problem – perhaps it should be instil awareness
… but then you have to know to look for it
Oh, yes, that’s what I meant. When it’s ‘just’ a case of checking and unchecking boxes, the consequences might not be obvious.
Any OWB developer that has some ‘mileage’ with the product should know there are several kinds of load profiles. The most useful one for staging-like tables would be truncate/insert. Which is the fastest anyway. No deletion, no logging for index maintenance. And, as you excellently have pointed out, no non-reusable tablespace.
If you want to selectively delete data from a staging table, my approach would be to design an extra table for each ‘kind’ of data. And truncate them before inserting.
But then again, there are a lot of developers that do like to do everything themselves instead of looking a bit further and using the features available. 🙂
In the end, it’s all about knowledge, isn’t it? The best tools can help you work more efficiently, but there’s no substitute for a proper understanding of how they can help you.
Nice article.
I have encoutered a similar situation when loading into the staging table and processing from the staging table were asynchrone processes. In wich case the normal sequence of load, proces, truncate is not applicable.
The solution under oracle 8.1.7 was to make two staging tables, one for loading and one for reading(processing) the data.
One table was partitioned with 1 big partition (c1 less then max_value) and at the start of processing the data we did a switch partition and in the and of processing we could savely truncate the table. In this way we did not have to delete or earmark the processed rows, and the loading was given a table that did not have wasted space in it.
And less logging to. 🙂