Established Member.. matt_hunter
Established Member..
1211 views

TRIM performance monitor counters

Hi All,

 

Doing some work with my IT guys on TRIM performance, we are looking to run the TRIM performance counters workgroup/event/client.

 

Is there an expected/ideal time that process should take?

 

On the client side when we search for records it takes quite a while (at least 15-20 seconds) to search for simple searches - when the results appear - it wont let you scroll down (when it does you can scroll for the first 40 records then wait another 20 seconds to scroll to the next 40 records).

 

We run 6.2.2 with Oracle 10g

 

Thanks

 

Matt

0 Likes
11 Replies
Erik Willsey Absent Member.
Absent Member.

Re: TRIM performance monitor counters

In most places a search result comes back in a second or so.... depending on the complexity of the search.  I know there are some tech articles out there for Oracle and 6R2.  Did you search SSO?

0 Likes
Established Member.. matt_hunter
Established Member..

Re: TRIM performance monitor counters

Had a look through - cant find much on it.  The issue seems to come and go after we have restarted the TRIM services.  Its a weird one.

 

My guys in IT dont seem to think its an Oracle problem but all i read is the databse is usually the cause!

 

0 Likes
Established Member.. matt_hunter
Established Member..

Re: TRIM performance monitor counters

Thanks Erik,

 

My system admin has noticed the following counter "Content Index transaction time millisecs/sec" from the TRIMEvent object has grown from 2000 to 6000 in about 30 minutes.

 

Do you think this is a cause or a result?  What does this counter mean?

 

I will send that document to the Oracle admin for a look.

0 Likes
Grundy Acclaimed Contributor.
Acclaimed Contributor.

Re: TRIM performance monitor counters

If you are doing 'Document Content' searches, these query the ISYS index, which is NOT in the database.

DCI searches like this will always perform slower than an equivalent text search against record titles or notes as it is a 2 part process where it grabs a result set from the ISYS database and then uses these record uris to form a database query against your main TRIM database.

 

As an example of some performance targets, any single word TITLE search should be returning in 1-10 seconds, preferably in the 1-5 second range.

Even then, this is heavily dependent on your data and how many results you expect to return, so you need to do performance testing for certain result sizes.

e.g.

Time to return results for a single word search where 1000 results are returned.

Time to return results for a single word search where 10000 results are returned.

Time to return results for a 3 word title search where 100 results are returned.

 

And then we still need to know how many records are in your database and how well your noise words are setup.

e.g. How many rows in your TSRECORD, TSINDEXWOR and TSRECWORD tables?

etc etc...

 

Without knowing the data, saying it took '30 seconds' to run a search is meaningless.

 

For all we know, you could be a company like 'Coke' and searching for the word 'coke' will return 5 million records or more! 

If you just say "We run a single word search and it takes 5 minutes!", it could be for the reason above and the performance may actually be very good for the result size. 

 

 

If you want to get into more in-depth performance tuning and get a better idea of search result performance, you should probably employ HP Professional Services or an experienced TRIM consultant to give your system a thorough health check.  🙂

 

 

PS:   We've got some extremely large customers that are happy to run combined DCI/metadata searches that take 24 hours to run, but they also have a reasinable expectation of the result size and dataset size!



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

INFORMOTION.com.au
0 Likes
Established Member.. matt_hunter
Established Member..

Re: TRIM performance monitor counters

Thanks for this Grundy very useful.

 

In simple terms it is taking us 40 - 50 secs to return results for a date registered search which has 178 records for that day.

 

Seems to be a bad execution plan in Oracle - my DBA is looking into it now.

 

Thanks for your info hopefully we can get it resolved shortly.

 

Matt Hunter

0 Likes
Grundy Acclaimed Contributor.
Acclaimed Contributor.

Re: TRIM performance monitor counters

Make sure you are running 100% stats on a daily basis.

 

A search that small and simple should be returning in a couple of seconds at most.



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

INFORMOTION.com.au
0 Likes
Established Member.. matt_hunter
Established Member..

Re: TRIM performance monitor counters

Thanks Grundy.

 

Ive relayed to the DBA waiting on response for testing.

 

As soon as i saw 24547 RM i knew something was up.

 

AddManyExtraValues seems to be the cause of our issue

 

00:07:59   24547 RM pmc4705  StoredProcedureCall: AddManyExtraValues[6],170987555,170987556,170987557,170987558,170987559,170987560,170987561,170987562,170987563,170987564,170987565,170987566)
00:07:59      16 RM pmc4705  StoredProcedureCall: ConstructManyLocations[] (18485005,26891099)
00:07:59       0 RM pmc4705  StoredProcedureCall: AddManyNotes[] (78,18485005,26891099)
00:07:59      16 RM pmc4705  StoredProcedureCall: AddManyAccessControls[] (9,9,18485005,26891099)
00:07:59      31 RM pmc4705  StoredProcedureCall: AddManyExtraValues[] (9,18485005,26891099)
00:07:59      15 RM pmc4705  GetRecordset(main)[2 4294967295 100 Records - Date Registered is on '31/10/2012'] SELECT  uri  FROM TSRECORD  WHERE (URI IN (SELECT  A0.uri  FROM  TSRECORD A0  WHERE ( regDateTime  BETWEEN  '20121030130000' AND  '201210311259591' AND  A0.uri > 0))) AND  ( TSRECORD.rcSecLevel <= 89  AND  MOD(223092870,TSRECORD.rc1CaveatKey)  = 0  AND  MOD(223092870,TSRECORD.rc2CaveatKey)  = 0  AND  MOD(6,TSRECORD.rc3CaveatKey)  = 0  AND TSRECORD.rc4CaveatKey = 1  AND TSRECORD.rc5CaveatKey = 1  )
00:07:59      47 RM pmc4705  GetRecordset(main)[2 4294967295 100 Records - Date Registered is on '31/10/2012'] SELECT COUNT(uri)  FROM TSRECORD  WHERE (URI IN (SELECT  A0.uri  FROM  TSRECORD A0  WHERE ( regDateTime  BETWEEN  '20121030130000' AND  '201210311259591' AND  A0.uri > 0))) AND  ( TSRECORD.rcSecLevel <= 89  AND  MOD(223092870,TSRECORD.rc1CaveatKey)  = 0  AND  MOD(223092870,TSRECORD.rc2CaveatKey)  = 0  AND  MOD(6,TSRECORD.rc3CaveatKey)  = 0  AND TSRECORD.rc4CaveatKey = 1  AND TSRECORD.rc5CaveatKey = 1  )

0 Likes
Grundy Acclaimed Contributor.
Acclaimed Contributor.

Re: TRIM performance monitor counters

It's a pretty core stored procedure, shouldn't be taking 24 seconds to run!

 

I would say there's some maintenance/tuning needed on the database.



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

INFORMOTION.com.au
0 Likes
Established Member.. matt_hunter
Established Member..

Re: TRIM performance monitor counters

Just an update on this one - We have been testing in our DEV & Test environments - we have narrowed the solution down to a bad execution plan by Oracle within AddManyExtraValues stored procedure and is using the plan to execute a full table scan when returning searches according to the DBA!

 

Thanks for all the help people have provided with this!

 

 

0 Likes
Grundy Acclaimed Contributor.
Acclaimed Contributor.

Re: TRIM performance monitor counters

If Oracle is doing full table scans, you might need to adjust the index cost adjustment from 100 to 10.

I think it's the optimizer_index_cost_adj setting.

 

 



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

INFORMOTION.com.au
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.