[20160713]改變引數在另外的會話.txt

[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)

--注意~內容。不過沒有隱含引數的修改。