| Performance issues and options to reduce load with Oracle text implementation [message #615796] |
Mon, 09 June 2014 06:10  |
orausern
Messages: 826
Registered: December 2005
|
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,
[mod-edit: image inserted into message body by bb]
-
Attachment: awr.gif
(Size: 260.15KB, Downloaded 6412 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  |
 |
Barbara Boehmer
Messages: 9101
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 |
orausern
Messages: 826
Registered: December 2005
|
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 |
|
Barbara Boehmer
Messages: 9101
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
