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.C1000000079 = :"SYS_B_00") OR (T1161.C1000000079 = :"SYS_B_01") OR (T1161.C1000000079 = :"SYS_B_02") OR (T1161.C1000000079 = :"SYS_B_03") OR (T1161.C1000000079 = :"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.C1000000560 >= :"SYS_B_13") OR (:"SYS_B_14" = :"SYS_B_15")) AND ( (T1161.C1000000560 <= :"SYS_B_16") OR (:"SYS_B_17" = :"SYS_B_18")) AND ( (T1161.C1000000001 = :"SYS_B_19") OR (:"SYS_B_20" = :"SYS_B_21")) AND ( (T1161.C1000003009 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" The information in this email may contain confidential material and it is intended solely for the addresses. Access to this email by anyone else is unauthorized. If you are not the intended recipient, please delete the email and destroy any copies of it, any disclosure, copying, distribution is prohibited and may be considered unlawful. Contents of this email and any attachments may be altered, Statement and opinions expressed in this email are those of the sender, and do not necessarily reflect those of Saudi Telecommunications Company (STC). _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org "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"
