From:Steve Adams
Date:31-Oct-2001 16:47
Subject:   Relationship between SQL statements in shared pool

X$KGLRD tracks the relationship between code objects and their cursors. Here is a simple example ...

        SQL> begin insert into t values(1); end;
          2  /

        PL/SQL procedure successfully completed.

        SQL> select kglnacnm, kglnadnm from sys.x_$kglrd;

        KGLNACNM                               KGLNADNM
        -------------------------------------- --------------------------------------
        begin insert into t values(1); end;    INSERT INTO T VALUES ( 1  )

The contingent object need not be an anonymous block. The dependency of packages and other stored code objects on their cursors is also represented here. Thus you can use X$KGLRD to find the source of a problem SQL statement without resorting to expensive full scans of DBA_SOURCE. You can also characterise the performance of code objects in terms of their constituent cursors.

After retrieving an SQL statement from shared pool using its address and hash_value (for example "select 1 from dual"), do you know a way to find its parent in the shared pool ("declare ...")? If we could establish the relationship between an SQL statement and the PL/SQL unit that contains it, we will gather better statistics on SQL statements present in the shared pool.