Single row table and LIO

From: dejan <dejan.colja_at_siol.net>
Date: 9 Nov 2004 07:43:43 -0800
Message-ID: <107f8a43.0411090743.79e25949@posting.google.com>

Hi
--

I have problem with LIO on small sigle row tables. Table was created in tablespace LMT/ASSM/Uniform and we are using Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production on unix.
--

Tablespace DATA_S have 8k block and extent = 128k (16 blocks)
--

Table with single record was created in this tablespace, Average record size computed by DBMS_STATS = 178 bytes. This means that at least 5 such records should stand in one block.
--

Immediately after inserting 1 row HWM goes up to block 15 ?? Full table scan all the time result in 15 LIO blocks. ?? !!!! The most frustrating is fact that in production database we need just 3 LIO
for the same table (exactly the same tablespace definition and query) and I don't know why. (it could be opposite !!)
--

Thanks for any explanation or hint.

Regards DEJAN

Test Case:
skbb_at_dev> select * from v$version
2 /

BANNER

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Compaq Tru64 UNIX: Version 9.2.0.4.0 - Production NLSRTL Version 9.2.0.4.0 - Production

5 rows selected.

Elapsed: 00:00:00.00
skbb_at_dev> select * from dba_tablespaces where tablespace_name = 'DATA_S';

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT

MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_
------------------------------ ---------- -------------- -----------

----------- ------------ ---------- --------- ---------
--------- --- ---------- --------- --- ------ --------
DATA_S 8192 131072 131072 1 2147483645 0 131072 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO DISABLED

1 row selected.

skbb_at_dev> create table dd_test (id number, id_desc varchar2(100)) tablespace data_s;

Table created.

Elapsed: 00:00:00.06
skbb_at_dev> insert into dd_test(id, id_desc) values (1, rpad('x',100, '.'));

1 row created.

Elapsed: 00:00:00.00
skbb_at_dev> commit;

Commit complete.

Elapsed: 00:00:00.01

skbb_at_dev> 
skbb_at_dev> set autotrace on
skbb_at_dev> select * from dd_test;
 
        ID ID_DESC


---------- ----------------------------------------------------------------------------------------------------

         1 x...................................................................................................

1 row selected.

Elapsed: 00:00:01.00

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 TABLE ACCESS (FULL) OF 'DD_TEST' Statistics

          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        453  bytes sent via SQL*Net to client
        426  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

skbb_at_dev> @show_space

Segment Owner ....... SKBB
Segmenta Name ....... DD_TEST
Segment Type ........ TABLE

Partition Name ......

 *==========================================================
 * Database: DEV  04.11.2004 - 10:36:18
 *==========================================================
 *  Segment    :SKBB.DD_TEST -
 *  Type       :TABLE
 *==========================================================

Tablespace :

  Name                - DATA_S
  Space Management    - AUTO
  Block Size          - 8k
  Initial (bl/size)   - 16 / 128k
  Next (bl/size)      - 16 / 128k

Segment :

  Pct_Free          - 10
  Pct_Used          -
  Ini_Trans         - 1

Initial (bl/size) - 16 / 128k
Next (bl/size) - 16 / 128k

  Num Rows        -
  Blocks          -
  Empty Blocks    -
  Avg Space       -
  Chain Count     -
  Avg Row Length  -
  Last Analyzed   -

...........

  Rows/bl (smpl.1000) - 1
  Cnt (Dba_Extents )  - 1
  Aloc.size (bl/size) - 16 / 128k

HWM :

 Total Blocks  (TB) ....................     16 (128k)
 Unused Blocks (UB) ....................      0 (0k)
** HWM ** ..............................     15 (TB-UB-1)

...........
Below HWM ....

 Number of Unformated Blocks ...........      0
 Number of 00-25 %free Blocks ..........      0
 Number of 25-50 %free Blocks ..........      0
 Number of 50-75 %free Blocks ..........      0
 Number of 75-   %free Blocks ..........     13
 Number of 100% full Blocks ............      0

...........

 Last Used Ext FileId...................4
 Last Used Ext BlockId..................272872
 Last Used Block........................16

PL/SQL procedure successfully completed. Elapsed: 00:00:03.02 Received on Tue Nov 09 2004 - 09:43:43 CST