Hi Tom,
I have a below case in which bind variable peeking seems to not work well.
1. I have a table RETRO where is around 1,5 millions records
2. This table has a three below indexes:
- IK_RETRO_02 ON RETRO(PAR_ID, RETRO_PAYMENT_DATE)
- IK_RETRO_05 ON RETRO(NVL(RETRO_PAYMENT_DATE, STATUS_ID)
- IK_RETRO_PAYMENT_TASK_ID ON RETRO(NVL(PAYMENT_TASK_ID,(-1)))
3. This are statistics for above indexes
INDEX_NAME BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS
IK_RETRO_02 3 73113 484 641 6672 760703 14572798
IK_RETRO_05 3 75054 60 2274 18289 603546 14653539
IK_RE..T.._ID2 3 49172 8745 5 86 755098 13896555
4. Parameter _OPTIM_PEEK_USER_BINDS is set to TRUE
5. I'm executing below query
SELECT /*+ monitor gather_plan_statistics */ *
FROM RETRO R
WHERE R.PAR_ID = NVL ( :B5, R.PAR_ID)
AND R.RETRO_PAYMENT_DATE BETWEEN NVL ( :B4, R.RETRO_PAYMENT_DATE) AND NVL ( :B3, R.RETRO_PAYMENT_DATE)
AND NVL (PAYMENT_TASK_ID, :B2) = :B1;
Where
- :B1 = 11416
- :B2 = -1
- :B3 IS NULL
- :B4 IS NULL
- :B5 = 396
And I have this execution plan. As you can see the index IK_RETRO_02 has been used
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
--------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 394K(100)| | 3224 |00:00:02.20 | 3634 | 296 |
| 1 | CONCATENATION | | 1 | | | | | 3224 |00:00:02.20 | 3634 | 296 |
|* 2 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 3 | TABLE ACCESS FULL | RETRO | 0 | 1 | 397 | 394K (1)| 00:00:16 | 0 |00:00:00.01 | 0 | 0 |
|* 4 | FILTER | | 1 | | | | | 3224 |00:00:02.18 | 3634 | 296 |
|* 5 | TABLE ACCESS BY INDEX ROWID BATCHED| RETRO | 1 | 1 | 397 | 160 (1)| 00:00:01 | 3224 |00:00:02.17 | 3634 | 296 |
|* 6 | INDEX RANGE SCAN | IK_RETRO_02 | 1 | 76 | | 156 (1)| 00:00:01 | 58909 |00:00:00.60 | 502 | 293 |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1_1 / R@SEL$1
5 - SEL$1_2 / R@SEL$1_2
6 - SEL$1_2 / R@SEL$1_2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B7 IS NULL)
3 - filter(("R"."PAR_ID"=TO_NUMBER(TO_CHAR("R"."PAR_ID")) AND NVL("PAYMENT_TASK_ID",TO_NUMBER(:B2))=TO_NUMBER(:B1) AND
"R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
4 - filter(:B7 IS NOT NULL)
5 - filter(NVL("PAYMENT_TASK_ID",TO_NUMBER(:B2))=TO_NUMBER(:B1))
6 - access("R"."PAR_ID"=TO_NUMBER(:B7))
filter(("R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
6. In case when I replace to '-1'
SELECT /*+ monitor gather_plan_statistics */ *
FROM RETRO R
WHERE R.PAR_ID = NVL ( :B5, R.PAR_ID)
AND R.RETRO_PAYMENT_DATE BETWEEN NVL ( :B4, R.RETRO_PAYMENT_DATE) AND NVL ( :B3, R.RETRO_PAYMENT_DATE)
AND NVL (PAYMENT_TASK_ID, -1) = :B1;
Where
- :B1 = 11416
- :B3 IS NULL
- :B4 IS NULL
- :B5 = 396
Then I have this execution plan. As you can see the index IK_RETRO_PAYMENT_TASK_ID has been used, and this is what is seems to be much better.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 204 (100)| | 3224 |00:00:00.18 | 204 | 25 |
| 1 | CONCATENATION | | 1 | | | | | 3224 |00:00:00.18 | 204 | 25 |
|* 2 | FILTER | | 1 | | | | | 0 |00:00:00.01 | 0 | 0 |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| RETRO | 0 | 1 | 397 | 102 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 4 | INDEX RANGE SCAN | IK_RETRO_PAYMENT_TASK_ID | 0 | 1679 | | 10 (0)| 00:00:01 | 0 |00:00:00.01 | 0 | 0 |
|* 5 | FILTER | | 1 | | | | | 3224 |00:00:00.17 | 204 | 25 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED| RETRO | 1 | 1 | 397 | 102 (0)| 00:00:01 | 3224 |00:00:00.16 | 204 | 25 |
|* 7 | INDEX RANGE SCAN | IK_RETRO_PAYMENT_TASK_ID | 1 | 1679 | | 10 (0)| 00:00:01 | 3224 |00:00:00.12 | 30 | 25 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1_1 / R@SEL$1
4 - SEL$1_1 / R@SEL$1
6 - SEL$1_2 / R@SEL$1_2
7 - SEL$1_2 / R@SEL$1_2
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:B7 IS NULL)
3 - filter(("R"."PAR_ID"=TO_NUMBER(TO_CHAR("R"."PAR_ID")) AND "R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
4 - access("R"."SYS_NC00073$"=TO_NUMBER(:B1))
5 - filter(:B7 IS NOT NULL)
6 - filter(("R"."PAR_ID"=TO_NUMBER(:B7) AND "R"."RETRO_PAYMENT_DATE">=NVL(:B4,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE")) AND
"R"."RETRO_PAYMENT_DATE"<=NVL(:B3,INTERNAL_FUNCTION("R"."RETRO_PAYMENT_DATE"))))
7 - access("R"."SYS_NC00073$"=TO_NUMBER(:B1))
Pleas let me know why in point 5, the plan which I have in point 6 is not used. I'm expecting this because value of :B2 is "-1" and bind variable peeking mechanism is enabled.
------- additional information added 4th Jan 2016 ------------
Number of distinct values for the columns
select count(*) from RETRO; -- the result is 22179267
select count(distinct PAR_ID) from RETRO; -- the result is 484
select count(distinct RETRO_PAYMENT_DATE) from RETRO; -- the result is 60
select count(distinct STATUS_ID) from RETRO; -- the result is 3
select count(distinct PAYMENT_TASK_ID) from RETRO; -- the result is 8745
The DDL for a table:
CREATE TABLE RETRO
(
CLIENT_ID NUMBER(3) NOT NULL,
RETRO_ID NUMBER(11) NOT NULL,
STATUS_ID NUMBER(5) DEFAULT 21 NOT NULL,
RETRO_DATE DATE NOT NULL,
RETRO_QTY_DATE DATE NOT NULL,
RETRO_PAYMENT_DATE DATE NOT NULL,
MOD_CUR_ID NUMBER(5) NOT NULL,
PAR_CUR_ID NUMBER(5) NOT NULL,
DET_CUR_ID NUMBER(5),
RETRO_TOTALFEE NUMBER(15,12),
RETRO_MOD_CUR_AMOUNT NUMBER(27,9) NOT NULL,
RETRO_PAR_CUR_AMOUNT NUMBER(27,9) NOT NULL,
RETRO_DET_CUR_AMOUNT NUMBER(27,9),
MOD_ID NUMBER(11),
PAR_ID NUMBER(11) NOT NULL,
ORIGIN_PAR_ID NUMBER(11) NOT NULL,
QTY_PAR_ID NUMBER(11),
PG_ID NUMBER(11) NOT NULL,
LEGAL_PG_ID NUMBER(11) NOT NULL,
PBC_ID NUMBER(11),
CUST_ID NUMBER(11) NOT NULL,
PRODUCT_ID NUMBER(11) NOT NULL,
PRODUCTCATEGORY_ID NUMBER(11) DEFAULT 71 NOT NULL,
DETAIL_ID NUMBER(11),
CATEGORY_ID NUMBER(5),
ACC_ID NUMBER(11),
CON_ID NUMBER(11),
QTY_ID NUMBER(11),
TRX_ID NUMBER(11),
MOD_DET_VOLUME NUMBER(21,6),
RATE_DET_VOLUME NUMBER(21,6),
RATE_CALC_VOLUME NUMBER(21,6),
RETRO_NO_SHARES NUMBER(21,6),
RETRO_COEFFICIENT NUMBER(6,3) DEFAULT 100 NOT NULL,
RETRO_PRICE NUMBER(27,9),
MP_ID NUMBER(11),
MPG_ID NUMBER(11),
CONS_ID NUMBER(11),
RETRO_CALC_KIND_ID NUMBER DEFAULT 122 NOT NULL,
RETRO_TIMESTAMP DATE DEFAULT SYSDATE NOT NULL,
RETRO_CREATED VARCHAR2(30 BYTE) DEFAULT USER NOT NULL,
WF_STATUS_ID NUMBER(5) DEFAULT 422 NOT NULL,
SRC_ID NUMBER(11),
REC_ID NUMBER(11),
RETRO_TEMPORARY CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_CORRECTED CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_MANUAL_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_THRESHOLD_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_REVERSAL CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_FLAG CHAR(1 BYTE) DEFAULT 'N' NOT NULL,
RETRO_TEXT VARCHAR2(100 BYTE),
RETRO_TEXT_PARTS VARCHAR2(500 BYTE),
RP_ID NUMBER(11),
CALC_TASK_ID NUMBER(11),
PAYMENT_TASK_ID NUMBER(11),
ORIGIN_PAYMENT_TASK_ID NUMBER(11),
ORIGIN_RETRO_PAYMENT_DATE DATE,
RETRO_RATE_1 NUMBER(15,12),
RETRO_RATE_2 NUMBER(15,12),
RETRO_RATE_3 NUMBER(15,12),
RETRO_RATE_4 NUMBER(15,12),
RETRO_INDEX_FACTOR NUMBER(15,12),
RETRO_EXCHANGE_RATE_DET_MOD NUMBER(21,9),
RETRO_EXCHANGE_RATE_MOD_PAR NUMBER(21,9),
RETRO_EXCHANGE_RATE_DET_PAR NUMBER(21,9),
RETRO_AMOUNT_1 NUMBER(27,9),
RETRO_AMOUNT_2 NUMBER(27,9),
RETRO_AMOUNT_3 NUMBER(27,9),
RETRO_AMOUNT_4 NUMBER(27,9),
RETRO_AMOUNT_5 NUMBER(27,9),
RETRO_AMOUNT_6 NUMBER(27,9),
REF_RETRO_ID NUMBER(11),
TAX_ID NUMBER(11),
TAX_AMOUNT NUMBER(27,9),
TAX_NO_DAYS NUMBER(3),
PI_ID NUMBER(11),
PAY_CUR_ID NUMBER(5),
RETRO_PAY_CUR_AMOUNT NUMBER(27,9),
TRT_ID NUMBER(11),
RETRO_NO_DAYS NUMBER(3),
RETRO_NO_DAYS_ACTUAL NUMBER(3),
RETRO_NO_YEARDAYS NUMBER(3),
RETRO_NO_DAYS_EFF NUMBER(3),
CON_CONSERVATORY_RATE NUMBER(15,5)
);
CREATE INDEX IK_RETRO_02 ON RETRO
(PAR_ID, RETRO_PAYMENT_DATE);
CREATE INDEX IK_RETRO_05_ ON RETRO
(RETRO_PAYMENT_DATE, STATUS_ID);
CREATE INDEX IK_RETRO_PAYMENT_TASK_ID ON RETRO
(NVL("PAYMENT_TASK_ID",(-1)));
CREATE INDEX IK_RETRO_PAYMENT_TASK_ID2 ON RETRO
(PAYMENT_TASK_ID);