WCP12c: Improve performance of your Search Content Queries

Hi,

A year ago I spoke in NL OUG 2017 on how to improve you Oracle WebCenter Portal 12c performance.

https://es.slideshare.net/DanielMerchnGarca/nloug-2017-oracle-webcenter-portal-12c-performance

Recently, I had a customer who was running a very well tunned Oracle WebCenter Portal 12c platform, but it has been growing a lot during the latest years.

I have realised that queries to Oracle WebCenter Content were performing very bad, so I checked the typical:
  • Check the fragmentation of the current OTS index by using the PL/SQL command. It shown a perfect 1% and the PL/SQL job was scheduled for being executed each 3 days.

  • WebCenter Content configuration and AutoSuggestIndexer. All was good...

  • JVM Monitoring and CPU / Memory resources. All good as well.
So... what was happening then?. Enabling requestaudit trace in WebCenter Content it was showing response times very high.

The query thrown from Oracle WebCenter Portal was giving very bad answer time

>requestaudit/6    08.14 13:00:18.674    IdcServer-20950    GET_SEARCH_RESULTS [dUser=weblogic][dSecurityGroup=XXX][QueryText=%28+xIdcProfile+%3cmatches%3e+%60YYYYY%60+%3cAND%3e+xNewsSection+%3cmatches%3e+%60Real+Estate+News%60+%3cAND%3e+xEventsDate+%3c%3d+%602018-08-14+00%3a00%3a00%60+%29][IsJava=1] 3.2619810104370117(secs) 

However the same query thrown directly from WebCenter Content by URL was giving decent average response time.

>requestaudit/6    08.14 13:00:18.674    IdcServer-20950    GET_SEARCH_RESULTS [dUser=weblogic][dSecurityGroup=XXX][QueryText=%28+xIdcProfile+%3cmatches%3e+%60YYYYY%60+%3cAND%3e+xNewsSection+%3cmatches%3e+%60Real+Estate+News%60+%3cAND%3e+xEventsDate+%3c%3d+%602018-08-14+00%3a00%3a00%60+%29][IsJava=1] 0.28199873787298(secs)

