INST_ID invalid Identifier

Toad for Oracle - Beta
1

In Session browser I am getting ORA-00904 "INST_ID" Invalid Identifier when trying to see the explain plan on 12c or 19c

2

I am on BETA Version 14.1.42.811

3

Please turn on spool sql (main menu -> Database -> Spool sql -> Spool to screen)
then make the error happen
then post the output here.

4

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:19.441
Select * FROM GV$SESSION WHERE 0=1;

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:19.632
with ps as
(select inst_id, sid, serial#, qcsid, qcserial#
from GV$px_session
where qcserial# is not null)
Select PS.QCSID as parent_session_sid, PS.QCSERIAL# as parent_session_serial, PS.inst_id as parent_session_instid, s.INST_ID, rawtohex(s.SADDR) as SADDR, s.SID, s.SERIAL#, s.AUDSID, rawtohex(s.PADDR) as PADDR, s.USER#, s.USERNAME, s.COMMAND, s.OWNERID, s.TADDR, s.LOCKWAIT, s.STATUS, s.SERVER, s.SCHEMA#, s.SCHEMANAME, s.OSUSER, s.PROCESS, s.MACHINE, s.PORT, s.TERMINAL, UPPER(s.PROGRAM) PROGRAM, s.TYPE, s.SQL_ADDRESS, s.SQL_HASH_VALUE, s.SQL_ID, s.SQL_CHILD_NUMBER, s.SQL_EXEC_START, s.SQL_EXEC_ID, s.PREV_SQL_ADDR, s.PREV_HASH_VALUE, s.PREV_SQL_ID, s.PREV_CHILD_NUMBER, s.PREV_EXEC_START, s.PREV_EXEC_ID, s.PLSQL_ENTRY_OBJECT_ID, s.PLSQL_ENTRY_SUBPROGRAM_ID, s.PLSQL_OBJECT_ID, s.PLSQL_SUBPROGRAM_ID, s.MODULE, s.MODULE_HASH, substr(s.ACTION, 1, 64) as ACTION, s.ACTION_HASH, s.CLIENT_INFO, s.FIXED_TABLE_SEQUENCE, s.ROW_WAIT_OBJ#, s.ROW_WAIT_FILE#, s.ROW_WAIT_BLOCK#, s.ROW_WAIT_ROW#, s.TOP_LEVEL_CALL#, s.LOGON_TIME, s.LAST_CALL_ET, s.PDML_ENABLED, s.FAILOVER_TYPE, s.FAILOVER_METHOD, s.FAILED_OVER, s.RESOURCE_CONSUMER_GROUP, s.PDML_STATUS, s.PDDL_STATUS, s.PQ_STATUS, s.CURRENT_QUEUE_DURATION, s.CLIENT_IDENTIFIER, s.BLOCKING_SESSION_STATUS, s.BLOCKING_INSTANCE, s.BLOCKING_SESSION, s.FINAL_BLOCKING_SESSION_STATUS, s.FINAL_BLOCKING_INSTANCE, s.FINAL_BLOCKING_SESSION, s.SEQ#, s.EVENT#, s.EVENT, s.P1TEXT, s.P1, s.P1RAW, s.P2TEXT, s.P2, s.P2RAW, s.P3TEXT, s.P3, s.P3RAW, s.WAIT_CLASS_ID, s.WAIT_CLASS#, s.WAIT_CLASS, s.WAIT_TIME, s.SECONDS_IN_WAIT, s.STATE, s.WAIT_TIME_MICRO, s.TIME_REMAINING_MICRO, s.TIME_SINCE_LAST_WAIT_MICRO, s.SERVICE_NAME, s.SQL_TRACE, s.SQL_TRACE_WAITS, s.SQL_TRACE_BINDS, s.SQL_TRACE_PLAN_STATS, s.SESSION_EDITION_ID, s.CREATOR_ADDR, s.CREATOR_SERIAL#, s.ECID, s.SQL_TRANSLATION_PROFILE_ID, s.PGA_TUNABLE_MEM, s.SHARD_DDL_STATUS, s.CON_ID, s.EXTERNAL_NAME, s.PLSQL_DEBUGGER_CONNECTED
FROM GV$SESSION S, PS
WHERE ( (s.USERNAME is not null) and (NVL(s.osuser,'x') <> 'SYSTEM') and (s.type <> 'BACKGROUND') )
and PS.SID (+) = S.SID AND PS.SERIAL# (+) = S.SERIAL# and PS.INST_ID (+) = S.INST_ID
order by STATUS, OWNERID;

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:32.575
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from gv$session
where sid = :sid
and inst_id = :inst;
:sid(VARCHAR[4],IN)='1495'
:inst(INTEGER,IN)=1

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:32.588
Select *
from gv$sql
where inst_id = :inst_id
and sql_id = :sqlid;
:inst_id(INTEGER,IN)=1
:sqlid(VARCHAR[13],IN)='363yc8jybbzm9'

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:36.376
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from gv$session
where sid = :sid
and inst_id = :inst;
:sid(VARCHAR[4],IN)='1495'
:inst(INTEGER,IN)=1

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:36.400
WITH display_map AS
(SELECT X.*
FROM v$sql_plan,
XMLTABLE ('/other_xml/display_map/row'
passing case when other_xml IS NULL then null else XMLTYPE(other_xml ) end
COLUMNS
op NUMBER PATH '@op',
dis NUMBER PATH '@dis',
par NUMBER PATH '@par',
dep NUMBER PATH '@dep',
skp NUMBER PATH '@skp') as X
Where hash_value = :sqlhv
and child_number = :cn
and inst_id = :inst
and other_xml IS NOT NULL)
SELECT
NVL(m.dis, sp.id) as id,
NVL(m.par, sp.parent_id) as parent_id,
NVL(m.dep, sp.depth) as depth,
decode(m.skp, 1, 'Y', null) as inactive,
sp.*
FROM v$sql_plan sp,
display_map m
Where m.op (+) = sp.id
and sp.hash_value = :sqlhv
and sp.child_number = :cn
and sp.inst_id = :inst
AND NVL(m.skp,0) <> 1
ORDER BY sp.id;
-- ORA-00904: "INST_ID": invalid identifier
:sqlhv(VARCHAR[10],IN)='2092301929'
:cn(INTEGER,IN)=2
:inst(INTEGER,IN)=1

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:54.602
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from gv$session
where sid = :sid
and inst_id = :inst;
:sid(VARCHAR[4],IN)='1495'
:inst(INTEGER,IN)=1

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:15:54.615
Select *
from gv$sql
where inst_id = :inst_id
and sql_id = :sqlid;
:inst_id(INTEGER,IN)=1
:sqlid(VARCHAR[13],IN)='363yc8jybbzm9'

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:16:05.142
Select schemaname, decode(sql_hash_value, 0, prev_hash_value, sql_hash_value) hash,
decode(sql_hash_value, 0, RawToHex(prev_sql_addr), RawToHex(sql_address)) address
, decode(sql_id, null, prev_sql_id, sql_id) sql_id
, decode(sql_child_number, null, prev_child_number, sql_child_number) sql_child_number
from gv$session
where sid = :sid
and inst_id = :inst;
:sid(VARCHAR[4],IN)='1495'
:inst(INTEGER,IN)=1

-- Session: SCHOLM2L1C@STAGING_DEV
-- Timestamp: 11:16:05.163
WITH display_map AS
(SELECT X.*
FROM v$sql_plan,
XMLTABLE ('/other_xml/display_map/row'
passing case when other_xml IS NULL then null else XMLTYPE(other_xml ) end
COLUMNS
op NUMBER PATH '@op',
dis NUMBER PATH '@dis',
par NUMBER PATH '@par',
dep NUMBER PATH '@dep',
skp NUMBER PATH '@skp') as X
Where hash_value = :sqlhv
and child_number = :cn
and inst_id = :inst
and other_xml IS NOT NULL)
SELECT
NVL(m.dis, sp.id) as id,
NVL(m.par, sp.parent_id) as parent_id,
NVL(m.dep, sp.depth) as depth,
decode(m.skp, 1, 'Y', null) as inactive,
sp.*
FROM v$sql_plan sp,
display_map m
Where m.op (+) = sp.id
and sp.hash_value = :sqlhv
and sp.child_number = :cn
and sp.inst_id = :inst
AND NVL(m.skp,0) <> 1
ORDER BY sp.id;
-- ORA-00904: "INST_ID": invalid identifier
:sqlhv(VARCHAR[10],IN)='2092301929'
:cn(INTEGER,IN)=2
:inst(INTEGER,IN)=1

5

Ah! Thank you. I see that too. Will fix.

As a workaround, if the SQL that you want to explain is on the same instance you are connected to, you can uncheck "Use RAC Views"

6

Do you know when this will be fixed and in what beta version?

7

A beta will be coming out today with this fix, among others.