Hi.
I need a little help with some off behaviour with what should be a very simple query.
DB Version: 11.2.0.4
ACTIVITY_ATTENDANCE Rowcount: 4,300,000
Stats: Gathered pre tuning exercise.
Index on column: ACCOUNTING_FY_LOV_ID
Query 1 (using bind variable)
SELECT aa.fraction_of_date_attended AS approved_va_days
, aa.accounting_fy_lov_id as financial_year_lov_id
FROM activity_attendance aa
WHERE aa.ACCOUNTING_FY_LOV_ID = :P84_CURRENT_FY_LOV_ID;
Explain Plan
Plan hash value: 335757524
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 87062 | 425K| 2298 (1)| 00:01:07 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | 87062 | 425K| 2298 (1)| 00:01:07 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | 87062 | 425K| 2298 (1)| 00:01:07 | Q1,00 | PCWC | |
|* 4 | TABLE ACCESS FULL| ACTIVITY_ATTENDANCE | 87062 | 425K| 2298 (1)| 00:01:07 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("AA"."ACCOUNTING_FY_LOV_ID"=TO_NUMBER(:P84_CURRENT_FY_LOV_ID))
Query 1 (using literal in place of bind variable)
SELECT aa.fraction_of_date_attended AS approved_va_days
, aa.accounting_fy_lov_id as financial_year_lov_id
FROM activity_attendance aa
WHERE aa.ACCOUNTING_FY_LOV_ID = 4306;
Explain Plan
Plan hash value: 909240005
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1302 | 6510 | 129 (0)| 00:00:04 |
| 1 | TABLE ACCESS BY INDEX ROWID| ACTIVITY_ATTENDANCE | 1302 | 6510 | 129 (0)| 00:00:04 |
|* 2 | INDEX RANGE SCAN | ACTIVITY_ATTENDANCE_IDX15 | 1302 | | 5 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("AA"."ACCOUNTING_FY_LOV_ID"=4306)
Hopefully you can see from the 2 examples above, using a bind variable generates a less optimal explain plan than hard coding in a literal against the indexed column. This goes against everything I thought I knew about performance tuning and wondered if anyone can help explain why? This performance difference also manifests itself in our front end application as well. Using a bind variable takes about 3 seconds to run the report but using a hard coded literal takes 0.3 of a second.
The number of rows returned is about 1100 so the index should be being used given I have 4.3 million rows in the table.
Thanks in advance for any help.
Duncs