Database Tuning =============== The purpose of this document is to help users improve the performance of the RDBMS used by VPO. These are general suggestions and guidelines. While they apply to most installations, there are certainly situations in which they do not apply. Additional information on DB tuning can be found in various documents. Some are listed at the end of the document. The information in those documents should be reviewed to fully understand the pros and cons of the following suggestions. This document contains some general notes on performance and a RDBMS specific section. General notes ------------- 1) Tuning is heavily dependent on the used parameters like: * machine type * memory and swap space and utilization * disks and controllers * number of nodes in OV * applications integrated with initial command * what other applications are running on the system * how the kernel is configured There are some additional hints for improving performance: * increase main memory If a lot of memory is available, more DB queries can be done in the memory and not on disk and are therefore much quicker. * tune kernel: (HP9000 Series 800 ) - increase number of buffer headers along with number of buffer pages (nbuf=2048, cache=4096 (= 16 MB)) (HP9000 Series 700 ) - number of buffer headers along with number of buffer pages should be set to 0 (this implies dynamic management of that resources)! - increase inode cache (1563) - take care that number of open files is sufficient. (Sun Solaris) - increase maximum memory allowed in buffer cache (bufhwm) * don't configure swap space on disks holding the database files and/or transaction logfiles, make sure that you have sufficient free space on disks having swap configured Note, after increasing the amount of memory for the DB watch carefully the memory utilization (e.g. using "glance") If the processes use too much memory the swap rate will increase and slow down the performance instead of increasing it. 2) Run opcdbreorg(1M) periodically and do periodically download the history messages to reduce the size of some DB tables. This reduces the disk space needed and reduces fragmentation in the ORACLE DB. Refer to the opcdbreorg(1M) man page for more information about the opcdbreorg utility. For more information about scheduling history downloads see the HP VPO Administrator's Guide to Online Information. ORACLE Specific Tuning Hints ---------------------------- The hints listed below can be activated by either performing some ORACLE DBA commands from the admin tools svrmgrl or sqlplus or by setting some parameters in the ORACLE configuration file init${ORACLE_SID}.ora. The init.ora file will typically be named initopenview.ora for a VPO installation and resides in the directory $ORACLE_HOME/dbs. However this might not always be the case and a customer is free to change this. Whenever parameters in the init.ora file are changed, the ORACLE DB must be shutdown and restarted to activate the changes. 1) Place the redo logfiles and the data files on different disks. The location of the logfiles is defined at DB creation time and can be modified with svrmgrl/svrmgrm with the ALTER DATABASE RENAME FILE command. For some suggestions on how to distribute the DB files please see the section I/O load balancing at the end of the document. (see ORACLE7 Server - Administration / chapter on Online-Redo-Log Administration) Note: the hints listed under 2),3),4),6),8) might all affect the overall performance on the system as they are taking memory away from other processes. It is therefore very important to watch the system performance and especially check the paging and swapping rate when increasing these parameters. 2) Tune the ORACLE Shared Pool. This is the area in the SGA that holds copies of the ORACLE Data Dictionary, shared SQL areas and session specific SQL areas. -> to increase the ORACLE Shared Pool set SHARED_POOL_SIZE in the init.ora file openview default: SHARED_POOL_SIZE = 6000000 for larger DBs : SHARED_POOL_SIZE = 24000000 3) Tune the ORACLE Buffer Cache. This is the area in the SGA that holds copies of DB blocks. -> to increase the number of buffers set DB_BLOCK_BUFFERS in the init.ora file openview default: DB_BLOCK_BUFFERS = 550 for larger DBs : DB_BLOCK_BUFFERS = 3000 (could even be set to DB_BLOCK_BUFFERS = 5000) 4) Increase the size of logbuffers. You should set this parameter only after the Shared Pool size and the DB Buffer Pool size have been adequately tuned. -> to increase the size of logbuffers set LOG_BUFFER in the init.ora file openview default: LOG_BUFFER = 65536 for larger DBs : LOG_BUFFER = 262144 5) Use an additional disk for 'opc_act_messages' and 'opc_hist_messages' tables. This means that the file(s) belonging to the OPC_1 tablespace should be moved to another location. -> to move data files in ORACLE use the ALTER TABLESPACE OPC_1 RENAME to command (see ORACLE7 Server - Administration) If further disks are available you also may move the opc_node_names (tablespace OPC_3), opc_msg_text, and opc_orig_msg_text tables (tablespace OPC_2) to separate disks. (see the I/O load balancing section). Please note that in ITO 5 the history messages were moved into new tablespaces: OPC_6 - OPC_9. 6) Tune the number of processes which can connect to the openview DB. In the VPO environment this is important if running many concurrent GUIs (both the JAVA and MOTIF GUIS need 1 connection per running user plus about 20 for the VPO server) openview default: PROCESSES = 50 for larger DBs : PROCESSES = 100 (for 50 to 100 operators working in parallel set this to : PROCESSES = 200) 7) Tune the number of blocks ORACLE reads at once when performing sequential scans. openview default: DB_FILE_MULTIBLOCK_READ_COUNT = 8 for larger DBs : DB_FILE_MULTIBLOCK_READ_COUNT = 16 8) Tune the number of bytes that a user has available for sorting. Since this memory is allocated on a per user process basis be carefull whith this parameter in VPO environments running many concurrent GUIs. openview default: SORT_AREA_SIZE = 65536 for larger DBs : SORT_AREA_SIZE = 100000 9) Tune the number of Database Writers. The recommendation is to set this to the number of disks +1. Please do not set this parameter if you are using asynchronous I/O. -> to set the number of Database Writers set DB_WRITERS in the init.ora file openview default: DB_WRITERS = 1 set it to: number of discs holding ORA DB files +1 10) Tune Rollback Segments Rollback segments store data as it existed before an update. One rule of thumb is to have one rollback segment per four active transactions. The openview DB comes with a default of 4 rollback segments. Additional rollback segments can be added via the CREATE ROLLBACK SEGMENT command and have to be activated by: - changing the init.ora file or - through the ALTER ROLLBACK SEGMENT .. ONLINE command. openview default: ROLLBACK_SEGMENTS = (r01,r02,r03,r04) 11) Increase the size of the redo log files. You can create the new redo logs on a different disk and thus combine this step with step 1). For example to increase the redo log file size from 3MB (default) to 20MB use the following steps. Login as the Oracle database administrator: # su - oracle sqlplus /nolog SQL> connect / as sysdba; Get the current redo logs and logfile group numbers: SQL> select * from v$log; Add 3 new logfile groups with new size (20MB), for example: SQL> alter database add logfile '/u01/oradata/openview/redo04.log' size 20M; SQL> alter database add logfile '/u01/oradata/openview/redo05.log' size 20M; SQL> alter database add logfile '/u01/oradata/openview/redo06.log' size 20M; Switch the active redo log until one of the new ones is active: SQL> alter system switch logfile; SQL> alter system switch logfile; SQL> alter system switch logfile; Drop the old logfile groups, for example: SQL> alter database drop logfile group 1; SQL> alter database drop logfile group 2; SQL> alter database drop logfile group 3; SQL> exit Remove the old files in the file system, for example: rm -f /u01/oradata/openview/redo01.log rm -f /u01/oradata/openview/redo02.log rm -f /u01/oradata/openview/redo03.log Note: Oracle still accesses these files. To regain the disk space, you need to shutdown and re-start the database. I/O load balancing ------------------- Depending on the number of disks available here is how you could distribute the DB files ( tablespaces, redo log files). Configuration with DB files spread over 2 disks: disk1: Oracle Binaries RBS SYS TEMP OPC_1 OPC_7 OPC_8 OPC_9 OPC_10 OPC_INDEX_2 control01.ctl control02.ctl redo01.log redo02.log disk2: OPC_2 OPC_3 OPC_4 OPC_5 OPC_6 OPC_TEMP OPC_INDEX_1 OPC_INDEX_3 control03.ctl redo03.log Configuration with DB files spread over 3 disks: disk1: Oracle Binaries RBS SYS TEMP OPC_1 OPC_8 OPC_10 OPC_INDEX_2 control01.ctl redo01.log disk2: OPC_2 OPC_3 OPC_5 OPC_6 OPC_TEMP OPC_INDEX_3 control02.ctl redo02.log disk3: OPC_4 OPC_7 OPC_9 OPC_INDEX_1 control03.ctl redo03.log Configuration with DB files spread over 4 disks: disk1: Oracle Binaries SYS TEMP OPC_1 OPC_8 OPC_10 control01.ctl redo01.log disk2: OPC_2 OPC_3 OPC_5 OPC_TEMP OPC_INDEX_2 control02.ctl redo02.log disk3: OPC_4 OPC_6 OPC_INDEX_3 control03.ctl redo03.log disk4: RBS OPC_7 OPC_9 OPC_INDEX_1 Books on Oracle Performance Tuning: ----------------------------------- Documentation supplied with the Oracle8i release. Oracle8i and Unix Performance Tuning Ahmed Alomari Prentice Hall PTR September 2000 Oracle Performance Tuning Tips & Techniques Richard J. Niemiez, Joe Trezzo, and Bradley D. Brown Osborne McGraw-Hill April 1999 Oracle Performance Tuning Peter Corrigan and Mark Gurry O'Reilly & Associates, Inc. November 1996