Identifying Parallel Query Processes and Sessions

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 Received on Thu Apr 16 1998 - 03:23:44 CDT