Re: OT: What about creating a repository of scripts of oracle-l

From: Ethan Post <post.ethan_at_gmail.com>
Date: Fri, 13 Jun 2014 09:08:35 -0500
Message-ID: <CAMNhnU0uF3nkwTs+o58okcOD3EQjSiv1cbRMSYbg6=F+HujGhA_at_mail.gmail.com>

I think Jaun's script inserts the bind variables into the SQL, didn't see that at your link but it is a long read and I didn't go through it all. First off, looking for binds is not something I do all that often, maybe once every month or two at most, however why do it at all, a script to do this is something I should have in my toolkit, thankfully I maintain such a toolkit and I will likely ad a script post haste.

Thinking here I will likely implement something akin to the following.

• Problem SQL's are already automatically detected and monitored.
• Dump a full SQL of problem SQL with binds every N hours to file.
• Will likely create a SQL_ID.trc file in the alert log directory for each SQL with an issue. In the databases I manage this is not more than a handful.
• I can always go grab a file and get everything I need to run a tuning session or see history etc...
• Alert on a SQL ID will also send the above file compressed as attachment.

Those are just some thoughts. My goal is just to never have to run the script in the first place. Every time I have to stop and run a script against a single database I see a bottleneck.

EP

On Fri, Jun 13, 2014 at 4:18 AM, Norman Cobley <nc5_at_sanger.ac.uk> wrote:

> Juan,
> There is an excellent script to do this already out there from Tom Kyte
> (and probably many many others)
>
> https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_
> QUESTION_ID:767025833873
>
> I used a modified version which includes bind variable values
>
> I guess one benefit of such a repository would be to avoid reinventing the
> wheel
>
>
> On 13/06/2014 00:27, Juan Carlos Reyes Pacheco wrote:
>
>>
>> A query to get the current execution of a user and the value of the
>> binding
>> I took me some hours to make it. if some one could improve it will be
>> nice.
>>
>>
>
>
> --
> The Wellcome Trust Sanger Institute is operated by Genome Research
> Limited, a charity registered in England with number 1021457 and a company
> registered in England with number 2742969, whose registered office is 215
> Euston Road, London, NW1 2BE. --
> http://www.freelists.org/webpage/oracle-l
>
>
>

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

Received on Fri Jun 13 2014 - 16:08:35 CEST