Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
919 views
in Technique[技术] by (71.8m points)

plsql - Oracle - How to execute this script in parallel

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...