plsql - Oracle - How to execute this script in parallel

Question

I have a script to kill all sessions in Oracle:

declare
begin
for rec in (
SELECT username,machine,sid,serial#,inst_id from gv$session where type <> 'BACKGROUND' 
and sid <> (select sys_context('userenv','sid') from dual) and status <> 'KILLED'
and username not in ('SYSRAC','SYS')
) 
loop
execute immediate 'alter system disconnect session '''|| rec.sid|| ',' || rec.serial# || ',@' ||rec.inst_id||''' immediate';
end loop;
end;
/

Once a month I need kill all session before execute several scripts to add/remove/modify columns, grants and revokes, create new objects, drop objects etc..

This database usually has more than 2000 connections and when I execute this script it takes from 15 to 20 minutes to kill all sessions. How can I execute this script in parallel and kill all sessions faster?

question from:https://stackoverflow.com/questions/65887705/oracle-how-to-execute-this-script-in-parallel

Answer 1

A few things to absorb there ...

1. 2000 connections sounds like overkill, especially if you're killing all of them which suggests they all belong to the same app. I'd be checking your connection pooling stats to ensure this isn't too many.

2. It might be worth checking out edition based redefinition to allow deployment without any downtime

3. If you're killing everything anyway, just do shutdown abort/startup and you're done

4. If you really want to kill those sessions, then you can use DBMS_PARALLEL_EXECUTE to get the job scheduler to run these concurrently.

But step back and take a look at the whole picture here, because generally, we don't kill all sessions to deploy code.