10 Replies Latest reply: Jun 19, 2014 6:02 AM by orausern RSS

    Performance issues and options to reduce load with Oracle text implementation

    orausern

      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:

      Elapsed Time (s)Executions Elapsed Time per Exec (s) %Total%CPU%IOSQL IdSQL ModuleSQL Text
      27,386.25305,4410.0916.5015.829.98ddr8uck5s5kp3  begin ctxsys.drvdml.com_sync_i...
      14,618.81213,9800.078.818.3927.7902yb6k216ntqf  begin ctxsys.syncrn(:idxownid,...

       


      Full Text of above top sql:

      ddr8uck5s5kp3begin ctxsys.drvdml.com_sync_index(:idxname, :idxmem, :partname);
      end
      02yb6k216ntqf

      begin ctxsys.syncrn(:idxownid, :idxoname, :idxid, :ixpid, :rtabnm, :flg); end;

       

      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 optimized and doesn't the optimization itself lead to some load? Can someone suggest?

       

      Thanks,

      OrauserN

        • 1. Re: Performance issues and options to reduce load with Oracle text implementation
          Herald ten Dam

          Hi,

           

          for synchronisation it is always the best to have as much memory given for the index. Did you set some memory parameters for the index or in general?

           

          Herald ten Dam

          • 2. Re: Performance issues and options to reduce load with Oracle text implementation
            orausern

            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?

             

            Thanks again!

            OrauserN

            • 3. Re: Performance issues and options to reduce load with Oracle text implementation
              Barbara Boehmer

              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

              • 4. Re: Performance issues and options to reduce load with Oracle text implementation
                orausern

                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,

                OrauserN

                • 5. Re: Performance issues and options to reduce load with Oracle text implementation
                  Barbara Boehmer

                  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

                   

                  Message was edited by: Barbara Boehmer (fixed copy and paste error and provided better link)

                  • 6. Re: Performance issues and options to reduce load with Oracle text implementation
                    Herald ten Dam

                    Hi,

                     

                    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

                    • 7. Re: Performance issues and options to reduce load with Oracle text implementation
                      orausern

                      Thank you  Barbara (as allways!) and Herald,

                       

                      Hi Barbara,

                      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?

                       

                      Hi Herald,

                      I got a  No from business for any latency so we have to keep going with Sync on commit.

                       

                      Thanks

                      OrauserN

                      • 8. Re: Performance issues and options to reduce load with Oracle text implementation
                        Barbara Boehmer

                        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):

                         

                         

                        http://docs.oracle.com/cd/E11882_01/text.112/e24436/csql.htm#CCREF23731

                        • 9. Re: Performance issues and options to reduce load with Oracle text implementation
                          kevinUCB

                          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.

                          • 10. Re: Performance issues and options to reduce load with Oracle text implementation
                            orausern

                            Thank you everyone for the excellent help!