Re: Long running query
The use of 'Like' means that a table scan is performed and if the table(s) is big then performance is poor. Try reducing the number of fields searched and change the qualify to absolutes Stuart Schon Service Desk Systems - Manager Please consider the environment before printing this email -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ali A. Musa Sent: Wednesday, 27 March 2013 4:38 PM To: arslist@ARSLIST.ORG Subject: Re: Long running query Check the column included in where and ensured they are indexed WHERE ((T1411.C300411500 LIKE :"SYS_B_00") AND ((T1411.C112 LIKE :"SYS_B_01") OR (T1411.C112 LIKE :"SYS_B_02") OR (T1411.C112 LIKE :"SYS_B_03") OR (T1411.C112 LIKE :"SYS_B_04") -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rajesh Sent: Wednesday, March 27, 2013 1:09 AM To: arslist@ARSLIST.ORG Subject: Long running query Have seen a post similar this, but since i could not find this out i am posting this again. We are facing performance issue and while going throught the DB log we can see following query run and it is taking log time to execute Allow unqualified search is checked T1141 :- SLM measurement and the Field C300411500 is an indexed field. SELECT * FROM ( SELECT T1411.C1, C300314700, C300411500, C300426800, C300365100, C300411700, C490008000, C301721300, C490009000, C300905300 FROM T1411 WHERE ((T1411.C300411500 LIKE :"SYS_B_00") AND ((T1411.C112 LIKE :"SYS_B_01") OR (T1411.C112 LIKE :"SYS_B_02") OR (T1411.C112 LIKE :"SYS_B_03") OR (T1411.C112 LIKE :"SYS_B_04") OR (T1411.C112 LIKE :"SYS_B_05") OR (T1411.C112 LIKE :"SYS_B_06") OR (T1411.C112 LIKE :"SYS_B_07") OR (T1411.C112 LIKE :"SYS_B_08") OR (T1411.C112 LIKE :"SYS_B_09") OR (T1411.C112 LIKE :"SYS_B_10") OR (T1411.C112 LIKE :"SYS_B_11") OR (T1411.C112 LIKE :"SYS_B_12") OR (T1411.C112 LIKE :"SYS_B_13") OR (T1411.C112 LIKE :"SYS_B_14") OR (T1411.C112 LIKE :"SYS_B_15") OR (T1411.C112 LIKE :"SYS_B_16") OR (T1411.C112 LIKE :"SYS_B_17") OR (T1411.C112 LIKE :"SYS_B_18") OR (T1411.C112 LIKE :"SYS_B_19") OR (T1411.C112 LIKE :"SYS_B_20") OR (T1411.C112 LIKE :"SYS_B_21") OR (T1411.C112 LIKE :"SYS_B_22") OR (T1411.C112 LIKE :"SYS_B_23") OR (T1411.C112 LIKE :"SYS_B_24") OR (T1411.C112 LIKE :"SYS_B_25") OR (T1411.C112 LIKE :"SYS_B_26") OR (T1411.C112 LIKE :"SYS_B_27") OR (T1411.C112 LIKE :"SYS_B_28") OR (T1411.C112 LIKE :"SYS_B_29") OR (T1411.C112 LIKE :"SYS_B_30") OR (T1411.C112 LIKE :"SYS_B_31") OR (T1411.C112 LIKE :"SYS_B_32") OR (T1411.C112 LIKE :"SYS_B_33") OR (T1411.C112 LIKE :"SYS_B_34") OR (T1411.C112 LIKE :"SYS_B_35") OR (T1411.C112 LIKE :"SYS_B_36") OR (T1411.C112 LIKE :"SYS_B_37") OR (T1411.C112 LIKE :"SYS_B_38") OR (T1411.C112 LIKE :"SYS_B_39") OR (T1411.C112 L IKE :"SYS_B_40") OR (T1411.C112 LIKE :"SYS_B_41") OR (T1411.C112 LIKE :"SYS_B_42") OR (T1411.C112 LIKE :"SYS_B_43") OR (T1411.C112 LIKE :"SYS_B_44") OR (T1411.C112 LIKE :"SYS_B_45") OR (T1411.C112 LIKE :"SYS_B_46") OR (T1411.C112 LIKE :"SYS_B_47"))) ORDER BY :"SYS_B_48" DESC ) WHERE ROWNUM <= :"SYS_B_49" Query going for FTS on ARADMIN.T1161 (Helpdesk Form) -- SELECT COUNT (*) FROM T1161 WHERE ( ( ( ( (T1161.C100079 = :"SYS_B_00") OR (T1161.C100079 = :"SYS_B_01") OR (T1161.C100079 = :"SYS_B_02") OR (T1161.C100079 = :"SYS_B_03") OR (T1161.C100079 = :"SYS_B_04"))AND (:"SYS_B_05" != :"SYS_B_06")) OR ( (:"SYS_B_07" = :"SYS_B_08") AND (:"SYS_B_09" = :"SYS_B_10"))) AND (T1161.C7 >= :"SYS_B_11")AND (T1161.C7 <= :"SYS_B_12") AND ( (T1161.C100560 >= :"SYS_B_13") OR (:"SYS_B_14" = :"SYS_B_15")) AND ( (T1161.C100560 <= :"SYS_B_16") OR (:"SYS_B_17" = :"SYS_B_18")) AND ( (T1161.C11 = :"SYS_B_19") OR (:"SYS_B_20" = :"SYS_B_21")) AND ( (T1161.C103009 IS NULL) OR (:"SYS_B_22" = :"SYS_B_23")) AND (:"SYS_B_24" = :"SYS_B_25")AND
Re: Long running query
Check the column included in where and ensured they are indexed WHERE ((T1411.C300411500 LIKE :"SYS_B_00") AND ((T1411.C112 LIKE :"SYS_B_01") OR (T1411.C112 LIKE :"SYS_B_02") OR (T1411.C112 LIKE :"SYS_B_03") OR (T1411.C112 LIKE :"SYS_B_04") -Original Message- From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Rajesh Sent: Wednesday, March 27, 2013 1:09 AM To: arslist@ARSLIST.ORG Subject: Long running query Have seen a post similar this, but since i could not find this out i am posting this again. We are facing performance issue and while going throught the DB log we can see following query run and it is taking log time to execute Allow unqualified search is checked T1141 :- SLM measurement and the Field C300411500 is an indexed field. SELECT * FROM ( SELECT T1411.C1, C300314700, C300411500, C300426800, C300365100, C300411700, C490008000, C301721300, C490009000, C300905300 FROM T1411 WHERE ((T1411.C300411500 LIKE :"SYS_B_00") AND ((T1411.C112 LIKE :"SYS_B_01") OR (T1411.C112 LIKE :"SYS_B_02") OR (T1411.C112 LIKE :"SYS_B_03") OR (T1411.C112 LIKE :"SYS_B_04") OR (T1411.C112 LIKE :"SYS_B_05") OR (T1411.C112 LIKE :"SYS_B_06") OR (T1411.C112 LIKE :"SYS_B_07") OR (T1411.C112 LIKE :"SYS_B_08") OR (T1411.C112 LIKE :"SYS_B_09") OR (T1411.C112 LIKE :"SYS_B_10") OR (T1411.C112 LIKE :"SYS_B_11") OR (T1411.C112 LIKE :"SYS_B_12") OR (T1411.C112 LIKE :"SYS_B_13") OR (T1411.C112 LIKE :"SYS_B_14") OR (T1411.C112 LIKE :"SYS_B_15") OR (T1411.C112 LIKE :"SYS_B_16") OR (T1411.C112 LIKE :"SYS_B_17") OR (T1411.C112 LIKE :"SYS_B_18") OR (T1411.C112 LIKE :"SYS_B_19") OR (T1411.C112 LIKE :"SYS_B_20") OR (T1411.C112 LIKE :"SYS_B_21") OR (T1411.C112 LIKE :"SYS_B_22") OR (T1411.C112 LIKE :"SYS_B_23") OR (T1411.C112 LIKE :"SYS_B_24") OR (T1411.C112 LIKE :"SYS_B_25") OR (T1411.C112 LIKE :"SYS_B_26") OR (T1411.C112 LIKE :"SYS_B_27") OR (T1411.C112 LIKE :"SYS_B_28") OR (T1411.C112 LIKE :"SYS_B_29") OR (T1411.C112 LIKE :"SYS_B_30") OR (T1411.C112 LIKE :"SYS_B_31") OR (T1411.C112 LIKE :"SYS_B_32") OR (T1411.C112 LIKE :"SYS_B_33") OR (T1411.C112 LIKE :"SYS_B_34") OR (T1411.C112 LIKE :"SYS_B_35") OR (T1411.C112 LIKE :"SYS_B_36") OR (T1411.C112 LIKE :"SYS_B_37") OR (T1411.C112 LIKE :"SYS_B_38") OR (T1411.C112 LIKE :"SYS_B_39") OR (T1411.C112 L IKE :"SYS_B_40") OR (T1411.C112 LIKE :"SYS_B_41") OR (T1411.C112 LIKE :"SYS_B_42") OR (T1411.C112 LIKE :"SYS_B_43") OR (T1411.C112 LIKE :"SYS_B_44") OR (T1411.C112 LIKE :"SYS_B_45") OR (T1411.C112 LIKE :"SYS_B_46") OR (T1411.C112 LIKE :"SYS_B_47"))) ORDER BY :"SYS_B_48" DESC ) WHERE ROWNUM <= :"SYS_B_49" Query going for FTS on ARADMIN.T1161 (Helpdesk Form) -- SELECT COUNT (*) FROM T1161 WHERE ( ( ( ( (T1161.C100079 = :"SYS_B_00") OR (T1161.C100079 = :"SYS_B_01") OR (T1161.C100079 = :"SYS_B_02") OR (T1161.C100079 = :"SYS_B_03") OR (T1161.C100079 = :"SYS_B_04"))AND (:"SYS_B_05" != :"SYS_B_06")) OR ( (:"SYS_B_07" = :"SYS_B_08") AND (:"SYS_B_09" = :"SYS_B_10"))) AND (T1161.C7 >= :"SYS_B_11")AND (T1161.C7 <= :"SYS_B_12") AND ( (T1161.C100560 >= :"SYS_B_13") OR (:"SYS_B_14" = :"SYS_B_15")) AND ( (T1161.C100560 <= :"SYS_B_16") OR (:"SYS_B_17" = :"SYS_B_18")) AND ( (T1161.C11 = :"SYS_B_19") OR (:"SYS_B_20" = :"SYS_B_21")) AND ( (T1161.C103009 IS NULL) OR (:"SYS_B_22" = :"SYS_B_23")) AND (:"SYS_B_24" = :"SYS_B_25")AND ( (T1161.C112 LIKE :"SYS_B_26") OR (T1161.C112 LIKE :"SYS_B_27") OR (T1161.C112 LIKE :"SYS_B_28") OR (T1161.C112 LIKE :"SYS_B_29") OR (T1161.C112 LIKE :"SYS_B_30") OR (T1161.C112 LIKE :"SYS_B_31") OR (T1161.C112 LIKE :"SYS_B_32") OR (T1161.C112 LIKE :"SYS_B_33") OR (T1161.C112 LIKE :"SYS_B_34") OR (T1161.C112 LIKE :&qu
Long running query
Have seen a post similar this, but since i could not find this out i am posting this again. We are facing performance issue and while going throught the DB log we can see following query run and it is taking log time to execute Allow unqualified search is checked T1141 :- SLM measurement and the Field C300411500 is an indexed field. SELECT * FROM ( SELECT T1411.C1, C300314700, C300411500, C300426800, C300365100, C300411700, C490008000, C301721300, C490009000, C300905300 FROM T1411 WHERE ((T1411.C300411500 LIKE :"SYS_B_00") AND ((T1411.C112 LIKE :"SYS_B_01") OR (T1411.C112 LIKE :"SYS_B_02") OR (T1411.C112 LIKE :"SYS_B_03") OR (T1411.C112 LIKE :"SYS_B_04") OR (T1411.C112 LIKE :"SYS_B_05") OR (T1411.C112 LIKE :"SYS_B_06") OR (T1411.C112 LIKE :"SYS_B_07") OR (T1411.C112 LIKE :"SYS_B_08") OR (T1411.C112 LIKE :"SYS_B_09") OR (T1411.C112 LIKE :"SYS_B_10") OR (T1411.C112 LIKE :"SYS_B_11") OR (T1411.C112 LIKE :"SYS_B_12") OR (T1411.C112 LIKE :"SYS_B_13") OR (T1411.C112 LIKE :"SYS_B_14") OR (T1411.C112 LIKE :"SYS_B_15") OR (T1411.C112 LIKE :"SYS_B_16") OR (T1411.C112 LIKE :"SYS_B_17") OR (T1411.C112 LIKE :"SYS_B_18") OR (T1411.C112 LIKE :"SYS_B_19") OR (T1411.C112 LIKE :"SYS_B_20") OR (T1411.C112 LIKE :"SYS_B_21") OR (T1411.C112 LIKE :"SYS_B_22") OR (T1411.C112 LIKE :"SYS_B_23") OR (T1411.C112 LIKE :"SYS_B_24") OR (T1411.C112 LIKE :"SYS_B_25") OR (T1411.C112 LIKE :"SYS_B_26") OR (T1411.C112 LIKE :"SYS_B_27") OR (T1411.C112 LIKE :"SYS_B_28") OR (T1411.C112 LIKE :"SYS_B_29") OR (T1411.C112 LIKE :"SYS_B_30") OR (T1411.C112 LIKE :"SYS_B_31") OR (T1411.C112 LIKE :"SYS_B_32") OR (T1411.C112 LIKE :"SYS_B_33") OR (T1411.C112 LIKE :"SYS_B_34") OR (T1411.C112 LIKE :"SYS_B_35") OR (T1411.C112 LIKE :"SYS_B_36") OR (T1411.C112 LIKE :"SYS_B_37") OR (T1411.C112 LIKE :"SYS_B_38") OR (T1411.C112 LIKE :"SYS_B_39") OR (T1411.C112 L IKE :"SYS_B_40") OR (T1411.C112 LIKE :"SYS_B_41") OR (T1411.C112 LIKE :"SYS_B_42") OR (T1411.C112 LIKE :"SYS_B_43") OR (T1411.C112 LIKE :"SYS_B_44") OR (T1411.C112 LIKE :"SYS_B_45") OR (T1411.C112 LIKE :"SYS_B_46") OR (T1411.C112 LIKE :"SYS_B_47"))) ORDER BY :"SYS_B_48" DESC ) WHERE ROWNUM <= :"SYS_B_49" Query going for FTS on ARADMIN.T1161 (Helpdesk Form) -- SELECT COUNT (*) FROM T1161 WHERE ( ( ( ( (T1161.C100079 = :"SYS_B_00") OR (T1161.C100079 = :"SYS_B_01") OR (T1161.C100079 = :"SYS_B_02") OR (T1161.C100079 = :"SYS_B_03") OR (T1161.C100079 = :"SYS_B_04"))AND (:"SYS_B_05" != :"SYS_B_06")) OR ( (:"SYS_B_07" = :"SYS_B_08") AND (:"SYS_B_09" = :"SYS_B_10"))) AND (T1161.C7 >= :"SYS_B_11")AND (T1161.C7 <= :"SYS_B_12") AND ( (T1161.C100560 >= :"SYS_B_13") OR (:"SYS_B_14" = :"SYS_B_15")) AND ( (T1161.C100560 <= :"SYS_B_16") OR (:"SYS_B_17" = :"SYS_B_18")) AND ( (T1161.C11 = :"SYS_B_19") OR (:"SYS_B_20" = :"SYS_B_21")) AND ( (T1161.C103009 IS NULL) OR (:"SYS_B_22" = :"SYS_B_23")) AND (:"SYS_B_24" = :"SYS_B_25")AND ( (T1161.C112 LIKE :"SYS_B_26") OR (T1161.C112 LIKE :"SYS_B_27") OR (T1161.C112 LIKE :"SYS_B_28") OR (T1161.C112 LIKE :"SYS_B_29") OR (T1161.C112 LIKE :"SYS_B_30") OR (T1161.C112 LIKE :"SYS_B_31") OR (T1161.C112 LIKE :"SYS_B_32") OR (T1161.C112 LIKE :"SYS_B_33") OR (T1161.C112 LIKE :"SYS_B_34") OR (T1161.C112 LIKE :"SYS_B_35") OR (T1161.C112 LIKE :"SYS_B_36") OR (T1161.C112 LIKE :"SYS_B_37") OR (T1161.C112 LIKE :"SYS_B_38") OR (T1161.C112 LIKE :"SYS_B_39") OR (T1161.C112 LIKE :"SYS_B_40") OR (T1161.C112 LIKE :"SYS_B_41") OR (T1161.C112 LIKE :"SYS_B_42") OR (T1161.C112 LIKE :"SYS_B_43") OR (T1161.C112 LIKE :"SYS_B_44") OR (T1161.C112 LIKE :"SYS_B_45") OR (T1161.C112 LIKE :"SYS_B_46") OR (T1161.C112 LIKE :"SYS_B_47") OR (T1161.C112 LIKE :"SYS_B_48") OR (T1161.C112 LIKE :"SYS_B_49") OR (T1161.C112 LIKE :"SYS_B_50") OR (T1161.C112 LIKE :"SYS_B_51") OR (T1161.C112 LIKE :"SYS_B_52") OR (T1161.C112 LIKE :"SYS_B_53") OR (T1161.C112 LIKE :"SYS_B_54") OR (T1161.C112 LIKE :"SYS_B_55") OR (T1161.C112 LIKE :"SYS_B_56") OR (T1161.C112 LIKE :"SYS_B_57"))) ARYSTEM 7.1 patch 11 OS Sun 5.10 Oracle 10.2.0.3.0 - 64bi Can anyone let me know what exactly this query is about? Regards Rajesh ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Re: Long Running Query
Thanks Anay and Kiran. On Wed, Jan 9, 2013 at 3:49 AM, Hullule, Kiran wrote: > ** > > Following solution works fine except db2 database, for db2 customer has to > run runstat on db for T table in question and understand where exactly the > time is taken, based on the output of runstat customer can implement > indexes. > > ** ** > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Ashtaputre, Anay > *Sent:* Wednesday, January 09, 2013 1:54 PM > > *To:* arslist@ARSLIST.ORG > *Subject:* Re: Long Running Query > > ** ** > > ** > > Following Defect & Knowledge article can be referred – > > ** ** > > SW00443560 > > KA380785 > > ** ** > > Following fields will need to be indexed – > > ** ** > > 'C23009', 'C100079' & 'C7' > > ** ** > > Indexing to be done at the db level for better performance and after > indexing customer should get ‘execution plan’ aka ‘explain plan’ of the > query by running it on db directly and see the time difference between > pre-indexing and post-indexing. That will tell you whether or not solution > implemented is helping. > > ** ** > > ** ** > > Thanks > > Anay.. > > ** ** > > *From:* Action Request System discussion list(ARSList) [mailto: > arslist@ARSLIST.ORG] *On Behalf Of *Vishal Navale > *Sent:* Wednesday, January 09, 2013 1:13 AM > *To:* arslist@ARSLIST.ORG > *Subject:* Re: Long Running Query > > ** ** > > ** > > Thanks Rick for the answer. > > > > Modify the query and indexing - do you have some suggestions for how to do > this. > > > > Thanks > > Vishal > > > > > > On Tue, Jan 8, 2013 at 2:00 PM, Rick Cook wrote:*** > * > > modifying the query, > > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > > _ARSlist: "Where the Answers Are" and have been for 20 years_ > _ARSlist: "Where the Answers Are" and have been for 20 years_ > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Re: Long Running Query
Following solution works fine except db2 database, for db2 customer has to run runstat on db for T table in question and understand where exactly the time is taken, based on the output of runstat customer can implement indexes. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Ashtaputre, Anay Sent: Wednesday, January 09, 2013 1:54 PM To: arslist@ARSLIST.ORG Subject: Re: Long Running Query ** Following Defect & Knowledge article can be referred - SW00443560 KA380785 Following fields will need to be indexed - 'C23009', 'C100079' & 'C7' Indexing to be done at the db level for better performance and after indexing customer should get 'execution plan' aka 'explain plan' of the query by running it on db directly and see the time difference between pre-indexing and post-indexing. That will tell you whether or not solution implemented is helping. Thanks Anay.. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Vishal Navale Sent: Wednesday, January 09, 2013 1:13 AM To: arslist@ARSLIST.ORG Subject: Re: Long Running Query ** Thanks Rick for the answer. Modify the query and indexing - do you have some suggestions for how to do this. Thanks Vishal On Tue, Jan 8, 2013 at 2:00 PM, Rick Cook mailto:remedyr...@gmail.com>> wrote: modifying the query, _ARSlist: "Where the Answers Are" and have been for 20 years_ _ARSlist: "Where the Answers Are" and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Re: Long Running Query
Following Defect & Knowledge article can be referred - SW00443560 KA380785 Following fields will need to be indexed - 'C23009', 'C100079' & 'C7' Indexing to be done at the db level for better performance and after indexing customer should get 'execution plan' aka 'explain plan' of the query by running it on db directly and see the time difference between pre-indexing and post-indexing. That will tell you whether or not solution implemented is helping. Thanks Anay.. From: Action Request System discussion list(ARSList) [mailto:arslist@ARSLIST.ORG] On Behalf Of Vishal Navale Sent: Wednesday, January 09, 2013 1:13 AM To: arslist@ARSLIST.ORG Subject: Re: Long Running Query ** Thanks Rick for the answer. Modify the query and indexing - do you have some suggestions for how to do this. Thanks Vishal On Tue, Jan 8, 2013 at 2:00 PM, Rick Cook mailto:remedyr...@gmail.com>> wrote: modifying the query, _ARSlist: "Where the Answers Are" and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Re: Long Running Query
Thanks Rick for the answer. Modify the query and indexing - do you have some suggestions for how to do this. Thanks Vishal On Tue, Jan 8, 2013 at 2:00 PM, Rick Cook wrote: > modifying the query, ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Re: Long Running Query
My guess would be that it is refreshing the Overview Console. If you have lots (like 100,000+) being returned, this constantly refreshing table will bog your system down. You might look at reducing table sizes, modifying the query, adjusting indexes, or all three. Rick On Tue, Jan 8, 2013 at 10:57 AM, Vishal Navale wrote: > ** > Hi, > > I am seen below long runnnig query on Remedy Server which causes > performance issues. Does anyone knows what is his query? > > Thanks > Vishal > > > 895060 SQL 218104 Prv:390680 390680 Remedy Application Service Tue Jan > 08 2013 09:20:30.1500 > SELECT TOP 2001 > T1916.C1,C100161,C301626500,C10,C7,C100164,C100217,C100218,C179,C23009,C3 > FROM T1916 WHERE (((T1916.C23009 = N'MAINCHANGE') AND > ((T1916.C100716 = N'ranjselv') OR (T1916.C4 = N'ranjselv') OR (T1916.C4 > = N'ranjselv')) AND ((T1916.C7 = 1) OR (T1916.C7 = 2) OR (T1916.C7 = 3) OR > (T1916.C7 = 4) OR (T1916.C7 = 5) OR (T1916.C7 = 6) OR (T1916.C7 = 7) OR > (T1916.C7 = 8) OR (T1916.C7 = 9))) OR ((T1916.C23009 = N'MAINHELPDESK') > AND (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR > (T1916.C7 = 2) OR (T1916.C7 = 3))) OR ((T1916.C23009 = N'TMSTASK') AND > (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 1000) OR (T1916.C7 = 2000) OR > (T1916.C7 = 3000) OR (T1916.C7 = 4000) OR (T1916.C7 = 5000))) OR > ((T1916.C23009 = N'MAINKNOWLEDGEDATABASE') AND (T1916.C4 = N'ranjselv') > AND (T1916.C7 = 4)) OR ((T1916.C23009 = N'MAINKNOWNERROR') AND > (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR (T1916.C7 > = 2))) OR ((T1916.C23009 = N'MAINPROBLEM') AND (T1916.C4 = N'ranjselv') > AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR (T1916.C7 = 2) OR (T1916.C7 = 3) > OR (T1916.C7 = 4) OR (T1916.C7 = 5 ORDER BY C100161 ASC, 1 ASC > _ARSlist: "Where the Answers Are" and have been for 20 years_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
Long Running Query
Hi, I am seen below long runnnig query on Remedy Server which causes performance issues. Does anyone knows what is his query? Thanks Vishal 895060 SQL 218104 Prv:390680 390680 Remedy Application Service Tue Jan 08 2013 09:20:30.1500 SELECT TOP 2001 T1916.C1,C100161,C301626500,C10,C7,C100164,C100217,C100218,C179,C23009,C3 FROM T1916 WHERE (((T1916.C23009 = N'MAINCHANGE') AND ((T1916.C100716 = N'ranjselv') OR (T1916.C4 = N'ranjselv') OR (T1916.C4 = N'ranjselv')) AND ((T1916.C7 = 1) OR (T1916.C7 = 2) OR (T1916.C7 = 3) OR (T1916.C7 = 4) OR (T1916.C7 = 5) OR (T1916.C7 = 6) OR (T1916.C7 = 7) OR (T1916.C7 = 8) OR (T1916.C7 = 9))) OR ((T1916.C23009 = N'MAINHELPDESK') AND (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR (T1916.C7 = 2) OR (T1916.C7 = 3))) OR ((T1916.C23009 = N'TMSTASK') AND (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 1000) OR (T1916.C7 = 2000) OR (T1916.C7 = 3000) OR (T1916.C7 = 4000) OR (T1916.C7 = 5000))) OR ((T1916.C23009 = N'MAINKNOWLEDGEDATABASE') AND (T1916.C4 = N'ranjselv') AND (T1916.C7 = 4)) OR ((T1916.C23009 = N'MAINKNOWNERROR') AND (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR (T1916.C7 = 2))) OR ((T1916.C23009 = N'MAINPROBLEM') AND (T1916.C4 = N'ranjselv') AND ((T1916.C7 = 0) OR (T1916.C7 = 1) OR (T1916.C7 = 2) OR (T1916.C7 = 3) OR (T1916.C7 = 4) OR (T1916.C7 = 5 ORDER BY C100161 ASC, 1 ASC ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "Where the Answers Are, and have been for 20 years"
UPDATE: Re: 6.3 and Long Running Query
Just an update on this old thread, the 'Large-Result-Logging-Threshold' setting has finally been added to the product docs in 7.6.04 Optimizing and Troubleshooting Guide starting on pg. 100. Jason On Wed, Aug 25, 2010 at 10:32 AM, Jason Miller wrote: > I was going to mention that we should start adding these things to the > Undocumented Config Settings section on ARSWiki (and was going to add it)... > and what do you know? It is already there! > > http://arswiki.org/wiki/Undocumented_Config_Settings > > Jason > > > On Wed, Aug 25, 2010 at 10:10 AM, Jason Miller wrote: > >> Hi Frank, >> >> Add 'Large-Result-Logging-Threshold: 100' to the ar.cfg/ar.conf file. >> >> I *think* the 100 is bytes of memory. NOTE: we added an extra zero >> from Mark Walter's suggestion (POST: "ARS 7.1 server group issue" from >> 2/25/09). >> >> You will end up with something like this in the arthread.log >> >> /* Sun Aug 22 2010 23:25:36.6000 */ Thread Id 7984 (GLEWMSF) large >> result buffer allocation - /Length: 1081258/Entries: 9643/Client Ver: >> >=10/RPC ID: 16988634/User: / >> /* Sun Aug 22 2010 23:25:42.4280 */ Thread Id 3828 (GLEWMSF) large >> result buffer allocation - /Length: 3350577/Entries: 31159/Client Ver: >> >=10/RPC ID: 16988666/User: / >> /* Sun Aug 22 2010 23:55:39.0250 */ Thread Id 3996 (GLEWF) large >> result buffer allocation - /Length: 1724986/Entries: 14665/Client Ver: >> >=10/RPC ID: 16999731/User: /Form: / >> /* Mon Aug 23 2010 11:21:26.4610 */ Thread Id 4436 (GLEWF) large >> result buffer allocation - /Length: 3994125/Entries: 31626/Client Ver: >> >=10/RPC ID: 17364769/User: /Form: / >> >> >> On Wed, Aug 25, 2010 at 9:46 AM, Frank Caruso wrote: >> >>> ** I remember seeing a posting to the list within the last few months >>> about a ARS 6.3 setting in the conf file that would allow the logging of >>> long running queries. I believe this is an undocumented feature. >>> I have tried searching the archives but am not able to find the email. >>> >>> Anybody have or remember the posting? >>> >>> Frank Caruso >>> >>> _attend WWRUG10 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"
RESOLVED: Re: 6.3 and Long Running Query
Thank you. On Wed, Aug 25, 2010 at 1:32 PM, Jason Miller wrote: > ** I was going to mention that we should start adding these things to the > Undocumented Config Settings section on ARSWiki (and was going to add it)... > and what do you know? It is already there! > > http://arswiki.org/wiki/Undocumented_Config_Settings > > Jason > > > On Wed, Aug 25, 2010 at 10:10 AM, Jason Miller wrote: > >> Hi Frank, >> >> Add 'Large-Result-Logging-Threshold: 100' to the ar.cfg/ar.conf file. >> >> I *think* the 100 is bytes of memory. NOTE: we added an extra zero >> from Mark Walter's suggestion (POST: "ARS 7.1 server group issue" from >> 2/25/09). >> >> You will end up with something like this in the arthread.log >> >> /* Sun Aug 22 2010 23:25:36.6000 */ Thread Id 7984 (GLEWMSF) large >> result buffer allocation - /Length: 1081258/Entries: 9643/Client Ver: >> >=10/RPC ID: 16988634/User: / >> /* Sun Aug 22 2010 23:25:42.4280 */ Thread Id 3828 (GLEWMSF) large >> result buffer allocation - /Length: 3350577/Entries: 31159/Client Ver: >> >=10/RPC ID: 16988666/User: / >> /* Sun Aug 22 2010 23:55:39.0250 */ Thread Id 3996 (GLEWF) large >> result buffer allocation - /Length: 1724986/Entries: 14665/Client Ver: >> >=10/RPC ID: 16999731/User: /Form: / >> /* Mon Aug 23 2010 11:21:26.4610 */ Thread Id 4436 (GLEWF) large >> result buffer allocation - /Length: 3994125/Entries: 31626/Client Ver: >> >=10/RPC ID: 17364769/User: /Form: / >> >> >> On Wed, Aug 25, 2010 at 9:46 AM, Frank Caruso wrote: >> >>> ** I remember seeing a posting to the list within the last few months >>> about a ARS 6.3 setting in the conf file that would allow the logging of >>> long running queries. I believe this is an undocumented feature. >>> I have tried searching the archives but am not able to find the email. >>> >>> Anybody have or remember the posting? >>> >>> Frank Caruso >>> >>> _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> >> > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: 6.3 and Long Running Query
I was going to mention that we should start adding these things to the Undocumented Config Settings section on ARSWiki (and was going to add it)... and what do you know? It is already there! http://arswiki.org/wiki/Undocumented_Config_Settings Jason On Wed, Aug 25, 2010 at 10:10 AM, Jason Miller wrote: > Hi Frank, > > Add 'Large-Result-Logging-Threshold: 100' to the ar.cfg/ar.conf file. > > I *think* the 100 is bytes of memory. NOTE: we added an extra zero > from Mark Walter's suggestion (POST: "ARS 7.1 server group issue" from > 2/25/09). > > You will end up with something like this in the arthread.log > > /* Sun Aug 22 2010 23:25:36.6000 */ Thread Id 7984 (GLEWMSF) large > result buffer allocation - /Length: 1081258/Entries: 9643/Client Ver: > >=10/RPC ID: 16988634/User: / > /* Sun Aug 22 2010 23:25:42.4280 */ Thread Id 3828 (GLEWMSF) large > result buffer allocation - /Length: 3350577/Entries: 31159/Client Ver: > >=10/RPC ID: 16988666/User: / > /* Sun Aug 22 2010 23:55:39.0250 */ Thread Id 3996 (GLEWF) large > result buffer allocation - /Length: 1724986/Entries: 14665/Client Ver: > >=10/RPC ID: 16999731/User: /Form: / > /* Mon Aug 23 2010 11:21:26.4610 */ Thread Id 4436 (GLEWF) large > result buffer allocation - /Length: 3994125/Entries: 31626/Client Ver: > >=10/RPC ID: 17364769/User: /Form: / > > > On Wed, Aug 25, 2010 at 9:46 AM, Frank Caruso wrote: > >> ** I remember seeing a posting to the list within the last few months >> about a ARS 6.3 setting in the conf file that would allow the logging of >> long running queries. I believe this is an undocumented feature. >> I have tried searching the archives but am not able to find the email. >> >> Anybody have or remember the posting? >> >> Frank Caruso >> >> _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ > > > ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
Re: 6.3 and Long Running Query
Hi Frank, Add 'Large-Result-Logging-Threshold: 100' to the ar.cfg/ar.conf file. I *think* the 100 is bytes of memory. NOTE: we added an extra zero from Mark Walter's suggestion (POST: "ARS 7.1 server group issue" from 2/25/09). You will end up with something like this in the arthread.log /* Sun Aug 22 2010 23:25:36.6000 */ Thread Id 7984 (GLEWMSF) large result buffer allocation - /Length: 1081258/Entries: 9643/Client Ver: >=10/RPC ID: 16988634/User: / /* Sun Aug 22 2010 23:25:42.4280 */ Thread Id 3828 (GLEWMSF) large result buffer allocation - /Length: 3350577/Entries: 31159/Client Ver: >=10/RPC ID: 16988666/User: / /* Sun Aug 22 2010 23:55:39.0250 */ Thread Id 3996 (GLEWF) large result buffer allocation - /Length: 1724986/Entries: 14665/Client Ver: >=10/RPC ID: 16999731/User: /Form: / /* Mon Aug 23 2010 11:21:26.4610 */ Thread Id 4436 (GLEWF) large result buffer allocation - /Length: 3994125/Entries: 31626/Client Ver: >=10/RPC ID: 17364769/User: /Form: / On Wed, Aug 25, 2010 at 9:46 AM, Frank Caruso wrote: > ** I remember seeing a posting to the list within the last few months about > a ARS 6.3 setting in the conf file that would allow the logging of long > running queries. I believe this is an undocumented feature. > I have tried searching the archives but am not able to find the email. > > Anybody have or remember the posting? > > Frank Caruso > > _attend WWRUG10 www.wwrug.com ARSlist: "Where the Answers Are"_ ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
6.3 and Long Running Query
I remember seeing a posting to the list within the last few months about a ARS 6.3 setting in the conf file that would allow the logging of long running queries. I believe this is an undocumented feature. I have tried searching the archives but am not able to find the email. Anybody have or remember the posting? Frank Caruso ___ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug10 www.wwrug.com ARSlist: "Where the Answers Are"
