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

From: DA Morgan <>
Date: Fri, 04 Jan 2008 13:34:00 -0800
Message-ID: <>

Vince wrote:

> On Jan 4, 10:15 am, 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.
>> 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!!!!!
> 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.

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



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(*)


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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Jan 04 2008 - 15:34:00 CST

Original text of this message