Notes on DEPTREE

After writing my own scripts to find either all parents or children of an object, I took a look at Oracle’s solution to find dependencies:

$ORACLE_HOME/rdbms/admin/utldtree.sql

It was actually written about 20 years ago, in 1991:
Rem rkooi 10/19/91 – Creation

Running the script, creates a number of objects:

OBJECT_AND_OBJECT_ID
----------------------------------------------------------------------------------------------------
PROCEDURE   SYS.DEPTREE_FILL    3304377
SEQUENCE   SYS.DEPTREE_SEQ    3304375
TABLE   SYS.DEPTREE_TEMPTAB    3304376
VIEW   SYS.DEPTREE    3304378
VIEW   SYS.IDEPTREE    3304379

7 rows selected.



desc DEPTREE_FILL
PROCEDURE DEPTREE_FILL
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TYPE                           CHAR                    IN
 SCHEMA                         CHAR                    IN
 NAME                           CHAR                    IN

desc ideptree
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 DEPENDENCIES                                                   VARCHAR2(4000)

desc deptree
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 NESTED_LEVEL                                                   NUMBER
 TYPE                                                           VARCHAR2(19)
 SCHEMA                                                         VARCHAR2(30)
 NAME                                                           VARCHAR2(1002)
 SEQ#                                                           NUMBER

To use these objects, you are then supposed to run the procedure, DEPTREE_FILL:

begin
DEPTREE_FILL 
(
  'PACKAGE BODY'
, 'APPS'
, 'FND_GLOBAL'
) ;
end;

The procedure will populate the temporary TABLE SYS.DEPTREE_TEMPTAB.

----

Then, retrieve from the view:

select * from ideptree;

DEPENDENCIES
----------------------------------------------------------------------------------------------------
   CURSOR ."begin fnd_global.initialize(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12, :13, :14, :1
5, :16, :17, :18); end; "

PACKAGE BODY APPS.FND_GLOBAL

2 rows selected.

select * from deptree;

NESTED_LEVEL TYPE                SCHEMA
------------ ------------------- ------------------------------
NAME
----------------------------------------------------------------------------------------------------
      SEQ#
----------
           0 PACKAGE BODY        APPS
FND_GLOBAL
         0

...

           1 CURSOR              
"SELECT Q.RESOURCE_CONSUMER_GROUP FROM FND_CONCURRENT_REQUESTS R, FND_CONCURRENT_PROCESSES P, FND_CO
NCURRENT_QUEUES Q WHERE R.REQUEST_ID = FND_GLOBAL.CONC_REQUEST_ID AND R.CONTROLLING_MANAGER = P.CONC
URRENT_PROCESS_ID AND Q.CONCURRENT_QUEUE_ID= P.CONCURRENT_QUEUE_ID AND Q.APPLICATION_ID = P.QUEUE_AP
PLICATION_ID"
        .5

           1 CURSOR              
"BEGIN  :1:=FND_GLOBAL.USER_ID;  :2:=FND_GLOBAL.RESP_ID;  :3:=FND_GLOBAL.RESP_APPL_ID;  :4:=FND_GLOB
AL.SECURITY_GROUP_ID;  :5:=FND_GLOBAL.LOGIN_ID;  :6:=FND_GLOBAL.NLS_LANGUAGE;  :7:=FND_GLOBAL.NLS_DA
TE_FORMAT;  :8:=FND_GLOBAL.NLS_DATE_LANGUAGE;  :9:=FND_GLOBAL.NLS_TERRITORY;  :10:=FND_GLOBAL.NLS_NU
MERIC_CHARACTERS;  :11:=FND_GLOBAL.NLS_SORT;END;"
        .5

14 rows selected.

——————–

Some interesting observations. On this package body, it finds shared cursors!

————————–

When I run the DEPTREE_FILL procedure for the Package, FND_GLOBAL, not the Package Body, I get over 70K rows.

begin
DEPTREE_FILL 
(
  'PACKAGE'
, 'APPS'
, 'FND_GLOBAL'
) ;
end;

