[20160713]改变参数在另外的会话.txt

最新推荐文章于 2025-09-05 21:27:49 发布

转载最新推荐文章于 2025-09-05 21:27:49 发布 · 55 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://yq.aliyun.com/articles/279725

文章标签：

#数据库

本文介绍如何使用Oracle DBMS_SYSTEM包中的SET_INT_PARAM_IN_SESSION和SET_BOOL_PARAM_IN_SESSION过程来动态调整会话级参数，包括整型和布尔型参数的设置，并通过实际案例展示了这些过程的有效性和应用方式。

[20160713]改变参数在另外的会话.txt

--DBMS_SYSTEM包包含两个过程SET_BOOL_PARAM_IN_SESSION和SET_INT_PARAM_IN_SESSION，它仅仅支持逻辑值true与false，以及某个数
--值的修改，好像不支持字符串的修改。自己测试看看

1.环境：

SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.测试DBMS_SYSTEM.SET_INT_PARAM_IN_SESSION

SCOTT@book> @ &r/spid
SID SERIAL# SPID PID P_SERIAL# C50
---------- ---------- ------ ------- ---------- --------------------------------------------------
68 1021 57797 30 109 alter system kill session '68,1021' immediate;

SCOTT@book> show parameter optimizer_index_caching
NAME TYPE VALUE
----------------------- -------- ------
optimizer_index_caching integer 0

--打开另外的会话，执行：
SYS@book> exec dbms_system.SET_INT_PARAM_IN_SESSION(68, 1021, 'optimizer_index_caching',20);
PL/SQL procedure successfully completed.

SCOTT@book> show parameter optimizer_index_caching
NAME TYPE VALUE
----------------------- -------- ------
optimizer_index_caching integer 20

--OK!有效。

3.测试DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION

SYS@book> @ &r/hide _optimizer_ignore_hints
old 10: and lower(a.ksppinm) like lower('%&1%')
new 10: and lower(a.ksppinm) like lower('%_optimizer_ignore_hints%')
NAME DESCRIPTION DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
----------------------- ---------------------------------------- ------------- ------------- -------------
_optimizer_ignore_hints enables the embedded hints to be ignored TRUE FALSE FALSE

SYS@book> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(68, 1021, '_optimizer_ignore_hints',true);
PL/SQL procedure successfully completed.

--我修改的是隐含参数，如果确定修改有效呢？查询GV$SES_OPTIMIZER_ENV视图：

SCOTT@book> select * from GV$SES_OPTIMIZER_ENV where sid=68 and name like '%hint%';
INST_ID SID ID NAME SQL_FEATURE ISD VALUE
------- ---------- ---------- ----------------------- ----------- --- ------
1 68 146 _optimizer_ignore_hints QKSFM_ALL NO true

SYS@book> exec dbms_system.SET_BOOL_PARAM_IN_SESSION(68, 1021, '_optimizer_ignore_hints',false);
PL/SQL procedure successfully completed.

SCOTT@book> select * from GV$SES_OPTIMIZER_ENV where sid=68 and name like '%hint%';
no rows selected

--你也可以执行一台语句看看执行计划outline。例子：
SCOTT@book> @ &r/dpc '' outline
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 80baj2c2ur47u, child number 0
-------------------------------------
select * from dept where deptno=20
Plan hash value: 2852011669
----------------------------------------------------------------------------------------
| Id | Operation                   | Name    | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |        |       |     1 (100)|          |
|   1 | TABLE ACCESS BY INDEX ROWID| DEPT    |      1 |    20 |     1   (0)| 00:00:01 |
|* 2 |   INDEX UNIQUE SCAN         | PK_DEPT |      1 |       |     0   (0)|          |
----------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / DEPT@SEL$1
   2 - SEL$1 / DEPT@SEL$1
Outline Data
-------------
/*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
      DB_VERSION('11.2.0.4')
      OPT_PARAM('optimizer_index_caching' 20)
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "DEPT"@"SEL$1" ("DEPT"."DEPTNO"))
      END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("DEPTNO"=20)