Home » Server Options » Text & interMedia » (INSERT/UPDATE/MERGE/DELETE) Slow performance on table after creating CTXSYS.CONTEXT text index on o (Oracle 11G R2 64bit database on Windows 2008 Server 64bit)
(INSERT/UPDATE/MERGE/DELETE) Slow performance on table after creating CTXSYS.CONTEXT text index on o [message #544172] Mon, 20 February 2012 07:57 Go to next message
s_a_s
Messages: 1
Registered: February 2012
Location: United Kingdom
Junior Member
Hi,

We have a table having around half a million records. We created CTXSYS.CONTEXT text index on one of the columns of type varchar2(4000 Byte).

Before creating the text index it took us 1 Second to Merge 5000 Records into the table while selecting from another table.

After creating the text index on the table the same merge as above is taking 7 seconds.

I have tried following parameters for the index but to no avail:

SYNC (MANUAL)
nontransactional
nopopulate

Our environment is Oracle 11G R2 64bit database on Windows 2008 Server 64bit.

Can anyone help on how to get the performance back up.

Would really appreciate any input.
Thanks
Re: (INSERT/UPDATE/MERGE/DELETE) Slow performance on table after creating CTXSYS.CONTEXT text index on o [message #560934 is a reply to message #544172] Tue, 17 July 2012 20:35 Go to previous message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
The index blocks are not cached until they are read. Since index lookups are random,
multiple head movements on the disk slow down the inserts. You can cache the index by:
ECSCDAP1 > alter index SCOTT.PK_EMP storage(buffer_pool keep);

Index altered.

Unless the init.ora parameter "db_keep_cache_size" is set by a DBA,
Oracle ignores the Keep pool and will stuff your index into the default
buffer_pool where it will compete with other less important indexes and tables.
ECSCDAP1 > alter system set db_keep_cache_size=3g scope=memory sid='CSCDAP1';

System altered.

ECSCDAP1 > -- connect to second node.

ECSCDAP2 > alter system set db_keep_cache_size=3g scope=memory sid='CSCDAP2';

System altered.

ECSCDAP2 > -- to make permanent.  From any node. 

ECSCDAP2 > alter system set db_keep_cache_size=3g scope=spfile sid='*';
Previous Topic: issue with Contains() in Oracle 9i (merged 3)
Next Topic: Multi-lingual searches
Goto Forum:
  


Current Time: Thu Mar 28 10:41:25 CDT 2024