Thursday, November 23, 2023

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
);

No comments:

Post a Comment