-
Query Perfomance
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
-
Was there any large data load yesterday?
Did you change analyze method?
Tamil
-
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
-
Code:
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
-
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
-
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
-
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
-
Some thing went wrong when collecting statistics on the index.
Re-analyze the index and see FTS is appearing again.
Tamil
-
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.
-
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?.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|