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