tracking functions

[Zeroanarchy]

Hi,

Wondering if there is any method available code or program to track the use of a oracle function. I currently have over 150 functions but can not work out (without a lot of manually checking) which ones are in use, the last time they where used and how often they get used.

has anyone else come across this problem or found a solution?

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[engineer2100]

just check the User_source table.

SELECT NAME,TEXT FROM USER_SOURCE WHERE UPPER(TEXT) LIKE
 '%UPPER%';

Let me know if this is what you were looking for.

 

[Zeroanarchy]

Thanks engineer2100 for your help. I have had a look at User_Source and I can not find any reference to a time or date value. Is there a method of determining the time and date of the last time the function ran in this table?

Cheers
zeroanarchy

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[engineer2100]

I am afraid AFAIK there isn't such a feature that will tell you how often a particular function was used.

 

[Zeroanarchy]

is there a method one can use. For example a piece of code I can add in a function to track it's use. Or is there perhaps a program that is able to do the job?

Thanks in advance.

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[engineer2100]

None I know of!

there is V$SQL and V$SQLAREA where in you can see if the PL/SQL procedure/function that is using the SQL Function has run or not and when it was last run.

-Engi

 

[Zeroanarchy]

Thanks engineer2100 for your help. Little surprised oracle has not come up with a method to manage stored procedures and functions.

In relation to the v$SQL and the V$SQLAREA, unfortunately both only cache the PL/SQL procedure/function. They don't hold any information relating to the last time it was run nor the number of times it gets run. What it does do is cache the calls, and it will drop the calls from the cache if it has not been requested for an undefined period of time. It does tell me how long it takes to run the query but not much else.

Thanks again for your time and help.

Cheers

ZeroAnarchy
Experience is a wonderful thing. It enables you to recognize a mistake
when you make it again.

 

[engineer2100]

yes it does only that. Your requirement is not catered to as i see it.