SQL Navigator 6.3 Session Browser

SQL Navigator
1

I found a weird bug using the Session Browser,

When selecting a statement I got the “SQLN_EXPLAIN_PLAN” table does not exits, and I answered yes for it to be created, afterward the public synonym question was asked and I told it to create it. After that, i was again asked to create the SQLN_EXPLAIN_PLAN table, hitting yes again, it went into a infinite loop on it till I killed the session. Here is the SQL trace from the Monitoring Tool (which I had running at the moment).

Timestamp: 23/7/10 4:17:38 PM

select cost, cardinality from RSANCHEZ.SQLN_EXPLAIN_PLAN

Describe Only

Timestamp: 23/7/10 4:17:38 PM

select /*+ rule sqlab */ * from RSANCHEZ.SQLN_EXPLAIN_PLAN where statement_id = ‘CFF6E222’ order by id

stmt_id=[‘CFF6E222’]

Elapsed time: 0.000

Timestamp: 23/7/10 4:17:40 PM

create public synonym SQLN_EXPLAIN_PLAN for SQLN_EXPLAIN_PLAN

Error occurred: [955] (ORA-00955: name is already used by an existing object
)

Timestamp: 23/7/10 4:17:40 PM

explain plan set statement_id = ‘CFF6E224’ into RSANCHEZ.RSANCHEZ.SQLN_EXPLAIN_PLAN for SELECT a.telefono,
a.fecha,
a.hora,
a.imei,
a.tac,
a.anexo,
a.ROWID
FROM cext.log_telefono_imei a

Error occurred: [905] (ORA-00905: missing keyword
)

Timestamp: 23/7/10 4:17:40 PM

select cost, cardinality from RSANCHEZ.RSANCHEZ.SQLN_EXPLAIN_PLAN

Error occurred: [933] (ORA-00933: SQL command not properly ended
)

Timestamp: 23/7/10 4:17:46 PM

select ‘1’ from RSANCHEZ.RSANCHEZ.RSANCHEZ.SQLN_EXPLAIN_PLAN

Error occurred: [933] (ORA-00933: SQL command not properly ended
)

Timestamp: 23/7/10 4:17:46 PM

create table RSANCHEZ.RSANCHEZ.RSANCHEZ.SQLN_EXPLAIN_PLAN ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(30), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns numeric, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30))

Error occurred: [922] (ORA-00922: missing or invalid option
)

Timestamp: 23/7/10 4:17:46 PM

explain plan set statement_id = ‘CFF6E22A’ into RSANCHEZ.RSANCHEZ.RSANCHEZ.SQLN_EXPLAIN_PLAN for SELECT a.telefono,
a.fecha,
a.hora,
a.imei,
a.tac,
a.anexo,
a.ROWID
FROM cext.log_telefono_imei a

Error occurred: [905] (ORA-00905: missing keyword
)

2

Hi Rony,

I cannot replicate your problem. Please try to execute the following SQL and send us the results.

select * from sys.all_synonyms
where SYNONYM_Name like ‘SQLN_%’

Thanks
Jeff

3
  1. Create a session
  2. Make sure no explain plan table exists for the current user
  3. In the preferences, leave empty the Explain Plan Table Owner
  4. Enter the session browser
  5. Click on Current SQL tab
  6. Select any other session

In the output window you should get “Invalid variant type conversion”, close that and the error cycle will start.

The resulting dataset for the sql you gave me is:

PUBLIC SQLN_EXPLAIN_PLAN ALARMAS SQLN_EXPLAIN_PLAN

The ALARMAS.SQL_EXPLAIN_PLAN has a grant all on public

4

W can reproduce this problem in our environment now, a CR have been create on your behalf and will fix it in next release.

The workaround is manually create the plan table using the following SQL

**create table SQLN_EXPLAIN_PLAN ( statement_id varchar2(30),
timestamp date,
remarks varchar2(80),
operation varchar2(30),
options varchar2(30),
object_node varchar2(128),
object_owner varchar2(30),
object_name varchar2(30),
object_instance numeric,
object_type varchar2(30),
optimizer varchar2(255),
search_columns numeric,
id numeric,
parent_id numeric,
position numeric,
cost numeric,
cardinality numeric,
bytes numeric,
other_tag varchar2(255),
partition_start varchar2(255),
partition_stop varchar2(255),
partition_id numeric,
other long,
distribution varchar2(30))
**If the public synonym does not exist, create it manually as well.

create public synonym SQLN_EXPLAIN_PLAN for SQLN_EXPLAIN_PLAN

Thanks for let us know this bug.

Message was edited by: jchan

5

Thanks for the help!

Regards