Oracle-L: performance issue on select count(*)

From: Linda Wang <lwang344_at_hotmail.com>
Date: Mon, 27 Oct 2003 05:24:32 -0800
Message-ID: <F001.005D4AE7.20031027052432@fatcity.com>

Hi,
I have an online application that does a 'select count(*)' on a few tables. The 'select counts' always runs slow (about 10secs) for the first time and then fast again (< 1sec) after subsequent accesses. The query runs slow again when the data is flushed out of the buffer cache. 10046 trace shows that the query takes a long time whenever there are disk accesses to fetch the data (about 1000 8K) into db cache. It should not take that long to fetch 1000 8K blocks into the cache and I/O does not appear to be the problem.

Anyone has any idea what the problem may be or how I can speed up my query?

DB: 8.1.7.4
query: select count(*) from tickets where tid='value1'; where tickets has about 2 million records partition on a date field. and tid is indexed.

thanks.

linda

Enjoy MSN 8 patented spam control and more with MSN 8 Dial-up Internet Service. Try it FREE for one month! http://join.msn.com/?page=dept/dialup

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Linda Wang
INET: lwang344_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Oct 27 2003 - 07:24:32 CST