Just a note ... He probably got the query from a DBA as this is what you see in the V$SQL view in Oracle when running with the Oracle-Cursor-Sharing option of SIMILAR or FORCE. With cursor sharing set to FORCE or SIMILAR, Oracle replaces literal values with system generated bind variables in the SQL statement. This increases soft parsing but significantly reduces hard parsing. The only way to really see what is sent to the database is to turn on the SQL log on the ARS server.
Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[email protected]] On Behalf Of Mueller, Doug Sent: Monday, February 28, 2011 11:57 AM To: [email protected] Subject: Re: Huge queries hogging up the database 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 _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"
