Thank you Herald!! Can you please elaborate some more? I have set them up with default setting like as follows:
CREATE INDEX EMPLOYEE_IDX01 ON EMPLOYEES (EMP_NAME) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('LEXER cust_lexer WORDLIST emp_wl SYNC (ON COMMIT)');
My doubts are (sorry if some of these are basic questions!)
1) How much memory should I allocate to the Oracle Text index and how to do so ?
2) Also how to measure the impact of this change - I mean how do I prove it really helps , is there some kind of metrics I can generate (before this change and afterwards) to prove this is helpful?
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
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')
You can also modify the default and maximum values using CTX_ADM.SET_PARAMETER:
The following contains general guidelines for what to set the max_index_memory parameter and others to:
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.
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
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:
Message was edited by: Barbara Boehmer (fixed copy and paste error and provided better link)
how stale may it be? If it can wait a couple of minutes maybe then you may think of using an interval for example of 5 minutes. Then Oracle can bulk sync the index, but that is also a business matter.
something like this for the statement: alter index "Owner."Index" rebuild parameters('replace metadata sync (every "SYSDATE+5/1440")');
Herald ten Dam
Thank you Barbara (as allways!) and Herald,
I ran the sql you gave and found that in my production the default and max memory is 12 MB and 1GB! So what do I do now? To what value should I increase this? How can I test to see at what point paging happens , I am quite ignorant on this. Can you please suggest further?
I got a No from business for any latency so we have to keep going with Sync on commit.
It is up to you what values you want to experiment with. Since 1G, which is the same as 1024M is your maximum, I might try setting it to that for one index and see what effect it has. You can use the same AWR report that you used previously and compare performance. To determine when paging occurs depends on your operating system and what tools you have. Another option might be to use TRANSACTIONAL, which would enable searching of unsynchronized rows and allow you to synchronize at five-minute intervals, as Herald suggested, without any latency issue. You can read about that here (scroll down to transactional):
Since you are syncing on commit, the memory you allocate to syncing only has to match the in-memory list of tokens that each document contains. It is highly unlikely that memory is your issue.
Ideally, it would help your situation if you could sync less frequently, allocate the right amount of memory, and then monitor the optimization process to determine if the text indexes need more frequent service. If your use-case is that a small number of users need 'transactional' text search performance, but most can afford a 15/30/60 minute delay, then you can configure the index to behave that way.
You might also take a look at the total number of text indexes you have -- and need -- to answer the queries your users are asking. Each text index uses a number of objects that are best stored in RAM, and if you can collapse two or more distinct indexes into a single datastore (master-detail, multi-column, etc.), you can save some system resources.