Practice: Configuring and Observing Automatic In-Memory

Overview

This practice shows how to configure Automatic In-Memory and then observe how in-memory objects are automatically and dynamically populated in the IM column store without user intervention, and then possibly automatically evicted from the IM column store.

Before starting any new practice, refer to the Practices Environment recommendations.

Step 1 : Set up the environment with In-Memory Column Store

The shell script configures the IM column store to 110M, creates NO INMEMORY tables in HR schema in PDB21, and finally inserts rows in HR tables.


$ cd /home/oracle/labs/M104783GC10
$ /home/oracle/labs/M104783GC10/AutoIM_setup.sh
...
SQL> ALTER SYSTEM SET sga_target=812M SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET inmemory_size=110M SCOPE=SPFILE;

System altered.

SQL> ALTER SYSTEM SET query_rewrite_integrity=stale_tolerated SCOPE=SPFILE;

System altered.

SQL> SET ECHO OFF

System altered.

SQL> ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL=LOW SCOPE=SPFILE;

System altered.
...
SQL> CREATE TABLESPACE imtbs DATAFILE SIZE 10G;

Tablespace created.

SQL> EXIT
...
SQL> CREATE TABLE hr.emp INMEMORY AS SELECT * FROM hr.employees ;

Table created.

SQL> INSERT INTO hr.emp SELECT * FROM hr.emp;

107 rows created.
...
SQL> /

1753088 rows created.

SQL> COMMIT;

Commit complete.

SQL> EXIT
$

Step 2: Configure in-memory tables

  • Query the data dictionary to determine whether HR tables are specified as INMEMORY.

    
    $ sqlplus sys@PDB21 AS SYSDBA
    Enter password:
    
    Connected to:
    
    SQL> COL table_name FORMAT A18
    SQL> SELECT table_name, inmemory, inmemory_compression 
    FROM   dba_tables WHERE  owner='HR';
    
    TABLE_NAME         INMEMORY INMEMORY_COMPRESS
    ------------------ -------- -----------------
    REGIONS            DISABLED
    LOCATIONS          DISABLED
    DEPARTMENTS        DISABLED
    JOBS               DISABLED
    EMPLOYEES          DISABLED
    JOB_HISTORY        DISABLED
    EMP                ENABLED  FOR QUERY LOW
    COUNTRIES          DISABLED
    
    8 rows selected.
    
    SQL>
  • Apply the INMEMORY and MEMCOMPRESS FOR CAPACITY LOW attributes to the HR.JOB_HISTORY table.

    
    SQL> ALTER TABLE hr.job_history INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
    
    Table altered.
    
    SQL> SELECT table_name, inmemory, inmemory_compression 
    FROM   dba_tables WHERE  owner='HR';
    
    TABLE_NAME         INMEMORY INMEMORY_COMPRESS
    ------------------ -------- -----------------
    REGIONS            DISABLED
    LOCATIONS          DISABLED
    DEPARTMENTS        DISABLED
    JOBS               DISABLED
    EMPLOYEES          DISABLED
    JOB_HISTORY        ENABLED  FOR CAPACITY LOW
    EMP                ENABLED  FOR QUERY LOW
    COUNTRIES          DISABLED
    
    8 rows selected.
    
    
    SQL>

Step 3 : Configure Automatic In-Memory

  • Connect to the CDB root, then set INMEMORY_AUTOMATIC_LEVEL to HIGH, and re-start the database instance.

    
    SQl> CONNECT / AS SYSDBA
    Connected.
    SQL> ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL=HIGH SCOPE=SPFILE;
    
    System altered.
    
    SQL> SHUTDOWN IMMEDIATE
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL> STARTUP
    ORACLE instance started.
    
    Total System Global Area  851442944 bytes
    Fixed Size                  9571584 bytes
    Variable Size             440401920 bytes
    Database Buffers          276824064 bytes
    Redo Buffers                7204864 bytes
    In-Memory Area            117440512 bytes
    Database mounted.
    Database opened.
    SQL> ALTER PLUGGABLE DATABASE ALL OPEN;
    
    Pluggable database altered.
    
    SQL> 
  • Query the data dictionary to determine whether HR tables are specified as INMEMORY.

    
    SQl> CONNECT sys@PDB21 AS SYSDBA
    Enter password:
    Connected.
    SQL> SELECT table_name, inmemory, inmemory_compression 
    FROM   dba_tables WHERE  owner='HR';
    
    TABLE_NAME         INMEMORY INMEMORY_COMPRESS
    ------------------ -------- -----------------
    REGIONS            DISABLED
    LOCATIONS          DISABLED
    DEPARTMENTS        DISABLED
    JOBS               DISABLED
    EMPLOYEES          DISABLED
    JOB_HISTORY        ENABLED  FOR CAPACITY LOW
    EMP                ENABLED  FOR QUERY LOW
    COUNTRIES          DISABLED
    
    8 rows selected.
    
    SQL>

    Why are the HR tables not enabled to INMEMORY, except those already manually set to INMEMORY? Display the INMEMORY_AUTOMATIC_LEVEL in the PDB.

    
    SQl> SHOW PARAMETER INMEMORY_AUTOMATIC_LEVEL
    
    NAME                                 TYPE        VALUE
    ------------------------------------ ----------- -------------
    inmemory_automatic_level             string      LOW
    SQL> SELECT ispdb_modifiable FROM v$parameter WHERE name='inmemory_automatic_level';
    
    ISPDB
    -----
    TRUE
    
    SQL>
  • Set INMEMORY_AUTOMATIC_LEVEL to HIGH at the PDB level, and re-start PDB21.

    
    SQl> ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL=HIGH SCOPE=SPFILE;
    
    System altered.
    
    SQL> SHUTDOWN IMMEDIATE
    Pluggable Database closed.
    SQL> STARTUP
    Pluggable Database opened.
    SQL>

