Home » Server Options » Text & interMedia » Performance issues and options to reduce load with Oracle text implementation (Oracle 11.2.0.2 on Linux)
Performance issues and options to reduce load with Oracle text implementation [message #615796] Mon, 09 June 2014 06:10 Go to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Hi Experts,

My database on Oracle 11.2.0.2 on Linux. We have Oracle Text implemented for fuzzy search. Our oracle text indexes are defined as sync on commit as we can not afford to have stale data. Now our application does literally thousands of inserts/updates/deletes to those columns where we have these Oracle text indexes defined. As a result, we are seeing a lot of performance impact due to the oracle text sync routines being called on each commit. We are doing the index optimization every night (full optimization every night at 3 am). The oracle text index related internal operations are showing up as top sql in our AWR report and there are concerns that it is causing lot of load on the DB. Since we do the full index optimization only once at night, I am thinking should I change that , and if I do so, will it help us?

For example here are some data from my one day's AWR report -in the uploaded file - awr.gif. The Oracle text internal procedures are taking as much as 20% of the DB time in the AWR report.

Now if I do the full index optimization more often and not just once at night 3 PM, will that mean, the load on DB due to sync on commit will decrease? If yes how often should I run the optimize procedure and doesn't the optimization itself lead to some load? Can someone suggest?

Note that there is another thread on this in OTN forum at this link: https://community.oracle.com/message/12474670
One expert has suggested to increase the memory related to the Oracle Text indexes but I am not sure how to do so and how to measure its impact.

I will be thankful for suggestions on the issue.




Thanks,
./fa/11934/0/


[mod-edit: image inserted into message body by bb]
  • Attachment: awr.gif
    (Size: 260.15KB, Downloaded 95 times)

[Updated on: Mon, 09 June 2014 08:06] by Moderator

Report message to a moderator

Re: Performance issues and options to reduce load with Oracle text implementation [message #615826 is a reply to message #615796] Mon, 09 June 2014 12:43 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
You can query the ctx_parameters view to see what your default and maximum memory values are:

SCOTT@orcl12c> COLUMN bytes	FORMAT 9,999,999,999
SCOTT@orcl12c> COLUMN megabytes FORMAT 9,999,999,999
SCOTT@orcl12c> SELECT par_name AS parameter,
  2  	    TO_NUMBER (par_value) AS bytes,
  3  	    par_value / 1048576 AS megabytes
  4  FROM   ctx_parameters
  5  WHERE  par_name IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY')
  6  ORDER  BY par_name
  7  /

PARAMETER                               BYTES      MEGABYTES
------------------------------ -------------- --------------
DEFAULT_INDEX_MEMORY               67,108,864             64
MAX_INDEX_MEMORY                1,073,741,824          1,024

2 rows selected.


You can set the memory value in your index parameters:

SCOTT@orcl12c> CREATE INDEX EMPLOYEE_IDX01
  2  ON EMPLOYEES (EMP_NAME)
  3  INDEXTYPE IS CTXSYS.CONTEXT
  4  PARAMETERS ('SYNC (ON COMMIT) MEMORY 1024M')
  5  /

Index created.


You can also modify the default and maximum values using CTX_ADM.SET_PARAMETER:

http://docs.oracle.com/cd/E11882_01/text.112/e24436/cadmpkg.htm#CCREF2096

The following contains general guidelines for what to set the max_index_memory parameter and others to:

http://docs.oracle.com/cd/E11882_01/text.112/e24435/aoptim.htm#CCAPP9274

[Updated on: Mon, 09 June 2014 12:50]

Report message to a moderator

Re: Performance issues and options to reduce load with Oracle text implementation [message #615828 is a reply to message #615826] Mon, 09 June 2014 13:17 Go to previous messageGo to next message
orausern
Messages: 735
Registered: December 2005
Location: Hyderabad
Senior Member
Thank you Barbara, as allways!!

One more queestion: I have 110 oracle text indexes in Production. and I guess they have this default setting of using 64 MB memory. So if lets say I double it to use 125 MB for each of the index, then total memory to be used by these Oracle text will be: 13750 MB or 13.5+ GB right? Currently our SGA is sized at 10GB, so I sholuld ask it to be raised to about 17 GB for this to happen. Am I right?

I am not sure so asking this. Is it right to allocate 125 MB for each index? Or may be for smaller indexes we can leave the default and for larger index we should use this value? Also do we need to increase the SGA like I said above since this Oracle text memory will take up memory from SGA so we need to increase the SGA size.


Thanks,

Re: Performance issues and options to reduce load with Oracle text implementation [message #615839 is a reply to message #615828] Mon, 09 June 2014 18:04 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
The default and maximum values on your system may not be the same as the values on my system so you need to run the following query to find out what the values on your system are:

SELECT par_name AS parameter,
       TO_NUMBER (par_value) AS bytes,
       par_value / 1048576 AS megabytes
FROM   ctx_parameters
WHERE  par_name IN ('DEFAULT_INDEX_MEMORY', 'MAX_INDEX_MEMORY')
ORDER  BY par_name;


According to what you posted, your index synchronization seems to be taking a lot of time. So, the idea is to increase the memory allocated to the index without causing paging to disk. You may need to do some testing to see at what point paging occurs. Increasing the SGA may help. The following contains some general guidelines:



http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_sga.htm#TGDBA520
Previous Topic: Text Index returns column name as well
Next Topic: CTXSYS fuzzy searching
Goto Forum:
  


Current Time: Wed Dec 17 17:23:15 CST 2014

Total time taken to generate the page: 0.10660 seconds