I have noticed that TOAD Session Browser will extract the SQL for the Current Statement from gv$sql_text, however the Explain Plan 1st queries v$sql_plan and then, if I'm not directly connected to the instance executing the sql, it does an explain plan and displays the results from the PLAN_TABLE (New Explain Plan). If I am connected to the instance executing the SQL statement, it will display the execution plan (Cached Explain Plan) from v$sql_plan. Is there any reason not to use gv$sql_Plan to always display the execution plan, rather than sometimes doing an explain plan? I, for one, would find this immensely helpful, rather than having to inquire into gv$sql_text myself.
For example:
User A and B are logged into different instances (RAC), say instance 1 & 2 respectively. Session Browser is started on instance 1. Session browser always uses GV$sqltext to display the Current Statement, I believe, so that's not an issue. However, to show the explain plan, it will use v$sql_plan for User A and display the cached plan because User A is connected to Instance 1. For User B, who is connected to instance 2, it will do an EXPLAIN PLAN FOR ... and display the plan from the PLAN_TABLE. For User B, it could use GV$sql_plan and display the cached plan. It would be nice if it always showed the cached plan.
If TOAD session browser used GV$sql_plan instead of V$sql_plan, there would be no need (or less of a need) to use the TOAD_PLAN_TABLE, and Session Browser could display the cached Explain Plan (or what other's call the execution plan) in most cases -- I guess not in all cases. As you noted, even if User B and User C are connected to the same instance, sometimes TOAD will use the PLAN_TABLE instead of V$sql_Plan. Maybe it uses the PLAN_TABLE if it can't find the cached plan in V$sql_plan. I don't know, but it sure would be great if TOAD would attempt to use the GV$sql_plan view all the time rather than the V$sql_plan view, just like
WORKAROUND:
None
STATUS:
Waiting for fix in a future version of Toad for Oracle.
© ALL RIGHTS RESERVED. Feedback Terms of Use Privacy Cookie Preference Center