I have a question about which session is used to explain plan a query in TOAD.
I have the following options set for Oracle -> Transactions:
Execute queries in threads - checked
Use a separate connection when TOAD is generating transactions - unchecked
If I open a tab and execute a query I can see in the session browser that a background session is created.
If I then run an explain plan of the query it appears that it is run in the main TOAD session.
This causes a problem for me as I need the explain plan in the same session as some of my queries reply on session context attributes that affect the plan.
Is this the expected behaviour for the settings above? The separate connection option mentions Explain Plan specifically so I would have thought unchecking this would cause it to use the query’s session.
If this is behaving as designed could I then ask for an enhancement to ensure the plan is run in the same session?
In Toad we have a ‘main’ session, sessions for threads, and a
session for explain plan (and sometimes other sessions not relevant to this
discussion).
The ‘main’ connection is what most of Toad’s internal select
statements use. It’s also what the editor and SB-Tables-Data will use if
you uncheck “execute queries in threads”, and it’s also what
explain plan will use if you uncheck “use a separate connection when Toad
is generating transactions”.
So, right now, you can achieve what you want by unchecking the ‘queries in
threads’ option.
If you want to issue a ‘SET ROLE’ command for all of these sessions,
look in the help under ‘SET ROLE’ and it will tell you how to edit
TOAD.INI to do that.
If there is something else we need to do to make all the sessions
‘equal’, let me know.
I really like the threaded queries so I would be reluctant to turn it off.
Ideally what I would like to do is have two (or more) tabs open with the same query in each but different context attributes set in the background sessions. I would then like to explain plan each and compare them. Unfortunately with a third session running doing the explaining, neither generated plan is accurate.
I’m not sure of the relevance of the SET ROLE feature you mention. Do you mean I can use a similar mechanism to set context attributes across the sessions?
As you can see from my example above I actually don’t want the sessions to be “equal”!
I guess one solution is to turn off threaded queries and open multiple explicit connections. However the threaded queries feature is so convenient without the multiple connection overhead.
There’s not a ‘set context attributes’ feature like ‘set
role’, but your situation made me think of ‘set role’ so I
thought I’d mention it on the off chance that it would help. We could add
a similar ‘set context attribute’ feature if needed, but it sounds
like it’s not what you want anyway.
Maybe there is a way Mike can use the threaded session for explain plan. He
should be in soon. It may not be possible, but if it is, the limitation of that
would be that you could not do an EP while a threaded query for that session is
still running. (or vice versa). Which is something that you can do now.
Both possibilities but as John says the query has to be run first. Also the convenience of Ctrl-E in the editor is great!
I’m also left with the niggling concern that Ctrl-E is showing me an incorrect explain plan. SQL*Plus, using both dbms_xplan and autotrace, shows the correct plan.
I realise that this is probalbly a rare situation but it did cause me some head scratching. I had reverted to SQL*Plus as it seemed more reliable than TOAD for these queries - a less than ideal scenario! Once I worked out what TOAD was doing it made sense.
Hmmm – John, if it has been run already would be nice then to have a
jump-to button to send me to the session browser with correct top and bottom
tabs in focus J
Well, that’s Mike’s dept since it would be an addition to the
editor. My opinion (and that’s all it is) is that there are enough buttons
in the editor, and this one would only save a couple of clicks, so it’s
not worth the real estate.
Unless I’m missing something, this is much easier said than done.
How do you find the right query in the SGA given only the query text? We
can’t assume that the query is the most recent. We can’t query v$sql
or v$sqlarea using a ‘where’ clause with the full sql text, since
those are CLOB columns, and V$SQL_TEXT has it broken out over multiple lines.
We’d need to know how Oracle comes up with a hash value for the query, and
I don’t think that’s public knowledge.
You can calculate it directly too, but easier to use the built in,
and if the hash algorithm changes (as it did once already) hopefully
dbms_utility will be updated to keep up
Once I worked out what TOAD was doing it made sense.