Feature Summary

The memoptimized rowstore provides the capability of fast lookup of data for the tables that are mainly queried based on primary key columns.
The memoptimized rowstore uses a memory area in the system global area (SGA) called the memoptimize pool that stores the hash indexes of the tables when enabled for fast lookup.

Fast lookup is enabled by a hash index structure in the memoptimize pool that provides fast access to the blocks of a given table permanently pinned in the buffer cache to avoid disk I/O.
This hash index is created when the Memoptimized Rowstore is configured and is maintained automatically by Oracle Database.
When a table is enabled for fast lookup, the table’s blocks are pinned in the buffer cache, and queries on the table use the hash index in the memoptimize pool to improve performance.

To enable the memoptimize pool:

Set the MEMOPTIMIZE_POOL_SIZE initialization parameter to a non-zero value. The minimum setting is 100M.

To enable a Table for Fast Lookup

Specify MEMOPTIMIZE FOR READ in CREATE TABLE or ALTER TABLE statements.
The memoptimize pool is should first be enabled.

To populate a Table in the Memoptimize Pool

To disable an Existing Table for Fast Lookup

NOTE

This feature is allowed on Engineered systems and Oracle Cloud, and disabled for non-Engineered systems and non-Oracle Cloud.
The error ORA-12754 error is expected for  non-Engineered systems and non-Oracle Cloud.

SQL> alter table test memoptimize for read;
*
ERROR at line 1:
ORA-12754: Feature ‘Memoptimized Rowstore’ is disabled due to missing capability ‘Runtime Environment’.

Workaround : Set the init.ora parameter _exadata_feature_on=true, restart the database with memoptimize_pool_size > 0.

Starting 18.1 database, “Scalable Sequences” have been introduced.

The ability to create Scalable Sequences has been added to improve the performance of data loading into tables having sequence values as keys. This feature provides the option to add instance and session offsets to significantly reduce the possibility of sequence and index block contention when loading data across RAC instances and multiple loading processes within single instances.

The new business benefit is that it further enhances the Oracle Database data loading capabilities by reducing contention when loading data into tables that use sequence values as keys. By adding the ability to create sequences with instance and session ids added to the sequence value, contention on sequence value generation and index blocks inserts for the key values is significantly reduced. This means that Oracle Database is even more scalable for data loading and can support even higher data loading rates.

SQL Statement:

The addition of 100 in generating iii ensures that all generated values are of the same length, and consequently, there will be no duplicates in the generated values across all instances.

The default length of the scalable sequence offset is 6.

Manage runaway queries in the database , for versions 18c and above.

From 11g onwards, you can use the Resource Manager plan directives :

SWITCH_GROUP     => ‘CANCEL_SQL’

OR

SWITCH_GROUP     => ‘KILL_SESSION’

to cancel the SQL or kill the session respectively. You can also change the consumer group for the runaway queries. For details, refer to the document:

Managing and Monitoring Runaway Query Using Resource Manager Note 1600965.1

Along with these, you could kill the session using the ALTER SYSTEM KILL SESSION command.

From 18c onwards, you can manually cancel any runaway query using “ALTER SYSTEM CANCEL SQL” statement, instead of killing the session. Please note this is in addition to the options available through the Resource Manager.

The following clauses are required in an ALTER SYSTEM CANCEL SQL statement:

SID – Session identifier

SERIAL – Session serial number

SQL_ID – SQL ID of the SQL statement (optional)

INST_ID – Instance ID (optional)

If the SQL_ID is not provided, the currently running statement will be cancelled.

Cancelling a DML will result in implicit rollback of that statement.

For Example:

While this is executing, Cancel the SQL from another session

Check the INSERT statement in the first session

Here, we can see, only the second Insert statement (which was cancelled) got rolled back

Typically, when rolling forward a physical standby database using primary incremental backup, multiple steps are required:

1. Identify the Start SCN on Standby for performing incremental backup on primary
2. Perform incremental backup on primary with FROM SCN clause
3. Move the backup-pieces from primary to standby
4. Catalog the backup-piece on Standby
5. Perform recovery on standby using recover database noredo
6. Refresh standby controlfile again from primary

Starting from 12.1, we could use “RECOVER DATABASE FROM SERVICE”command which will automate a few steps like performing incremental backup on primary, transfer the backup-pieces to standby over network and perform recovery on standby. However, we still had to manually refresh the standby controlfile and manually restore newly-added datafiles. These steps required manual efforts and are error prone especially when standby files are physically located in a path different to that of primary.

Starting with 18.1, we can use a single command to refresh the standby with changes made on primary:

RMAN> RECOVER STANDBY DATABASE FROM SERVICE primary_connect_identifier;

This command will internally keep track of standby file locations, refresh standby controlfile from primary, update the new standby controlfile with standby file names, perform incremental backup on primary, transfer the backup-pieces over network to standby and perform recovery on standby

1. To refresh the standby, ensure that managed recovery is stopped on standby:

RMAN> RECOVER STANDBY DATABASE FROM SERVICE PRM180;
RMAN-03090: Starting recover at 03-AUG-18
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/03/2018 12:33:05
RMAN-05150: Managed Recovery Process must be disabled before running RECOVER STANDBY DATABASE.

2. Ensure that Oracle Net connectivity is established between the physical standby database and the primary database by adding an entry corresponding to the primary database in the tnsnames.ora file of the physical standby database. In below example, PRM180 is the connect identifier for primary.

3. Connect RMAN to standby as target and run “RECOVER STANDBY DATABASE FROM SERVICE” command. Find below an example run for the command: