I sorry. I remember we had some query before v$sql_bind_capture came along.
Can'the find it anymore. Probably deleted it. I has to do with cursors. That
was the drawback. It was only available while the cursor lasted. Hardly ever
used it.
Regards
Lothar
Von meinem Samsung Gerät gesendet.
-------- Ursprüngliche Nachricht --------
Von: Jack Applewhite <jack.applewhite@xxxxxxxxxxxxx>
Datum: 21.02.2016 05:22 (GMT+01:00)
An: "oracle-l@xxxxxxxxxxxxx" <Oracle-L@xxxxxxxxxxxxx>
Betreff: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind
Var. Values?
Lothar and Stefan,
I may pursue some tracing method, but that involves a lot of coding for
capturing, grepping, and comparing pieces and parts. I'm not sure I could ever
make it dynamic enough to handle the wide variety of SQL and numbers of bind
variables we encounter.
Lothar,
The v$SQL_Bind_Data view can only be queried within a session. It doesn't
provide visibility into other sessions' SQL. I looked at the view definition
and discovered some puzzling things. In our 11.2.0.4 Enterprise DBs the
following Public Synonyms
V$SQL_Bind_Capture
V$SQL_Bind_Data
V$SQL_Bind_Metadata
all point to the equivalent Sys-owned V_$SQL* views - the underlying
"underscore" views.
However, when you look in the view SQL for those views, they're selecting from
O$SQL_Bind_Capture
V$SQL_Bind_Data
V$SQL_Bind_Metadata
It's not a typo that I wrote O$SQL_Bind_Capture, that's what's there - in every
database. Is it the same in your 11gR2 DBs? I don't understand that at all.
The Pub Syns point to the views, but the views point to the Pub Syns. How can
that be? Also, what the
heck is O$SQL_Bind_Capture? I can't find that object querying every which way
I know how.
That askTom article is very old - 2002 - and refers to file system tracing,
which is doable, but not at all up-to-the-minute. Tracing is really not an
option for us. By the time we'd trace the sessions involved in a cluster of
duplicate SQLs, they might be
gone and the damage to performance already done.
Raza,
"Communicate with the user group and let them know implication of their
actions" is exactly what we do over and over, via the Support and Development
groups responsible for each
application area. In the case of the Student Info. System, the end-user group
is, as I've said, 10,000 - 12,000 harried, overworked, overregulated,
underpaid, Teachers, Counselors, Administrators, etc. When they're facing
deadlines to get grades posted, finalize
report cards, generate reports for Damagement, etc. I'm NOT going to be the
one to tell them to have patience with the critical (for them) report that ran
in seconds last week, but now is taking FOREVER because the deadline is looming
for everyone else as
well and they're running it, too. They don't know why and it's not their fault
that some Idiot Developers didn't include simple code to keep them from
multi-clicking and stacking up the batch request queue. We're all in agreement
as to the root cause, so let's
focus on being Florence Nightengales to swoop in and bind the wounds.
I want to make those often-desperate front-line Educators' jobs easier -
without they're knowing it or having to think about it.
Let me say that I am very grateful for all the recommendations offered by folks
on this list that I hold in high regard. I will follow the leads you've given
and, eventually, find something that works. Still, if you have any other
guidance, I'm eager to get
it.
Thanks so much.
----
Jack C. Applewhite - Database Administrator
Austin I.S.D. - MIS Department
512.414.9250 (wk)
From: Lothar Flatz <l.flatz@xxxxxxxxxx>
Sent: Saturday, February 20, 2016 5:59 AM
To: Jack Applewhite
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind
Var. Values?
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/.
sql_trace | Oracle Scratchpad
jonathanlewis.wordpress.com
[…] published a note yesterday about enabling SQL trace system-wide for a
single statement – and got a response on twitter from Bertrand Drouvot ...
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-Gorman
about.me/dbakevlar
Kellyn Pot'Vin-Gorman (dbakevlar) on about.me
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> 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 do this
automatically, looking for duplicate SQL_IDs with duplicate bind
variable values.
