2009/04/16

Text Index Synchronization and Optimization

Oracle Text is a core component of iFS, Ultra Search, and XML DB. The indexing and searching abilities of Oracle Text are full-text retrieval against virtually any datatype (including all LOB types). They are not restricted to data stored in the database. It can index and search documents stored on the file system and index more than 150 document types, including a search on widget and find documents that contain the work gadget.

How to apply Oracle Text and Text Index can be found here:
http://www.oracle.com/technology/oramag/oracle/04-sep/o54text.html

The Oracle Text indexes must be periodically synchronized so that new data is included in the indexes. This process can be scheduled to run on a periodic basis for Oracle 9i. The Procedure 'CTX_DDL.SYNC_INDEX' is used to synchronize text index. Oracle 10g supports an improved method of index synchronization over Oracle 9i. Oracle 10g has the ability to synchronize the Text indexes as data is saved. This method does not require background jobs to be configured.

In systems with frequent record modification or creation, the Text indexes will become fragmented over time. This fragmentation will lead to slow performance during index synchronization, saving data, and searching. The Procedure 'CTX_DDL.OPTIMIZE_INDEX' should be used to optimize the Text indexes periodically.

Text index types: CONTEXT, CTXCAT, or CTXRULE.

To make sure which indexes are Text indexes, log into user account and issue the following command:
SELECT idx_name
FROM ctxsys.ctx_indexes
WHERE idx_owner='';

Text Packages and associated information can be found in the Oracle official document 'Oracle Text Reference'.

2009/04/10

Sql Tuning Advisor

Sql Tuning Advisor (STA) is a new feature introduced in Oracle 10g. This automates the entire SQL tuning process.

Three ways to utilize STA:
Enterprise Manager Grid Control or Database Control
DBMS_SQLTUNE package
sqltrpt.sql script

1. STA Through EM
The user must have been granted the SELECT_CATALOG_ROLE role.
STA interface can be found through Performance Page > Advisor Central (Related Links) > SQL Tuning Advisor.
Through 'Top Activity' or 'Historical SQL (AWR)', you can choose Hot SQL that you want to tune. And then you can create tune sets and schedule sql tuning.

2. DBMS_SQLTUNE package
To use the APIs the user must have been granted the DBA role and the ADVISOR privilege.
Running SQL Tuning Advisor using DBMS_SQLTUNE package is a two-step process:
1) Create a SQL tuning task
2) Execute a SQL tuning task
Example can be found in metalink Doc 262687.1.

3. sqltrpt.sql script
Starting with Oracle 10.2 there is a script ORACLE_HOME/rdbms/admin/sqltrpt.sql which can be used for usage of SQL Tuning Advisor from the command line.