1

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.

0
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.

0

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.

2
  • but in both cases, the child number is 0. So in either case I only see the execution plan the optimizer choses. But I want to see the execution plan costs of the plan he doesn't choose. Jan 27 at 16:55
  • 1
    @EasterBunnyBugSmasher - If you want to see the plans that the optimizer considered and rejected (which is not clear from your question), you can generate a 10053 trace for the statement blogs.oracle.com/optimizer/… Mar 16 at 22:17
0

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!

1
  • 2
    If you're trying to see the plans that the optimizer considered and discarded, you want a 10053 trace as I mentioned in the comments on the other answer. If you want to see which step of the chosen plan is most expensive, that's available from dbms_xplan. Reading through and understanding from the trace exactly why the optimizer chose a particular path and exactly how it costs every step is not trivial. But it is certainly available information-- see for example Jonatan Lewis's Cost Based Oracle Fundamentals book which would be very helpful in learning about a 10053 trace. Mar 17 at 17:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.