Oracle database

Tune the Oracle database server, and then add the platform, reserve, lease, and hierarchy indexes.

Tune the database server

Specify the following initialization parameters:

CURSOR_SHARING
Set CURSOR_SHARING to the default value EXACT to optimize performance. EXACT ensures that TRIRIGA® uses bind variables extensively.
NLS_LENGTH_SEMANTICS
If you require multibyte support, set NLS_LENGTH_SEMANTICS to CHAR and set the database character set to AL32UTF8 or AL16UTF16.
WORKAREA_SIZE_POLICY
Use the Program Global Area (PGA) setting WORKAREA_SIZE_POLICY=AUTO to automatically size work areas.
OPTIMIZER_FEATURES_ENABLE
Set OPTIMIZER_FEATURES_ENABLE to your current Oracle version.
PARALLEL_DEGREE_POLICY
Specifies whether automatic degree of parallelism, statement queuing, and in-memory parallel execution are enabled. Change the default of MANUAL to ADAPTIVE.
PROCESSES=
Increase the process number to handle more concurrent users by setting the PROCESSES= initialization parameter to the maximum number of users who can access Oracle concurrently. A guideline is to add up all maximum Java™ Database Connectivity (JDBC) connection pool values from each Java virtual machine (JVM) and add another 100 for Oracle. For example, adding 200 for an application server, plus 200 for a process server, plus 100 for Oracle requires a PROCESSES= value of at least 500.
Automatic memory management
In Oracle 11g and 12c, automatic memory management automatically readjusts the following main pool sizes based on existing workloads:
  • db_cache_size
  • shared_pool_size
  • large_pool_size
  • java_pool_size
The SGA_TARGET and SGA_MAX_SIZE initialization parameters enable automatic memory management.

The memory_target parameter is dynamic. You can change memory_target by using the alter system command, which deallocates RAM from an instance’s System Global Area (SGA) or Program Global Area (PGA) and reallocates that RAM to another instance. The memory_target and MEMORY_MAX_TARGET initialization parameters enable this feature. Consult with your database administrator to size these parameters

For more information about automatic memory management, see Enabling Automatic Memory Management.

For more information about tuning the Oracle database, see the Oracle Database Documentation Library.

Oracle RAC considerations

Many smaller nodes in a Real Application Clusters (RAC) configuration might yield better performance and might be suitable for high availability. However, small system-based RAC impairs performance. To scale Oracle for performance, use vertical scalability by adding CPU, RAM, and disk to the individual nodes.

RAC requires more resources because it maintains the cluster state across all the nodes. RAC also serves the data to the applications. Because resources are used by the work to maintain the cluster, each node is less powerful than if it were in a single instance. RAC is highly available but at a cost of maintainability and individual system resources. Scale the hardware solution vertically to handle the load of the requests from the application tier. Adding nodes horizontally slows down the environment.

Platform indexes

Indexes provide significant performance improvements when measured against a broad performance test workload. However, database platform improvements might vary depending on factors such as application usage, load patterns, hardware sizing, application, and database server configuration. Database administrators must monitor databases for efficient index usage to determine the impact that is produced by applying the indexes. Administrators must also identify indexes that improve performance based on situational and data composition needs.

The indexes that are listed are not included in the TRIRIGA base product unless otherwise stated.

Application platform indexes

Add the following application platform tuning indexes to Oracle databases:

CREATE INDEX "PERF01_BUDGET_CODES" ON "BUDGET_CODES" ("STATUS","CODE_REF_ID","TRANSACTION_ID") TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_BUDGET_CURRENCIES" ON "BUDGET_CURRENCIES" ("TRANSACTION_ID","CURRENCY_CODE","AMOUNT") TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_BUDGET_TRANSACTION" ON "BUDGET_TRANSACTION" ("TRANSACTION_TYPE","REVERSE_FLAG","SYSTEM_DATE") TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_ORGANIZATION" ON "T_ORGANIZATION" (SYS_GUIID,SYS_OBJECTID) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF02_ORGANIZATION" ON "T_ORGANIZATION" (UPPER(TRINAMETX)) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRIBUILDING" ON "T_TRIBUILDING" (SYS_GUIID,SYS_OBJECTID) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRIPEOPLE" ON "T_TRIPEOPLE" ("TRIRECORDNAMESY") TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF02_TRIPEOPLE" ON "T_TRIPEOPLE" (TRIIDTX) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRIPROPERTY" ON "T_TRIPROPERTY" (SYS_GUIID,SYS_OBJECTID) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRISPACE" ON "T_TRISPACE" (SYS_OBJECTID,SYS_GUIID,UPPER(triNameTX),UPPER(triIdTX)) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRISPACEALLOCATIONFACT" ON "T_TRISPACEALLOCATIONFACT" (TRICAPTUREPERIODTXOBJID,triDimSpaceClassTXObjId,triDimLocationTXObjId,triDimWorkpointFlagLI, TRIFACTALLOCWORKPOINTS,TRIFACTALLOCAREAIMPNU) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF02_TRISPACEALLOCATIONFACT" ON "T_TRISPACEALLOCATIONFACT" (TRICAPTUREPERIODTXOBJID,TRIFACTALLOCMOVESNU,TRIFACTALLOCWORKERSNU) TABLESPACE "TRIDATA_INDX";

