Re: Tracking Procedures

On May 15, 3:38 pm, sybra..._at_hccnet.nl wrote:
> On 15 May 2007 12:03:10 -0700, "ame..._at_iwc.net" <ame..._at_iwc.net>
> wrote:
>
> >What about a script to parse v$sql every 3 minutes to get the names of
> >the stored procedures that are running or have run, then I can trace
> >it that way........
>
> >I assume that entries stay in v$sql for a bit of time once they are
> >done running???
>
> Horrible suggestion, as you would temporarily lock the library cache.
>
> Actually you could very easily 'instrument' those stored procedures,
> by wrapping them up in one new procedure per procedure, logging it's
> call in a table, and calling the original procedure.
> Obviously you would need a second user and synonyms to have the
> application call your wrappers.
> I know the idea is horrible, but as you seem to be already in an
> undocumented mess, it doesn't matter that much, does it.
>
> BTW did you consider using dba_dependencies?
>
> --
> Sybrand Bakker
> Senior Oracle DBA

Call me ignorant, but why would a query on v$sql lock the library cache?

dba_dependencies seems good to see how things are related and what uses what, but it cannot show me what is being executed and what is not. Received on Thu May 17 2007 - 08:36:57 CDT