Toad World® Forums

nav 6.5 appears to be slow on first startup


#1

while connecting to my oracle servers with nav 6.5, it took an extremely long time to read the schema list of my server.

I am not sure if it’s related to nav or not but this is the third time this has happened.

The first connection seems to be slow. The second time I restart nav it is not slow and nav is back to it’s super responsive self.


#2

Restarting does not fix. I restarted nav again today and the first time nav opened the schema’s it took a veeeerrrrrrrryyyyyyyyy llllllllooooooooonnnnnnnnnngggggggggggg time.


#3

Don’t know if this would help, but maybe you should turn on sqltracker and see what query is running and maybe see what is taking so long… Again not sure if this would help.


#4

sql nav 6.5 takes 1 min 25 seconds to open my schema node.

sql nav 6.4 takes 2 seconds to open the same schema node on the same server.


#5

no that wont help. I know this server does not have a big load. Plus I just compaired the time to open the node with nav 6.4.


#6

I did a trace on the startup query.

6.4
/* Formatted on 12-Jan-2011 17:29:04 (QP5 v5.160) */
SELECT username
FROM sys.all_users
WHERE EXISTS
(SELECT ‘x’
FROM sys.all_objects
WHERE owner = username)
OR username = USER
ORDER BY username

6.5
SELECT u.username
FROM sys.all_users u
WHERE u.username <> USER
AND EXISTS
(SELECT ‘x’
FROM sys.all_objects o
WHERE o.owner = u.username)
UNION ALL
SELECT USER FROM DUAL

This is the query you use to populate the schema list. The difference is the or statement. This line of code adds over a minute and 15 seconds to the query in our dev environment. Both queries return the same data. Please confirm my findings and if you can duplicate the kind of error that I am experiencing.


#7

Hi Henry,

Thank you for sending the note.

Changes have been made in 6.5 to handle very large list of users better. We could NOT see the performance issue as you reported with our test environment. We will review the changes again and will provide you another update/build soon to get it resolved for you.

Thanks and regards,
Bruce

Message was edited by: bruceduong
could NOT see the issue


#8

All I can say: the “6.4”-statement has a cost of 16, while the “6.5”-statement has a cost of 140 (against a 10.2.0.4 64-bit enterprise editition). The FTS on SYS.OBJ$ seems to be the main problem.
6_5.png


#9

All I can say: the “6.4”-statement has a cost of 16, while the “6.5”-statement has a cost of 140 (against a 10.2.0.4 64-bit enterprise editition). The FTS on SYS.OBJ$ seems to be the main problem.
6_4.png


#10

Hi Henry,

I’m sorry for the slowness. We can reproduce and see the difference like you indicated in some of our databases.

In 6.5 we use the new query because we try to improve performance for a database which has more than 20000 Users. It used to take around 4 minutes to expand the Schemas node and we improved it to 10 seconds.

Apparently that’s not your case. In one of our testing 10g database, we can find running the new query is slower than old. Because the time is too short(0.3sec & 0.9 sec)we could not tell much difference by experiencing. You surely can notice the difference as one of them takes more than 1 min. It would help us to optimize and verify the query if we can set up an environment similar to yours. Could you provide the information below: Database version? How many Users in the database? How many objects(select count(*) from sys.all_objects)? How many schemas under All Schemas node?

I’ll keep work on this and I hope we can provide a version which nicely works for all database.

Thanks,
Vincent


#11

On the database where I obtained the explain plans from, both selects return 18 rows (and, indeed, there are 18 schema’s).
BTW: The 6.4-select took about 0.1 seconds, the 6.5-select about 5 seconds.
Database is a 10.2.0.4 64-bit enterprise edition (Windows), containing just shy of 32000 objects (sys.all_objects) and 284 users (sys.all_users).


#12

I am also noticing nav 6.5 is slow expanding table nodes and package nodes. One of my table nodes has over 600 tables. I will get some numbers for you later.


#13

We are using oracle 10G

select count() from sys.all_objects
==>
COUNT(
)

                                   69721

-------- End of Data --------
1 row(s) fetched


#14

155 Schemas
270 users


#15

Hi Henry,

Thanks for providing the information. We have been trying to optimize the new query for similar environment like yours. We could not make much progress yet because the query also needs to work for one of our testing database which has 20,000 users. We are still working on this and will also consider other options to take care of the performance for both environments. I will keep you informed if we have any update.

Thanks,
Vincent


#16

Hi Henry

Vincent has made some improvements for you in the current beta (2032), but since we could not reproduce the exact scenario here you will need to check it for us and let us know.

The new queries run much faster then the old ones when run in SQL Plus so hopefully it will resolve your issue.

Good luck!

Lidia

Message was edited by: Lidia


#17

how do i find out which version of the 6.5 beta I have? Can I try this new version with the “fixed” code?


#18

Help->about SQL Navigator …see the BUILD number 6.5.0.XXXX :slight_smile:


#19

I get an error when installing:

1638

The installation of component sql navigator for oracle beta has failed with the following error:

1638

cancel, retry, ignore

retry fails.


#20

deleted the previous beta. install worked.