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@xxxxxxxxxxxxx<mailto:jack.applewhite@xxxxxxxxxxxxx>> 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@xxxxxxxxx<mailto:dbakevlar@xxxxxxxxx>>
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
<http://about.me/dbakevlar>
[Kellyn Pot'Vin on about.me]
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
[https://aboutme.imgix.net/background/users/d/b/a/dbakevlar_1385139292_81.jpg?w=300&q=80&auto=format,redeye&h=140&crop=faces&fit=crop]<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 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@xxxxxxxxxxxxx<mailto: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<tel:512.414.9250> (wk)
________________________________
From: Chris Stephens <cstephens16@xxxxxxxxx<mailto: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.
