Hi, Team
There's a procedure in our Oracle 11.2.0.1 DB which captures data from MS SQL Server through DB Link, since the context is quite long (cursors, pragma autonomous_transaction and loop included), let us take it brief like this:

CREATE OR REPLACE PROCEDURE P_SNYC_work_order_active
(M_INPUTSTRING in varchar2,
M_RES OUT varchar2) AS
pragma autonomous_transaction;
L_CUST_COUNT INTEGER;
L_wo01 varchar2(50);
....
CURSOR L_CUR_PROJECT IS
SELECT TRIM(' ' from nvl(wo01, '')) wo01,
TRIM(' ' from nvl(wo02, '')) wo02,
nvl(wo03, 0) wo03,
wo04 wo04,
wo05 wo05,
wo06 wo06,
TRIM(' ' from nvl(wo07, '')) wo07,
CompCode CompCode
FROM v_mes_work_order_active@DL_12; -- table from MS SQL Server
CURSOR L_CUR_PROJECT_DETAIL IS
SELECT TRIM(' ' from nvl(wo01, '')) wo01,
TRIM(' ' from nvl(wo02, '')) wo02,
nvl(wo05, 0) wo05,
TRIM(' ' from nvl(wo06, '')) wo06
FROM v_mes_work_order_detail@DL_12 -- table from MS SQL Server
where TRIM(' ' from wo01) = L_wo01;
BEGIN
M_RES := 'OK';
L_NUM := 0;

......
FOR CUR_ROW_DETAIL IN L_CUR_PROJECT_DETAIL LOOP
SELECT /*+ index(T_PM_PROJECT_DETAIL, T_PM_PROJECT_DETAIL_02) */ COUNT(1)
INTO L_NUM
FROM T_PM_PROJECT_DETAIL
WHERE PROJECT_ID = CUR_ROW_DETAIL.wo01
AND NEXT_MATERIAL = CUR_ROW_DETAIL.wo02;
--both the datatype of PROJECT_ID and NEXT_MATERIAL is varchar2(50) (Oracle)
--both the datatype of wo01 and wo02 is nvarchar2 (MS SQL Server)
......

END LOOP;
......
commit;
EXCEPTION
WHEN OTHERS THEN
M_RES := 'oops, error occurs!' || L_wo01;
rollback;
END;


This procedure elapsed for a long time that does not meet our customer's requirement. And I tried the '10046 event' then use tkprof to view the result.
Something that puzzled me was the difference between 'Row Source Operation' and 'Execution Plan'. Row Source Operation show that the table scan is TABLE ACCESS FULL even if i tried adding index hint to force the Execution Plan to choose INDEX scan successfully.

********************************************************************************

SQL ID: fn8tkyafh3zgq
Plan Hash: 1639316620
SELECT /*+ index(T_PM_PROJECT_DETAIL, T_PM_PROJECT_DETAIL_02) */ COUNT(1)
FROM
T_PM_PROJECT_DETAIL WHERE PROJECT_ID = :B2 AND NEXT_MATERIAL = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 13 0.00 0.00 0 0 0 0
Fetch 13 2.15 2.14 0 55744 0 13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 27 2.16 2.14 0 55744 0 13

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (MSADMIN) (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=4288 pr=0 pw=0 time=0 us)
1 TABLE ACCESS FULL T_PM_PROJECT_DETAIL (cr=4288 pr=0 pw=0 time=0 us cost=1175 size=816 card=34)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
1 SORT (AGGREGATE)
1 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'T_PM_PROJECT_DETAIL' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'T_PM_PROJECT_DETAIL_02' (INDEX)

********************************************************************************

My question is:
1、Why the 'Execution Plan' shows INDEX SCAN while the 'Row Source Operation' turns out to be FULL TABLE SCAN. I believe that there's nothing wrong with my indexes. Something relates to binding variable?
2、I know that row source operation is captured in the trace file at the time of execution, but is there any way to make 'Row Source Operation' fetch rows as I wish (in this case, INDEX SCAN)?

Thanks

SQL> create table t ( x varchar2(10), y char(40) );

Table created.

SQL>
SQL> insert into t
  2  select rownum, rownum
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

SQL>
SQL> create index IX on T ( x ) ;

Index created.

SQL>
SQL> exec dbms_stats.gather_table_stats('','T');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where x = 1234;

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

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    46 |    14   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    46 |    14   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("X")=1234)

FOR CUR_ROW_DETAIL IN L_CUR_PROJECT_DETAIL LOOP

l_my_local_var1_as_varchar2 := CUR_ROW_DETAIL.wo01;
l_my_local_var2_as_varchar2 := CUR_ROW_DETAIL.wo02;

SELECT /*+ index(T_PM_PROJECT_DETAIL, T_PM_PROJECT_DETAIL_02) */ COUNT(1)
INTO L_NUM
FROM T_PM_PROJECT_DETAIL
WHERE PROJECT_ID = l_my_local_var1_as_varchar2
AND NEXT_MATERIAL = l_my_local_var2_as_varchar2;

END LOOP;