| 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(*)
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 <tim_at_sagelogix.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 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 lwang344_at_hotmail.com 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: tim_at_sagelogix.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). _________________________________________________________________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: 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 Tue Oct 28 2003 - 08:39:24 CST
 |
 |