As I said, if you have the tuning pack you can get the peeked binds for a sql
from v$sql_monitor:
SQL> select binds_xml from v$sql_monitor where sql_id='19h1955fbud4d' and
SQL_EXEC_ID=16777216;
BINDS_XML
----------------------------------------------------------------------------------------------------------
<binds><bind name=":OBJN" pos="1" dty="2" dtystr="NUMBER" maxlen="22"
len="5">2088469</bind></binds>
Of course, that only works for monitored sql - because of MONITOR hint or
because cpu or IO time > 5 seconds. Based on your description of the problem,
the latter doesn’t seem to be a limitation.
On Feb 19, 2016, at 1:06 PM, Jack Applewhite <jack.applewhite@xxxxxxxxxxxxx>
wrote:
Glad I ran the idea out to this excellent forum. The deal-breaker is that the
values in v$SQL_Bind_Capture can be old. I thought they were up-to-date for
each execution of the duplicate SQL. We've been using a modified (it shows
SID, PID, and Username) version of the SQL Developer "Active Sessions" report
to find and kill the offending sessions. We'll have to be more careful with
that.
Kellyn's concern about our possibly annoying Management is appreciated, but
we have their full support. They love it that we find ways to improve
performance of our various systems, especially the key Student Information
System. However, we can't be killing "innocent bystanders" because of stale
bind variable values.
We'll have to find another method, so I'm still open to ideas.
Thanks.
----
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)
________________________________________
From: Stefan Koehler <contact@xxxxxxxx>
Sent: Friday, February 19, 2016 1:45 PM
To: oracle-l; Jack Applewhite
Subject: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind
Var. Values?
Hi Jack,
I'm trying to craft a query using v$Session and v$SQL_Bind_Capture to do
this automatically, looking for duplicate SQL_IDs with duplicate bind
variable values.
This will not work as V$SQL_BIND_CAPTURE does not provide the information you
are looking for. For more information please check Jonathan's blog post
and especially the comment section:
https://jonathanlewis.wordpress.com/2008/07/24/bind-capture/
However in theory there is a technical solution for your request. The
currently used bind variables can be dumped with an errorstack trace as it
includes cursor information. Afterwards you have to grep the binds and
compare for all the corresponding processes which are running the particular
SQL. This is how it would technically work, but i strongly disagree with this
approach / solution.
I strongly recommend Kellyn's approach to find and fix the root cause. It
saves resources, time and makes the application more stable.
Best Regards
Stefan Koehler
Freelance Oracle performance consultant and researcher
Homepage: http://www.soocs.de
Twitter: @OracleSK
Jack Applewhite <jack.applewhite@xxxxxxxxxxxxx> hat am 19. Februar 2016 um
19:34 geschrieben:
Environment: 11.2.0.4 Enterprise on Oracle Linux 5.11.
We have a mix of 3rd Party OTS Apps and home-grown Apps. In both cases we
are frequently plagued by impatient Users clicking Go or whatever,
launching the same SQL several times in a row. We have no control over the
Vendor Apps and we're thumping our Developers on the head frequently to
build in some prevention for that, but, of course, they're slow to respond
to us.
Often these duplicates chew up lots of CPU because they're inefficient to
begin with - slow, which is why Users multi-click. We find them, verify
they're duplicates, and kill them, but that's very time-consuming.
I'm trying to craft a query using v$Session and v$SQL_Bind_Capture to do
this automatically, looking for duplicate SQL_IDs with duplicate bind
variable values. I'm looking to pivot the rows in v$SQL_Bind_Capture to
columns for easier comparison. The pivot SQL has to be dynamic, since
different SQLs have different numbers of bind variables, but I found an Ask
Tom posting on how. I'm thinking that, if I can find these sets of
duplicates, I can automatically kill all but the first-launched.
I've Googled and looked around, but am finding nothing about such an
automated process. Is there one out there? Am I an idiot for trying to do
this?
Any guidance would be appreciated.
TIA
----
Jack C. Applewhite - Database Administrator
Confidentiality Notice: This email message, including all attachments, is for
the sole use of the intended recipient(s) and may contain confidential
student and/or employee information. Unauthorized use of disclosure is
prohibited under the federal Family Educational Rights & Privacy Act (20
U.S.C. §1232g, 34 CFR Part 99, 19 TAC 247.2, Gov’t Code 552.023, Educ. Code
21.355, 29 CFR 1630.14(b)(c)). If you are not the intended recipient, you may
not use, disclose, copy or disseminate this information. Please call the
sender immediately or reply by email and destroy all copies of the original
message, including attachments.
�m� 0��zX����y�Z��譧%
