2
Setting Up the Database

This chapter describes how to configure the database to enable and disable various Discoverer features.

This chapter consists of the following sections:

• 2.1 Scheduled Workbooks

• 2.2 Summary Management

• 2.3 Query Prediction

2.1 Scheduled Workbooks

The workbook scheduling feature in Discoverer uses native features in the Oracle DBMS, and is therefore only available when running against an Oracle database. This feature uses the same highly scalable and reliable processing procedures within the kernel as the Summary Management feature and the setup for both features is similar. These procedures use standard packages in the DBMS called DBMS_JOB.

To enable the processing procedures for Workbook Scheduling in Discoverer:

• Confirm that DBMS_JOBS has been installed.

• Specify result set storage.

• Set the time period at which the process kicks in.

These procedures are described in the following sections.

For more information about Scheduled Workbooks, see Chapter 9, "Scheduled Workbooks".

2.1.1 Confirming that DBMS_JOBS is Installed

1. Log onto SQL*Plus as the Administrator and execute the following SQL statement:

   SQL> select * from all_objects where object_name='DBMS_JOB' and object_type = 'PACKAGE';

   If the statement returns no rows, use your DBA SQLDBA (Oracle 7.2) or SVRMGRL (Oracle 7.3 or later) to create the necessary packages.

2. From Windows 95 or NT choose Start | Run.

3. Type in one of the following"
   • SQLDBA (for Oracle7.2)

   • SVRMGRL (for Oracle 7.3 or later)

   • SVRMGR (for Oracle8i Personal Edition)

4. When in the DBA facility, type CONNECT INTERNAL

5. Execute the following SQL statement:

   SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;

   SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;

   NOTE: In some ports, the RDBMS directory is called RDBMS72 or RDBMS73.

2.1.2 Specifying Result Set Storage

When a Scheduled Workbook is run, the results are stored in database tables within the database. Result set data created as part of the workbook scheduling process may be stored in one of two areas:

• Storing the Result Set in the User's Schema

• Storing the Result Set in the centralized Repository User's Schema

2.1.2.1 Storing the Result Set in the User's Schema

Advantages: A database limit can be specified on the maximum amount of data a user can store in the database. If the result set is stored under the user's schema, then you keep control over the maximum amount of space one individual user can fill with result sets. If the user creates a scheduled workbook that fills that space, it affects only his/her own scheduled workbook.

Privileges required: The user must have the following privileges in the database:

• Create Procedure

• Create Table

• Create View

To grant these privileges, do the following:

1. Log onto SQL*Plus or SQLDBA as the Database Administrator.

2. Type the following:

   SQL> Grant CREATE PROCEDURE to <USER>;

   SQL> Grant CREATE TABLE to <USER>;

   SQL> Grant CREATE VIEW to <USER>;

   where <USER> is the userid of the user who is to be allowed to schedule workbooks.

   These privileges must be granted directly to the user and not to a database role.

2.1.2.2 Storing the Result Set in the centralized Repository User's Schema

In order to enable Workbook Scheduling using a centralized repository user's schema, the SQL script batchusr.sql located in [ORACLE_HOME]\discvr4\sql must be run in SQL*Plus or SQLDBA as a database administrator (e.g., SYSTEM).

An EUL must exist to run this script against (see Chapter 5.2, "Creating an End User Layer").

This script creates a new user which is granted the following privileges:

• Create Procedure

• Create Table

• Create View

In addition, the administrator of the End User Layer must change the user so that their Repository User property is pointing to this repository user's schema just created. This is done via the Tools | Privileges | Scheduled Workbooks tab; you select the user schema just created from the drop-down list in the field named Select the user to own the result tables created in the database and click OK or Apply.

The centralized repository user's schema may be customized by the database administrator for space management purposes and underlying data access.

The repository user created will not be able to directly schedule a workbook through Discoverer Plus.

Advantages: Each user does not need DML procedures to run scheduled workbooks.

N.B. One user can potentially run a scheduled workbook that fills the available result set space, preventing other scheduled workbooks from running until it is cleared.

