| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Count(*) is very slow
Can you try to see if performance has been improved after changing COUNT(*) to
COUNT(ROWID)?
If no, you can issue the query as follow:
SELECT
NAME, VALUE
FROM
V$SYSSTAT
WHERE
NAME IN ('sorts(memory)','sorts(disk)') ;
If you see 'sorts(disk)' have a high value, please increase the value of ORT_AREA_SIZE in INIT.ORA. Be careful on ORT_AREA_RETAINED_SIZE. Try to decrease this value at the same time.
And also, memory occupied by OS process will not be released since Oracle will not shrink back to original size after sorting.
If you still get problems after tuning SORT_AREA_SIZE, try SORT_DIRECT_WRITES. You can refer to DB Admin Guide.
Andreas Kyritz wrote:
> Hi George,
>
> George Meltser schrieb in Nachricht <70ff74$qol$1_at_as4100c.javanet.com>...
> >I have a table with 3,5 mln rows and after it's analyzed it takes 3 seconds
>
> on which server-system do you get this result ?
>
> >to do
> >a count(*), so please stop blaming dbms.
>
> Sorry, but it really takes 36 seconds (3 seconds CPU-time on the server !!)
> after analyzing the table. There is, except of the operating system, no
> other proccess on the server. On the same machine the results with MS SQL
> Server, Centura, Informix and DB/2 - dbms where never so bad.
>
> The database I've used, was created with default values. Do you know, which
> parameter can tune this query ? Is it better, to work with a clustered
> primary key ?
>
> --
> Andreas Kyritz
> AKyritz_at_easy-soft-dresden.de
--
Best Regards,
Reid Lai <reidlai_at_hk.super.net>
Certified Oracle 7 DBA (OCP)
 |
 |