Step 4 : Test

  • Wait one minute to observe the HR tables to be automatically assigned the INMEMORY attribute.

    
    SQL> SELECT table_name, inmemory, inmemory_compression 
    FROM   dba_tables WHERE  owner='HR';
    
    TABLE_NAME         INMEMORY INMEMORY_COMPRESS
    ------------------ -------- -----------------
    REGIONS            ENABLED  AUTO
    LOCATIONS          ENABLED  AUTO
    DEPARTMENTS        ENABLED  AUTO
    JOBS               ENABLED  AUTO
    EMPLOYEES          ENABLED  AUTO
    JOB_HISTORY        ENABLED  FOR CAPACITY LOW
    EMP                ENABLED  FOR QUERY LOW
    COUNTRIES          DISABLED
    
    8 rows selected.
    
    SQL>

    Observe that HR.JOB_HISTORY and HR.JOB_EMP which were manually specified as INMEMORY, retain their previous settings.

    Why is HR.COUNTRIES not automatically enabled?

    
    SQL> ALTER TABLE hr.countries INMEMORY;
     ALTER TABLE hr.countries INMEMORY
    *
    ERROR at line 1:
    ORA-64358: in-memory column store feature not supported for IOTs
    
    SQL>
  • Populate the in-memory tables into the IM Column Store.

    
    SQL> @/home/oracle/labs/M104783GC10/AutoIM_scan_AUTO.sql
    SQL> set echo on
    SQL> begin
      2  for i in (select constraint_name, table_name from dba_constraints where table_name='EMPLOYEES') LOOP
      3  execute immediate 'alter table hr.employees drop constraint '||i.constraint_name||' CASCADE';
      4  end loop;
      5  end;
      6  /
    
    PL/SQL procedure successfully completed.
    
    SQL> drop index hr.EMP_EMP_ID_PK;
    drop index hr.EMP_EMP_ID_PK
                  *
    ERROR at line 1:
    ORA-01418: specified index does not exist
    
    
    SQL>
    SQL> INSERT INTO hr.employees SELECT * FROM hr.employees;
    
    107 rows created.
    
    SQL> /
    
    214 rows created.
    ...
    SQL> /
    
    27392 rows created.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL> /
    ...
    SQL> /
    
    Commit complete.
    
    SQL> COMMIT;
    
    Commit complete.
    
    SQL>

    Why aren't the ENABLED AUTO tables populated into the IM column store? The internal statistics are not sufficient yet to identify cold and hot data in the IM column store to consider which segments can be populated into the IM column store.

  • Execute the /home/oracle/labs/M104783GC10/AutoIM_scan_AUTO.sql SQL script to insert more rows into the HR.EMPLOYEES table, query the HR.EMPLOYEES table, and then possibly get the table automatically populated into the IM column store.

    
    SQL> @/home/oracle/labs/M104783GC10/AutoIM_scan.sql
    SQL> SELECT /*+ FULL(hr.employees) NO_PARALLEL(hr.employees) */ count(*) FROM hr.employees;
    
      COUNT(*)
    ----------
           107
    
    SQL> SELECT /*+ FULL(hr.departments) NO_PARALLEL(hr.departments) */ count(*) FROM hr.departments;
    
      COUNT(*)
    ----------
            27
    
    SQL> SELECT /*+ FULL(hr.locations) NO_PARALLEL(hr.locations) */ count(*) FROM hr.locations;
    
      COUNT(*)
    ----------
            23
    
    SQL> SELECT /*+ FULL(hr.jobs) NO_PARALLEL(hr.jobs) */ count(*) FROM hr.jobs;
    
      COUNT(*)
    ----------
            19
    
    SQL> SELECT /*+ FULL(hr.regions) NO_PARALLEL(hr.regions) */ count(*) FROM hr.regions;
    
      COUNT(*)
    ----------
             4
    
    SQL> SELECT /*+ FULL(hr.emp) NO_PARALLEL(hr.emp) */ count(*) FROM hr.emp;
    
      COUNT(*)
    ----------
       3506176
    
    
    SQL>
  • Display the population status of the HR tables into the IM Column Store. You may have to wait for a few minutes before the population of EMPLOYEES table starts.

    
    SQL> COL segment_name FORMAT A18
    SQL> SELECT segment_name, inmemory_size, bytes_not_populated, inmemory_compression FROM v$im_segments;
    
    SEGMENT_NAME       INMEMORY_SIZE BYTES_NOT_POPULATED INMEMORY_COMPRESS
    ------------------ ------------- ------------------- -----------------
    PROMOTIONS               1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    TIMES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    SALES_ZM                35717120                   0 AUTO
    SALES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    EMP                     24969216           125337600 FOR QUERY LOW
    SALES                    1310720                   0 AUTO
    COSTS                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    SALES                    1310720                   0 AUTO
    
    23 rows selected.
    
    SQL> EXIT
    $

    Observe the HR.EMPLOYEES table is now populated with an INMEMORY_COMPRESS value set to AUTO. Compression used the automatic in-memory management based on internal statistics. After some time, the HR.EMP tablemay be evicted according to the internal statistics. If you re-query the HR.EMP table, the server may decide to evict the HR.EMPLOYEES table to let HR.EMP back into the IM column store.