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

[dba...@yahoo.com]

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;

[joel garry]

On Jan 3, 2:29 pm, dba_...@yahoo.com wrote:

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

Wouldn't a bulk collect of a large number of rows be faster, avoiding
many context switches? Why should we limit ourselves to bad-
performing PL even for the slowest query?

jg
--
@home.com is bogus.
And I thought it was bad when the OC bus driver got lost!
http://news.bbc.co.uk/1/hi/england/gloucestershire/7139603.stm

[dba...@yahoo.com]

That's not the question.

Ultimately, there WILL BE full table scans in your system,
and they ARE occuring, whether you know it or not.

As I prefaced, "we already know how to speed queries up.

But, these days, just how fast is Oracle for the slowest possible
query??? "

Please run the script, and see what you get, and post it.

Thanks

[DA Morgan]

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
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

[DA Morgan]

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.

[fitzj...@cox.net]

On Jan 3, 5:09 pm, dba_...@yahoo.com wrote:
> That's not the question.
>
> Ultimately, there WILL BE full table scans in your system,
> and they ARE occuring, whether you know it or not.

A full table scan is one thing, invoking piss-poor PL/SQL code to
measure performance is entirely another. The latter is a complete
waste of time.

>
> As I prefaced, "we already know how to speed queries up.
> But, these days, just how fast is Oracle for the slowest possible
> query??? "
>

This isn't a query, it's a dismal example of a procedural loop. You
can't equate one with the other.

> Please run the script, and see what you get, and post it.
>

No, I'd prefer to not bog down my databases with impediments such as
your 'shining example' of how to not write PL/SQL.

> Thanks

David Fitzjarrell

[dba...@yahoo.com]

For your info, this is not how I write code.
This is the type of code I've inherited and have to maintain.
The loop is deliberately written badly. Yes, it is in fact,
a 'shining example' of how to not write PL/SQL.

I know how to tune. I've sped queries up over thousands
of times faster, from hours to seconds.

So, to repeat, as I prefaced, since you seemed to miss it,
"Yes, we already know how to speed queries up."

The question is, these days, just how many rows
per second can Oracle retrieve in the worst case
scenario? Based on empirical evidence. After you've
finished arguing on other ways to do it, and speculating
what it depends on and how fast it might go.

10 rows per second?

1 thousand rows per second?

1 million rows per second?

1 trillion rows per second?

Aren't you scientist enough to try the experiment?

Maybe this will help you to get it.

Say, the disaster planning committee lays out the
worst case scenario, things that they -have seen-
before in their experience, fully expect to happen
again, and asks,

"How quickly can you recover? What is the realistic time frame?

10 seconds?

10 hours?

10 days?

10 years?"

What do you do? Critisize them for asking the question?!
And then tell them that the worst case scenario, that is
already seen occuring, would NEVER happen?
Duh!!!!!

[Frank van Bortel]

DA Morgan wrote:

Err... 200k rows inserted in .65 secs is
200k/.065 = 307692.30769230769230769230769231 rows/sec
in my calc. I'll go for 307692 :)

And those are inserts - no asked for.
But nice to know; my home-brew Intel E6600 PC with SATA drive:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set timing on
SQL> exec fast_way

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.26
SQL> select count(*) from child;

COUNT(*)
----------
200000

769230 inserts/sec
--
Regards,
Frank van Bortel

Top-posting is one way to shut me up...

[Vince]

I think to Mr. Morgan's point, your test of how fast can Oracle
perform a full table scan is not performing the scan in one fell
swoop. The timing you suggest has no choice but to include all the
time taken up by context switching beteen PL/SQL and SQL. That amout
of time is going to far exceed anything being done in a FTS. The
better test is to perform the FTS and note how long it takes.

