|Oracle Ultra Search User's Guide
Part Number B10043-01
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.
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.
Oracle9i Database Performance Tuning Guide and Reference for information on how to tune this parameter
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.
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
OPTIMIZE_INDEX in token mode. The Ultra Search index name is
Oracle Text Reference for more information on
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.
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.
Oracle9i Database Performance Tuning Guide and Reference for information on tuning this parameter
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
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
<data-source class="oracle.jdbc.pool.OracleConnectionCacheImpl" name="UltraSearchDS" location="jdbc/UltraSearchPooledDS" username="user" password="pass" url="jdbc:oracle:thin:@hostname:1521:oracle_sid" min-connections="2" max-connections="30" inactivity-timeout="30" > <property name="cacheScheme" value="1" /> </data-source>
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
The PL/SQL package used for Ultra Search query is