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

最新推荐文章于 2023-10-24 11:38:25 发布

weixin_33670713

最新推荐文章于 2023-10-24 11:38:25 发布

阅读量169

点赞数

文章标签：数据库

之前有同学问我如何才能给非自身的其他会话设置会话级别(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.oracledatabase12g.com & www.askmaclean.com

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.