Re: Tracking Procedures

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 15 May 2007 22:18:01 +0200
Message-ID: <464A1579.7090408@gmail.com>

amerar_at_iwc.net schrieb:
> On May 15, 2:03 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:

>> ame..._at_iwc.net schrieb:
>>
>>> Hi,
>>> We have a database with over 2000 stored procedures.  We're pretty
>>> sure not all of them are used.  Short of turning on tracing for a few
>>> weeks, or trying to use TOAD or something to piecemeal everything
>>> together, does Oracle have any way of storing the last time a
>>> procedure was executed??
>>> Thanks!
>> Enable auditing would yield this information among others.
>>
>> Best regards
>>
>> Maxim

>
>
>
> But, how much overhead would auditing put on the database? I do not
> want to generate tons of log files, or tons of any unnecessary I/
> O......
>
>
>

This is the question which you should answer in your test environment ( with more or less realistic test cases). However, in my expirience, auditing don't produce significant performance overhead, if used appropriately.
In your case ( i assume, you only wish to find out, which procedures are *not* accessed for longer time) - i would enable auditing for procedures, except those you know for sure - they are heavily used. Then monitor first time user_audit_object and disable auditing as soon as you identified acces to procedure. So, you would reduce the auditing to hopefully very small amount of rarely used procedures...

Best regards

Maxim Received on Tue May 15 2007 - 15:18:01 CDT