Re: Poll: How fast is YOUR database in rows per second, on a full bore table scan???

dba_222_at_yahoo.com wrote:
> Poll: How fast is YOUR database in rows per second, on a full bore
> table scan???
>
> This is for those who like Tom Kyte's work, and empirically get their
> metrics.
>
>
> Quoting:
>
> ftp://ftp.software.ibm.com/eserver/benchmarks/FDC_BC_CompAnalysis_Dec2004.pdf,
>
> "The 8-node completion time for the test was 24 minutes. To put this
> in perspective, returning this query in 24 minutes means Oracle9i RAC
> scanned and sorted roughly
> 87,000 rows per second
> per CPU or 1.4 million rows per second clusterwide. "
>
>
> This makes for an interesting question. These days, just how fast
> -does- Oracle retrieve data for a full bore, non-indexed table scan???
> In terms of rows per second?
>
>
> Yes, we already know how to speed queries up.
>
> And yes, we know "it depends" on this, that, the other thing,
> your degree of speculation, the gravitational pull of the moon, etc.
>
>
> But, these days, just how fast is Oracle for the slowest possible
> query???
>
>
> Please pick a nice big non-partitioned table (not view) that you have,
> at least a few
> million rows. Modify the script below, run it in your database, and
> report the results.
> No BLOBS. Not "too many" columns. If you get a divide by zero
> error,
> your table is indexed, or not big enough.
>
> Please include any notes you think are relevant to this informal
> poll.
> Hardware and SAN info such as age. RAID level. Oracle version.
> Etc.
>
>
> Thanks a lot!
>
>
>
>
> Declare
>
> Cursor the_cursor is
> Select *
> From big_table
> Where non_indexed_field is not null;
>
> V_time1 date;
> V_time2 date;
>
> V_count integer := 0;
> V_rows_per_second number := 0;
> V_seconds integer := 0;
>
>
> Begin
>
> V_time1 := sysdate;
>
> For c1 in the_cursor loop
>
> V_count := V_count + 1;
>
> End loop;
>
> V_time2 := sysdate;
>
> V_seconds := (v_time2 - v_time1) * 86400 ;
> V_rows_per_second := v_count / ((v_time2 - v_time1) * 86400 );
>
> Dbms_output.put_line ('Row count: ' || v_count);
>
> DBMS_OUTPUT.PUT ( 'TIME to retrieve every row in the table (seconds):
> ' );
> Dbms_output.put_line ( to_char ( V_seconds, '999,999,999.99') ) ;
> Dbms_output.put_line ('Rows per second: ' ||
> trunc(V_rows_per_second) );
>
>
> /* ----------------- */
>
> V_count := 0;
> V_rows_per_second := 0;
> V_seconds := 0;
>
> V_time1 := sysdate;
>
> Select count(*)
> Into v_count
> From big_table
> Where non_indexed_field is not null;
>
> V_time2 := sysdate;
> V_seconds := (v_time2 - v_time1) * 86400 ;
> V_rows_per_second := v_count / ((v_time2 - v_time1) * 86400 );
>
> Dbms_output.put_line ('-----------------');
>
> DBMS_OUTPUT.PUT ( 'TIME to count the table (seconds): ' );
> Dbms_output.put_line ( to_char ( V_seconds, '999,999,999.99') ) ;
> Dbms_output.put_line ('Rows per second: ' ||
> trunc(V_rows_per_second) );
>
>
> End;

This is a stupid, deprecated, cursor loop. Who cares how slowly you can make something run by writing bad code? There hasn't been a good excuse for a cursor loop since 8.1.7.

Write decent code, and as I demonstrated, and you can process more than 500K rows/second on a pathetically slow laptop.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org