Session browser Explain Plan tab may show incorrect results with DBMS_XPLAN display

Toad for Oracle - Beta
1

I was tuning a query that was running on Instance 1. I was connected to Instance 2 and I ran the same query (and got a slightly different execution plan).

Went back to session browser to look at execution plan on Instance 1 using DBMS_XPLAN display mode, what I actually saw was the plan I just generated on Instance 2 (same . I usually forget that DBMS_XPLAN cannot show plans from the other Instance, but I usually get an error that it couldn’t find the SQL_ID. In this case it did find it and displayed it just fine, though it wasn’t the plan I was expecting. Took me a bit to figure out why the Tree display and the DBMS_XPLAN display were different.

Can Session Browser be smart enough to tell me DBMS_XPLAN should not be used because I’m connected to the wrong Instance? Would be nice to be able to use DBMS_XPLAN regardless of what Instance I’m currently connected to, but that’s really an Oracle issue, unless you all have some inventive method to overcome their shortsightedness :).

2

Hi Dale,

Yes, I’ll make a change so that we connect to the target instance if possible, and if not possible, give an error message. Look for it once we get back into betas.

-John

3

John, I ran into this issue again. Is some sort of change in the works for this? I’m using the .33 beta that just came out.

4

Hm, looks like that one fell through the cracks, sorry… I’m logging it now so that won’t happen again.

5

actually, here is what I just noticed…if I had it on the normal Tree format, then switched to dbms_xplan, it didn’t switch instances when I did that…but when I clicked to another session in the SessB top/left, then back to the one that I wanted to do the EP on, it worked.

My main session was on dbm011, but the one I was explaining was on dbm012. This is what my spool SQL looked like.

– Timestamp: 13:28:54.021
Connect: JDORLON@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.3.83)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbm01)(INSTANCE_NAME=dbm012)))

– Session: JDORLON@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.3.83)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbm01)(INSTANCE_NAME=dbm012)))
– Timestamp: 13:28:54.653
Select *
from gv$sql_plan
Where hash_value = :sqlhv
and child_number =:cn
and inst_id =:inst
order by id;
:sqlhv(VARCHAR[10],IN/OUT)=‘1493600392’
:cn(INTEGER,IN/OUT)=0
:inst(INTEGER,IN/OUT)=2
– Elapsed Time: 0.295 seconds
– Rows fetched: 2

– Session: JDORLON@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.3.83)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbm01)(INSTANCE_NAME=dbm012)))
– Timestamp: 13:28:54.969
select * from table(dbms_xplan.display_cursor(sql_id => ‘9nk5rk9chd248’, cursor_child_no => null, format => ‘TYPICAL’));
– Elapsed Time: 0.139 seconds
– Rows fetched: 13

– Timestamp: 13:28:55.122
Disconnect: JDORLON@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.3.83)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbm01)(INSTANCE_NAME=dbm012)))

6

A further development:
This issue rears its confusing head when the same SQL_ID appears in both instances but with different execution plans.
When the SQL_ID appears in only one instance and I switch to the other instance within Session Browser, I get a three error message popups - all are for ORA-12545. Looks like those errors only happen once when I change connections, but it would be nice to avoid them, as they too are confusing.

7

I figured out why this was so dodgy. It only fails to switch to the right instance when you are connected to an instance # greater than 1, and the target instance is instance #1. Fixed for next beta.

8

By the way…regarding connections…

If you are experiencing failed connection attempts and it is due to Toad’s creating an incorrect connect string (for instance, mine above was JDORLON@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.2.3.83)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dbm01)(INSTANCE_NAME=dbm012))))…

Toad creates this via a query to GV$LISTENER_NETWORK. This technique may or may not work depending on how your listener is set up. If there is a firewall or port forwarding involved somewhere, it may not.

To get around this, create a TNSNames.ora entry on your PC for each instance.

So, for example…this is my main TNSNames.ora entry, which is load balanced, so I don’t know which instance I’ll end up on when I use this:

EXADATA12102 =
  (DESCRIPTION =
    ( ADDRESS = ( PROTOCOL = TCP )( HOST = my_awesome_server )( PORT = 1521 ) )
    ( CONNECT_DATA = ( SERVER = DEDICATED ) ( SERVICE_NAME = dbm01 )))

My instance names on this database are dbm011 and dbm012. So If I create tnsnames.ora entries for each of these, like below…Toad will use these instead of trying to figure things out via the query to GV$LISTENER_NETWORK.

dbm011 =
  (DESCRIPTION =
    ( ADDRESS = ( PROTOCOL = TCP )( HOST = my_awesome_server  )( PORT = 1521 ) )
    ( CONNECT_DATA = ( SERVER = DEDICATED ) ( SERVICE_NAME = dbm01 ) (INSTANCE_NAME = dbm011)))

dbm012 =
  (DESCRIPTION =
    ( ADDRESS = ( PROTOCOL = TCP )( HOST = my_awesome_server  )( PORT = 1521 ) )
    ( CONNECT_DATA = ( SERVER = DEDICATED ) ( SERVICE_NAME = dbm01 ) (INSTANCE_NAME = dbm012)))