The issue is that they are not always a realistic view of what will actually take place. eg
SQL> create table t as
2 select d.* , cast(object_id as varchar2(20)) abc
3 from dba_objects d;
Table created.
SQL>
SQL> create index t_ix on t ( abc );
Index created.
SQL>
SQL> variable b1 number
SQL> exec :b1 := 12345
PL/SQL procedure successfully completed.
SQL>
SQL> set autotrace traceonly explain
SQL> select created
2 from t
3 where abc = :b1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3343387620
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T | 1 | 14 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_IX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ABC"=:B1)
SQL>
So based on that, I might think my query is fine. Let's see what *really* happens when I run it:
SQL> set autotrace off
SQL> select created
2 from t
3 where abc = :b1;
CREATED
---------
08-MAR-17
1 row selected.
SQL> select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
SQL_ID b4dcv66pbub14, child number 0
-------------------------------------
select created from t where abc = :b1
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 450 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 14 | 450 (1)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(TO_NUMBER("ABC")=:B1)
18 rows selected.
SQL>
SQL>
I didnt use the index because my data types didn't match. So 'explain plan' and 'autotrace' can be misleading.