Toad World® Forums

SQLNav hangs when opening a (debuggable(?)) package

Hello,
actually it doesn’t really hang, but keeps bombing the database with the following plsql (as the SQL Tracker reveals):

BEGIN
SELECT debuginfo
INTO :result
FROM sys.all_probe_objects
WHERE owner = :owner
AND object_name = :name
AND object_type = :type
AND status = ‘VALID’;
END;

If the result is F, the plsql gets executed once. If the result is T, SQLNav keeps running the same query and nothing much seems that can be done. Even if the session is killed, SQLNav doesn’t return to the user but opens a new session and keeps bombing…

Tried with 6.4.0.1930 and 6.5.0.2032

Any suggestions?

I tried with a smaller package and it seems somehow to be related with the size of the package (number of executable lines?)
I tried with a package body with 3 proecedures with total of 554 lines. The plsql block above executed 217 times. As each query took ~0.3 seconds, it took way over a minute to complete. I had no patience to wait whether then ~9000 row package would also open though…

Hi Andres,

Roman is still working on this and could not reproduce the issue you reported yet with large package in our test envi.

Would it posible if you could please provide the script to create the package that you have issue with? and could you please also provide us the steps.

Thanks and regards,
Bruce

I took a small package AT_TEST_PKT, 294 total lines, 24 test procedures-functions.
Opened the Quest sql tracker to see what’s going on. I typed the name in code editor and did Ctrl+Enter on it to open. As the package was initially not compiled with debug, the queries to the database were as in the attachment result_no_debug.txt. The query in the first post ran once. Then i closed the package and executed

alter package at_test_pkt compile plsql_debug = true;

And again clicked ctrl+Enter on it. And the result is in result_debug.txt. The query ran three times at first with some other queries in the middle and then finally 187 more times. There are not that many executable lines in the package!.
Here one query took in average 0.0x seconds. I just finshed waiting for another package where each query took 1.2-1.3 seconds. And i waited for over an hour for a 8000-line package to open, where each query took 2-60(!) seconds! Can this please be fixed so that the query will run once (ok, three times, but not hundreds).

I also have the body and spec of AT_TEST_PKT here, but you probably have to comment out some of it in order to compile the package with no errors.

And about the reconnect thing, you’ll have to find out that how (when the session is executing these queries from all_probe_objects) SQLNavigator is able to reconnect without asking and continue to run these queries (in different database session), when killed from another session. But that’s not that critical if you can just skip the queries altogether…
result_debug.txt (106 KB)

I took a small package AT_TEST_PKT, 294 total lines, 24 test procedures-functions.
Opened the Quest sql tracker to see what’s going on. I typed the name in code editor and did Ctrl+Enter on it to open. As the package was initially not compiled with debug, the queries to the database were as in the attachment result_no_debug.txt. The query in the first post ran once. Then i closed the package and executed

alter package at_test_pkt compile plsql_debug = true;

And again clicked ctrl+Enter on it. And the result is in result_debug.txt. The query ran three times at first with some other queries in the middle and then finally 187 more times. There are not that many executable lines in the package!.
Here one query took in average 0.0x seconds. I just finshed waiting for another package where each query took 1.2-1.3 seconds. And i waited for over an hour for a 8000-line package to open, where each query took 2-60(!) seconds! Can this please be fixed so that the query will run once (ok, three times, but not hundreds).

I also have the body and spec of AT_TEST_PKT here, but you probably have to comment out some of it in order to compile the package with no errors.

And about the reconnect thing, you’ll have to find out that how (when the session is executing these queries from all_probe_objects) SQLNavigator is able to reconnect without asking and continue to run these queries (in different database session), when killed from another session. But that’s not that critical if you can just skip the queries altogether…
result_no_debug.txt (5.2 KB)

I took a small package AT_TEST_PKT, 294 total lines, 24 test procedures-functions.
Opened the Quest sql tracker to see what’s going on. I typed the name in code editor and did Ctrl+Enter on it to open. As the package was initially not compiled with debug, the queries to the database were as in the attachment result_no_debug.txt. The query in the first post ran once. Then i closed the package and executed

alter package at_test_pkt compile plsql_debug = true;

And again clicked ctrl+Enter on it. And the result is in result_debug.txt. The query ran three times at first with some other queries in the middle and then finally 187 more times. There are not that many executable lines in the package!.
Here one query took in average 0.0x seconds. I just finshed waiting for another package where each query took 1.2-1.3 seconds. And i waited for over an hour for a 8000-line package to open, where each query took 2-60(!) seconds! Can this please be fixed so that the query will run once (ok, three times, but not hundreds).

I also have the body and spec of AT_TEST_PKT here, but you probably have to comment out some of it in order to compile the package with no errors.

And about the reconnect thing, you’ll have to find out that how (when the session is executing these queries from all_probe_objects) SQLNavigator is able to reconnect without asking and continue to run these queries (in different database session), when killed from another session. But that’s not that critical if you can just skip the queries altogether…
at_test_pkt_body.txt (6.83 KB)

