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

Communicate with the user group and let them know implication of their actions - repeat requests will only slow-down their, and other users' requests from completing. In addition, outline a proposal to logout users who have idle connections - after say 5 minutes ?

When users experience "inclusivity", they'll trend towards a more cooperative outlook.

A little carrot, a little stick...can yield considerable results.

Good luck.

On 2/19/2016 2:36 PM, MacGregor, Ian A. wrote:
> Have you looked at implementing a results cache? I must admit that I
> never have; because, I was under the apparently mistaken belief it was
> an extra cost option. However, I just checked and the Oracle
> database licensing information posted for January 2016 says it’s
> included with the enterprise edition. I’m thinking the cache could
> help to identify the problem statements besides returning results
> directly from the cache. My knowledge of how the cache works is
> somewhat less than superficial
>
>
> Ian MacGregor
> SLAC National Accelerator Center
>
>> On Feb 19, 2016, at 1:49 PM, Jack Applewhite
>> <jack.applewhite_at_austinisd.org
>> <mailto:jack.applewhite_at_austinisd.org>> wrote:
>>
>> Kellyn,
>>
>> We have used mviews to substitute for some of our Vendor App's
>> inefficient views. It's helped immensely when we can identify them.
>> However, the duplicate SQL situations can occur in any number of the
>> hundreds of batch reports executed in the SIS or queries from our
>> home-grown Apps. It's just too many possibilities to cover in
>> advance. Our Users keep coming up with new situations to create
>> duplicate SQL sessions. That's why catching them "in the act" has
>> been our goal.
>>
>> Thanks.
>> ----
>> Jack C. Applewhite - Database Administrator
>> Austin I.S.D. - MIS Department
>> 512.414.9250 (wk)
>>
>>
>> ------------------------------------------------------------------------
>> *From:*Kellyn Pot'Vin-Gorman <dbakevlar_at_gmail.com
>> <mailto:dbakevlar_at_gmail.com>>
>> *Sent:*Friday, February 19, 2016 3:40 PM
>> *To:*Jack Applewhite
>> *Cc:*oracle-l
>> *Subject:*Re: Find / Kill Sessions Running Duplicate SQL with
>> Duplicate Bind Var. Values?
>> Would you consider a plan B? :)
>>
>> If these queries are so CPU intensive and are run so often to capture
>> the data, is the data static enough to push to an mview or reporting
>> table instead, which would control the rate at which it was called
>> and simplify the query on the user end?
>>
>> Just another thought....
>> Kellyn
>>
>> Kellyn Pot'Vin on about.me
>>
>> Kellyn Pot'Vin-Gorman
>> about.me/dbakevlar
>>
>> <http://about.me/dbakevlar>
>> <http://about.me/dbakevlar>
>>
>> Kellyn Pot'Vin-Gorman (dbakevlar) on about.me <http://about.me/dbakevlar>
>> about.me <http://about.me/>
>> View Kellyn Pot'Vin-Gorman onabout.me <http://about.me/>.About.me
>> <http://about.me/>makes it easy for you to learn about Kellyn
>> Pot'Vin-Gorman’s background and interests.
>>
>>
>> On Fri, Feb 19, 2016 at 2:33 PM, Jack
>> Applewhite<jack.applewhite_at_austinisd.org
>> <mailto:jack.applewhite_at_austinisd.org>>wrote:
>>
>> Chris,
>>
>> The users of our Student Info. System are Teachers, Counselors,
>> Administrators, Nurses, etc. - about 10,000 - 12,000. They don't
>> have access to anything but the various Apps' GUIs. Anyway,
>> training that many folks on what a database session, view, etc.
>> is would be impossible.
>>
>> Wolfgang,
>>
>> Thanks for the info., but we're a School District and can't
>> afford the add-on Packs.
>>
>> I did look at the v$SQL_Bind_Capture view definition in Reference
>> and, sure enough, it says
>> "To limit the overhead, binds are captured at most every 15
>> minutes for a given cursor."
>>
>> Still looking...
>> ----
>> Jack C. Applewhite - Database Administrator
>> Austin I.S.D. - MIS Department
>> 512.414.9250 <tel:512.414.9250>(wk)
>>
>> ------------------------------------------------------------------------
>> *From:*Chris Stephens <cstephens16_at_gmail.com
>> <mailto:cstephens16_at_gmail.com>>
>> *Sent:*Friday, February 19, 2016 3:07 PM
>> *To:*Jack Applewhite
>> *Cc:*oracle-l; Stefan Koehler
>>
>> *Subject:*Re: Find / Kill Sessions Running Duplicate SQL with
>> Duplicate Bind Var. Values?
>> 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
>> <mailto: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 <tel:512.414.9250> (wk)
>>
>> ________________________________________
>> From: Stefan Koehler <contact_at_soocs.de <mailto: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 <http://www.soocs.de/>
>> Twitter: _at_OracleSK
>>
>>
>> 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.
>>
>>
>> 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