-----

select * from ideptree

...
   TRIGGER APPS.MRP_SN_WIP_OPR_RES_INSTS_T1
            VIEW APPS.MRP_AP_SUPPLIER_CAPACITIES_V
      VIEW APPS.MRP_AP_SUPPLIER_FLEX_FENCES_V
   VIEW APPS.MRP_AP_PO_REQ_SUPPLY_V
            VIEW APPS.MRP_AP_REPAIR_TRANSFERS_RESV_V
      VIEW APPS.MRP_AP_SUB_INVENTORIES_NEW_V
            PACKAGE BODY APPS.PAY_COST_ALLOCATION_SWI
         PACKAGE BODY APPS.HRDPP_UPDATE_AU_EMP_ASG
            PACKAGE BODY APPS.HRDPP_UPDATE_AU_EMP_ASG
            PACKAGE BODY APPS.HRDPP_UPDATE_AU_PERSONAL_PAY_M
            PACKAGE BODY APPS.HRDPP_UPDATE_AU_PERSON
         CURSOR ."declare b boolean; retval VARCHAR2(1);begin b := fnd_concurrent.set_interi
m_status(:1,:2); if ( b ) then retval := 'T'; else retval := 'F'; end if;  :3 := retval; end; "

70483 rows selected.

-----

select *
from deptree

...

NESTED_LEVEL TYPE                SCHEMA
------------ ------------------- ------------------------------
NAME
----------------------------------------------------------------------------------------------------
      SEQ#
----------

...

           3 PACKAGE BODY        APPS
HRDPP_MAINTAIN_SUPER_INFO
     27800

           3 PACKAGE BODY        APPS
HRDPP_CREATE_PAYE_TAX_INFO
     27801

           3 PACKAGE BODY        APPS
HRDPP_CREATE_SUPER_CONTRIBUTIO
     27802

           3 PACKAGE BODY        APPS
HRDPP_UPDATE_SUPER_CONTRIBUTIO
     27803

...

           2 CURSOR              
"SELECT Fnd_Profile.value('ICX_CLIENT_IANA_ENCODING') FROM DUAL"
   57865.5

           2 CURSOR              
"SELECT Fnd_Profile.value('JTF_FM_ERROR_LOG') FROM DUAL"
   57865.5

70541 rows selected.

-----------------

Objects can be duplicated in the output many times. 

select 
 SCHEMA || '.' || NAME    AS the_object
, count(*)
from deptree 
group by   SCHEMA || '.' || NAME    
having count(*) > 1
order by 2

...

APPS.OKL_CREATE_KLE_PVT
       139

APPS.OKL_OPEN_INTERFACE_PVT
       163

APPS.OKL_VENDOR_AGREEMENT_PUB
       216

APPS.BEN_PLAN_DESIGN_DELETE_API
       237

12544 rows selected.

—————-

In other words, APPS.BEN_PLAN_DESIGN_DELETE_API appeared in the output 237 times! Not useful if you want to get the order of operations, requiring that it only appear once.

——————

What does the raw data look like?

select *
from ideptree 
where  DEPENDENCIES  like  '%BEN_PLAN_DESIGN_DELETE_API%'

...
            PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API
            PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API
            PACKAGE BODY APPS.BEN_PLAN_DESIGN_DELETE_API

237 rows selected.

------------------

What's the duplicate ratio? 

select count( distinct  SCHEMA || '.' || NAME )   
        as distinct_count
, count(*)   as total_count
, (count(*) / count( distinct  SCHEMA || '.' || NAME ) ) 
        as Duplicate_Ratio
from deptree 

DISTINCT_COUNT TOTAL_COUNT DUPLICATE_RATIO
-------------- ----------- ---------------
         26477       70541      2.66423688

----------------

My thoughts on DEPTREE are:
– needs to create five objects before use
– is a two or three stage process
– produces many duplicate rows
– only finds the children of an object, not parents
– can find shared cursor dependencies

Summary: It was a good start, for 1991. But I prefer my own scripts instead.