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 Files
Last edited by kgb; 12-22-2010 at 05:29 AM .
Best wishes!
Dmitri
You are comparing plans between what appears to be two entirely different tables.
Originally Posted by
stecal
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
Forum Rules
Click Here to Expand Forum to Full Width