Donghua's Blog - DBAGlobe

Tuesday, July 7, 2020

Readonly Oracle Home in 19c

Starting with Oracle Database 18c, you can configure an Oracle home in read-only mode, with following benefits:

Enables seamless patching and updating of Oracle databases without extended downtime.
Simplifies patching and mass rollout as only one image needs to be updated to distribute a patch to many servers.
Simplifies provisioning by implementing separation of installation and configuration.

Environment Variables: (~/.bash_profile)

ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/db/dbhome_1
PATH=$PATH:/u01/db/dbhome_1/bin
LD_LIBRARY_PATH=/u01/db/lib
ORACLE_SID=PRORCL
# Read-only Oracle Home
# ORACLE_BASE_CONFIG/dbs contains the configuration files for ORACLE_HOME
ORACLE_BASE_CONFIG=/u01/app/oracle
# user-specific files, instance-specific files, and log files reside
# in ORACLE_BASE_HOME, e.g. networking directores
ORACLE_BASE_HOME=/u01/app/oracle/homes/OraDB19Home1

export ORACLE_BASE ORACLE_HOME PATH LD_LIBRARY_PATH ORACLE_SID
export ORACLE_BASE_CONFIG ORACLE_BASE_HOME

Oracle Home Software Installation

[oracle@db01 stage]$ mkdir -p /u01/db/dbhome_1
[oracle@db01 stage]$ unzip /u01/stage/DB.19.7.GoldImage/db_home_2020-05-16_04-52-09PM.zip -d /u01/db/dbhome_1
[oracle@db01 stage]$ /u01/db/dbhome_1/runInstaller -silent -responseFile /u01/stage/db_install.rsp

Enable Readonly Home

[oracle@db01 stage]$ $ORACLE_HOME/bin/roohctl -enable
Enabling Read-Only Oracle home.
Update orabasetab file to enable Read-Only Oracle home.
Orabasetab file has been updated successfully.
Create bootstrap directories for Read-Only Oracle home.
Bootstrap directories have been created successfully.
Bootstrap files have been processed successfully.
Read-Only Oracle home has been enabled successfully.

Check the log file /u01/app/oracle/cfgtoollogs/roohctl/roohctl-200707AM032033.log for more details.

Create Listener & DB

# Create listener
/u01/db/dbhome_1/bin/netca /orahome /u01/db/dbhome_1 /instype typical /inscomp client,oraclenet,javavm,server,ano /insprtcl tcp /cfg local /authadp NO_VALUE /responseFile /u01/db/dbhome_1/network/install/netca_typ.rsp /lisport 1521 /silent /orahnam OraDB19Home1

# Create Database
/u01/db/dbhome_1/bin/dbca -silent -createDatabase  -emConfiguration NONE  -templateName 'General_Purpose.dbc' -storageType FS -datafileDestination '/u01/app/oracle/oradata' -datafileJarLocation '/u01/db/dbhome_1/assistants/dbca/templates' -sampleSchema true -oratabLocation /etc/oratab  -runCVUChecks false -continueOnNonFatalErrors true -createAsContainerDatabase true -numberOfPDBs 1 -pdbName appdb1 -gdbName 'ORCL' -sid 'PRORCL' -initParams filesystemio_options=setall -ignorePrereqs

Check dbs & network Files Location
[oracle@db01 admin]$ ls -l /u01/app/oracle/dbs/
total 20
-rw-rw---- 1 oracle oinstall 1544 Jul  7 04:02 hc_PRORCL.dat
-rw-r----- 1 oracle oinstall   46 Jul  7 04:02 initPRORCL.ora
-rw-r----- 1 oracle oinstall   24 Jul  7 03:41 lkORCL
-rw-r----- 1 oracle oinstall 2048 Jul  7 03:46 orapwPRORCL
-rw-r----- 1 oracle oinstall 3584 Jul  7 04:02 spfilePRORCL.ora

[oracle@db01 admin]$ ls -l /u01/app/oracle/homes/OraDB19Home1/network/admin/
total 12
-rw-r----- 1 oracle oinstall 362 Jul  7 03:33 listener.ora
-rw-r----- 1 oracle oinstall 190 Jul  7 03:33 sqlnet.ora
-rw-r----- 1 oracle oinstall 875 Jul  7 04:18 tnsnames.ora

