Hi,
I think of 2 ways to fulfill your request. You can trace certain sql
ids, like described here :
https://jonathanlewis.wordpress.com/2014/05/22/sql_trace/.
You can also forge a query on the basis of this query:
select b.sql_text, a.bind_vars, c.datatype, c.value
from v$sql_cursor a, v$sql b, v$sql_bind_data c
where b.address = a.parent_handle
and a.curno = c.cursor_num
;
to get your bind variables the cursor needs to exist and the
information will disappear quickly. You might have a look here
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4254158186083.
The question is how to execute the query. You could use VDP to start is
your just use a monitoring script. In VDP you would not even need to
kill that session. I will not go to the details how to code this. I
think it can be done.
*But: I side my fellow oakis Kellyn and Stefan. It would be just curing
symptoms here. If the queries will be faster users will not send them
twice. It normally pays off to have a professional performance
specialist looking at your issues.*
It is a know fact that the environment you describe is challenging. That
does not mean it can't be fixed. Normally it can.
BTW: Are you aware of the good free and low budget alternatives of
diagnostic and tuning pack?
Thanks
Lothar
On 19.02.2016 22:49, Jack Applewhite 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
Kellyn Pot'Vin-Gorman
about.me/dbakevlar
<http://about.me/dbakevlar>
<http://about.me/dbakevlar>
Kellyn Pot'Vin-Gorman (dbakevlar) on about.me <http://about.me/dbakevlar>
about.me
View Kellyn Pot'Vin-Gorman on about.me. 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.
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
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.
