Hi,

I would use RRR|Log to analyze a ~1Gb ESCL/API/FLTR/SQL-log snapshot on
Monday, Wednesday and Friday, to be able to see the difference in the
server activity.

        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.

> hi,
>
> We see this issue on every Monday after the deployment and it resolves by
> itself by Wednesday after the deployment is done on Saturday . I am
> checking
> the other interfaces that might have an impact as well.
>
> Thanks,
> Vikram
>
> Misi Mladoniczky wrote:
>>
>> Hi,
>>
>> No you don't have the "%zzz%".
>>
>> You have a "zzz%", which is an entirely different thing, and will allow
>> usage of indexes.
>>
>> It is the leading match in the LIKE "%zzz%" that prevents the index from
>> being used.
>>
>> Admin changes can take a lot of resources from the server in the period
>> right after the change, to allow for the server to recache all
>> definitions. Typically less than 10 minutes will be needed.
>>
>> Admin changes will require the clients to recache as well, which may
>> slow
>> things down slightly, but should not prevent the database from using
>> indexes.
>>
>>         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.
>>
>>> hi Doug,
>>>
>>> Thanks for the reply as always lot of knowledge shared. You are right I
>>> picked this query from TOAD application and hence the SYS_B_XX variable
>>> comes into the picture. I was able to catch the query from SQL logs and
>>> its
>>> pasted below :
>>>
>>> <SQL > <TID: 000033> <RPC ID: 0001008137> <Queue: List      >
>>> <Client-RPC:
>>> 390620   > <USER: vikram                        > /* Mon Feb 28 2011
>>> 08:46:16.9857 */ SELECT
>>> T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C536871025,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536870987,C901170021,C536870957
>>> FROM T28     WHERE (((T28.C112 LIKE '%;44451;%') OR ((T28.C112 LIKE
>>> '%;44450;%') OR ((T28.C112 LIKE '%;9999999;%') OR ((T28.C112 LIKE
>>> '%;500014;%') OR ((T28.C112 LIKE '%;500013;%') OR ((T28.C112 LIKE
>>> '%;500000;%') OR ((T28.C112 LIKE '%;50098;%') OR ((T28.C112 LIKE
>>> '%;99986;%') OR ((T28.C112 LIKE '%;99986;%') OR ((T28.C112 LIKE
>>> '%;99989;%')
>>> OR ((T28.C112 LIKE '%;99930;%') OR ((T28.C112 LIKE '%;99985;%') OR
>>> ((T28.C112 LIKE '%;99931;%') OR ((T28.C112 LIKE '%;99925;%') OR
>>> ((T28.C112
>>> LIKE '%;99906;%') OR ((T28.C112 LIKE '%;99928;%') OR ((T28.C112 LIKE
>>> '%;99957;%') OR ((T28.C112 LIKE '%;99932;%') OR ((T28.C112 LIKE
>>> '%;99929;%')
>>> OR ((T28.C112 LIKE '%;99921;%') OR ((T28.C112 LIKE '%;99926;%') OR
>>> ((T28.C112 LIKE '%;99902;%') OR ((T28.C112 LIKE '%;99950;%') OR
>>> ((T28.C112
>>> LIKE '%;99924;%') OR ((T28.C112 LIKE '%;44407;%') OR ((T28.C112 LIKE
>>> '%;99960;%') OR ((T28.C112 LIKE '%;99922;%') OR ((T28.C112 LIKE
>>> '%;99990;%')
>>> OR ((T28.C112 LIKE '%;44405;%') OR ((T28.C112 LIKE '%;44404;%') OR
>>> ((T28.C112 LIKE '%;44418;%') OR ((T28.C112 LIKE '%;44418;%') OR
>>> ((T28.C112
>>> LIKE '%;99923;%') OR ((T28.C112 LIKE '%;99908;%') OR ((T28.C112 LIKE
>>> '%;99904;%') OR ((T28.C112 LIKE '%;99927;%') OR ((T28.C112 LIKE
>>> '%;99939;%')
>>> OR ((T28.C112 LIKE '%;99971;%') OR ((T28.C112 LIKE '%;99991;%') OR
>>> ((T28.C112 LIKE '%;99901;%') OR ((T28.C112 LIKE '%;99905;%') OR
>>> ((T28.C112
>>> LIKE '%;99941;%') OR ((T28.C112 LIKE '%;99958;%') OR ((T28.C112 LIKE
>>> '%;99907;%') OR ((T28.C112 LIKE '%;99953;%') OR ((T28.C112 LIKE
>>> '%;44406;%')
>>> OR ((T28.C112 LIKE '%;99987;%') OR ((T28.C112 LIKE '%;99903;%') OR
>>> ((T28.C112 LIKE '%;99988;%') OR ((T28.C112 LIKE '%;99951;%') OR
>>> ((T28.C112
>>> LIKE '%;99972;%') OR ((T28.C112 LIKE '%;500012;%') OR ((T28.C112 LIKE
>>> '%;50045;%') OR ((T28.C112 LIKE '%;50048;%') OR ((T28.C112 LIKE
>>> '%;500011;%') OR ((T28.C112 LIKE '%;500010;%') OR ((T28.C112 LIKE
>>> '%;666;%')
>>> OR ((T28.C112 LIKE '%;500009;%') OR ((T28.C112 LIKE '%;500008;%') OR
>>> ((T28.C112 LIKE '%;500007;%') OR ((T28.C112 LIKE '%;50046;%') OR
>>> ((T28.C112
>>> LIKE '%;500006;%') OR ((T28.C112 LIKE '%;500005;%') OR ((T28.C112 LIKE
>>> '%;500004;%') OR ((T28.C112 LIKE '%;500003;%') OR ((T28.C112 LIKE
>>> '%;500002;%') OR ((T28.C112 LIKE '%;500001;%') OR ((T28.C112 LIKE
>>> '%;0;%')
>>> OR ((T28.C112 LIKE '%;2005000;%') OR ((T28.C112 LIKE '%;655;%') OR
>>> ((T28.C112 LIKE '%;20017;%') OR ((T28.C112 LIKE '%;652;%') OR
>>> ((T28.C112
>>> LIKE '%;671;%') OR (T28.C112 LIKE
>>> '%;0;%'))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
>>> AND (T28.C536870913 LIKE '101200140%')) ORDER BY 1 ASC
>>>
>>>
>>> We have a lot of users in lot of groups and hence we have high number
>>> of
>>> LIKE clauses.
>>> As you can see we have "%zzz% as you mentioned in your post which will
>>> cause
>>> a complete table scan. Right now we are trying 2 things :
>>>
>>> a) Find the workflow that tends to fire this query and
>>> b) Try and find a way to minimize the groups every user belongs. Can
>>> computed groups be a good option for this and is it supported for ARS
>>> 5.0.1.? Sorry to ask you about  this as I have limited understandning
>>> about
>>> computed groups.
>>>
>>> One interesting observation though, we see this issue only after the
>>> weekend
>>> we have some kind of code change on the production system and in a
>>> sense
>>> when we do some kind of admin operation on the application not
>>> necessarily
>>> the change is done on the schema on which these huge queries are fired
>>> but
>>> we still face this issue. Can there be any possible connection between
>>> this?
>>>
>>> Thanks,
>>> Vikram
>>>
>>> Mueller, Doug wrote:
>>>>
>>>> Vikram,
>>>>
>>>> Several observations about this command:
>>>>
>>>> First, this command seems to have been run through some kind of
>>>> translator
>>>> that
>>>> has taken every string between "" symbols and replaced it with an
>>>> incrementing
>>>> string of SYS_B_xx  where xx is an incrementing number.
>>>>
>>>> Because of this, it is a bit hard to assist with the question you ask
>>>> because
>>>> there is critical information in the query that you have masked out
>>>> and
>>>> that
>>>> information may be the key to the solution.  The most critical missing
>>>> piece of
>>>> data is whatever SYS_B_67 was originally.  As you will see from the
>>>> remaining
>>>> notes, the rest of the strings really aren't important.
>>>>
>>>> 1) You comment on disallowing unqualified queries and say that this
>>>> didn't
>>>> help
>>>>    this query.  Well, that option is not related as this is NOT an
>>>> unqualified
>>>>    query.  There is a qualification that is being specified by the
>>>> person
>>>> who
>>>>    is issuing this query.
>>>>
>>>>      '536870993' LIKE "SYS_B_67"
>>>>
>>>>    Now, the issue here is what is SYS_B_67.  If it is really a search
>>>> that
>>>> looks
>>>>    something like the following
>>>>
>>>>         %zzz%
>>>>
>>>>    Then, you simply have a terribly inefficient query.  You are
>>>> searching
>>>> in a
>>>>    character field for where some set of characters (zzz in my
>>>> example)
>>>> is
>>>>    somewhere in the field.  This requires a table scan to look at each
>>>> record
>>>>    and the data in that field to see if the string matches.
>>>>
>>>>    If it is really a search that looks more like
>>>>
>>>>        zzz%
>>>>
>>>>    Then, you can get an efficient search out of this.  This is to find
>>>> a
>>>> value
>>>>    where the string STARTS with a set of characters (zzz in my
>>>> example)
>>>> and has
>>>>    other data after it.  If like this, then indexes will be used in
>>>> the
>>>> search
>>>>    if there is an index on field 536870993.  If there is no index on
>>>> that
>>>> field
>>>>    one could be added and will make a difference.  REMEMBER, the
>>>> search
>>>> cannot
>>>>    have a leading % for the index to be useful.  If there is a leading
>>>> %
>>>> like in
>>>>    the first example, the index will not be used even if present.
>>>>
>>>>    There is no other qualifier that the customer is supplying to
>>>> further
>>>> limit
>>>>    the search so the entire indexing scheme hinges on this one field.
>>>>
>>>> 2) All the comparisons of field 112 to a value are things the AR
>>>> System
>>>> adds
>>>>    onto the search because you have a row level security field (field
>>>> 112)
>>>> on
>>>>    the form and you do not have global access to the entries, and the
>>>> system is
>>>>    checking to see if you have access to rows it is retrieving.
>>>>
>>>>    At first glance, the search is not efficient as it is a LIKE
>>>> "%xxx%"
>>>> type
>>>>    search.  So, if that was the only search criteria, it is correct,
>>>> this
>>>> would
>>>>    be an inefficient criteria.  However, the design is for this to be
>>>> an
>>>>    ADDITIVE criteria, not the primary criteria.  The primary criteria
>>>> is
>>>> the
>>>>    search the user provides, this is just an additional test.  In the
>>>> role
>>>> of
>>>>    additional test, efficiency is not significant as only one index
>>>> can
>>>> be
>>>> used
>>>>    for a search.  The row would already have been retrieved to apply
>>>> this
>>>>    criteria as additional qualification.
>>>>
>>>>    So, the entire clause with Ored tests of field 112 can be
>>>> discounted
>>>> from any
>>>>    qualification you are trying to check performance of.
>>>>
>>>>    Now, if that clause is the only clause in the WHERE, that means you
>>>> have
>>>>    allowed unqualified queries and someone is issuing an unqualified
>>>> query
>>>>    (which is a table scan).
>>>>
>>>> 3) Whoever the user is in this scenario is a member of 67 groups
>>>> (SYS_B_00
>>>>    through SYS_B_66).  This is a lot of groups for a single user to be
>>>> a
>>>> member
>>>>    of.  Not disallowed, and the system will work and it does support
>>>> that.
>>>> It
>>>>    is just an observation that it is a large number.  Typically, a
>>>> user
>>>> is
>>>> a
>>>>    member of a small number of groups and this clause is significantly
>>>> shorter.
>>>>
>>>>
>>>>
>>>> Hopefully, this gives you the information you need.  What I really
>>>> feel
>>>> is
>>>> the
>>>> challenge here is that the user is qualifying field 536870993 to look
>>>> for
>>>> a
>>>> string ANYWHERE in the field and that is causing the problem.
>>>>
>>>> Solutions:
>>>>
>>>> 1) If this really should be an = instead of a LIKE "%zzz%", you may
>>>> check
>>>> the
>>>>    default QBE setting for the field and change it from Anywhere to
>>>> Leading or
>>>>    Matching.  The default QBE match criteria (a view property of the
>>>> field) is
>>>>    Anywhere.  So, a user may not be aware if they are using the query
>>>> screen and
>>>>    putting in a value for that field what is happening.  Changing the
>>>>    definition will change the command issued.
>>>>
>>>> 2) If a user is doing this in a query bar, educate the user about the
>>>> cost
>>>> of
>>>>    this type of operation.
>>>>
>>>> 3) If workflow is issuing this command, determine whether the workflow
>>>> could
>>>>    use a leading or equal search.
>>>>
>>>> 4) Is there other criteria you can use on some other field(s) on the
>>>> form
>>>> to
>>>>    help limit the scope of the rows that you need to search across?
>>>>
>>>> Of course, if what you need to do is a search for that string anywhere
>>>> in
>>>> the
>>>> field AND there are no other criteria you can use to search other
>>>> fields
>>>> to
>>>> limit which rows are looked at, then you are stuck with the
>>>> performance
>>>> of
>>>> the
>>>> operation you need to perform.
>>>>
>>>>
>>>> Good luck with your situation and with finding a solution for long
>>>> query.
>>>>
>>>> Doug Mueller
>>>>
>>>> -----Original Message-----
>>>> From: Action Request System discussion list(ARSList)
>>>> [mailto:[email protected]] On Behalf Of vikram_k
>>>> Sent: Monday, February 28, 2011 1:29 AM
>>>> To: [email protected]
>>>> Subject: Huge queries hogging up the database
>>>>
>>>> hi all,
>>>>
>>>> I had recently put some posts regarding AR Performance and I am
>>>> thankful
>>>> that most of my problems are now resolved. But there is one issue
>>>> which
>>>> is
>>>> a
>>>> bottle neck I am seeing a huge oracle query like
>>>>
>>>> SELECT
>>>> T28.C1,C600011000,C536870984,C536870913,C900106001,C901050057,C536871019,C900200400,C536870958,C536870950,C900170325,C536870935,C900100003,C536870929,C536870931,C536870914,C536871018,C536870938,C650000000,C536870952,C536871025,C536871038,C536900000,C536870912,C7,C900040206,C901092014,C900110076,C536870987,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
>>>>
>>>> I was told to disallow unqualified search which I did and yet I am
>>>> seeing
>>>> this kind of query.
>>>>
>>>> Can anyone help me understand what exactly is this query doing and
>>>> what
>>>> can
>>>> be done to avoid it...
>>>>
>>>> Thanks,
>>>> Vikram
>>>> --
>>>> View this message in context:
>>>> http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31030153.html
>>>> Sent from the ARS (Action Request System) mailing list archive at
>>>> Nabble.com.
>>>>
>>>> _______________________________________________________________________________
>>>> 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"
>>>>
>>>>
>>>
>>> --
>>> View this message in context:
>>> http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31046950.html
>>> Sent from the ARS (Action Request System) mailing list archive at
>>> Nabble.com.
>>>
>>> _______________________________________________________________________________
>>> 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"
>>
>>
>
> --
> View this message in context:
> http://old.nabble.com/Huge-queries-hogging-up-the-database-tp31030153p31047544.html
> Sent from the ARS (Action Request System) mailing list archive at
> Nabble.com.
>
> _______________________________________________________________________________
> 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"

Reply via email to