DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Oracle does not use index

  1. #1
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688

    Angry Oracle does not use index

    Hi All,

    I have a problem with couple of SQL statements which do not use index and have different execution plans in sqlplus and during execution. The one bellow is the most CPU consuming and effect on overal application performance. Execution plan in the sqlplus is correct and as expected, however in the EM console, I see full table scan.
    Oracle 11.1.0.7, Windows server 64 bit.

    Schema statistics collected on all objects using:

    Code:
    exec dbms_stats.gather_schema_stats(user,cascade=>true);
    Problematic SQL:

    Code:
    select refcon from HISTOMVTS where infosbackoffice = :refext
    There is an index on text column "infosbackoffice"

    execution plan in the sqplus:

    Code:
    SQL> select refcon from HISTOMVTS where infosbackoffice = '123';
    
    no rows selected
    
    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 1551545895
    
    -----------------------------------------------------------------------------------------------------------
    | Id  | Operation                   | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
    -----------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT            |                             |     1 |    22 |     1   (0)| 00:00:01 |
    |   1 |  TABLE ACCESS BY INDEX ROWID| HISTOMVTS                   |     1 |    22 |     1   (0)| 00:00:01 |
    |*  2 |   INDEX RANGE SCAN          | I_HISTOMVTS_INFOSBACKOFFICE |     1 |       |     1   (0)| 00:00:01 |
    -----------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       2 - access("INFOSBACKOFFICE"='123')
    The same SQL with FULL table scan in the EM:



    I tried to apply Stored Outlines from the other server and using execution plan produced in the sqlplus, but result is the same.



    AWR report is attached here:
    http://www.dbasupport.com/forums/att...1&d=1292928685
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by kgb; 12-22-2010 at 05:29 AM.
    Best wishes!
    Dmitri

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You are comparing plans between what appears to be two entirely different tables.

  3. #3
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    688
    Quote Originally Posted by stecal View Post
    You are comparing plans between what appears to be two entirely different tables.
    Sorry, my fault, as there are 2 problematic SQLs I uploaded a wrong one.
    FIXED.
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width