6 Replies Latest reply: Jul 24, 2014 8:45 AM by William Phelps RSS

    Enhancing performance for with respect to search in oracle UCM 11g

    Bharath Umesh

      Hi All,

       

      Please provide inputs on how to enhance performance in Oracle UCM while doing search.

      Is there any config.cfg entry that we will have to do?

      Please give some information regarding this as its very critical .

       

      Thanks in advance,

      Bharath

        • 1. Re: Enhancing performance for with respect to search in oracle UCM 11g
          William Phelps

          I'm sorry, but there is no "MakeTheSearchGoFaster=true" configuration entry available.

           

          Search performance can be a tuning exercise.  Is the database optimized?  Is the database machine itself struggling? Do the metadata columns being searched have proper indexes created for them?  Do you have complex security models defined?  Any search caching enabled? Are there many metadata fields marked as "searchable"?

           

          Search performance can be a user training exercise.  Are the users doing "contains" (meaning "substring") searches?  If so, no amount of index creation will help.  Oracle Text Search seems to handle these types of searches better than simple DATABASE.FULLTEXT.

          • 2. Re: Enhancing performance for with respect to search in oracle UCM 11g
            Srinath Menon-Oracle

            Hi Bharath ,

             

            To add to what William mentioned , the starting points to analyse this should be

             

            1. WCC server tracing with system*,search*,requestaudit + full verbose tracing

             

            2. AWR report for search sessions which are showing performance issue .

             

            Thanks,

            Srinath

            • 3. Re: Enhancing performance for with respect to search in oracle UCM 11g
              Bharath Umesh

              Hi Srinath,

               

              Could you please elaborate on the points you have mentioned.

              A step by step description would be very helpful

               

              Thanks,

              Bharath

              • 4. Re: Enhancing performance for with respect to search in oracle UCM 11g
                Bharath Umesh

                Hi Wiliam,

                 

                Can you please let know how we could find out about

                1)Do the metadata columns being searched have proper indexes created for them? 

                2)  Any search caching enabled?

                 

                Thanks,

                Bharath

                • 5. Re: Enhancing performance for with respect to search in oracle UCM 11g
                  Srinath Menon-Oracle

                  1. WCC server tracing with system*,search*,requestaudit + full verbose tracing

                   

                  Enable the server tracing with the aforementioned sections along with full verbose and then run the search operation which is showing performance problem.

                   

                  Capture the server output and there look for those queries which are taking maximum time.

                   

                   

                  2. AWR report for search sessions which are showing performance issue .

                   

                  For the same search , collect the AWR report from DB and check the sql's which are taking long time to complete and that will give very good information in terms of all other db aspects .

                   

                  Thanks,

                  Srinath

                  • 6. Re: Enhancing performance for with respect to search in oracle UCM 11g
                    William Phelps

                    1) Since you are asking this question, it's a safe bet that no indexes have been created.  This is a task for the DBA.  Custom metadata that is added to the system via Configuration Manager do not automatically have corresponding database indexes created.  This is by design.

                     

                    What you need to do is determine *which columns* are being used in typical searches by your users, and create indexes on those commonly searched columns.  DO NOT simply add indexes to EVERY column - that is actually a performance hit on the database during document checkin and updates.  If you have 100 custom metadata fields, but users only search against five of them, create indexes on the five columns.  Srinath's AWR report suggestion will be of great value in determining this information.

                     

                    2) Search caching is turned on by default, so if you haven't disabled it intentionally there's nothing else to do in that regard.  HOWEVER, if you are using ACLs in the security model, be aware that ACLs basically cause every query being conducted to be rewritten and unique to the user.  As such, the cache is useless (unless the same user is doing the same search over and over).  Overly complex security models result in complex security clauses being appended to the query, which is why searches for users usually execute slower than searches executed by admins.

                     

                    Search performance is an analysis exercise.  There's usually no one magic bullet.