We help IT Professionals succeed at work.

Why the query is not using index in Oracle?

geotiger
geotiger asked
on
3,013 Views
Last Modified: 2013-12-19
Why the query is not using index in a client's environment? We tested the query and it used indexes (see the explain plan). But it does full table scan in our client's machine.


Our environment: Oracle 9.2.0.8 on Window server
Clinet's environment: Oracle 9.2.0.8 on Unix server

We have asked the client to dump the schema and imported the dumped schema to our environment. It uses indexes. I asked the client to send me fragmentation info. There is only 130 fragments in the tablespace.

Any clue what it might cause the problem?


SELECT
    C.DOCUMENT_SUBTYPE_ID AS DOCUMENT_SUBTYPE_ID,
    DNAME.NAME AS CAT_NAME,
    SUM(C.PAGE_COUNT) AS PAGE_COUNT,
    SUM(C.BOOKMARK_COUNT) AS BOOKMARK_COUNT,
    SUM(C.HYPERLINK_COUNT) AS HYPERLINK_COUNT,
    SUM(C.VARIABLE_COUNT) AS VARIABLE_COUNT,
    COUNT(*) AS TOTAL_DOCS
FROM CONTAINER C
    INNER JOIN I18N DNAME ON (DNAME.PARENT_OBJECT_ID=C.DOCUMENT_SUBTYPE_ID AND DNAME.LOCALE_CODE = 'en_US')
    INNER JOIN CONTAINER_CONTENT_OBJECT CCO ON (CCO.CONTAINER_ID = C.OBJECT_ID)
    INNER JOIN CONTENT_OBJECT CO ON (CCO.CONTENT_OBJECT_ID = CO.OBJECT_ID)
    INNER JOIN CONTAINER_ANCESTOR CA ON C.OBJECT_ID = CA.CONTAINER_ID
WHERE C.DOCUMENT_YN = 'Y' AND CA.ANCESTOR_ID = 7208866 AND CCO.CURRENT_YN = 'Y'
GROUP BY C.DOCUMENT_SUBTYPE_ID,DNAME.NAME
ORDER BY COUNT(*) DESC

Plan
SELECT STATEMENT  CHOOSECost: 89  Bytes: 710  Cardinality: 10                                                  
      13 SORT ORDER BY  Cost: 89  Bytes: 710  Cardinality: 10                                            
            12 SORT GROUP BY  Cost: 89  Bytes: 710  Cardinality: 10                                      
                  11 NESTED LOOPS  Cost: 86  Bytes: 710  Cardinality: 10                                
                        9 NESTED LOOPS  Cost: 76  Bytes: 660  Cardinality: 10                          
                              7 NESTED LOOPS  Cost: 66  Bytes: 380  Cardinality: 10                    
                                    4 NESTED LOOPS  Cost: 24  Bytes: 567  Cardinality: 21              
                                          1 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTAINER_ANCESTOR_AID_CID Cost: 3  Bytes: 210  Cardinality: 21        
                                          3 TABLE ACCESS BY INDEX ROWID WYETHQA.CONTAINER Cost: 1  Bytes: 17  Cardinality: 1        
                                                2 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTAINER_OID_IDX Cardinality: 1  
                                    6 TABLE ACCESS BY INDEX ROWID WYETHQA.CONTAINER_CONTENT_OBJECT Cost: 2  Bytes: 11  Cardinality: 1              
                                          5 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONT_CONT_OBJ_CONT_ID_IDX Cost: 1  Cardinality: 1        
                              8 INDEX RANGE SCAN NON-UNIQUE WYETHQA.I18N_POI_LC_N Cost: 1  Bytes: 28  Cardinality: 1                    
                        10 INDEX RANGE SCAN NON-UNIQUE WYETHQA.CONTENT_OBJECT_OBJECT_ID_IDX Cost: 1  Bytes: 5  Cardinality: 1                          



=======================================================================================
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi geotiger,

Make sure that the statistics are current on that machine.  Have them run an analyze on the joined tables.


Good Luck,
Kent

Author

Commented:
They did. That was the first thing that I had asked them to do.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi geotiger,

How much data is on the customer machine?  It could be that Oracle has determined that the table scan is appropriate.


Good Luck,
Kent

Author

Commented:
I had imported all the client data into a schema in our instance. It uses the indexes as expected.  It is very strange.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi geotiger,

Try forcing the index just to see if it will.


Good Luck,
Kent
You need first to break the query and run an explain plan for a single statement, such as:
SELECT * FROM CONTAINER_ANCESTOR CA
WHERE CA.ANCESTOR_ID = 7208866

If the above query uses the index, then you may have an ANSI SYNTAX issue, which was very common in version 9i. The workaround is to remove those INNER JOINs and replace them with Oracle's old syntax. I personally wouldn't the 1999 ANSI Syntax on 9i unless I need a full-outer-join.

Author

Commented:
I will ask the client to do that. What it bothers is that we imported the whole schema into ours. It uses indexes as expected. Their indexes must have been stored differently some how in their database (fragmented?). Is there a way to check this?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi geotiger,

Are both systems running the save version of Oracle, same fix pack, same underlying O/S?


Good Luck,
Kent

Author

Commented:
Both are running the same version of Oracle - 9.2.0.8. The client is running Oracle on Solaris while our test server is Window 2000. The Unix server the client is using is much more powerful then our test server but three times slower then ours due to that it igores indexes.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi geotiger,

I'd ask Oracle.  Even though the two systems have a common code base, there obviously must be differences to support the different host operating systems and hardware, including instruction sets.  It could well be that there is a uniqueness in the modules that is at work.


Good Luck,
Kent
Can you post the results when you run the statement on both hosts with and without index hint and the corresponding explain plans.

Commented:
have you tried to rebuild indexes? if they are old, or b-tree is degenrated, optimizer might rather not to use it.

alter index <index_name> rebuild;

Author

Commented:
I compared the tablespace fragmentation and index fragmentation between the client's  and ours. The client has higher numbers but not significant. I had recommended the client to coalesce tablespaces and rebuild the indexes. I will see how this would work out.

Commented:
coalescing space only joins free space pieces which are next to each other. check if tablespaces are created to have uniform size of extent, if no you would better to create new tablespaces with uniform size of extent to prevent internal space fragmentation.
geotiger,

Have you tried running an EXPLAIN PLAN for a single SELECT statement on your client's environment as I suggested you before?

Are the indexes being suppressed on just one table or is this a global problem?

Do you know anything about SQL PERFORMANCE TUNING?


Author

Commented:
Paquicuba, thanks for the suggestion. I did ask the client to run simple sql and it did use index. But it did not solve the problem of why it does not use index in complex query (joins). Being a DBA for over 10 years, I have learned a little SQL performance tuning.

Thanks for all the suggestions.  The problem is resolved now. It turns out that the client ran analysis differently than what we had asked.  Once the client ran dbms_stats with cascade enabled, it all worked as expected. You just can not believe what you have heard (or read the results you received from email) completely. You need to see how they did it.

Thanks for all the suggestions.

TG
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.