Many features in Oracle E-Business Suite Release 12 are built on the advanced capabilities of the underlying Oracle database technology. Release 12 utilizes various Oracle database features to optimize performance and scalability.
Oracle Database 11g, which is used by Oracle E-Business Suite Release 12.1, includes a number of sophisticated features that enable you to track the performance of your database, and if necessary take the appropriate corrective action.
Note: For further details of the capabilities and usage of the tools described, see Oracle Database Performance Tuning Guide.
The Automatic Workload Repository is a repository of database performance statistics built in to every Oracle 11g database. AWR automatically generates snapshots of performance data at regular intervals (typically, once an hour) and collects the statistics for use in problem detection and tuning. The gathered data can be displayed in both reports and views.
You can access AWR through Oracle Enterprise Manager Database Control, managing snapshots or modifying settings in order to create baselines that capture typical performance periods. The baselines can be used for comparisons with similar workload periods where performance problems have been reported.
The Automatic Database Diagnostic Monitor is a tool that allows an Oracle database to diagnose its performance, and determine how identified problems could be resolved. ADDM analyzes the AWR data on a regular basis, locating the root causes of performance problems and providing recommendations for correcting them. Because AWR is a repository of historical performance data, ADDM can be used to analyze performance issues after the event, saving time and resources in reproducing a problem (which may not even be possible).
Automatic database diagnostic monitoring is enabled by default, and its primary interface is Oracle Enterprise Manager Database Control.
Active Session History is a means by which a detailed history of database activity is captured and stored. Only active sessions are captured, so the amount of data recorded is directly related to the work being performed. The V$ACTIVE_SESSION_HISTORY view records current sampled session activity.
Unlike the instance-level statistics gathered by AWR, ASH gathers data at the session level. You can run ASH reports to analyze transient performance problems with the database that may only occur during specific times. For example, ASH can often be used to identify short-duration problems (perhaps lasting only a couple of minutes) that would represent too small a proportion of an ADDM analysis period to show up.
Database performance features include optimization, resource usage, space management, and access rights.
The SQL used in Release 12 has been extensively tuned for cost-based optimization. In calculating the lowest cost (most efficient) method of executing an SQL statement, the Oracle query optimizer evaluates many factors to calculate the most efficient way to execute a SQL statement. For example, the optimizer considers the available access paths, factoring in statistical information for the tables and indexes that the SQL statement will access. The optimizer also considers hints, which are optimization suggestions placed in a comment of the SQL statement.
As part of its operation, the optimizer creates a set of potential execution plans for the SQL statement, based on the available access paths and any hints. It then estimates the cost of each execution plan, based on data dictionary statistics for the data distribution and storage characteristics of the tables, indexes, and partitions. Finally, the optimizer compares the costs of the execution plans and chooses the one with the smallest cost, which means optimum execution characteristics.
For some operations, such as batch processing, Release 12 uses cost-based optimization to achieve the most efficient means of processing all rows that are accessed by the statement. For other operations, such as accessing forms or communication with the desktop client, Release 12 uses cost-based optimization to achieve the best response time for processing the first rows that are accessed by the statement.
Several other Oracle database performance features in Release 12 also require use of the cost-based query optimizer.
Note: For further details of optimization, see: Oracle Database Concepts and Oracle Database Performance Tuning Guide.
The gives the system administrator extensive control over processing resources on the database node. The administrator can distribute server CPU based on business rules, ensuring that the highest priority activities always have sufficient CPU resources. The administrator could, for example, guarantee Order Entry users 40% of CPU resources during business hours, regardless of the load or number of users in other groups on the system.
System administrators can also use the Database Resource Manager to limit the impact of any inefficient ad hoc queries. For example, a limit of 5% of CPU resources could be placed on ad hoc queries against the database.
Note: For further details, see Oracle Database Concepts and Oracle Database Administrator's Guide
Partitioning helps support very large tables and indexes by dividing them into smaller, more manageable pieces called partitions. Once the desired partitions have been defined, SQL statements containing the partition key can access them instead of the original tables or indexes and thereby reduce the I/O needed.
Partitioning can significantly enhance performance and manageability. For example, the speed of operations that involve copying or deleting data may be greatly improved by the use of partitioned tables. Operations that might have taken hours might now be completed in minutes. This can be useful in data warehouse applications.
Important: Custom partitioning of standard Oracle E-Business Suite tables in Release 12 is fully supported for objects that are not already partitioned.
Partitioning should always be planned and tested carefully before being implemented on a critical system. After implementation, you should check that the desired performance benefits have been achieved.
As well as providing more computing power, multi-node systems facilitate the addition of machines to meet increases in demand. They also provide resilience in the event of failures of individual components.
Oracle Real Application Clusters (Oracle RAC) harness the processing power of multiple interconnected computers. Oracle RAC software called Oracle Clusterware and a collection of computers (known as a cluster) harness the processing power of each component to create a robust and powerful computing environment. A large task divided into subtasks and distributed among multiple nodes is completed more quickly and efficiently than if the entire task was processed on one node. Cluster processing also facilitates deployment of additional hardware resources for larger workloads and rapidly growing user populations.
In Oracle RAC environments, all active instances can concurrently execute transactions against a shared database. Oracle RAC coordinates each instance's access to the shared data, to provide data consistency and data integrity. From a developer's point of view, Oracle RAC enables applications to be scaled to meet increasing data processing demands, without the need to change the application code.
All Oracle E-Business Suite modules can be successfully deployed against a Oracle RAC-enabled database. Using Parallel Concurrent Processing (see Chapter 1), concurrent managers on separate application tier machines can be configured to direct requests to different database servers in an Oracle RAC cluster.
Automatic Storage Management (ASM) provides a file system and volume manager dedicated to the storage of Oracle database files. It extends the concepts of disk striping and mirroring, to optimize performance and remove the need for manual I/O tuning.
To meet the increasing demand for up-to-date details of business activities, Oracle E-Business Suite utilizes Oracle database features that help to optimize the types of query typically required in such environments.
Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. They can markedly increase the speed of queries on very large databases when used to precompute and store aggregated data such as sums and averages. Materialized views can therefore improve performance of Oracle E-Business Suite products that perform many queries on summary data.
Query optimization can use materialized views to improve query performance by automatically recognizing when one can be used to satisfy a request. The optimizer transparently rewrites the request to use the materialized view. Queries are then directed to the materialized view, and not to the underlying detail tables or views.
In distributed environments, materialized views can be used to replicate data at remote sites, providing local access to data that would otherwise have to be accessed from the main site, with any network delays this might introduce.
The Oracle database has always included mechanisms to protect its contents from unauthorized access, without hindering access by legitimate users. Details of these can be found in the standard database documentation.
Additional features provide application monitoring capabilities; for example, Database Connection Tagging enables recording in V$SESSION of the current use to which a database connection is being put.
Note: For further details of Database Connection Tagging, see My Oracle Support Knowledge Document 1100653.1, Using Database Connection Tagging With Oracle E-Business Suite Release 12.1.3.
Various strategies and optional components can be used to enhance and customize security to meet the specific needs of a site. Chapter 11 discusses optional database features that can be used to enhance and customize security further.