As we stepped over the same bind variables recently in a similar issue:
You should check out contents of Oracle's v$sql_bind_capture - you will most 
likely find the contents of your bind variables in there, as long as you have 
the sql id of the statement.

In our case, we found several malformed search qualifications leading to full 
table scans.

Cheers,
Thomas

________________________________
Von: Action Request System discussion list(ARSList) 
[mailto:[email protected]] Im Auftrag von Axton
Gesendet: Montag, 28. Februar 2011 21:45
An: [email protected]
Betreff: Re: Huge queries hogging up the database

**  :"SYS_B_XX" is an Oracle bind variable.  Oracle strips all the literals and 
replaced them with bind variables because CURSOR_SHARING is set to SIMILAR or 
FORCE.  This is ok.  The Remedy SQL logs should be used to get the SQL 
statement with the literal values.

This could be a case where a user is searching for "%" simply to return all 
records.

To Doug's point, the T28.C536870993 LIKE "SYS_B_67" part of your query is 
simply an inefficient query.  It may try to return 1m rows or it may be 
performing a table scan.  Fix that part of the query and the rest should be 
fine.

Axton

On Mon, Feb 28, 2011 at 11:56 AM, Mueller, Doug 
<[email protected]<mailto:[email protected]>> 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]<mailto:[email protected]>] On Behalf Of vikram_k
Sent: Monday, February 28, 2011 1:29 AM
To: [email protected]<mailto:[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<http://www.arslist.org>
attend wwrug11 www.wwrug.com<http://www.wwrug.com> ARSList: "Where the Answers 
Are"

_______________________________________________________________________________
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"_

_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
attend wwrug11 www.wwrug.com ARSList: "Where the Answers Are"

Reply via email to