Syntax

DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER(
   parameter   IN    NUMBER,
   value       OUT   NUMBER);

Parameters

Syntax

DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER(
   parameter   IN    NUMBER,
   value       IN    NUMBER);

Parameters

Syntax

DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE( 
   tbs_name    IN    VARCHAR2,
   nologging   IN    BOOLEAN DEFAULT TRUE);

Parameters

Security Model

Administrator privileges are required to execute this procedure.

Usage Notes

To enable IM FastStart, the ASSM tablespace specified in FASTSTART_ENABLE must exist, and the SYSAUX tablespace must be online. 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. The database stores the FastStart metadata in the SYSAUX tablespace. In an Oracle Real Application Clusters (Oracle RAC) environment, IM FastStart data is shared across all nodes.

Whereas the initial loading of IMCUs into memory is expensive and CPU-bound, an IM FastStart tablespace requires intermittent I/O. The database periodically writes columnar data to the IM FastStart area. If a database instance must restart, then Oracle Database reads the columnar data directly from the IM FastStart area rather than reconstructing the IMCUs from scratch. No compression or formatting of the columnar data is required.

Examples

This example creates fs_tbs as an ASSM tablespace, and then uses FASTSTART_ENABLE to specify this tablespace as the IM FastStart area:

CREATE TABLESPACE fs_tbs 
  DATAFILE 'fs_tbs.dbf' SIZE 500M 
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE('fs_tbs');

The following query shows that the IM FastStart LOB was created (sample output included):

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;

OWNER SEGMENT_NAME         MB
----- -------------------- ----------
SYS   SYSDBIMFS_LOBSEG$    .125

Syntax

DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(
   snapshot    IN    VARCHAR2);

Parameters

Usage Notes

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.

The maximum number of SYS_IME columns for a table, regardless of whether the attribute is INMEMORY or NO INMEMORY, is 50. After the limit is reached for a table, the database will not add new SYS_IME columns. To make space for new expressions, you must manually drop SYS_IME columns with the IME_DROP_ALL_EXPRESSIONS Procedure or IME_DROP_EXPRESSIONS Procedure.

The 50-expression limit for each table, which includes both INMEMORY and NO INMEMORY expressions, is different from the 20-expression limit for the database, which includes only INMEMORY expressions. For example, if 20 tables are populated in the IM column store, then each table might each have 1 SYS_IME column with the INMEMORY attribute, and 49 SYS_IME columns with the NO INMEMORY attribute.

IM expressions and virtual columns are stored in In-Memory structured called In-Memory Expression Units (IMEUs). Every IMEU is linked to a parent In-Memory Compression Unit (IMCU) from which it inherits compression characteristics.

ESS information is stored in the data dictionary and exposed in the DBA_EXPRESSION_STATISTICS view. This view shows the metadata that the optimizer has collected in the ESS. IM expressions are exposed as system-generated virtual columns, prefixed by the string SYS_IME, in the DBA_IM_EXPRESSIONS view.

Syntax

DBMS_INMEMORY_ADMIN.IME_CLOSE_CAPTURE_WINDOW();

Usage Notes

On invocation of this procedure, the optimizer saves all gathered statistics to disk, and essentially freezes the expressions tracked in the window. The database preserves the statistics captured in this window until a new expression capture window is opened, at which point the database purges the statistics captured in the previous window.

Syntax

DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS();

Usage Notes

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.

Syntax

DBMS_INMEMORY_ADMIN.IME_GET_CAPTURE_STATE(
   p_capture_state  OUT  VARCHAR2,
   p_last_modified  OUT  TIMESTAMP);

Parameters

Usage Notes

This procedure is useful for avoiding conflicting calls for IME_OPEN_CAPTURE_WINDOW Procedure and IME_CLOSE_CAPTURE_WINDOW Procedure. For example, if the current expression capture window state is OPEN, then you cannot open another window, and if the window state is CLOSED, then you cannot close a window.

Syntax

DBMS_INMEMORY_ADMIN.IME_OPEN_CAPTURE_WINDOW();

Security Model

Administrator privileges are required to execute this procedure.

Usage Notes

On invocation of this procedure, the optimizer begins a new window snapshot and starts tracking expressions that occur within this window. An expression capture window is global across all instances in an Oracle RAC database.

Conflicting actions are not permitted. For example, in an Oracle RAC database, opening expression capture window on instance 1 at time t0 and attempting to open another expression capture window on instance 2 at time t1 before closing the first window is a conflicting action. To obtain the current capture state and reduce the potential for conflicting procedure invocations, use the IME_GET_CAPTURE_STATE Procedure.

Example

This following program opens an expression capture window:

EXEC DBMS_INMEMORY_ADMIN.IME_OPEN_CAPTURE_WINDOW();

Syntax

DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

Usage Notes

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. IME_POPULATE_EXPRESSIONS solves this problem by forcing population.

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.

Syntax

DBMS_INMEMORY_ADMIN.POPULATE_WAIT(
   priority    IN    VARCHAR2 DEFAULT 'LOW',
   percentage  IN    NUMBER   DEFAULT 100,
   timeout     IN    NUMBER   DEFAULT 99999999,
   force       IN    VARCHAR2 DEFAULT FALSE)
RETURN VARCHAR2;

Parameters

Return Values

The following table describes the possible return values for POPULATE_WAIT. The function returns the values 0, 1, 2, and 3 only if the condition is met before the end of the interval specified by timeout. For example, if timeout is 600, then the function returns 1 only if an out-of-memory error occurs before 600 seconds pass. The function returns -1 only if the end of the timeout interval occurs before the database completes the requested operation.

Usage Notes

Sample use cases for ensuring that objects are populated include:

• When the database is closed, open the database with STARTUP RESTRICT so that only administrators can access the database, and then execute POPULATE_WAIT with the desired timeout setting. If POPULATE_WAIT returns -1, indicating a timeout, then reexecute POPULATE_WAIT. When the function returns 0, disable the restricted session so that non-administrative users can query the database.

• Block database connections by using services or an application tier technique. When no analytic indexes exists, and when the application depends on the IM column store to provide reasonable performance, these techniques prevent runaway queries.