标签归档：ORACLE 12C

ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

发表于 2015 年 3 月 10 日 由 惜分飞

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
ORACLE 12C IDENTITY
GENERATED ALWAYS AS IDENTITY方式测试

C:\Users\ffcheng>sqlplus chf/xifenfei@pdb

SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

上次成功登录时间: 星期五 12月 19 2014 21:00:26 +08:00

连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

XFF_PDB@CHF> select * from v$version;

BANNER                                                 CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.          0
1.0.2.0 - 64bit Production

PL/SQL Release 12.1.0.2.0 - Production                      0
CORE    12.1.0.2.0      Production                          0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ          0
ction

NLSRTL Version 12.1.0.2.0 - Production                      0

XFF_PDB@CHF>  create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na
me varchar2(100));

表已创建。

XFF_PDB@CHF> select object_name,object_type from user_objects;

OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
ISEQ$$_91982    SEQUENCE
T_XIFENFEI      TABLE

XFF_PDB@CHF> set long 10000
XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_XIFENFEI"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE
   9999999999999999999999999999 INCREMENT BY 1 START WITH 1 
   CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "INMEMORY"

XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM');
INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM')
*
第 1 行出现错误:
ORA-32795: 无法插入到“始终生成”身份列


XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM');

已创建 1 行。

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM');

已创建 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> col name for a30
XFF_PDB@CHF> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         1 WWW.XIFNEFEI.COM
         2 WWW.orasos.COM

XFF_PDB@CHF> update t_xifenfei set id=3 where id=2;
update t_xifenfei set id=3 where id=2
                      *
第 1 行出现错误:
ORA-32796: 无法更新“始终生成”身份列

XFF_PDB@CHF> delete from t_xifenfei where id=1;

已删除 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual;

   NEXTVAL
----------
         3

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM');

已创建 1 行。

XFF_PDB@CHF> commit;

提交完成。

XFF_PDB@CHF> select * from t_xifenfei;

        ID NAME
---------- ------------------------------
         2 WWW.orasos.COM
         4 WWW.ORASOS.COM

XFF_PDB@CHF> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1
2';

会话已更改。

XFF_PDB@CHF>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------

D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc

XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM');

已创建 1 行。

XFF_PDB@CHF> COMMIT;

提交完成。

--跟踪trace文件
PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460 
ad='7ff05d11a18' sqlid='21uzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM')
END OF STMT
PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)'
STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE  ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)'
WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971

XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI
TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);

表已更改。

XFF_PDB@CHF> drop sequence ISEQ$$_91982;
drop sequence ISEQ$$_91982
              *
第 1 行出现错误:
ORA-32794: 无法删除系统生成的序列

XFF_PDB@CHF> drop table t_xifenfei;

表已删除。

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE

XFF_PDB@CHF> purge table t_xifenfei;

表已清除。

XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';

未选定行

这里的出来几个结论：
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

GENERATED BY DEFAULT AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI
TY,name varchar2(100)) tablespace users;

表已创建。

XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com');

已创建 1 行。

XFF_PDB@CHF> col name for a20
XFF_PDB@CHF> select * from t_xifenfei2;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
         1 www.orasos.com

XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com');
insert into t_xifenfei2 values (null,'www.xifenfei.com')
                                *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("CHF"."T_XIFENFEI2"."ID")


XFF_PDB@CHF> desc t_xifenfei2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------

 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

得出结论：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A
S IDENTITY,name varchar2(100)) tablespace users;

表已创建。

XFF_PDB@CHF> desc t_xifenfei3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------

 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> select * from t_xifenfei3;

        ID NAME
---------- --------------------
         1 www.xifenfei.com

测试结论：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值

传统自增长列实现方法

XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace
users;

表已创建。

XFF_PDB@CHF> create sequence xff_sequence
  2  increment by 1
  3  minvalue 1
  4  nomaxvalue
  5  start with 1
  6  cache 20
  7  order;

序列已创建。

