Re: tuning question...

In article <8o6lcg$h1u$1_at_nnrp1.deja.com>,   gdas_at_my-deja.com wrote:
> I have query down to a level where the overall optimizer cost
> is "2"...However the query still takes an awfully long time to run...
> (almost 5 minutes). When I look at the statistics for the query,
>
> The following items caught my eye:
>
> 5307293 consistent gets
> 158609 physical reads
>
> What exactly is a "consistent get?" Are there any init.ora parameters
> that I can fiddle with to try to get these numbers down?
>
> Thanks in advance.
> Gavin
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

'Consistent gets' indicates the number of passes ('gets') made to the block buffer to retrieve the query data in a consistent manner. This is determined by the SCN, System Change Number) to ensure that the data has not been modified or altered since the query began. The 'consistent gets' value is incremented in one of the following ways:

• for full table scans: incremented by 1 for each block read
• for table access via index: increments by index height + 2*keys in the range

       Since there are two gets for every row in this scenario the
       'consistent gets' value increments quite rapidly

     * for index-only scans: incremented by 1 for each block read.  This
       only applies to queries that only access columns in the index and
       do not access the associated table.

'consistent gets' are from the block buffers, thus they are memory reads. 'physical reads', on the other hand, are reads from the disk (or disks). This value is incremented by 1 for each block read, regardless of whether it was a single-block read or a multi-block read.

Your query may be hitting a low-cardinality index, causing the 'consistent gets' figure to skyrocket. Additionally your 'physical reads' for this query seem quite high, which may also stem from a low-cardinality index. You might check the USER_INDEXES or DBA_INDEXES information on the index or indexes in question:

 select table_name, index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key
 from user_indexes
 where table_name in (...)
 and index_name in (...)

Presuming you have statistics on these tables/indexes this may show where the query is hanging up. Explain plan will provide the index/indexes utilized; you can then pinpoint, indirectly, the selectivity of the index/indexes used and make necessary corrections.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.