本文共 3667 字，大约阅读时间需要 12 分钟。
[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 Production2.测试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 FALSESYS@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 trueSYS@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)--注意~内容。不过没有隐含参数的修改。
转载地址：http://zngda.baihongyu.com/