Re: LOB segment space usage

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 17 Aug 2007 23:11:58 +0200
Message-ID: <46C60F1E.80101@gmail.com>

andreik schrieb:
> On Aug 17, 5:29 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>> andreik schrieb:

I can hardly explain your results, because my tests can't confirm them SQL> SET SERVEROUTPUT ON
SQL> SELECT * FROM V$VERSION
2 /

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT VALUE
2 FROM NLS_DATABASE_PARAMETERS
3 WHERE PARAMETER = 'NLS_CHARACTERSET' 4 /

VALUE

AL32UTF8 SQL> CREATE TABLESPACE smallblock DATAFILE '/opt/oracle/oradata/ORA102/smallblock01.dbf' SIZE 10M

2 EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO BLOCKSIZE 2K
3 /

Tablespace created.

SQL> create table t1 (i int, b clob)

2 lob (b) store as t1_lob (

   3          chunk 2K
   4          disable storage in row
   5          tablespace data2k
   6          pctversion 0)

7 /

Table created.

SQL> insert into t1 values (1, rpad('*',994,'*'))

2 /

1 row created.

SQL> exec show_space('T1_LOB',user,'LOB')

Unformatted Blocks .....................              27
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               1
Total Blocks............................              32
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               6
Last Used Ext BlockId...................              97
Last Used Block.........................              32

PL/SQL procedure successfully completed.

SQL> truncate table T1

2 /

Table truncated.

SQL> insert into t1 values (1, rpad('*',995,'*'))

2 /

1 row created.

SQL> exec show_space('T1_LOB',user,'LOB')

Unformatted Blocks .....................              26
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               2
Total Blocks............................              32
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               6
Last Used Ext BlockId...................              97
Last Used Block.........................              32

PL/SQL procedure successfully completed.

As everybody can see - 994 '*' characters fit into one block, 995 no more. And here is what i see in the blockdump - as you may guess, the '*' is represented as 0x2A:

...snipped

Auxillary Map

Extent 0 : L1 dba: 0x01800021 Data dba: 0x01800025

Second Level Bitmap block DBAs

DBA 1: 0x01800023

buffer tsn: 6 rdba: 0x01800025 (6/37)
scn: 0x0000.001e6e00 seq: 0x02 flg: 0x04 tail: 0x6e002802 frmt: 0x02 chkval: 0xb3cb type: 0x28=PAGETABLE MANAGED LOB BLOCK Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0CD97600 to 0x0CD97E00

CD97600 00006228 01800025 001E6E00 04020000  [(b..%....n......]
CD97610 0000B3CB 0000D388 01000000 01000000  [................]
CD97620 00002E71 00000000 00000000 00000000  [q...............]
CD97630 01800021 00000000 2A002A00 2A002A00  [!........*.*.*.*]
CD97640 2A002A00 2A002A00 2A002A00 2A002A00  [.*.*.*.*.*.*.*.*]
         Repeat 122 times

CD97DF0 2A002A00 2A002A00 2A002A00 6E002802 [.*.*.*.*.*.*.(.n] Long field block dump:
Object Id 54152
LobId: 00010001712E PageNo 0
Version: 0x0000.00000000 pdba: 25165857 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a

     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
     00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a 00 2a
-----------------------------------

...snipped

So, from 4 sides - block dump, dbms_space package,Tanel Põder Whitepaper and last but not least - Oracle Documentation i get consistent results saying , that characters are stored in lob by 2 bytes per character - and from your side the opposite.
Now tell me please - what should i rely on?

Best regards

Maxim Received on Fri Aug 17 2007 - 16:11:58 CDT