2.1.3 Setting the Start Time for Workbook Processing

The Workbook processes run within the database on the server, and are controlled by parameters in the initialization file of the Oracle DBMS--the INIT<SID>.ORA file.

To limit the number of processing requests that can run simultaneously:

The parameter job_queue_processes specifies the number of concurrent processes to use to process DBMS_JOB. It controls the number of processing requests that can be handled simultaneously. The default value is zero, which means processing requests will not be created. You should set it to a minimum of 2 or more if you have any other applications that use DBMS_JOB.

You need more than one job queue process, because if one job fails for any reason, it may keep getting re-submitted and thus prevent everything else in the queue from being completed. If you want to have 10 simultaneous processing requests handled then you will need to set this to 10.

The INIT<SID>.ORA parameter job_queue_interval is the time in seconds which controls how often the job processes wake up to process pending jobs. The default is 60, which is quite frequent. What you set this to depends on how frequently you want the process to wake up and serve the requests that have been made. Oracle recommends that you update the 60 second default to at least 10 minutes (a value of 600). Note that this parameter also affects summary management.

To enable these parameters:

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

   For example, on Oracle8i Personal Edition the INIT<SID>.ORA file is held in <ORACLE_HOME>\database. Its default name is INITORCL.ORA where ORCL is the <SID> name.

2. Enter 2 lines into the file. For example:

   job_queue_processes = 2

   job_queue_interval = 600 (Note: this is equivalent to 10 minutes)

   NOTE:The Summary Management and Workbook Scheduling features both use this scheduling capability within the Oracle DBMS. The interval you specify and the number of concurrent requests affect both features.

2.2 Summary Management

The Summary Management feature in Discoverer uses native features in the Oracle DBMS, and is therefore only available when running against the Oracle database. This feature uses the same highly scalable and reliable processing procedures within the kernel as the workbook scheduling capability and the setup for both features is similar. These procedures use standard packages in the DBMS called "DBMS_JOB".

For more information about Summary Management, see Chapter 15.1, "Introduction" or Chapter 16.1, "Introduction" for details about how Discoverer can manage your summaries for you.

To enable the processing procedures for Summary Management in Discoverer you can check the following:

• Confirming that DBMS_JOBS is Installed

• Privileges

• Determining Tablespace Quotas

• Checking Object/Schema Name

• Setting the Start Time for Summary Processing

2.2.1 Confirming that DBMS_JOBS is Installed

1. Log onto SQL*Plus as the Administrator and execute the following SQL statement:

   SQL> select * from all_objects where object_name='DBMS_JOB' and object_type = 'PACKAGE';

   If the statement returns no rows, use your DBA SQLDBA (Oracle 7.2) or SVRMGRL (Oracle 7.3 or later) to create the necessary packages.

2. From Windows 95 or NT choose Start | Run.

3. Type in one of the following:
   • SQLDBA (for Oracle7.2)

   • SVRMGRL (for Oracle 7.3 or later)

   • SVRMGR (for Oracle8i Personal Edition)

4. When in the DBA facility, type CONNECT INTERNAL

5. Execute the following SQL statement:

   SQL> start <ORACLE_HOME>/rdbms/admin/dbmsjob.sql;

   SQL> start <ORACLE_HOME>/rdbms/admin/prvtjob.plb;

   NOTE: In some ports, the RDBMS directory is called RDBMS72 or RDBMS73.

2.2.2 Privileges

The user ID that you intend to use to create the Summary Folders must have the following database privileges:

• CREATE TABLE

• CREATE VIEW

• CREATE PROCEDURE

• SELECT ON V_$PARAMETER

• ANALYZE ANY (ASM)

• CREATE/DROP/ALTER ANY MATERIALIZED VIEW (8.1.6+)

• GLOBAL QUERY REWRITE (8.1.6+)

Use SQL*DBA (Oracle 7.2) or SVRMGRL (Oracle 7.3 or later) 'CONNECT INTERNAL' and execute the following SQL statements: (Note: SVRMGR on Oracle8i Personal Edition, SVRMGR23 or SVRMGR30 on WindowsNT Server.)

