SearchCache Is Running Expensive Queries At A Regular Interval, Causing Extra Load On The Database (Doc ID 1528849.1)

Last updated on NOVEMBER 03, 2016

Applies to:

Oracle WebCenter Content - Version 11.1.1.4.0 and later
Information in this document applies to any platform.

Symptoms

Using UCM Version 11gR1-11.1.1.4.0-idcprod1-101229T001824 on Linux.

Search Engine:: DATABASE.METADATA

There are two SQL statements generated by Oracle UCM that spends a really long time executing.  The two queries are very similar, one performing a count(*), while the other queries for the data :

>systemdatabase/6 12.20 14:41:00.911 SearchCache 62287 ms. SELECT COUNT(*) FROM Revisions, DocMeta, Documents, RevClasses
 systemdatabase/6 12.20 14:41:00.911 SearchCache WHERE Revisions.dID=DocMeta.dID And Revisions.dID=Documents.dID And Revisions.dDocName = RevClasses.dDocName And dIsPrimary = 1 And dReleaseState IN ('Y', 'U', 'I') AND ((Revisions.dDocName LIKE 'IDC_PN33320621' OR Revisions.dDocName LIKE 'IDC_PN33320622' OR Revisions.dDocName LIKE 'IDC_PN33320623' OR Revisions.dDocName LIKE 'IDC_PN33320624' OR Revisions.dDocName LIKE 'IDC_PN33320625' OR Revisions.dDocName LIKE 'IDC_PN33320626' OR Revisions.dDocName LIKE 'IDC_PN33320627' OR Revisions.dDocName LIKE 'IDC_PN33320628' OR Revisions.dDocName LIKE 'IDC_PN33320629' OR Revisions.dDocName LIKE 'IDC_PN33320630' OR Revisions.dDocName LIKE 'IDC_PN33320631' OR Revisions.dDocName LIKE 'IDC_PN33320632' OR Revisions.dDocName LIKE 'IDC_PN33320633' OR Revisions.dDocName LIKE 'IDC_PN33320634' OR Revisions.dDocName LIKE 'IDC_PN33320635' OR Revisions.dDocName LIKE 'IDC_PN33320636' OR Revisions.dDocName LIKE 'IDC_PN33320637' OR Revisions.dDocName LIKE 'IDC_PN33320638' OR Revisions.dDocName LIKE 'IDC_PN33320639' OR Revisions.dDocName LIKE 'IDC_PN33320640' OR Revisions.dDocName LIKE 'IDC_PN33320641' OR Revisions.dDocName LIKE 'IDC_PN33320642' OR Revisions.dDocName LIKE 'IDC_PN33320643' OR Revisions.dDocName LIKE 'IDC_PN33320644' OR Revisions.dDocName LIKE 'IDC_PN33320645' OR Revisions.dDocName LIKE 'IDC_PN33320646' OR Revisions.dDocName LIKE 'IDC_PN33320647' OR Revisions.dDocName LIKE 'IDC_PN33320648' OR Revisions.dDocName LIKE 'IDC_PN33320649' OR Revisions.dDocName LIKE 'IDC_PN33320650' OR Revisions.dDocName LIKE 'IDC_PN33320651' OR Revisions.dDocName LIKE 'IDC_PN33320652' OR Revisions.dDocName LIKE 'IDC_PN33320653' OR Revisions.dDocName LIKE 'IDC_PN33320654' OR Revisions.dDocName LIKE 'IDC_PN33320655' OR Revisions.dDocName LIKE 'IDC_PN33320656' OR Revisions.dDocName LIKE 'IDC_PN33320657' OR Revisions.dDocName LIKE 'IDC_PN33320658' OR Revisions.dDocName LIKE 'IDC_PN33320659' OR Revisions.dDocName LIKE 'IDC_PN33320661' OR Revisions.dDocName LIKE 'IDC_PN33320662' OR Revisions.dDocName LIKE 'IDC_PN33320663' OR Revisions.dDocName LIKE 'IDC_PN33320664' OR Revisions.dDocName LIKE 'IDC_PN33320665' OR Revisions.dDocName LIKE 'IDC_PN33320666' OR Revisions.dDocName LIKE 'IDC_PN33320667' OR Revisions.dDocName LIKE 'IDC_PN33320668' OR Revisions.dDocName LIKE 'IDC_PN33320669' OR Revisions.dDocName LIKE 'IDC_PN33320660' OR Revisions.dDocName LIKE 'IDC_PN33320672'))[Executed. Returned row(s): true]
