Home » RDBMS Server » Performance Tuning » sql_id and sql_child_id not are null for SQL statement (Oracle 11g R2, Windows Server 2003 sp2)
sql_id and sql_child_id not are null for SQL statement [message #550572] Tue, 10 April 2012 08:46 Go to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Hi all,

i am trying to analyze a query i have and noticed that it does not show the sql_id in v$session.


preparing a test case:
create table t1(a number, b varchar(10));
insert into t1 values(123 , 'value1');



when i execute

select count(*) from dual;
select * from dual;
select count(*) from t1;


i can see the sql_id
by running

select 
sql_id sql_id_,
sql_child_number sql_child_num,
module module_,
action action_,
logon_time lgtime,
v$session.*
from v$session
where 
type='USER'
and username='MY_USER'
and osuser = 'OS_USER'
and module = 'SQL*Plus'
order by lgtime desc


however, when i'm running

select * from t1


sql_id and sql_child_id in v$session appears to be null,
and i can't analyze it.


Can anybody please explain why those columns are NULL?

Regards,
Andrey

[Updated on: Tue, 10 April 2012 08:49]

Report message to a moderator

Re: sql_id and sql_child_id not are null for SQL statement [message #550574 is a reply to message #550572] Tue, 10 April 2012 08:53 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
>and i can't analyze it.
what exactly does above mean.

what problem are you really trying to solve?
Re: sql_id and sql_child_id not are null for SQL statement [message #550575 is a reply to message #550572] Tue, 10 April 2012 08:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If sql_id is null take prev_sql_id:
SQL> create table t1(a number, b varchar(10));

Table created.
SQL> insert into t1 values(123 , 'value1');

1 row created.

SQL> commit;

Commit complete.

SQL> select sys_context('userenv','sid') from dual;
SYS_CONTEXT('USERENV','SID')
--------------------------------------------------------
158

1 row selected.

SQL> select * from t1;
         A B
---------- ----------
       123 value1

1 row selected.

SQL> select sql_id, prev_sql_id from v$session where sid=158;
SQL_ID        PREV_SQL_ID
------------- -------------
              27uhu2q2xuu7r

1 row selected.

SQL> select SQL_TEXT from v$sql where sql_id='27uhu2q2xuu7r';
SQL_TEXT
------------------------------------------------------------------
select * from t1

1 row selected.

Regards
Michel
Re: sql_id and sql_child_id not are null for SQL statement [message #550576 is a reply to message #550575] Tue, 10 April 2012 10:01 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Thank you Michel.

Can you explain where that difference come from?
why count(*) inserts a value to sql_id whereas just * works upon prev_sql_id?
Re: sql_id and sql_child_id not are null for SQL statement [message #550577 is a reply to message #550576] Tue, 10 April 2012 10:03 Go to previous messageGo to next message
Andrey_R
Messages: 188
Registered: January 2012
Location: Euro-Asia
Senior Member

Black Swan - Thanks for replying.
i am trying to use dbms_xplan.display_cursor upon a query, and i need its sql_id for that cause, which i had some trouble to find out.
Re: sql_id and sql_child_id not are null for SQL statement [message #550579 is a reply to message #550577] Tue, 10 April 2012 10:18 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
08:17:47 SQL> set autotrace traceonly explain
08:18:02 SQL> select sysdate from dual;

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

-----------------------------------------------------------------
| Id  | Operation	 | Name | Rows	| Cost (%CPU)| Time	|
-----------------------------------------------------------------
|   0 | SELECT STATEMENT |	|     1 |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL	 |	|     1 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------

08:18:11 SQL> 


>i am trying to use dbms_xplan.display_cursor upon a query
or just do as above
Re: sql_id and sql_child_id not are null for SQL statement [message #550581 is a reply to message #550576] Tue, 10 April 2012 10:24 Go to previous message
Michel Cadot
Messages: 58934
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Andrey_R wrote on Tue, 10 April 2012 17:01
Thank you Michel.

Can you explain where that difference come from?
why count(*) inserts a value to sql_id whereas just * works upon prev_sql_id?


No I can't, I just noticed this which is true in all Oracle versions (using sql_hash_value... for versions before 10g).
So all my queries contains something like "nvl(sql_id,prev_sql_id)".

Regards
Michel

Previous Topic: Bulk Inserts
Next Topic: Partition Pruning Not happening!
Goto Forum:
  


Current Time: Fri Aug 29 03:18:40 CDT 2014

Total time taken to generate the page: 0.22676 seconds