Hi all,
Let's say I called a procedure and it is calling many other procedures/functions/packages and they are running select statements, DMLs etc. This procedure that I called is run for 1 hour and after running half an hour when I check session info I can see which sql statement is being run by this session and (probably or assume) it is a select statement. now I can see the session is running a select statement but is this what session started at first or is it a statement in a proc which session called it at the first place.
How can I get top level sql id ? Of course many of you have already understood (if I was able to explain properly) that I am looking for TOP_LEVEL_SQL_ID column in V$Active_Session_History but if I don't have diagnostic pack, how can I get this information? is this a public data that I can get from somewhere else? and I searched for it but I am not sure about. for example, (G)V$Open_Cursor view has some information about my question. when I trigger a procedure and when proc runs a select statement, V$open_cursor shows me 2 of those statements and they have an SQL_EXEC_ID but again I don't know which one is the first. there might be many active sql statements here at the same time how can I find top level sql id that this session issued at first?
a sample code could be like this:
alter session set plsql_optimize_level = 0;
create or replace function sf_x(p number) return number as
x number := 0;
begin
select count(e.salary) into x from hr.employees e, hr.employees,hr.employees,hr.departments,hr.departments
where e.employee_id = p;
return x;
end;
/
create or replace package pkg_x as
procedure sp_x ;
end;
/
create or replace package body pkg_x as
procedure sp_x as
begin
for rec in (select sf_x(level + 100) aa from dual connect by level < 10)
loop
dbms_lock.sleep(5);
dbms_output.put_line(rec.aa);
end loop;
end;
end;
/
start a new session and get SID and then run
Begin
pkg_x.sp_x;
end;
/
and check v$open_cursor for that session via another session. also I used some cartesian product to make that inner select statement keep running. Finally, after running pkg_x.sp_x, when I checked v$session I can see that my big select statement is currently running but I want to get pkg_x.sp_x (first called statement at the session).
thanks.
Message was edited by: Mustafa KALAYCI I changed the subject.