Configuring the Oracle Server for Ultra Search

• Increase the size of the Oracle redo logs, if necessary.

  • Every instance of an Oracle database has an associated online redo log, which is a set of two or more online log files that record all committed changes made to the database. Online redo logs protect the database in the event of an instance failure. The size of redo log files determines the frequency of redo log file switches. This, in turn, significantly impacts text indexing speed. To reduce the frequency of logfile switches, ensure that the redo log files are each 10Mb or more. See Oracle9i Designing and Tuning for Performance or Oracle9i Administrator's Guide for details on tuning your system. However, here are some quick tips on how to increase the redo log file sizes, if necessary. Enter the statements in the following section with the appropriate Oracle administrator privileges.
    
    
  • Step 1: Locate redo log files and determine their sizes:

    •  SELECT v$logfile.member, v$logfile.group#, v$log.status, v$log.bytes
      
       FROM v$log, v$logfile
      
       WHERE v$log.group# = v$logfile.group#
      

  • Step 2: Add larger redo log files:

    • ALTER DATABASE ADD LOGFILE '<redo log directory>/newredo1.log' size 10m;
      

    • ALTER DATABASE ADD LOGFILE '<redo log directory>/newredo2.log' size 10m;
      

    • ALTER DATABASE ADD LOGFILE '<redo log directory>/newredo3.log' size 10m;
      

    • A production database should have more log members for each log group, and different storage devices should be used to increase performance and reliability.
      
      
  • Step 3: Drop the old log files:
    • For each old redo log file, enter the ALTER SYSTEM SWITCH LOGFILE statement until that logfile's status is INACTIVE. This is necessary to ensure that Oracle is not using that logfile when you try to drop it.
    • Then, drop the old redo log file with the following statement:

      ALTER DATABASE DROP LOGFILE '<redo log directory>/redo01.log';
      ALTER DATABASE DROP LOGFILE '<redo log directory>/redo02.log'; 
      ALTER DATABASE DROP LOGFILE '<redo log directory>/redo03.log';
      

  • Step 4: Manually delete the old log files from the file system
    • For each old redo log file, use the appropriate operating system statement to delete the unwanted log file from the file system.
      
  
  
  

• Increase the size of the undo space.

  • Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo. The undo space created by the Oracle Installer is likely to be too small.
  • Historically, Oracle has used rollback segments to store undo. Oracle now offers another method of storing undo that eliminates the complexities of managing rollback segment space, and enables DBAs to exert control over how long undo is retained before being overwritten. This method uses an undo tablespace.
  • Oracle Corporation recommends that you use automatic undo management and increase the undo space using an UNDO_TABLESPACE. See Oracle9i Administrator's Guide for details on using automatic undo management.
    
  
  
  

• Tune the Oracle initialization parameters in the init<SID>.ora file.

  • PROCESSES: Increase this number to 50 or more.
  • SORT_AREA_SIZE: Increase this parameter to 5MB or more.
  • SORT_AREA_RETAINED_SIZE: Increase this parameter to 5MB or more.
  • JOB_QUEUE_PROCESSES: Increase this to three. (Set it to at least one.) This is needed because the Ultra Search crawler is launched by scheduling a database job. If this is 0, then no database jobs are run. As a result, any attempts to launch the Ultra Search crawler fail.
  • For the latest information on initialization parameters, see the Ultra Search Readme.
    

• The starter database created by the Oracle Installer most likely creates a temporary tablespace that is too small. Oracle Ultra Search uses the Oracle Text engine intensively. Therefore, a large temporary tablespace must be created for the Oracle Text system user CTXSYS.

• If you want greater read and write performance, create a raw tablespace.

• When you have created the temporary tablespace, assign it as the temporary tablespace for the CTXSYS user. To do so, you must log on as the SYSTEM or SYS user. You can assign the temporary tablespace to the CTXSYS user with the following statement:
  

  ALTER USER CTXSYS TEMPORARY TABLESPACE <NEW_TEMPORARY_TABLESPACE>
  

• See the Oracle9i Administrator's Guide for information on how to create a temporary tablespace.

• For each Ultra Search instance, you must create a tablespace large enough for containing all data obtained during the crawling and indexing processes. This amount is naturally subject to the amount of data you intend to crawl and index. However, it is often not possible to know in advance how much data you intend to collect. Try to obtain an estimate of the cumulative size of all data you want to crawl.

• If you cannot estimate the size, then try to allocate as much space as possible. If you run out of disk space later, Ultra Search is able to resume crawling after you have added more datafiles to the instance tablespace.

