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

WebCenter Content: Check-in sin Primary File

Image
En esta entrada se explican dos ascpectos relacionados con los Primary File: Primary File fields Permitir que un Check-in no obligue a subir un fichero asociado para todos los contenidos. Cómo permitir Check-in para un perfil específico y ocultar los campos de Primary File de un formulario de Check-in . Permitir Check-in sin Primary File Para permitir los Check-in sin Primary asociado para todos los contenidos de WebCenter Content solo hay que añadir la siguiente variable a la configuración: AllowPrimaryMetaFile=true Configuración de Oracle WebCenter Content Permitir Check-in y ocultar los campos para un Profile específico En caso de requerir realizar la acción para ciertos perfiles de WebCenter Content. Seguir los siguientes pasos: Crear una nueva regla que se activará con las condiciones de Check-in Regla que se activa con Check-in En la pestaña de Side Effects añadir el siguiente IdocScript que permitirá la creación de contenido sin Primary File y ade...
Read more