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<mailto:jack.applewhite@xxxxxxxxxxxxx>> 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@xxxxxxxx<mailto: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 to do this
automatically, looking for duplicate SQL_IDs with duplicate bind
variable values.