oracle 19c inmemory 的初步实践

﹏ヽ暗。殇╰゛Y 2022-09-09 13:51 253阅读 0赞

oracle 数据库的 inmemory 组件(列式存储)可以认为是一大利器，可以一定程度上解决大表碰到的性能问题。

os: centos 7.6  
db: oracle 19.3

# 版本 #

# cat /etc/centos-release
    CentOS Linux release 7.6.1810 (Core)
    #
    # 
    # su - oracle
    $
    $
    $ sqlplus / as sysdba;
    
    SQL*Plus: Release 19.0.0.0.0 - Production on Wed Sep 1 15:02:57 2021
    Version 19.3.0.0.0
    
    Copyright (c) 1982, 2019, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    Version 19.3.0.0.0

# inmemory 参数 #

memory

SQL> show parameter memory%target 
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    memory_max_target		     big integer 0
    memory_target			     big integer 0

sga

SQL> show parameter sga_;
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    sga_max_size			     big integer 1744M
    sga_min_size			     big integer 0
    sga_target			     big integer 1744M
    unified_audit_sga_queue_size	     integer	 1048576

inmemory

SQL> show parameter inmemory;
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    inmemory_adg_enabled		     boolean	 TRUE
    inmemory_automatic_level	     string	 OFF
    inmemory_clause_default 	     string
    inmemory_expressions_usage	     string	 ENABLE
    inmemory_force			     string	 DEFAULT
    inmemory_max_populate_servers	     integer	 0
    inmemory_optimized_arithmetic	     string	 DISABLE
    inmemory_prefer_xmem_memcompress     string
    inmemory_prefer_xmem_priority	     string
    inmemory_query			     string	 ENABLE
    inmemory_size			     big integer 0
    
    NAME				     TYPE	 VALUE
    ------------------------------------ ----------- ------------------------------
    inmemory_trickle_repopulate_servers_ integer	 1
    percent
    inmemory_virtual_columns	     string	 MANUAL
    inmemory_xmem_size		     big integer 0
    optimizer_inmemory_aware	     boolean	 TRUE

可以看到默认的 inmemory\_size = 0 ，处于未启用状态。

# 启用 inmemory #

由于 inmemory 会占用 sga 的大小，所以不能设置太大，同时要检查 sga、memory 的大小。

SQL> alter system set inmemory_size=200M scope=spfile;
    
    SQL> startup force;

默认sys用户下的对象及SYSTEM、SYSAUX表空间上的对象无法使用IMO特性，但通过设置"\_enable\_imc\_sys"隐含参数也可以使用

创建基础表(scott用户)

$ sqlplus / as sysdba;
    
    SQL> show pdbs;
    
        CON_ID CON_NAME			  OPEN MODE  RESTRICTED
    ---------- ------------------------------ ---------- ----------
    	 2 PDB$SEED			  READ ONLY  NO
    	 3 PDB1 			  READ WRITE NO
    
    SQL> alter session set container=pdb1;
    
    SQL> create user scott identified by scottscott default tablespace users;
    grant connect,resource to scott;
    alter user scott quota unlimited on users;

$ sqlplus scott/scottscott@192.168.56.91:1521/pdb1
    
    SQL> create table test1 (id integer primary key,name varchar(50),memo varchar(50));
    
    insert into test1(id,name,memo)
    select level as id,
           dbms_random.random(),
           dbms_random.random()
      from dual
      connect by level <=100000
    ;
    
    commit;
    
    SQL> alter table test1 inmemory priority high;
    
    SQL> select/*+full(t1) */ count(*) from test1 t1;
    
    SQL> EXEC DBMS_INMEMORY.REPOPULATE('SCOTT','TEST1', FORCE=>TRUE);

SQL> select pool,ALLOC_BYTES/1024/1024,USED_BYTES/1024/1024,POPULATE_STATUS,con_id from V$INMEMORY_AREA;
    
    SQL> select * from v$im_column_level ic;

取消 inmemory

SQL> alter table test1 no inmemory;

参考:  
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/INMEMORY\_SIZE.html\#GUID-B5BEB6BF-5308-485F-920D-CBB584DDDE8F