Database : 10.2.0.1 (on Linux)
I have a query (see below) which is doing table scan and not use the index. If I give " Explain plan for select ...." it uses the index. But when I check execution plan for query coming from application using sql_id, it is not using the index. I tried using Index hint, didn't work through application.
SELECT
/* + Index(PK_BATCH_ID, INDX_T_TIME_BONUS) */
tb.time_bonus_id, b.* FROM t_time_bonus tb, t_batch b
WHERE tb.site_look_id=:"SYS_B_0" AND tb.batch_id=:"SYS_B_1"
AND tb.offered=:"SYS_B_2" AND tb.offer_time<sysdate AND tb.pool_batch_id=b.batch_id
AND tb.pool_promotion_id=b.promotion_id AND rownum=:"SYS_B_3" FOR UPDATE nowait
Plan hash value: 1671755995
<format> ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1084608392
-------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | FOR UPDATE | |
| 2 | COUNT | |
| 3 | FILTER | |
| 4 | HASH JOIN | |
| 5 | TABLE ACCESS BY INDEX ROWID| T_TIME_BONUS |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 6 | INDEX RANGE SCAN | INDX_T_TIME_BONUS |
| 7 | TABLE ACCESS FULL | T_BATCH |
-------------------------------------------------------------
<\format>
--------------------------
| 0 | SELECT STATEMENT | | | | | 7446 (100)| |
| 1 | FOR UPDATE | | | | | | |
| 2 | COUNT | | | | | | |
| 3 | FILTER | | | | | | |
| 4 | HASH JOIN | | 78486 | 6898K| 3296K| 7446 (3)| 00:01:30 |
| 5 | TABLE ACCESS FULL| T_TIME_BONUS | 78486 | 2376K| | 6965 (3)| 00:01:24 |
| 6 | TABLE ACCESS FULL| T_BATCH | 56289 | 3243K| | 128 (3)| 00:00:02 |
----------------------------------------------------------------------------------------------
\
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.1
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 50
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean TRUE
\
Thanks