cancel
Showing results for 
Search instead for 
Did you mean: 

Record Number Search extremely slow

Highlighted
Jamie_1
Regular Collector

Record Number Search extremely slow

We undertook a re-index of our database over the weekend and ever since any searching based on record numbers has been extremely slow (well over 1 minute when previously under 5 sec's).  Searching on words or an expanded number search works fine, only searches based on the record number are affected.  Does anyone have an idea on possible issues or solutions.

 

Thanks

5 REPLIES
EWillsey
Honored Contributor

Re: Record Number Search extremely slow

Just to double-check, what specifically did you do when you "re-index"?

 

If you used the TRIM Enterprise Studio's Dataset->Utilities->Reindex features, that doesn't change anything to do with record numbers.  If you used the Dataset->Schema->Repair feature to fix/rebuild the indexes then maybe the indexes got messed up on the database side.

Jamie_1
Regular Collector

Re: Record Number Search extremely slow

Erik,

Thanks for the quick reply.  The re-index was undertaken from TRIM Enterprise Studio, no Schema Repair has been undertaken, yet.  I beleive the process undertkane was to turn off Refernetial Integrity (using Oracle), run the re-index and then turn Referential Integiry back on.  I beleive the administrator also ran an Integrity Check.  Is it worthwhile runnining a schema repair?

 

BTW we are using V6.1.5.6

 

Thanks

Jamie

Grundy
Honored Contributor

Re: Record Number Search extremely slow

Sounds like a database specific issue.

Record number searches do not rely so much on a TRIM created index.

 

I suggest the Oracle DBA ensure full statistics are being generated on a nightly basis and he can look at recreating any indexes in Oracle itself.

It's also possible that the database server is just underspecced and a reboot has occurred recently that has wiped any 'cache' in Oracle that previously stored common searches (such as testing a record number search range).

 

Hope this gives you a few ideas. :smileyhappy:



::::::::::::::::::::::
NOT A HP EMPLOYEE
::::::::::::::::::::::

INFORMOTION.com.au
Strider_1
Member

Re: Record Number Search extremely slow

Yep, sounds like what happened to us. Date searching was a bit slow also. For us it was caused by a missing index on TSRecord.

 

Good luck !

Sander Hoogwerf
Honored Contributor

Re: Record Number Search extremely slow

We sometimes experience a problem that the indexes are all there with proper statistics, but that the state of the index is UNUSABLE . Then we need to rebuild the index which is best done in out of office hours because rebuilding when users are active may result in 'object in use errors' . But sometimes we shutdown TRIM during daytime and fix the index issues so that users can at least have proper performance the rest of the day.

 

Usually these unusable indexes are caused by a buggy overactive Oracle Resource Manager, killing long running jobs (especially multi hour reindexing tasks).


(Any opinions expressed in this forum are my own personal opinion and should not be construed as an official statement by DXC Technology.)

Analytics & Data Management
Application & Business Services
DXC Technology
//Add this to "OnDomLoad" event