Re: Bitmap I/O

Alistair,

I'm no expert in this area but I do know that cost is made of more than just I/O costs, it also contains an element relating to CPU as well. Also I/O portion is the optimizer's estimate based on the information available to it at parse time. If you want to look further into this particular case you could try the following:

1. Add some histograms as this will give Oracle more information to enable better "costing" of the SQL statement.
2. You could enable CBO tracing with event 10053 which will give you more insight into what the optimizer is doing.

The likes of Jonathan and Lex (he used to run the "Everything You Always wanted to know about the Optimizer" workshop at Oracle) etc. would be able to go into much more detail than me and could probably say exactly how the cost is calculated.

HTH Chris Dunscombe

www.christallize.com

Quoting "Finely, Alistair" <Alistair.Finely_at_BSkyB.com>:

> Consider the following table created on both a 9i and 10g Db (16K block
> size):
>
> CREATE TABLE AF_TEST ( a CHAR(200 BYTE), b CHAR(100 BYTE), c VARCHAR2(100
> BYTE) );
>
> CREATE BITMAP INDEX bi_af_test ON AF_TEST (c);
>
> Column 'c' contains 4 distinct values, each value will constitute 25% of the
> table.I have evenly distributed them and analyzed the table and index with
> dbms_stats. When I run:
>
> select * from af_test where c = <one of the distinct values>
>
> Oracle performs a full table scan (fair enough - 25% of the rows are being
> returned) and auto trace returns the following:
> Statistics
> ----------------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 36766 consistent gets
> 15794 physical reads
> 0 redo size
> 24127462 bytes sent via SQL*Net to client
> 593661 bytes received via SQL*Net from client
> 16040 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 240581 rows processed
>
>
> However if I force the use of the index autotrace returns this:
> Statistics
> ----------------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 21030 consistent gets
> 2766 physical reads
> 0 redo size
> 24141170 bytes sent via SQL*Net to client
> 593661 bytes received via SQL*Net from client
> 16040 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 240581 rows processed
>
> The cost of the full table scan was a fraction of that of the index, BUT why
> is the I/O so much less with the index. I have tried this with various row
> lengths so that the number of blocks populated changes, but the results are
> essentially the same. Why would the cost be higher, but the I/O be less?
>
> To populate the table I repeatedly ran the following inserts as a script to
> put about 1M rows in the table
> INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'England'
> FROM USER_OBJECTS
> INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Scotland'
> FROM USER_OBJECTS
> INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Wales' FROM
> USER_OBJECTS
> INSERT INTO AF_TEST (a, b, c) SELECT object_name, object_type, 'Northern
> Ireland' FROM USER_OBJECTS
>
>
> -----------------------------------------
> Information in this email may be privileged, confidential and is intended
> exclusively for the addressee. The views expressed may not be official
> policy, but the personal views of the originator. If you have received it
> in error, please notify the sender by return e-mail and delete it from your
> system. You should not reproduce, distribute, store, retransmit, use or
> disclose its contents to anyone. Please note we reserve the right to
> monitor all e-mail communication through our internal and external
> networks.
>
> --
> http://www.freelists.org/webpage/oracle-l
>

Chris Dunscombe

www.christallize.com

--
http://www.freelists.org/webpage/oracle-l