[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)
--注意~内容。不过没有隐含参数的修改。