Free Space

Question – How can you have a single file in a single tablespace showing multiple free extents when there are no objects using any space in that file ? For example, from an 11.1.0.7 database:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_8K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_8K               3        128    1048576        128            3
TEST_8K               3        256    1048576        128            3
TEST_8K               3        384    1048576        128            3
TEST_8K               3        512  130023424      15872            3

4 rows selected.

The answer in this case is simple – here’s what I did just before running my query:

SQL> create table t1(n1 number);

Table created.

SQL> create table t2(n1 number);

Table created.

SQL> create table t3(n1 number);

Table created.

SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

SQL> drop table t3;

Table dropped.

SQL>

This test is on 11.1.0.7; test_8k is my default tablespace, uses an 8KB block size (did you spot the clue) with 1MB uniform extents. The segments created immediately and I haven’t purged my recyclebin. Because I’ve dropped the tables Oracle includes their space in the “free space” views, but because I need to be able to flash them back into existence the segments can’t be coalesced into the adjacent free space, and they will also be reported in dba_segments.

Here’s a harder one – there are NO objects in this tablespace, and nothing hiding in the recyclebin:

SQL> select
  2          *
  3  from    user_free_space
  4  where
  5          tablespace_name = 'TEST_2K'
  6  order by
  7          file_id, block_id
  8  ;

TABLESPACE_N    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
------------ ---------- ---------- ---------- ---------- ------------
TEST_2K               7        512   58720256      28672            7
TEST_2K               7      29184   58720256      28672            7
TEST_2K               7      57856   58720256      28672            7
TEST_2K               7      86528   32505856      15872            7

And here’s the statement I executed (as SYS) just before I ran the query – so no chance that there’s anything hidden in the file:

create
        tablespace test_2k
        datafile 'C:\ORACLE\ORADATA\d11g\d11g\test_2k.dbf'
        SIZE 200M reuse
        blocksize 2k
        extent management local
        uniform size 4k
        segment space management manual
;

Update: 26th Feb

I’m sitting in Munich airport and boarding starts in 10 minutes, so just enough time to give an answer. I’ll start with a block dump of block 3 of the data file.

Block dump from disk:
buffer tsn: 22 rdba: 0x01c00003 (7/3)
scn: 0x0b86.06d63ae4 seq: 0x01 flg: 0x04 tail: 0x3ae41e01
frmt: 0x02 chkval: 0x4e8a type: 0x1e=KTFB Bitmapped File Space Bitmap
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0C112200 to 0x0C112A00
C112200 0000621E 01C00003 06D63AE4 04010B86  [.b.......:......]
C112210 00004E8A 00000007 00000200 00000000  [.N..............]
C112220 00000000 00003800 00000000 00000000  [.....8..........]
C112230 00000000 00000000 00000000 00000000  [................]
        Repeat 123 times
C1129F0 00000000 00000000 00000000 3AE41E01  [...............:]
File Space Bitmap Block:
BitMap Control:
RelFno: 7, BeginBlock: 512, Flag: 0, First: 0, Free: 14336
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000
...
0000000000000000 0000000000000000 0000000000000000 0000000000000000
0000000000000000 0000000000000000 0000000000000000 0000000000000000

This is a locally managed tablespace, so the third block of the file is the first bitmap space management block for the tablespace. The tablespace uses a 2KB block size (so the amount of space in the block for the bit map is slight under 2KB) and a 4KB uniform extent size (so each bit in the map represents 2 blocks/4KB in the tablespace).

As you can see from line 15, the first bit represents the extent starting at block 512, and there are 14,336 bits available (1,792 bytes), so the bitmap in the next block would start at block 512 + 2 * 14,336 = 512 * 28,672 = 29,184. This lines up exactly with the first chunk of free space reported in dba_free_space above.

As one of the comments indicated – the code that populates x$ktfbfe is probably called once for each bitmap space management block, and it doesn’t seem to bother trying to “coalesce” two free space fragments identified by adjacent bitmap blocks.

Row sizes 2

In an earlier post I showed you how you could generate SQL to analyze the distribution of row sizes in a table. In the introduction to the code I made a comment about how it failed to “allow for nulls at the end of rows”; a feature of Oracle storage that isn’t commonly known is that a set of consecutive null columns at the end of a row take up no storage space, while any null columns followed by a non-null column take up one byte (holding the value 0xFF) per column so that Oracle can “count its way” through the null columns to the non-null column. Consider this example:

Fragmentation ?

Here’s a simple piece of SQL that could, in theory, compare the current size of  a table with the size it might be after a call to “alter table move” – and it’s followed by the results for a table that’s current in the database that I’m looking at:

