Re: tuning question...

In article <8o6ro4$oor$1_at_nnrp1.deja.com>,   gdas_at_my-deja.com wrote:
> David, thank you for the help. I checked the explain plans and index
> statistics and I think I found another problem...
>
> This query involves two tables: datastage and feature.
> the primary keys of both tables are indexed. The indexes are :
>
> DATASTAGE_UQ_ID_IDX for table datastage and
> PK_Feature for table feature
>
> The following plan shows a cardinality of 4 for PK_Feature...By
> definition PK_FEATURE should be a high cardinality index since the
> number of index values will equal the number of rows in the table.
>
> (In terms of table statistics...datastage have approx. 500,000 rows
 and
> feature has approximately 1.5 million)
>
> I apologize if the formatting of this plan doesn't come out right.
>
> SELECT STATEMENT (cardinality 1)
> SORT AGGREGATE (cardinality 1)
> NESTED LOOPS 147
> TABLE ACCESS BY INDEX ROWID FEATURE
> (cardinality 4)
> INDEX RANGE SCAN PK_FEATURE
> (cardinality 4)
> TABLE ACCESS BY INDEX ROWID
> DATASTAGE (cardinality 3682)
> INDEX RANGE SCAN
> DATASTAGE_UQ_ID_IDX (cardinality 3682)
>
> I ran the query you gave me against the system table:
>
> select table_name, index_name, blevel, leaf_blocks, distinct_keys,
> avg_leaf_blocks_per_key
> from user_indexes where table_name in ('FEATURE') and index_name
> ='PK_FEATURE';
>
> The results are very suspicious. Only the table_name and index name
> were returned. All the values for blevel, leaf_blocks, distinct_keys
> etc... were all null/empty.
>
> Is something corrupted? I will try to rebuild the index to see if
 that
> might help. In the meantime, do you have any other ideas?
>

It looked like I had some stale stats on the pk_feature index...I analyzed it and the information was returned. However, now I have to indexes being scanned in this query and both of them are high cardinality indexes.

Do you know what else I might want to investigate? I'm currently trying to investigate my cache hit ratio and make any appropriate changes there. (Let me know if I am completely going down the wrong path).

Thanks,
Gavin

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Aug 25 2000 - 17:39:35 CDT