OraFAQ Forum: SQL & PL/SQL » how to find top level sql and pl/sql calls, not TOP-N queries

Home » SQL & PL/SQL » SQL & PL/SQL » how to find top level sql and pl/sql calls, not TOP-N queries (11gR2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

how to find top level sql and pl/sql calls, not TOP-N queries [message #590608]

Fri, 19 July 2013 16:04

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

I am on an 11gR2 database and looking for a way to trap the TOP LEVEL SQL and PL/SQL CALLS. This is not a tuning issue and I am not looking for TOP-N queries. I want the initial queries submitted by a calling process. For example.

create table a (a integer);

create or replace view va as select * from a;

create or replace trigger va_io
instead of insert on va
for each row
begin
   insert into a values (1);
end;
/

insert into va values (2);

This may seem silly but it exemplifies the request I have. Executing INSERT INTO VA VALUES (2); will attempt to insert into a view. The instead of trigger on this view will instead do the other statement INSERT INTO A VALUES (1);. There will thus be two queries in v$open_cursor and v$sql etc. V$session will show the SQL_ID for whichever statement is currently active.

I want to identify or trap the sql statment INSERT INTO VA VALUES (2);. Ideally I would at any time be able to reference a database package call or database function that would tell me the call depth. From this I could determine if I am at the top level or not.

I seem to recall there was a way in determine in a trigger or pl/sql procedure if you were at the top level of the call tree but I can't find any package of function or system variable that tells me this. There is of of course FORMAT_CALL_STACK but I am loathe to call this expensive routine every time I try to insert/update/delete a row so I was hoping for a variable of function call that would tell me the system call depth.

Anyone remember it or am I being dense? Seem to recall I had to code this myself in the past.

Thanks. Kevin

Report message to a moderator

Re: how to find top level sql and pl/sql calls, not TOP-N queries [message #590609 is a reply to message #590608]

Sat, 20 July 2013 01:10

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As far as I know, there is no way to get the sql stack or the top sql itself without calling "dbms_utility.format_call_stack" and analyse its returned value.
I didn't test it but are you sure it is so expensive (the call not the analyse part which is indeed quite expensive)?

Regards
Michel

Report message to a moderator

Re: how to find top level sql and pl/sql calls, not TOP-N queries [message #590610 is a reply to message #590609]

Sat, 20 July 2013 01:18

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Worst, when you just insert in pure SQL not from PL/SQL you even do not get it in the stack:

SQL> create sequence s;

Sequence created.

SQL> create table log (id int, tim timestamp default systimestamp, text varchar2(1000));

Table created.

SQL> create table a (a integer);

Table created.

SQL> create or replace view va as select * from a;

View created.

SQL> create or replace trigger va_io
  2  instead of insert on va
  3  begin
  4     insert into a values (1);
  5     insert into log (id, text) values (s.nextval, dbms_utility.format_call_stack);
  6  end;
  7  /

Trigger created.

SQL> insert into va values (2);

1 row created.

SQL> col text format a50 word wrap
SQL> col tim format a23
SQL> select * from log;
        ID TIM                     TEXT
---------- ----------------------- --------------------------------------------------
         1 20/07/2013 08:14:36.750 ----- PL/SQL Call Stack -----
                                     object      line  object
                                     handle    number  name
                                   69F15E30         1  anonymous block
                                   69F39678         3  MICHEL.VA_IO

1 row selected.

Regards
Michel

Report message to a moderator

Re: how to find top level sql and pl/sql calls, not TOP-N queries [message #590611 is a reply to message #590610]

Sat, 20 July 2013 01:22

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

But in this simple case you have it from v$session and v$sql:

SQL> drop sequence s;

Sequence dropped.

SQL> drop table log;

Table dropped.

SQL> drop view va;

View dropped.

SQL> drop table a;

Table dropped.

SQL> create sequence s;

Sequence created.

SQL> create table log (id int, tim timestamp default systimestamp, text varchar2(1000));

Table created.

SQL> create table a (a integer);

Table created.

SQL> create or replace view va as select * from a;

View created.

SQL> create or replace trigger va_io
  2  instead of insert on va
  3  begin
  4     insert into a values (1);
  5     for rec in (
  6       select sql_text from v$session s, v$sql q
  7       where sid=sys_context('userenv','sid') and q.sql_id=s.sql_id
  8     ) loop
  9       insert into log (id, text) values (s.nextval, 'cur sql: '||rec.sql_text);
 10     end loop;
 11  end;
 12  /

Trigger created.

SQL> insert into va values (2);

1 row created.

SQL> col text format a50 word wrap
SQL> col tim format a23
SQL> select * from log;
        ID TIM                     TEXT
---------- ----------------------- --------------------------------------------------
         1 20/07/2013 08:20:48.140 cur sql: insert into va values (2)

1 row selected.

Regards
Michel

Report message to a moderator

Re: how to find top level sql and pl/sql calls, not TOP-N queries [message #590662 is a reply to message #590611]

Sun, 21 July 2013 10:15

Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA

Senior Member

Most likely you ran your code in 10G. There was a change in behavior in 11G and your code will result in:

SQL> select * from log;

        ID TIM
---------- -----------------------
TEXT
--------------------------------------------------
         1 21-JUL-13 11.00.17.7720
           00 AM
cur sql: SELECT SQL_TEXT FROM V$SESSION S, V$SQL Q
 WHERE SID=SYS_CONTEXT('userenv','sid') AND Q.SQL_
ID=S.SQL_ID


SQL>

Check this: Find triggering statement

SY.

Report message to a moderator