XFF_PDB@CHF> create or replace trigger xifenfei_id
  2  before insert on t_xifenfei4
  3  for each row
  4  begin
  5  select xff_sequence.nextval into :new.id from dual;
  6  end;
  7  /

触发器已创建

XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.xifenfei.com');

已创建 1 行。

XFF_PDB@CHF> select * from t_xifenfei4;

        ID NAME
---------- --------------------
         1 www.xifenfei.com
发表在 ORACLE 12C | 标签为 , | 评论关闭

设置pdb随cdb一起启动

发表于 2014 年 11 月 28 日 由 惜分飞

在Oracle 12.1.0.1版本中,在cdb数据库启动过程中,业务pdb无法自动open,如果要实现该功能,需要人工写触发器来实现cdb open后,pdb 给open起来.在12.1.0.2及其以后版本,可以通过设置ALTER PLUGGABLE DATABASE PDB SAVE STATE来实现在cdb open之后业务pdb能够自动open.
数据库启动后pdb未自动open

XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。

Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             394265296 bytes
Database Buffers          297795584 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

XFF_CDB$ROOT@SYS> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED

查询dba_pdb_saved_states无记录

XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;

未选定行

在数据库mount状态下save state

XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB save state;

插接式数据库已变更。

XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;

未选定行

pdb为mount状态下,执行save state无记录,证明save state不成功

在数据库open状态下save state—-设置pdb随cdb启动

XFF_CDB$ROOT@SYS> ALTER PLUGGABLE DATABASE PDB open;

插接式数据库已变更。

XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB save state;

插接式数据库已变更。

XFF_CDB$ROOT@SYS> col con_name for a20
XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;

CON_NAME             STATE
-------------------- --------------
PDB                  OPEN

pdb为open状态下,执save state成功.
需要注意save state需要在pdb open情况下执行才能够生效.

重启数据库测试pdb随cdb启动

XFF_CDB$ROOT@SYS> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。

Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             364905168 bytes
Database Buffers          327155712 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

禁用pdb随cdb启动—DISCARD STATE

XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB DISCARD  state;

插接式数据库已变更。

XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;

未选定行

XFF_CDB$ROOT@SYS> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。

Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             364905168 bytes
Database Buffers          327155712 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
XFF_CDB$ROOT@SYS>

12.1.0.1中设置pdb随cdb启动

CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/
发表在 ORACLE 12C | 标签为 , , | 2 条评论

对于IN Memory Option 部分细节测试—主要当inmemory_size不足之时

发表于 2014 年 11 月 20 日 由 惜分飞

本文对于IMDB的几个特性进行了具体测试:
1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考
2. 对于IM空间不足已经存在的对象和加入新对象的现象
3. 对于PRIORITY级别进行了简单测试
数据库基本配置信息

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

SQL> show parameter inmemory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     2
inmemory_query                       string      ENABLE
inmemory_size                        big integer 100M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

数据库版本12.1.0.2,inmemory_size配置为100M

准备测试环境

SQL> create tablespace inmemory datafile 'D:\APP\FFCHENG\ORADATA\XFF\PDB\in_memory01.dbf' 
   2  size 100m autoextend on next 4m maxsize 10g;

表空间已创建。

SQL> create user chf identified by xifenfei;

用户已创建。

SQL> grant dba to chf;

授权成功。

SQL> alter user chf default tablespace inmemory;

用户已更改。

SQL> create table chf.t_inmemory1 as select * from dba_objects;

表已创建。

创建测试表空间,用户,测试表

测试压缩级别

SQL> alter table chf.t_inmemory1 inmemory NO MEMCOMPRESS;

表已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

未选定行

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS

--NO MEMCOMPRESS 压缩比例非常小，基本上不压缩

SQL> alter table chf.t_inmemory1 no inmemory ;

表已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

未选定行

SQL>  alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR DML
  2  ;

表已更改。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     FOR DML

--FOR DML 压缩比例非常小，基本上不压缩和NO MEMCOMPRESS在压缩效果上类似

