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.<\/p>\n
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. <\/p>\n
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<\/a> 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<\/i> 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<\/a> on the subject and sections of Jonathan Lewis<\/a>‘ CBO book<\/a>.<\/p>\n 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.<\/p>\n 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<\/a>.<\/p>\n 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<\/a> 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<\/a> (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!<\/p>\n The longer term solution will be either to eliminate the Direct Path operations (do we need<\/i> 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.<\/p>\n 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.<\/p>\n","protected":false},"excerpt":{"rendered":"