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

Big George wrote:
> Oracle 10g
> I have a table with 20 millions of records
>
> Name of Table: BigTable
> Structure of Table:
> NumID Char(8)
> LastName1 Varchar2(40)
> LastName2 Varchar2(40)
> FirstName Varchar2(35)
> StateID Integer
> CityID Integer
> StreetID Integer
>
> Primary Key: NumID
> Indexes:
> Ind_Names: LastName1+LastName2
> Ind_NumID: NumID
>
> If I make a query searching for a NumID or for Names, it is very fast.
> Get row immediately, like:
> select * from BigTable
> where NumID = '12345678'
>
> I supose it means that indexes are OK
> The problem is when I try to make a simple count(*), like:
>
> select count('x') from BigTable
>
> It takes 1min30 sec ! Is it normal? Or is it to be faster? I think that
> something is wrong with the table. Is it not supposed that Oracle can
> handle this amount of records very fast? Maybe there is some tuning
> that it is needed.
>
> I tried:
>
> select /*+ INDEX(BigTable Index_Of_NumID) */ count('x') from BigTable
> where numId = numId
>
> and "where numId = numId" made the difference. Query was faster, but
> only 10 secs less than before.
>
> I have seen that /*+ INDEX(BigTable Index_Of_NumID) */ makes no
> difference at all in time used.
>
> About using GATHER_STATICTIS, that is used when a table has many rows
> deleted, inserted or updated. In this case, BigTable is a Table that
> does not change. The same 20 millions records stay there.
>
> Here is my Execution Plan:
>
> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=65010 Card=1 Bytes
> =17)
>
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'Index_Of_NumID' (INDEX
> (UNIQUE))
> (Cost=65010 Card=16486652 Bytes=280273084)
>
>
> Statistics
> ----------------------------------------------------------
> 1 recursive calls
> 0 db block gets
> 231638 consistent gets
> 231553 physical reads
> 0 redo size
> 425 bytes sent via SQL*Net to client
> 508 bytes received via SQL*Net from client
> 2 SQL*Net roundtrips to/from client
> 0 sorts (memory)
> 0 sorts (disk)
> 1 rows processed

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.

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?
What platform are you on?
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>

I expect you will see an issue with your disk.

jg

--
@home.com is bogus.
Useless home firewalls.
http://www.mg.co.za/articlePage.aspx?articleid=275381&area=/insight/insight_tech/