20 Predicting query performance

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.

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:

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:

• Analyze the tables that users will query using the ANALYZE TABLE command. Use the Business Areas and Folders worksheet of the EUL Data Definition workbook to display when the folders in the EUL were analyzed. For information about how to analyze data tables, see "How to analyze data tables".

• Grant your users access to the system view V_$SQL (for more information, see "How to use query prediction with secure views")

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

• If you expect the table contents to change significantly over time, analyze the data tables at regular intervals.

• If your end users find that query prediction itself takes a long time (i.e. > 10 seconds) this suggests that the tables have not been analyzed. For example, if it takes 25 seconds or so for Discoverer to display the query prediction but the query runs in less than 2 seconds.

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:

• You could turn off query prediction using the appropriate Discoverer registry setting, as follows:

  • For Discoverer Desktop

    In the Windows registry, set the DWORD value of the HKEY_CURRENT_USER\Software\Oracle\Discoverer 10\Database\QPPEnable registry key to 0 (zero).

    If you subsequently decide to re-enable query prediction, either remove the registry key or set it to 1.

    For more information about Discoverer registry settings stored in the Windows registry, see "About Discoverer Administrator and Discoverer Desktop registry settings".

  • For Discoverer Plus and Discoverer Viewer

    Change the value of the QPPEnable registry setting in the in the pref.txt file. For the changes to take effect, you must 'apply' the preferences. The new value is then written to the reg_key.dc file (for more information about how to set preferences, see the Oracle Business Intelligence Discoverer Configuration Guide). If QPPEnable is set to 1, query prediction is turned on. To turn query prediction off, set QPPEnable to 0.

    For more information about Discoverer registry settings stored in the .reg_key.dc file, see "About Discoverer Plus and Discoverer Viewer registry settings".

• You could make sure that query prediction does not enforce the use of the Cost-Based Optimizer using the appropriate Discoverer registry setting, as follows:

  • For Discoverer Desktop

    In the Windows registry, set the DWORD value of the HKEY_CURRENT_USER\Software\Oracle\Discoverer 10\Database\QPPCBOEnforced registry key to 0 (zero).

    When this registry key is set to zero, use of the Cost-based Optimizer (CBO) is not enforced and will follow the normal rules of the database server.

    If you subsequently decide that you want query prediction to force the use of the Cost-Based Optimizer, either remove the registry key or set it to 1.

    For more information about Discoverer registry settings stored in the Windows registry, see "About Discoverer Administrator and Discoverer Desktop registry settings".

  • For Discoverer Plus and Discoverer Viewer

    Change the value of the QPPCBOEnforced registry setting in the in the pref.txt file. For the changes to take effect, you must 'apply' the preferences. The new value is then written to the reg_key.dc file (for more information about how to set preferences, see the Oracle Business Intelligence Discoverer Configuration Guide). If QPPCBOEnforced is set to 1, query prediction will enforce the use of the cost-based optimizer. To specify that query prediction is to use the default optimizer, set QPPCBOEnforced to 0.

    For more information about Discoverer registry settings stored in the .reg_key.dc file, see "About Discoverer Plus and Discoverer Viewer registry settings".

• You could tune the way the Cost-Based Optimizer uses indexes. For example, you could adjust the following database parameters:

  • optimizer_index_cost_adj

  • optimizer_index_caching

  For more information about database parameters, see the Oracle10g documentation.

• You could delete the existing statistics out of the statistics table.

  For more information, see "How to delete old query prediction statistics".

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

• You can also grant your users access to the system view V_$SQL by running the following script in SQL*Plus:

  <ORACLE_HOME>\discoverer\util\eulsuqpp.sql

  You must know the SYSTEM password to use this script.

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.