SQL> alter table chf.t_inmemory1 no inmemory ;

表已更改。

SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY LOW;

表已更改。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       4325376 NONE     FOR QUERY LOW

-- FOR QUERY LOW为默认压缩级别,这里看压缩比例在3:1左右,具体取决于数据

SQL> alter table chf.t_inmemory1 no inmemory ;

表已更改。

SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY HIGH;

表已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

未选定行

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       3276800 NONE     FOR QUERY HIGH

-- FOR QUERY HIGH,这里看压缩比例在4:1左右,具体取决于数据


SQL> alter table chf.t_inmemory1 no inmemory ;

表已更改。

SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY LOW;

表已更改。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       2228224 NONE     FOR CAPACITY LOW

-- FOR CAPACITY LOW,这里看压缩比例在6:1左右,具体取决于数据

SQL> alter table chf.t_inmemory1 no inmemory ;

表已更改。

SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY HIGH;

表已更改。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488        131072 NONE     FOR CAPACITY HIGH

-- FOR CAPACITY HIGH,这里看压缩比例在10:1左右,具体取决于数据

这里可以看出来,压缩效果确实如Oracle所描述,级别越高压缩效果越好.

测试inmemory_size大小不足之时

SQL> alter table chf.t_inmemory1 inmemory no MEMCOMPRESS;

表已更改。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
     91040

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS

--dml插入数据,不再次查询数据,v$im_segments.inmemory_size不发生改变(这个是bug还是设计考虑？？)
SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;

已创建 91040 行。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS


SQL> commit;

提交完成。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS


SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
    182080

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      22282240 NONE     NO MEMCOMPRESS


SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;

已创建 182080 行。

SQL> commit;

提交完成。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      22282240 NONE     NO MEMCOMPRESS

--通过10046证明,虽然v$im_segments.inmemory_size值未及时更新,但是IMDB是生效的
SQL> oradebug setmypid
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;

会话已更改。

SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> oradebug TRACEFILE_NAME
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_7604.trc
SQL> SELECT COUNT(object_id) FROM chf.t_inmemory1;

COUNT(OBJECT_ID)
----------------
          364156

SQL> oradebug EVENT 10046 trace name context off
已处理的语句

PARSING IN CURSOR #455134016 len=44 dep=0 uid=0 oct=3 lid=0 tim=126773093621 hv=1133975269 
 ad='7ff07339500' sqlid='5909ukj1tf5r5'
SELECT COUNT(object_id) FROM chf.t_inmemory1
END OF STMT
PARSE #455134016:c=15600,e=3912,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=3154396630,tim=126773093620
WAIT #455134016: nam='Disk file operations I/O' ela= 154 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=126773093926
EXEC #455134016:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3154396630,tim=126773094005
WAIT #455134016: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773094044
FETCH #455134016:c=0,e=13751,p=0,cr=3110,cu=1,mis=0,r=1,dep=0,og=1,plh=3154396630,tim=126773107829
STAT #455134016 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3110 pr=0 pw=0 time=13751 us)'
STAT #455134016 id=2 cnt=364160 pid=1 pos=1 obj=91914 op='TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=3110 pr=0 
  pw=0 time=5386 us cost=17 size=455200 card=91040)'
WAIT #455134016: nam='SQL*Net message from client' ela= 116 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108164
FETCH #455134016:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3154396630,tim=126773108215
WAIT #455134016: nam='SQL*Net message to client' ela= 0 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108246


SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS


SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
    364160

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS

SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;

已创建 364160 行。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS


SQL> commit;

提交完成。

SQL> SELECT COUNT(*) FROM chf.t_inmemory1;

  COUNT(*)
----------
    728320

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS


SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      71892992 NONE     NO MEMCOMPRESS

--这里可以看出来INMEMORY_SIZE已经使用了71892992,再插入一次数据,一共100M的IM肯定不够使用


SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;

已创建 728320 行。

SQL> commit;

提交完成。

SQL> select count(object_id) from chf.t_inmemory1;

