Suggestions for reducing LIO on this query? - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Suggestions for reducing LIO on this query?

  1. #11
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Hello All

    Slimdave, the LIO thing is based on my own observations of autotrace and chapter 7 of 'Expert One-on-One' which gave me the re-org idea in the first place. I've recreated the test case from it below, as I think it's quite interesting:

    SQL> create table colocated (x int, y varchar2(2000)) pctfree 0;

    Table created.

    SQL> begin
    2 for i in 1 .. 100000
    3 loop
    4 insert into colocated values (i, rpad(dbms_random.random,75,'*'));
    5 end loop;
    6 end;
    7 /

    PL/SQL procedure successfully completed.

    SQL> create table disorganized nologging pctfree 0
    2 as
    3 select x,y from colocated order by y
    4 /

    Table created.

    SQL> alter table colocated add constraint colocated_pk primary key(x);

    Table altered.

    SQL> alter table disorganized add constraint disorganized_pk primary key(x);

    Table altered.

    SQL> set autotrace traceonly
    SQL> select * from colocated where x between 20000 and 40000;

    20001 rows selected.


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




    Statistics
    ----------------------------------------------------------
    152 recursive calls
    1 db block gets
    2933 consistent gets
    43 physical reads
    0 redo size
    1935140 bytes sent via SQL*Net to client
    108331 bytes received via SQL*Net from client
    1335 SQL*Net roundtrips to/from client
    4 sorts (memory)
    0 sorts (disk)
    20001 rows processed

    SQL> select * from disorganized where x between 20000 and 40000;

    20001 rows selected.


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




    Statistics
    ----------------------------------------------------------
    152 recursive calls
    3 db block gets
    21385 consistent gets
    1107 physical reads
    0 redo size
    1935140 bytes sent via SQL*Net to client
    108331 bytes received via SQL*Net from client
    1335 SQL*Net roundtrips to/from client
    4 sorts (memory)
    0 sorts (disk)
    20001 rows processed

    Tamilselvan, originally an IOT was my preferred approach but the primary key doesn't contain the columns that appear in the WHERE clause of the query which is why I went for the re-org (intending to go for an index clustered table in the longterm.) However, as my testing showed this had the effect of degrading the other indexes that previously benefitted from a good 'natural' clustering factor and means I have to scrap the idea of an IOT/Index Clustered Table/Re-org.

    Thanks

    Austin

  2. #12
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    Sorry, I see what you're getting at now. I could use an IOT as the replicated table instead of an index clustered table...

    Thanks

    Austin

  3. #13
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Many times DBAs forgot about PCTTHRESHOLD, INCLUDING and OVERFLOW clauses when creating IOT.
    If You know your application very well ( columns frequently selected), then carefully choose those 3 parameters values.

    I am sure IOT will definitely reduce the LIO.

    Tamil

  4. #14
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by slimdave
    but i can't think how LIO's would be affected.
    Except Table Access By ROWID thru Unique Index Scan, LIO(buffer gets) will be less if table data is ordered.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #15
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    The question is are we gonna reorganize a table every few months to keep the data ordered? Dont think so!

  6. #16
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by pando
    The question is are we gonna reorganize a table every few months to keep the data ordered? Dont think so!
    Well re-org of table once in a year for huge (more than 5GB data) is a wise thing.

    And as far as Medium tables are conserned, you can take up once in 6 months.

    And Small tables really dont require such things.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width