AW: Re: Find / Kill Sessions Running Duplicate SQL with Duplicate Bind Var. Values?

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_at_austinisd.org> Datum: 21.02.2016 05:22 (GMT+01:00) An: "oracle-l_at_freelists.org" <Oracle-L_at_freelists.org> 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_at_bluewin.ch>

Sent: Saturday, February 20, 2016 5:59 AM

To: Jack Applewhite

Cc: oracle-l_at_freelists.org

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_at_gmail.com>

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_at_austinisd.org> 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_at_gmail.com>

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_at_austinisd.org> 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_at_soocs.de>

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: _at_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.

--
http://www.freelists.org/webpage/oracle-l