Hi Jack,
I'm not sure you want me to answer the question on idiocy. The challenge of
the logic required to isolate what processes are legitimate and which ones
should be killed without alienating your users is a folly at best and could
create create a situation where management may end up questioning the
legitimacy of having a DBA team....:)
I would recommend reporting on the wasted resource usage and trending to
justify fixing the SQL in question add a better course of action.
Kellyn Pot'Vin-Gorman
Consulting Member of Technical Staff, SC at Oracle
On Feb 19, 2016 11:34 AM, "Jack Applewhite" <jack.applewhite@xxxxxxxxxxxxx>
wrote:
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.
