evaluate execution plan with different statements

Question

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.

Answer 1

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.

Answer 2

Find the child_number of the plan (plan_hash_value) you want to view:

select sql_id, child_number, plan_hash_value from v$sql where sql_id = 'abcd';

Using the sql_id (abcd) and child_number (0), view the plan:

select * from table(dbms_xplan.display_cursor('abcd', 0));

If the statement was not executed and can not be found in v$sql:

explain plan for SELECT * FROM complicated_view v WHERE v.day = DATE '2020-01-25';
select * from table(dbms_xplan.display);

explain plan for SELECT * FROM complicated_view v WHERE v.day = DATE '2021-01-25';
select * from table(dbms_xplan.display);

explain plan for SELECT /*+ optimizer_features_enable('12.2.0.1') */ * FROM complicated_view v WHERE v.day = DATE '2021-01-25';
select * from table(dbms_xplan.display);

And so on.

Answer 3

It turns out that Oracle doesn't provide such functionality for the public. I talked to two of our DBAs and a Consultant, and the common opinion was that it is likely that a functionality similar to this exists, but is probably considered company secrets, just like how they evaluate execution plans.

thanks to all those who tried to help me!