To test this, look at the amount of time it takes for you to run the
PL/SQL version of a FTS and then perform the select * from
the_same_really_big_table after setting termout off (you dont want to
have the amount of time it takes to display data to interfere with how
long it takes to perform the FTS, or add a filter of where columnx is
not null (columnx being a not null, non indexed column). I would place
money on the latter.

[fitzj...@cox.net]

You cannot get an accurate report given this lacklustre code, as you
only count the returned records, NOT every record in the table. It
would be so easy to set up an example where this would return 0 rows
in who knows how many seconds:

SQL> set serveroutput on size 1000000
SQL>
SQL> Declare
2
3
4 Cursor the_cursor is
5 Select *
6 From test_tbl
7 Where mycol is not null;
8
9
10 V_time1 date;
11 V_time2 date;
12
13
14 V_count integer := 0;
15 V_rows_per_second number := 0;
16 V_seconds integer := 0;
17
18
19 Begin
20
21
22 V_time1 := sysdate;
23
24
25 For c1 in the_cursor loop
26
27
28 V_count := V_count + 1;
29
30
31 End loop;
32
33
34 V_time2 := sysdate;
35
36
37 V_seconds := (v_time2 - v_time1) * 86400 ;
38 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
39
40
41 Dbms_output.put_line ('Row count: ' || v_count);
42
43
44 DBMS_OUTPUT.PUT ( 'TIME to retrieve every row in the table
(seconds):
45 ' );
46 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
47 Dbms_output.put_line ('Rows per second: ' ||
48 trunc(V_rows_per_second) );
49
50
51 /* ----------------- */
52
53
54 V_count := 0;
55 V_rows_per_second := 0;
56 V_seconds := 0;
57
58
59 V_time1 := sysdate;
60
61
62 Select count(*)
63 Into v_count
64 From test_tbl
65 Where mycol is not null;
66
67
68 V_time2 := sysdate;
69 V_seconds := (v_time2 - v_time1) * 86400 ;
70 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
71
72
73 Dbms_output.put_line ('-----------------');
74
75
76 DBMS_OUTPUT.PUT ( 'TIME to count the table (seconds): ' );
77 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
78 Dbms_output.put_line ('Rows per second: ' ||
79 trunc(V_rows_per_second) );
80
81
82 End;
83 /
Row count: 0

TIME to retrieve every row in the table (seconds):

4.00
Rows per second: 0
-----------------
TIME to count the table (seconds): 3.00
Rows per second: 0

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.72
SQL>
SQL>
SQL>
SQL> select count(*) from test_tbl;

COUNT(*)
----------
8000000

Elapsed: 00:00:03.38
SQL>

8 million rows in the table, no indexes, and nothing returned by your
mess of code. What, exactly, does that tell you? Oh, gee, did I
mention that the column in the where clause is NULL for all entries?
That, apparently, is a situation you didn't expect. So, how fast is
my database? 0 records in 4 seconds ... hmmm ...

Actually, your reported time isn't for the retrieval, it's for the
counting of the returned rows from the cursor, where the 'key' value
is not null, so it isn't an accurate measure of performance of the
scan, but of the loop. Using a populated column this time, from the
same table:

SQL> set serveroutput on size 1000000
SQL>
SQL> Declare
2
3
4 Cursor the_cursor is
5 Select *
6 From test_tbl
7 Where myothercol is not null;
8
9
10 V_time1 date;
11 V_time2 date;
12
13
14 V_count integer := 0;
15 V_rows_per_second number := 0;
16 V_seconds integer := 0;
17
18
19 Begin
20
21
22 V_time1 := sysdate;
23
24
25 For c1 in the_cursor loop
26
27
28 V_count := V_count + 1;
29
30
31 End loop;
32
33
34 V_time2 := sysdate;
35
36
37 V_seconds := (v_time2 - v_time1) * 86400 ;
38 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
39
40
41 Dbms_output.put_line ('Row count: ' || v_count);
42
43
44 DBMS_OUTPUT.PUT ( 'TIME to count every retrieved row from the
table (seconds):
45 ' );
46 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
47 Dbms_output.put_line ('Rows per second: ' ||
48 trunc(V_rows_per_second) );
49
50
51 /* ----------------- */
52
53
54 V_count := 0;
55 V_rows_per_second := 0;
56 V_seconds := 0;
57
58
59 V_time1 := sysdate;
60
61
62 Select count(*)
63 Into v_count
64 From test_tbl
65 Where myothercol is not null;
66
67
68 V_time2 := sysdate;
69 V_seconds := (v_time2 - v_time1) * 86400 ;
70 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
71
72
73 Dbms_output.put_line ('-----------------');
74
75
76 DBMS_OUTPUT.PUT ( 'TIME to count the table (seconds): ' );
77 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
78 Dbms_output.put_line ('Rows per second: ' ||
79 trunc(V_rows_per_second) );
80
81
82 End;
83 /
Row count: 8000000
TIME to count every retrieved row from the table (seconds):
97.00
Rows per second: 82474
-----------------
TIME to count the table (seconds): 5.00
Rows per second: 1600000

PL/SQL procedure successfully completed.

Elapsed: 00:01:42.52

Let's set half of the non-null column values to NULL and see how that
affects the reported time:

SQL> set serveroutput on size 1000000
SQL>
SQL> Declare
2
3
4 Cursor the_cursor is
5 Select *
6 From test_tbl
7 Where myothercol is not null;
8
9
10 V_time1 date;
11 V_time2 date;
12
13
14 V_count integer := 0;
15 V_rows_per_second number := 0;
16 V_seconds integer := 0;
17
18
19 Begin
20
21
22 V_time1 := sysdate;
23
24
25 For c1 in the_cursor loop
26
27
28 V_count := V_count + 1;
29
30
31 End loop;
32
33
34 V_time2 := sysdate;
35
36
37 V_seconds := (v_time2 - v_time1) * 86400 ;
38 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
39
40
41 Dbms_output.put_line ('Row count: ' || v_count);
42
43
44 DBMS_OUTPUT.PUT ( 'TIME to count every retrieved row from the
table (seconds):
45 ' );
46 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
47 Dbms_output.put_line ('Rows per second: ' ||
48 trunc(V_rows_per_second) );
49
50
51 /* ----------------- */
52
53
54 V_count := 0;
55 V_rows_per_second := 0;
56 V_seconds := 0;
57
58
59 V_time1 := sysdate;
60
61
62 Select count(*)
63 Into v_count
64 From test_tbl
65 Where myothercol is not null;
66
67
68 V_time2 := sysdate;
69 V_seconds := (v_time2 - v_time1) * 86400 ;
70 V_rows_per_second := v_count / ((v_time2 - v_time1) *
86400 );
71
72
73 Dbms_output.put_line ('-----------------');
74
75
76 DBMS_OUTPUT.PUT ( 'TIME to count the table (seconds): ' );
77 Dbms_output.put_line ( to_char ( V_seconds,
'999,999,999.99') ) ;
78 Dbms_output.put_line ('Rows per second: ' ||
79 trunc(V_rows_per_second) );
80
81
82 End;
83 /
Row count: 4000000
TIME to count every retrieved row from the table (seconds):
55.00
Rows per second: 72727
-----------------
TIME to count the table (seconds): 5.00
Rows per second: 800000

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.49

Return half of the table data, return in half of the time. Not a very
reliable 'metric' it would appear. So, your calculated time is only
for counting the returned rows, not for scanning the entire table.
Checking on how long a full table scan would take on my server:

SQL> set autotrace traceonly
SQL> select *
2 from test_tbl
3 where nyothercol is not null;

4000000 rows selected.

Elapsed: 00:00:26.52

A little under 27 seconds to scan 8 million rows -- 301,659 rows per
second. And I could probably improve that by setting arraysize to
some large number. Not bad for a server with 36 other active
databases besides the one I'm using. Here is the query plan, to prove
it's a full table scan:

Execution Plan
----------------------------------------------------------
Plan hash value: 2532572490

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost
(%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4724K| 117M| 3595
(20)| 00:00:44 |
|* 1 | TABLE ACCESS FULL| TEST_TBL | 4724K| 117M| 3595
(20)| 00:00:44 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("MYOTHERCOL" IS NOT NULL)

Note
-----
- dynamic sampling used for this statement

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
13967 consistent gets
15 physical reads
0 redo size
43538337 bytes sent via SQL*Net to client
6639 bytes received via SQL*Net from client
801 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4000000 rows processed

Your script is flawed, not only in code, but also in the underlying
'logic'. You're not measuring what you think you're measuring. Duh.
Come back when and if you have something better to offer.

David Fitzjarrell

[fitzj...@cox.net]

Comments embedded.

On Jan 4, 12:15 pm, dba_...@yahoo.com wrote:

> For your info, this is not how I write code.
> This is the type of code I've inherited and have to maintain.
> The loop is deliberately written badly. Yes, it is in fact,
> a 'shining example' of how to not write PL/SQL.
>
> I know how to tune.  I've sped queries up over thousands
> of times faster, from hours to seconds.
>

And a single index, properly configured, can do just that. Your point
being?

> So, to repeat, as I prefaced, since you seemed to miss it,
> "Yes, we already know how to speed queries up."

I missed nothing. You, however, have missed much, as this isn't a
question of how fast you make the query it's a question of accurate
measurement. And this isn't accurate, as it doesn't measure what you
think it does.

>
> The question is, these days, just how many rows
> per second can Oracle retrieve in the worst case
> scenario?  

The last three words describe your code precisely.

> Based on empirical evidence.  

Which your code doesn't generate.

> After you've
> finished arguing on other ways to do it, and speculating
> what it depends on and how fast it might go.

I'm not arguing, simply stating facts. Proven in another reply to
this thread.

>
> 10 rows per second?
>
> 1 thousand rows per second?
>
> 1 million rows per second?
>
> 1 trillion rows per second?
>
> Aren't you scientist enough to try the experiment?
>

I'm 'scientist' enough to know a bad experiment when I see one, and
this certainly meets that criteria.

> Maybe this will help you to get it.
>

You're the one needing help 'getting it', as your example is broken
from the start.

> Say, the disaster planning committee lays out the
> worst case scenario, things that they -have seen-
> before in their experience, fully expect to happen
> again, and asks,
>
> "How quickly can you recover? What is the realistic time frame?
>
> 10 seconds?
>
> 10 hours?
>
> 10 days?
>
> 10 years?"
>
> What do you do?  

I answer the question, as it's a valid interrogatory. Yours is not.

<rancid commentary snipped>
> Duh!!!!!

And that last word is likely the most intelligent comment you've made
in this thread.

David Fitzjarrell

[DA Morgan]

Part of the reason I was so amazed anyone would throw out the number 58K
rows per second for an FTE. I can scan, process, and insert ten times
that number of rows on a production system with decent code.

[DA Morgan]

dba...@yahoo.com wrote:

> The question is, these days, just how many rows
> per second can Oracle retrieve in the worst case
> scenario?

No it isn't. Never has been and never will be. But if that's
your question here's my answer:

CREATE OR REPLACE PROCEDURE dba_222 (sleepval IN PLS_INTEGER) IS
BEGIN
FOR rec IN (select obj# from source$) LOOP
dbms_lock.sleep(sleepval);
END LOOP;
END dba_222;
/

set timing on
exec dba_222(86400);
set timing off

My calculation is one row per day. So this should complete in
1627 years give or take a few months.

[fitzj...@cox.net]

On Jan 4, 3:26 pm, DA Morgan <damor...@psoug.org> wrote:

> dba_...@yahoo.com wrote:
> > The question is, these days, just how many rows
> > per second can Oracle retrieve in the worst case
> > scenario?
>
> No it isn't. Never has been and never will be. But if that's
> your question here's my answer:
>
> CREATE OR REPLACE PROCEDURE dba_222 (sleepval IN PLS_INTEGER) IS
> BEGIN
>    FOR rec IN (select obj# from source$) LOOP
>      dbms_lock.sleep(sleepval);
>    END LOOP;
> END dba_222;
> /
>
> set timing on
> exec dba_222(86400);
> set timing off
>
> My calculation is one row per day. So this should complete in
> 1627 years give or take a few months.
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington

> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

He should run it on one of my test databases, he'd only be waiting 336
years ...

David Fitzjarrell

[DA Morgan]

Exactly. How long an FTE takes is this easy to determine.

Here's how long Oracle takes to do an FTE on my laptop:

SQL> create table t as select obj#, line from source$;

Table created.

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

COUNT(*)
----------
594510

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

58K rows per second my ....

Any time spent beyond this is wasted time based upon someone
introducing complexity.

BTW by the third time I ran it in 11g ...

SQL> /

COUNT(*)
----------
594510

Elapsed: 00:00:00.03
SQL>

Which is, realistically, what we would see in a production environment
where the blocks are in memory.

--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington

damo...@x.washington.edu (replace x with u to respond)

[hpuxrac]

On Jan 3, 4:29 pm, dba_...@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...,

2 things.

First I notice the OP didn't give any numbers from the systems they
have. Makes you wonder eh?

Second I predict at least 10 more posts in this thread from Captain
Morgain. Gets the url out eh?

[Helma]

if you're interested in I/O, this unsupported tool may interest you:

http://www.oracle.com/technology/software/tech/orion/index.html

i'm going to play around with it too, to see the I/O difference
between machines.

H.

[DA Morgan]

Or, of coures, you can just move to 11gR1 where calibrate_io is
now built-in functionality.
http://www.psoug.org/reference/dbms_res_mgr.html

SELECT * FROM gv$io_calibration_status;

SELECT * FROM dba_rsrc_io_calibrate;

[Helma]

On 9 jan, 15:25, DA Morgan <damor...@psoug.org> wrote:
> Helma wrote:
> > if you're interested in I/O, this unsupported tool may interest you:
>
> >http://www.oracle.com/technology/software/tech/orion/index.html
>
> > i'm going to play around with it too, to see the I/O difference
> > between machines.
>
> > H.
>
> Or, of coures, you can just move to 11gR1 where calibrate_io is

> now built-in functionality.http://www.psoug.org/reference/dbms_res_mgr.html

>
> SELECT * FROM gv$io_calibration_status;
>
> SELECT * FROM dba_rsrc_io_calibrate;
> --
> Daniel A. Morgan
> Oracle Ace Director & Instructor
> University of Washington

> damor...@x.washington.edu (replace x with u to respond)

> Puget Sound Oracle Users Groupwww.psoug.org

Thanks Daniel, i still need to look into the new features of 11. I'll
check this one too.

H

[ErikYkema]

On Jan 3, 11:29 pm, dba_...@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...,

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
-----------------
TIME to count the table (seconds): 1.00
Rows per second: 1999999

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

Regards,
Erik Ykema

[Frank van Bortel]

Some observations:
- same machine, using RH ES 5 (CentOS 5.1+updates), with LVM/stripe set
does not make a difference in timing (differences are
too small to measure...)
- playing around with the LIMIT clause actually *does* make a
difference;
from 1000 to 500: none
from 1000 to 100: -10% (.30 secs to .33/.34)
from 1000 to 10000: +10% (.30 secs to .27/.26 sec)
from 1000 to 100000: -10% (.30 sec to .32/.33)

So it may pay off to test with different LIMIT values, as far as
memory consumption allows. Thomas Kyte mentions 500 as a reasonable
value, Daniel uses 1000, on my configuration, the optimum is
even higher, but I do not have to consider other processes here.

High volume ETL processing may well be worth checking for
the extra percent.
--

Regards,
Frank van Bortel

Top-posting in UseNet newsgroups is one way to shut me up