CREATE INDEX "PERF01_TRIWORKTASK" ON "T_TRIWORKTASK" (SYS_GUIID,UPPER("TRINAMETX"),SYS_OBJECTID) TABLESPACE "TRIDATA_INDX";

CREATE INDEX TRIDATA.IDX2002171047150 ON TRIDATA.T_TRISPACECLASSCURRENT (TRIFLOORCOMMONBL ASC, SPEC_ID ASC);

CREATE INDEX TRIDATA.IDX2002171049050 ON TRIDATA.T_TRISPACE (CLASSIFIEDBYSPACESYSKEY ASC, SYS_PROJECTID ASC,SYS_OBJECTID ASC, TRISTATUSCL ASC, TRINOTCOMMONBL ASC, TRINOAREAROLLUPBL ASC, SPEC_ID ASC, TRIUSERMESSAGEFLAGTX ASC, TRIPATHTX ASC, TRIAREANU ASC, SYS_TYPE1 ASC, SYS_GUIID ASC, TRIAREAUO ASC);

CREATE INDEX TRIDATA.IDX2002171054180 ON TRIDATA.T_TRISPACECLASSCURRENT (TRIBUILDINGCOMMONBL ASC, SPEC_ID ASC, TRINOTRENTABLEBL ASC);

CREATE INDEX TRIDATA.IDX2002171058490 ON TRIDATA.T_ORGANIZATION (SYS_GUIID ASC, SYS_OBJECTID ASC, SPEC_ID ASC, SYS_TYPE1 ASC, TRIPATHTX ASC, TRISHORTNAMETX ASC, TRIORGTYPECLOBJID ASC, TRIORGTYPECL ASC, TRIIDTX ASC, TRINAMETX ASC, TRISTATUSCL ASC);

CREATE INDEX TRIDATA.IDX2002171100140 ON TRIDATA.T_TRISPACE (SYS_OBJECTID ASC, TRINOAREAROLLUPBL ASC);

CREATE INDEX TRIDATA.IDX2002171100080 ON TRIDATA.T_TRISPACE (SYS_OBJECTID ASC, TRISTATUSCL ASC);

CREATE INDEX TRIDATA.IDX2002171102140 ON TRIDATA.T_TRISPACECLASSCURRENT (TRINOTRENTABLEBL ASC, SPEC_ID ASC, TRINAMETX ASC);

CREATE INDEX TRIDATA.IDX2002171104130 ON TRIDATA.T_TRISPACE (CLASSIFIEDBYSPACESYSKEY ASC, SYS_PROJECTID ASC, SYS_OBJECTID ASC, TRISTATUSCL ASC, TRINOAREAROLLUPBL ASC, SPEC_ID ASC, TRINAMETX ASC, TRIOCCUPANCYSTATUSCL ASC, TRIOCCUPANCYSTATUSCLOBJID ASC, TRIAREANU ASC, TRITOTALPRORATEDAREANU ASC, SYS_TYPE1 ASC, SYS_GUIID ASC, TRIAREAUO ASC);

CREATE INDEX TRIDATA.IDX2002171104410 ON TRIDATA.T_TRISPACECLASSCURRENT (TRINOTRENTABLEBL ASC, SPEC_ID ASC);

CREATE INDEX TRIDATA.IDX2002171106310 ON TRIDATA.T_TRISPACE (CLASSIFIEDBYSPACESYSKEY ASC, SYS_PROJECTID ASC, SYS_OBJECTID ASC, TRISTATUSCL ASC, TRINOAREAROLLUPBL ASC, SPEC_ID ASC, TRIUSERMESSAGEFLAGTX ASC, TRIPATHTX ASC, TRIAREANU ASC, SYS_TYPE1 ASC, SYS_GUIID ASC, TRIAREAUO ASC);

CREATE INDEX TRIDATA.IDX2002171108520 ON TRIDATA.T_TRISPACE (CLASSIFIEDBYSPACESYSKEY ASC, SYS_PROJECTID ASC, SYS_OBJECTID ASC, TRISTATUSCL ASC, TRINOAREAROLLUPBL ASC, SPEC_ID ASC, TRINAMETX ASC, TRIOCCUPANCYSTATUSCL ASC, TRIOCCUPANCYSTATUSCLOBJID ASC, TRIAREANU ASC, SYS_TYPE1 ASC, SYS_GUIID ASC, TRIAREAUO ASC);

