C
Query Prediction

This appendix consists of the following sections:

C.1 What is Query Prediction?

Query Prediction provides an estimate of the time required to retrieve the information in a query. The Query Prediction appears before the query begins, so you can decide whether or not to run the query. Query Prediction uses the Cost-Based Optimizer (CBO) in Oracle RDBMS Release 7.2 or later.

C.2 Enabling and Configuring Query Prediction

Section 2.3, "Query Prediction," contains information on enabling and optimizing Query Prediction.
Section E.2, "Registry Settings," includes Registry Settings that you can use to configure various Query Prediction options.

C.3 Improving the Accuracy of Query Prediction

Improving the Query Prediction accuracy enables users to make better decisions on whether to run their queries immediately or schedule them for later. Quite often, this results in reduced server load, and therefore improved query performance.

To implement Query Performance Prediction effectively, you should analyze the tables you intend to query using the ANALYZE TABLE command.

e the Business Areas and Folders Worksheet of the EUL Data Definition Workbook to display when the Folders in the EUL were last analyzed (see Section B.3, "EUL Data Definition.") for more information.

Query Prediction uses the cost-based optimizer in Oracle RDBMS Release 7.2 or later. Creating histogram statistics by performing column level analysis will lead to more accurate query predictions, reducing the server load and improving query performance.

For Oracle 7.2 databases issue the following SQL statement using SQL*Plus (logged in as the data table owner):

SQL>  analyze table <username.tablename> compute statistics;

For Oracle databases (v7.3 upwards), issue the following SQL statement using SQL*Plus (logged in as the data table owner):

SQL> analyze table <username.tablename> compute statistics for all columns;
SQL>  analyze table <username.tablename> compute statistics;

NOTE: If you expect the table contents to change significantly over time, your analysis should be reapplied at regular intervals.

C.4 Running queries against Secure Views and making Query Prediction faster

Query Prediction uses the EXPLAIN PLAN statement in order to analyze queries which Discoverer Plus runs. Unfortunately, EXPLAIN PLAN does not work against secure views, and therefore, Query Prediction is not normally able to work in these environments.

With Discoverer, it is possible to workaround this by granting your users access to the system view V$SQL. In fact, it is generally recommended to do this as it usually has the added benefit of making Query Prediction faster.

To do this log into SQL*DBA (Oracle7 7.2) or SVRMGRL (Oracle7 7.3) on the database (UNIX) server as the user INTERNAL.

Note: SVRMGR on Personal Oracle7 7.3, SVRMGR23 or SVRMGR30 on WindowsNT Server.

Grant the select by issuing the following command:

NB: 'v$xxxxxx' is a public synonym to a view called 'v_$xxxxxx', so the grant statements would read as follows:

SQL> grant select on v_$sql to public;

NB. You can do this by running the script {oracle_home}\discvr4\sql\eulsuqpp.sql in sqlplus. You must know the SYS password to use this script.

C.5 Deleting Old Query Prediction Statistics

This section describes how to delete old Query Prediction statistics from the database.

Either use Server Manager or SQL*Plus to connect to the database as the EUL owner.

For example:
```
SQL> connect <eul owner>/<eul owner password> 
```
Run eulstdel.sql.

For example:
```
SQL> Start d:\{ORACLE HOME}\discvr4\sql\eulstdel.sql
```
This displays a summary of the query statistics stored in the database. And offers to delete query statistics older than a specified number of days.
Enter the number of days (or leave it blank if you don't want to delete any statistics).

C Query Prediction