Resolving The Problem

In some situations analyzing the RequisitePro tables and indexes in an Oracle database will improve query performance. Analyzing tables and indexes will generate current statistics for the RequisitePro objects involved in SQL statements. Statistics are used by the Oracle Optimizer to determine the most efficient way to execute a query. The statistics generated by the analyze command are only used if the database parameter optimizer_mode is set to a value of Choose, First_Rows or All_Rows.

Note: If the database optimizer_mode parameter is set to "Rule" the following steps will have no impact on database performance.

The following instructions should be completed by the Database Administrator.

The steps below explain how to generate two scripts that can be used to analyze the tables and indexes respectively.

Script to analyze RequisitePro tables:

1. Connect to SQL PLUS as the RequisitePro database user.
2. Issue the following command to set the appropriate page size
   
   
Set pagesize 100

   
3. Turn spooling on which will send write output from SQL PLUS to a file.
   
Spool <local path on the machine>\Table_analyze.sql

   
4. Issue the following command and press enter.
   
select 'Analyze Table '||table_name||' compute statistics;' from user_tables;

   
5. Turn spooling off.
   
Spool off

   
6. Browse to the location of the Table_analyze.sql file specified in step 3. Open the file with a text editor such as Notepad and remove the lines at the top and bottom of the file that do NOT begin with the text:
   
Analyze table RQ…

Save the file.

   
7. Return to SQL Plus and run the script as follows:
   
@ <local_path>\Table_analyze.sql

   
   For a large database it may take a few minutes for the script to execute. Once the script completes the RequisitePro tables have been analyzed.
   
   

1. Connect to SQL PLUS as the RequisitePro database user.
2. Issue the following command to set the appropriate page size
   
Set pagesize 100

   
3. Turn spooling on which will send write output from SQL PLUS to a file.
   
Spool <local path on the machine>\Index_analyze.sql

   
4. Issue the following command and press enter.
   
select 'Analyze Index '||index_name||' compute statistics;' from user_indexes

   
5. Turn spooling off.
   
Spool off

   
6. Browse to the location of the Index_analyze.sql file specified in step 3. Open the file with a text editor such as Notepad and remove the lines at the top and bottom of the file that do NOT begin with the text:
   
Analyze Index RQ…

Save the file.

   
7. Return to SQL Plus and run the script as follows:
   
@ <local_path>\Index_analyze.sql

   
   For a large database it may take a few minutes for the script to execute. Once the script completes the RequisitePro indexes have been analyzed.