Schema Browser slow in Packages

The schema browser has become really slow when loading the list of Packages. I’m not sure when it started exactly, but it has been happening the last few beta releases at least.

I did try spooling the sql in the beta and the 12.1 version that I have, and the sql appears to be the same.

When’s the last time you collected data dictionary statistics? See numerous blogs on toad world about this. Since it was OK and is now slower - sounds like a very likely candidate for having your DBA learn about and implement collecting DD statistics. Most mistakenly think this is all automated and not something DBA has to manage. Oracle docs (ref’ed in blogs) say otherwise :slight_smile:

I will check, but I don’t think that is the issue. If I run Toad 12.1 right now, the packages load much faster in the schema browser than in the beta. If it was a problem with statistics, I would expect it to be slow in 12.1 also.

The packages you’d need to run to verify this take about two minutes - so testing is easy and painless. I could well be wrong - but this is sanity check #1 before we have a dev spend time tuning what may (and 90% of time is) a database issue. If you can check, then it is far easier for us to then investigate - as we know then it has to be toad …

have DBA run these two commands and retest:

exec dbms_stats.gather_dictionary_stats;

exec dbms_stats.gather_fixed_objects_stats;

if it’s still slow - then we know it’s toad. Otherwise we’re guessing and that;'s hard to debug

Is it the loading of packages that is slow, or the responsiveness of Toad after lots of packages are loaded? How many packages are in your schema?

Do you think the problem could be in the database? There should be status messages that give you an idea when the query executes, and when it is fetching. If the execution time is slow, the problem is in the database (out of date statistics?). If the fetching is slow, it could be the database, the network, or Toad.

I’ve got a local database with 50,000 packages all in one schema. For me, Toad 12.1 loads those packages into the schema browser in less than 10 about 5 seconds.

oops, you said the beta is slow…I’ll retest

Just to confirm, I have a similar database (about 40K items) on remote database and I too get return in about 5-10 seconds at very worst - sometimes a little quicker depending on DB activity and dictionary/buffer cache effectiveness …

Yes, I definitely see the beta loading more slowly, and it does appear to be in Toad.

I’ll investigate.

Spoke to John - not sure why I am not getting it, but he is and is on it - so I’ll drop off as you’re clearly in good hands and he can reproduce it.

This was caused by a recent update to a 3rd party component (the tree that holds the packages). It will be fixed in the next beta.