OraFAQ Forum: Performance Tuning » Too much LIO from single row table

Home » RDBMS Server » Performance Tuning » Too much LIO from single row table

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Too much LIO from single row table [message #65593]

Wed, 03 November 2004 22:30

dejan
Messages: 2
Registered: November 2004

Junior Member

Hi,
I have problem with sigle row tables.
Table belongs to tablespace LMT/ASSM/Uniform and we are using Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production on unix.
--
Tablespace TBS_SMALL have 8k block and extent = 128k (16 blocks)
--
I have a table with single record in this tablespace, Average resord size computed by DBMS_STATS = 178 bytes.
This means that at least 5 such records woud stand in one block.
--
But full table scan all the time result in 16 LIO blocks. ?? !!!!
--
Thanks for any explanation or hint.

Report message to a moderator

Re: Too much LIO from single row table [message #65601 is a reply to message #65593]

Thu, 04 November 2004 03:26

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

an FTS does the sequential read (many blocks at a time), which is considered the fastest.
an Index scan does a random read (and single block) which is considered the slowest.

To reduce LIO please look into sort_area_size , db_file_multi_block_read_count etc.
Run a statspack report.
Look for the buffer gets in topsql.
Idea is to reduce this buffer gets.
You reduce the buffer gets, your LIO is reduced!.

Report message to a moderator

Re: Too much LIO from single row table [message #65610 is a reply to message #65601]

Mon, 08 November 2004 00:59

dejan
Messages: 2
Registered: November 2004

Junior Member

Did you mean FTS <--> scattered read .. ??
--
Anyway I think, that problem is in Oracle Space Management. Immediately when I inserted row in fresh created table HWM jumps to 15. Here is key to high LIO.

Test Case:
skbb@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@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@dev> create table dd_test (id number, id_desc varchar2(100)) tablespace data_s;

Table created.

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

1 row created.

Elapsed: 00:00:00.00
skbb@dev> commit;

Commit complete.

Elapsed: 00:00:00.01
skbb@dev>
skbb@dev> set autotrace on
skbb@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

Report message to a moderator

Re: Too much LIO from single row table [message #65611 is a reply to message #65610]

Mon, 08 November 2004 04:04

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

if You are considering the consistent gets
>>15 consistent gets
the optimizer is doing exact it needs to do.
Becuase the there is ONE record and CBO uses a FTS ( FULL TABLE SCAN).
throw more records into the table.
create an index
analyze the table and index
query the table with where clause on indexed column.

You can see the decrease in consistent gets.
acutally some times ( depending on query) and index scan can reduce the consistent gets.

I did the same ...

-- a table with index
-- table and index are analyzed/

mag@mutation_mutation > desc dd_Test
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 ID                                                             NUMBER
 ID_DESC                                                        VARCHAR2(100)

mag@mutation_mutation > column id_desc format a20
mag@mutation_mutation > select distinct(id) from dd_test;

        ID
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9

9 rows selected.

mag@mutation_mutation > set autotrace on
mag@mutation_mutation > select * from dd_test where id=2;

        ID ID_DESC
---------- --------------------
         2 x...................
           ....................
           ....................
           ....................
           ....................

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DD_TEST'
   2    1     INDEX (RANGE SCAN) OF 'DDTEST_ID' (NON-UNIQUE)

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

--
-- NOw another table without any index NOT analuzed
--- with ONE record..
-- YOu can see CBO went for a FULL TABLE SCAN and with
--  high consistennt gets.

  1* insert into mydd(id, id_desc) values (1, rpad('x',100, '.'))
mag@mutation_mutation > /

1 row created.

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE

Statistics
----------------------------------------------------------
          2  recursive calls
         22  db block gets
          3  consistent gets
          2  physical reads
       1188  redo size
       1019  bytes sent via SQL*Net to client
       1073  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

mag@mutation_mutation > select * from mydd;

        ID ID_DESC
---------- --------------------
         1 x...................
           ....................
           ....................
           ....................
           ....................

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'MYDD'

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

mag@mutation_mutation >

Report message to a moderator

Previous Topic:	PGA tuning - Oracle 9i
Next Topic:	Space allocation to TEMP LOB

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 22 23:25:42 CST 2024