Output of "/u01/app/oracle/cfgtoollogs/roohctl/roohctl-200707AM032033.log"
[main] [ 2020-07-07 03:20:33.560 GMT ] [RoohCtl.execute:461] Oracle Home value read from System Properties: /u01/db/dbhome_1
[main] [ 2020-07-07 03:20:33.561 GMT ] [RoohCtl.execute:479] Operation enable
[main] [ 2020-07-07 03:20:33.561 GMT ] [RoohCtl.execute:484] nodeList value read from CLI: null
[main] [ 2020-07-07 03:20:33.704 GMT ] [InventoryUtil.getOUIInvSession:349] setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS
[main] [ 2020-07-07 03:20:33.710 GMT ] [HAUtils.<init>:339] oui location /u01/app/oraInventory/ContentsXML
[main] [ 2020-07-07 03:20:33.716 GMT ] [InventoryUtil.getOUIInvSession:349] setting OUI READ level to ACCESSLEVEL_READ_LOCKLESS
[main] [ 2020-07-07 03:20:33.716 GMT ] [OracleHome.isClientHome:1816] Homeinfo /u01/db/dbhome_1,1
[Finalizer] [ 2020-07-07 03:20:33.766 GMT ] [Util.finalize:136] Util: finalized called for oracle.ops.mgmt.has.Util@d7e7713
[main] [ 2020-07-07 03:20:33.993 GMT ] [HAUtils.<init>:372] isClientHome: false
[main] [ 2020-07-07 03:20:33.994 GMT ] [Version.isPre:757] version to be checked 19.0.0.0.0 major version to check against 10
[main] [ 2020-07-07 03:20:33.994 GMT ] [Version.isPre:768] isPre.java: Returning FALSE
[main] [ 2020-07-07 03:20:33.994 GMT ] [Version.isPre:757] version to be checked 19.0.0.0.0 major version to check against 10
[main] [ 2020-07-07 03:20:33.994 GMT ] [Version.isPre:768] isPre.java: Returning FALSE
[main] [ 2020-07-07 03:20:33.994 GMT ] [Version.isPre:757] version to be checked 19.0.0.0.0 major version to check against 11
[main] [ 2020-07-07 03:20:33.995 GMT ] [Version.isPre:768] isPre.java: Returning FALSE
[main] [ 2020-07-07 03:20:33.995 GMT ] [Version.isPre:789] version to be checked 19.0.0.0.0 major version to check against 11 minor version to check against 2
[main] [ 2020-07-07 03:20:33.995 GMT ] [Version.isPre:798] isPre: Returning FALSE for major version check
[main] [ 2020-07-07 03:20:33.995 GMT ] [UnixSystem.isHAConfigured:3609] olrFileName = /etc/oracle/olr.loc
[main] [ 2020-07-07 03:20:33.995 GMT ] [RoohCtl.checkOracleHomeForConfiguration:592] Oracle restart configured: false
[main] [ 2020-07-07 03:20:33.995 GMT ] [RoohCtl.checkOracleHomeForConfiguration:601] Oracle Grid Infrastructure configured: false
[main] [ 2020-07-07 03:20:33.996 GMT ] [RoohCtl.checkOracleHomeForConfiguration:653] Enumerating oratab file
[main] [ 2020-07-07 03:20:34.011 GMT ] [RoohCtl.checkOracleHomeForConfiguration:682] Created oracle.net.config.Config for Oracle Home: /u01/db/dbhome_1
[main] [ 2020-07-07 03:20:34.014 GMT ] [RoohCtl.processOperation:770] Orabasetab Location: /u01/db/dbhome_1/install/orabasetab
[main] [ 2020-07-07 03:20:34.023 GMT ] [RoohCtl.createModifiedOrabasetab:163] Oracle Home read from orabasetab: /u01/db/dbhome_1
[main] [ 2020-07-07 03:20:34.023 GMT ] [RoohCtl.createModifiedOrabasetab:164] Oracle Base read from orabasetab: /u01/app/oracle
[main] [ 2020-07-07 03:20:34.024 GMT ] [RoohCtl.createModifiedOrabasetab:165] Oracle Home Name read from orabasetab: OraDB19Home1
[main] [ 2020-07-07 03:20:34.024 GMT ] [RoohCtl.processOrabasetab:255] Copying file /u01/db/dbhome_1/install/orabasetab.temp to /u01/db/dbhome_1/install/orabasetab
[main] [ 2020-07-07 03:20:34.025 GMT ] [RoohCtl.processOrabasetab:263] Deleting temp file: /u01/db/dbhome_1/install/orabasetab.temp
[main] [ 2020-07-07 03:20:34.027 GMT ] [OsUtilsBase.deleteFromEnvironment:2304] Removed from env ORACLE_BASE=/u01/app/oracle
[main] [ 2020-07-07 03:20:34.033 GMT ] [InstallUtils.getOracleBase:489] OracleBase from orabase /u01/app/oracle
[main] [ 2020-07-07 03:20:34.034 GMT ] [OsUtilsBase.deleteFromEnvironment:2304] Removed from env ORACLE_BASE=/u01/app/oracle
[main] [ 2020-07-07 03:20:34.043 GMT ] [InstallUtils.getOraBaseConfigLocation:592] orabaseconfig location from orabaseconfig util /u01/app/oracle
[main] [ 2020-07-07 03:20:34.043 GMT ] [OsUtilsBase.deleteFromEnvironment:2304] Removed from env ORACLE_BASE=/u01/app/oracle
[main] [ 2020-07-07 03:20:34.052 GMT ] [InstallUtils.getOraBaseHomeLocation:551] orabasehome from orabasehome /u01/app/oracle/homes/OraDB19Home1
[main] [ 2020-07-07 03:20:34.052 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORACLEBASE%/
[main] [ 2020-07-07 03:20:34.052 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORACLEBASE%/homes
[main] [ 2020-07-07 03:20:34.053 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASECONFIG%/
[main] [ 2020-07-07 03:20:34.053 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASECONFIG%/%DBS%
[main] [ 2020-07-07 03:20:34.053 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/
[main] [ 2020-07-07 03:20:34.053 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/rdbms
[main] [ 2020-07-07 03:20:34.053 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/rdbms/log
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/rdbms/audit
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/%DBS%
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/network
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/network/admin
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/network/trace
[main] [ 2020-07-07 03:20:34.054 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/network/log
[main] [ 2020-07-07 03:20:34.055 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/assistants
[main] [ 2020-07-07 03:20:34.055 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/assistants/dbca
[main] [ 2020-07-07 03:20:34.055 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/assistants/dbca/templates
[main] [ 2020-07-07 03:20:34.055 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file %ORABASEHOME%/install
[main] [ 2020-07-07 03:20:34.055 GMT ] [RoohCtl.processBootstrapFile:287] Line from the file FILEPROCESS|%ORACLEHOME%/network/admin/sqlnet.ora|%ORABASEHOME%/network/admin/sqlnet.ora|WIN
[main] [ 2020-07-07 03:20:34.057 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/
[main] [ 2020-07-07 03:20:34.057 GMT ] [RoohCtl.createBootstrapDirs:406] Directory /u01/app/oracle/ exists
[main] [ 2020-07-07 03:20:34.057 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:406] Directory /u01/app/oracle/ exists
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/dbs
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/dbs
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/
[main] [ 2020-07-07 03:20:34.058 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/
[main] [ 2020-07-07 03:20:34.059 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/rdbms
[main] [ 2020-07-07 03:20:34.059 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/rdbms
[main] [ 2020-07-07 03:20:34.059 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/rdbms/log
[main] [ 2020-07-07 03:20:34.059 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/rdbms/log
[main] [ 2020-07-07 03:20:34.060 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/rdbms/audit
[main] [ 2020-07-07 03:20:34.060 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/rdbms/audit
[main] [ 2020-07-07 03:20:34.060 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/dbs
[main] [ 2020-07-07 03:20:34.060 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/dbs
[main] [ 2020-07-07 03:20:34.060 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/network
[main] [ 2020-07-07 03:20:34.061 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/network
[main] [ 2020-07-07 03:20:34.061 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/network/admin
[main] [ 2020-07-07 03:20:34.062 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/network/admin
[main] [ 2020-07-07 03:20:34.062 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/network/trace
[main] [ 2020-07-07 03:20:34.062 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/network/trace
[main] [ 2020-07-07 03:20:34.063 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/network/log
[main] [ 2020-07-07 03:20:34.063 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/network/log
[main] [ 2020-07-07 03:20:34.063 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/assistants
[main] [ 2020-07-07 03:20:34.068 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/assistants
[main] [ 2020-07-07 03:20:34.068 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/assistants/dbca
[main] [ 2020-07-07 03:20:34.068 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/assistants/dbca
[main] [ 2020-07-07 03:20:34.068 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/assistants/dbca/templates
[main] [ 2020-07-07 03:20:34.069 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/assistants/dbca/templates
[main] [ 2020-07-07 03:20:34.069 GMT ] [RoohCtl.createBootstrapDirs:404] Creating directory /u01/app/oracle/homes/OraDB19Home1/install
[main] [ 2020-07-07 03:20:34.069 GMT ] [RoohCtl.createBootstrapDirs:415] Created directory /u01/app/oracle/homes/OraDB19Home1/install
[main] [ 2020-07-07 03:20:34.069 GMT ] [RoohCtl.processFilesDirectives:961] Processing entry /u01/db/dbhome_1/network/admin/sqlnet.ora|/u01/app/oracle/homes/OraDB19Home1/network/admin/sqlnet.ora|WIN
[main] [ 2020-07-07 03:20:34.070 GMT ] [RoohCtl.processFilesDirectives:982] OS specific construct WIN
[main] [ 2020-07-07 03:20:34.070 GMT ] [RoohCtl.processFilesDirectives:988] Process entry false
Reference URL: https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/about-read-only-oracle-home.html#GUID-D848002A-DBAD-48FA-8467-E849630B8E42

Wednesday, July 1, 2020

"Move online including rows" vs traditional "delete + move";

Setup:

create table t1 as
select rownum as id, rpad('a',20,'a') as value from 
(select level from dual connect by level <=10000),
(select level from dual connect by level <=10000);


create table t2 as
select rownum as id, rpad('a',20,'a') as value from 
(select level from dual connect by level <=10000),
(select level from dual connect by level <=10000);

Delete+Move Performance: (Total: 7 minutes 8 seconds)

SQL> delete from t1 where mod(id,10)<>0;
90000000 rows deleted.
Elapsed: 00:05:38.24


SQL> commit;
Commit complete.
Elapsed: 00:00:00.09


SQL> alter table t1 move online;
Table altered.
Elapsed: 00:01:29.39


SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T1';
     BYTES
----------
 369098752


SQL> select * from t1 fetch first 5 rows only;
        ID VALUE
---------- --------------------
        10 aaaaaaaaaaaaaaaaaaaa
        20 aaaaaaaaaaaaaaaaaaaa
        30 aaaaaaaaaaaaaaaaaaaa
        40 aaaaaaaaaaaaaaaaaaaa
        50 aaaaaaaaaaaaaaaaaaaa

Move online + Filter clause: (Total: 1 minutes 45 seconds)

SQL> alter table t2 move online including rows where mod(id,10)=0;
Table altered.
Elapsed: 00:01:45.85


SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T2';
     BYTES
----------
 369098752


SQL> select * from t2 fetch first 5 rows only;
        ID VALUE
---------- --------------------
        10 aaaaaaaaaaaaaaaaaaaa
        20 aaaaaaaaaaaaaaaaaaaa
        30 aaaaaaaaaaaaaaaaaaaa
        40 aaaaaaaaaaaaaaaaaaaa
        50 aaaaaaaaaaaaaaaaaaaa

ORA-30009: Not enough memory for CONNECT BY operation

When using connect by to generate large amount testing dataset, it can easier hit error: "ORA-30009: Not enough memory for CONNECT BY operation".

SQL> create table t1 as
  2  select level as id,rpad('a',10,'a') as value
  3  from dual
  4  connect by level <=1000000;
Table created.

SQL> create table t1 as
  2  select level as id, rpad('a',20,'a')  as value
  3  from dual
  4  connect by level <=10000000;
from dual
     *
ERROR at line 3:
ORA-30009: Not enough memory for CONNECT BY operation

Below is the workaround to bypass the issue:

SQL> create table t1 as
  2  select rownum as id, rpad('a',20,'a') as value from
  3  (select level from dual connect by level <=10000),
  4  (select level from dual connect by level <=10000);
Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
 100000000


SQL> select bytes from dba_segments where owner='DONGHUA' and segment_name='T1';
     BYTES
----------
3690987520

Saturday, June 27, 2020

SQL Developer 19.4 onwards new features - CODESCAN

SQL> help codescan
SET CODESCAN
---------

set CODESCAN ALL | NONE
|SQLINJECTION [ON | OFF]
Controls warning messages issued for code quality issues.
ALL or SQLINJECTION ON turns on warnings for possible SQL injection vulnerabilities.
NONE or SQLINJECTION OFF disables warnings.
Default is ALL.

SQL> create or replace procedure p(name in varchar2)
2 as
3 begin
4 execute immediate 'select * from t1 where id1='''||name||'''';
5 end;
6* /

SQLcl security warning: SQL injection NAME line 1 -> NAME line 4

Procedure P compiled

19c New Features: Same table enabled for both memoptimized read and write

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
ENABLED ENABLED

SQL> alter table test_fast_ingest no memoptimize for read;

Table altered.

SQL> alter table test_fast_ingest no memoptimize for write;

Table altered.

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
DISABLED DISABLED

SQL> alter table test_fast_ingest memoptimize for read;

Table altered.

SQL> alter table test_fast_ingest memoptimize for write;

Table altered.

SQL> select memoptimize_read,memoptimize_write from dba_tables where table_name='TEST_FAST_INGEST';

MEMOPTIM MEMOPTIM
-------- --------
ENABLED ENABLED

18c New Features - Memoptimized rowstore fast lookup

Fast lookup enables fast data retrieval from database tables for applications, such as Internet of Things (IoT) applications.
Fast lookup uses a hash index that is stored in the SGA buffer area called memoptimize pool to provide fast access to blocks of tables permanently pinned in the buffer cache, thus avoiding disk I/O and improving query performance.

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-E46EF11C-E999-4277-950F-E78EEC895ABB

Execution Plan with memoptimized read fast lookup

SQL> show parameter memoptimize_pool_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
memoptimize_pool_size                big integer 400M


SQL> alter table test_fast_ingest memoptimize for read;

Table altered.

SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID READ OPTIM| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN READ OPTIM         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Execution Plan without memoptimize

SQL> alter table test_fast_ingest no memoptimize for read;

Table altered.

Elapsed: 00:00:00.01
SQL> /* take 2nd execution output, avoid overhead with SQL parsing */
SQL> select * from test_fast_ingest where id=1;

        ID TEST_COL
---------- ---------------
         1 test

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1177632651

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    22 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_FAST_INGEST |     1 |    22 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C008161      |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=1)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        487  bytes sent via SQL*Net to client
        392  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processe

19c New Features Memoptimized Rowstore Fast Ingest

Summary:

Regular insert (per row commit): Elapsed: 14:47.37

Regular insert (batch commit): Elapsed: 04:34.80

Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

Test Script

create table test_normal_ingest (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  commit;
end loop;
end;
/

create table test_normal_ingest_batch (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
end loop;
commit;
end;
/


create table test_fast_ingest (
id number primary key,
test_col varchar2(15))
segment creation immediate
memoptimize for write;

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  commit;
end loop;
end;
/

Test Output:

Regular insert (per row commit): Elapsed: 14:47.37

SQL> create table test_normal_ingest (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:14:47.37

Regular insert (batch commit): Elapsed: 04:34.80

SQL> create table test_normal_ingest_batch (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
  7  end loop;
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.
Elapsed: 00:04:34.80

Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

SQL> create table test_fast_ingest (
  2  id number primary key,
  3  test_col varchar2(15))
  4  segment creation immediate
  5  memoptimize for write;

Table created.

Elapsed: 00:00:00.28
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:27.56

DB Setting:

SQL> show parameter  large_pool_size                      big integer 1G

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 1G


SQL> select total_size,used_space,free_space from v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE
---------- ---------- ----------
2154823680    1212896 2153610784

Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-CFADC9EA-2E2F-4EBB-BA2C-3663291DCC25