Identifying Parallel Query Processes and Sessions

[Billy Verreynne]

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.

I'll appreciate if those of you who use PQ, try it on your platform and tell
me whether it works or not.

Here's the SQL:
--
SELECT
substr(NVL(s.sid,0)||':'||NVL(s.serial#,0),1,10) "Session:Serial",
p.spid "Unix PID",
SUBSTR(NVL(s.schemaname,'-'),1,10) "Schema",
NVL(s.osuser,p.username) "User",
NVL(s.status,'IDLE') "Status"
FROM v$process p, v$session s
WHERE p.addr = s.paddr (+)
AND p.program != 'PSEUDO'
AND p.program NOT LIKE '%(%'
--

The thinking and observations behind it.

A PQ, busy or idle, will always have a v$process entry. The only way I could
pick up the PQ processes where to look at the PROGRAM column. For
user/client connections (using either TNS or BEQ), it seems that Oracle
always suffixes something like "(TNS V1-V2)" to the PROGRAM column value.
The standard Oracle processes are identified with a "(PMON)", "(SMON)",
"(DBWR)" etc.

OK, now eliminating any row with a bracket in PROGRAM should give you the
PQ's - well almost. You also need to elimate the row where PROGRAM contains
the value "PSEUDO".

Now I connect it to v$session with an outer join. Idle PQs will not have a
session entry, whereas busy PQs will.

And that is basically it. You can of course change the projection part of
the SQL SELECT statement to display any other columns from either v$process
or v$session. To test this output, just do a SELECT * FROM v$pq_slaves. The
number of rows should correspond. To make sure that the process from
v$process is in fact a PQ, do a Unix ps -fp <pid> on the Unix PID for that
v$process row.

Next thing of course is to try and tie a PQ to the actual client process.
Maybe possible on a single instance, but I doubt if it can be done on a
Parallel Server.

Comments and suggestions appreciated.

thanks,
Billy

[Jonathan Lewis]

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 <vsl...@onwe.co.za> wrote in article
<6h4f1r$o77$1...@hermes.is.co.za>...