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

On Jan 3, 11:29 pm, dba_..._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_Dec...,
>
> "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;

Hi,
I'd like to request some more background about your desire to start this 'poll'. As you can see in the replies, a big part of the respondents seems to see it as non-sensical. Why is it not? I can even make a bad query worse - reduce the hash area size, create a silly stored outline, make a bad hint--+, set pctused to 1 pct, suspend the session, add even more context switches, add an unneccessary cartesian join.
Are you trying to point out that the reported 87k rows/CPU/sec are ridiculously little and indicates a badly tuned system and/or application? And hence you are criticizing that publication?

My results on my test table of 1.999M rows are TIME to retrieve every row in the table (seconds):

          11.00
Rows per second: 181818

WindowsXPPro-SP2, Dell Lattitude D610 Laptop, Pentium M 1.73 GHz, standard disk. Oracle XE.

Regards,
Erik Ykema Received on Sat Jan 12 2008 - 11:47:28 CST