Skip Headers
Oracle® Business Intelligence Discoverer Administration Guide
10g Release 2 (10.1.2.1)
B13916-04
  Go To Documentation Library
Library
Go To Product List
Product
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

20 Predicting query performance

This chapter explains how to use query prediction in Discoverer Administrator and contains the following topics:

About predicting query performance

Discoverer includes functionality to predict the time required to retrieve the information in a Discoverer query. The query prediction appears before the query begins, enabling Discoverer users to decide whether or not to run the query. This is a powerful facility that enables Discoverer users to control how long they wait for large reports.

Query Prediction uses the Cost-Based Optimizer (CBO) in the Oracle RDBMS. Therefore query prediction is not available when running against databases running with the Rule-Based Optimizer (RBO).

How does query prediction work in Discoverer Plus?

Discoverer end users can specify that they be informed when a query is predicted to take longer than a defined time. A dialog displays the query prediction details and the option to cancel the query.

Description of qp.gif follows
Description of the illustration qp.gif

If the user chooses to cancel the query they can schedule the workbook for Discoverer to run the query later, for example, overnight so that the user can open the worksheet the next morning (for more information about scheduling workbooks in Discoverer Plus, see the Oracle Business Intelligence Discoverer Plus User's Guide). For more information about enabling end users to schedule workbooks using Discoverer Administrator, see Chapter 8, "Scheduling workbooks".

Why might query prediction not be available?

Discoverer end users might find that query prediction is not available when running a worksheet. To see possible reasons (in Discoverer Administrator), choose Help | Database Information to display the "Database Information dialog". Query prediction might not be available for any of the following reasons:

Reason Solution
Connection to a database that does not support query prediction (e.g. Oracle 7.1.x) Upgrade the database.
Views required for query prediction are not available. Refer to "How to make the necessary database views available for query prediction"
The timed-statistics parameter in init<sid>.ora is set to FALSE (the default value). Refer to "How to verify and change the timed_statistics parameter for query prediction"
Data tables have not been analyzed. Refer to "How to analyze data tables"
The optimizer mode parameter in init<sid>.ora is set to RULE instead of CHOOSE Refer to "How to verify and change the optimizer_mode parameter for query prediction"

About improving the speed and accuracy of query prediction

When users have confidence in the speed and accuracy of query prediction, they will be more likely to schedule long-running queries to run later. With accurate query prediction, the load placed on the server is typically reduced and query performance is improved for all users.

To implement query prediction effectively:

How to make the necessary database views available for query prediction

Various database views must have the SELECT privilege granted to the public user before query prediction is enabled in Discoverer. For more information, see the Oracle10g documentation.

To make the necessary views available for query prediction, for Oracle9i (and later) databases:

  1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;
    
    

    Where sys is the SYS user and sys_pw is the SYS user password.

  2. Grant SELECT access on the views necessary for query prediction by typing the following at the command prompt:

    SQL> grant select on v_$session to public;
    SQL> grant select on v_$sesstat to public; 
    SQL> grant select on v_$parameter to public; 
    
    

    Note: To grant SELECT on v_$parameter you must log in as the SYS user. If you are unable to login as the SYS user or are unsure about the SYS user name and password, see your database administrator.

How to verify and change the timed_statistics parameter for query prediction

The timed_statistics parameter found in the database view v_$parameter must be set to TRUE to enable query prediction in Discoverer.

To verify that timed_statistics is set to TRUE:

  1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT dba_user/dba_pw@database;
    
    

    Where dba_user is the database administrator and dba_pw is the database administrator password.

  2. Type the following at the command prompt:

    SQL> select value from v$parameter where name = 'timed_statistics';
    
    

    If the query returns the value TRUE, timed_statistics is set correctly for query prediction. If the query returns the value FALSE, query prediction will not be available unless you change the value of the timed_statistics parameter in the init<sid>.ora file.

    Note: v$parameter is a synonym (i.e. a pointer) to the view v_$parameter.

To edit the init<sid>.ora file:

  1. Locate the INIT<SID>.ORA file.

    The INIT<SID>.ORA file is held in <ORACLE_HOME>\database. The default name of the file is INITORCL.ORA, where ORCL is the <SID> name.

  2. Edit the file to include the following line:

    timed_statistics = TRUE
    
    
  3. For the change to take effect, shut down and restart the database.

How to analyze data tables

Discoverer uses the results of data table analysis for query prediction. Data table analysis generates information about the database tables (e.g. the size of a table). For more information, see the Oracle10g database documentation.

To analyze data tables:

  1. Start SQL*Plus (if it is not already running) and connect as the owner of the data tables you want to analyze.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> connect tab_owner/tab_pw@database;
    
    

    Where tab_owner is the username and tab_pw is the password of the data table owner.

  2. Type the following query:

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

Notes

How to verify and change the optimizer_mode parameter for query prediction

To verify that the optimizer_mode parameter is set to CHOOSE:

  1. Start SQL*Plus (if it is not already running) and connect as the database administrator.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT dba_user/dba_pw@database;
    
    

    Where dba_user is the database administrator and dba_pw is the database administrator password.

  2. Type the following at the command prompt:

    SQL> select value from v$parameter where name = 'optimizer_mode'; 
    
    

Note: v$parameter is a synonym (i.e. a pointer) to the view v_$parameter.

If the query returns the value CHOOSE, optimizer_mode is set correctly for query prediction. The system will use the Cost Based Optimizer if the tables have been analyzed, and the Rule Based Optimizer if the tables have not.

If the query returns the values FIRSTROWS or ALLROWS, optimizer_mode is also set correctly for query prediction

Both FIRSTROWS and ALLROWS force the use of the Cost Based Optimizer, even if the tables have not been analyzed.

If the query returns the value RULE, query prediction will not be available unless you change the value of the optimizer_mode parameter in the init<sid>.ora file.

To edit the init<sid>.ora file:

  1. Locate INIT<SID>.ORA.

    The INIT<SID>.ORA file is held in <ORACLE_HOME>\database. The default name of the file is INITORCL.ORA, where ORCL is the <SID> name.

  2. Edit the file to include the following line:

    optimizer_mode = CHOOSE
    
    
  3. For the change to take effect, shut down and restart the database.

How to reduce long query prediction times

You can reduce the time it takes to complete the query prediction process.

Discoverer uses the Cost-Based Optimizer within the query prediction process. Note that the Cost-Based Optimizer only parses the query statements, and that query execution is usually governed by the server's default optimizer mode.

In a large schema environment (e.g. Oracle Applications), the database can take a long time to parse a statement using the Cost-Based Optimizer. The query prediction process might therefore take several minutes to complete.

If users are having to wait a long time before the query prediction process is complete, consider the following solutions:

How to use query prediction with secure views

Discoverer's query prediction feature uses the EXPLAIN PLAN statement to analyze queries. However, EXPLAIN PLAN cannot analyze queries against secure views, with the result that query prediction is not normally able to work in these environments. To work around this limitation, grant your users access to the system view V_$SQL.

To grant your users access to the system view V_$SQL, for Oracle9i (and later) databases:

  1. Start SQL*Plus (if it is not already running) and connect as the SYS user, or a user to which the sysdba privilege has been granted.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> CONNECT sys/sys_pw@database AS SYSDBA;
    
    

    Where sys is the SYS user and sys_pw is the SYS user password.

  2. Grant SELECT access on the views necessary for query prediction by typing the following at the command prompt:

    SQL> grant select on v_$sql to public;
    
    

Note: To grant SELECT on v_$sql you must log in as the SYS user. If you are unable to login as the SYS user or are unsure about the SYS user name and password, see your database administrator.

Notes

How to delete old query prediction statistics

Query prediction statistics can become obsolete for many reasons. You can delete all query prediction statistics that were created before a specified date.

To delete old query prediction statistics from the database:

  1. Start SQL*Plus (if it is not already running) and connect as the EUL owner.

    For example, if SQL*Plus is already running, you might type the following at the command prompt:

    SQL> connect jchan/tiger@database;
    
    

    Where jchan is the EUL owner's username and tiger is the EUL owner's password.

  2. Run the SQL file eulstdel.sql.

    For example you might type the following at the command prompt:

    SQL> start <ORACLE_ HOME>\discoverer\util\eulstdel.sql
    
    

    Where <ORACLE_HOME> is where Discoverer Administrator is installed.

    A summary of the query statistics stored in the database is displayed. You are given the option to delete query statistics older than a specified number of days.

  3. (optional) Enter the number of days after which query statistics will be deleted (or leave it blank if you do not want to delete any statistics).

    If you do not specify a number of days, no query statistics are deleted.