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;

Depends. Here's one answer from my T43 Lenovo Thinkpad with a 5400RPM drive.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set timing on
SQL> SELECT COUNT(*) FROM airplanes;

   COUNT(*)

     250000

Elapsed: 00:00:00.51
SQL> set timing off

Here's another:

SQL> CREATE TABLE parent (

   2 part_num NUMBER,
   3 part_name VARCHAR2(15));

Table created.

SQL>
SQL> CREATE TABLE child AS

   2 SELECT *
   3 FROM parent;

Table created.

SQL> DECLARE
   2 j PLS_INTEGER := 1;
   3 k parent.part_name%TYPE := 'Transducer';    4 BEGIN
   5 FOR i IN 1 .. 200000
   6 LOOP

   7      SELECT DECODE(k, 'Transducer', 'Rectifier',
   8      'Rectifier', 'Capacitor',
   9      'Capacitor', 'Knob',
  10      'Knob', 'Chassis',
  11      'Chassis', 'Transducer')
  12      INTO k
  13      FROM dual;
  14
  15      INSERT INTO parent VALUES (j+i, k);

PL/SQL procedure successfully completed.

SQL>
SQL> SELECT COUNT(*) FROM parent;

   COUNT(*)

     200000

SQL> SELECT COUNT(*) FROM child;

   COUNT(*)

          0

SQL> CREATE OR REPLACE PROCEDURE fast_way IS

   2
   3 TYPE myarray IS TABLE OF parent%ROWTYPE;    4 l_data myarray;
   5
   6 CURSOR r IS
   7 SELECT part_num, part_name
   8 FROM parent;
   9
  10 BEGIN
  11 OPEN r;
  12 LOOP
  13 FETCH r BULK COLLECT INTO l_data LIMIT 1000;   14

  15      FOR j IN 1 .. l_data.COUNT
  16      LOOP
  17        l_data(1).part_num := l_data(1).part_num * 10;
  18      END LOOP;
  19
  20      FORALL i IN 1..l_data.COUNT
  21      INSERT INTO child VALUES l_data(i);
  22
  23      EXIT WHEN r%NOTFOUND;

Procedure created.

SQL> set timing on
SQL> exec fast_way

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.65
SQL> set timing off
SQL> Only 85,000 rows per second?

Seems like a lot of time was spent on the sorting part.

-- 
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