>systemdatabase/6 12.20 14:41:00.911 SearchCache {[SearchCache.2823(Not In Transaction) Active time: 80722s]}
 systemdatabase/6 12.20 14:41:00.911 SearchCache {( Action executed
 systemdatabase/6 12.20 14:41:00.911 SearchCache SELECT COUNT(*) FROM Revisions, DocMeta, Documents, RevClasses
 systemdatabase/6 12.20 14:41:00.911 SearchCache WHERE Revisions.dID=DocMeta.dID And Revisions.dID=Documents.dID And Revisions.dDocName = RevClasses.dDocName And dIsPrimary = 1 And dReleaseState IN ('Y', 'U', 'I') AND ((Revisions.dDocName LIKE 'IDC_PN33320621' OR Revisions.dDocName LIKE 'IDC_PN33320622' OR Revisions.dDocName LIKE 'IDC_PN33320623' OR Revisions.dDocName LIKE 'IDC_PN33320624' OR Revisions.dDocName LIKE 'IDC_PN33320625' OR Revisions.dDocName LIKE 'IDC_PN33320626' OR Revisions.dDocName LIKE 'IDC_PN33320627' OR Revisions.dDocName LIKE 'IDC_PN33320628' OR Revisions.dDocName LIKE 'IDC_PN33320629' OR Revisions.dDocName LIKE 'IDC_PN33320630' OR Revisions.dDocName LIKE 'IDC_PN33320631' OR Revisions.dDocName LIKE 'IDC_PN33320632' OR Revisions.dDocName LIKE 'IDC_PN33320633' OR Revisions.dDocName LIKE 'IDC_PN33320634' OR Revisions.dDocName LIKE 'IDC_PN33320635' OR Revisions.dDocName LIKE 'IDC_PN33320636' OR Revisions.dDocName LIKE 'IDC_PN33320637' OR Revisions.dDocName LIKE 'IDC_PN33320638' OR Revisions.dDocName LIKE 'IDC_PN33320639' OR Revisions.dDocName LIKE 'IDC_PN33320640' OR Revisions.dDocName LIKE 'IDC_PN33320641' OR Revisions.dDocName LIKE 'IDC_PN33320642' OR Revisions.dDocName LIKE 'IDC_PN33320643' OR Revisions.dDocName LIKE 'IDC_PN33320644' OR Revisions.dDocName LIKE 'IDC_PN33320645' OR Revisions.dDocName LIKE 'IDC_PN33320646' OR Revisions.dDocName LIKE 'IDC_PN33320647' OR Revisions.dDocName LIKE 'IDC_PN33320648' OR Revisions.dDocName LIKE 'IDC_PN33320649' OR Revisions.dDocName LIKE 'IDC_PN33320650' OR Revisions.dDocName LIKE 'IDC_PN33320651' OR Revisions.dDocName LIKE 'IDC_PN33320652' OR Revisions.dDocName LIKE 'IDC_PN33320653' OR Revisions.dDocName LIKE 'IDC_PN33320654' OR Revisions.dDocName LIKE 'IDC_PN33320655' OR Revisions.dDocName LIKE 'IDC_PN33320656' OR Revisions.dDocName LIKE 'IDC_PN33320657' OR Revisions.dDocName LIKE 'IDC_PN33320658' OR Revisions.dDocName LIKE 'IDC_PN33320659' OR Revisions.dDocName LIKE 'IDC_PN33320661' OR Revisions.dDocName LIKE 'IDC_PN33320662' OR Revisions.dDocName LIKE 'IDC_PN33320663' OR Revisions.dDocName LIKE 'IDC_PN33320664' OR Revisions.dDocName LIKE 'IDC_PN33320665' OR Revisions.dDocName LIKE 'IDC_PN33320666' OR Revisions.dDocName LIKE 'IDC_PN33320667' OR Revisions.dDocName LIKE 'IDC_PN33320668' OR Revisions.dDocName LIKE 'IDC_PN33320669' OR Revisions.dDocName LIKE 'IDC_PN33320660' OR Revisions.dDocName LIKE 'IDC_PN33320672'))[Executed. Returned row(s): true] )}

 
These 2 queries take very long time to execute: each query takes over 1 minute when executed.  Additionally, the queries are executed every couple of minutes.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms