Archive for September, 2010

Synchronize Oracle Text index

18 September 2010

An often asked question is how to synchronize an Oracle Text Context index (A ctxcat index is automatically synchronized) , or otherwise asked, when does an insert/update/delete on the column(s) which is indexed come available for a search. There are more options depending on the version:

  1. Manual with ctx_ddl.sync_index (8 and above)
  2. With a job calling ctx_ddl.sync_index (8 and above)
  3. Synchronize through ‘Sync ‘ (10 and above)
  4. Manual with ‘Alter index’

Manual with ctx_ddl.sync_index

This is the oldest method. You call the procedure with the name of the index and it will sync the index. There is an option for a second parameter, which is the memory available for the synchronization. If there are a lot of updates to do, it is the best to set here some value. Otherwise the default is the value found in ctx_parameters for default_index_memory.

Ctx_ddl.sync_index called in a job

The manual update cost a lot of management, when must I do it and so on. Therefore it is better to schedule the synchronization. This can be done with dbms_job or in newer versions of the Oracle database with dbms_scheduler. By using these you don’t have less to manage yourself. With this job you can self design when the jobs needs to run.

Synchronize through ‘Sync’

Since 10g it is possible by creating the index to set the characteristics of a synchronization. This can be done by setting the sync option within the parameters section. There are 3 options:

  • Manual: You still have to call ctx_ddl.sync_index
  • Every ‘interval’: with this option you create a job (just as the second here above mentioned option). “interval” has the same syntax as just by the dbms_scheduler package. For this option the user needs ‘Create job’ rights.
  • on Commit: This option will at every commit sync the index. Use this option only if you don’t have too many changes on the columns of the index. The transaction will wait till the index is synchronized.

Manual with ‘Alter index’

This is the less used option, because the whole index will be rebuild. Because of the complete rebuild, it cost time and resources. An advantage is that the index also is optimised afterwards. Use this option only if you need to make changes to the index itself, as some setting of parameters, or a new tablespace. Otherwise use one of the above.

Synchronization and when

It is possible to see which indexes needs to be synchronized. There is the view CTX_PENDING. In this view are the indexes with the rowids of the records who need to be synchronized. So you can possible query this view and see which indexes needs a sync. This can of course also be scheduled.

After an optimisation an index is not optimised, meaning the index can be bigger than necessary is. But about that I will talk later.

Oracle 11g and Documentum (Part 3)

7 September 2010

So how did we go further? By using Oracle Enterprise Manager Grid Control (OEMGC), the queries which cause performance penalties, could be found quickly and so some extra indexes were made. But that wasn’t the only thing we saw during test. We also saw some queries running twice instead of running once. It happens for example by changing from one tab to another. Sometimes a query from the first tab was also run, before the second tab. For this behaviour we had to get support from EMC.

For other queries we need to check further, and came to the problem what Oracle has when FORCE is used for CURSOR_SHARING. The Oracle Optimizer doesn’t always like histograms with the setting of the parameter. After looking at the queries the histograms on the table DM_GROUP_R were the problem. So the statistics on the table were collected without the histogram and then these statistics were locked.