24 Tuning Oracle Database

As with any enterprise class business application, there is no simple procedure for tuning that works for all systems. This section describes one sample configuration and outlines the principles for tuning Oracle Database.

Oracle Identity Manager has many configuration options. The best way to identify bottlenecks and optimize performance is to monitor key database performance indicators in your production environment and adjust the configuration accordingly. This chapter serves as a guideline to help you choose the initial baseline database configuration.

This chapter discusses the following topics:

Using Database Roles/Grants for Oracle Identity Manager Database
Sample Instance Configuration Parameters
Physical Data Placement
Database Performance Monitoring

24.1 Using Database Roles/Grants for Oracle Identity Manager Database

As a database administrator, you can create roles to grant all privileges to a secure application role required to run a database application. You can then grant the secure application role to other roles or users. An application can have various roles, each granted a different set of privileges that allow the user access more or less data while using the application. For example, you can create a role with a password to prevent unauthorized use of the privileges granted to the role. An application can be designed in such a way so that when it starts, it enables the proper role. As a result, an application user does not need to know the password for an application's role.

Depending on what is granted or revoked, a grant or revoke takes effect at different times, such as:

All grants and revokes for system and object privileges to users, roles, and PUBLIC grants take immediate effect.
All grants and revokes of roles to users, other roles, and PUBLIC take effect only when a current user session issues a SET ROLE statement to re-enable the role after the grant and revoke, or when a new user session is created after the grant or revoke.

You can see which roles are currently enabled by examining the SESSION_ROLES data dictionary view.

In Oracle Identity Manager, there are prerequisite grants that are provided to Oracle Identity Manager schema to create necessary objects before installing Oracle Identity Manager. Some of these grants can be revoked later on after installing the Oracle Identity Manager and can be granted to particular users in future as required by the application.

Table 24-1 describes the grants required for database applications:

Table 24-1 Role Grants for Database Applications

