Home » Other » 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: 441
Registered: January 2012
Location: Israel
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: 68634
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: 441
Registered: January 2012
Location: Israel
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: 68634
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

Report message to a moderator

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: 441
Registered: January 2012
Location: Israel
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

Report message to a moderator

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: 68634
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

Report message to a moderator

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: 441
Registered: January 2012
Location: Israel
Senior Member
Thanks Michel.
Does anyone know the answer to my question?

Regards,
Andrey

Report message to a moderator

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

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Apr 16 05:45:36 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.