• Pay attention to the STORAGE clause in your CREATE TABLESPACE statement. The amount of data to be stored in the tablespace can potentially be very large. This can cause the Oracle Server to progressively allocate many new extents when more storage space is needed. If the extent management clause specifies that each new extent is to be larger than the previous extent (that is, the PCTINCREASE setting is nonzero), then you could encounter the situation where the next extent that the Oracle Server wants to allocate is larger than what is available. In such a situation, indexing is halted until new extents can be added to the tablespace.

  To help mitigate this problem, certain instance-specific tables have explicit storage parameter settings. The initial extent size, next extent size, and PCTINCREASE setting are defined for these tables. These tables are created when a new instance is created. The tables and their storage clause settings are as follows:
  
  

  • DR$WK$DOC_PATH_IDX$I 
                    (initial extent size 5M, next extent size 50M, PCTINCEASE 1)
    

  • DR$WK$DOC_PATH_IDX$K 
                    (initial extent size 5M, next extent size 50M, PCTINCEASE 1)
    

  
  

  For more information on creating tablespaces and managing storage settings, see Oracle 9i SQL Reference.

• If you want greater read and write performance, create raw tablespaces. See your Oracle 9i documentation on how to create a tablespace.

• Be sure to create a new large tablespace for each Ultra Search instance user.

• The Ultra Search system uses Oracle's Fine Grained Access Control feature to support multiple Ultra Search instances within one physical database. This feature is especially useful for large organizations or application service providers (ASPs) that want to host multiple disjoint search indexes within one physical installation of Oracle.

  Important:
  
  

• The Ultra Search system requires that each Ultra Search virtual instance belong to a unique database user. Therefore, as part of the installation process, you must create one or more new database users to own all data for your Ultra Search instance. (Note: If you intend to create more than one database instance, you should also create multiple user tablespaces - one for each user).
  
  

• You need to grant certain roles and privileges to each Ultra Search user. For convenience, the WKUSER role has all necessary privileges.

• Enter the following statements to create and configure a new user. You can run these statements as the WKSYS, SYSTEM, or SYS database user.

  CREATE USER <username> 
                IDENTIFIED BY <password> DEFAULT TABLESPACE <default_tbs> 
                TEMPORARY TABLESPACE <temporary_tbs> QUOTA UNLIMITED 
                ON <default_tbs>;
  

  [ where <username> = name of the Ultra Search instance owner ]
  [    and <password> = password of the Ultra Search instance owner ]
  [    and <default_tbs> = default tablespace for the Ultra Search instance created in step 3]
  [    and <temporary_tbs> = temporary tablespace created in step 2]
  
  

  GRANT WKUSER TO <username>;
  

  After the above steps, WKSYS or a Ultra Search super-user, can create a Ultra Search instance on this user schema.

• If you want this user to have the general administrative privilege or the super-user privilege of Ultra Search, you can log in as a Ultra Search super-user or WKSYS and click on the "Users" tab to grant the appropriate privilege.

  Note:

     For database release: After the database is installed, all the user schema accounts will be locked. To login as user WKSYS, you can unlock WKSYS by running the following statements as the SYSTEM, or SYS database user.
  

          ALTER USER WKSYS ACCOUNT UNLOCK;
  
          ALTER USER WKSYS IDENTIFIED BY <password>;
  

  
  
  

     For IAS release: After the infrastructure database is installed, all the user schema passwords will be randomized. To login as user WKSYS, you can change the WKSYS schema password by running the following statement as the SYSTEM, or SYS database user.
  

          ALTER USER WKSYS IDENTIFIED BY <password>;
  

Step 5: Gather Statistics for the Tables

If you notice performance degradation on the crawler, it might be because statistics have not been gathered for the tables. You should gather statistics for the following tables: wk$url, wk$doc, and dr$wk$doc_path_idx$i. Statistics for the wk$url table are the most important. You must regularly gather statistics, because statistics are used by the cost-based optimizer to generate the best execution plan. Make sure that the crawler is not running during the performance tuning period to avoid interference.

You can use the DBMS_STATS PL/SQL package or the ANALYZE procedure to gather statistics. The DBMS_STATS package can be run on either the table level or the schema level. Running on the schema level computes the statistics for the all the objects in the schema including the tables and indexes. Oracle Corporation recommends using the DBMS_STATS package.

Connect to the schema owning the Ultra Search instance. For example:

EXEC DBMS_STATS.GATHER_TABLE_STATS(‘<schema_name>', ‘<table_name>', null, DBMS_STATS.AUTO_SAMPLE_SIZE);

EXEC DBMS_STATS.GATHER_SCHEMA_STATS(‘<schema_name>', DBMS_STATS.AUTO_SAMPLE_SIZE);

or

ANALYZE TABLE <table_name> ESTIMATE STATISTICS SAMPLE 20 percent;

where <schema_name> for the owner of the Ultra Search instance and <table_name> is the table you want to gather statistics for (for example, wk$url).

Occasionally rebuilding the B-tree indexes could also improve performance by freeing up disk space. For example:

ALTER INDEX <index_name> REBUILD;

where <index_name> with the index that you want to rebuild.

To get a list of the indexes, run the following statement:

SELECT index_name FROM user_indexes WHEREindex_type='NORMAL';