Query Perfomance

[Chucks_k]

Hi all,

I have a query which has been running fine until yesterday when it began to run really slowly. I looked at the explain plan and saw that one of the indexes it used to use is not being used at all now thus increasing the cost by a huge amount. Indexes and Tables are analyzed daily so I dont know what could have caused the execution path to miss out the index. Could it be that i need to rebuild the index??

Thanks in advance,
Chucks_k

[tamilselvan]

Was there any large data load yesterday?

Did you change analyze method?

Tamil

[Chucks_k]

Hi there,

There was no dataload as i am aware of, and the analyze method has not changed at all. We run a daily analyze of the schema (estimate) via script. Even if there was a huge dataload would it make a big difference? Or would it screw up the stats?

Thanks in advance
Chucks_k

[tamilselvan]

select index_name, leaf_blocks,
       AVG_LEAF_BLOCKS_PER_KEY,
       AVG_DATA_BLOCKS_PER_KEY,
       CLUSTERING_FACTOR,
       status,
       last_analyzed
from dba_indexes
where index_name = 'XXXXX'
/

Can you post the result of the above query.

Tamil

[Chucks_k]

Hi Tamil,

This the result for of query:

INDEX_NAME LEAF_BLOCKS AVG_LEAF_BLOCKS_PER_KEY
------------------------------ ----------- -----------------------
AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR STATUS LAST_ANAL
----------------------- ----------------- -------- ---------
AOP_CUS_DK_I 11008 1
1 1444800 VALID 08-FEB-05


Thanks,
Chucks

[tamilselvan]

The clustering_factor,1444800 seems to be high.
If this is equivalent to table's blocks, then it may not be a good index.

select table_name, blcoks
from dba_tables
where table_name ='XXXXX';

Run this query and let me know the value.

Tamil

[Chucks_k]

Hi Tamil,

This is the result of the query:

SQL> select table_name,blocks
2 from dba_tables
3 where table_name='ACCOUNT_OWNERSHIPS';

TABLE_NAME BLOCKS
------------------------------ ----------
ACCOUNT_OWNERSHIPS 12505

Which points towards a good index right?. Seems very strange that index is suddenly not used!

SELECT * FROM CUSTOMERS c, ACCOUNT_OWNERSHIPS ao
WHERE ao.scv_cust_id = c.SCV_CUST_ID
AND c.CUSTOMER_NUMBER = 'xxxxxxxxxx';

Below are the 2 explain plans. The first one for when the query is run now and the second one for when it was run before.

Explain Plan (full table scan on ACCOUNT_OWNERSHIPS) ...

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 3 M 1899
NESTED LOOPS 3 M 305 M 1899
TABLE ACCESS FULL SCV_OWNER.ACCOUNT_OWNERSHIPS 3 M 64 M 1899
TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 45 K 3 M
INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 45 K

Expect to see something like (no full table scans) ...

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 6 K 61
NESTED LOOPS 6 K 750 K 61
TABLE ACCESS BY INDEX ROWID SCV_OWNER.CUSTOMERS 19 1 K 4
INDEX RANGE SCAN SCV_OWNER.CUS_SCV_I1 19 3
TABLE ACCESS BY INDEX ROWID SCV_OWNER.ACCOUNT_OWNERSHIPS 10 M 259 M 3
INDEX RANGE SCAN SCV_OWNER.AOP_CUS_DK_I 10 M 2

Thanks in advance,
Chucks_k

[tamilselvan]

Some thing went wrong when collecting statistics on the index.
Re-analyze the index and see FTS is appearing again.

Tamil

[slimdave]

Originally posted by tamilselvan
The clustering_factor,1444800 seems to be high.
If this is equivalent to table's blocks, then it may not be a good index.

That's a crazy assertion when you don't even know what the problematic query is.

Also, you probably mean "if it's equivalent to the number of rows", not blocks.

[Chucks_k]

Hi Tamil,

I ran an analyze on the index:

ANALYZE INDEX index_name VALIDATE STRUCTURE;

And re-ran the query with no avail. Its very strange. Do u think a rebuild of the query will help?.