Tim, Thanks for your reply.
The select count(*) is doing an index range scan on the column tid. No table access in the execution plan. The query you provided returned the following result:
NUM_ROWS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY LAST_ANAL
--------- ------------ ----------------------- ----------------------- ---------
2326064.1 161201 1 3 28-OCT-03
I have tried local partition index on tid but the execution time was still around 10secs for the initial execution and <1sec subsequently. The table is partitioned on a date field.
I would be interested to know if there is a way to speed up the initial execution or how to diagnose what the delay was. It does not seems right that there is such a big difference in elapsed time between the initial and subsequent execution.
I monitored the wait events during both executions. They were all pretty low. It does not appear to be I/O bound either. tnsping from my PC to the database took about 30msec. Any other suggestions what I could check?
Thanks.
linda
select * from v$session_event where sid=98;
Initial run:
SID EVENT TOTAL TOTAL TIME AVERAGE MAX
WAITS TIMEOUTS WAITED WAIT WAIT
--- ---------------------------- ----- -------- ------ -------- ----
98 latch free 115 68 1 .008695652 1
98 control file sequential read 3 0 0 0 0
98 refresh controlfile command 1 0 0 0 0
98 buffer busy waits 1 0 0 0 0
98 log file sync 1 0 1 1 1
98 db file sequential read 1968 0 827 .420223577 10
98 file open 5 0 2 .4 1
98 SQL*Net message to client 305 0 0 0 0
98 SQL*Net message from client 304 0 31819 104.667763 29911
Subsequent run:
-----------------
SID EVENT TOTAL TOTAL TIME AVERAGE MAX
WAITS TIMEOUTS WAITED WAIT WAIT
--- ---------------------------- ----- -------- ------ -------- ----
99 latch free 162 93 3 .018518519 2
99 control file sequential read 3 0 0 0 0
99 refresh controlfile command 1 0 0 0 0
99 buffer busy waits 1 0 0 0 0
99 log file sync 1 0 0 0 0
99 file open 3 0 1 .333333333 1
99 SQL*Net message to client 54 0 0 0 0
99 SQL*Net message from client 53 0 2893 54.5849057 2698
From: Tim Gorman <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Subject: Re: performance issue on select count(*) Date: Mon, 27 Oct 2003 10:34:59 -0800
Linda,
I am guessing that since your table is partitioned on an unspecified date column, that the index on TID is either LOCAL or non-partitioned (i.e. GLOBAL).
If it is LOCAL (you would have had to specify the keyword, as it is not the
default), then you will be performing indexed RANGE scans on each of the
partitions in the index. Naturally, the more partitions there are, the
longer this may take, but probably not a great deal longer than if the index
was a GLOBAL non-partitioned index.
But regardless of the number of RANGE scans and the type of index it is, the
main question is whether TID is a good index to use in the first place.
This is a matter of data, purely the nature of the data.
You can diagnose this better using results from the following query:
SELECT NUM_ROWS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY, LAST_ANALYZED FROM DBA_INDEXES WHERE INDEX_NAME = '<name-of-index>';
Of particular interest are the values in AVG_LEAF_BLOCKS_PER_KEY and
AVG_DATA_BLOCKS_PER_KEY, as the cost-based optimizer uses these to calculate
the cost of an index RANGE scan (assuming that column-level statistics or
"histograms" have not been gathered).
If the values of these two columns are high, then the CBO will be hesitant to use the index, and with good reason. Thus, with the use of the index rejected as an option, you'll of course have a FULL table scan on your hands.
There is probably more to it, but this should be a start. Feel free to post
the results of the query above to the list, if you wish...
Hope this helps...
-Tim
on 10/27/03 6:24 AM, Linda Wang at [EMAIL PROTECTED] wrote:
> 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: Tim Gorman INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
_________________________________________________________________
Never get a busy signal because you are always connected with high-speed Internet access. Click here to comparison-shop providers. https://broadband.msn.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Linda Wang INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
