Hi,
This will add load to your system mainly when you do not have other
qualification criteria that narrows down the search, and makes good use of
your indexes.
If you do an unqualified search, the C112-additions to the SELECT-statment
can definitely add a lot of load to the query.
Best Regards - Misi, RRR AB, http://www.rrr.se
Products from RRR Scandinavia (Best R.O.I. Award at WWRUG10):
* RRR|License - Not enough Remedy licenses? Save money by optimizing.
* RRR|Log - Performance issues or elusive bugs? Analyze your Remedy logs.
Find these products, and many free tools and utilities, at http://rrr.se.
> Interesting that you say ignore C112 clauses.
> My Overview Console search adds C112 LIKE clauses.
>
> SELECT
> T943.C1,C1000000000,C7,C1000000164,C179,C230000009,C301626500,C1000000161,C1000000218,C3,C1000000019,C1000000018
> FROM T943 WHERE (((T943.C112 LIKE N'%;''user'';%') OR (T943.C112 LIKE
> N'%;0;%') OR (T943.C112 LIKE N'%;-1098;%') OR (T943.C112 LIKE
> N'%;-20000;%') OR (T943.C112 LIKE N'%;-20026;%') OR (T943.C112 LIKE
> N'%;803;%') OR (T943.C112 LIKE N'%;804;%') OR (T943.C112 LIKE N'%;
> 20061;%') OR (T943.C112 LIKE N'%;20313;%') OR (T943.C112 LIKE N'%;
> 20316;%') OR (T943.C112 LIKE N'%;13006;%') OR (T943.C112 LIKE N'%;
> 20055;%') OR (T943.C112 LIKE N'%;20211;%') OR (T943.C112 LIKE N'%;
> 20221;%') OR (T943.C112 LIKE N'%;20315;%') OR (T943.C112 LIKE N'%;
> 1000000001;%') OR (T943.C112 LIKE N'%;1000000113;%') OR (T943.C112
> LIKE N'%;20032;%') OR (T943.C112 LIKE N'%;20026;%') OR (T943.C112 LIKE
> N'%;20000;%') OR (T943.C112 LIKE N'%;20012;%') OR (T943.C112 LIKE N'%;
> 20214;%') OR (T943.C112 LIKE N'%;20213;%') OR (T943.C112 LIKE N'%;
> 20302;%') OR (T943.C112 LIKE N'%;20218;%') OR (T943.C112 LIKE N'%;
> 20003;%') OR (T943.C112 LIKE N'%;1000000058;%'))
>
> This clause is added when Unrestricted Access is not checked.
> If I check Unrestricted access, it doesn't add that clause.
>
> Also having that clause in the SQL statement adds 5-8 seconds to the
> query and makes my CPU usage jump from 60% to 100% for the query.
>
> I cannot pinpoint where that clause gets added either. It seems to be
> added at the start of any console search. incident, problem, work
> order or overview and is solely based on if that Unrestricted Access
> is checked or not.
>
> We have Multitenancy turned on and are on ARS 7.1 patch 3, ITSM 7.0.3
>
> I'm wondering if it's similar to the OPs issue
>
>
>
> On Jan 24, 10:00 am, "Mueller, Doug" <[email protected]> wrote:
>> Viki,
>>
>> As Axton noted, although it looks BIG, ignore the C112 clauses in any
>> WHERE clause when looking at
>> AR System logs. This also includes any C60xxx fields. All of these
>> clauses will be at the end of the query.
>> They may be large (if you are in a lot of groups). They do not affect
>> the overall performance of the system.
>>
>> So, you get down in this case to the very simply query Axton has
>> indicated. This is an UNQUALIFIED
>> search of the given table (T28).
>>
>> First, do you have "disallow unqualified queries" server option set.
>> This command would indicate you do not
>> have this set. It is recommended that you do to control this type of
>> unqualified query from accidentally
>> happening.
>>
>> If you turn on SQL logging at the AR System level, it will tell you a
>> lot of information.
>>
>> What login issued the command
>> When
>> From what type of client (the client type can tell you it came from
>> the mid-tier or a specific utility or if 0, it
>> generally means from a custom API program)
>>
>> If you turn on various types of logs, one might include the IP address
>> too and then you can see the user and
>> the IP address and track back to the machine the command is coming from.
>>
>> All this can help track the problem.
>>
>> But, setting the option to disallow unqualified queries may go a long
>> way toward helping to either stop
>> accidents or to cause someone to come and ask you why their unqualified
>> search stopped working....
>>
>> Note you have a little bit of a clue about the user. They are not an
>> Administrator and they are in all the
>> groups that show up in the query below that field 112 is being searched
>> for.
>>
>> I hope this helps,
>>
>> Doug Mueller
>>
>> ________________________________
>> From: Action Request System discussion list(ARSList)
>> [mailto:[email protected]] On Behalf Of Axton
>> Sent: Monday, January 24, 2011 6:45 AM
>> To: [email protected]
>> Subject: Re: Ramdom Performance issue.
>>
>> ** What is table T28?
>>
>> select name
>> from arschema
>> where schemaid = 28
>> /
>>
>> The C112 parts of the query have to do with the row level locking that
>> is set up on T28.
>>
>> Also, what method are you using to identify this as a cause or indicator
>> of your performance problem?
>>
>> Axton
>>
>> On Mon, Jan 24, 2011 at 3:13 AM, Viki_kulkarni
>> <[email protected]<mailto:[email protected]>> wrote:
>> hi all,
>>
>> since couple of weeks we are facing a strange issue.
>> The AR Server just peaks at 100% and hangs. We see that there is a heavy
>> query running on DB which looks like this
>>
>> SELECT
>> T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C9
>> 00200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,
>> C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C53687102
>> 5,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536
>> 870987,C901170021,C536870957
>> FROM T28
>> WHERE (((T28.C112 LIKE :"SYS_B_00") OR ((T28.C112 LIKE :"SYS_B_01") OR
>> ((T28.C112 LIKE :"SYS_B_02") OR ((T28.C112 LIKE :"SYS_B_03") OR
>> ((T28.C112
>> LIKE :"SYS_B_04") OR ((T28.C112 LIKE :"SYS_B_05") OR ((T28.C112 LIKE
>> :"SYS_B_06") OR ((T28.C112 LIKE :"SYS_B_07") OR ((T28.C112 LIKE
>> :"SYS_B_08")
>> OR ((T28.C112 LIKE :"SYS_B_09") OR ((T28.C112 LIKE :"SYS_B_10") OR
>> ((T28.C112 LIKE :"SYS_B_11") OR ((T28.C112 LIKE :"SYS_B_12") OR
>> ((T28.C112
>> LIKE :"SYS_B_13") OR ((T28.C112 LIKE :"SYS_B_14") OR ((T28.C112 LIKE
>> :"SYS_B_15") OR ((T28.C112 LIKE :"SYS_B_16") OR ((T28.C112 LIKE
>> :"SYS_B_17")
>> OR ((T28.C112 LIKE :"SYS_B_18") OR ((T28.C112 LIKE :"SYS_B_19") OR
>> ((T28.C112 LIKE :"SYS_B_20") OR ((T28.C112 LIKE :"SYS_B_21") OR
>> ((T28.C112
>> LIKE :"SYS_B_22") OR ((T28.C112 LIKE :"SYS_B_23") OR ((T28.C112 LIKE
>> :"SYS_B_24") OR ((T28.C112 LIKE :"SYS_B_25") OR ((T28.C112 LIKE
>> :"SYS_B_26")
>> OR ((T28.C112 LIKE :"SYS_B_27") OR ((T28.C112 LIKE :"SYS_B_28") OR
>> ((T28.C112 LIKE :"SYS_B_29") OR ((T28.C112 LIKE :"SYS_B_30") OR
>> ((T28.C112
>> LIKE :"SYS_B_31") OR ((T28.C112 LIKE :"SYS_B_32") OR ((T28.C112 LIKE
>> :"SYS_B_33") OR ((T28.C112 LIKE :"SYS_B_34") OR ((T28.C112 LIKE
>> :"SYS_B_35")
>> OR ((T28.C112 LIKE :"SYS_B_36") OR ((T28.C112 LIKE :"SYS_B_37") OR
>> ((T28.C112 LIKE :"SYS_B_38") OR ((T28.C112 LIKE :"SYS_B_39") OR
>> ((T28.C112
>> LIKE :"SYS_B_40") OR ((T28.C112 LIKE :"SYS_B_41") OR ((T28.C112 LIKE
>> :"SYS_B_42") OR ((T28.C112 LIKE :"SYS_B_43") OR ((T28.C112 LIKE
>> :"SYS_B_44")
>> OR ((T28.C112 LIKE :"SYS_B_45") OR ((T28.C112 LIKE :"SYS_B_46") OR
>> ((T28.C112 LIKE :"SYS_B_47") OR ((T28.C112 LIKE :"SYS_B_48") OR
>> ((T28.C112
>> LIKE :"SYS_B_49") OR ((T28.C112 LIKE :"SYS_B_50") OR ((T28.C112 LIKE
>> :"SYS_B_51") OR ((T28.C112 LIKE :"SYS_B_52") OR ((T28.C112 LIKE
>> :"SYS_B_53")
>> OR ((T28.C112 LIKE :"SYS_B_54") OR ((T28.C112 LIKE :"SYS_B_55") OR
>> ((T28.C112 LIKE :"SYS_B_56") OR ((T28.C112 LIKE :"SYS_B_57") OR
>> ((T28.C112
>> LIKE :"SYS_B_58") OR ((T28.C112 LIKE :"SYS_B_59") OR ((T28.C112 LIKE
>> :"SYS_B_60") OR ((T28.C112 LIKE :"SYS_B_61") OR ((T28.C112 LIKE
>> :"SYS_B_62")
>> OR ((T28.C112 LIKE :"SYS_B_63") OR ((T28.C112 LIKE :"SYS_B_64") OR
>> ((T28.C112 LIKE :"SYS_B_65") OR (T28.C112 LIKE
>> :"SYS_B_66"))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
>> )))
>> AND (T28.C536870993 LIKE :"SYS_B_67")) ORDER BY :"SYS_B_68" ASC
>>
>> but we don't know from where this query is getting fired out by which
>> action. It happens at random and hence i am not able to keep my logging
>> on
>> for long time on my production setup.
>>
>> Can anyone help me decipher the query or guess why and how this query is
>> coming into the picture while doing what action. I am sure it has to do
>> something with the 112 field while pulling reports. Am I correct?
>>
>> Any help is appreciated as this is now creeping up as a major issue.
>>
>> Thanks,
>> Viki
>>
>> --
>> View this message in
>> context:http://old.nabble.com/Ramdom-Performance-issue.-tp30746787p30746787.html
>> Sent from the ARS (Action Request System) mailing list archive at
>> Nabble.com.
>>
>> ___________________________________________________________________________
>> ____
>> UNSUBSCRIBE or access ARSlist Archives
>> atwww.arslist.org<http://www.arslist.org>
>> attend wwrug11www.wwrug.com<http://www.wwrug.com> ARSList: "Where the
>> Answers Are"
>>
>> _attend WWRUG11www.wwrug.comARSlist: "Where the Answers Are"_
>>
>> ___________________________________________________________________________
>> ____
>> UNSUBSCRIBE or access ARSlist Archives atwww.arslist.org
>> attend wwrug11www.wwrug.comARSList: "Where the Answers Are"
>
> _______________________________________________________________________________
> UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
> attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
>
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
