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
[image: Kellyn Pot'Vin on about.me]
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
<http://about.me/dbakevlar>
On Fri, Feb 19, 2016 at 2:33 PM, Jack Applewhite <
jack.applewhite@xxxxxxxxxxxxx> 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@xxxxxxxxx>
*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@xxxxxxxxxxxxx> wrote:
Glad I ran the idea out to this excellent forum. The deal-breaker is thatConfidentiality Notice: This email message, including all attachments, is
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@xxxxxxxx>
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 todo 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: @OracleSK
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.
