如何给其他会话设置session级别的参数

之前有同学问我如何才能给非自身的其他会话设置会话级别(alter session)的参数;在实际的Oracle管理过程中我们往往希望在不修改实例级别参数的情况下, 让部分session使用指定的参数值。

如果仅仅是修改本地会话的参数值,那么很好办,只需要alter session set parameter 即可,如:

 

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------
www.askmac.cn & www.askmac.cn

SQL> alter session set workarea_size_policy=MANUAL;

Session altered.

 

但是如果是要给非本地会话的其他会话设置session级别的参数的话,那么就比较麻烦了。

 

就我所知,存在2种方法,这里抛砖引玉一下:

 

1.  通过 AFTER LOGON ON Trigger登录后触发器实现部分用户或程序登录即触发session级别的参数被设置,如:

 

CREATE OR REPLACE TRIGGER AUTO_SESSION_PARAMETER
  AFTER LOGON ON MACLEAN.SCHEMA
BEGIN
    execute immediate 'alter session set optimizer_mode=RULE';
END;
/

Trigger created.

 

当有用户以MACLEAN账号登录时,会触发该Trigger在session级别设置optimizer_mode=RULE使用RBO优化器。

 

2.使用DBMS_SYSTEM包的SET_INT_PARAM_IN_SESSION和SET_BOOL_PARAM_IN_SESSION 2个存储过程,实际我在利用<dbms_system包加速imp导入数据时的索引建立> 一文中已经介绍过了如何利用SET_INT_PARAM_IN_SESSION优化imp导入数据时创建索引相关的会话级别参数。

 

SET_INT_PARAM_IN_SESSION 过程用以给其他SESSION设置值为整形类的参数,如db_file_multiblock_read_count,其具体用法:

 

dbms_system.set_int_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
intval  IN BINARY_INTEGER);

exec dbms_system.set_int_param_in_session(10, 161, 'sort_area_size', 1048576);

 

SET_BOOL_PARAM_IN_SESSION 过程用以给其他SESSION设置布尔类型的参数,如skip_unusable_indexes:

 

dbms_system.set_bool_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
bval    IN BOOLEAN);

exec dbms_system.set_bool_param_in_session(10, 161, 'skip_unusable_indexes', TRUE);

 

注意虽然有些parameter的值是TRUE或者False,但是这些参数实际可能是string字符串类型,而非boolean类型,具体可以通过show parameter来查看,如star_transformation_enabled参数默认虽然是FALSE,但是实际是一个String type的Parameter:

 

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

 

遗憾的是DBMS_SYSTEM包仅仅提供了设置整形和布尔类值的过程, 对于字符类值的参数尚不支持对其他session的修改。 换句话说就是如star_transformation_enabled这类的会话参数,DBMS_SYSTEM包没有提供一个可用的过程来对非本地的其他会话修改, 到目前为止(版本11.2.0.3)都不支持这种做法。针对这类字符值参数,据我所知如果要达到我们上述的目的那么只能用触发器的方法。

 

实际上Metalink上已经有相关的Note说明了该软件缺陷”SET A CHARACTER-STRING VALUED PARAMETER IN ANOTHER USERS SESSION”:

 

Hdr: 8939043 11.1.0.7 RDBMS 11.1.0.7 SQL LANGUAGE PRODID-5 PORTID-267
Abstract: SET A CHARACTER-STRING VALUED PARAMETER IN ANOTHER USERS SESSION

Using DBMS_SYSTEM you can set a Boolean parameter with the
SET_BOOL_PARAM_IN_SESSION procedure.  Similarly, you can also set an Integer
parameter with the SET_INT_PARAM_IN_SESSION procedure. 

There is no procedure in dbms_system or in other package or any other direct
method to set this parameter.
so, it should be a direct method to simulate something like 

alter session <number_of_session> set cursor_sharing='EXACT';

We have a specific process that is running very slowly since upgrading to
11g.  If we set optimizer_features_enabled =11.1.0.7 the process runs well.  
We can not set this parameter at the database level though as it causes
online errors in other parts of the system.

Posted

in

by

Tags:

Comments

Leave a Reply

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