Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries February 16, 2012
Posted by mwidlake in database design, internals, performance.Tags: data dictionary, design, performance, SQL
trackback
My last post was on slow dictionary queries caused by having many, many thousands of extents in your database. This post is about a special case of this problem, which I encountered recently. It was on an Exadata box – Exadata is not particularly relevant to the issue, but I’m curious to see if mentioning Exadata will make this post more popular 
I was querying the used and free space on a very small database on the client’s X2-2 1/4 rack Exadata machine. The query was really slow, taking about 30 seconds. This is a FAST box, what is going on?
I quickly realised that the problem was specific to one tablespace:
>@SPC_SUM
Enter the tablespace (or leave null)> DATA_01
TS_NAME ORD SUM_BLKS SUM_K MAX_CHNK_K NUM_CHNK
-------------------- ----- ----------- ------------ ----------- --------
DATA_01 alloc 262,144 2,097,152 2,097,152 1
free 63,128 505,024 504,384 11
2 rows selected.
Elapsed: 00:00:00.21
> @SPC_SUM
Enter the tablespace (or leave null)> USERS
TS_NAME ORD SUM_BLKS SUM_K MAX_CHNK_K NUM_CHNK
-------------------- ----- ----------- ------------ ----------- --------
USERS alloc 748,320 5,986,560 5,372,160 2
free 127,904 1,023,232 6,144 3,058
2 rows selected.
Elapsed: 00:00:26.05
We can see it takes 0.21 seconds for tablespace DATA_01, 26.05 seconds for the USERS. Yes, USERS is larger but not 150 times larger. What is going on? Well, as the title of this post suggests, it is down to dropping tables. The below shows my route to that conclusion.
What I could see was that for the USERS tablespace the maximum chunk of free space was relatively small compared to the sum of free space – 6MB out of 1GB, pretty much – and that there was a lot of individual pieces of free space, 3,058. This tablespace was shattered into a lot of bits. So, what sort of extent size management do we have? How big are the tables and indexes in this tablespace {NB I already knew we had no partitions so I did not have to worry about that}.
select tablespace_name,initial_extent,next_extent
,extent_management,min_extlen,allocation_type
from dba_tablespaces where tablespace_name ='USERS'
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT EXTENT_MAN MIN_EXTLEN ALLOCATIO
------------------------------ -------------- ----------- ---------- ---------- ---------
USERS 65536 LOCAL 65536 SYSTEM
select tablespace_name,blocks,count(*) from dba_extents
where tablespace_name = 'USERS'
group by tablespace_name,blocks
having count(*) >1
order by blocks desc,tablespace_name
TABLESPACE_NAME BLOCKS COUNT(*)
------------------------------ ---------- ----------
USERS 2560 2
USERS 2048 3
USERS 1536 7
USERS 1408 5
USERS 1280 2
USERS 1248 2
USERS 1152 2
USERS 1024 229
USERS 896 15
USERS 768 21
USERS 736 3
USERS 720 3
USERS 704 2
USERS 672 2
USERS 640 25
USERS 624 2
USERS 576 2
USERS 512 117
USERS 400 2
USERS 384 34
USERS 360 2
USERS 312 2
USERS 288 4
USERS 256 49
USERS 248 2
USERS 240 2
USERS 192 5
USERS 160 4
USERS 128 1165
USERS 8 1788
30 rows selected.
So we have system controlled extent allocation, this should be fine. I did a quick check of the table contents – 300 or so tables and 200 or so indexes. And, as you can see from the check of extents above, very few larger extents and a lot of small ones. {As a little aside, note the relatively large number of 8-block, 128-block, 256-block and 1024-block extents; These are the sizes that LMTs with system managed extents tend to use unless a similar sized chunk is found to be available, in which case it is used}.
I did some other checks on indexes and segment sizes and it all boiled down to one thing. There were not a lot of things in this tablespace, what was there was small and… The total of all those table and index blocks was way less than the used space in the tablespace.
That is the major indicator of what is going on here. The difference in space used in the tablespace and the total of the visible tables and indexes.
The last link in the chain is the recycle bin.
select owner,ts_name,count(*) from dba_recyclebin group by owner,ts_name OWNER TS_NAME COUNT(*) ------------------------------ ------------------------------ ---------- USER1 USERS 542356 USER1 2 WEGWEGWEG USERS 97 KKKUKUYLLX USERS 149 USOVFPKEKS USERS 3 .... ERHJTRTTTURT USERS 4 11 rows selected.
That’s 542,356 objects in the recyclebin for one user, in the one tablespace. My problem tablespace. The penny clicked, something I had already noticed and was a little uncomfortable about fell into place.
The client is using Oracle Data Integrator (ODI) to pull data together and put it into the database. The process they are using basically creates a table, uses it to load some data into and then pushes the data into the target tables. Then drops the table. The drop does not do a “DROP TABLE … PURGE;”. This is done many, many times per load cycle, which is intended to run several times a day.
Something you should always keep in mind with the recyclebin is that the tables, indexes and their extents that go into the recycle bin do not get reported in several of the key data dictionary views. I did mention this in a blog post way back but maybe a whole post on it is called for.
So, the tablespace was being shattered by the constant creating and dropping of small tables. It was hidden from easy view due to how the recyclebin is (not) exposed in data dictionary views.
It is not good practice to constantly create and drop lots of tables. As well as the admittedly rather odd impact that this posting is all about, there is a lot of internal work involved for the oracle database in creating and dropping tables. It is an overhead best avoided. The client had good reasons for this approach but now they are going to look at the alternatives.
I’ve not actually proven in this posting that all those dropped tables is the actual cause of the slow performance in querying the free space in that tablespace {though the corroborative evidence is very strong}. Neither have I said how we cleared up the mess. I’ll cover both of those in the next post on this mini-thread.
Another interesting post, thanks Martin.
The moral of this story is to be careful of using the recycle bin I guess, or, if you are creating temporary table structures ensure you purge them after they are no longer in use.
Another solution may be to turn off the recycle bin at the session level where the tables are created.
Thanks Tony.
Yes, I think your take for the moral of the story is good. I think the recycle bin is one of those little aspects of Oracle that just drifts into the background – until it bites you.
Purging is of course good practice, if you know the drop is a certainty. Turning off the recyclebin is part of the next post. And why it is not just a case of turning it off…
I expect adding the characters O,P & C to the next post, though not in that order might make it more popular as well
FWIW I don’t think recycle bin objects *should* exist in the DD views, though I’d also have something to say about permanent temporary tables as used in the ETL as well. 
I’ll try that acronym hint Mr Litchfield
I think I agree with you about the appearance of the recyclebin objects in many dictionary views but there is some inconsistency there (which is what I really blogged about a while back) which bothers me – and I also think people need to be more mindful of the recyclebin. I think seeing the extents belonging to them when you want to see what extents are in a tablespace is better than not??? Hmmm, one to ponder
Isn’t it the case that when an object goes ito the recyclebin that it shows as being free space and it will be automatically purged from the recyclebin should it be necessary for Oracle to reuse those extents? In which case Oracle will look after itself, like it does with everything else. I don’t see the problem, Martin?
The recyclebin should be off by default.
“The recyclebin should be off by default”.
I turn it off on prod systems as NOTHING should be deleted without someone signing in blood on a production systems {unless it is me wanting it deleted, then it should just be deleted…}
Do you mean you have a personal rule that you turn it off or that the system arrives with the recyclebin off by default? It’s on by default
“oracle will look after itself, like it does with everything else”
Hmmmm, I know you and I know sarcasm. Others reading this comment may miss that, you naughty chap
The problem is, Neil, that this particular tablespace is full of deleted objects and that it’s not easy to spot that – and yes, the cause is a situation that I think most DBAs would say is a bad idea, ie creating and dropping hundreds of thousands of tables.
Querying the free space is slow – I would imagine that the automated freeing up of space by removing purged objects will be similarly slow and I have some anecdotal evidence today that this is the case. Certainly the proactive removal of such objects is slow, but that is half of the next post.
It should be off by default when Oracle is unpacked from the syrofoam pellets. Tell people it exists and let them turn it on. This isn’t a Windows laptop being operated by my octogenarian father. Although, some Oracle installations may seem like he set them up…
[...] Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE … [...]