Re: "select count('x') from BigTable" takes more than 1 minute !

From: Big George <jbeteta_at_gmail.com>
Date: 24 Aug 2006 16:43:24 -0700
Message-ID: <1156463004.873709.247640@i42g2000cwa.googlegroups.com>

> That does seem awfully slow. I just did a count on my 14 million row
> table and it took about 7 seconds, and the index used is way bigger
> than yours. On the other hand, it only did 73K physical reads.

Interesting question is: Why a Count on 14 million record Joel table takes 7 secs and a Count on my 20 million record table takes 90 secs?

I think that Joel's questions are putting me in the correct way.

>
> Just off the top of my head:
>
> Perhaps you have way lots wasted space in your index.
> What are your top wait events?
> What exact version are you on?

Oracle 10g Release 10.1.0.4.0

> What platform are you on?

Windows Server 2003

For the other questions I'm lost. I'm not the DBA. But I can ask. Joel, any indicator that I could compare with your Database ?

> What is your blocksize?
> How big is your SGA?
> What else is going on?
> Are you using asynchronous I/O?
> What is db_file_multiblock_read_count?
> What do OS utilities say is going on?
> select sid, event,p1,p2,p3 from v$session_wait where sid = < SID with
> performance issue>
Received on Thu Aug 24 2006 - 18:43:24 CDT