Setting a parameter in another session

Today I needed to set the optimizer_dynamic_sampling in several sessions to a lower value. It was set to 5, which is way too high for this application.

I used what is below.

begin
  for cur in (select sid,serial# from v$session where action like 'DRAIN%') loop
    sys.dbms_system.set_int_param_in_session(cur.sid,cur.serial#,'optimizer_dynamic_sampling',2);
  end loop;
end;
/

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.