Re: performance issue on select count(*)

From: Tim Gorman <tim_at_sagelogix.com>
Date: Mon, 27 Oct 2003 10:34:59 -0800
Message-ID: <F001.005D4B38.20031027103459@fatcity.com>

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).

Received on Mon Oct 27 2003 - 12:34:59 CST