Re: Identifying Parallel Query Processes and Sessions

If you look at v$fixed_view_definitions, you will find that the x$ table
corresponding to the v$pq_slave table is x$kxfpdp. One of the columns
in this table (that is not revealed is v$pq_slave) is kxfpdppro. This
column corresponds to the addr column of v$process.

You could create a clone of v$pq_slave which displays this column and the join it to v$process and work on from there. One of the benefits of doing this is that the v$pq_slave columns for CPU usage, busy time, idle time is measured in minutes. - you can rewrite the text
you find in v$fixed_view_definitions to display the CPU usage in seconds.

My strategy on parallel server (in v7) is simply to create a UNION ALL
view selecting all data from remote databases. The easy but breakable way is to do this statically - the harder, but flexible way is to use dbms_sql driven through a cursor on v$active_instances.

Another feature for chasing PQ slaves on parallel server is to look at v$lock for locks of type PS - the SID tells you the session using PQ slaves, id1 tells you the instance they are running on, and id2 gives the slave number (which is indx+1 in x$kxfpdp). I used to cheat on tracking id2 and match it against the slave name ('Pnnn' where nnn is the id2 left padded with zeros) but I have a bug in 7.3.3.5 where I sometimes get two slaves with the same name on a single instance.

Jonathan Lewis

Billy Verreynne <vslabs_at_onwe.co.za> wrote in article <6h4f1r$o77$1_at_hermes.is.co.za>...
> Hi gang
>
> I've played around with trying to identify parallel query processes
and
> sessions and came up with the following very simple technique. So
simple,
> I'm not sure if it's correct or not! I have tried it on a number of
 v7.3.x
> Unix databases on different platforms and it seems to work all the
time.
>
Received on Fri Apr 17 1998 - 15:25:45 CDT