Re: Identify child_number for a session?

I think you are going to have to go X$ digging.

Look at v$open_cursor, for the matching
sid, saddr, address and hash_value for
the v$session you are interested in - then go under v$open_cursor to x$kgllk. (See V$fixed_view definitions for the definition of gv$open_cursor to line up the x$ columns with the v$columns).

The x$kgllk.kgllkhdl SEEMS to match the
v$sql.child_address column - which gets you where you want to go.

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____UK_______April 8th
____UK_______April 22nd
____Denmark__May 21-23rd
____USA_(FL)_May 2nd

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May
____Estonia___June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"Richard Kuhler" <noone_at_nowhere.com> wrote in message
news:kFoja.27817$Hx.17741736_at_twister.socal.rr.com...


> How do you identify the CHILD_NUMBER for a given session?  V$SESSION

has


> SQL_ADDRESS and SQL_HASH_VALUE but I don't see CHILD_NUMBER.  If two

> different sessions submit the same query but receive different plans

> then they will have different CHILD_NUMBERs and different V$SQL

> entries.  However, I don't see how you can tell which CHILD_NUMBER

each


> session is using.  Ultimately, I want to distinguish between the

plans


> in V$SQL_PLAN using that CHILD_NUMBER information.

>

>

> Oracle9i Enterprise Edition Release 9.2.0.2.0 - Production

> With the Partitioning and Oracle Data Mining options

> JServer Release 9.2.0.2.0 - Production

>

>

> Thanks,

> Richard Kuhler

>