Re: Poll: How fast is YOUR database in rows per second, on a full bore table scan???
Date: Thu, 03 Jan 2008 22:23:29 -0800
Message-ID: <1199427792.357058@bubbleator.drizzle.com>
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);16 END LOOP;
17 COMMIT;
18 END;
19 /
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;
24 END LOOP;
25 COMMIT;
26 CLOSE r;
27 END fast_way;
28 /
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.orgReceived on Fri Jan 04 2008 - 00:23:29 CST