select
	blocks, num_rows, avg_row_len, pct_free,
	ceil(num_rows * avg_row_len / (8000 * ((100 - pct_free)/100))) blocks_needed
from
	user_tables
where
	table_name = 'T1'
;

    BLOCKS   NUM_ROWS AVG_ROW_LEN   PCT_FREE BLOCKS_NEEDED
---------- ---------- ----------- ---------- -------------
        25       1000          22         10             4

PX and system allocation

A few years ago (2007) I wrote about a problem that could appear when you mixed parallel execution with system managed extent allocation. A couple of years later I added a note that Christian Antognini had observed a patch in 11.1.0.7 that addressed the specific issue I had raised. Today, thanks to an email exchange with Christo Kutrovsky of Pythian, I can report that there is a variation of this issue still available even in 11.2.0.3.

The basic problem is that you can end up with a very large number of very small extents, leading to poor performance in parallel queries and a significant waste of space in a data segment. Here’s a simple, though not particularly realistic, way to demonstrate the problem.

Fragmentation 4

This note is part four of a four-part series, and covers Index fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 – 4
2. Disk and Tablespace Fragmentation
3. Table Fragmentation
4. Index Fragmentation – this bit

4. Index “fragmentation”.

The multiple extent and ASSM “fragmentation” that I described in the previous article about table fragmentation applies equally well to indexes, of course, and matters in just the same way – i.e. hardly ever.

Fragmentation 3

This note is part three of a four-part series, and covers Table fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 – 4
2. Disk and Tablespace Fragmentation
3. Table Fragmentation – this bit
4. Index Fragmentation

3. Table “fragmentation”.

In the introduction we discussed one type of table fragmentation that doesn’t (usually) matter – the fragmentation of a table into multiple extents. Here’s a funny thought – ASSM (automatic segment space management) introduces another form of table fragmentation that usually doesn’t matter.

Fragmentation 2

This note is part two of a four-part series, and covers Disk and Tablespace fragmentation. The whole series is as follows

1. Introduction – with links to parts 2 - 4
2. Disk and Tablespace Fragmentation – this bit
3. Table Fragmentation
4. Index Fragmentation

2.1 Disk “fragmentation”.

Tablespaces are made up of files, and files are stored on discs – which are often “logical volumes” rather than real devices. When you issue a (real) disc read request, the largest amount of data you can get off a (real, physical) disc in a single physical action is something like 300KB to 500KB – the content of a single circular track on a single platter of a disc.

Fragmentation 1

This note started life as a nutshell until I realised that it was going to be more of a coconut than a hazel nut and decided to turn it into a short series instead. I should manage to  post  four parts over the next two weeks:

1. Introduction (this bit)
2. Disk and Tablespace Fragmentation
3. Table Fragmentation
4. Index Fragmentation

Shrink Tablespace

Here’s an example of a theme that appears on the OTN database forum from time to time (I haven’t included a link to it because it’s just one example of many similar questions):

“I have a user tablespace allocated for 3-4 schemas. As I urgently needed space on hard disk I had to remove one of the schema(drop user). Now this tablespace is shown as 70% filled. I want to reduce allocated space to it.”

Free Space

I’ve just seen the following question on OTN:

I had a log table which was about 30G in Production, since it was growing in size we decided to drop it. After dropping it we can’t see the space being freed. Even at OS level we can’t see any reduction in filesize.Can you please explain and/or help in this?

This prompted me to come up with the following analogy.

Index Fragmentation

Here’s a thought for the weekend:

When people talk about “index fragmentation”, what do they mean, and why do they care ?

I often see email or forum posts from people claiming that their indexes are fragmented and need to be rebuilt – but they rarely explain (even when asked) what they mean by “fragmented”, and how they have measured the “fragmentation”, and why they think they have evidence that the index needs to be rebuilt.

So if you are accustomed to talking about indexes being “fragmented”, would you let me know what you mean, and how you measure “fragmentation”. (I can think of three or four interpretations for the term – but I’m interested to hear from people who actually use it.)

[Further reading on Fragmentation]

Ancient History

I’ve just been browsing through a subdirectory on my laptop that has survived many migrations from machine to machine over the years – even though some of the material needs programs that probably don’t exist any more.

This slide, from a presentation I gave at the UKOUG annual conference in 1996, raised a wry smile. The title of the presentation was “Fact, Folklore, or Fairy-table” – possibly the first ever “Mythbuster” presentation on the Oracle scene. The title of each slide declared a popular belief, and the rest of the slide commented on the sense or (usually) lack thereof in the belief.