Tags
The purpose of this document is to provide Baan administrators with a procedure to follow when it is necessary to tune a Baan-Oracle environment. These steps cover basic Baan-Oracle tuning steps that can be performed quickly and easily by Baan administrators or by Oracle DBAs.
Because each installation differs when it comes to things like volume of data, frequency of updates, physical storage, number of processors, etc., it is impossible to provide tuning advice that is more than general in nature in a quick guide. Instead, this guide focuses on the basic parameters that are common to all Baan-Oracle installations. Most of what is covered here applies equally to UNIX and Windows systems.
Tuning beyond what is covered here requires knowledge specific to the installation being tuned and experience at tuning Baan-Oracle environments. If this guide does not help you restore performance and you believe further tuning is required, please contact the Baan Support Center at 1-800-925-2226.
The procedure outlined in this guide starts with the basic parameters that govern the behavior of the Baan Oracle database driver and ends with examining at the parameters of the database itself. This approach will allow you to optimize these parameters so they are set at values that have proven to give the best performance in the majority of Baan-Oracle environments.
The procedure we suggest is:
- Investigate the Baan driver parameters in the db_resource file.
- Make sure that index and table optimization is set correctly.
- Make sure the Oracle table and index statistics are up to date.
- Make sure that the Oracle initialization parameters are set to optimal values.
If the steps you perform in this approach do not restore performance to the point where you believe it needs to be, then you will be able to continue with Baan support with these settings already optimized.
1. The Baan Driver Parameters
To investigate the Baan driver parameters, you will need to look at the db_resource file in the $BSE/lib/defaults directory. For most systems, the following parameters should be set with these values:
dbsinit:021
ora_init:0101000
rds_full:2
ora_max_array_fetch:2
ora_max_array_insert:2
lock_retry:0
ora_timeout:{120, 60, 60, 60, 60}
The settings given above assume: 1) a Level 2 database and 2) that you have disabled the Oracle lock retry mechanism (lock_retry=0). These values have proven to give the best performance in most instances. Your ora_timeout settings may be different and if so, should be left as is unless locking errors occur frequently.
If your database is a Level 1 database, or if you are using the lock_retry mechanism (mandatory on a Windows NT server), then ora_init should be set to 0111000 and you should not set ora_timeout.
If a parameter mentioned here is not present in your db_resource file, then the Baan Oracle driver is using the default value for the parameter. If you would like to know the default value for each of the above parameters, or if you would like more information about the parameters themselves, please refer to the Baan Oracle Database Driver Technical Resource Manual specific to the version of Baan you are using.
2. Index and Table Optimization
If the performance you are getting from your database is not ideal with the settings given above, then you should next look at the table and index settings in the ora_storage or ora_storage_param file located in the BSE/lib/ora directory. Below are example entries, one for a table and one for an index.
*:*:T:group:0214:5:TABLESPACE BAAN_DATA PCTFREE 5 INITRANS 3
*:*:I:group:0214:5:TABLESPACE BAAN_INDX PCTFREE 5 INITRANS 3
The optimization value is the number that follows the word “group” in the examples. That parameter is important because it governs how certain aspects of Baan behave with respect to hints and statistics. As shown in the examples, 0214 is a recommended value and has proven to give the best performance for Level 2 databases. If your database is still a Level 1 database, then 011 is the optimization setting that you should use.
If your ora_storage or ora_storage_param file contains entries with optimization entries other than 0214 or 011 for Level 2 and Level 1, respectively, then you should change the settings to the applicable value.
All of the settings mentioned so far are easy to check from within the BSE and changes to them are incorporated by using an editor such as vi or wordpad.
3. Oracle Table and Index Statistics
The next area to investigate, and the one that has the greatest potential at improving database performance, is the table and index statistics in Oracle. This is something that will require DBA access to the database.
Oracle uses these statistics to optimize queries to make processing as efficient as possible. If statistics are not available or are not up to date, then performance will suffer because Oracle will have a more difficult time retrieving data rapidly.
If you can get into Oracle via a tool such as SQL*Plus or SQL*Plus Worksheet, the following query will tell you when statistics for a table were last updated (your Baan schema may be different):
SELECT table_name, last_updated, num_rows
FROM dba_tables
WHERE table_name LIKE ‘BAAN.<TABLE NAME>’;
If the above query shows a date that is older than one week to one month (depending on the frequency of updates to the table), then the statistics available to Oracle are likely to be hampering performance.
To update the statistics, use the following commands:
First, the table’s statistics should be generated with this command:
ANALYZE TABLE table name ESTIMATE STATISTICS 25 PERCENT.
Second, all indexes for the table should be analyzed with this command:
ANALYZE INDEX index name COMPUTE STATISTICS.
These commands will update the Oracle statistics for the table and its indexes so they are available to the database and make the queries run as efficiently as possible. It is especially important for large and/or frequently updated tables.
If the Oracle statistics are not kept up to date, then it is advisable to incorporate this analysis into the database maintenance plan. If tables are large, then it’s likely to be okay to analyze them once every other week or maybe even monthly. If the table is updated heavily during the week, then it’s best to analyze it weekly so that performance is maintained.
4. Oracle Initialization File Parameters
Last, if performance is still not where it needs to be, then the last area to investigate is the initialization settings for the Oracle database itself. These settings are maintained in a file named init<SID>.ora.
The nature of these settings is such that they should only be worked with by a DBA.
The recommended path to investigate these settings is to have the DBA generate a report of Oracle performance statistics using two Oracle scripts, utlbstat.sql and utlestat.sql. Prior to running them, the DBA will have to ensure that timed statistics are enabled on the database and enable them if not.
How this process works is that the first script takes a snapshot of what is going on in the database and stores that information in a table. Then the second script takes another snapshot and fills a second table and then generates a report called report.txt that is based on the differences between the tables. Analyzing the report.txt output will allow you to identify performance bottlenecks and draw some conclusions about how the settings in the parameter file are affecting performance.
Once timed statistics are enabled, the process is to:
1. Start the database and allow it to stabilize under a normal workload.
2. Run the utlbstat.sql script.
3. Run the session that is the subject of concern.
4. After 15, 30, or 60 minutes have passed (the amount of time depends on the nature of the problem), run the utlestat.sql script.
5. Analyze the report.txt output.
6. Make changes to the parameters in the init<SID>.ora file or determine if hardware changes are needed.
If you have any questions about this process, please contact Baan Tools Support at 800-925-2226.
END OF QUICK GUIDE