How to kill a oracle parallel query – knowledge article for the new oracle dba




The new oracle dba can find here useful information about how to kill a oracle parallel query.

Applies to:

Oracle Server – Enterprise Edition – Version: 9.0.1.0 to 11.1.0.7
Information in this document applies to any platform.

Purpose

The article describes the simplest solution to kill a parallel execution query at OS level.

Scope and Application

For dba’s

The simplest Solution to kill a PX Session at OS Level

In case, a parallel execution query needs to be kill at OS level, than the simplest way is to kill a parallel execution slave and not the query coordinator.  Only the query coordinator has a connection to each slave in the px query. If we kill a slave a signal is send to the query coordinator (QC). Than the QC sends a signal to the other slaves to stop the query.

The following Note can help to find a parallel execution server/slave for the px query, that nees to be killed:

Note.344196.1 Script to map Parallel Execution Server to User Session.

If SMON is the query coordinator  than a kill of parallel execution server/slave  can cause a Instance crash. That is not the case if the QC is a user background process 

 

 

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.1
Oracle Server – Personal Edition – Version: 9.2.0.1
Information in this document applies to any platform.

Purpose

This query can be used to display which sessions are currently running a parallel query. It will also display which, and how many, query slaves each session owns.
This script is RAC aware and an enhancement of  Note.202219.1

Software Requirements/Prerequisites

You execute this script SQL Plus

Configuring the Script

You need only privileges to query some gv$ views. 

Running the Script

You can execute this script in SQL Plus

Caution

This script is provided for educational purposes only and not supported by Oracle Support Services. It has been tested internally, however, and works as documented. We do not guarantee that it will work for you, so be sure to test it in your environment before relying on it.

Proofread this script before using it! Due to the differences in the way text editors, e-mail packages and operating systems handle text formatting (spaces, tabs and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected.

Script

col username for a12
col username for a12
col "QC SID" for A6
col SID for A6
col "QC/Slave" for A8
col "Requested DOP" for 9999
col "Actual DOP" for 9999
col "Slaveset" for A8
col "Slave INST" for A9
col "QC INST" for A6
set pages 300
set lines 200
select
decode(px.qcinst_id,NULL,username,
' - '||lower(substr(pp.SERVER_NAME,
length(pp.SERVER_NAME)-4,4) ) )"Username",
decode(px.qcinst_id,NULL, 'QC', '(Slave)') "QC/Slave" ,
to_char( px.server_set) "SlaveSet",
to_char(s.sid) "SID",
to_char(px.inst_id) "Slave INST",
decode(px.qcinst_id, NULL ,to_char(s.sid) ,px.qcsid) "QC SID",
to_char(px.qcinst_id) "QC INST",
px.req_degree "Req. DOP",
px.degree "Actual DOP"
from gv$px_session px,
gv$session s ,
gv$px_process pp
where px.sid=s.sid (+)
and px.serial#=s.serial#(+)
and px.inst_id = s.inst_id(+)
and px.sid = pp.sid (+)
and px.serial#=pp.serial#(+)
order by 6 , 1 desc
/

 

Script Output

Example output from this script:

Username     QC/Slave SlaveSet SID    Slave INS QC SID QC INS   Req. DOP Actual DOP
------------ -------- -------- ------ --------- ------ ------ ---------- ----------
SYSTEM       QC                136    1         136
 - pz99      (Slave)  1        142    3         136    1               2          2
 - pz99      (Slave)  1        144    1         136    1               2          2


 

Related


Products


  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Enterprise Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server – Personal Edition

 

 

 

Author: admin