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

Big George wrote:
> > 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

Consider upgrade.

>
> > What platform are you on?
> Windows Server 2003

[barely successful in keeping comment to myself :-) ]

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

Not really, since I'm on a different platform and version. But the fact that you have other big tables that count much faster indicates something particular about this index.

The fairly useless magical incantation of "what is your pctfree/pctused and freespace in this index" + "rebuild it" might fix your problem. (There are obscure things that can happen if you have the situation of massive inserts followed by slightly less massive deletes - you can wind up with a lot of empty blocks or a huge highwater mark). You might consider that knowing what the real problem is can help avoid or deal with it in the future.

But "why is this index slower than others?" really is a DBA issue, so you should get the DBA involved, if you have one. One other thing you can do as a non-DBA is run a trace on the count you are running. Much information on how to do this can be found with googling, perusing metalink, or reading certain books (names like Lewis, Kyte and Milsap are good things to look for).
http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_perf.html

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

jg

--
@home.com is bogus.
Businessman named Crook with a lawyer named Law
http://www.signonsandiego.com/uniontrib/20060825/news_1b25prgn.html