SearchCache Is Running Expensive Queries At A Regular Interval, Causing Extra Load On The Database
Last updated on FEBRUARY 02, 2018
Applies to:
Oracle WebCenter Content - Version 11.1.1.4.0 and laterInformation 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] )}
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