Home » SQL & PL/SQL » Client Tools » V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool (Oracle Database 11g Release 11.1.0.7.0 - 64bit Production Windows)
V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566070] Tue, 11 September 2012 05:14 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi All,
I'm experiencing a problem with getting SQL_ID of a statement ran from pl\sql developer tool:
It just doesn't show up in my v$session. When I run it in SQL*Plus - sql_id is showing correctly.

My test case looks like this:



1) I open PL\SQL Developer tool, connect with user "ANDREY"
2) I run the statement
select userenv('sid') from dual;

The result is: 106

3) Still in the PL\SQL Developer session, I run
select * from test_table;



then, I open a SQL*Plus session, with

1)
C:\...>sqlplus andrey@connstring

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 12:53:03 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL>



2) I try to find the sql_id of the statement executed from PL\SQL Developer tool with:

SQL> select sql_id, prev_sql_id
  2  from v$session v
  3  where v.sid = 106;

SQL_ID        PREV_SQL_ID
------------- -------------
              bydf32qgqdwdu

SQL> select sql_text from v$sql
  2  where sql_id='bydf32qgqdwdu';

SQL_TEXT
------------------------------------------------------------------------

begin   sys.dbms_output.get_line(line => :line, status => :status); end;

SQL>



I can't see my statement I know I executed "select * from test_table".

****************************************************************
****************************************************************


So, I try to do the same from two SQL*Plus sessions I open:


First session:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\...>sqlplus andrey@connstring

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 13:05:09 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL>
SQL> select userenv('sid') from dual;

USERENV('SID')
--------------
           122

SQL>
SQL>
SQL> select * from test_table where rownum<5;

      ACOL BCOL
---------- --------------------
      1210 some string1210
      1211 some string1211
      1212 some string1212
      1213 some string1213

SQL>


Second session:

Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\...>sqlplus andrey@connstring

SQL*Plus: Release 11.2.0.2.0 Production on Tue Sep 11 13:06:48 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

SQL>
SQL> select sql_id,prev_sql_id
  2  from v$session v
  3  where v.sid=122;

SQL_ID        PREV_SQL_ID
------------- -------------
              078s8pqj8zzvd

SQL> select sql_text
  2  from v$sql
  3  where sql_id='078s8pqj8zzvd';

SQL_TEXT
---------------------------------------------------------------------

select * from test_table where rownum<5

SQL>



Does anybody know why this is happening?
why is sql_id\prev_sql_id in v$session recording differently from PL\SQL Developer tool
Then it does in SQL*Plus?


Thanks in advance for your help.

Best Regards,
Andrey

[Updated on: Tue, 11 September 2012 05:16]

Report message to a moderator

Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566075 is a reply to message #566070] Tue, 11 September 2012 05:51 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I can't see my statement I know I executed "select * from test_table".


Set the equivalent to "set serverouput off" in your PL/SQL Developer.

And this is in NO case a Performances question, take care in which forum you create your topic.

Regards
Michel

[Updated on: Tue, 11 September 2012 05:52]

Report message to a moderator

Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566083 is a reply to message #566075] Tue, 11 September 2012 06:11 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Thanks Michel, however:

The issue is not the output that can't be seen.
It's that

1. If I run "select * from test_table" from PL\SQL Developer session - Its v$session record will not show the correct sql_id.
2. If I run "select * from test_table" from SQL*Plus session - Its v$session record will show the correct sql_id.

* I will take into account that this kind of issues belong to this forum thread,
And not performance. Thanks.


Regards,
Andrey

[Updated on: Tue, 11 September 2012 06:14]

Report message to a moderator

Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566086 is a reply to message #566083] Tue, 11 September 2012 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The issue is not the output that can't be seen.
It's that


It is the issue, because SQL Developer issues the statement you see ("begin sys.dbms_output.get_line(line => :line, status => :status); end;") before you can get the sql id of your (previous) statement.

Check, execute "set serveroutput on" in SQL*Plus et reexecute your test and you will see you can no more get your SQL but the same thing than in SQL Developer.

Regards
Michel


Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566129 is a reply to message #566086] Tue, 11 September 2012 08:03 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Thanks Michel, I've done that, but now it's showing me

SQL_TEXT
-------------------------------------------------------------------------
begin :id := sys.dbms_transaction.local_transaction_id; end;


Don't understand what component I should disable now to make it work.
Tried running PL\SQL Developer's login.sql script with "set serveroutput off" line,
or CMD window(in addition to graphically tick off the "enable" button under "OUTPUT").

nothing seems to change, I can only get this DBMS_...line's sql_id and not my query's..

Please help..

Thanks and Best Regards,
Andrey
Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566138 is a reply to message #566129] Tue, 11 September 2012 08:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58638
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know PL/SQL Developer but it seems to do many things behind the scene.
Maybe someone else will know the product and help you.
Anyway, this is not the good tool to do performance and tuning investigation.

Regards
Michel
Re: V$SESSION doesn't show sql_id of statement ran from PL\SQL Developer tool [message #566170 is a reply to message #566138] Tue, 11 September 2012 10:32 Go to previous message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Thanks Michel.
Does anyone know the answer to my question?

Regards,
Andrey
Previous Topic: can not open sqlplus
Next Topic: sql+ gui version download (2 Merged)
Goto Forum:
  


Current Time: Thu Jul 31 16:14:48 CDT 2014

Total time taken to generate the page: 0.15757 seconds