Oracle Tips and Tricks — David Fitzjarrell

October 31, 2006

Serial or Cereal? — follow-up

Filed under: General — dfitzjarrell @ 15:24

I reported a few days ago that serializable transactions do not generate errors when the underlying table is truncated although they quietly “error out” by returning incorrect results; Oracle has agreed this is a bug and should return an error. No patch has yet been issued, however I understand from knowledgeable sources this is on the way.

Apparently the squeaky wheel does get the grease. 🙂

October 23, 2006

Serial, or Cereal?

Filed under: General — dfitzjarrell @ 15:47

An interesting issue regarding serialisable transaction behaviour has reared it’s head in the newsgroup; the original situation is posted below:

Session 1 (isolation level read committed)

Insert into dd values(1);


Insert into dd values(2);


Session 2 (isolation level serializable)
Set transaction isolation level serializable
Select * from dd; (2 rows returned at this point)

Now issue another insert from session 1

Insert into dd values(3);

Now run the select again from session2:

Select * from dd;
(still return 2 rows)

Now truncate the table from session1:

Truncate table dd;
(issue implicit commit)

Now again run the following select from session 2:

Select * from dd; (This should still show 2
rows as read data from rollback segments since in serializable mode, it need to show the pre-image before the truncate but it is showing 0 rows)

This, by the way, is in 10.2.0. This behaviour also occurs in 9.2.0.x for table truncates, but not for table drops:

— 1) create table test_table as select object_id myid, object_name myname from all_objects where rownum < 100;
— 2) select count(*) from test_table; — … 99
— 3) commit;
— 6 A) drop table test_table;
— 6 B) truncate table test_table;
— *********************************
— 4) commit
ALTER SESSION set isolation_level=serializable
–5) select count(*) from test_table — … result = 99
— 7) select count(*) from test_table
— A) … ORA-00942: table does not exist
— B) … result = 0 !!

This should be considered a bug in Oracle, and should be addressed. Truncate should result in the same behaviour as a drop table (both commands being DDL) and cause the transaction to fail with an Oracle error (it should return an ORA-08177, in my opinion) as it shouldn’t silently ‘fail’ by returning 0 rows.

If you feel so inclined recreate the example on your system and report it as a bug to Oracle. Hopefully the more requests this gets for attention the faster it will get corrected.

October 17, 2006

Location, location, location

Filed under: General — dfitzjarrell @ 15:21

A popular question people new to Oracle ask involves the location of data in a table; the popular belief by many is that new data always goes at the end of the current data. This may not always be true as such behaviour is governed by the type of insert and by a mechanism involving two often misunderstood parameters, PCTFREE and PCTUSED.

How does the type of insert govern where the data resides? Using the /*+ append */ hint causes Oracle to do just that: append the data to the end of the current set, populating new blocks in the process. Such a process bypasses the pctfree/pctused mechanism and thus makes for a speedier insert of data as Oracle need not access the freelists to find possible locations for the new data amongst the old. Should a ‘plain vanilla’ insert be executed Oracle goes about the task of accessing the freelists (at least one for each block) to find and use (if possible) free space in already populated data blocks. The locations on these freelists is governed by two parameters mentioned earlier: PCTFREE and PCTUSED. So, what are these parameters and how do they work to trigger inserts into populated data blocks? Let’s start with PCTFREE and discover its purpose.

PCTFREE sets the threshold, indirectly, for the percentage of the data block which can contain new data. 100 – PCTFREE returns the percentage of the data block new data can occupy; the rest (PCTFREE) is reserved for updates to existing data in that block. So, for example, the default value for PCTFREE (10) reserves 10 percent of the available data block space for updates, and provides 90 percent for data inserts.

But, wait! Simply because PCTFREE is set to 10 doesn’t mean that when the block falls below 90% populated Oracle will automatically insert new data in that block. There is another parameter, PCTUSED, which governs when a populated data block can be considered available to accept new data. PCTUSED is the threshold which controls when a populated data block can be used for inserts; if this parameter is not explicitly set when a table is created it defaults to a value of 40. When the percentage of the block populated by data reaches or becomes less than PCTUSED that block is placed on the freelist. Oracle accesses the freelist during inserts to find available locations to store data; of course simply being available doesn’t mean Oracle automatically uses these locations as there may be insufficient space in the block to store the row or rows in question. The PCTUSED threshold triggers the access of the freelist during inserts; this threshold is evaluated for every populated block in a table, so there may be a number of blocks on the freelists for Oracle to search. Other populated blocks which haven’t met the PCTUSED setting will be bypassed.

I know, this sounds confusing. Let’s look at an example and see how this mechanism operates; for the purposes of this example we’ll set db_block_size to 8192, PCTFREE to 10 and PCTUSED to 50:

We have a brand new, never before used table into which we’ll insert data. What data is not important at this point, the issue is to discuss the mechanism.

Inserting data into this table for the first time provides us with 90% of the available block space for new rows. Estimating the total available space in an 8k block to be 8000 bytes (192 bytes for the header and other overhead) we have 7200 bytes available for new rows per block. As we insert data Oracle notes how much space each row occupies and how much of the 7200 bytes remains for the next insert. New data stops flowing into a given block when either the 7200 byte limit has been reached or when the next row which could be inserted into the block would put that block over the 7200 byte limit. Oracle disallows any new inserts to that block and proceeds to populate a new block subject to the same conditions. [PCTFREE and PCTUSED are set at the table level and apply to all blocks associated with that table; different tables can have differing PCTFREE/PCTUSED settings.] To confuse matters further since the block isn’t fully populated according to PCTFREE (presuming the 7200 byte limit hasn’t been reached) another insert, with a shorter row, can add that row to this same block because even though it wouldn’t take the larger insert the block has not yet been taken off of the freelist. It’s only removed from the freelist when the occupied space meets the 100 – PCTFREE setting.

Once a block is populated to the PCTFREE threshold (leaving no less than PCTFREE available for updates) no new data can enter that block via an insert until the populated percentage is equal to or is less than the PCTUSED threshold. In our populated data block we now delete some rows, leaving 60% of the block populated. As this is not less than the 50 to which PCTUSED is set Oracle does not make this location available to the next insert. Further deletions reduce that percentage further, until we reach 49% population of the data block. We have now reached the point where the existing block is put on the freelist and made available to Oracle for the next round of inserts. Presuming the incoming data will fit in the holes we left behind Oracle will reuse these locations until, you guessed it, the free space reaches the PCTFREE threshold (or, as stated earlier, no row will fit in the available space remaining). At this point the process starts over again, removing the block from the available list until such time as its populated space is at or below the setting for PCTUSED.

In a nutshell (so to speak):

Data inserted into block until PCTFREE threshold is reached.
Data block removed from consideration until deletes put the used space at or below PCTUSED.
Data block again available for inserts until PCTFREE threshold is reached.

And this repeats until the table no longer exists, or is placed in a read-only tablespace, or is archived to history … I think you get the idea.

This, of course, means there can be no ‘order’ to the rows in a heap table (the default table type in Oracle) since new data can be inserted in the middle of existing data which may be months old. The ROWID tells the tale of where this data is stored, and if the table contains a date column to track when data changes occur you will, more likely than not in an active table, find that newer data has ROWID values which once belonged to data originally loaded into the table and since deleted. So asking for, say, row 342 in a table is … silly, because a heap is not an ordered list.

Yes, this topic can be confusing, but patience is rewarded. Read through the text again, and again if necessary. All will be made clear … eventually.

Create a free website or blog at