HI Tom,
I have a query having parameter as a clientid. if i hardcode the clientid in the query and generate the explain then it generate it different from when i pass the same paramaeter as a bind and give the value at run time.There is drastic cost difference between the two plan generated.what would be the plan actual executed how would i know and also how it could be different?
parameter with hardcode value query
explain plan for SELECT count(*) FROM CLPT
INNER JOIN SL ON CLPT.ID = SL.ID AND CLPT.CLIENT_ID = SL.CLIENT_ID
WHERE CLPT.STATUS = 1 AND CLPT.CLIENT_ID = 3087
AND CLPT.CREATED_ON >= TO_TIMESTAMP('01/02/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/17/2019 23:59:59', 'MM/DD/YYYY HH24:MI:SS') ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);bind_param query:
explain plan for SELECT count(*) FROM CLPT
INNER JOIN SL ON CLPT.ID = SL.ID AND CLPT.CLIENT_ID = SL.CLIENT_ID
WHERE CLPT.STATUS = 1 AND CLPT.CLIENT_ID = :client_id
AND CLPT.CREATED_ON >= TO_TIMESTAMP('01/02/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND CLPT.CREATED_ON <= TO_TIMESTAMP('12/17/2019 23:59:59', 'MM/DD/YYYY HH24:MI:SS') ;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);
When you get an
EXPLAIN plan, the database doesn't run the query. It predicts what
might happen when you do.
This is a problem for bind variables because it doesn't know what value it has!
So in this example the optimizer estimates 50 rows:
create table t as
select case level
when 1 then 1
else 99
end c1
from dual
connect by level <= 100;
exec dbms_stats.gather_table_stats ( user, 't', method_opt => 'for columns c1 size skewonly' ) ;
set serveroutput off
var x number;
explain plan for
select * from t
where c1 = :x;
select *
from table(dbms_xplan.display(format => 'TYPICAL'));
PLAN_TABLE_OUTPUT
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50 | 150 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50 | 150 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=TO_NUMBER(:X))Why?
Well all it's got to go on is:
- Total rows = 100
- Num distinct values = 2
- Bind value = ¯\_(ツ)_/¯
So it just divides the number of rows by distinct values = 100 / 2 = 50 rows estimated.
It has to guess because it has no idea what the bind value is.
But when you pass the literal value 1, the optimizer knows you're only searching for rows with this value. So it can use the histogram on this column to see it expects exactly one row:
explain plan for
select * from t
where c1 = 1;
select *
from table(dbms_xplan.display(format => 'TYPICAL'));
PLAN_TABLE_OUTPUT
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 3 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=1) There are various other reasons with the
explain plan can be wrong. What you really want is the
EXECUTION plan. The plan the database really used when running the query.
Do this by running the query with the gather_plan_statistics hint (or set statistics_level = all in the session):
exec :x := 1;
select /*+ gather_plan_statistics */* from t
where c1 = :x;
select *
from table(dbms_xplan.display_cursor(format => 'ROWSTATS LAST'));
PLAN_TABLE_OUTPUT
SQL_ID 83n1jk5rqfy4f, child number 0
-------------------------------------
select /*+ gather_plan_statistics */* from t where c1 = :x
Plan hash value: 1601196873
-------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
|* 1 | TABLE ACCESS FULL| T | 1 | 1 | 1 |
-------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("C1"=:X) And you can see it correctly estimates one row (E-rows).
So why is the real plan able to get the correct estimate, when the prediction can't?
Because it's running the query, it can inspect - peek - at the value for the bind variable when choosing the plan. This allows it to get much better row estimates.