DBMS_INMEMORY_ADMIN

DBMS_INMEMORY_ADMIN provides interfaces for managing an In-Memory FastStart (IM FastStart) area and In-Memory Expressions (IM expressions).

IM FastStart area为使用ASSM表空间实时存储IM column store数据方便下次重启时载入

The IM FastStart area stores data that optimizes the population of the IM column store when the database restarts. Because the database reads columnar data directly from persistent storage without needing to compress or format it, population is faster when a database instance restarts.

When you enable IM FastStart for the IM column store, you must specify an ASSM tablespace for the IM FastStart area. The tablespace stores the data in a SecureFiles LOB named SYSDBIMFS_LOGSEG$. The SYSAUX tablespace stores the metadata. When data is populated or repopulated in the IM column store, the database automatically writes the data to the IM FastStart area. You cannot manually force a write. If you specify an object as NO INMEMORY, then the database removes it from the IM FastStart area.

When the IM FastStart area is under space pressure, the database automatically drops the oldest 15% of segments and continues saving columnar data. If space is unavailable, then the database stops writing to the IM FastStart area.

• DBMS_INMEMORY_ADMIN Operational Notes

It is possible for a DBMS_INMEMORY_ADMIN FastStart operation to fail or be interrupted. In a failure or interruption scenario, the following rules determine which subprograms you can use:

1. If FASTSTART_ENABLE does not succeed, then the only permitted operation is re-executing FASTSTART_ENABLE.
2. If FASTSTART_MIGRATE_STORAGE does not succeed, then the only permitted operation is re-executing FASTSTART_MIGRATE_STORAGE.
3. If FASTSTART_DISABLE does not succeed, then all DBMS_INMEMORY_ADMIN operations are permitted.

（二）FASTSTART programs

1. FASTSTART_DISABLE Procedure

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_DISABLE;

When you execute the procedure, the database executes the following actions:

1. Waits until all IM FastStart operations complete
2. Disables the IM FastStart feature, and performs the following operations:

Cleans the IM FastStart area

Deletes IM FastStart metadata stored in the SYSAUX tablespace

Releases the IM FastStart tablespace (but does not delete it)

This procedure does not interrupt or affect any concurrent IM column store operations.

查看是否清空FASTSTART

COL OWNER FORMAT a5

COL SEGMENT_NAME FORMAT a20

SELECT l.OWNER, l.SEGMENT_NAME, SUM(s.BYTES)/1024/1024 MB

FROM DBA_LOBS l, DBA_SEGMENTS s

WHERE l.SEGMENT_NAME = s.SEGMENT_NAME AND l.TABLESPACE_NAME = 'FS_TBS'

GROUP BY l.OWNER, l.SEGMENT_NAME;

1. FASTSTART_ENABLE Procedure

DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE(

   tbs_name    IN    VARCHAR2,

   nologging   IN    BOOLEAN DEFAULT TRUE);

If the nologging parameter is set to FALSE, then the database creates the FastStart LOB with the LOGGING option. If set to TRUE (default), then the database creates the LOB with the NOLOGGING option.

在启用FASTSTART时指定的表空间必须为空，且最少为INMEMORY_SIZE的2倍大小

Only one FastStart tablespace can exist for every PDB or non-CDB. The specified tablespace must have enough space to store data for the IM column store, and it must not contain any other data before it is designated for the FastStart area. Oracle recommends sizing the tablespace at least twice of the size of the INMEMORY_SIZE initialization parameter.

The database does not create the FastStart area on disk until the IM column store is populated. After population, the data periodically saves the columnar data (but not metadata such as the transaction journal) to the FastStart area, which is represented on disk as the SYSDBIMFS_LOBSEG$ segment. In an Oracle Real Application Clusters (Oracle RAC) environment, IM FastStart data is shared across all nodes.

IM FastStart is not supported in a standby database instance.

1. FASTSTART_MIGRATE_STORAGE Procedure

This procedure moves the In-Memory FastStart (IM FastStart) data and catalogs from the current tablespace to a new tablespace.

DBMS_INMEMORY_ADMIN.FASTSTART_MIGRATE_STORAGE( tbs_name    IN    VARCHAR2 );

When you execute the procedure, the database executes the following actions:

1. Waits until all IM FastStart operations complete
2. Disables the IM FastStart feature
3. Copies IM FastStart data and metadata to the new tablespace, leaving the old tablespace intact
4. Re-enables IM FastStart the feature

1. GET_FASTSTART_TABLESPACE Function

This function returns the tablespace assigned to In-Memory FastStart (IM FastStart). If the feature is disabled, then the function returns NOT ENABLED.

VARIABLE b_fstbs VARCHAR2(20)

BEGIN

   :b_fstbs := DBMS_INMEMORY_ADMIN.GET_FASTSTART_TABLESPACE;

END;

/

PRINT b_fstbs

（三）IM Expressions programs

1. IME_CAPTURE_EXPRESSIONS Procedure

获取最新20 hottest expression并设置为虚拟列

This procedure captures and populates the 20 most frequently accessed (“hottest”) expressions in the database in the specified time range.

DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS( snapshot    IN    VARCHAR2);

Snapshot：Specifies a snapshot that defines the period of time from which expression statistics are considered. You can specify either of the following values:

CUMULATIVE：The database considers all expression statistics since the creation of the database.

CURRENT：The database considers only expressions statistics from the past 24 hours.

When you invoke this procedure, the database queries the Expression Statistics Store (ESS), and considers only expressions on tables that are at least partially populated in the IM column store. The database adds the 20 hottest expressions to their respective tables as hidden virtual columns, prefixed with the string SYS_IME, and applies the default INMEMORY column compression clause. If any SYS_IME columns added during a previous invocation are no longer in the latest top 20 list, then the database marks them as NO INMEMORY.

Note: You cannot execute IME_CAPTURE_EXPRESSIONS on a standby database.

Note: IM expressions do not support NLS-dependent data types.

1. IME_DROP_ALL_EXPRESSIONS Procedure

它会drop所有表的SYS_IME列，同时它还会清除这些表使用的IMEU与EMCU，如果这些对象的IM PRORITY为NONE, 需要使用DBMS_INMEMORY.POPULATE或全表扫描来重新载入

The IME_DROP_ALL_EXPRESSIONS procedure drops all SYS_IME columns from all tables, regardless of whether they have the INMEMORY attribute. In effect, the procedure acts as a database-wide reset button.

Using IME_DROP_ALL_EXPRESSIONS triggers a drop of all IMEUs and IMCUs for segments that have SYS_IME columns.

For example, if 50 populated tables have one SYS_IME column each, then IME_DROP_ALL_EXPRESSIONS removes all 50 tables from the IM column store. To populate these segments again, you must use the DBMS_INMEMORY.POPULATE procedure or perform a full table scan.

DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS();

1. IME_POPULATE_EXPRESSIONS Procedure

This procedure forces the population of expressions captured in the latest invocation of DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS.

DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

此过程会强制重载所有表的SYS_IME列，如果你只重载某个表的SYS_IME，使用DBMS_INMEMORY.REPOPULATE来重载IMCU即可

If you do not invoke this procedure, then the database gradually repopulates SYS_IME columns when their parent IMCUs are repopulated. If a table is not repopulated, then any new SYS_IME columns captured by the IME_CAPTURE_EXPRESSIONS procedure are not populated.

Internally, the procedure invokes DBMS_INMEMORY.REPOPULATE for all tables that have SYS_IME columns with the INMEMORY attribute. To populate SYS_IME columns in a specified subset of tables, use DBMS_INMEMORY.REPOPULATE instead of IME_POPULATE_EXPRESSIONS.