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
Austin I.S.D. - MIS Department
512.414.9250 (wk)
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.
