This is an archive of the Maximo Yahoo Community. The content of this pages may be a sometimes obsolete so please check post dates.
Thanks to the community owner Christopher Wanko for providing the content.
Matt,
Did you ever get a resolution to this issue? If so, what was it? We're in the process to moving to 7.1.1.7 (SQL Server), just loaded our documents (300k+ records), and now are experiencing the same type of issue. Deconstructs show that the queries run instantaneously on the server with respect to doclinks. When run in Maximo, delays are several seconds to over a minute. The product is almost unuseable in this condition. Curious if anyone else is having this issue...
Tim
--- In MAXIMO@yahoogroups.com, "Mary" <mmangier@...> wrote:
>
> I had this problem in 6 and changed the doclinks relationship on the affected tables. IBM has some documentation on this for 6 and 7.
>
> --- In MAXIMO@yahoogroups.com, Matt Holland <matthewgholland@> wrote:
> >
> > I have also checked the work types associated with the fields. I understand
> > this can impact how Maximo shapes the queries/statements it executes against
> > the database.
> >
> >
> >
> > On Mon, Sep 20, 2010 at 6:23 PM, Matt Holland <matthewgholland@>wrote:
> >
> > > The query is being executed internally by Maximo when trying to create a
> > > new Work Order. If the doclinks table has no records then the Work Order
> > > record opens faster. With the 200k+ attachments it can sometimes take over 1
> > > minute just to create a new WO record. I have compared out of the box search
> > > types and they appear to match for the doclinks table. They also match for
> > > work order.
> > >
> > > See below for the Oracle query being executed:
> > >
> > > select * from doclinks where (ownertable=:"SYS_B_00" and ownerid= :
> > > "SYS_B_01" ) or (ownertable=:"SYS_B_02" and ownerid in (selectworkorderid
> > > from workorder where parent= :"SYS_B_03" and istask=:"SYS_B_04" and siteid
> > > = :"SYS_B_05" )) or (ownertable=:"SYS_B_06" andownerid in (select assetuid
> > > from asset where assetnum= :"SYS_B_07" and siteid= :"SYS_B_08" )) or (
> > > ownertable=:"SYS_B_09" and ownerid in(select locationsid from locations
> > > where location= :"SYS_B_10" and siteid= :"SYS_B_11" )) or (ownertable=:
> > > "SYS_B_12" and ownerid in(select jobplanid from jobplan where jpnum= :
> > > "SYS_B_13" and (siteid is null or siteid= :"SYS_B_14" ))) or (ownertable=:
> > > "SYS_B_15" andownerid in (select pmuid from pm where pmnum= :"SYS_B_16"
> > > and siteid= :"SYS_B_17" )) or (ownertable=:"SYS_B_18" and ownerid in (
> > > selectsafetyplanuid from safetyplan,wosafetyplan where safetyplan.
> > > safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum= :"SYS_B_19"
> > > and wosafetyplan.siteid= :"SYS_B_20" )) or (ownertable in (:"SYS_B_21",:
> > > "SYS_B_22",:"SYS_B_23") and ownerid in (select ticketuid fromticket,
> > > relatedrecord where ticketid=recordkey and ticket.class = relatedrecord.
> > > class and relatedrecclass= :"SYS_B_24" andrelatedreckey= :"SYS_B_25" and
> > > relatedrecsiteid= :"SYS_B_26" )) or (ownertable in (:"SYS_B_27",:
> > > "SYS_B_28",:"SYS_B_29") and ownerid in(select workorderid from workorder,
> > > relatedrecord where wonum=recordkey and workorder.woclass = relatedrecord.
> > > class and relatedrecclass=:"SYS_B_30" and relatedreckey= :"SYS_B_31" and
> > > relatedrecsiteid= :"SYS_B_32" )) or (ownertable=:"SYS_B_33" and ownerid in
> > > (selectcommloguid from commlog where ownerid= :"SYS_B_34" and ownertable=
> > > :"SYS_B_35")) or (ownertable=:"SYS_B_36" and ownerid in (select slaidfrom
> > > sla,slarecords,workorder where sla.slanum=slarecords.slanum and slarecords
> > > .ownerid=workorder.workorderid andsla.objectname=:"SYS_B_37" and
> > > slarecords.ownertable=:"SYS_B_38" and workorder.wonum= :"SYS_B_39" ))
> > >
> > >
> > >
> > > On Fri, Sep 17, 2010 at 4:41 PM, Pat Morrow <pmorrow8@> wrote:
> > >
> > >>
> > >>
> > >> Look at the queries that are being executed. If they include
> > >> to_upper(fieldname) in them, the indexes cannot be used. Or if they have a
> > >> 'like' statement and the comparator has a '%' symbol as the first character,
> > >> the index will not be able to be used.
> > >> eg. wonum like '%243%' - no index would be used
> > >> wonum like '243%' - index would be used
> > >>
> > >> If the above seem to be the case - have the system admin change the type
> > >> or search on these fields to 'TEXT'.
> > >>
> > >> Take a good LOOK at the APPLICATION code (decompile the java) and
> > >> queries. I have found that the databases are usually tuned and running
> > >> fine, but the application is doing something that turns off the indexes.
> > >>
> > >> Pat Morrow
> > >> pmorrow8@ <pmorrow8%40yahoo.com>
> > >>
> > >> --- On Fri, 9/17/10, Matt Holland <matthewgholland@<matthewgholland%40gmail.com>>
> > >> wrote:
> > >>
> > >> From: Matt Holland <matthewgholland@<matthewgholland%40gmail.com>
> > >> >
> > >> Subject: Re: [MAXIMO List] Is there anything I check to assist with slow
> > >> work order application?
> > >> To: "MAXIMO" <MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com>>
> > >> Date: Friday, September 17, 2010, 9:28 AM
> > >>
> > >>
> > >> I managed to identify the source. Its an Oracle 10g database and using a
> > >> performance monitor to look for the top consumers I found that the query
> > >> being executed was hitting tables with indexes but they weren't being
> > >> used.
> > >> Instead a full table scan was being used instead of the indexes. The
> > >> tables
> > >> were DOCLINKS,WORKORDER, and WOSAFETYPLAN. I have asked the Oracle DBAs to
> > >> investigate but in the meantime I am wondering if there is anything else I
> > >> can look into. All of the indexes exist on these tables and are defined
> > >> exactly as they are in an out of the box Maximo 7.1.1.6 system.
> > >>
> > >> On Thu, Sep 16, 2010 at 8:27 AM, Hanna, Christopher CTR <
> > >> christopher.a.hanna@ <christopher.a.hanna%40uscg.mil>> wrote:
> > >>
> > >> >
> > >> >
> > >> > In 6.2.2, there is a setting in maximo.properties called
> > >> > mxe.db.logSQLTimeLimit. I would assume that 7x has a similar setting.
> > >> This
> > >> > sets a threshold for logging any SQL that takes longer then x
> > >> milliseconds
> > >> > to execute. Try setting it to something like 5000 (5 seconds) and see if
> > >> > that will identify the poor performing SQL. Then you (or your DBA) can
> > >> look
> > >> > at an explain plan and tune appropriately.
> > >> >
> > >> > -----Original Message-----
> > >> > From: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > >> 40yahoogroups.com> [mailto:
> > >> > MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > >> 40yahoogroups.com>] On Behalf Of
> > >> > matthewgholland
> > >> > Sent: Wednesday, September 15, 2010 1:04 PM
> > >> > To: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > >> 40yahoogroups.com>
> > >> > Subject: [MAXIMO List] Is there anything I check to assist with slow
> > >> work
> > >> > order application?
> > >> >
> > >> > System specs:
> > >> > Windows Server 2003 / WebSphere 6.1 / Maximo 7.1.1.6 / Oracle 10g
> > >> >
> > >> > Problem:
> > >> > When trying to access a work order I am able to quickly access the
> > >> > application and without to much delay I am able to display a result set
> > >> > listing all the work orders. The problem is with creating a work order,
> > >> or
> > >> > opening a workorder. It can sometimes take up to 30 seconds to open 1
> > >> work
> > >> > order.
> > >> >
> > >> > Supporting Details:
> > >> > There are approx. 591k records in the workorder table. All of the out of
> > >> > the box indexes are applied. Indexes have been rebuilt and the
> > >> statistics
> > >> > have been refreshed through database configuration. The workorder table
> > >> has
> > >> > 231 persistent fields.
> > >> >
> > >> > Anything else I can check to see why this may be taking so long?
> > >> >
> > >> >
> > >> >
> > >>
> > >> --
> > >> Thanks,
> > >>
> > >> Matt Holland
> > >>
> > >>
> > >>
> > >> ------------------------------------
> > >>
> > >>
> > >> Email addresses you might need:
> > >> Posting: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com>
> > >> Join : MAXIMO-subscribe@yahoogroups.com<MAXIMO-subscribe%40yahoogroups.com>
> > >> Leave : MAXIMO-unsubscribe@yahoogroups.com<MAXIMO-unsubscribe%40yahoogroups.com>
> > >> Cry : MAXIMO-owner@yahoogroups.com <MAXIMO-owner%40yahoogroups.com>
> > >> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
> > >>
> > >>
> > >>
> > >>
> > >>
> > >
> > >
> > >
> > > --
> > > Thanks,
> > >
> > > Matt Holland
> > >
> > >
> >
> >
> > --
> > Thanks,
> >
> > Matt Holland
> >
> >
> >
> >
>
My guess is you have many records in the DOCLINKS table. When you open Work
Order tracking it has to go out and look for many types of attachments that
may be tied back to that work order via Work Order Parent, Location, Asset,
etc.... If you change the system property mxe.db.logSQLTimeLimit to 1 and
then do a live refresh. You will see in the application server log every SQL
statement being generated by the Maximo system. Copy the SQL statement where
the select is ran against DOCLINKS and run this same statement in SQL Server
Management Studio. You will notice it takes quite a bit of time to execute.
This is because the indexes are not being used and it is doing a full table
scan. You will need to modify the DOCLINKS relationship for the WORKORDER
object to function more effectively. By changing this relationship the Work
Order application navigates in a couple of seconds compared to the several
minutes it was taking previously. I would start with the relationship where
clause.
You can also modify the webclient.properties file there is a property
called webclient.enabledoclinkonload . Setting it to true will disable
searching for attachments on any record until you try to view the
attachments. Where you will see performance gains it's not very reliable.
When you go to open the attachments it still has to go back and do the full
table scans and all of the records will appear to have attachments until you
try to actually view what those attachments are.
Those are a couple of ways to aid with this issue, the solid fix is in the
relationship. Let me know if this has helped any. I have emailed the
relationship I used directly to you. It should help get you in the right
direction.
On Sat, Jul 2, 2011 at 9:29 AM, <tkuzmicky@ussposco.com> wrote:
> **
>
>
> Matt,
>
> Did you ever get a resolution to this issue? If so, what was it? We're in
> the process to moving to 7.1.1.7 (SQL Server), just loaded our documents
> (300k+ records), and now are experiencing the same type of issue.
> Deconstructs show that the queries run instantaneously on the server with
> respect to doclinks. When run in Maximo, delays are several seconds to over
> a minute. The product is almost unuseable in this condition. Curious if
> anyone else is having this issue...
>
> Tim
>
>
> --- In MAXIMO@yahoogroups.com, "Mary" <mmangier@...> wrote:
> >
> > I had this problem in 6 and changed the doclinks relationship on the
> affected tables. IBM has some documentation on this for 6 and 7.
> >
> > --- In MAXIMO@yahoogroups.com, Matt Holland <matthewgholland@> wrote:
> > >
> > > I have also checked the work types associated with the fields. I
> understand
> > > this can impact how Maximo shapes the queries/statements it executes
> against
> > > the database.
> > >
> > >
> > >
> > > On Mon, Sep 20, 2010 at 6:23 PM, Matt Holland <matthewgholland@>wrote:
> > >
> > > > The query is being executed internally by Maximo when trying to
> create a
> > > > new Work Order. If the doclinks table has no records then the Work
> Order
> > > > record opens faster. With the 200k+ attachments it can sometimes take
> over 1
> > > > minute just to create a new WO record. I have compared out of the box
> search
> > > > types and they appear to match for the doclinks table. They also
> match for
> > > > work order.
> > > >
> > > > See below for the Oracle query being executed:
> > > >
> > > > select * from doclinks where (ownertable=:"SYS_B_00" and ownerid= :
> > > > "SYS_B_01" ) or (ownertable=:"SYS_B_02" and ownerid in
> (selectworkorderid
> > > > from workorder where parent= :"SYS_B_03" and istask=:"SYS_B_04" and
> siteid
> > > > = :"SYS_B_05" )) or (ownertable=:"SYS_B_06" andownerid in (select
> assetuid
> > > > from asset where assetnum= :"SYS_B_07" and siteid= :"SYS_B_08" )) or
> (
> > > > ownertable=:"SYS_B_09" and ownerid in(select locationsid from
> locations
> > > > where location= :"SYS_B_10" and siteid= :"SYS_B_11" )) or
> (ownertable=:
> > > > "SYS_B_12" and ownerid in(select jobplanid from jobplan where jpnum=
> :
> > > > "SYS_B_13" and (siteid is null or siteid= :"SYS_B_14" ))) or
> (ownertable=:
> > > > "SYS_B_15" andownerid in (select pmuid from pm where pmnum=
> :"SYS_B_16"
> > > > and siteid= :"SYS_B_17" )) or (ownertable=:"SYS_B_18" and ownerid in
> (
> > > > selectsafetyplanuid from safetyplan,wosafetyplan where safetyplan.
> > > > safetyplanid=wosafetyplan.safetyplanid and wosafetyplan.wonum=
> :"SYS_B_19"
> > > > and wosafetyplan.siteid= :"SYS_B_20" )) or (ownertable in
> (:"SYS_B_21",:
> > > > "SYS_B_22",:"SYS_B_23") and ownerid in (select ticketuid fromticket,
> > > > relatedrecord where ticketid=recordkey and ticket.class =
> relatedrecord.
> > > > class and relatedrecclass= :"SYS_B_24" andrelatedreckey= :"SYS_B_25"
> and
> > > > relatedrecsiteid= :"SYS_B_26" )) or (ownertable in (:"SYS_B_27",:
> > > > "SYS_B_28",:"SYS_B_29") and ownerid in(select workorderid from
> workorder,
> > > > relatedrecord where wonum=recordkey and workorder.woclass =
> relatedrecord.
> > > > class and relatedrecclass=:"SYS_B_30" and relatedreckey= :"SYS_B_31"
> and
> > > > relatedrecsiteid= :"SYS_B_32" )) or (ownertable=:"SYS_B_33" and
> ownerid in
> > > > (selectcommloguid from commlog where ownerid= :"SYS_B_34" and
> ownertable=
> > > > :"SYS_B_35")) or (ownertable=:"SYS_B_36" and ownerid in (select
> slaidfrom
> > > > sla,slarecords,workorder where sla.slanum=slarecords.slanum and
> slarecords
> > > > .ownerid=workorder.workorderid andsla.objectname=:"SYS_B_37" and
> > > > slarecords.ownertable=:"SYS_B_38" and workorder.wonum= :"SYS_B_39" ))
> > > >
> > > >
> > > >
> > > > On Fri, Sep 17, 2010 at 4:41 PM, Pat Morrow <pmorrow8@> wrote:
> > > >
> > > >>
> > > >>
> > > >> Look at the queries that are being executed. If they include
> > > >> to_upper(fieldname) in them, the indexes cannot be used. Or if they
> have a
> > > >> 'like' statement and the comparator has a '%' symbol as the first
> character,
> > > >> the index will not be able to be used.
> > > >> eg. wonum like '%243%' - no index would be used
> > > >> wonum like '243%' - index would be used
> > > >>
> > > >> If the above seem to be the case - have the system admin change the
> type
> > > >> or search on these fields to 'TEXT'.
> > > >>
> > > >> Take a good LOOK at the APPLICATION code (decompile the java) and
> > > >> queries. I have found that the databases are usually tuned and
> running
> > > >> fine, but the application is doing something that turns off the
> indexes.
> > > >>
> > > >> Pat Morrow
> > > >> pmorrow8@ <pmorrow8%40yahoo.com>
> > > >>
> > > >> --- On Fri, 9/17/10, Matt Holland <matthewgholland@
> <matthewgholland%40gmail.com>>
> > > >> wrote:
> > > >>
> > > >> From: Matt Holland <matthewgholland@<matthewgholland%40gmail.com>
> > > >> >
> > > >> Subject: Re: [MAXIMO List] Is there anything I check to assist with
> slow
> > > >> work order application?
> > > >> To: "MAXIMO" <MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com>>
> > > >> Date: Friday, September 17, 2010, 9:28 AM
> > > >>
> > > >>
> > > >> I managed to identify the source. Its an Oracle 10g database and
> using a
> > > >> performance monitor to look for the top consumers I found that the
> query
> > > >> being executed was hitting tables with indexes but they weren't
> being
> > > >> used.
> > > >> Instead a full table scan was being used instead of the indexes. The
> > > >> tables
> > > >> were DOCLINKS,WORKORDER, and WOSAFETYPLAN. I have asked the Oracle
> DBAs to
> > > >> investigate but in the meantime I am wondering if there is anything
> else I
> > > >> can look into. All of the indexes exist on these tables and are
> defined
> > > >> exactly as they are in an out of the box Maximo 7.1.1.6 system.
> > > >>
> > > >> On Thu, Sep 16, 2010 at 8:27 AM, Hanna, Christopher CTR <
> > > >> christopher.a.hanna@ <christopher.a.hanna%40uscg.mil>> wrote:
> > > >>
> > > >> >
> > > >> >
> > > >> > In 6.2.2, there is a setting in maximo.properties called
> > > >> > mxe.db.logSQLTimeLimit. I would assume that 7x has a similar
> setting.
> > > >> This
> > > >> > sets a threshold for logging any SQL that takes longer then x
> > > >> milliseconds
> > > >> > to execute. Try setting it to something like 5000 (5 seconds) and
> see if
> > > >> > that will identify the poor performing SQL. Then you (or your DBA)
> can
> > > >> look
> > > >> > at an explain plan and tune appropriately.
> > > >> >
> > > >> > -----Original Message-----
> > > >> > From: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > > >> 40yahoogroups.com> [mailto:
> > > >> > MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > > >> 40yahoogroups.com>] On Behalf Of
> > > >> > matthewgholland
> > > >> > Sent: Wednesday, September 15, 2010 1:04 PM
> > > >> > To: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com> <MAXIMO%
> > > >> 40yahoogroups.com>
> > > >> > Subject: [MAXIMO List] Is there anything I check to assist with
> slow
> > > >> work
> > > >> > order application?
> > > >> >
> > > >> > System specs:
> > > >> > Windows Server 2003 / WebSphere 6.1 / Maximo 7.1.1.6 / Oracle 10g
> > > >> >
> > > >> > Problem:
> > > >> > When trying to access a work order I am able to quickly access the
> > > >> > application and without to much delay I am able to display a
> result set
> > > >> > listing all the work orders. The problem is with creating a work
> order,
> > > >> or
> > > >> > opening a workorder. It can sometimes take up to 30 seconds to
> open 1
> > > >> work
> > > >> > order.
> > > >> >
> > > >> > Supporting Details:
> > > >> > There are approx. 591k records in the workorder table. All of the
> out of
> > > >> > the box indexes are applied. Indexes have been rebuilt and the
> > > >> statistics
> > > >> > have been refreshed through database configuration. The workorder
> table
> > > >> has
> > > >> > 231 persistent fields.
> > > >> >
> > > >> > Anything else I can check to see why this may be taking so long?
> > > >> >
> > > >> >
> > > >> >
> > > >>
> > > >> --
> > > >> Thanks,
> > > >>
> > > >> Matt Holland
> > > >>
> > > >>
> > > >>
> > > >> ------------------------------------
> > > >>
> > > >>
> > > >> Email addresses you might need:
> > > >> Posting: MAXIMO@yahoogroups.com <MAXIMO%40yahoogroups.com>
> > > >> Join : MAXIMO-subscribe@yahoogroups.com<MAXIMO-subscribe%
> 40yahoogroups.com>
> > > >> Leave : MAXIMO-unsubscribe@yahoogroups.com<MAXIMO-unsubscribe%
> 40yahoogroups.com>
> > > >> Cry : MAXIMO-owner@yahoogroups.com <MAXIMO-owner%40yahoogroups.com>
> > > >> Group : http://groups.yahoo.com/group/MAXIMOYahoo! Groups Links
> > > >>
> > > >>
> > > >>
> > > >>
> > > >>
> > > >
> > > >
> > > >
> > > > --
> > > > Thanks,
> > > >
> > > > Matt Holland
> > > >
> > > >
> > >
> > >
> > > --
> > > Thanks,
> > >
> > > Matt Holland
> > >
> > >
> > >
> > >
> >
>
>
>
--
Thanks,
Matt Holland