Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind Var. Values?

From: Chris Stephens <cstephens16_at_gmail.com>
Date: Fri, 19 Feb 2016 15:07:37 -0600
Message-ID: <CAEFL0swtb6r3OK9TAZQ0gaxa7PJy3EPor-OqsEG0eKKL438a-w_at_mail.gmail.com>



Would it be possible to give users a view into what sessions are active in the database they are connected to so they could see their IP or Username or something is active and hence no need to "click" again?

On Fri, Feb 19, 2016 at 2:06 PM, Jack Applewhite < jack.applewhite_at_austinisd.org> 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_at_soocs.de>
> 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: _at_OracleSK
>
> > Jack Applewhite <jack.applewhite_at_austinisd.org> 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.
>

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Feb 19 2016 - 22:07:37 CET

Original text of this message