How does Toad open a separate session for explain plan?
I noticed a strange situation where I have these entries in my tnsnames file:
DB-CLONE=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1822)
)
(CONNECT_DATA=
(SERVICE_NAME=pdbmbprd)
)
)
DB=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1921)
)
(CONNECT_DATA=
(SERVICE_NAME=pdbmbprd)
(INSTANCE_NAME=CDBMBPRD1)
)
)
With separate session for explain plan enabled, calling explain plan from a DB-CLONE session will open a session in DB instead of DB-CLONE:
----------------------------------
-- Session: XXXX@DB
-- Timestamp: 15:38:38.059
explain plan set statement_id='Tnnnnnn:060723153838' For
[...]
----------------------------------
-- Session: XXXX@DB
-- Timestamp: 15:38:39.567
Commit:
----------------------------------
-- Session: XXXX@DB
-- Timestamp: 15:38:39.623
Select *
From PLAN_TABLE
Where statement_id = :STATEMENT_ID
order by id;
:STATEMENT_ID(VARCHAR[20],IN)='Tnnnnnn:060723153838'
----------------------------------
-- Session: XXXX@DB
-- Timestamp: 15:38:39.900
select * from table(dbms_xplan.display(table_name => 'PLAN_TABLE',statement_id => 'Tnnnnnn:060723153838', format => 'TYPICAL'));
However, if I add the instance name to the DB-CLONE tnsnames entry
DB-CLONE=
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=localhost)
(PORT=1822)
)
(CONNECT_DATA=
(SERVICE_NAME=pdbmbprd)
(INSTANCE_NAME=CDBMBPRD1)
)
)
It works as expected and opens a separate session for the correct connection
----------------------------------
-- Session: XXXX@DB-CLONE
-- Timestamp: 16:05:27.405
explain plan set statement_id='Tnnnnnn:060723160527' For
select * from adj_monitor;
----------------------------------
-- Session: XXXX@DB-CLONE
-- Timestamp: 16:05:27.490
Commit:
----------------------------------
-- Session: XXXX@DB-CLONE
-- Timestamp: 16:05:27.544
Select *
From PLAN_TABLE
Where statement_id = :STATEMENT_ID
order by id;
:STATEMENT_ID(VARCHAR[20],IN)='T850233:060723160527'
----------------------------------
-- Session: XXXX@DB-CLONE
-- Timestamp: 16:05:27.776
select * from table(dbms_xplan.display(table_name => 'PLAN_TABLE',statement_id => 'T850233:060723160527', format => 'TYPICAL'));
I suspect that multiple entries having the same service_name/instance_name in the tnsnames file is confusing Toad. I thought Toad would just use the same connect info on the current session instead of going to the tnsnames file.
I just tested the Execute queries in threads (on) and Execute scripts in Toad session (off) and they behave as expected. It's only the Explain Plain that has the problem.
I stumbled onto this bug because I was testing new indexes in DB-CLONE and I was pulling my hair out trying to get the explain plan to show the query using them. Of course when the explain plan goes to the DB where the indexes don't exist then it will never use them. 
It just occurred to me that the order of the tnsnames entries may also play a role. If the order changes it may pick the wrong one (first one that matches). I haven't had time to test this yet.
@JohnDorlon ?
This is the first time you haven't commented on an issue I've posted. I hope all is well.
Hi Raymond,
I am still alive. Thanks for asking.
I was on vacay when you posted that, and it fell off my radar. Sorry about that.
I haven't tested this yet, but it sure feels like a bug.
Toad should just be grabbing the same connection info and not be confused by DB vs DB-CLONE. I wonder if you named your clone as DB_CLONE (underscore instead of dash), it would have worked better.
I'll log this so I don't forget again. Thanks for the reminder.
Hi John, glad to hear you are well and had time to go on vacation.
I will test your suggestion and let you know.
Hi Raymond,
So far I'm not able to reproduce this. Is this a RAC database?
-John
Yes, all our DBs are RAC.
Ahh, ok. Here's what's happening.
Given the following TNSNames entries...
# Pluggable, node selected by load balancing
OraCloud_21c_Plug_RAC =
(DESCRIPTION=(CONNECT_TIMEOUT=5)(TRANSPORT_CONNECT_TIMEOUT=3)(RETRY_COUNT=3)
(ADDRESS_LIST=(LOAD_BALANCE=on)
(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521))
(ADDRESS=(PROTOCOL=TCP)(HOST=xx.xx.xx.xx)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=blah)))
# Pluggable, node 1
OraCloud_21c_Plug_RAC1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = blah)
(INSTANCE_NAME = blah_1)))
# Pluggable, node 2
OraCloud_21c_Plug_RAC2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xx.xx.xx.xx)(PORT = 1521))
(CONNECT_DATA = (SERVER = DEDICATED)
(SERVICE_NAME = blah)
(INSTANCE_NAME = blah_2)))
...if you connect to OraCloud_21c_Plug_RAC, we don't know ahead of time which instance you'll actually be connected to. But you do with OraCloud_21c_Plug_RAC1 and OraCloud_21c_Plug_RAC2. So if you make your initial connection to OraCloud_21c_Plug_RAC, then do something in Toad that's going to make a 2nd session, Toad will try to find OraCloud_21c_Plug_RAC1 or OraCloud_21c_Plug_RAC2 so it can be sure to connect to the same instance as your main connection.
While I sit here and think about it, I'm not sure this is really needed for Explain Plan. But it is for killing a session or start/stop trace, and maybe a few other things.
In your case, I guess DB and DB-CLONE are actually different databases, not DB-CLONE being a load balanced entry and DB connecting to a specific instance of DB-CLONE. That would explain things.
Anyway, yes, as you've found - the simple solution is to put an instance name in there when connecting to RAC.
Now that I know what's happening, I'll look at the code and see if maybe there is a better way do to do this.
Correct, they are physically different DBs not different instances of the same DB. This is why doing an explain plan with the wrong connection was so confusing to me. The other DB didn't have the indexes I just added.
I guess I couldn't have known that from the TNS entry.
If the TNS lookup doesn't work, we run a SQL like this instead to get usable TNS info, but of course, not everyone has privileges to do that.
I think for EP, I need to just use the same connect string and not worry about connecting to the same instance of a RAC cluster.
select distinct ll.inst_id,
sn.value service_name,
ll.value tns,
ll.network
from GV$LISTENER_NETWORK ll,
GV$LISTENER_NETWORK sn,
GV$INSTANCE i
where ll.type = 'LOCAL LISTENER'
and sn.type = 'SERVICE NAME'
and sn.value = SYS_CONTEXT ('USERENV', 'SERVICE_NAME')
and ll.inst_id = sn.inst_id
and ll.inst_id = i.inst_id
and i.instance_name = :iname -- instance name of the main Toad connection goes here
order by ll.network nulls first;
1 Like
Sounds like a good solution. I can't think of any reason the instance would matter for EP.
Will that fix also solve the issue where if the instance name is not in the tnsnames entry Toad changes the setting to use the same session for EP?
Looks like the safest thing is for you to always specify instance name in your TNSNames, and/or use distinct service names for your different databases (I realize for clones that's not possible)
I have fixed it for EP (because it doesn't matter if we connect to a different instance), but the other case where we need to connect to a specific instance can still get the wrong tnsnames entry. There is no perfect solution for this.
The other cases are
- Kill/Trace session from Session Browser (and Oracle Version is less than 11)
- Pulling a Trace File from server to load into into Trace File Browser (in this case, the trace file is only on the instance that created it).
- Load Alert Log from Alert log browser (Alert log may differ depending on instance)
TNS lookup goes by service name and instance name. Of course, a cloned DB as opposed to a RAC instance could give a false match.
The SQL that I mentioned previously doesn't work in all cases. In Azure, for example, the listener is on an internal host name (which is what the SQL returns), but clients uses a different host name to connect.
I see the fix is in Toad Beta 17.0.204
Downloading now to check it out.
ok, let me know how it goes!