Oracle database maintenance

These maintenance and tuning guidelines apply to Oracle databases.

  1. Run the dbms_stats package on the database tables. Oracle uses a cost-based optimizer. The cost-based optimizer needs data to decide on the access plan, and this data is generated by the dbms_stats package. Oracle databases depend on data about the tables and indexes. Without this data, the optimizer must estimate.

    Rebuilding the indexes and running the dbms_stats package is critically important for optimal performance with Oracle databases. After the database is populated, this should be done on a regularly scheduled basis, for example, weekly.

    • REBUILD INDEX: After many changes to table data, caused by insertion, deletion, and updating activity, logically sequential data might be on non-sequential physical data pages, so that the database manager must perform additional read operations to access data. Rebuild the indexes to help improve SQL performance.
      1. Generate the REBUILD INDEX commands by running the following SQL statement on the Oracle database, where dbuser is the value from com.collation.db.user=: 
        select 'alter index dbuser.'||index_name||' rebuild tablespace ' 
||tablespace_name||';' from dba_indexes where owner = 'dbuser' 
and index_type not in ('LOB');

        This generates all of the ALTER INDEX commands that you need to run.

      2. Run the commands in SQLPLUS or some comparable facility. Rebuilding the indexes on a large database takes 15 - 20 minutes.
  2. DBMS_STATS: Use the Oracle RDBMS to collect many different kinds of statistics as an aid to improving performance. The optimizer uses information and statistics in the dictionary to determine the best access to the database based on the query provided. Statistical information is collected for specific tables and indexes in the local database when you run the DBMS_STATS command. When significant numbers of table rows are added or removed, or if data in columns for which you collect statistics is updated, run the DBMS_STATS command again to update the statistics.
    • The gen_db_stats.jy program in the $COLLATION_HOME/bin directory outputs the database commands for either an Oracle or DB2® database to update the statistics on the TADDM tables. The following example shows how the program is used:
      1. cd $COLLATION_HOME/bin
      2. Run this SQL statement, where tmpdir is a directory where this file is created: 
        ./gen_db_stats.jy > tmpdir/TADDM_table_stats.sql

        In a streaming server deployment, run this statement on the primary storage server.

      3. After this is complete, copy the file to the database server and run the following command:
        • To execute a script file in SQLPlus, type @ and then the file name: SQL > @{file}
      4. Run the commands in SQLPLUS or some comparable facility.
  3. Buffer pool: A buffer pool or buffer cache is a memory structure inside Oracle System Global Area (SGA) for each instance. This buffer cache is used for caching data blocks in the memory. Accessing data from the memory is significantly faster than accessing data from disk. The goal of block buffer tuning is to efficiently cache frequently used data blocks in the buffer cache (SGA) and provide faster access to data. Tuning block buffer is a key task in any Oracle tuning initiative and is a part of the ongoing tuning and monitoring of production databases. The Oracle product maintains its own buffer cache inside the SGA for each instance. A properly sized buffer cache can usually yield a cache hit ratio over 90%, which means that nine requests out of ten are satisfied without going to disk. If a buffer cache is too small, the cache hit ratio will be small and more physical disk I/O results. If a buffer cache is too big, parts of the buffer cache are underutilized and memory resources are wasted.
    Table 1. Buffer pool size guidelines (db_cache_size)
    Number of CIs Guideline buffer pool size
    < 500,000 38000
    500,000 - 1,000,000 60000
    > 1,000,000 95000
  4. You can double the size of maximum open cursors, if the discovery or bulkloading takes too long to complete and NRS contain the following error:
    com.ibm.tivoli.namereconciliation.service.NrsService
getAliases(masterGuid)
SEVERE: NOTE ˆ*** SQL State = 60000.   SQL Code  = 604.   SQL Message = 
ORA-00604: error occurred at recursive SQL level 1 
ORA-01000: maximum open cursors exceeded                               
ORA-01000: maximum open cursors exceeded
  5. Verify that the versions of your Oracle JDBC driver and the Oracle server are the same. If necessary, replace the Oracle JDBC driver file in the following locations.
    Note: This only applies when BIRT Report Viewer is enabled.
    • TADDM 7.3.0 - $COLLATION_HOME/deploy-tomcat/birt-viewer/WEB-INF/platform/plugins/org.eclipse.birt.report.data.oda.jdbc_2.2.1.r22x_v20070919/drivers/
    • TADDM 7.3.0.1, and later - $COLLATION_HOME/apps/birt-viewer/WEB-INF/platform/plugins/org.eclipse.birt.report.data.oda.jdbc_2.2.1.r22x_v20070919/drivers/
    • $COLLATION_HOME/lib/jdbc/