CREATE INDEX TRIDATA.IDX2002171109370 ON TRIDATA.T_TRISPACECLASSCURRENT (TRINOTUSABLEBL ASC, SPEC_ID ASC, TRIPATHTX ASC, TRINAMETX ASC);

CREATE INDEX TRIDATA.IDX2002171112150 ON TRIDATA.T_TRIMOVEREQUEST (SYS_GUIID ASC, SYS_PROJECTID ASC, SYS_OBJECTID ASC, SPEC_ID ASC, SYS_TYPE1 ASC, TRIREVISIONNU ASC, TRISTATUSCLOBJID ASC, TRILOCATIONREQUESTEDOBJID ASC, TRILOCATIONREQUESTED ASC, TRIREQUESTEDFORTXOBJID ASC, TRIREQUESTEDFORTX ASC, TRIDESCRIPTIONTX ASC, TRIREQUESTCLASSCLOBJID ASC, TRIREQUESTCLASSCL ASC, TRICREATEDDATETIMESY ASC, TRIIDTX ASC, TRIUSERMESSAGEFLAGTX ASC, TRISTATUSCL ASC);

CREATE UNIQUE INDEX TRIDATA.IDX2002171101580 ON TRIDATA.T_TRISPACECLASSCURRENT (SPEC_ID ASC, TRIFLOORCOMMONBL, TRIBUILDINGCOMMONBL, TRIPROPERTYCOMMONBL, TRINAMETX);

CREATE INDEX TRIDATA.PERF1806071724280 ON TRIDATA.T_ORGANIZATION (SYS_GUIID ASC, SYS_OBJECTID ASC, TRIFORMLABELSY ASC, TRIUSERMESSAGEFLAGTX ASC, TRISTATUSCLOBJID ASC, TRISTATUSCL ASC, TRINAMETX ASC, TRIIDTX ASC, SYS_GEOGRAPHYNAMEOBJID ASC, SYS_ORGNAMEOBJID ASC, SYS_TYPE1 ASC, SPEC_ID ASC);
Reserve indexes
Tune any Reserve indexes to include appropriate indexes for performance improvement. The following indexes increase performance for reserve queries on Oracle. However, review and tune these indexes for your implementation.
CREATE INDEX "PERF01_TRIRESERVATIONINSTANCE" ON "T_TRIRESERVATIONINSTANCE" (triPlannedStartDT,SYS_OBJECTID,SYS_GUIID,SYS_PROJECTID,triStatusCL) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF01_TRIRESERVATIONRESOURCE" ON "T_TRIRESERVATIONRESOURCE" (SPEC_ID,SYS_OBJECTID,triResourceTypeLI,SYS_PROJECTID) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF03_TRIPEOPLE" ON "T_TRIPEOPLE" (SPEC_ID,SYS_OBJECTID) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF01_MYPROFILE" ON "T_MYPROFILE" (SPEC_ID,SYS_OBJECTID,triRecordIdSY) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF01_TRIRESERVATIONDEF" ON "T_TRIRESERVATIONDEFINITION" (SPEC_ID,SYS_OBJECTID,SYS_GUIID,SYS_PROJECTID) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF01_TRIROLE" ON "T_TRIROLE" (SPEC_ID,triNameTX) TABLESPACE "PERF34_INDX";

CREATE INDEX "PERF01_TRICONTACTROLE" ON "T_TRICONTACTROLE" (SPEC_ID,SYS_OBJECTID,ClassifiedByRoleSysKey) TABLESPACE "PERF34_INDX";
Lease indexes

Performance benchmark testing for Lease indexes was performed on the DB2® database platform. However, the findings from that platform might also apply to Oracle Database. Your database administrator can take the identified queries from the Db2 results and use the index advisor to see which indexes are used on that platform.

Customizations:

The Db2 results are based on default queries and do not consider any additional columns that might be in your deployment. For more information, see Lease indexes in Tuning the IBM Db2 indexes.

Hierarchy indexes

If you have large hierarchies and see slow hierarchy cache (HCACHE) rebuild times of 5 minutes or more, add the following index to reduce rebuild times:

CREATE UNIQUE INDEX TRIDATA.UK2_IBS_SPEC_STRUCTURE ON TRIDATA.IBS_SPEC_STRUCTURE
(PARENT_SPEC_ID, CHILD_SPEC_ID,CHILD_SPEC_TEMP_ID)
LOGGING
TABLESPACE TRIDATA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 10M
NEXT 10M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
);