|Oracle Ultra Search Online Documentation
The operations described in this document are database administration operations. They can be performed using Oracle Enterprise Manager or SQL*Plus.
This document lists the necessary steps for:
- Configuring the Oracle Server for Ultra Search.
- Creating tablespaces and users for each Ultra Search instance.
Step 1: Tune the Oracle Database
Increase the size of the Oracle redo logs, if necessary.
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#
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;
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';
Increase the size of the undo space.
Tune the Oracle initialization parameters in the init<SID>.ora file.
Step 2: Create and Assign the Temporary Tablespace to the CTXSYS User
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.
Step 3: Create a Large Tablespace for Each Ultra Search Instance User
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.
Step 4: Create and Configure New Database Users for Each Ultra Search Instance
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.
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.
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>;
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);
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';