ITPub博客

首页 > 数据库 > Oracle > [20190917]oracle参数deferred属性.txt

[20190917]oracle参数deferred属性.txt

原创 Oracle 作者:lfree 时间:2019-09-17 22:12:52 0 删除 编辑

[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

--//可以发现许多隐含参数具有这个特性的.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2657326/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2485
  • 访问量
    6292788