Tip of the Week
Tip for Week of May 18, 2003

Mapping Parallel Coordidate Query to Its Slave Queries

This tip comes from Wayne Zhu is a Sr. Database Administrator in Philadelphia, PA.

The script:
===========

set lines 2048
set pages 2048
break on qcsid


select p.qcsid,q.*
--             s.* /* if need to see the session info */
from v$px_session p, v$session s, v$sqlarea q
where p.sid=s.sid(+)
and p.serial#=s.serial#
and s.sql_address=q.address

and s.sql_hash_value=q.hash_value
order by 1
/

Sample output:
==============

     QCSID SQL_TEXT

----------
--------------------------------------------------------------
        13 SELECT    MTW.CUSTNAME,    FSN.ORD_NUMBER, ... 
           SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SWAP_JOIN_INPUTS(A2)
           SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SWAP_JOIN_INPUTS(A2)
           SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) SWAP_JOIN_INPUTS(A2)
           SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "LCCZ_PK")
*/
           SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "LCCZ_PK")

*/
           SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "LCCZ_PK")
*/ 
        17 SELECT DISTINCT    MTW.CUSTNAME,   ...
           SELECT A1.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5,A1.C6,A1.C7,A1.C8,A
           SELECT A1.C0,A1.C1,A1.C2,A1.C3,A1.C4,A1.C5,A1.C6,A1.C7,A1.C8,A
           SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "SZHF_PK")
*/
           SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDEX(A2 "SZHF_PK") 
*/




Note:
=====
One can also join the v$process when needed. For example, start from
a UNIX process of high CPU usage and find out the corresponding slave 
query and/or coordinate query.


See also:

=========
select p.qcsid,sum(buffer_gets),sum(disk_reads),
--     sum(executions),sum(rows_processed),  /* any column of interest*/ 
       sum(cpu_time),sum(elapsed_time)       /* very useful 9i new columns */
from v$px_session p, v$session s, v$sqlarea q
where p.sid=s.sid(+)
and p.serial#=s.serial#
and s.sql_address=q.address
and s.sql_hash_value=q.hash_value
group by p.qcsid
/