Re: Poll: How fast is YOUR database in rows per second, on a full bore table scan???
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 04 Jan 2008 13:34:00 -0800
Message-ID: <1199482423.895837@bubbleator.drizzle.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!!!!!
Date: Fri, 04 Jan 2008 13:34:00 -0800
Message-ID: <1199482423.895837@bubbleator.drizzle.com>
Vince wrote:
> On Jan 4, 10:15 am, dba_..._at_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.
>>
>> 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
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 damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Jan 04 2008 - 15:34:00 CST