Evaluate execution plan with different statements

execution-planoracle

I have 2 different sql statements similar to this:

SELECT * FROM complicated_view v WHERE v.day = DATE '2020-01-25';
SELECT * FROM complicated_view v WHERE v.day = DATE '2021-01-25';

while both of them could be executed with the same execution plan, they don't. One is slow, the other one is fast. Now I'm trying to figure out why. What I'm currently looking for, is a way to evaluate an SQL statement identified by its sql_id with different execution plans, so I can see which step the optimizer estimates to be pretty costly. Something like this:

select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash2');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash1');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id2', plan_hash_value => 'plan_hash2');

Unfortunately, there is no function "evaluate_plan" in dbms_xplan, it's a functionality I'm missing and hope to get a hint on how to get this information.

That way I could identify the step that the optimizer considers to be too costly and work on that.
Is there any functionality in oracle that can give me this information?

oh, and here is the advanced question: Can I do this and in addition also add the optimizer_feature enable, something like this:

select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '19.1.0');
select * from table (dbms_xplan.evaluate_plan(sql_id => 'sql_id1', plan_hash_value => 'plan_hash1', OPTIMIZER_FEATURES_ENABLE => '12.2.0.1');

we're getting very different runtime behaviour due to this setting.

I'm not looking for help on how to make my view better. I'm not asking WHY the statement is slow. I'm looking for help on HOW to work on problems like this. I'm looking for a way to evaluate an execution plan for a statement in a scenario where it COULD be executed with the execution plan, but has never been, since another execution plan was always used.

Best Answer

If you can run the query with the different plans (i.e. if it does not run forever) you can use this approach to see which step ran longer than the optimizer expected:

SELECT /*+gather_plan_statistics*/ * 
FROM complicated_view v 
WHERE v.day = DATE '2020-01-25';
                 
select * 
from table(dbms_xplan.display_cursor(null,null,'ALLSTATS'));

If you search for gather_plan_statistics you will see a number of articles that explain how to use it. I look for steps in the plan that take the most time and whose actual rows are very different from the optimizer's estimated row count.

Related Question