A Oracle Text Reference

Oracle Files uses Oracle Text to facilitate full-text search and other advanced capabilities. The speed with which results are returned depends on several factors, including the quality of the Oracle Text index used with Oracle Files (IFS_TEXT). In addition, the end user's experience of the performance of the search can depend on how much time you let elapse before an in-progress search times out.

This appendix provides information about how to maintain the Oracle Text index to ensure optimal Oracle Files performance, and includes these topics:

• Oracle Text Tablespaces and Disk Utilization

• Creating and Maintaining the Oracle Text Index

• Modifying the Search Timeout Parameter

• Troubleshooting Oracle Text Problems

Previous names for Oracle Text include Oracle Context and Oracle interMedia Text. Many of the underlying indexes, views, tables, and various PL/SQL packages referred to in much of the administrator and application developer documentation still use Context or interMedia-related nomenclature. For example, the database schema that owns all Oracle Text objects, such as the indexes, is CTXSYS.

For detailed information about Oracle Text, visit the Oracle Technology Network (http://otn.oracle.com/products/text/).

Oracle Text Tablespaces and Disk Utilization

Disk space for Oracle Text is divided among three distinct tablespaces:

• The Oracle Text Data tablespace contains tables that hold text tokens (separate words) that exist within the various indexed documents. The storage for these text tokens is roughly proportional to the ASCII content of the document. The ASCII content percentage will vary depending on the format of the original document. Text files only have white space as their non-ASCII content and, therefore, will incur a greater per-document percentage overhead. Document types such as Microsoft Word or PowerPoint contain large amounts of data required for formatting that does not qualify as text tokens. The per-document percentage on these types of documents will, therefore, be lower. On a system with diverse content types, the expected overhead is approximately 8 percent of the sum of the original sizes of the indexed documents.

• The Oracle Text Keymap tablespace contains the tables and indexes required to translate from the Oracle Files locator of a document (the Oracle Files DocID) to the Oracle Text locator of that same document (the Oracle Text DocID). The expected space utilization for this tablespace is approximately 70 bytes per indexed document.

• The Oracle Text Index tablespace contains the B*tree database index that is used against the text token information stored in the Oracle Text Data tablespace. This will grow as a function of the ASCII content just as the Oracle Text Data tablespace does. On a system with diverse content types, the expected overhead is approximately 4 percent of the sum of the ASCII content of the documents, or approximately 1 percent of the sum of the total sizes of the indexed documents.

Use this information to estimate and plan disk storage needs for your Oracle Files instance.

Creating and Maintaining the Oracle Text Index

At the end of the configuration process for Oracle Files, the Oracle Files Configuration Assistant uses the SQL scripts shown in Table A-1 to create and populate the IFS_TEXT index.

These scripts are located in the following directory:

$ORACLE_HOME/ifs/files/admin/sql

If the installation process fails and you need to run these scripts manually, you should execute the scripts at a SQL*Plus prompt after logging on as the Oracle Files schema user (for example, IFSSYS).

Table A-1 SQL Scripts for Creating Oracle Text Index

Script	Usage
CreateContextFunnelProcedure.sql	Creates the procedure used by USER DATASTORE.
GrantContextToIFS.sql	Grants the Oracle Files user (schema) privileges on the Oracle Text-specific commands required to maintain the index. You must run this script as a database user with administrator privileges, such as SYSTEM.
CreateContextPreferences.sql	Tablespace and other text preferences are created by the Oracle Files user.
CreateContextIndex.sql	Creates the IFS_TEXT index based on the text preferences.

Maintaining the IFS_TEXT Index By Using the Oracle Text PL/SQL Packages

Two PL/SQL procedures are provided with Oracle Text for maintaining the index. Unlike a regular database index, the Oracle Text index is not dynamically updated with each insert or update of information. Rather, the index must be refreshed (or synchronized) periodically, using the Oracle Text stored procedure ctx_ddl.sync_index.

The ctx_ddl.sync_index procedure does not rebuild the entire index, but adds and deletes records that have changed since the last synchronization. Since the changes are incremental, the more frequently this procedure is run, the faster it runs. Over the course of time, however, the index can become fragmented, so a companion procedure (ctx_ddl.optimize_index) is provided to optimize the index.

During Oracle Files configuration, the procedures to sync and optimize the IFS_TEXT index are automatically set up to run periodically in the background, using the DBMS_JOBS package of the Oracle9i Database Server. DBMS_JOBS procedures, which are similar to cron jobs on UNIX systems, are portable across all platforms on which the Oracle9i Database Server runs.

When the Oracle Files schema is created during configuration, two DBMS_JOBS are set up: Sync Job and Optimize Job.

Note: Sync Job and Optimize Job are only created automatically when a new schema is created. If you are upgrading from an existing schema, these jobs will not be created automatically.

Sync Job

Sync Job will periodically call the 'ctx_ddl.sync_index()' method. This method indexes the documents that were created or updated since the last run. By default, this job is set up to run every 30 minutes.

Optimize Job

Optimize Job will periodically call the 'ctx_ddl.optimize_index()' method. The goal of this job is to optimize the IFS_TEXT index by defragmenting it. By default, this job is run in FULL mode, with a maximum of one hour allocated for the optimization task. The job is set up to run every 24 hours, starting at midnight.

Monitoring DBMS_JOBS

DBMS_JOB log files can be found under the Oracle home that hosts the Oracle9i Database Server, in the directory that holds the background process logs. This directory is pointed to by the BACKGROUND_DUMP_DEST configuration parameter of the database server. You can recognize the log trace files by their name pattern, DBNAME_j###_process-id.trc.

Another database configuration parameter, JOB_QUEUE_PROCESSES, determines how many processes are available at any given time to run all background tasks. You may need to increase the value of this parameter if not enough processes are available to run Sync Job and Optimize Job. The default value is 10.

You can also look at the USER_JOBS view to see a list of all the jobs set up by the current schema user. The USER_JOBS view shows details such as the PL/SQL being run by each job, the last time each job was run, and when the jobs are scheduled to be run next. To see the USER_JOBS view, log on to the Oracle Files schema using SQL*Plus.

Changing or Removing the Default DBMS_JOBS

Two SQL files are used to set up and clear DBMS_JOBS in Oracle Files: SetupContextJobs.sql and ClearContextJobs.sql. These files are located in the following directory:

$ORACLE_HOME/ifs/files/admin/sql

SetupContextJobs.sql is used by the system during configuration to set up Sync Job and Optimize Job. ClearContextJobs.sql is provided for you to remove Sync Job and Optimize Job, in case you want to set up your own DBMS_JOBS.

See the Oracle9i Database Administrator's Guide for information about setting up your own DBMS_JOBS. You can also look at Sync Job and Optimize Job as examples.

Manually Synchronizing and Optimizing IFS_TEXT

To synchronize an existing IFS_TEXT index, use SQL*Plus to connect as the Oracle Files schema user, and enter:

exec ctx_ddl.sync_index('ifs_text');

You can also run the SyncContextIndex.sql script from the $ORACLE_HOME/ifs/files/admin/sql directory. In addition to synchronizing the IFS_TEXT index, this script will print extra log information to the console.

To optimize an existing IFS_TEXT index, use SQL*Plus to connect as the Oracle Files schema user, and enter:

exec ctx_ddl.optimize_index('ifs_text', 'FAST');

or

exec ctx_ddl.optimize_index('ifs_text', 'FULL', maxtime);

Monitoring Oracle Text Indexing of Oracle Files Documents

Oracle Files provides some utility-type SQL scripts to facilitate interaction with Oracle Text (see Table A-2). Read each .sql file for additional usage details. All scripts are available in:

$ORACLE_ HOME/ifs/files/admin/sql

Table A-2 SQL Scripts for Monitoring Oracle Text Indexing

Script	Usage
ViewContextErrors.sql	Script that decodes the operating system specific errors that were generated during Oracle Text indexing.
SyncContextIndex.sql	Script that synchronizes the Oracle Text index and enables you to monitor the Oracle Text synchronization process. Uncomment the first two lines in the script, which includes call to ctx_output.add_event(), to monitor on a rowid by rowid basis.
ViewDocumentByRowID.sql	Script that enables you to view additional information about a document that is indexed by Oracle Text. Use the docid from the Oracle Text log with this script.

Indexing Non-Standard Content Types

Oracle Files does not by default index every file that is moved into the system, but you can configure it to index any type of content you choose. To do this, designate the MIME type as "Indexed" on the New Format page (or Edit Format page, if the format already exists) in the Oracle Enterprise Manager Web site. The MIME type of a document is determined by its extension.

For example, you may want to index all your .java source code files. To do so:

1. Use the Oracle Enterprise Manager Web site to add the .java MIME type and designate it as Indexed on the New Format page.

2. Insert the files into the repository.

3. Sync the index using the procedure discussed in "Manually Synchronizing and Optimizing IFS_TEXT".

Modifying the Search Timeout Parameter

The IFS.SERVICE.SESSION.DefaultSearchTimeoutPeriod service configuration parameter specifies the timeout period for a running search that has not yet returned results. The default setting for this parameter (in the default service configurations) is 60 seconds. Increase this value and users will wait longer than a minute before a search times out; decrease the value to shorten the time in which a running search will time out.

Troubleshooting Oracle Text Problems

This section provides Oracle Text troubleshooting information.

Table A-3 Troubleshooting Oracle Text Problems

Problem	Probable Cause	Corrective Action
Cannot search on contents of any documents.	Documents have not been indexed.	Start the database instance and make sure that the Oracle Text indexing jobs are running. See "Creating and Maintaining the Oracle Text Index" for more information.
Server is slow only on content-based search activity.	Probable cause #1: Oracle Text tablespaces are on the same disk as other database files.	Move the Oracle Text tablespaces to other disks. See the Oracle9i Database Administrator's Guide for more information on moving tablespaces.
	Probable cause #2: Oracle Text indexes have become fragmented.	Regularly optimize the Oracle Text index GLOBALINDEXEDBLOB_I. See "Manually Synchronizing and Optimizing IFS_TEXT" for more information.
Searching on the contents of new documents stops working.	A recent document has caused Oracle Text server to fail.	Log in to SQL*Plus as files_schema/schema_password, and enter the following command: select count(*) from ctx_user_pending; If there are any rows in that view and the rows are not changing, then a recent document has caused Oracle Text to stop indexing. To determine which Oracle Files documents these rows refer to, see the problem "Oracle Files rows show up in the Oracle Text view ctx_user_index_errors." Check again to see if there are any rows in ctx_user_pending and, if so, that the rows are changing. If this does not resolve the issue, contact your Oracle Support Representative for further assistance.
Oracle Files rows show up in the Oracle Text view ctx_user_index_errors.	Oracle Files documents are corrupt or do not have the correct extension.	Determine which Oracle Files document is being referred to, based on the err_texkey from ctx_user_index_errors. SQL*Plus files_schema/schema_password select du.uniquename, vd.name, co.contentsize, cs.id, vd.id from odmv_document vd, odm_contentobject co, odmm_contentstore cs, odm_document od, odm_directoryuser du where vd.id = od.id and od.contentobject = co.id and co.content = cs.id and du.id = vd.owner and cs.id in ( select distinct od.id from ctx_user_index_errors cp, odmm_ contentstore od where od.rowid = err_textkey ) order by cs.id; Go to the Oracle Files Web interface and log in as the Subscriber Administrator. Search on the document name vd.id, where vd.id is the vd.id returned from the select statement above. Check document attributes, such as document size, to make sure that it is the correct document. Examine this document, looking for these problems: Is the file damaged in any way? Is the file name extension correct for this document? Is the character set of the document correct? If no obvious problems are found, send the document to your Oracle Support Representative for further diagnosis.
Oracle Files rows never get processed and never leave the Oracle Text view ctx_user_pending.	Oracle Files documents are corrupt or do not have the correct extension.	Follow the steps in "Oracle Files rows show up in the Oracle Text view ctx_user_index_errors." to determine which Oracle Files documents are being referred to, substituting ctx_user_pending for ctx_user_index_errors and pnd_rowid for err_textkey. Examine this document, looking for these problems: Is the file damaged in any way? Is the file name extension correct for this document? Is the character set of the document correct? If no obvious problems are found, send the document to your Oracle Support Representative for further diagnosis. Delete the document from Oracle Files.