Oracle Scratchpad

August 8, 2019

Free Space

Filed under: Execution plans,Oracle,Performance,Troubleshooting — Jonathan Lewis @ 1:27 pm BST Aug 8,2019

Several years ago I wrote a note about reporting dba_free_space and dba_extents to produce a map of the space usage in a tablespace in anticipation of messing about with moving or rebuilding objects to try and reduce the size of the files in the tablespace.  In the related page where I published the script I pointed out that a query against dba_extents would be expensive because (for locally managed tablespaces) it makes use of the structure x$ktfbue which generates its data dynamically by reading segment header blocks and expanding the extent lists it finds there. I also pointed out in a footnote to the original article that if you’ve enabled the recyclebin and have “dropped” some objects then there will be some space that is reported as “free” when it’s “not quite” free since the extents will still be allocated. This brings me to the topic for today’s blog.

While visiting a client site recently I came across an instance that was running a regular report to monitor available space in the database. Basically this was a query against the view dba_free_space. Surprisingly it was taking a rather long time to complete and the reason for this came in two parts. First, the recyclebin was enabled and had some objects in it and secondly there were no stats on the fixed object x$ktfbue.

In the case of the client the particular query produced a plan that included the following lines:

Id  Operation             Name              Rows    Bytes  Cost (%CPU)  Time
--  --------------------- ----------------  ----   ------  -----------  --------
63  HASH JOIN                               2785     212K     46  (85)  00:00:01
64    TABLE ACCESS FULL   RECYCLEBIN$       1589    20657      7   (0)  00:00:01
65    FIXED TABLE FULL    X$KTFBUE          100K    6347K     38 (100)  00:00:01 

This comes from the part of the view where Oracle calculates the size of all the extents of objects in the recyclebin so that they can each be reported as free space. Notice that in this plan (which will be dependent on the Oracle version, the system statistics, relevant object_statistics and various optimizer parameters) the optimizer has chosen to do a hash join between the recyclebin (recyclebin$) and the x$ structure and this has resulted in a “full tablescan” of x$ktfbue, which means Oracle reads the segment header block of every single segment in the entire database. (I don’t know where the row stats came from as there were no stats on x$ktfbue, and this plan was pulled from the AWR history tables so the query had been optimised and captured some time in the past.)

If there had been nothing in the recyclebin the hash join and two tablescans wouldn’t have mattered, unfortunately the recyclebin had been enabled and there were a few rows in recyclebin$, so the “tablescan” happened. Here’s a cut-n-paste from a much simpler query run against a fairly new (no 3rd party app) database running to give you some idea of the impact:

SQL> execute snap_events.start_snap

PL/SQL procedure successfully completed.

SQL> select count(*) from x$ktfbue;


1 row selected.

SQL> execute snap_events.end_snap
Session Events - 01-Aug 21:28:13
Event                                             Waits   Time_outs        Csec    Avg Csec    Max Csec
-----                                             -----   ---------        ----    --------    --------
Disk file operations I/O                              7           0           0        .018           1
db file sequential read                           5,239           0          14        .003           6
SQL*Net message to client                             7           0           0        .000           0
SQL*Net message from client                           7           0       1,243     177.562         572
events in waitclass Other                             3           1           0        .002           0

PL/SQL procedure successfully completed.

On my little laptop, with nothing else going on, I’ve managed to get away with “only” 5,239 single block reads, and squeezed them all into just 14 centiseconds (local SSD helps). The clients wasn’t so lucky – they were seeing tens of thousands of real physical reads.

The ideal solution, of course, was to purge the recyclebin and disable the feature – it shouldn’t be necessary to enable it on a production system (set parameter recyclebin to off) – but that’s an action that ought to require at least some paperwork and it needs an instance restart. In the short term gathering stats on the fixed table helped because the plan changed from a hash join with “tablescan” of x$ktfbue to a nested loop with an “indexed” access path, looking more like the following (from a query against just recyclebin$ and x$ktfbue)

| Id  | Operation                | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT         |                  |       |       |     4 (100)|          |
|   1 |  NESTED LOOPS            |                  |     7 |   182 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL      | RECYCLEBIN$      |     6 |    66 |     4   (0)| 00:00:01 |
|*  3 |   FIXED TABLE FIXED INDEX| X$KTFBUE (ind:1) |     1 |    15 |     0   (0)|          |

Predicate Information (identified by operation id):

This was still fairly resource-intensive for the client, but was something of an improvement – they had a lot more than 6 items in their recyclebin. You may be luckier than the client because the portion of the view definition that accesses x$ktfbue joins recyclebin$ to ts$ (the list of tablespaces) and if you’re lucky the arithmetic of the join might result in Oracle picking the nested loop join anyway (until the day it doesn’t).

Part of the problem, of course, is that x$ktfbue is one of the objects that Oracle skips when you gather “fixed object” stats – it can be a bit expensive to gather statistics for exactly the reason that querying it can be expensive … all those single block segment header reads.

If you want to check the current object statistics and gather them (as a one-off, probably) here’s some suitable SQL:

        table_name, num_rows, avg_row_len, sample_size, last_analyzed
        owner = 'SYS'
and     table_name = 'X$KTFBUE'



You probably shouldn’t have the recyclebin enabled in a production system but if you do, and if you also run a regular report on free space (as many sites seem to do) make sure

  • you have a regular routine to minimise the number of objects that it accumulates and
  • you gather statistics (occasionally) on x$ktfbue to minimise the workload due to the join between recyclebin$ and x$ktfbue.

Update (Dec 2020)

You can go further than simply improving things by gathering the appropriate statistics. Franck Pachot has (re-)published a note defining a variation of this view that allows for a very efficient access path for certain types of query.

1 Comment »

  1. […] that you’ve emptied the recyclebin before you […]

    Pingback by Shrink Tablespace | Oracle Scratchpad — August 25, 2019 @ 9:45 am BST Aug 25,2019 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Google photo

You are commenting using your Google 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

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by