DISCOVERER 3.1x TAKING A LONG TIME?

OPTIMIZING CLIENT REGISTRY SETTINGS WILL HELP

 

Russ Proudman www.proudman.homestead.com

 

Discoverer 3.1 forces the use of the cost-based optimizer within its query prediction process (it only parses the statements - the execution of queries is usually governed by the server's default optimizer mode). Unfortunately, the CBO can be inefficient on very large schema's, such as Oracle Applications or the SmartStore system.

When query prediction is used in a large schema environment, the database server can take a long time to parse a statement using the cost-based optimizer. This is a known server behavior and has been documented as part of Oracle bug number 564060.

Also, by default, Discoverer checks that the objects that the user has access to (tables / views, etc.) indeed exist in the database and therefore goes through a checking process making sure all objects are there. Turning this off means that if a user is not allowed to access these objects an error message will occur. But leaving this on means a further overhead. Therefore in a proper Discoverer environment, turning this off will get rid of needless time when a query is invoked by a user.

------------------------------------------------------------------------------------------------------------------------------------------------

 

3 REGISTRY SETTINGS TO CHANGE

 

Note that for each of the three suggestions below, if the entry does not exist in your registry, then create it and set the value suggested.

 

 

1. Database\QPPEnable

type - DWORD, default 1

 

To Do: Turn off query prediction.

This can be done by specifying the following registry key:

HKEY_CURRENT_USER\Software\Oracle\Discoverer 3.1\Database\QPPEnable

 

It should be set to a DWORD value of 0 (zero). To re-enable query prediction at some later point in time,

either remove the registry key or set it to 1.

------------------------------------------------------------------------------------------------------------------------------------------------

2. Database\QPPCBOEnforced

type - DWORD, default 1

 

To Do: Stop query prediction forcing the use of the cost-based optimizer.

This can be done by specifying the following registry key:

HKEY_CURRENT_USER\Software\Oracle\Discoverer 3.1\Database\QPPCBOEnforced

It should be set to a DWORD value of 0 (zero) which means use of the Cost-based Optimizer (CBO)

is not enforced. The CBO will follow the normal rules of the database server.

------------------------------------------------------------------------------------------------------------------------------------------------

3. Database\ObjectsAlwaysAccessible

type - DWORD, default 0

 

To Do: Stop validating that the tables/views folders refer to exist and that the user has access to them.

This can be done by specifying the following registry key:

HKEY_CURRENT_USER\Software\Oracle\Discoverer 3.1\Database\ObjectsAlwaysAccessible

 

It should be set to a DWORD value > 0 (ie: 1) which means validation will not take place.

------------------------------------------------------------------------------------------------------------------------------------------------