Configuration Guidelines
This section describes the general recommendations for configuring various database objects and includes a brief syntax overview. It covers the general aspects of the database objects and does not cover any specific implementation requirements.
Index
Index recommendations specify points that need to be considered when creating indexes on a table.
1. Indexes on a table should be created according to the functional requirements of the table and not in order to perform SQL tuning.
2. The foreign keys on a table should be indexes.
Note: If the implementation creates a CM index on table-columns where the product already provides an index, then the CM index will be overridden by the base index.
Table Partitioning Recommendations
Oracle Utilities recommends using a minimum of 'n' partitions for selective database objects, where 'n' is number of RAC nodes.
Interval Partitioning F1_MO_UPD and F1_MO_UPD_BACKLOG tables
Performance tests conducted on GDE batch showed that Interval partitioning of tables, F1_MO_UPD and F1_MO_UPD_BACKLOG improved performance. Hence, it is recommended by Product Development to convert the tables F1_MO_UPD and F1_MO_UPD_BACKLOG into partitioned tables using Oracle's Interval partition scheme.
To implement interval partitioning on F1_MO_UPD and F1_MO_UPD_BACKLOG tables follow the steps below:
Note that the steps mentioned below are for F1_MO_UPD table. However, the same can be applied for partitioning the F1_MO_UPD_BACKLOG table.
1. Login to the database either with a schema owner account or as SYS.
2. Make sure that the GDE export related batches are not running.
3. Check the record count for the F1_MO_UPD table.
4. Execute the F1_MO_UPD_INT_PART.sql SQL script to create an interval-partitioned table.
5. Check the definition of F1_MO_UPD to verify if it has the partition clause “PARTITION BY RANGE (“ENT_KEY_HASH”) INTERVAL (1)”. Execute the following:
select dbms_metadata.get_ddl('TABLE','F1_MO_UPD') from dual;
6. Drop the backup table <table_name>_OLD created by the script after verifying the data is correct.
Transparent Data Encryption Recommendations
Oracle Utilities supports Oracle Transparent Data Encryption (TDE). Oracle 19c supports tablespace level encryption. The application supports tablespace level encryption for all application data. Make sure that the hardware resources are sufficiently sized for this as TDE uses additional hardware resources. The Oracle Advanced Security license is a prerequisite for using TDE.
Please consider the following when implementing TDE:
• Create a wallet folder to store the master key. By default, the wallet folder should be created under $ORACLE_BASE/admin/<sid>.
• The wallet containing the master key can be created using the following command:
alter system set encryption key authenticated by "keypasswd"
• The wallet can be closed or opened using the following commands:
alter system set wallet open identified by "keypasswd";
alter system set wallet close;
• Column level encryption can be achieved using the following commands:
create table <table_name>
(name varchar2(200) default ' ' not null,
bo_data_area CLOB encrypt using 'AES128',
bo_status_cd char(12) encrypt using 'AES128')
lob (bo_data_area) store as securefile (cache compress)
tablespace <tablespace_name>;
• AES128 is the default encryption algorithm.
• Tablespace level encryption is also supported using the following command:
Create tablespace <tablespace_name> logging datafile '<datafile
location>' size <initial size> reuse autoextend on next <next size>
maxsize unlimited extent management local uniform size
<uniform size> encryption using 'AES128' default storage(encrypt);
• Indexed columns can only be encrypted using the NO SALT Option. Salt is a way to strengthen the security of encrypted data. It is a random string added to the data before it is encrypted, causing repetition of text in the clear to appear different when encrypted.
Data Compression Recommendations
Oracle Utilities supports Advanced Data Compression, available with Oracle 19c onwards, to reduce the database storage footprint. Make sure that your resources are sufficiently sized for this as it uses additional system resources. Compression can be enabled at the Tablespace level or at the Table level.
Exadata Hardware
For Exadata hardware the compression recommendations are:
• Load data into the uncompressed table partitions using a conventional load and then, once data is loaded using a CTAS operation, load into a temporary heap table. Then truncate the original partition. Alter the original partition into HCC compressed and then partition exchange this with the temporary heap table.
• All multi column Indexes (primary as well as secondary) will be compressed using the default compression. HCC or OLTP compression is not applicable on the top of compressed Indexes.
Non- Exadata Hardware
For non-Exadata hardware the recommendations are the same as above, except that you cannot use HCC compression (it is only available in Exadata database machine). Instead of HCC you can use any other compression tool available to you for non-Exadata hardware.
CLOB Fields
All CLOB fields should be stored as SecureFiles and Medium compressed. This requires a separate license for Advanced Data Compression. As a part of the schema, we create the product-owned tables with compression turned OFF at the LOB level. If you have the license for Advanced Data Compression, you can enable compression by updating the storage.xml.
From Oracle 12c onwards:
• Admin and Master Data tables and their indexes will NOT be compressed.
• All Transactional Tables, including ILM enabled MOs shall be compressed.
• Compression will be done at the tablespace level.
• All multicolumn indexes on transactional/ILM tables will be compressed.
• Use 'compress advanced low'.
Compression Guidelines
• Admin and Metadata tables and their indexes will NOT be compressed.
• All Transactional Tables will be compressed.
This includes ILM enabled MOs where applicable.
• Compression will be done at the tablespace level.
• Different MOs will have different tablespaces.
• Partitioned MOs will have one tablespace per partition.
• Child tables will use reference partitioning with parent + children sharing the same tablespace. (parent and child will always be managed/archived together).
• All multicolumn indexes on transactional tables will be compressed.
• Use ‘compress advanced low’.
• Local partitioned indexes will reside in the same tablespace as the table.
• Each MO will have an index tablespace. All MO (Parent-Child Table) indexes will share this tablespace.
• Do NOT specify standard index compression.
• Securefile medium compression in row for LOBs and CLOBs.
Examples:
Create a Tablespace with Advanced Rowstore Compress
CREATE BIGFILE TABLESPACE CM_XT012_P2017JANDATAFILE '+DATA' SIZE 50M AUTOEXTEND ON MAXSIZE UNLIMITED DEFAULT ROW STORE COMPRESS ADVANCED;
Create Table with Subpartitions using Compressed Tablespaces & Securefiles Compression
CREATE TABLE CI_ADJ (
ADJ_ID CHAR(12) NOT NULL ENABLE,
SA_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE, ADJ_TYPE_CD CHAR(8) DEFAULT ' ' NOT NULL ENABLE, ADJ_STATUS_FLG CHAR(2) DEFAULT ' ' NOT NULL ENABLE, CRE_DT DATE,
CAN_RSN_CD CHAR(4) DEFAULT ' ' NOT NULL ENABLE,
ADJ_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE, XFER_ADJ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE, CURRENCY_CD CHAR(3) DEFAULT ' ' NOT NULL ENABLE, COMMENTS VARCHAR2(254) DEFAULT ' ' NOT NULL ENABLE, VERSION NUMBER(5,0) DEFAULT 1 NOT NULL ENABLE, BEHALF_SA_ID CHAR(10) DEFAULT ' ' NOT NULL ENABLE, BASE_AMT NUMBER(15,2) DEFAULT 0 NOT NULL ENABLE, GEN_REF_DT DATE,
APPR_REQ_ID CHAR(12) DEFAULT ' ' NOT NULL ENABLE,
ADJ_DATA_AREA CLOB, ILM_DT DATE,
ILM_ARCH_SW CHAR(1),)
ENABLE ROW MOVEMENT
PARTITION BY RANGE (ILM_DT)
SUBPARTITION BY RANGE (ADJ_ID) SUBPARTITION TEMPLATE (
SUBPARTITION S01 VALUES LESS THAN ( '124999999999' ), SUBPARTITION S02 VALUES LESS THAN ( '249999999999' ), SUBPARTITION S03 VALUES LESS THAN ( '374999999999' ), SUBPARTITION S04 VALUES LESS THAN ( '499999999999' ), SUBPARTITION S05 VALUES LESS THAN ( '624999999999' ), SUBPARTITION S06 VALUES LESS THAN ( '749999999999' ), SUBPARTITION S07 VALUES LESS THAN ( '874999999999' ), SUBPARTITION S08 VALUES LESS THAN ( MAXVALUE )
) (
PARTITION "P2017JAN" VALUES LESS THAN (TO_DATE('2017-02-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017JAN,
PARTITION "P2017FEB" VALUES LESS THAN (TO_DATE('2017-03-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017FEB,
PARTITION "P2017MAR" VALUES LESS THAN (TO_DATE('2017-04-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017MAR,
PARTITION "P2017APR" VALUES LESS THAN (TO_DATE('2017-05-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017APR,
PARTITION "P2017MAY" VALUES LESS THAN (TO_DATE('2017-06-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017MAY,
PARTITION "P2017JUN" VALUES LESS THAN (TO_DATE('2017-07-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017JUN,
PARTITION "P2017JUL" VALUES LESS THAN (TO_DATE('2017-08-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017JUL,
PARTITION "P2017AUG" VALUES LESS THAN (TO_DATE('2017-09-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017AUG,
PARTITION "P2017SEP" VALUES LESS THAN (TO_DATE('2017-10-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017SEP,
PARTITION "P2017OCT" VALUES LESS THAN (TO_DATE('2017-11-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017OCT,
PARTITION "P2017NOV" VALUES LESS THAN (TO_DATE('2017-12-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017NOV,
PARTITION "P2017DEC" VALUES LESS THAN (TO_DATE('2017-01-01 00:00:01',
'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace CM_XT012_P2017DEC,
PARTITION "PMAX" VALUES LESS THAN (MAXVALUE)
tablespace CM_XT012_PMAX
);
Create a Compressed Local Index
CREATE UNIQUE INDEX CM_ILM_XT012L0 ON CI_ADJ ( ILM_DT, ILM_ARCH_SW, ADJ_ID )
TABLESPACE CM_XT012_IND LOCAL COMPRESS ADVANCED LOW;
Create a Compressed Global Partitioned Index
CREATE UNIQUE INDEX XT012S2 ON CI_ADJ ( XFER_ADJ_ID, ADJ_ID ) TABLESPACE CM_XT012_IND
GLOBAL PARTITION BY HASH (XFER_ADJ_ID, ADJ_ID ) (
PARTITION PART1 TABLESPACE CM_XT012_IND, PARTITION PART2 TABLESPACE CM_XT012_IND, PARTITION PART3 TABLESPACE CM_XT012_IND, PARTITION PART4 TABLESPACE CM_XT012_IND, PARTITION PART5 TABLESPACE CM_XT012_IND, PARTITION PART6 TABLESPACE CM_XT012_IND, PARTITION PART7 TABLESPACE CM_XT012_IND, PARTITION PART8 TABLESPACE CM_XT012_IND
)
COMPRESS ADVANCED LOW;
Do NOT specify standard index compression.
CREATE INDEX XT012S1 ON CI_ADJ ( SA_ID, ADJ_TYPE_CD ) TABLESPACE CM_XT012_IND COMPRESS ADVANCED LOW;
CREATE INDEX XT012S4 ON CI_ADJ(APPR_REQ_ID,ADJ_ID) TABLESPACE
CM_XT012_IND COMPRESS ADVANCED LOW;
Database Vault Recommendations
The product supports Database Vault. All non-application User IDs can be prevented from using DDL or DML statements against the application schema. So SYS and SYSTEM cannot issue DDL or DML statements against CISADM schema.
The application-specific administration account can issue DDL statements but should not be able to perform any DML or DCL statements.
Application user must be given DML only permissions.
Database Vault can be used to control access during patch process and Install/Upgrade process.
Oracle Fuzzy Search Support
The product supports Oracle Fuzzy searches. To use this feature, Oracle Text must be installed. After Oracle Text is installed, an index must be created on the table where the fuzzy search needs to be performed from the application. This is only an Oracle database option and is not supported by other databases. Additionally, not all languages are supported. Refer to the Oracle database documentation for more information about fuzzy searching.
A typical syntax for implementation of fuzzy searching is as below. For the most updated syntax, please refer to Oracle Fuzzy documentation.
GRANT CTXAPP TO <Application schema owner e.g CISADM>;
GRANT EXECUTE ON CTX_DDL TO <Application schema owner e.g CISADM>;
create index <Application schema owner e.g CISADM>.<Index_Name> on Application schema owner e.g CISADM>.<Table_Name> (<column_name>) indextype is ctxsys.context parameters ('sync (on commit)');
begin
ctx_ddl.sync_index(‘Application schema owner e.g CISADM>.<Index_Name>’);
end
/
Storage Recommendations
This section specifies recommended options for storing the database objects.
SecureFile for Storing LOBs
Beginning with Oracle 11g, tables having fields with data type of CLOB or BLOBS should have the LOB Columns stored as SecureFiles.
• The storage options with SecureFiles for Heap Tables should be ENABLE STORAGE IN ROW, CACHE and COMPRESS.
• For the IOT Table the PCTTHRESHOLD 50 OVERFLOW clause should be specified and the storage options with SecureFiles should be ENABLE STORAGE IN ROW, CACHE and COMPRESS.
• The PCTTHRESHOLD should be specified as a percentage of the block size. This value defines the maximum size of the portion of the row that is stored in the Index block when an overflow segment is used.
• The CHUNK option for storage, which is the data size used when accessing or modifying LOB values, can be set to higher than one database block size if big LOBs are used in the IO Operation.
• For SecureFiles, make sure that the initialization parameter db_securefile is set to ALWAYS.
• The Tablespace where you are creating the SecureFiles should be enabled with Automatic Segment Space Management (ASSM). In Oracle Database 19c, the default mode of Tablespace creation is ASSM so it may already be set for the Tablespace. If it's not, then you have to create the SecureFiles on a new ASSM Tablespace.
Note: To enable compression on SecureFiles, you must have an Oracle Advanced Compression license in addition to Oracle Database Enterprise Edition. This feature is not available for the standard edition of the Oracle database.
If you are using Oracle Database Enterprise Edition, please verify that the “COMPRESS” flag is turned on by setting it to “Y” in Storage.xml.
Refer to the
Database Syntax section for more information on SecureFiles.
ILM Enabled Tablespace Requirements
• One tablespace for each partition of Parent table (Child table is referenced partitioned and would inherit the tablespace from Parent partition).
• One tablespace for each MO's global indexes (including child tables indexes) and _K table.
Database Configuration Recommendations
This section specifies the recommended methods for configuring the database with a focus on specific functional area.
Large Redo Log File Sizes
The Redo Log files are written by the Log Writer Background process. These log files are written in a serial manner. Once a log File is full, a log switch occurs and the next log file starts getting populated.
It is recommended that the size of the Redo log files should be sufficiently high so that you do not see frequent Log Switches in the alert logs of the database. Frequent Log Switches impact the IO performance and can be avoided by having a larger Redo log file size.
Frequent Log Switches impacts the IO performance and can be avoided by having a bigger Redo log File Size.
Database Syntax
SecureFile
CREATE TABLE <Table_Name>
( COLUMN1 ….,
COLUMN2 (CLOB)
)
LOB(COLUMN2) STORE AS SECUREFILE (CACHE COMPRESS);
CREATE TABLE <Table_Name>
( COLUMN1 ….,
COLUMN2 (CLOB)
CONTRAINT <> PRIMARY KEY(…)
)
ORGANIZATION INDEX PCTTHRESHOLD 50 OVERFLOW
LOB(COLUMN2) STORE AS SECUREFILE (ENABLE STORAGE IN ROW CHUNK CACHE COMPRESS);
Database Initialization Parameters
The recommended initialization parameters are given below. These parameters are a starting point for database tuning. An optimal value for a production environment may differ from one customer deployment to another.
db_block_size=8192
log_checkpoint_interval=0
db_file_multiblock_read_count=8
transactions=3000
open_cursors=3000
db_writer_processes=10
db_files=1024
dbwr_io_slaves=10 (Only if Asynchronous IO is not Supported)
sessions=4500
memory_target=0
memory_max_target=0
processes=3000
dml_locks=48600
_b_tree_bitmap_plans=FALSE