SQL> Grant CREATE TABLE to <USER>; 
SQL> Grant CREATE VIEW to <USER>; 
SQL> Grant CREATE PROCEDURE to <USER>; 
SQL> Grant SELECT ON V_$PARAMETER to <USER>; 
SQL> Grant CREATE ANY MATERIALIZED VIEW to <USER>; 
SQL> Grant DROP ANY MATERIALIZED VIEW to <USER>; 
SQL> Grant ALTER ANY MATERIALIZED VIEW to <USER>; 
SQL> Grant GLOBAL QUERY REWRITE to <USER> WITH ADMIN OPTION; 
SQL> Grant ANALYZE ANY to <USER>; 

where <USER> is the userid of the person using the Administration Edition.

Script to enable user to create summaries against an 8.1.6+ database

A script granting privileges required to create summaries against an 8.1.6+ database can be found in [ORACLE_HOME]\DISCVR4\SQL\eulasm.sql.

2.2.3 Determining Tablespace Quotas

A user must have enough quota in their default tablespace to create summary tables.

Use SQL*DBA (Oracle 7.2) or SVRMGRL (Oracle 7.3 or later) connect internal and execute the following SQL statement: (Note: SVRMGR on Oracle8i Personal Edition, SVRMGR23 or SVRMGR30 on WindowsNT Server.).

SQL> select * from dba_ts_quotas where username = <user>;  

where <user> is the userid of the person using the Administration Edition.

Reset the Tablespace Quotas by issuing the following SQL statement:

SQL> alter user <user> quota <n> on <tablespace>;

where <user> is the userid of the person using the Administration Edition

where <n> is the quota in K(ilobytes) or M(egabytes) or Unlimited

where <tablespace> is the default tablespace name e.g. USERS

2.2.4 Checking Object/Schema Name

A user should not have an object in their schema with the same name as their username. To check that this is not the case, login to SQL*Plus as the user and issue the following command:

SQL> select object_name from user_objects where object_name = <user>;

where <user> is the same as the login used for SQL*Plus.

2.2.5 Setting the Start Time for Summary Processing

The Summary Management processes run within the database on the server, and are controlled by parameters in the initialization file of the Oracle DBMS--the INIT<SID>.ORA file.

2.2.5.1 Limiting the Number of Processing Requests that can Run Simultaneously

The parameter 'job_queue_processes' specifies the number of concurrent processes to use to process DBMS_JOB. I.E It controls the number of processing requests that can be handled simultaneously. The default value is zero which means processing requests will NOT be created. You should set it to a minimum of 2, or more if you have any other applications that use DBMS_JOB.

You need more than one job queue process, because if one job fails for any reason, it may keep getting re-submitted and thus prevent everything else in the queue from being completed. If you wish to have 10 simultaneous processing requests handled then you will need to set this to 10.

The INIT<SID>.ORA parameter 'job_queue_interval' is the time in seconds which controls how often the job processes wake up to process pending jobs. The default is 60, which is quite frequent. What you set this to depends on how frequently you want the process to wake up and serve the requests that have been made. We recommend that you update the 60 second default to at least 10 minutes (a value of 600). Note that this parameter also affects summary management.

To enable these parameters:

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

   For example, on Personal Oracle7 the INIT<SID>.ORA file is held in <ORACLE_HOME>\database. Its default name is INITORCL.ORA where ORCL is the <SID> name.

2. Enter 2 lines into the file. For example:

   job_queue_processes = 2

   job_queue_interval = 600 (Note: this is equivalent to 10 minutes)

   NOTE: The Summary Management and Workbook Scheduling features both use this scheduling capability within the Oracle DBMS. The interval you specify and the number of concurrent requests affect both features.

2.3 Query Prediction

2.3.1 Enabling Query Prediction

If Query Prediction is not available, a prompt displays during each query execution in Discoverer Plus advising that the "query estimate is not available." In addition, the message Query performance prediction not available because <reason> displays. You can find details about the reason in the Database Information dialog, by choosing Help | Database Information.

