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

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 20 Feb 2016 19:32:12 -0500
Message-ID: <02f701d16c3f$508ccfc0$f1a66f40$_at_rsiz.com>



Because you cannot control the code you therefore need to look into the server-side result cache. Whether or not that proves useful with your vendor spawned queries depends a lot on the texture of your access. Since you cannot modify the queries (except possibly view definitions, where you possibly could include appropriate result cache hints), it seems likely you’ll need to use AUTO. Sometimes that causes management problems, especially mixed with automatic memory granule re-assignment. This is the first link that came up that seemed relevant and well written:  

http://www.oracle.com/technetwork/articles/datawarehouse/vallath-resultcache -rac-284280.html  

Unless you’re using RAC the RAC things are not relevant, but the article seems relevant whether or not you use RAC.  

Good luck. As others have mentioned, this is a symptom fix. In particular a big part of this is a PEBKAC problem. Nonetheless, service-side caching potentially will make the response to the non-first inquirer very fast. If anything triggers a potential change to a dependency, then the query runs again. In the event of quickly changing data that can make this feature next to useful. Your mileage may vary.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jack Applewhite
Sent: Saturday, February 20, 2016 5:18 PM To: MacGregor, Ian A.
Cc: oracle-l
Subject: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind Var. Values?  

Ian,  

After some digging I found info. about "SQL Query Result Cache" and "PL/SQL Function Result Cache" in Concepts, which lead to the DBMS_Results_Cache supplied PL/SQL package. Very interesting and I can see that it could be useful for performance. However, unless you set the Result_Cache_Mode init parameter to FORCE, which caches ALL Selects (a big performance hit), you have to include the Result_Cache hint. Will explore further, but we'll never get our Vendors to change their code. Also, it doesn't look like I can get the session info. on the duplicate SQLs and bind values.  

Thanks a bunch for pointing this out.



Jack C. Applewhite - Database Administrator Austin I.S.D. - MIS Department
512.414.9250 (wk)  

From: MacGregor, Ian A. <ian_at_slac.stanford.edu> Sent: Friday, February 19, 2016 4:36 PM
To: Jack Applewhite
Cc: oracle-l
Subject: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind Var. Values?  

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> 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> 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
<http://d3mod6n032mdiz.cloudfront.net/thumb2/d/b/a/dbakevlar/dbakevlar-105x7 0.jpg>  

Kellyn Pot'Vin-Gorman

about.me/dbakevlar  

 <http://about.me/dbakevlar> Kellyn Pot'Vin-Gorman (dbakevlar) on about.me

about.me <http://about.me/>

View Kellyn Pot'Vin-Gorman on about.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> 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 (wk)  

From: Chris Stephens <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> 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 <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.  

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 Sun Feb 21 2016 - 01:32:12 CET

Original text of this message