COUNT(OBJECT_ID)
----------------
         1456624

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS

--这里现在的INMEMORY_SIZE变为了63438848小于在插入数据之前的71892992,证明IM肯定出现问题,比如已经满了,
  v$im_segments显示值不准确


--测试刷新buffer_cache对IM的影响
SQL> alter system flush buffer_cache;

系统已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS

--结果证明无影响


autotrace结果
SQL> set autot trace exp stat
SQL> set lines 120
SQL> pages 1000
SQL> set pages 1000
SQL> select count(*) from chf.t_inmemory1;


执行计划
----------------------------------------------------------
Plan hash value: 3154396630

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY1 | 91040 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      16693  consistent gets
      16690  physical reads
          0  redo size
        546  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


10046结果
SQL ID: 1b61dgunxftdx Plan Hash: 3154396630

select count(object_id) 
from
 chf.t_inmemory1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.26       4.14      16689      22446          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.26       4.14      16689      22448          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=22446 pr=16689 pw=0 time=4144536 us)
   1456640    1456640    1456640   TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=22446 pr=16689 pw=0 
                                         time=2560999 us cost=17 size=455200 card=91040)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                     16689        0.03          3.05
  SQL*Net message from client                     2        5.40          5.40

--autotrace和10046都证明,当IM size不足之时,数据库未能够使用IM的特性,哪怕是部分也不能使用


--创建新对象存放IM中
SQL> create table chf.t_inmemory2 as select * from dba_objects;

表已创建。

SQL> alter table chf.t_inmemory2 inmemory;

表已更改。

SQL> select count(*) from chf.t_inmemory2;

  COUNT(*)
----------
     91041

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
  2  from v$im_segments where segment_name = 'T_INMEMORY1';

SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS

SQL> select count(*) from chf.t_inmemory2;


执行计划
----------------------------------------------------------
Plan hash value: 2042227318

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1532  consistent gets
       1530  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


SQL> alter table chf.t_inmemory1 no inmemory;

表已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
   2  from v$im_segments where segment_name = 'T_INMEMORY2';

未选定行

SQL> set autot traceonly exp stat
SQL> select count(*) from chf.t_inmemory2;


执行计划
----------------------------------------------------------
Plan hash value: 2042227318

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
         57  recursive calls
          0  db block gets
       1565  consistent gets
       1532  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autot off
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION 
   2  from v$im_segments where segment_name = 'T_INMEMORY2';

SEGMENT_NAME
-----------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW


SQL> set autot traceonly exp stat
SQL> select count(*) from chf.t_inmemory2;


执行计划
----------------------------------------------------------
Plan hash value: 2042227318

-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    53   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    53   (0)| 00:00:01 |
-----------------------------------------------------------------------------------


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
--当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM

上述测试几个结论:
1. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,v$im_segments.INMEMORY_SIZE显示不准确
2. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,当IM中的对象不能全部在IM中之时,对其对象操作,会转换成传统数据库操作,
不会使用部分的IM特性,但是执行计划依然提示使用INMEMORY
3. flush buffer_cache 不影响对象的IM
4. 当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM,
访问依然是传统方式,但是执行计划是INMEMORY

测试PRIORITY

 
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;

SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS


SQL> alter table chf.t_inmemory2  inmemory;

表已更改。

SQL> select count(*) from chf.t_inmemory2;

  COUNT(*)
----------
     91041

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;

SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS


SQL> alter table chf.t_inmemory1  inmemory no memcompress PRIORITY LOW;

表已更改。

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;

未选定行

SQL> select count(*) from chf.t_inmemory2;

  COUNT(*)
----------
     91041

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;

SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW

这里测试证明：
1. 指定PRIORITY不为none也需要访问对象后才能够放入IM中
2. 当IM不足时,PRIORITY级别高的会把级别低的对象刷出IM

特此声明:本文仅出自个人测试,得出结论,不可作为任何官方依据使用,具体环境需要具体测试

发表在 ORACLE 12C | 标签为 , | 一条评论