Can we use PL/Scope to find dead and potentially dead PL/SQL code? Yes! And it takes less than a second!

PL/Scope records declarations and usages of procedures and functions, so it should be easy to do a quick comparison of what’s declared and what’s actually used.

Deleting code makes me feel good, because it clears up clutter and usually means I’ve found a better way to perform a task.

So how do I find code that can be deleted? If you have a properly layered hard-shell PL/SQL-application (also often referred to as #SmartDB), PL/Scope will become very useful and making the task fairly easy, provided that you hav a a good structure in your application.

To demonstrate how this can be achieved, I’ve set up a test-case, where I create an object-type, a package, a standalone function and a standalone procedure with a few references between them:

CREATE OR REPLACE TYPE myType AS OBJECT (
   TypeAttribute INTEGER,
   MEMBER PROCEDURE ReferencedTypeProcedure,
   MEMBER FUNCTION UnreferencedTypeFunction RETURN PLS_INTEGER
);
/

CREATE OR REPLACE TYPE BODY myType AS
   MEMBER PROCEDURE ReferencedTypeProcedure IS
   BEGIN
      NULL;
   END;
   
   MEMBER FUNCTION UnreferencedTypeFunction RETURN PLS_INTEGER IS
   BEGIN
     RETURN 42;
   END;
END;
/

CREATE OR REPLACE PACKAGE myPackage AS
   PROCEDURE MyUnReferencedPublicProcedure(pInput PLS_INTEGER);
   PROCEDURE MyReferencedPublicProcedure(pInput PLS_INTEGER);
   FUNCTION  MyUnReferencedPublicFunction(pInput PLS_INTEGER) RETURN PLS_INTEGER;
   FUNCTION  MyReferencedPublicFunction(pInput PLS_INTEGER) RETURN PLS_INTEGER;
   FUNCTION  MyReferencedPublicFunction(pInput VARCHAR2) RETURN VARCHAR2;
END myPackage;
/

CREATE OR REPLACE PACKAGE BODY myPackage AS
   PROCEDURE MyReferencedInternalProcedure(pInput PLS_INTEGER) IS
   BEGIN
     NULL;
   END;

   PROCEDURE MyUnReferencedPublicProcedure(pInput PLS_INTEGER) IS
   BEGIN
     NULL;
   END;

   PROCEDURE MyUnreferencedInternalProcedure(pInput PLS_INTEGER) IS
   BEGIN
     NULL;
   END;

   PROCEDURE MyReferencedPublicProcedure(pInput PLS_INTEGER) IS
      lMyType myType := myType(NULL);
   BEGIN
      lMyType.ReferencedTypeProcedure;
   END;

   FUNCTION MyUnReferencedPublicFunction(pInput PLS_INTEGER) RETURN PLS_INTEGER IS
   BEGIN
     RETURN pInput;
   END;
   
   FUNCTION MyReferencedPublicFunction(pInput PLS_INTEGER) RETURN PLS_INTEGER IS
   BEGIN
      MyReferencedInternalProcedure(pInput);
      RETURN pInput;
   END;

   FUNCTION MyReferencedPublicFunction(pInput VARCHAR2) RETURN VARCHAR2 IS
   BEGIN
      MyReferencedInternalProcedure(pInput);
      RETURN pInput;
   END;
END myPackage;
/

CREATE OR REPLACE FUNCTION MyStandaloneFunction (pInput PLS_INTEGER) RETURN PLS_INTEGER
AS
BEGIN
   RETURN pInput * 2;
END;
/

CREATE OR REPLACE PROCEDURE MyStandaloneProcedure (pInput PLS_INTEGER) AS
BEGIN
   IF MyStandaloneFunction(pinput) > myPackage.MyReferencedPublicFunction(pInput) THEN
      myPackage.MyReferencedPublicProcedure(pInput);
   END IF;
end;
/

First – before you decide to delete code, make sure that your source has been compiled so that PL/Scope-metadata has been collected for all the involved schemas. You want all relevant objects to be compiled with ‘IDENTIFIERS:ALL’ to find both identifiers/references and statements:

SELECT type, plscope_settings, COUNT(*)
FROM ALL_PLSQL_OBJECT_SETTINGS
WHERE OWNER IN ('MySchema1', 'MySchema2')
GROUP BY type, plscope_settings
ORDER BY type, plscope_settings;

If necessary, compile them to collect the PL/Scope metadata:

ALTER ... COMPILE PLSCOPE_SETTINGS='IDENTIFIERS:ALL';

The best is to set these compiler-setting on the database level, if you’re on dev or test. That way, metadata is collected by default.

Now – let’s get to work:

One of several things PL/Scope collects data about is declaration and definition of procedures and functions.

Finding the declaration of all standalone procedures can be done by:

SELECT Object_Type, Object_Name, Type, Name, Signature, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage IN ('DECLARATION')
AND Type IN ('FUNCTION', 'PROCEDURE')
AND Object_Type IN ('FUNCTION', 'PROCEDURE');

OBJECT_TYPE  OBJECT_NAME           TYPE               NAME                  SIGNATURE                              LINE
------------ --------------------- ------------------ --------------------- -------------------------------- ----------
FUNCTION     MYSTANDALONEFUNCTION  FUNCTION           MYSTANDALONEFUNCTION  61E40C36B13171D5AACDC9FA40852B3A          1
PROCEDURE    MYSTANDALONEPROCEDURE PROCEDURE          MYSTANDALONEPROCEDURE E94699DD7B3C10FBE76DFF9C97ACC05B          1

Listing functions and procedures in packages is also easy:

SELECT Object_Type, Object_Name, Type, Name, Signature, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage = 'DECLARATION'
AND Type IN ('FUNCTION', 'PROCEDURE')
AND Object_Type IN ('PACKAGE', 'PACKAGE BODY');

OBJECT_TYPE  OBJECT_NAME TYPE               NAME                            SIGNATURE                              LINE
------------ ----------- ------------------ ------------------------------- -------------------------------- ----------
PACKAGE      MYPACKAGE   FUNCTION           MYREFERENCEDPUBLICFUNCTION      E925718DC7E9F225FF6FC7EC14F9BFEF          6
PACKAGE      MYPACKAGE   FUNCTION           MYREFERENCEDPUBLICFUNCTION      C1DC028F981F2833F5A87E294B52A17A          5
PACKAGE      MYPACKAGE   FUNCTION           MYUNREFERENCEDPUBLICFUNCTION    C47A76539917994464008666B6400C9C          4
PACKAGE      MYPACKAGE   PROCEDURE          MYREFERENCEDPUBLICPROCEDURE     10985A9228A3FF6555AF22C8BD5BF125          3
PACKAGE      MYPACKAGE   PROCEDURE          MYUNREFERENCEDPUBLICPROCEDURE   3FF2BC911D1241E280FD46225FBE4BBC          2
PACKAGE BODY MYPACKAGE   PROCEDURE          MYUNREFERENCEDINTERNALPROCEDURE 6138E62F9BFE6468E21AA584F457948D         12
PACKAGE BODY MYPACKAGE   PROCEDURE          MYREFERENCEDINTERNALPROCEDURE   CBAECA439F3390F40E226F60E563CD38          2

A listing of declarations of functions and procedures; standalone, in packages or in types:

SELECT Object_Type, Object_Name, Type, Name, Signature, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage = 'DECLARATION'
AND Type IN ('FUNCTION', 'PROCEDURE')
ORDER BY Object_Type, Object_Name, Name, Line;

OBJECT_TYPE  OBJECT_NAME           TYPE               NAME                            SIGNATURE                              LINE
------------ --------------------- ------------------ ------------------------------- -------------------------------- ----------
FUNCTION     MYSTANDALONEFUNCTION  FUNCTION           MYSTANDALONEFUNCTION            61E40C36B13171D5AACDC9FA40852B3A          1
PACKAGE      MYPACKAGE             FUNCTION           MYREFERENCEDPUBLICFUNCTION      C1DC028F981F2833F5A87E294B52A17A          5
PACKAGE      MYPACKAGE             FUNCTION           MYREFERENCEDPUBLICFUNCTION      E925718DC7E9F225FF6FC7EC14F9BFEF          6
PACKAGE      MYPACKAGE             PROCEDURE          MYREFERENCEDPUBLICPROCEDURE     10985A9228A3FF6555AF22C8BD5BF125          3
PACKAGE      MYPACKAGE             FUNCTION           MYUNREFERENCEDPUBLICFUNCTION    C47A76539917994464008666B6400C9C          4
PACKAGE      MYPACKAGE             PROCEDURE          MYUNREFERENCEDPUBLICPROCEDURE   3FF2BC911D1241E280FD46225FBE4BBC          2
PACKAGE BODY MYPACKAGE             PROCEDURE          MYREFERENCEDINTERNALPROCEDURE   CBAECA439F3390F40E226F60E563CD38          2
PACKAGE BODY MYPACKAGE             PROCEDURE          MYUNREFERENCEDINTERNALPROCEDURE 6138E62F9BFE6468E21AA584F457948D         12
PROCEDURE    MYSTANDALONEPROCEDURE PROCEDURE          MYSTANDALONEPROCEDURE           E94699DD7B3C10FBE76DFF9C97ACC05B          1
TYPE         MYTYPE                PROCEDURE          REFERENCEDTYPEPROCEDURE         A44E4B1A4C2B9B107A3FAE2D055DE35D          3
TYPE         MYTYPE                FUNCTION           UNREFERENCEDTYPEFUNCTION        F6D636F3E46C2FDDCDA0884325FFEC79          4

PL/Scope also records calls to functions and procedures, which you can list like this:

SELECT Object_Type, Object_Name, Type, Name, Signature, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage = 'CALL';

OBJECT_TYPE  OBJECT_NAME           TYPE               NAME                            SIGNATURE                              LINE
------------ --------------------- ------------------ ------------------------------- -------------------------------- ----------
PACKAGE BODY MYPACKAGE             PROCEDURE          MYREFERENCEDINTERNALPROCEDURE   CBAECA439F3390F40E226F60E563CD38         36
PACKAGE BODY MYPACKAGE             PROCEDURE          MYREFERENCEDINTERNALPROCEDURE   CBAECA439F3390F40E226F60E563CD38         30
PACKAGE BODY MYPACKAGE             PROCEDURE          REFERENCEDTYPEPROCEDURE         A44E4B1A4C2B9B107A3FAE2D055DE35D         20
PACKAGE BODY MYPACKAGE             OBJECT             MYTYPE                          07655F23A77221F1F71FD30F99E06F4D         18
PROCEDURE    MYSTANDALONEPROCEDURE PROCEDURE          MYREFERENCEDPUBLICPROCEDURE     10985A9228A3FF6555AF22C8BD5BF125          4
PROCEDURE    MYSTANDALONEPROCEDURE FUNCTION           MYREFERENCEDPUBLICFUNCTION      C1DC028F981F2833F5A87E294B52A17A          3
PROCEDURE    MYSTANDALONEPROCEDURE FUNCTION           MYSTANDALONEFUNCTION            61E40C36B13171D5AACDC9FA40852B3A          3

Putting it all together

So – now we should know enought to combine these to find functions and procedures that are not registered as called from any piece of (static) code:

SELECT Object_Type, Object_Name, Type, Name, Signature, Usage, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage = 'DECLARATION'
AND Type IN ('FUNCTION', 'PROCEDURE')
AND Signature NOT IN (
 SELECT Signature
 FROM ALL_IDENTIFIERS
 --WHERE Owner IN ('MySchema1', 'MySchema2')
 AND Usage = 'CALL'
)
ORDER BY Object_Name, Object_Type, Name;

OBJECT_TYPE  OBJECT_NAME           TYPE               NAME                            SIGNATURE                        USAGE             LINE
------------ --------------------- ------------------ ------------------------------- -------------------------------- ----------- ----------
PACKAGE      MYPACKAGE             FUNCTION           MYREFERENCEDPUBLICFUNCTION      E925718DC7E9F225FF6FC7EC14F9BFEF DECLARATION          6
PACKAGE      MYPACKAGE             FUNCTION           MYUNREFERENCEDPUBLICFUNCTION    C47A76539917994464008666B6400C9C DECLARATION          4
PACKAGE      MYPACKAGE             PROCEDURE          MYUNREFERENCEDPUBLICPROCEDURE   3FF2BC911D1241E280FD46225FBE4BBC DECLARATION          2
PACKAGE BODY MYPACKAGE             PROCEDURE          MYUNREFERENCEDINTERNALPROCEDURE 6138E62F9BFE6468E21AA584F457948D DECLARATION         12
PROCEDURE    MYSTANDALONEPROCEDURE PROCEDURE          MYSTANDALONEPROCEDURE           E94699DD7B3C10FBE76DFF9C97ACC05B DECLARATION          1
TYPE         MYTYPE                FUNCTION           UNREFERENCEDTYPEFUNCTION        F6D636F3E46C2FDDCDA0884325FFEC79 DECLARATION          4

Notice that I match on signature, since “MyReferencedPublicFunction” is overloaded and one of them is referenced, the other isn’t. This shows that a name match is not sufficient, but the signature is unique for every declaration.

The list over is potentially dead code. I say potentially, because you will have to do further investigation to see if it is called from either dynamic (avoid execute immediates and open for <string>) or external code (Java, C#, PHP, …). But you now have a list to work out from, so that you can search through your other code. You might for instance import your other source code into a search index like Solr, Elasticsearch or even Oracle Text to search for matches from this list.

This also shows how important it is to have well defined external APIs (with proper API-packages, schema setup and grants), so that you know whether a package may be in external use or not. This can also be referred as “hard-shell PL/SQL API” and #SmartDB architecture.

The quick win

If you want a quick win, you can safely remove code that is only declared internally in packages and unreferenced – this is for sure dead code:

SELECT Object_Type, Object_Name, Type, Name, Signature, Usage, Line
FROM ALL_IDENTIFIERS
WHERE Owner IN ('MySchema1', 'MySchema2')
AND Usage = 'DECLARATION'
AND TYPE IN ('FUNCTION', 'PROCEDURE')
AND Object_Type LIKE '% BODY'
AND Signature NOT IN (
 SELECT Signature
 FROM ALL_IDENTIFIERS
 --WHERE Owner IN ('MySchema1', 'MySchema2')
 AND Usage = 'CALL'
)
ORDER BY Object_Name, Object_Type, Name;

OBJECT_TYPE  OBJECT_NAME           TYPE               NAME                            SIGNATURE                        USAGE             LINE
------------ --------------------- ------------------ ------------------------------- -------------------------------- ----------- ----------
PACKAGE BODY MYPACKAGE             PROCEDURE          MYUNREFERENCEDINTERNALPROCEDURE 6138E62F9BFE6468E21AA584F457948D DECLARATION         12

Ending notes

I’ve already mentioned that you will have to work through the signatures, not the names in case of overloads.

Also note that if you remove or plan to remove unreferenced code, you can check again to see if there are any functions or procedures that had the removed code as the only reference.

The contents of execute immediate and open … for “string” are not tracked, but you can find the location of these statements in the view ALL_STATEMENTS, so that you can analyze the contens of these. You’ll soon get tired of that and try to avoid dynamic statements in the future.

If you have an architecture where the client (middle-tier or end-tier) has (close to) full access to your schemas, you’re basically out of control. The further you design towards a hard-shell PL/SQL-API-application (#SmartDB), the more control you get.

On my system with close to 1 million lines of code, these queries takes less than a second.

And – don’t forget to clean up after the demo:

DROP PROCEDURE MyStandaloneProcedure;
DROP FUNCTION MyStandaloneFunction;
DROP TYPE myType;
DROP PACKAGE myPackage;

See my earlier blog-post on PL/Scope for references to other PL/Scope resources.