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
youll 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 youre 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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@xxxxxxxxxxxxxxxxx>
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 its included with the enterprise
edition. Im 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>
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>
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-Gormans background and interests.
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 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@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 dothis automatically, looking for duplicate SQL_IDs with duplicate bind
variable values.
