[20190917]oracle引數deferred屬性.txt

[20190917]oracle引數deferred屬性.txt

--//以前測試要修改一些引數，如果發現不能修改，我一般選擇寫入spfile檔案，重啟測試檔案。
--//或者寫入initXXXX.ora檔案，使用該引數檔案啟動資料庫例項。
--//實際上有小量引數是具有deferred屬性。這類引數對當前回話不生效，對以後登入的回話生效。
--//這類型別的引數不是很多，簡單看看順便做一個記錄：

1.環境：
SCOTT@test01p> @ ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

2.測試:
SYS@test> select name,DISPLAY_VALUE,ISSES_MODIFIABLE, ISSYS_MODIFIABLE from v$parameter where  ISSYS_MODIFIABLE='DEFERRED';
NAME                           DISPLAY_VALUE                  ISSES ISSYS_MOD
------------------------------ ------------------------------ ----- ---------
backup_tape_io_slaves          FALSE                          FALSE DEFERRED
recyclebin                     on                             TRUE  DEFERRED
session_cached_cursors         50                             TRUE  DEFERRED
audit_file_dest                D:\APP\ORACLE\ADMIN\TEST\ADUMP FALSE DEFERRED
object_cache_optimal_size      10240000                       TRUE  DEFERRED
object_cache_max_size_percent  10                             TRUE  DEFERRED
sort_area_size                 65536                          TRUE  DEFERRED
sort_area_retained_size        0                              TRUE  DEFERRED
olap_page_pool_size            0                              TRUE  DEFERRED
9 rows selected.
--//12cR2僅僅9個這樣型別的引數(不包括隱含引數).可以發現一個引數session_cached_cursors引數設定其實不用重啟資料庫.
--//其它應該很少修改.

SYS@test> alter system set session_cached_cursors=100;
alter system set session_cached_cursors=100
                                          *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

SYS@test> alter system set session_cached_cursors=100 scope=memory;
alter system set session_cached_cursors=100 scope=memory
                                            *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

--//按照以前我如果要修改就修改spfile檔案,然後等待時機重啟資料庫.
SYS@test> alter system set session_cached_cursors=100 DEFERRED scope=memory;
System altered.

--//再重新登入
SYS@test> @ hide session_cached_cursors
NAME                   DESCRIPTION                              DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE
---------------------- ---------------------------------------- ------------- ------------- ------------
session_cached_cursors Number of cursors to cache in a session. TRUE          100           100

3.修改我自己的指令碼:
$ cat hide.sql
col name format a40
col description format a66
col session_value format a22
col default_value format a22
col system_value format a22

select
   a.ksppinm  name,
   a.ksppdesc DESCRIPTION,
   b.ksppstdf DEFAULT_VALUE,
   b.ksppstvl SESSION_VALUE,
   c.ksppstvl SYSTEM_VALUE,
   DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')  ISSES_MODIFIABLE,
   DECODE
       (
          BITAND (a.ksppiflg / 65536, 3)
         ,1, 'IMMEDIATE'
         ,2, 'DEFERRED'
         ,3, 'IMMEDIATE'
         ,'FALSE'
       ) ISSYS_MODIFIABLE
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx
 and a.indx = c.indx
 and lower(a.ksppinm) like lower('%&1%')
order by 1;

SYS@test> @ hide.txt session_cached_cursors
NAME                   DESCRIPTION                              DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
---------------------- ---------------------------------------- ------------- ------------- ------------ ----- ---------
session_cached_cursors Number of cursors to cache in a session. TRUE          100           100          TRUE  DEFERRED

--//實際上對於引數session_cached_cursors,對於會話是可以修改的,.執行如下:
SYS@test> alter session set session_cached_cursors=200 ;
Session altered.

4.繼續測試:
--//再來看看open_cursor引數:
SYS@test> @ hide open_cursor
NAME                       DESCRIPTION                                   DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE          FALSE         FALSE        TRUE  FALSE
open_cursors               max # cursors per session                     FALSE         300           300          FALSE IMMEDIATE

--//可以發現ISSYS_MODIFIABLE=IMMEDIATE.按照道理立即生效而實際情況呢?
--//下面一段測試ora-04031例子,先開啟2個會話分別以scott,sys使用者登入:
--//session 1,scott使用者登入:
create table a1 ( id1 number,id2 number);

