Oracle - Database Explorer is Slow

The Database Explorer runs a number of queries on the Oracle system views. If you have a large database these queries can be quite slow. This is because the standard Oracle system views (all_tables etc) are complicated multi-table joins, which are slow on Oracle systems with a large number of objects.

This is particularly true with the Functions, Procedures and Packages display, which uses all_objects (which is very slow).

There are a number of solutions to this problem:

• you can review the system queries that AQT uses (these are contained in file oracle.cfg) to use customized queries for your site.
• if your users have access to the base Oracle system tables you can use system queries that use those tables, rather than the system views. These are considerably faster. This is discussed in the next section.

Using Queries on the Base System Tables

Queries that use the Base System Tables are given in the Oracle config file 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:

• the schema-lists (as shown in the Object Tree on the left)
• the object-lists (in the middle grid), which are displayed when you click on a Schema.

To implement these queries:

• ensure that you have access to the following system tables: sys.user$, sys.obj$, sys.tab$, sys.ts$
• edit oracle.cfg and de-comment the faster queries (eg. remove the ' from the start of the line). These are located near the bottom of the file.
• restart AQT

Note that these are "simpler" queries and may give slightly different results to the standard queries:

• you will see all objects irrespective of whether you have access to them
• there are a few other unusual cases in which the objects shown are slightly different 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.

Queries Still Slow?

If the queries that populate the schema-lists (in the Object Tree) are still very slow, you can speed them as follows.

• Change queries 1,2,3,4,5,6,9,18 to:

  Select USERNAME from SYS.ALL_USERS ORDER BY 1

  This will show, in the schema-list, all the Oracle userids. This is a fast, but not very good, way of displaying the schemas. It will show userids/schemas even if there are no objects for them (so when you click on a schema name you may not see any objects in the middle grid).

• Some people want to hard-code a list of schemas that people are to see. This can be done by changing queries 1,2,3,4,5,6,9,18 to (for instance):

  Select 'SCHEMA1' from DUAL union select 'SCHEMA2' from DUAL union select 'SCHEMA3' from DUAL