After enabling systemdatabase trace, I have seen the real query thrown in both scenarions.
  1. The slow scenario (from WCP) was adding lot of clauses for the dDocAccount.

    >searchquery/6    08.14 13:00:15.483    IdcServer-20950    query(live): (((( ((NewsWebContent) WITHIN xIdcProfile)   and   SDATA(sdxDPGNewsSection LIKE 'REAL ESTATE NEWS')   and   SDATA(xEventsDate <= '2018/08/14 04:00:00') ))))  and  (((((idcnull) WITHIN dDocAccount)) or (((AUTHEN%) WITHIN dDocAccount)) or (((WCILS%) WITHIN dDocAccount)) or (((PEWebCenter/PU%) WITHIN dDocAccount)) or (((WebCenterSpaces/PU%) WITHIN dDocAccount)) or (((PUBLIC%) WITHIN dDocAccount)) or (((scf5139f6597d4aecb2536b63edec01c3%) WITHIN dDocAccount)))) [1,3] sort(xEventsDate DESC)

  2. The direct scenarion (from WCC) was not adding the clauses and performing well without the dDocAccount clause

 So, the problem was that the dDocAccount were not optimized. So to fix the problem:
  1.  Optimize the dDocAccount metadata using the Configuration Manager Applet, Advance settings.


  2. Configure Oracle WebCenter Content config DoCaseInsensitiveAcctSearch=false to do not make Case Insensitive Account Searching. (It requires restart of the Content Server)


  3. Perform a Fast Re-Index of Oracle WebCenter Content.

  4. Check the query generated in the systemdatabase trace. Now it should changed the dDocAccount clause and the performance should increase considerably.

    >systemdatabase/6    10.30 13:21:09.825    IdcServer-29950    Parameters: ([indexName:FT_IdcText2],[tableName:IdcText2],[queryText:((((((((((((DEFINESCORE((SHQ), RELEVANCE * .1))   and  ( ((idcnull) WITHIN xWebsiteObjectType)   or   ((Data File) WITHIN xWebsiteObjectType) )  and    idccontenttrue  NOT   ((image/jpeg) WITHIN dFormat)   and    idccontenttrue  NOT   ((image/png) WITHIN dFormat)   and    idccontenttrue  NOT   SDATA(sdxRegionDefinition LIKE 'RD\_ARTICLE')   and    idccontenttrue  NOT   SDATA(sdxRegionDefinition LIKE '\LEGALCASE\_RD')   and    idccontenttrue  NOT   SDATA(sdxRegionDefinition LIKE '\NEWJOINERS\_RD') ))))))))))  and  (((SDATA(sddDocAccount LIKE 'IDCNULL')) or (SDATA(sddDocAccount LIKE 'AUTHEN%')) or (SDATA(sddDocAccount LIKE 'WCILS%')) or (SDATA(sddDocAccount LIKE 'PEWEBCENTER/PU%')) or (SDATA(sddDocAccount LIKE 'WEBCENTERSPACES/PU%')) or (SDATA(sddDocAccount LIKE 'PUBLIC%')) or (SDATA(sddDocAccount LIKE 'SCF5139F6597D4AECB2536B63EDEC01C3%')))))*10.0*10.0]

Comments

Post a Comment

Popular posts from this blog

OJET: Inter-Module communication in TypeScript Template

Image
In this post I bring another example on how to Communicate Modules using the Oracle JET - TypeScript Template based on the Oracle JET Cookbook: InterModule Communication From now and so on will create multiple examples using TypeScript for those who are not familiar with it and wants to turn the Cookbook examples into TypeScript :). The example ( ojet-typescript-list-detail ) can be found as usual in my GitHub repository: https://github.com/DanielMerchan/ojet-examples This example is a bit different compared to the Cookbook example where the modules are not running at the same time within a Module. This example is built as described in the following images: Customers module acts as the Wrapper Module. It coordinates when to load the list and detail modules. Customers module makes use also of signals npm module for communication between Customers-List, Customers-Detail. Explanation of the Example customers.js is the coordinator responsible module for: initiali
Read more

OJET: Build and Deploy in an Application Server

Image
There are some other sources where an Oracle JET Application is packaged and deployed as a WAR in Oracle WebLogic Application Server (Arda Eralp) https://blogs.oracle.com/ardaeralp/deploy-oracle-jet-application-to-weblogic-server (Andrejus Baranovski's) http://andrejusb.blogspot.com/2016/07/serving-oracle-jet-application-from.html They may look bit old or maybe because they call directly grunt tasks by using the grunt-cli . In this post I will update a bit these posts in Oracle JET 6.1.0 and the usage of ojet build (instead of calling the grunt tasks) for also building the corresponding WAR file. The example can be found in my GIT repository: https://github.com/DanielMerchan/ojet-examples Let's start... Install grunt-war module in your Oracle JET Application. npm install grunt-war --save-dev Create a war.js (or call it whatever you want) in /scripts/grunt/config folder under your souce files. This is a sample code inside of it, check https://www.npmjs.com/packag
Read more

OJET: Select All options using only Checkboxset

Image
This is an example on how to select all other options by using another oj-checkboxset. GitHub: ojet-checkbockset-select-all The idea is to keep track of the selection and make use of the on-value-changed as described in the following code. self.colorOptions = ko.observableArray([ { id: "blueopt", value: "blue", color: "Blue" }, { id: "greenopt", value: "green", color: "Green" }, { id: "redopt", value: "red", color: "Red" }, { id: "limeopt", value: "lime", color: "Lime" }, { id: "aquaopt", value: "aqua", color: "Aqua" }, ]); self.selectAll = ko.observableArray([]); self.currentColor = ko.observableArray(["red"]); self.selectAllOptionChanged = (event) => { const newSelection = event.detail.value; if (newSelection.indexOf('all')
Read more