JL Computer Consultancy

Practical Oracle 8i - Building Efficient Databases

Chapter 17: Handling large objects (LOBs).


Addenda

Temporary LOBs. (06-Mar-2002)

Problems with character sets. (01-Feb-2002)

Bug appending to LOBs (31-Mar-2001)

LOBs and the LOBINDEX

LOBs and uniform locally managed tablespaces

 

Errata

Back to Book Index

 

Temporary Lobs

I have suggested (p.385) a way of using temporary LOBs to build a report in memory before storing it in the database for subsequent Web-based access. The strategy seems quite a nice idea; however, experimenting recently on Oracle 9.0.1.2, I came across a surprising performance hit, which also showed up on Oracle 8.1.7. (I'm fairly sure it wasn't so obvious on 8.1.5 - but perhaps I'm wrong).

The issue relates to whether you cache the the temporary LOB or not. The following sample code can be used to demonstrate the problem.

declare
	m_lob		clob;
	m_buf_size	integer;
	m_buffer	varchar2(100);
begin
	dbms_lob.createtemporary(
		lob_loc	=> m_lob,
		cache	=> true,		-- this is the line to watch
		dur	=> dbms_lob.call
	);
	for r in 1..500 loop			-- note the 500 iterations
		dbms_lob.writeappend(
			lob_loc	=> m_lob,
			amount => 1000,
			buffer => rpad('x',999,'x') || chr(10)
		);
	end loop;
	
	dbms_lob.freetemporary(m_lob);
end;
/

As you can see, this script creates a temporary lob, and then appends 500 lines of of 999 characters to it. On my machine, this takes less than one tenth of a second to complete - unless I switch the cache parameter from true to false, in which case it suddenly takes about 14 seconds to complete.

A quick trace of activity in the slow case shows that Oracle is spending a lot of time waiting for 499 direct path read (lob) waits and a little extra waiting for 500 direct path write waits. On top of this, every cycle through the loop requires an MR (media recovery) lock to be acquired, and a surprisingly large amount of latching - particularly on a couple of unusual items, the FOB s.o. list latch, and the loader state object free latch, and in fact also uses significantly more CPU than the cache option.

However, the cache option is far from ideal. In either case, I did a surprising number of db block gets, db block changes and consistent changes (why was the process trying make itself read-consistent with itself !) but the cached version did significantly more of each, as it had to cater for about 500K, or 135 blocks of LOB moving through the buffer.

Frankly I was a little disappointed with both results - I had expected Oracle to do something very clever with memory equivalent to the sort_area_size in much the same way that it appears to when using global temporary tables.

:

Top of page

 

Problems with Character sets:

I have a demonstration (p.381-382) of how easy it is to load a database LOB with the contents of an operating system file using the packaged procedure dbms_lob.loadfromfile(). However, in these days where increasing numbers of database systems are designed with international character sets in mind, this can cause a problem that was recently pointed out to me by Connor McDonald (www.oracledba.co.uk). There is a specific note in the PL/SQL Supplied Packages manual that warns of this problem, but it is something that is easy to overlook in the first stages of experimentation (I certainly failed to take any notice of it until the problem was pointed out to me). I quote:

	Note: The input BFILE must have been opened prior to using this
	procedure. No character set conversions are performed implicitly
	when binary BFILE data is loaded into a CLOB. The BFILE data
`	must already be in the same character set as the CLOB in the
	database. No error checking is performed to verify this.

If you load typical text files into CLOB columns in (for example) a UTF-8 database, the text is likely to be stored on disc as single byte characters, but UTF-8 is expecting double-byte characters. In effect you end you with half the number of CLOB characters you are expecting, all of which are complete garbage (see Metalink bug 1576747 for further details). The suggested workaround is to use SQL*Loader to load the CLOB data. An alternative strategy suggested by Connor was to use a combination of calls to

	dbms_lob.read(l_bfile,amt,v_offset,buf);
	vc := utl_raw.cast_to_varchar2(buf);
	dbms_lob.writeappend(v_lob,amt,vc);

This reads the file in chunks, then casts each chunk into a varchar2() local variable before appending this varchar2() to the CLOB. Furthermore, if you define a temporary LOB to hold the ever-growing object, and only transfer it to a permanent LOB when it is completely built, this will probably help to reduce the impact on the redo logs, control files, and (possibly) buffer cache of the continuous update to a permanent LOB.

Top of page

 

Bug appending to LOBs:

I have suggested (p.385) a way of using temporary LOBs to build a report in memory before storing it in the database for subsequent Web-based access. If you have tried this approach, you may have come across bug 122619, which applies to Oracle 8.1.5 and 8.1.6 (but appears to be fixed in 8.1.7.0, even though the bug note suggests that the fix is in the mythical 8.2).

The problem is an esoteric boundary condition applying to the dbms_lob.write_append() function. The details are not to clear from the bug note, but I believe it relates to appending data to LOB at the end of a CHUNK which is full, and also requires Oracle to create a new index entry for the LOB in the LOBINDEX. Obviously this won't happen all that often, but when it does, the session crashes.

Michael Sorenson (mailto:michael@garfield.dk) who informed me of this bug also sent a script (slightly modified below) to demonstrate the problem. To make sure the problem is going to happen, it builds a temporary LOB (which means the CHUNK size is one Oracle block) one byte at a time. The script may take a few minutes to run to its crash point.

rem
rem	bug_1222619.sql
rem	Supplied by michael@garfield.dk
rem	Michael Garfield Sorenson
rem

declare
	c_lob	clob;
begin
	dbms_lob.createtemporary(c_lob,true,dbms_lob.session);
	loop
		dbms_lob.writeappend(c_lob,1,'c');
		exit when dbms_lob.getlength(c_lob) > 200000;
	end loop;
	dbms_output.put_line('Looks like you have 8.1.7');
	dbms_lob.freetemporary(c_lob);
exception
	when others then
		dbms_output.put_line(
			'Exception: ' || to_char(sqlcode) ||
			' - length of lob ' || dbms_lob.getlength(c_lob)
		);
		begin
			dbms_lob.freetemporary(c_lob);
		exception
			when others then
				null;
		end;
end;
/

Top of page

 

LOBs and the LOBINDEX:

If a LOB is defined as 'enable storage in row' the first few chunk pointers (up to 12 it seems) are stored in the row, so for collections of small LOBS, the lobindex may never be used at all. If the LOB is defined as 'disable storage in row' then no space is used in the base table for chunk pointers.

Top of page

 

LOBs and uniform locally managed tablespaces.

A recent discussion on the Metalink Forum (Oracle Server Enterprise Edition) raised a little issue that may cause a little hassle in development. Someone was trying to create a table containing a LOB, and wanted the LOB segment to be in a locally managed tablespace with uniform sizing. They were getting the following Oracle error when trying to create the table:

	ORA-03237: Initial Extent of specified size cannot be allocated.

The reason for this was the combination of their block size, and the uniform size chosen for the tablespace. A quick test with a database built on 8K blocks, and a tablespace with a uniform size 16K soon reproduced the problem. Of course, given that LOBs are usually used for 'large objects', it is a little unlikely that you really want to build them in a tablespace with an extent size as small as 16K, however there are always reasons for doing the unexpected - not the least being the need for a small test suite.

Basically the problem is that the first extent of a LOB must be at least three blocks long. I mention (p.369) that a LOB segment includes a bitmap that tells Oracle very quickly which chunks in the segment are free and which are used. This was a slight simplification - the bitmap may be scattered all over the segment as new extents are added. I believe that Oracle actually has to maintain two bitmaps - and the first one is fixed as the second block in the segment and is used solely to tell Oracle where to find the other bitmap.

So when you create a LOB, Oracle has to allocate at least the first three blocks - one for the segment header block, one for the master bitmap, and one for 'any other data'. In fact, things can be a little more subtle - if you decide to allocate multiple freelist groups as part of the lob storage clause (it seems a little unlikely that you would actually need to do so unless running Oracle Parallel Server), then your initial extent has to be big enough for these as well, so if you set freelist groups 2, then your initial extent would have to be at least 5 blocks (1 seg header, plus 2 freelist groups, plus 1 master bitmap, plus one spare).

Top of page


Errata

The largest legal value for CHUNK is 32K - my sample text (p. 367 and following) uses 64K. I also hypothesise a 6MB chunk size in a 10MB extent (p.368), but this is a non-event. The largest chunk size appears to be dictated by the largest possible Oracle block size. It seems a little surprising that you can't set a chunk size up to at least 1MB, given that some operating systems are able to handle single I/O requests of 1MB, however with the read-ahead algorithms that Oracle is now using, you may find that a 32K chunk read often results in a read-ahead that gives you most of the benefit of a large chunk-size anyway.

I state that all current entries in the lobindex contain 4 chunk pointers (p.368). On subsequent tests this seems to be 8 chunk pointers for all entries other than the first. It is possible that this is dependent on block size, or even dependent on platform. I will also be reviewing the accuracy of my claim (fig 17-3 p.373) for the number of chunks listed in a lobindex entry for out-of-date 'SCN-loaded' chunks

Top of page.


Back to Book Index