tkprof, with the row source operation, shows what actually happens.

explain plan, which cannot:

o bind peek
o know the datatypes of the binds
o reuse a 'soft parse' (eg: it always hard parses)
o understand the optimizer environment in use by the 'real plan' always

can mislead.

for example:

---------------------------------------------------
set echo on
drop table t;

create table t
as
select '99' id, all_objects.*
from all_objects;
update t set id = '1' where rownum = 1;
create index t_idx on t(id);
exec dbms_stats.gather_table_stats( user, 'T', method_opt => 'for all indexed columns' );


variable n varchar2(5)
alter session set sql_trace=true;
set autotrace traceonly statistics
exec :n := '1 '
select * from t t_1 where id = :n;
exec :n := '99'
select * from t t_99 where id = :n;
set autotrace off
-----------------------------------------------

a tkprof with explain=u/p will show:

select * from t t_1 where id = :n

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 4 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 4 0 1

Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=4 pr=0 pw=0 time=99 us)
1 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=115 us)(object id 57199)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)

eh, explain plan <> row source operation...

********************************************************************************
select * from t t_99 where id = :n

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 3311 0.16 0.16 0 3988 0 49648
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3313 0.16 0.16 0 3988 0 49648

Rows Row Source Operation
------- ---------------------------------------------------
49648 TABLE ACCESS FULL T (cr=3988 pr=0 pw=0 time=99473 us)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
49648 TABLE ACCESS MODE: ANALYZED (FULL) OF 'T' (TABLE)


row source operation = reality, what actually happened. execution plan = guess based on "incomplete information". explain plan does not bind peek, doesn't have that ability

How about datatypes? Consider:


ops$tkyte@ORA10GR2> create table t
2 as
3 select to_char(object_id) id, all_objects.*
4 from all_objects
5 /

Table created.

ops$tkyte@ORA10GR2> alter table t add constraint t_pk primary key(id);

Table altered.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> variable n number
ops$tkyte@ORA10GR2> exec :n := 42

PL/SQL procedure successfully completed.

ops$tkyte@ORA10GR2> set autotrace traceonly
ops$tkyte@ORA10GR2> select * from t where id = :n;


Execution Plan
----------------------------------------------------------
Plan hash value: 1303508680

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 150 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 150 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | T_PK | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ID"=:N)

here, explain plan assumes all binds are strings, it doesn't "see" that in sqlplus we told sqlplus "hey, when you bind, please bind a number" - all it sees is an explain plan command with a :n in it - you don't BIND to explain plans, hence explain plan doesn't know what datatype, or the bind value to peek

Statistics
----------------------------------------------------------
536 recursive calls
0 db block gets
888 consistent gets
0 physical reads
0 redo size
1254 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

Now, I'm pretty sure that isn't the plan used - 888 IO's to get a single row via a primary key index??

ops$tkyte@ORA10GR2> select * from t where id = 42;


Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 150 | 236 (3)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T | 1 | 150 | 236 (3)| 00:00:02 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(TO_NUMBER("ID")=42)

see, when we compare a STRING to a NUMBER, we have to conver the string to a number, so there is an implicit function on the database column. That is why we full scan in reality...

Note
-----
- dynamic sampling used for this statement


Statistics
----------------------------------------------------------
5 recursive calls
0 db block gets
800 consistent gets
0 physical reads
0 redo size
1254 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed

ops$tkyte@ORA10GR2> set autotrace off


Even if explain plan "peeked" at binds, it would not be good however. Since explain plan hard parsed each time, it would simply show us what would happen if the query was bound with that value and hard parsed - it won't show us what'll really happen.

I suggest you use v$sql_plan or tkprof with row source operation so as to see "reality"