Query Prediction may be unavailable due to any of the following:

• Connection to a database that does not support query prediction,e.g. Oracle 7.1.x

• Certain views (noted in the 'Database Information' Dialog) are not accessible:
  • Sys.v$session not accessible

  • Sys.v$sesstat not accessible

  • Sys.v$parameter not accessible

• The timed_statistics parameter is set to FALSE in the INIT<SID>.ORA database parameter file. FALSE is the default value of this parameter.

• Data tables have not been analyzed.

• The init.ora parameter OPTIMIZER MODE is set to RULE, instead of CHOOSE.

To enable Query Prediction, perform the following steps based on the information displayed in the Database Information dialog.

2.3.1.1 What to do if Certain Views (SYS.V$xxx) are not Available

1. From Windows 95 or NT choose Start | Run.

2. Type in one of the following:
   • SQLDBA (for Oracle7.2)

   • SVRMGRL (for Oracle 7.3 or later)

   • SVRMGR (for Oracle8i Personal Edition)

3. When in the DBA facility, type CONNECT INTERNAL

4. Grant select access on these objects and issue the following:

   SQLDBA> grant select on v_$session to public;

   SQLDBA> grant select on v_$sesstat to public;

   SQLDBA> grant select on v_$parameter to public;

2.3.1.2 To Check the timed_statistics Parameter

To verify whether the Server has timed_statistics already defined:

1. Run SQL*Plus.

2. Login as SYSTEM/<password>

   where <password> is your system specific password.

3. Execute the following query:

   SQL> select value

   from v$parameter

   where name = 'timed_statistics';

   This should return the value TRUE. If it returns the value FALSE you need to manually edit the INIT<SID>.ORA file.

4. Locate INIT<SID>.ORA.

   For example, on Oracle8i Personal Edition the file INITORCL.ORA is located in <ORACLE_HOME>\database. The <SID> in this case is ORCL.

5. Edit the file to include the following line:

   timed_statistics = TRUE

6. Shut down and restart the server to effect this change.

2.3.1.3 To Analyze Tables

1. Run SQL*Plus.

2. Login as the data table owner.

3. Execute the following query:
   • For Oracle 7.2 databases:

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

   • For Oracle 7.3 (and later) databases:

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

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

2.3.1.4 To Check the optimizer_mode Parameter

To verify whether the Server has `optimizer_mode' already defined:

1. Run SQL*Plus.

2. Login as SYSTEM/<password>

   where <password> is your system specific password.

3. Execute the following query:

   SQL> select value

   from v$parameter

   where name = 'optimizer_mode';

This should return the value CHOOSE. This means that the system will use the Cost optimizer if the tables are analyzed, and the Rule optimizer if they are not. Optimizer_mode may also have the value FIRSTROWS or ALLROWS, both of which force the use of Cost optimizer, even if the tables are not analyzed.

If this query returns the value RULE you need to manually edit the INIT<SID>.ORA file. To do this:

1. Locate INIT<SID>.ORA.

   For example, on Oracle8i Personal Edition the file INITORCL.ORA is located in <ORACLE_HOME>\database. The <SID> in this case is ORCL.

2. Edit the file to include the following line:

   optimizer_mode = CHOOSE

3. Shut down and restart the server to effect this change.

2.3.2 Reducing Long Query Prediction Times

Discoverer uses a 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.

When query prediction is used in a large schema environment, such as Oracle Applications, the database server can take a long time to parse a statement using the cost-based optimizer. This can mean that Query Prediction can take several minutes. There are three ways around this problem:

• Turn off Query Prediction.

  This can be done by specifying the following registry key:

  HKEY_CURRENT_USER\Software\Oracle\Discoverer 4\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.

• 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 4\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.

• Tune the way the cost-based optimizer uses indexes, for example by adjusting database parameters such as:
  • optimizer_index_cost_adj

  • optimizer_index_caching

  For further information refer to Oracle 8i Designing and Tuning for Performance Release 2 (8.1.6) Part Number A76992-01.