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
|