These are the steps I follow to generate a real Execution Plan in PLSQL developer :

1)Using this hint in my main query /+gather_plan_statistics/

select /*+gather_plan_statistics*/ from mi_factcustomer

2)Finding the "SQL_ID" and "CHILD_NUMBER" from "V$SQL" table

select t.SQL_ID , 
from v$sql t where t.SQL_TEXT like '%/*+gather_plan_statistics*/%

3)Executing below query :

select *
from table(dbms_xplan.display_cursor('3dkfspt9cgb34',0,'ALLSTATS LAST +COST +BYTES'))

1 SQL_ID  3dkfspt9cgb34, child number 0
2 -------------------------------------
3 select /*+gather_plan_statistics*/* from mi_factcustomer
5 Plan hash value: 366873549
7 -----------------------------------------------------------------------------
8 | Id  | Operation           | Name            | E-Rows |E-Bytes| Cost (%CPU)|
9 -----------------------------------------------------------------------------
10  |   0 | SELECT STATEMENT    |                 |        |       | 38239 (100)|
11  |   1 |  PARTITION RANGE ALL|                 |   5548K|  2391M| 38239   (2)|
12  |   2 |   TABLE ACCESS FULL | MI_FACTCUSTOMER |   5548K|  2391M| 38239   (2)|
13  -----------------------------------------------------------------------------
15  Note
16  -----
17     - Warning: basic plan statistics not available. These are only collected when:
18         * hint 'gather_plan_statistics' is used for the statement or
19         * parameter 'statistics_level' is set to 'ALL', at session or system level

I have two questions :

  1. Are these steps , the correct way of generating a real execution plan?(I know there are other ways but I want to know If I'm following one of the correct ones).

  2. Sometimes I get the plan and then modify the query (in order to see the difference and impact of the changes on the first plan ) and follow the above steps to get the plan but I get the following warning below the plan :

    Warning: basic plan statistics not available. These are only collected when:

    • hint 'gather_plan_statistics' is used for the statement or
    • parameter 'statistics_level' is set to 'ALL', at session or system level despite using the hint /*+gather_plan_statistics*/.

Thanks in advance

1 Answer 1

  1. Those steps work. Instead of ALLSTATS LAST +COST +BYTES I do ALLSTATS LAST TYPICAL This gets all the columns you get from the default execution plan display but with the additional row source execution statistics. I don’t know if my way gets additional columns but it will include at least cost and bytes anyway.

  2. The warning means that that child cursor was not executed with row source statistics enabled (either via the hint you used or the statistics_level parameter, as in the notes) so you can’t see the row source statistics. If you searched for a sql_id and child number using the hint text in the sql_text then you shouldn’t be getting that message (because they have the hint).

After doing a couple of experiments, the answer I have is that you are not doing complete fetches for your statement (there's over 5 million estimate rows so it's not really expected for you to fetch every result). This is sometimes fine, because when you close a cursor, you're also telling the server you are done fetching from the cursor and the row source execution statistics that it collected during execution are fine to record. When you cancel mid-execution, you haven't usually done anything server side to tell Oracle you're done. A decent solution would be to run a small statement after you cancel the execution e.g

select /*+gather_plan_statistics*/* from mi_factcustomer
exec null

This will also tell Oracle you are done with the previous cursor.

  • Thanks for your answer . Actually I did not exactly understand number 2 . what should I do the second time I'm generating the plan??
    – Pantea
    Jan 3, 2022 at 14:13
  • Exactly the same route, you’re using LAST so it’s only giving you the statistics from the latest execution. Your SQL has the gather_plan_statistics so it should always be capturing these row source execution statistics when you run it. If you have a full test case that isn’t working then please edit it into your main question, we can take a look to see where you’re going wrong Jan 3, 2022 at 14:20
  • I've just edited my question and included the real example and it's execution plan. As you can see I'm including both "SQL_ID" and "CHILD_NUMBER" when using "dbms_xplan.display_cursor" so based on what you said, there shouldn't be any problem. but again I see the warnings below the plan
    – Pantea
    Jan 3, 2022 at 14:31
  • Thanks for that, my guess is that when you executed the dbms_xplan query, the last execution for the child number you used was still in progress, or it was only parsed and not executed. It looks like a fairly ad hoc statement the only your session is going to be running so I don’t think I’ve got the right explanation. I will try and replicate Jan 3, 2022 at 14:53
  • @Pantea I've updated my answer after replicating Jan 3, 2022 at 19:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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