Re: Tracking Procedures

On 17 May 2007 06:36:57 -0700, "amerar_at_iwc.net" <amerar_at_iwc.net> wrote:

>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?
>

As the results provided from v$sql *come* from 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.
>

I don't agree with you. You aren't telling me you have 2000 procedures, who don't call any other procedure, do you? There must be 'top-level' procedures (exposed to the end-user) and there must be procedures who never called directly by the end-user. Those should be in dba_dependencies.
Consequently, a procedure which is *NOT* in dba_dependencies is potentially
a) either exposed to the end-user
b) not being called at all.

-- 
Sybrand Bakker
Senior Oracle DBA