Oracle Performance and Engineering

In a 10g (or, 9i) database with DB_BLOCK_SIZE 8KB, we create a tablespace with UNIFORM EXTENT allocation with each EXTENT sized 16KB.

CREATE TABLESPACE auto_test DATAFILE '/u0/oracle/oradata/G1010/auto_test1.dbf'--EXTENT MANAGEMENT
SIZE 20M UNIFORM SIZE 16K;

It is created fine.

Now, I try to create a table with a LOB column -

CREATE TABLE lob_test
(id NUMBER,
large_string CLOB)
TABLESPACE auto_test;

ERROR at line 1:
ORA-03237: Initial Extent of specified size cannot be allocated in tablespace (AUTO_TEST)

Now, let's drop and recreate the tablespace with an uniform extent allocation of 24KB.

DROP TABLE lob_test;
DROP TABLESPACE auto_test;

CREATE TABLESPACE auto_test DATAFILE '/u0/oracle/oradata/G1010/auto_test2.dbf'
SIZE 20M UNIFORM SIZE 24K;

CREATE TABLE lob_test
(id NUMBER,
large_string CLOB)
TABLESPACE auto_test;

Table Created.

So, to create a LOB table the tablespace extent must be greater than or equal to the value of THREE database blocks.

Jonathan Lewis explains why -

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

I traced the session for the LOB Table creation and found the following recursive SQLs are extra from the trace of non-LOB table creation -

1)
insert into icol$(obj#,bo#,intcol#,pos#,segcol#,segcollength,offset,col#,
spare1,spare2)
values
(:1,:2,:3,:4,0,0,0,:5,:6,:7);

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.00 0.00 0 0 0 0

WHY? I found the obj# of the LOB_TEST. This should be the BO# (Base Object# - on which the index is created). But when I query -

SELECT * FROM icol$ WHERE bo# = 2605313;

No Row Returned

Trace file correctly shows that 0 row was processed during execution. So, why Oracle is even firing an INSERT when it knows there will not be any record? Or is it that the DML on icol$ (a part of the c_obj$ cluster) is faster than the IF - ELSE logic they would have to do otherwise.

2)
insert into ind$(bo#,obj#,ts#,file#,block#,intcols,type#,flags,property,
pctfree$,initrans,maxtrans,blevel,leafcnt,distkey,lblkkey,dblkkey,clufac,
cols,analyzetime,samplesize,dataobj#,degree,instances,rowcnt,pctthres$,
indmethod#,trunccnt,spare1,spare4,spare2,spare6)
values
(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,
:22,decode(:23,1,null,:23),decode(:24,1,null,:24),:25, :32*256+:26,:27,:28,
:29,:30,:31,:33)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.01 0.00 0 2 3 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.01 0.00 0 2 3 1

So, 1 row inserted here in IND$ table.

The OBJ# for this entry in IND$ is 2605315 (boj# = LOB_TEST OBJECT# = 2605315).
The corresponding entry for this OBJ# from OBJ$ table -

SELECT obj#, dataobj#, owner#, name FROM obj$ WHERE obj# = 2605315;

obj# dataobj# owner# name
2605315 2605315 304 SYS_IL0002605313C00002$$

This object can *not* be searched through an usual query through DBA_OBJECTS.

SELECT * FROM dba_objects WHERE object_id =2605315;

No Row Returned.

This is Oracle internal placeholder for LOB Index.

3)

insert into lob$(obj#, intcol#, col#, lobj#, part#, ind#, ts#, file#, block#,
chunk, pctversion$, flags, property, retention, freepools)
values
(:1, :2, :3, :4, 0, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14)


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 2 5 1
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 2 5 1

Again, 1 row inserted in the LOB$.

SELECT obj#, col#, lobj#, ind# FROM LOB$ WHERE obj# = 2605313;

obj# col# lobj# ind#
2605313 2 2605314 2605315

2605313 is the LOB_TEST table. 2605314 is the LOB_OBJECT (on Column #2 of LOB_TEST) and 2605315 is the Oracle created LOB index (locator) on the LOB column.

After the three statements above Oracle inserts the space values into SEG$ -


update seg$ set type#=:4,blocks=:5,extents=:6,minexts=:7,maxexts=:8,extsize=
:9,extpct=:10,user#=:11,iniexts=:12,lists=decode(:13, 65535, NULL, :13),
groups=decode(:14, 65535, NULL, :14), cachehint=:15, hwmincr=:16, spare1=
DECODE(:17,0,NULL,:17),scanhint=:18
where
ts#=:1 and file#=:2 and block#=:3


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.01 0.00 0 15 3 3
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 6 0.01 0.00 0 15 3 3

We get the values for TS# (tablespace), file# and block# of the LOB_OBJECT from IND$ as -

SELECT obj#, dataobj#, ts#, file#, block#, bo# FROM ind$ WHERE bo# = 2605313;

obj# dataobj# ts# file# block# bo#
2605315 2605315 26 46 15 2605313

Again,
2605313 is the table containing LOB (TEST_LOB)
2605314 is the LOB object created by Oracle (SYS_IL0002605313C00002$$)
2605315 is the internal index on LOB column

So, when we query for the above ts#, file# and block# in SEG$ -

SELECT blocks, extents, iniexts, extsize FROM seg$ WHERE ts# = 26 AND FILE#= 46 AND BLOCK# =15;

blocks extents iniexts extsize
3 1 3 3

Thus, LOB objects are created with THREE database blocks to start with!


Yet another reason to let Oracle choose both the extent manegement (LOCAL) and allocation policy (AUTOALLOCATE) rather than picking values randomly.

I