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

Quote:

---

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

Try analyzing the coulmn CUSTOMER_NUMBER

Quote:

---

Originally posted by Chucks_k
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

---

The first plan virtually ** SUCKS **..

Do either of the things .. Put a hint as below to force oracle to choose CUSTOMERS as driving table.

Code:

---

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

---

Or Delete the Column Stats on CUSTOMER_NUMBER ( I would expect this to be evenly distributed and having many distinct values )

Abhay.

Quote:

---

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

---

I assume ACCOUNT_OWNERSHIP.CUSTOMER_NUMBER is of datatype NUMBER. IF so, change the above query to:

Code:

---

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

---

Or simply dont put the quotes ('')

Thanks all for the input.

How do i analyze/delete stats for a particular column on a table?..Is it just a case of using dbms_stats? Is there another way?



Thanks in advance,
Chucks.

yes using dbms_stats

Hi all,

I deleted stats for that column(customer_number) in the table and re-analyzed the table. Re-ran the query and it is stil not picking up that index. Is it a case for rebuilding it(the index)?.


Thanks,
Chucks

Rebuilding wont do any good.. use hint as i said or use to_number fn as Jurij said..

BTW, how are u analyzing?

Abhay.

Hi there,

I tried using the hint and it came back with a even greater cost!. I analyzed the stats for the table like so:

analyze table customers compute statistics;

The customer_number is of datatype VARCHAR(2).

Thanks in advance,
Chucks

After you did analyze like u said above... Just use dbms_stats to delete stats on the column customer_number.. and see how oracle reacts.. or just delete stats using analyze and reanalyze as

Analyze Talble xxx compute statistics for table for all indexes

PS Sometimes histograms aint produce good plans.

Abhay.

Hi Chuck

Can you analyze your tables involved in this query like this and let us know whether anything changed with the plan.


exec dbms_stats.gather_table_stats('USERNAME','TABLE_NAME',CASCADE=>TRUE,ESTIMATE_PERCENT=>15);

regards
Hrishy

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


__________________

===
Slimdave,
You are right. I made a mistake while typing.
My point is: If the table is disorganized, then probably the optimizer may not choose INDEX access path.

Tamil

Hi all,

I have got it to work:) thanks to hrishys advice of using dbms_stats.gather_table_stats. I dont understand why when using this it picked up the index and not when i analyzed using dbms_utility?. Thank u all for yer valuable contributions, it has helped immensely.

Thanks once again,
Chucks.