| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: performance issue on select count(*)
Stephane,
the execution plan for the statement is an index range scan on tid. It did
not access the table. index is not partitioned. I will testpartitioning the
index and with the parallel fast full scan. Anyone else has any other
suggestions?
Thanks.
linda
>From: "Stephane Faroult" <sfaroult_at_oriolecorp.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: performance issue on select count(*)
>Date: Mon, 27 Oct 2003 05:49:24 -0800
>
>Linda,
>
> I guess that the key word is 'partition'. This type of query should not
>require to access the table if (hopefully) tid is indexed. If the index on
>tid is also partitioned, all index partitions have to be searched. My
>feeling is that in such a case what should run faster is some parallel fast
>full scan. Does your execution plan show this type of process or something
>wildly different ?
>
>SF
>
> >----- ------- Original Message ------- -----
> >From: "Linda Wang" <lwang344_at_hotmail.com>
> >To: Multiple recipients of list ORACLE-L
> ><ORACLE-L_at_fatcity.com>
> >Sent: Mon, 27 Oct 2003 05:24:32
> >
> >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
> >
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Stephane Faroult
> INET: sfaroult_at_oriolecorp.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).
-- 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 - 11:54:26 CST
 |
 |