Query on Public Synonym hangs

SQL Navigator
1

Hi SQLNav team – I’ve hit another one that looks like it could be an underlying issue in SQLNav 6.5. I have a public synonym in a 10.1.0.5 database to a materialized view in the my schema. If I query the materialized view, I get the data back with no issue. If I query the public synonym, it locks up my SQLNav and never comes back. Clicking stop does stop the query, but never returns the session so I can do something else. Quitting SQLNav is not possible. If I query the same materialized view and synonym through SQL*Plus, both return fine. Can you replicate this there?

2

I have a 10 database and we have a bunch of MV’s. I have no problem in navigator accessing either. Have you turned on SQLMonitor to see what is actually “firing” to the database? … Sorry, called SQL Tracker and it returns exactly what is on the screen. Could your SQLPLus use a different ODBC Connection?

Message was edited by: DaleFromLozier

3

Hi Mark,

We have tested with a few 10.2.0.1.0 DBs but could not reproduce the issue here yet.

Could you please send in the sql capture in the SQL Tracker as per Dale’s note.

Thanks,
Bruce

4

I tried it again both with and without DBA views enabled. While neither worked, I’ve attached the SQL trace from each. 7088 is with the dba views enabled and 8784 is with DBA views not enabled.

The flow was as follows:
Login
Enable tracking
Query the materialized view directly
Bring up a sql worksheet
Type SELECT * from master_employee_limited (which is the public synonym for the materialized view)
Off to oblivion.
sqlnavigator.exe_7088.txt (18.3 KB)

5

I tried it again both with and without DBA views enabled. While neither worked, I’ve attached the SQL trace from each. 7088 is with the dba views enabled and 8784 is with DBA views not enabled.

The flow was as follows:
Login
Enable tracking
Query the materialized view directly
Bring up a sql worksheet
Type SELECT * from master_employee_limited (which is the public synonym for the materialized view)
Off to oblivion.
sqlnavigator.exe_8784.txt (11.4 KB)

6

Hi Mark,

Thanks for the additional info but we could not make much progress here yet.

Could you please also provide us the Support Bundle. Please go to Help->Submit Feedback, Use the ‘Save to File’ then save us the .dta file. We would like to check the Oracle client version you are using too…

Thanks,
Bruce

7

Thanks for trying Bruce. Attached is the DTA and PROF files. I’m not sure what information the dta captures, so other possibly significant information is as follows:
– Windows 7 64 Bit SP1; 6 GB RAM
– Oracle client is 11.2.0.1.0
– Underlying database with the public synonym and the materialized view is 10.1.0.5 and is a Oracle Application Server infrastructure database
– Source of data for materialized view is a 11.2 database (don’t know how that could matter, but just in case)
– Works flawlessly on a different machines running SQLNav 6.3.1 and an 11.1.0.6 client

Let me know what else you’d like or recommendations. Everything else seems to work flawlessly except the two 6.5 bugs I’ve hit (trigger properties disappear when formatting code and external table extract DDL issues on 11.2 database) both of which have been previously reported and acknowledged.
SupportBundle.dta (4.98 KB)

8

Thanks for trying Bruce. Attached is the DTA and PROF files. I’m not sure what information the dta captures, so other possibly significant information is as follows:
– Windows 7 64 Bit SP1; 6 GB RAM
– Oracle client is 11.2.0.1.0
– Underlying database with the public synonym and the materialized view is 10.1.0.5 and is a Oracle Application Server infrastructure database
– Source of data for materialized view is a 11.2 database (don’t know how that could matter, but just in case)
– Works flawlessly on a different machines running SQLNav 6.3.1 and an 11.1.0.6 client

Let me know what else you’d like or recommendations. Everything else seems to work flawlessly except the two 6.5 bugs I’ve hit (trigger properties disappear when formatting code and external table extract DDL issues on 11.2 database) both of which have been previously reported and acknowledged.
SupportBundle.prof (1.71 MB)

9

Hi Mark,

Thanks for sending us the info.

Sorry, we still could NOT reproduce this yet having similar envi as yours, ie. Oracle client 11.2, win7, MV and Synonym on 10.1 DB (but it is not a Oracle Application server infrastructure), source data on 11.2 DB…

We got other issue in v6.5 with similar behaviour you reported where the Nav hangs and not responding by doing the following steps:

  • Run a select on a table to get the initial data in the data grid, then do fetch all in the data grid, then stop the fetch all thread.
  • If do fetch all again, Nav will hang…

We are not confident yet the fix will also fix your issue, but we will try to send you a 6.6 Beta build early next week to check and see if it would fix the issue for you.

We also would like to ask if you can confirm that you ONLY get the issue with v6.5. You mentioned that 6.3 works on another envi. Much appreciated if you could have v6.5 and v6.3 installed side by side and advise that on same envi, we only have the reported issue with v6.5.

Thanks again,
Bruce

10

OK, I’m officially confused. I went ahead and installed 6.3.1 into a new folder as per Bruce’s request below. I ran the two queries – one from the materialized view directly and one from the public synonym of that MV. They both worked fine as I suspected they would.

Then, just being thorough, I ran the same queries again in 6.5 which I had done numerous times over numerous days and reboots, etc. And, of course much to my surprise, these worked also.

Is it possible there was some component in one of the Quest common folders that may have gotten rolled back or re-installed as part of installing 6.3.1 that fixed this? Or do we just call it a fluke on my system and not waste any more time on it? I uninstalled 6.3.1 and rebooted and the queries both still work.

Please let me know if there is any other info you’d like from me. Otherwise, I’ll close this question and call it solved.

11

Hi Mark,

Thank you for checking v6.3 and the updates.

We are not sure ourselves neither why it now works on your environment. the 2 versions should run independently when installing side by side.

We can close it for now. Please let us know if you would get same issue again.

Thanks again Mark for your feedback.

Thanks and regards,
Bruce