Hi I am using version 11.2.0.4.0 of oracle. I have a query which is going for bad execution plan due to poor cardinality estimation for two tables(i have extracted and published that portion only) as i have mentioned below, individual conditions for which the estimation is going bad and affecting whole query execution path.
These are for two tables and currently we are using BIND variable for these in our code, and i am noticing, its giving better estimation with literals. I need to know, how to handle this scenario as i need this query to run for all types of volumes. Is there something i can do, without changing the code, as it runs fine for most of the exeution? In current scenario the main query which uses these below two tables providing a plan(index +nested loop) which runs fine for small volume but running for 10hr+ for large volume as ideally its going for same plan.
And yes, most of the time this query will be hit for small volume , but few occurrence of large volume killing this query performance.
Below is the values of the bind variable.
VARIABLE B1 VARCHAR2(32);
VARIABLE B2 VARCHAR2(32);
VARIABLE B3 NUMBER;
VARIABLE B4 VARCHAR2(32) ;
VARIABLE B7 VARCHAR2(32) ;
VARIABLE B5 NUMBER ;
VARIABLE B6 NUMBER ;
EXEC :B1 := 'NONE';
EXEC :B2 := NULL;
EXEC :B3 := 0;
EXEC :B4 := NULL;
EXEC :B7 := NULL;
EXEC :B5 := 0;
EXEC :B6 := 0;
---- For TABLE1-------
-- Published Actual VS Etimated cardinality
-- With bind values
select * from TABLE1 SF
WHERE ( (SF.C1_IDCODE = :B4) OR (NVL (:B4, 'NONE') = 'NONE'))
AND ( (SF.C2_ID = :B3) OR (NVL (:B3, 0) = 0));
Plan hash value: 2590266031
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 28835 |00:00:00.08 | 2748 | 46 | | | |
|* 1 | TABLE ACCESS STORAGE FULL| TABLE1 | 1 | 11 | 28835 |00:00:00.08 | 2748 | 46 | 1025K| 1025K| |
-----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - storage((("SF"."C1_IDCODE"=:B4 OR NVL(:B4,'NONE')='NONE') AND ("SF"."C2_ID"=:B3 OR NVL(:B3,0)=0)))
filter((("SF"."C1_IDCODE"=:B4 OR NVL(:B4,'NONE')='NONE') AND ("SF"."C2_ID"=:B3 OR NVL(:B3,0)=0)))
-- With literals
select * from TABLE1 SF
WHERE ( (SF.C1_IDCODE = null) OR (NVL (null, 'NONE') = 'NONE'))
AND ( (SF.C2_ID = 0) OR (NVL (0, 0) = 0));
Plan hash value: 2590266031
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 28835 |00:00:00.03 | 2748 | | | |
| 1 | TABLE ACCESS STORAGE FULL| TABLE1 | 1 | 28835 | 28835 |00:00:00.03 | 2748 | 1025K| 1025K| |
--------------------------------------------------------------------------------------------------------------------------------------
--------For TABLE2 -----------------------
-- Published Autotrace plan, as it was taking long time for completion, and actual cardinality is 45M, but its estimating 49 With bind value---
--withbind value
select * from TABLE2 MTF
WHERE ( (MTF.C6_CODE = TRIM (:B2)) OR (NVL (:B2, 'NONE') = 'NONE'))
AND ( (MTF.C3_CODE = :B1) OR (NVL (:B1, 'NONE') = 'NONE'))
AND ( (MTF.C4_CODE = :B7) OR (:B7 IS NULL))
AND ( (MTF.C5_AMT <= :B6) OR (NVL (:B6, 0) = 0))
AND ( (MTF.C5_AMT >= :B5) OR (NVL (:B5, 0) = 0));
Execution Plan
----------------------------------------------------------
Plan hash value: 1536592532
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 49 | 10437 | 358K (1)| 01:11:43 | | |
| 1 | PARTITION RANGE ALL | | 49 | 10437 | 358K (1)| 01:11:43 | 1 | 2 |
|* 2 | TABLE ACCESS STORAGE FULL| TABLE2 | 49 | 10437 | 358K (1)| 01:11:43 | 1 | 2 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - storage(("MTF"."C4_CODE"=:B7 OR :B7 IS NULL) AND ("MTF"."C3_CODE"=:B1 OR
NVL(:B1,'NONE')='NONE') AND ("MTF"."C5_AMT"<=TO_NUMBER(:B6) OR NVL(:B6,0)=0) AND
("MTF"."C5_AMT">=TO_NUMBER(:B5) OR NVL(:B5,0)=0) AND ("MTF"."C6_CODE"=TRIM(:B2) OR
NVL(:B2,'NONE')='NONE'))
filter(("MTF"."C4_CODE"=:B7 OR :B7 IS NULL) AND ("MTF"."C3_CODE"=:B1 OR
NVL(:B1,'NONE')='NONE') AND ("MTF"."C5_AMT"<=TO_NUMBER(:B6) OR NVL(:B6,0)=0) AND
("MTF"."C5_AMT">=TO_NUMBER(:B5) OR NVL(:B5,0)=0) AND ("MTF"."C6_CODE"=TRIM(:B2) OR
NVL(:B2,'NONE')='NONE'))
-- with literal
select * from TABLE2 MTF
WHERE ( (MTF.C6_CODE = TRIM (null)) OR (NVL (null, 'NONE') = 'NONE'))
AND ( (MTF.C3_CODE = 'NONE') OR (NVL ('NONE', 'NONE') = 'NONE'))
AND ( (MTF.C4_CODE = null) OR (null IS NULL))
AND ( (MTF.C5_AMT <= 0) OR (NVL (0, 0) = 0))
AND ( (MTF.C5_AMT >= 0) OR (NVL (0, 0) = 0));
Execution Plan
----------------------------------------------------------
Plan hash value: 1536592532
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45M| 9151M| 358K (1)| 01:11:41 | | |
| 1 | PARTITION RANGE ALL | | 45M| 9151M| 358K (1)| 01:11:41 | 1 | 2 |
| 2 | TABLE ACCESS STORAGE FULL| TABLE2 | 45M| 9151M| 358K (1)| 01:11:41 | 1 | 2 |
-----------------------------------------------------------------------------------------------------------
select column_name,num_nulls,num_distinct,density
from dba_tab_col_statistics where table_name='TABLE2'
and column_name in ('C3_CODE','C4_CODE','C5_AMT','C6_CODE');
C3_CODE 0 65 0.0153846153846154
C4_CODE 0 2 0.5
C5_AMT 0 21544 4.64166357222429E-5
C6_CODE 1889955 71 0.0140845070422535