Role Name	Description	Usage Specific to Oracle Identity Manager	If Revoked
CREATE TABLE	Enables a user to create, modify, and delete tables in the user's schema.	Although this is part of grant resource, this is explicitly required because the grant resource does not allow to create a table through a procedure.	User will not be able to create any new tables programmatically. You can revoke this grant when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object. This grant is needed for initial run of any archival utility because the archival utilities create tables programmatically.
CONNECT	Provides the create session privileges	To create sessions for users	This can be replaced with create session after installation. You can do this when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object.
RESOURCE	Enables a user to create, modify, and delete certain types of schema objects in the schema associated with that user. Grant this role only to developers and to other users that must create schema objects. This role grants a subset of the create object system privileges. For example, it grants the CREATE TABLE system privilege, but does not grant the CREATE VIEW system privilege. It grants the following privileges: CREATE CLUSTER CREATE INDEXTYPE CREATE OPERATOR CREATE PROCEDURE CREATE SEQUENCE CREATE TABLE CREATE TRIGGER CREATE TYPE In addition, this role grants the UNLIMITED TABLESPACE system privilege, which effectively assigns a space usage quota of UNLIMITED on all tablespaces in which the user creates schema objects.	To create sequences, indexes, procedures, triggers, and packages	User will not be able to create any database objects. Only SYS user will be able to do so. You can revoke this grant when the Oracle Identity Manager deployment is stable, which means all the components and connectors are imported and working as expected. This is because each connector creates its own schema object. Specify the quota for tablespaces correctly.
CREATE VIEW	Enables a user to create, modify, and delete views in the user's schema	To create SDP_VISIBLE_V, SDP_REQUIRED_V, SDP_LOOKUPCODE_V, and SDP_RECURSIVE_V views in Oracle Identity Manager	The user will not be able to create any views. Only SYS user will be able to do so.
DBMS_SHARED_POOL	Fits a database object in a shared pool memory	Used for pinning all the procedures and functions used in Oracle Identity Manager in shared memory	It can be revoked after installation but may impact performance because some of the procedures and functions may not be pinned explicitly. The pin_obj procedure is created only for Oracle Identity Manager. It is used to explicitly pin database objects into shared memory. Before revoking this role, make sure that the database-level trigger cache_seq is dropped, if already created.
SYS.DBMS_SYSTEM	Enables an XA Resource Manager and sets privileges so that the XA Resource Manager can manage the interaction between the Oracle database and the applications. Note: Each database connection is enlisted with the transaction manager as a transactional resource. The transaction manager obtains an XA Resource for each connection participating in a global transaction. The transaction manager uses the start method to associate the global transaction with the resource, and it uses the end method to disassociate the transaction from the resource. The resource manager associates the global transaction to all work performed on its data between the start and end method invocations.	For XA resource and database transactions	On Oracle Database version 10.2.0.4 onwards, it can be removed safely. Oracle has redeemed themselves by moving the DIST_TXN_SYNC procedure to a new package called DBMS_XA that is available to the public. Therefore, XA clients do not require execute privilege on DBMS_SYSTEM for later oracle versions.
SYS.DBMS_FLASHBACK	Enables self-service repair. If you accidentally delete rows from a table, then you can recover the deleted rows.	For any failure during reconciliation, you can roll back the changes by using this.	This is required for new reconciliation engine in Oracle Identity Manager 11g Release 1 (11.1.1) for error handling.
CREATE_MATERIALIZED_VIEW	Creates a materialized view in the grantee's schema	To create the OIM_RECON_CHANGES_BY_RES_MV materialized view	User will not be able to create any materialized view. Only SYS user will be able to do so. This materialized view is required for reporting purpose only.
SELECT ON V$XATRANS SELECT ON PENDING_TRANS$ SELECT ON DBA_2PC_PENDING SELECT ON DBA_PENDING_TRANSACTIONS	Enables an XA Resource Manager and sets privileges so that the XA Resource Manager can manage the interaction between the Oracle database and the applications.	NA	Not recommended to remove. Required for XA support.
ADMINISTER DATABASE TRIGGER	Allows the creation of database-level triggers.	To create DDL trigger named ddl_trigger in Oracle Identity Manager	Users will not be able to create new DDL triggers. It can be removed after schema creation.

24.2 Sample Instance Configuration Parameters

Table 24-2 provides information on some important performance-related database initialization parameters for Oracle 11g database.

SGA,PGA size are limited by the underlying operating system restrictions on the maximum available memory in some platforms. See Support Note: Oracle Database Server and the Operating System Memory Limitations [ID 269495.1].

Note:

For the Database Instance Parameters listed in Table 24-2, any one of the following memory management approaches can be used based on the Oracle Database versions:

Using Automatic Memory Management feature available in Oracle Database 11g: Here, the MEMORY_TARGET and MEMORY_MAX_TARGET parameters can be used to manage the SGA and PGA together.
Using Automatic Shared Memory Management (ASMM) available in Oracle Database 10g onward: Here, the SGA components can be managed by specifying the SGA_TARGET and SGA_MAX_SIZE parameters. PGA is managed separately through PGA_AGGREGATE_TARGET.

You should set the processes parameter to accommodate the following connection pool requirements and few extra connections for external programs:

Connection pool size of XA data-source configured in Application Server
Connection pool size for non-XA data-source configured in Application Server
Direct database connection pool size configured in xlconfig.xml

Table 24-2 Sample Configuration Parameters

Parameter	Recommended Initial Settings for Oracle Database 11g
db_block_size	8192
memory_target	Using Automatic Memory Management feature in Oracle Database 11g, the MEMORY_TARGET and MEMORY_MAX_TARGET parameters can be used to manage the SGA and PGA together. Recommended value is 3 GB. When considering MEMORY_TARGET for managing the database memory components, SGA_TARGET and PGA_AGGREGATE_TARGET can be left unallocated, which is 0.
db_keep_cache_size	800M
log_buffer	15 MB
cursor_sharing	FORCE
open_cursors	500
session_cached_cursors	500
query_rewrite_integrity	TRUSTED
query_rewrite_enabled	TRUE
db_file_multiblock_read_count	16
db_writer_processes	2
processes	Based on connection pool settings

