Jonathan,
Wow. I must say once again, your presentations were amazing. Thank you!
I had never even heard of _table_scan_cost_plus_one. Off to do some
digging into the documentation and Metalink. Fortunately I didn't have
to compete for the prize of your book as a copy has long been on my
technical bookshelf :)
Have a good trip home.
Rachel
- jonathan_at_jlcomp.demon.co.uk wrote:
> Well, I really enjoyed IOUG-A,
> but now that it's over, I can reveal
> how to convince Oracle 9.2 that a
> 300,000,000 row tablescan is cheaper
> than a primary key index unique scan.
>
> Many of you realised that the first
> step was optimizer_index_cost_adj = 10000.
> This made the index look very expensive.
>
> Many of you also hit on the idea of creating
> the index in a tablespace with a 2K block size
> to squeeze an extra branch level, and add that
> little (100) extra cost to the index.
>
> After that, people got a bit stuck, although
> setting db_file_multiblock_read_count was a
> common suggestion.
>
> One little fix, though, was to set:
> _table_scan_cost_plus_one = false,
> to make tablescans a little cheaper.
> Then I told Oracle that I had a 32 GHz
> CPU, and a disk capable of 5GB/s throughput
> by using dbms_stats.set_system_stats. And
> Oracle believed me:
>
> begin
> dbms_stats.set_system_stats('MBRc',32768);
> dbms_stats.set_system_stats('CPUSPEED',32768);
> dbms_stats.set_system_stats('SREADTIM',10);
> dbms-stats.set_system_stats('MREADTIM',10.01);
> ed;
>
> So Oracle decided that a massive tablescan
> was physically cheaper than an index scan,
> and that the CPU cost of checking all those
> rows was irelevant.
>
> QED
>
> Jonathan Lewis.
>
> Now booking for Tutorials pre/post
> Oracle World - SF, Sept 2003.
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: jonathan_at_jlcomp.demon.co.uk
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Fri May 02 2003 - 06:06:48 CDT
