jump to navigation

Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE Queries February 16, 2012

Posted by mwidlake in database design, internals, performance.
Tags: , , ,

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:

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

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
from dba_tablespaces where tablespace_name ='USERS'

------------------------------ -------------- ----------- ---------- ---------- ---------
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.


1. Tony Sleight - February 16, 2012

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.

mwidlake - February 16, 2012

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…

2. Niall Litchfield - February 16, 2012

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. 🙂

mwidlake - February 16, 2012

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

Neil Chandler - February 16, 2012

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.

mwidlake - February 16, 2012

“The recyclebin should be off by default”.
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 😦 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…}

“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.

Neil Chandler - February 17, 2012

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…

3. Oracle extents | Sybaritejourna - February 22, 2012

[…] Dropped Tables, Hiding Extents and Slow DBA_FREE_SPACE … […]

4. Martin Preiss - July 16, 2015

a small addition: I faced the same problem in 11.2 these days (and purged lots of objects from the bin) and then checked the behaviour in there the plan is an adaptive plan and the optimizer switches after some executions from the slow Nested Loops to a fast Hash Join (to avoid the slow repeated access on X$KTFBUE). Furthermore the views seems to have lost some history hints (including ordered) – looks better than before.



5. belkacem - November 9, 2015


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: