Tracking Child Sqlid / Session Id executed By Plsql and Procedure using program_id

Its not easy to track and tune  Child sql that are  executed internally as part  of plsql. 

Unfortunately  Sqlid attached execution plan  and Sql tuning advisor doesnt support on sqlid for plsql hence we need to identify  Child sql that are  executed internally as part  of plsql and tune child sqlid .

While this execution is in-progress, we could easily track the SQL that is currently executing along with its line no# with in the package.

The PLSQL_ENTRY_OBJECT_ID and PLSQL_ENTRY_SUBPROGRAM_ID from V$SESSION would tell us the name of the package and method that is currently executing.

Select owner,object_name,object_type,object_id  from dba_objects  where object_name in( 'MYPKG' );

OWNER      OBJECT_NAM  OBJECT_TYPE           OBJECT_ID

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

ABDUL      MYPKG      PACKAGE BODY              99507

ABDUL      MYPKG      PACKAGE                   99506

Check the query is triggered from any procedure

SELECT s.sql_id, s.sql_text FROM gv$sqlarea s JOIN dba_objects o ON s.program_id = o.object_id and o.object_name = '&procedure_name';

 select sql_fulltext, program_id, program_line#,

        (select object_name

          from all_objects

         where object_id = program_id) object_name ,

       plsql_entry_object_id c1,

       plsql_entry_subprogram_id c2,

        plsql_object_id  c3,

      plsql_subprogram_id c4

   from v$session b1,

       v$sql b2

  where b1.program_id in ( '99506'  ,'99507') 

  and b1.sql_id = b2.sql_id

  and b1.sql_child_number = b2.child_number;

SQL_FULLTEXT         PROGRAM_ID PROGRAM_LINE# OBJECT_NAME          C1         C2      C3            C4

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

SELECT B1.OWNER,B2.U      99507             5 MYPKG             99506          2

SERNAME FROM ALL_OBJ

ECTS B1, ALL_USERS B2

PROGRAM_LINE# represents the line no# at which this SQL is present.

  column text format a40

  select line, text

  from all_source

  where name ='MYPKG'

  and type ='PACKAGE BODY'

  and owner ='DEMO'

  and line <=10

  order by line ;

      LINE TEXT

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

         1 package body mypkg

         2 as

         3      procedure p1 as

         4      begin

         5              for x in (select b1.owner,b2.username

         6                      from all_objects b1, all_users b2)

         7              loop

         8                      null;

         9              end loop;

        10      end;

10 rows selected.

If we want to get  only  objects details  that are accessed  by package we can get  it by below sql 

select pname, tab, type, text from (

select ui.name pname, ud.table_name tab, us.type, 

       ui.st, ui.en, us.line, us.text,

       max(line) over (partition by us.name, us.type) mx_line

from   user_source us

join   user_tables ud

on     upper(us.text) like '%' || table_name || '%'

join   (select name, object_type, object_name, 

               line st, lead(line) over (partition by object_type order by line)-1 en

        from   user_identifiers

        where  type in ('FUNCTION', 'PROCEDURE')

        and    usage in ('DECLARATION', 'DEFINITION')

        and    object_type like 'PACKAGE%') ui

on     ui.object_name = us.name

and    ui.object_type = us.type

where  us.name = 'PKG'

)

where  line between st and nvl(en, mx_line);

 WITH TESTING AS

(

select

DISTINCT

name,

type,

decode(usage,'DECLARATION', 'body only', 'DEFINITION', 'spec and body', usage) defined_on,

line body_line,

object_name

from user_identifiers ui

where type = 'PROCEDURE'

and usage_context_id = (select usage_id

from user_identifiers

where object_name = ui.object_name

and object_type = ui.object_type

and usage_context_id = 0)

and object_name = 'MY_PACKAGE_NAME'

and object_type = 'PACKAGE BODY'

order by line

);