9i: sql child number not in v$session

From: kyle Hailey <kylelf_at_gmail.com>
Date: Tue, 1 Sep 2009 10:15:16 -0700
Message-ID: <6f373fd20909011015j48274a6end2de4b97fc121c45_at_mail.gmail.com>

Is there anyway to get the sql child number for a query executed by a session after the query has been executed?

I can get the hash_value from v$session for another session for the las query the executed but can't get the child number. If the query is running I can get the child_address from x$kgllk, ie

select kgllkhdl
from x$kgllk kgl, v$session s
where kgl.kglnahsh=s.sql_hash_value and
kgl.kgllksnm=&sid and
kgl.kglhdnsp=0 and kgl.kglhdpar != kgl.kgllkhdl;

But this only works while the query is running. I can still get the sql_hash_value from v$session after the query has finished, but haven't found a way to get the child_number.

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 01 2009 - 12:15:16 CDT