You can turn on SQL, Filter and Escalation log from server side and give the file same name. That way, you may be able to figure out what filter or escalation is generating this query.
Thanks, Wirasat ________________________________ From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Monday, January 24, 2011 10:00 AM To: [email protected] Subject: Re: Ramdom Performance issue. ** 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,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 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 at www.arslist.org<http://www.arslist.org> attend wwrug11 www.wwrug.com<http://www.wwrug.com> ARSList: "Where the Answers Are" _attend WWRUG11 www.wwrug.com ARSlist: "Where the Answers Are"_ _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"
