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

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 :).

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

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.

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

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)))

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.

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.

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)))