Re: SQL Challenge - how to flog the CPU

Yes -
It's under

    Miscellaneous > Old Wives Tales
    Item 2 about buffer hit ratios.

In summary:
create table jpl1 (n primary key)
 organization index
as select rownum n
 from all_objects where rownum <= 33
;

Then run the following script

set pagesize 0
set linesize 20
set trimspool on
set termout off
set arraysize 2000

select n from jpl1
connect by n > prior n
start with n = 1

The recursive descent on the CONNECT BY generates VAST volumes (in this case 2^32) logical I/Os (and its worse if you don't use an IOT or a PK index).

--

Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk

Doug Cowles wrote in message <378D13F4.5E863DA1_at_bigfoot.com>...
>Did you post this yet?
>
>- Dc
>
>Jonathan Lewis wrote:
>
>> For those of you who feel that there
>> db block buffer hit ratio is too small -
Received on Thu Jul 15 1999 - 01:00:29 CDT