The Database Explorer displays information on your database by running queries against the Oracle dictionary views. AQT has a choice of two sets of dictionary views:
The problem which AQT faces is that the ALL views can be very slow on systems with a large number of objects, particularly for users who have access to a lot of objects. The alternative is to use the DBA views, however many users will not have access to them.
How AQT deals with this is determined by Options > Database Explorer > For Oracle use DBA dictionary views?
In most cases the Auto option would be the best option for most users. It also allows AQT to use the appropriate set of tables depending on the database and userid you are using.
You can see which set of tables AQT is using with Help > Database Details > Oracle Use DBA Views?
Many of the displays in the Database Explorer are a lot faster if you use caching.
See Options > Database Explorer > Disk Cache. It is recommended that both Use Disk Cache for Schemas and Use Disk Cache for Objects is selected.
When this option is selected, once the information is retrieved it is saved in a cache file on disk. When you subsequently display this information, the data is read from the cache file rather than the database. If necessary you can refresh the cache with F6 (for the list of schemas) or F5 (for the list of objects).
Queries that use the Base System Tables are given in oracle.cfg file. They have been commented out so, by default, are not used.
Except for one query (Synonyms on Table) these queries only affect:
To implement these queries:
Note that these are "simpler" queries and may give slightly different results to the standard queries:
If there are any problems with these queries, you can revert back to the standard queries by commenting out the fast queries.
If the queries that populate the schema-lists (in the Object Tree) are still very slow, you can speed them as follows.
Select USERNAME from SYS.ALL_USERS ORDER BY 1
Select 'SCHEMA1' from DUAL union select 'SCHEMA2' from DUAL union select 'SCHEMA3' from DUAL