my client has unreasonably slow search results. i've already worked out some performance drags but the underlying cause seems to be the actual speed of queries from the database.
here's some console output to consider - green is the request, red is the response - about 11 seconds! can anyone suggest why this simple query would be so expensive?
searchquery 09.25 16:35:12.262 IdcServerThread-9249 Query by webmonkeymagic from cms.monkey.com.au
searchquery 09.25 16:35:12.262 IdcServerThread-9249 preparedQueryText: dDocTitle <substring> `monkey` <OR> dDocName <substring> `monkey` <OR> <ftx>monkey</ftx>
searchquery 09.25 16:35:12.263 IdcServerThread-9249 Setting substr to other operator conv flag: 'dDocTitle <substring> `monkey` <OR> dDocName <substring> `monkey` <OR> <ftx>monkey</ftx>'
searchquery 09.25 16:35:12.263 IdcServerThread-9249 Parsing universal query: 'dDocTitle <substring> `monkey` <OR> dDocName <substring> `monkey` <OR> <ftx>monkey</ftx>'
searchquery 09.25 16:35:12.264 IdcServerThread-9249 Converted native query: ' (SDATA(sddDocTitle LIKE '%monkey%')) or (SDATA(sddDocName LIKE '%monkey%')) or (DEFINESCORE((monkey), RELEVANCE * .01)) '
searchquery 09.25 16:35:12.265 IdcServerThread-9249 assigning connection
searchquery 09.25 16:35:12.265 IdcServerThread-9249 Connection is added to active connections with key of 'IdcServerThread-9249'.
searchquery 09.25 16:35:12.265 IdcServerThread-9249 Connection is removed from active connections with key of 'IdcServerThread-9249'.
searchquery 09.25 16:35:12.265 IdcServerThread-9249 Connection is added to active connections with key of 'IdcServerThread-9249'.
searchquery 09.25 16:35:12.266 IdcServerThread-9249 q# ((((((SDATA(sddDocTitle LIKE '%monkey%')) or (SDATA(sddDocName LIKE '%monkey%')) or (DEFINESCORE((monkey), RELEVANCE * .01))))))) [1,40] sort(dInDate Desc)
systemdatabase 09.25 16:35:12.267 IdcServerThread-9249 assigning connection
systemdatabase 09.25 16:35:12.267 IdcServerThread-9249 Connection with last id of IdcServerThread-9231.10 is added to active connections with key of 'IdcServerThread-9249'.
systemdatabase 09.25 16:35:12.267 IdcServerThread-9249 Assigned connection to this thread, took 0.25 ms.
systemdatabase 09.25 16:35:12.267 IdcServerThread-9249 Preparing connection for use, id initialized as IdcServerThread-9249.10
systemdatabase 09.25 16:35:12.267 IdcServerThread-9249 getColumns IdcText1
systemdatabase 09.25 16:35:12.273 IdcServerThread-9249 6.03 ms. getColumns IdcText1[Retrieved 77 columns.]
systemdatabase 09.25 16:35:12.274 IdcServerThread-9249 Executing CallableStatement ({call content_search.search_with_resultsetinterface(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})
systemdatabase 09.25 16:35:12.274 IdcServerThread-9249 Parameters: ([indexName:FT_IdcText1],[tableName:IdcText1],[queryText:(((((((SDATA(sddDocTitle LIKE '%monkey%')) or (SDATA(sddDocName LIKE '%monkey%')) or (DEFINESCORE((monkey), RELEVANCE * .01)))))))) * 10.0 * 10.0],[returnFields:dDocName,dDocTitle,dDocType,dSecurityGroup,dInDate,xRegionDefinition,xAudience,xWebsiteObjectType,xDoxRelated,xApprovedDate,xReviewDate,xOrgDivision,ALTERNATEFORMAT,dDocAuthor,xVideoRenditions,xStorageRule,xTranLanguage,xCaption,xCollectionID,xReadOnly,xDamConversionType,xDiscussionType,xDoxContacts,dOutDate,dFormat,dRendition2,dRendition1,xOrgUnit,xTagCloud,xScholarStatus,xApprovedBy,xInhibitUpdate,xPackagedConversions,xFaculties,xWikiPageName,xTagCategory,xIdcLanguage,xPromote,xOriginalAuthor,xOpenDate,xHidden,xWebsites,xStudyArea,xDontShowInListsForWebsites,dDocAccount,URL,xSubType,xMetadataDescription,dCreateDate,dID,dExtension,dWebExtension,VaultFileSize,dRevLabel,xCloseDate,xDiscussionCount,xPartitionId,xScholarCriteria,dGif,xDoxNo,dFullTextFormat,xGradLevel,xHandbookListing,xProfileTrigger,dRevisionID,xWebsiteSection,xEffectiveDate,WebFileSize,xComments,xWebFlag,xTranMaster,xScholarSource,dOriginalName,dPublishType,xTagOrg],[resultDescriptor:<ctx_result_set_descriptor><count exact="false"/><group sdata="sdDrillDown"><count exact="true"/></group><hitlist start_hit_num="1" end_hit_num="40" order="dInDate Desc">
<sdata name="dID"/><sdata name="sddDocName"/><score />
</hitlist></ctx_result_set_descriptor>],[result (output)],[metaResult (output)],[baseResult (output)],[count (output)],[qvSnippetStartTag:<span class=largeTableEntry><span class=highlightField>],[qvSnippetEndTag:</span></span>],[qvSnippetTranslation:true],[qvSnippetSeparator:<b>...</b>],[procFlags:2],[trace (output)])
systemdatabase 09.25 16:35:14.085 IdcServerThread-9250 assigning connection
systemdatabase 09.25 16:35:14.085 IdcServerThread-9250 Connection with last id of SubjectManager.11 is added to active connections with key of 'IdcServerThread-9250'.
systemdatabase 09.25 16:35:14.086 IdcServerThread-9250 Assigned connection to this thread, took 0.31 ms.
systemdatabase 09.25 16:35:14.086 IdcServerThread-9250 Preparing connection for use, id initialized as IdcServerThread-9250.11
systemdatabase 09.25 16:35:14.086 IdcServerThread-9250 SELECT DNAME from CONFIG WHERE 1=0
systemdatabase 09.25 16:35:14.088 IdcServerThread-9250 1.57 ms. SELECT DNAME from CONFIG WHERE 1=0[Executed. Returned row(s): false]
systemdatabase 09.25 16:35:14.103 IdcServerThread-9250 Connection with id of 'IdcServerThread-9250.11' is removed from active connections with key of 'IdcServerThread-9250'.
systemdatabase 09.25 16:35:14.103 IdcServerThread-9250 release pool connection systemdatabase 09.25 16:35:23.160 IdcServerThread-9249 10885 ms. Executing CallableStatement ({call content_search.search_with_resultsetinterface(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)})[Executed. First result is ResultSet:false]
searchquery 09.25 16:35:23.277 IdcServerThread-9249 Connection is removed from active connections with key of 'IdcServerThread-9249'.
searchquery 09.25 16:35:23.277 IdcServerThread-9249 release pool connection
searchquery 09.25 16:35:23.284 IdcServerThread-9249 Execution time is 11022 milliseconds
systemdatabase 09.25 16:35:23.284 IdcServerThread-9249 Connection with id of 'IdcServerThread-9249.10' is removed from active connections with key of 'IdcServerThread-9249'.
systemdatabase 09.25 16:35:23.284 IdcServerThread-9249 release pool connection
systemdatabase 09.25 16:35:23.352 IdcServerThread-9249 assigning connection
systemdatabase 09.25 16:35:23.353 IdcServerThread-9249 Connection with last id of Refinery QueryRefineryJobs.2 is added to active connections with key of 'IdcServerThread-9249'.
systemdatabase 09.25 16:35:23.353 IdcServerThread-9249 Assigned connection to this thread, took 0.32 ms.
systemdatabase 09.25 16:35:23.353 IdcServerThread-9249 Preparing connection for use, id initialized as IdcServerThread-9249.2
systemdatabase 09.25 16:35:23.353 IdcServerThread-9249 SELECT DNAME from CONFIG WHERE 1=0
systemdatabase 09.25 16:35:23.355 IdcServerThread-9249 1.71 ms. SELECT DNAME from CONFIG WHERE 1=0[Executed. Returned row(s): false]
systemdatabase 09.25 16:35:23.519 IdcServerThread-9249 SELECT/*+ USE_NL (Revisions DocMeta Documents) LEADING(Revisions)INDEX (Revisions dDocName)*/ DocMeta.*, Documents.*, Revisions.*
FROM DocMeta, Documents, Revisions
WHERE DocMeta.dID = Revisions.dID AND Revisions.dID=Documents.dID
AND Revisions.dDocName='DEV108451' AND Revisions.dStatus<>'DELETED' AND
(Revisions.dReleaseState='U' OR Revisions.dReleaseState='I' OR Revisions.dReleaseState='Y')
AND Documents.dIsPrimary<>0
systemdatabase 09.25 16:35:23.532 IdcServerThread-9249 12 ms. SELECT/*+ USE_NL (Revisions DocMeta Documents) LEADING(Revisions)INDEX (Revisions dDocName)*/ DocMeta.*, Documents.*, Revisions.*
FROM DocMeta, Documents, Revisions
WHERE DocMeta.dID = Revisions.dID AND Revisions.dID=Documents.dID
AND Revisions.dDocName='DEV108451' AND Revisions.dStatus<>'DELETED' AND
(Revisions.dReleaseState='U' OR Revisions.dReleaseState='I' OR Revisions.dReleaseState='Y')
AND Documents.dIsPrimary<>0[Executed. Returned row(s): true]
systemdatabase 09.25 16:35:23.559 IdcServerThread-9249 SELECT/*+ USE_NL (Revisions DocMeta Documents) LEADING(Revisions)INDEX (Revisions dDocName)*/ DocMeta.*, Documents.*, Revisions.*
FROM DocMeta, Documents, Revisions
WHERE DocMeta.dID = Revisions.dID AND Revisions.dID=Documents.dID
AND Revisions.dDocName='DEV108452' AND Revisions.dStatus<>'DELETED' AND
(Revisions.dReleaseState='U' OR Revisions.dReleaseState='I' OR Revisions.dReleaseState='Y')
AND Documents.dIsPrimary<>0
systemdatabase 09.25 16:35:23.566 IdcServerThread-9249 6.07 ms. SELECT/*+ USE_NL (Revisions DocMeta Documents) LEADING(Revisions)INDEX (Revisions dDocName)*/ DocMeta.*, Documents.*, Revisions.*
FROM DocMeta, Documents, Revisions
WHERE DocMeta.dID = Revisions.dID AND Revisions.dID=Documents.dID
AND Revisions.dDocName='DEV108452' AND Revisions.dStatus<>'DELETED' AND
(Revisions.dReleaseState='U' OR Revisions.dReleaseState='I' OR Revisions.dReleaseState='Y')
AND Documents.dIsPrimary<>0[Executed. Returned row(s): true]
systemdatabase 09.25 16:35:23.612 IdcServerThread-9249 Connection with id of 'IdcServerThread-9249.2' is removed from active connections with key of 'IdcServerThread-9249'.
systemdatabase 09.25 16:35:23.612 IdcServerThread-9249 release pool connection
Search Engine::ORACLETEXTSEARCH
Jdbc Driver Version::11.2.0.3.0
UCM version::10.1.3.5.1 (110524)