24.3 Physical Data Placement

The basic installation of Oracle Identity Manager uses two physical tablespace to store database objects: tablespace oim_lob for orchestration-related LOB data and oim for everything else. Oracle Identity Manager database objects belong to one of the following categories:

Physical tables
Indexes
Large objects (LOBs or CLOBs)

Tip:

To minimize disk space consumption, Oracle recommends the following:

During the initial startup phase of the deployment, Oracle Identity Manager tablespace is expected to grow at the rate 20G for every hundred thousand users reconciled into Oracle Identity Manager. LOB tablespace grows at around 30% of the size of main Oracle Identity Manager tablespace for the same users. Depending on the usage of orchestration in Oracle Identity Manager, which affects the LOB tablespace growth, the LOB tablespace can grow at a rate of 60% to 100% of the main tablespace in scenarios where orchestration is widely used.

Database administrators must monitor the exact growth rate in the real system for efficient disk space management.

For better performance, create multiple locally managed tablespaces and store each category of database object in a dedicated tablespace. This storage optimization helps efficient data access. The tables that are frequently accessed and have potential growth are highlighted in the following sections. Oracle recommends that you place these tables in their own dedicated tablespace(s).

Note that the tables highlighted in the following sections generally grow bigger and are accessed frequently in a typical Oracle Identity Manager deployment. In addition, you can use performance metrics to identify tables that are accessed frequently (hot tables). To reduce I/O contention, move hot tables to dedicated tablespaces. See "Database Performance Monitoring" for more information about performance metrics.

Note:

Oracle Identity Manager offers archival and purge solution to contain the data growth in most of these tables. See Chapter 23, "Using the Archival Utilities" for more information.

24.3.1 Tasks Tables

Oracle Identity Manager stores provisioning and approval task details in the following tables. These tables have lot of potential to grow big overtime. It is recommended to group these in one or more dedicated tablespaces.

24.3.2 Reconcliation Tables

The reconciliation schema of Oracle Identity Manager has both static and dynamic tables. The following is a list of static tables. The dynamic tables can be identified by querying the RECON_TABLE_NAME column in the RECON_TABLES table.

RECON_ACCOUNT_OLDSTATE
RECON_BATCHES
RECON_CHILD_MATCH
RECON_EVENTS
RECON_EVENT_ASSIGNMENT
RECON_EXCEPTIONS
RECON_HISTORY
RECON_JOBS
RECON_TABLES
RECON_UGP_OLDSTATE
RECON_USER_OLDSTATE
RECON_ACCOUNT_MATCH
RECON_ORG_MATCH
RECON_ROLE_HIERARCHY_MATCH
RECON_ROLE_MATCH
RECON_ROLE_MEMBER_MATCH
RECON_USER_MATCH
RA_LDAPUSER
RA_MLS_LDAPUSER
RA_LDAPROLE
RA_MLS_LDAPROLE
RA_LDAPROLEMEMBERSHIP
RA_LDAPROLEHIERARCHY

If your environment generates a large amount of reconciliation data, then move these tables to one or more dedicated tablespace(s).

24.3.3 Audit Tables

Oracle Identity Manager audits the transactions based on the audit level setting. Most of the audit levels are likely to increase data growth significantly. Oracle recommends storing audit tables in their own tablespace. Oracle Identity Manager audit tables are of two categories. Following are the tables that store audit data in XML format. In this list, UPA table is especially expected to grow big and it is important to place it in a dedicated tablespace.

The user profile audit data is stored in the following flat structured tables. These tables are used by Oracle Identity Manager historical reports for compliance reporting. It is recommended to store these tables and their indexes in a dedicated tablespace.

