Skip Headers

Oracle Ultra Search User's Guide
Release 9.0.3

Part Number B10043-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page

Go to previous page
Go to next page
View PDF

Tuning Query Performance

This appendix contains suggestions on how to improve the performance of the Ultra Search query. Query performance is generally impacted by response time and throughput.

  1. Tune the DB_CACHE_SIZE parameter.

    The database buffer cache keeps frequently accessed data read from datafiles. Efficient usage of the buffer cache can improve Ultra Search query performance. The cache size is controlled by the DB_CACHE_SIZE initialization parameter.

    See Also:

    Oracle9i Database Performance Tuning Guide and Reference for information on how to tune this parameter

  2. Optimize the index.

    Optimize the Ultra Search index after the crawler has made substantial updates. This is done by scheduling index optimization on a regular basis. Make sure index optimization is scheduled during off-peak hours, because query performance is significantly degraded during index optimization.

    See Also:

    "Index Optimization"

  3. Optimize the index based on tokens.

    Optimize the Ultra Search index by basing it on frequently searched tokens. To log, queries, use the administration tool to turn on query statistics collection. The frequently searched tokens then can be passed to CTX_DDL.OPTIMIZE_INDEX in token mode. The Ultra Search index name is WK$DOC_PATH_IDX.

    See Also:

    Oracle Text Reference for more information on OPTIMIZE_INDEX

  4. Simplify query expansion.

    The search response time is directly influenced by the Oracle Text query string used. Although Ultra Search provides a default mechanism to expand user input into a Text query, simpler expansions can greatly reduce search time.

    See Also:
  5. Size the shared pool.

    The shared pool stores the library cache and the dictionary cache. The library cache stores recently executed SQL and PL/SQL code. A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache. For this reason, the shared pool should be sized to ensure that frequently used data is cached. The shared pool size is controlled by the SHARED_POOL_SIZE initialization parameter.

    See Also:

    Oracle9i Database Performance Tuning Guide and Reference for information on tuning this parameter

  6. Define JDBC connection pooling.

    The Ultra Search middle tier connects to the database through JDBC. Because creation of a connection is an expensive operation in JDBC, a pool of open connections is used to improve the response time of queries. With Oracle iAS, OC4J can manage the connection pool for the applications.

    The minimum size, maximum size, and allocation algorithm of the pool can be specified in the data-sources.xml configuration file of OC4J.

    The following is an example of a data source definition, with minimum 2 and maximum 30 open-connections. Each connection closes after 30 seconds of inactivity, and new connections are created dynamically according to load. The other caching schemes are FIXED_WAIT_SCHEME and FIXED_RETURN_NULL_SCHEME.



                   inactivity-timeout="30" >
                  <property name="cacheScheme" value="1" />
  7. Pin the query package in memory.

    Pin frequently used packages in the shared memory pool. When a package is pinned, it remains in memory, no matter how full the pool gets or how frequently you access the package. You can pin packages using the supplied package DBMS_SHARED_POOL.

    The PL/SQL package used for Ultra Search query is WKSYS.WK_QRY.

    See Also:

    Oracle9i Supplied PL/SQL Packages and Types Reference