I took a small package AT_TEST_PKT, 294 total lines, 24 test procedures-functions.
Opened the Quest sql tracker to see what’s going on. I typed the name in code editor and did Ctrl+Enter on it to open. As the package was initially not compiled with debug, the queries to the database were as in the attachment result_no_debug.txt. The query in the first post ran once. Then i closed the package and executed

alter package at_test_pkt compile plsql_debug = true;

And again clicked ctrl+Enter on it. And the result is in result_debug.txt. The query ran three times at first with some other queries in the middle and then finally 187 more times. There are not that many executable lines in the package!.
Here one query took in average 0.0x seconds. I just finshed waiting for another package where each query took 1.2-1.3 seconds. And i waited for over an hour for a 8000-line package to open, where each query took 2-60(!) seconds! Can this please be fixed so that the query will run once (ok, three times, but not hundreds).

I also have the body and spec of AT_TEST_PKT here, but you probably have to comment out some of it in order to compile the package with no errors.

And about the reconnect thing, you’ll have to find out that how (when the session is executing these queries from all_probe_objects) SQLNavigator is able to reconnect without asking and continue to run these queries (in different database session), when killed from another session. But that’s not that critical if you can just skip the queries altogether…
at_test_pkt_spec.txt (667 Bytes)

Hi Andres,

Thank you for sending us the addtional info.

We are checking it out and will keep you posted.

Thanks and regards,
Bruce

Hi Andres,

We could reproduce the issue here now. We will try to provide the fix in another beta drop for you.

Thanks again,
Bruce

Thank you for your quick reply, looking forward to the new beta!

Andres

Hi Andres,

While we are still waiting for Henry’s info on the View Difference issue, we would like to send you the new build so you can check out thehang issue with debug info. Build 2078 is available for you from
http://sqlnavigator.inside.quest.com/shares/sqlnavigator/sbin/beta/2078/sqlnav_6.5.0.2078-KX_beta.zip

After we resolve the View Difference issues, we will release the new beta build to the whole Community.

Thanks,
Bruce

Yes, the issue does not appear in build 2078, thanks a lot!

Andres

But now a bit different but related issue. I didn’t notice this behaviour in previous beta, but this one is not present in 6.4. When i open new code editor, and press “s” (in order to type “select”), the first query that goes to the database is…

BEGIN
SELECT debuginfo
INTO :result
FROM sys.all_probe_objects
WHERE owner = :owner
AND object_name = :name
AND object_type = :type
AND status = ‘VALID’;
END;

result=[NULL]
owner=[‘XXXXXX’]
name=[NULL]
TYPE=[NULL]

Elapsed time: 0.234

See that both name and type are NULL, only owner is given. What’s the point of that? And when typing fast (so that autocomplete does not have a chance to hop in) - the same query is still executed - seems that after every keystroke - and … it takes time …

Andres

Even when editing a package, each keystroke produces the above query. This time name and type are given, but still it is not good when the query is executed so frequently.

Message was edited by: Andres

Marking unanswered because related unintended behaviour

Hi Andres,

This check is needed to update the breakpoint icons in the editor’s gutter on every source change. E.g. if you insert or delete line, the icons should be updated, unless the object has no debug info with it. Unfortunately, the logic in the corresponding routine isn’t perfect and can be optimized. We will do this in the next Beta release.

Thank you,
Roman

roman wrote:

/—/
unless the object has no debug info with it.
/—/

Sorry, in plain simple code editor there is NO OBJECT AT ALL?? whats the point of querying “and object_name = null”. I wouldn’t complain but the query against sys.all_probe_objects isn’t a simple query. It goes against all_objects (not dba_objects) and has a “distinct” in it, so depending on “when it rains on monday” and some other unknown circumstances it takes 0.01 - 2 seconds per query. Right now it takes 0.2 seconds. So i type a simple query of about 60 characters long - and then wait five seconds for SQL Navigator running remaining 25 times 0.2 seconds the query for the object that even does not exists?? How come this debugging thing comes alive in an anonymous sql editor??

Hi Andres,

Much appreaciated if you could please check out build 2080 and let us know if you are happy with build in the area of too many sql fired on the debug info as reported in your last post.

Build 2080 is now available for you to download - http://sqlnavigator.inside.quest.com/shares/sqlnavigator/sbin/beta/2080/sqlnav_6.5.0.2080-KX_beta.zip

Thanks and regards,
Bruce

Hi Andres,

Just would like to let you know that we still have issue in build 2080 as you reported where the debug info sql fired every key stroke. We will send you another drop later.

Thanks and regards,
Bruce

Hi Andres

Please try our latest build, it should no longer fire SQL with every keystroke when debug info is ON.
[

http://sqlnavigator.inside.quest.com/thread.jspa?threadID=31998](http://sqlnavigator.inside.quest.com/thread.jspa?threadID=31998)

regards
Lidia

Hi Lidia,
thanks, it really seems it has been fixed now!

Andres