PL/SQL - How can I check if a Package / Procedure / Function is being used?

Question

Hello to whoever sees this. First of all, my knowledge on PL/SQL is pretty much basic to layman. I can read the code, do queries and use PL/SQL Developer to do my research. That's all I'm needed to do.

All I need to know is if a Package/Procedure/Function is being used and/or, last time it was used. Is there a way to see it through queries or functionality of PL/SQL Developer?

Side note: I've found the question bellow, but it didn't fit my needs/ didn't fully understand how the awnsers there could be of use: How can you tell if a PL/SQL Package, Procedure, or Function is being used?

Answer 1

If all you want is the number of times it has been used (from some arbitrary reset time) and the date it was last used, create a couple of private package variables.

package body package_name as
    F1_Use_Count integer := 0;
    F1_Last_Used timestamp;
    ...
    function F1 ...
        F1_Use_Count := F1_Use_Count + 1;
        F1_Last_Used := SysTimestamp;
        ... the rest of the function
     end F1;

     -- You also want to add
     function Get_F1_Use_Count return integer is begin
         return F1_Use_Count;
     end;
     function Get_F1_Last_Used return timestamp is begin
         return F1_Last_Used;
     end
     proc Reset_F1_Stats is begin
         F1_Use_Count := 0;
         F1_Last_Used := null;
     end;

     -- Or all three above could be done with
     proc Get_Reset_F1_Stats( Use_count out integer, Use_Date out timestamp ) is begin
         Use_count := F1_Use_Count;
         Use_Date := F1_Last_Used;
         F1_Use_Count := 0;
         F1_Last_Used := null;
      end;
end package_name;

EDIT: To "session-proof" the action, write the values into a table instead of package variables.

CREATE TABLE Access_Stats(
    Proc_Id         VARCHAR2( 32 ) NOT NULL, 
    Access_Count    INTEGER DEFAULT 0 NOT NULL,
    Access_Date     DATE DEFAULT SYSDATE NOT NULL, 
    CONSTRAINT PK_TEST PRIMARY KEY( Proc_Id )
);

Inside the package body:

Proc Set_Stats( PName Access_Stats.Proc_Id%type ) is begin
    MERGE INTO Access_Stats a
    USING(
        SELECT 1 FROM dual ) tt
    ON( a.Proc_Id  = Upper( PName ))
    WHEN MATCHED THEN
        UPDATE
            SET access_count  = access_count + 1,
                access_date   = SYSDATE
    WHEN NOT MATCHED THEN
        INSERT( Proc_Id, access_count )
        VALUES( Upper( PName ), 1 );

    Commit;
END;

Then in all the functions and procedures you want to track, just make sure they start out with a call to the Set_Stats proc:

Proc WhatEver...
    Set_Stats( 'whatever' );
...

The getters would also have to be changed to read from the table. I would change the Get_Reset_F1_Stats proc to a more general Get_Reset_Stats version:

 proc Get_Reset_Stats(
             PName Access_Stats.Proc_Id%type,
             Use_count out integer,
             Use_Date out timestamp )
 is begin
     Select  Access_Count, Access_Date
         into Use_count, Use_Date
     From    Access_Stats
     where   Proc_Id = Upper( PName );
     update  Access_Stats
         set Access_Count = 0
     where   Proc_Id = Upper( PName );
 end;

The Get_Reset_Stats could just delete the row for the procedure being tracked, but by resetting the count to zero, the date of the reset is maintained. If you add other fields such as the user who executed the proc, the person who last executed (or reset) the procedure/function being tracked can also be maintained.

My standard caveat applies: the code shown above is designed more for illustration and is not presented as production-ready code. Modify to fit your own particular standards.

Answer 2

You have a choice between homespun and use Oracle's built-in facility.

The most reliable and accessible way I have come across for this type of stat is the home-spun code logging into into the procedures of interest by a simple NOLOGGING INSERT into a suitable table. Don't bother trying a complex summing as that will add overhead, just a simple INSERT and then use the reporting side to summarize as required. You can add timestamps and basic session info too .

Another novel approach I have seen which I quite liked was where they created a sequence corresponding to each procedure of interest and simply selected NEXTVAL at the start of each procedure. This won't give you historic/time based stats; you would add that to the reporting side, and you will need to understand the effect the CACHE on the sequence could have.

Personally, I like to see some sort of logging in code as it helps with support/debug issues, providing real insight into the day-in-the-life of production system. Such information can drive improvements.

Having said all that, Oracle does maintain a rich set of stats but the extraction and interpretation can be esoteric. If you have access to OEM/Grid COntrol (or whatever they call the web-based management console these days), you can see stats between time-frames, and drill down to individual statements and procedures. But this takes a little practice and know-how before you know what to look for and how to get it.

You can try rolling your own queries specifically targeting the procedures of interest. You would start with V$SQL to get the SQL_ID of the statements, then link this to DBA_HIST_SQLSTAT which maintains a snapshot history of statistics including total executions and executions since last snapshot.

If you search for DBA_HIST_SQLSTAT and execution I'm sure you will soon find a query to get you started. You will need to be granted access to these views if you are not the DBA.

Answer 3

You cannot tell with 100% certainty

• You can see the last time a package/procedure was compiled.
• you can see what other objects depend on it
• you can search through the packaged code for dynamic sql that refers to it as this will not show up in the dependencies
• you cannot tell if a report calls the object unless you look in the reports
• you cannot tell if an external script calls it unless you search in the scripts
• you cannot tell if the object is called by an external program