[20180529]模拟会话参数变化.txt

[20180529]模拟会话参数变化.txt

--//链接提到http://www.itpub.net/thread-2102533-1-1.html,修改参数cursor_sharing,第二天会发生变化.
--//我猜测可能是rac实例断开,有连上,看看这样是否参数会发生变化.

--//测试参考链接: http://blog.itpub.net/267265/viewspace-761721/

1.环境:
SCOTT@book> @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

--//在client端tnsnames.ora文件增加如下:
78G =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.100.78)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = book)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

2.测试:
R:\>sqlplus scott/book@78G

SCOTT@78G> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
         1         11 7212:8180                DEDICATED 58305       24          5 alter system kill session '1,11' immediate;

SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book                           SELECT        BASIC      NO

SCOTT@78G> alter session set optimizer_index_cost_adj=50;
Session altered.

SCOTT@78G> show parameter optimizer_index_cost_adj
NAME                     TYPE    VALUE
------------------------ ------- -----
optimizer_index_cost_adj integer 50
--//已经修改参数.

SCOTT@78G> select * from dba_objects ;
...
87016 rows selected.
--//执行过程中,重启数据库看看,执行startup force;
--//在数据库启动过程中可以发现用户会话hang,等待数据库启动完成后,继续显示执行结果集和.

SCOTT@78G> @ spid
       SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- ------------------------------------------------
       300         11 7212:8180                DEDICATED 58441       47          4 alter system kill session '300,11' immediate;
--//sid已经发生了变化,服务端进程号也发生了变化.

SCOTT@78G> show parameter optimizer_index_cost_adj
NAME                      TYPE     VALUE
------------------------- -------- -----------
optimizer_index_cost_adj  integer  100
--//会话参数发生了变化.

SCOTT@78G> select SERVICE_NAME,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER from v$session where sid  in ( select sid from v$mystat where rownum<=1);
SERVICE_NAME                   FAILOVER_TYPE FAILOVER_M FAI
------------------------------ ------------- ---------- ---
book                           SELECT        BASIC      YES
--//FAILED_OVER变成了Yes ,说明发生国failer_over.

SCOTT@78G> select count(*) from dba_objects;
  COUNT(*)
----------
     87016

--//可以确定前面的结果集显示是正常的.