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 )
SQL>
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.