Killing Parallel Query Session

 

为了加快表字段的打标采用了多CPU并行的方式取数：

SELECT/*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */ t2.service_type, t1.rowid

FROM '||v_table_name||' t1,LC_CP.b_serv_t@TO_HUBEI_ODS1_T t2

WHERE t1.acc_nbr = t2.acc_nbr
AND t2.state = ''F0A''
AND t1.par_id ='||v_day_id||'
ORDER BY t1.rowid;

在kill sission 的时候发现杀掉一个其他的会重启，好象一直杀不绝，没办法google了一下：

查询并行的session：

select x.server_name
, x.status as x_status, x.pid as x_pid
, x.sid as x_sid , w2.sid as p_sid from v$px_process x
, v$lock l, v$session v, v$session_wait w1, v$session_wait w2
where x.sid= l.sid(+)
and v.username='HBKF_CRM'
and to_number (substr(x.server_name,2)) = l.id2(+)
and x.sid = w1.sid(+)
and l.sid = w2.sid(+)
and x.sid = v.sid(+)
and nvl(l.type,'PS') = 'PS'
order by 1,2 ;

寻找父session：

select qcsid "Query Coordinator", count(*) as "Slaves Count" from v$px_session group by qcsid;

kill query coordinators and all his slaves will be killed too.

即杀掉父session，其他的session会跟着杀掉。