Toad World® Forums

DB Explorer poor performance


#1

Hi

I use DB Navigator in preference to DB Explorer but others here prefer DB Explorer. We have had some performance issues with DB Explorer on 6.3 so I thought I would test performance on the latest beta.

The performance has not improved and is taking over 3 minutes to return the list of tables in a schema in DB Explorer. In DB Navigator it is taking under 5 seconds.

I ran the SQL monitor tool and the issue does not seem to be tied to the underlying queries.

DB Navigator query Elapsed time: 0.016
DB Explorer query Elapsed time: 0.391

So it appears the query is running fine ( a fraction slower but still under a second) but the results are taking a very long time to display.

This is only on the initial display as obviously the results are cached after that. As expected though if you do a refresh then there is the same type of delay.

There are over 1500 tables in this schema but I wouldn’t expect that is an extreme amount.

Just wondering if this is a known issue or being experienced by others.

Thanks,
Adrian.


#2

Hi Adrian,

This is the first report like this. DB Explorer was designed to be faster than DB Navigator in most instances, it uses simplified queries and better data structures to store the info. If some particular query takes too much time on a particular database, we need to look how to improve it. Which query are you referring to? We will try to improve it in a future release. In the meantime, we might find a workaround for you.

Thanks,
Roman


#3

Hi Roman

The query from the SQL monitoring tool has this output for the DB Explorer tab when I expand the tables node.


Timestamp: 12/10/2010 1:42:43 PM

select table_name from sys.all_tables
where owner=‘SIS_OWNER’ and table_name not like ‘BIN$%’ and partitioned=‘NO’ and ((iot_type <> ‘IOT_OVERFLOW’ or iot_type IS NULL) AND nested <> ‘YES’) and table_name like ‘%’

owner=[‘SIS_OWNER’]

Elapsed time: 0.015

It appears that the query only takes a fraction of a second.

However it takes just under 3 minutes for the results to display. I was going to include a screen capture but it would pretty much be the same as looking at the attached image for 3 minutes.

When I try it on another schema in the same database with 77 tables it returns in about 5 seconds. The query results from the monitor are as follows


Timestamp: 12/10/2010 1:45:56 PM

select table_name from sys.all_tables
where owner=‘WEB_OWNER’ and table_name not like ‘BIN$%’ and partitioned=‘NO’ and ((iot_type <> ‘IOT_OVERFLOW’ or iot_type IS NULL) AND nested <> ‘YES’) and table_name like ‘%’

owner=[‘WEB_OWNER’]

Elapsed time: 0.000

So I’m not sure it is actually the database or some internal workings of Navigator.

Edit.

Actually having done a bit more investigation the times in the monitor tool are not valid. I just pulled the query out and ran it manually and it took over 3 minutes. So the query is the issue after all.

Regards,
Adrian.

Message was edited by: shmung
sqlnavperf.png


#4

Adrian,

Could you post an “explain plan” of that query, and maybe also have a look in v$session_longops if anything was logged there during the execution of the query?
Thx.


#5

Hi

I have attached the explain plan. There was nothing added to v$session_longops.

Regards,
Adrian.
sqlnav_explain_plan.txt (2.9 KB)


#6

Adrian,

My explain plan looks about the same, for a schema with the number of objects being about half as much again as yours. That also explains why there’s nothing appearing in v$session_longops - but then where did the 3 minutes of execution time go?
explain_plan.txt (2.48 KB)


#7

Adrian,

Another go at the problem: could you please rerun the 3-minute query and then launch the one in the attachment?
This should give an indication of the nature of the activities on the database during the query execution.
statement_check.txt (1.06 KB)


#8

Hi

I ran the query you supplied during the execution of the other query.
Interesting it returned a few records, I can’t pretend to know exactly what that means though from a performance point of view.

I have attached the results as a csv file. Let me know if you want some other format.

Thanks for all the support with this.

Regards,
Adrian.
sqlnav_perf_sess.csv (6.36 KB)


#9

Adrian,

The idea was to run my query after yours had finished, but I could find a few prior runs of the query in the output, so it’s OK.
The ones with score 39202 and 22203 are what I was interested in - and unfortunately, there is not much I can discern.
If we take the query with score 39202, for instance, we see just 1 sort, just over 4 seconds of CPU, not too much disk or buffer activity (considering there are 1600+ rows to retrieve - plus I see approximately the same numbers over here).
But over 228 seconds elapsed… Weird.
The only thing I can think of is that the database is heavily loaded (so that retrieving the blocks from memory and/or disk takes a long time), or that the network connection between the client and the server is very slow.
Anyone any other ideas?


#10

Yeah I wasn’t sure if it was supposed to run after or during. Something in the wording in your previous post made me think during. Oh well at least you got the data.

I will have another chat to our DBA people and show them what you have said above. Perhaps they can deduce something.

Thanks again for your help.

Adrian.


#11

A follow up to this issue for anyone interested.

We have fixed our performance issue in this area by adding an SQL profile to the database. The SQL in the profile was like this.

select table_name from sys.all_tables
where owner=:owner
and table_name not like ‘BIN$%’
and partitioned=‘NO’
and ((iot_type <> ‘IOT_OVERFLOW’ or iot_type IS NULL) AND nested <> ‘YES’) and table_name like ‘%’

This could probably be made more generic to allow for filtering on the table_name but we generally don’t do that filtering.

Adding the SQL profile altered the response time from a bit over 3 minutes to now being sub second.

Adrian.


#12

I look forward to a faster navigator. I use it all the time too and it can be brutally slow. I assume we should check this in the next release?

jet


#13

update your statistics


#14

dbms_stats.gather_dictionary_stats(ESTIMATE_PERCENT=>100, CASCADE=>true)

This might help.