--//session 2,sys使用者登入:
SYS@test01p> @ hide open_cursor
NAME                       DESCRIPTION                                   DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE          FALSE         FALSE        TRUE  FALSE
open_cursors               max # cursors per session                     FALSE         300           300          FALSE IMMEDIATE
--//open_cursors=300.

--//session 1:
SCOTT@test01p> alter system set open_cursors=50000 scope=memory ;
System altered.

--//session 2:
SYS@test01p> @ hide open_cursor
NAME                       DESCRIPTION                                   DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
-------------------------- --------------------------------------------- ------------- ------------- ------------ ----- ---------
_close_cached_open_cursors close cursors cached by PL/SQL at each commit TRUE          FALSE         FALSE        TRUE  FALSE
open_cursors               max # cursors per session                     FALSE         50000         50000        FALSE IMMEDIATE
--//你可以發現open_cursors無論是session還是system,都是50000.

--//session 1:
--//不要退出執行如下指令碼
$ cat sharepool/shp7
declare
msql varchar2(500);
mcur number;
mstat number;
begin
    for i in 1 .. 49000 loop
        mcur := dbms_sql.open_cursor;
        msql := 'select id1 from a1 where id2='||to_char(i);
        dbms_sql.parse(mcur,msql,dbms_sql.native);
--      mstat := dbms_sql.execute(mcur);
    end loop;
end;
/
--//註解那行不用執行。
--//測試例子是開啟游標並分析,由於沒有使用繫結變數,並且游標沒有正常關閉,大量消耗共享池.

SCOTT@test01p> @ sharepool/shp7
declare
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at line 9

--//如果不退出執行，出現ORA-01000: maximum open cursors exceeded.很明顯游標數量不足也就是當前open_cursors=300.
--//如果退出再次執行，就會消耗大量共享池，出現ora-04031錯誤。

SCOTT@test01p> @ sharepool/shp7
declare
*
ERROR at line 1:
ORA-04031: unable to allocate 256 bytes of shared memory ("shared pool","select name,online$,contents...","SQLA^56d22d85","qeeRwo: qeeCreateRwo")
ORA-06512: at "SYS.DBMS_SQL", line 1134
ORA-06512: at line 9

總結:
--//實際上想透過一些簡單的例子說明就是簡簡單單的引數設定,oracle有時候都能人搞暈.在看看那些隱含引數是具有DEFERRED特性的.
/* Formatted on 2019/9/17 21:48:38 (QP5 v5.227.12220.39754) */
SELECT *
  FROM (SELECT a.ksppinm name
              ,a.ksppdesc DESCRIPTION
              ,b.ksppstdf DEFAULT_VALUE
              ,b.ksppstvl SESSION_VALUE
              ,c.ksppstvl SYSTEM_VALUE
              ,DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')
                  ISSES_MODIFIABLE
              ,DECODE
               (
                  BITAND (a.ksppiflg / 65536, 3)
                 ,1, 'IMMEDIATE'
                 ,2, 'DEFERRED'
                 ,3, 'IMMEDIATE'
                 ,'FALSE'
               )
                  ISSYS_MODIFIABLE
          FROM x$ksppi a, x$ksppcv b, x$ksppsv c
         WHERE a.indx = b.indx AND a.indx = c.indx)
 WHERE ISSYS_MODIFIABLE = 'DEFERRED';

--//輸出太長,不在貼出.

SELECT count(*)
  FROM (SELECT a.ksppinm name
              ,a.ksppdesc DESCRIPTION
              ,b.ksppstdf DEFAULT_VALUE
              ,b.ksppstvl SESSION_VALUE
              ,c.ksppstvl SYSTEM_VALUE
              ,DECODE (BITAND (a.ksppiflg / 256, 1), 1, 'TRUE', 'FALSE')
                  ISSES_MODIFIABLE
              ,DECODE
               (
                  BITAND (a.ksppiflg / 65536, 3)
                 ,1, 'IMMEDIATE'
                 ,2, 'DEFERRED'
                 ,3, 'IMMEDIATE'
                 ,'FALSE'
               )
                  ISSYS_MODIFIABLE
          FROM x$ksppi a, x$ksppcv b, x$ksppsv c
         WHERE a.indx = b.indx AND a.indx = c.indx)
 WHERE ISSYS_MODIFIABLE = 'DEFERRED';

  COUNT(*)
----------
       124

--//可以發現許多隱含引數具有這個特性的.