23 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

23.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 23-1 describes the grants required for database applications:

Table 23-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.

23.2 Sample Instance Configuration Parameters

The following sample configuration parameter settings are based on a server with four CPUs (64 bit) and 8 or 20 gigabytes (GB) RAM.

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:

In Table 23-2, ASMM denotes the Automatic Shared Memory Management feature available in Oracle Database 10g onward. It automatically distributes the memory among various subcomponents to ensure the most effective memory utilization.

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 23-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. Minimum value is 6 GB. For maximum value, use the following formula: MEMORY_TARGET/MEMORY_MAX_TARGET=Total Memory X 80% or 20GB, whichever is greater, assuming that the computer has the database as the primary consumer. When considering MEMORY_TARGET for mangaging the database memory components, SGA_TARGET and PGA_AGGREGATE_TARGET can be left unallocated, which is 0.
`sga_target`	If you use ASMM available in Oracle Database 10g onward, then the SGA components can be managed by specifying the SGA_TARGET and SGA_MAX_SIZE parameters. PGA is managed separately through PGA_AGGREGATE_TARGET.Use any one of the two memory management approaches: MEMORY_TARGET available in Oracle Database 11g SGA_TARGET/PGA_AGGREGATE_TARGET available in Oracle Database 10g onward Use Oracle ASMM. Minimum value is 4 GB. For maximum value, use the following formula: SGA_TARGET=Total Memory X 80% X 60% or 16 GB assuming an overall memory cap of 20 GB for the Oracle Identity Manager database to run. Assuming that the computer has the Database as the primary consumer. Note: These memory paramater values are ballparked figures. As a database administrator, you can also refer to the memory advisors to manage and tune the database.
`sga_max_size`	10 GB
`pga_aggregate_target`	Minimum value is 2 GB. For maximum value, use the following formula: PGA_TARGET=Total Memory X 80% X 40% or 4 GB whichever is greater Assuming that the computer has the Database as the primary consumer.
`db_keep_cache_size`	800M
`log_buffer`	15 MB
`cursor_sharing`	FORCE
`open_cursors`	2000
`session_cached_cursors`	800
`query_rewrite_integrity`	TRUSTED
`db_file_multiblock_read_count`	16
`db_writer_processes`	2
`processes`	Based on connection pool settings

23.3 Physical Data Placement

The basic installation of Oracle Identity Manager uses two physical tablespace to store database objects: an (oim_lob) for orchestration-related LOB data and other (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 100K 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 optimizes storage for efficient data access. Oracle recommends that you place the following User Profile Audit (UPA) component tables and indexes in their own tablespaces:

UPA
UPA_FIELDS
UPA_GRP_MEMBERSHIP
UPA_RESOURCE
UPA_USR

These tables can store significant amounts of historical data and can be used by historical reports.

The database schema includes the following tables for reconciliation data:

If your environment generates a large amount of reconciliation data, move these tables to a new tablespace. Use the locally managed tablespaces with automatic extent allocation.

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.

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.

Keep Pool Changes

By default, Oracle Identity Manager assigns USR and PCQ tables to be cached in the database by using a keep pool buffer (see db_keep_cache_size in Table 23-2). If your installation contains more than 50,000 users, then Oracle recommends that you use the default database buffer for USR and PCQ tables instead of the keep pool buffer. You can use the following commands.

ALTER TABLE USR STORAGE(buffer_pool default);

ALTER TABLE PCQ STORAGE(buffer_pool default);

23.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 console or Automatic Workload Repository (AWR) in Oracle Database 11g.
Note:
You can use Oracle Enterprise Manager 11g 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 Stats 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 stats 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.