UPA_FIELDS
UPA_GRP_MEMBERSHIP
UPA_RESOURCE
UPA_USR
UPA_UD_FORMS
UPA_UD_FORMFIELDS

24.3.4 Redo-Log Files

Depending on the reconciliation processes configured in Oracle Identity Manager, the volume of database transactions and commits during a reconciliation run can be high. Oracle recommends that you use multiple redo-log files. The total allocated redo-log space should be 1 GB to 2 GB.

Oracle recommends use of at least three redo log groups with redo log members with minimum size of 500 MB for each. The multiplexing and the exact number of members and disk space for each member can be considered in accordance with the planning for failure.

24.3.5 Keep Pool Changes

By default, Oracle Identity Manager assigns frequently referenced small tables to be cached in the database by using a keep pool buffer. See db_keep_cache_size in Table 24-2. The USR table which stores user records is also cached by default. If your installation contains more than 50,000 users, then Oracle recommends that you use the default database buffer for USR table instead of the keep pool buffer. You can use the following command to put USR table in default buffer pool.

ALTER TABLE USR STORAGE(buffer_pool default);

24.4 Database Performance Monitoring

To identify performance bottlenecks, you can monitor real-time performance metrics for the Oracle Identity Manager database.

Perform the following at regular intervals:

Monitor real-time performance by using a performance-monitoring tool such as Oracle Enterprise Manager Fusion Middleware Control or Automatic Workload Repository (AWR) in Oracle Database 11g.
Note:

You can use Oracle Enterprise Manager Fusion Middleware Control to monitor Oracle Identity Manager. To do so:
1. Under Identity Management, select Oracle Identity Manager to go to the home page. On the Home page, you can monitor Oracle Identity Manager.
2. From the Oracle Identity Manager menu, select Performance to view performance metrics.
Collect routine statistics and report by using Oracle Database Enterprise Manager (EM), which is available in Oracle Database 11g (as a standard offering).
- Routine Statistics Gathering
  
  Routine statistics gathering can be taken care by the 'Automated Maintenance Tasks', which is available in the following navigation path in Oracle Database 11g:
  
  Oracle EM, the Server tab, Query Optimizer, Manage Optimizer Statistics, the Automated Maintenance Tasks link
- Reporting requirements of statistics through Oracle Database 11g EM
  
  To report on the state of the currently gathered statistics, EM provides a reporting interface in the following navigation path:
  
  Oracle EM, the Server tab, Query Optimizer, Manage Optimizer Statistics, the Object Statistics link
  
  This interface can be used for the reporting purpose for All Objects (of the Schema or even the Object of choice), which have Stale, Missing, or Locked states or are already analyzed.
Collect complete schema statistics upon implementation of Oracle Identity Manager.

Update schema statistics regularly, so that the Cost-Based Optimizer (CBO) can access the latest statistics. You must consider complete schema or table statistics on mass data change events such as bulkload of users or accounts, import of a new connector, a huge reconciliation run from a new target system, or use of an archival utility.

This helps the CBO determine an efficient query execution plan that is based on the current state of data. The following is a sample SQL command to collect database statistics on a regular basis:

See Also:

Gathering routine statistics and reporting can be done by performing the automated maintenance tasks available in Oracle Database 11g. See Oracle Database Performance Tuning Guide 11g Release 1 (11.1) for details.
```
DBMS_STATS.GATHER_SCHEMA_STATS(OWNNAME=> schema_owner,
  ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,
  DEGREE=>8,
  OPTIONS=>'GATHER AUTO',
  NO_INVALIDATE=>FALSE);
```
Look for relevant recommendations provided in advisory sections in the Automatic Database Diagnostic Monitor (ADDM) or Automatic Workload Repository (AWR) report, and adjust the instance configuration parameters according to the recommended settings. This is specially required after importing a new connector and completing a round of reconciliation from a new target system so that you can identify the need of any new indexes according to your matching rules.