Optimization within a RAC database

Oracle databases offer multiple optimization possibilities in the sight of the database administrator. One must revert to these if the problem cannot be resolved in the application.

This is typically the case for third-party applications, where we have no influence how SQL operations are generated. Therefore it is important to know the specific characteristics of the application to be optimized in order to choose the most promising starting point for the optimal outcome.

Parameterization of the Interconnect

Through the adjustment, different hidden parameters are impeded so that individual data blocks will not be maintained disproportionately long within a physical instance.
This avoids that sessions seem to be blocked by waiting endless for getting access to a single block to complete an operation.
This may impact the overall performance slightly negative in theory but the reduction of variance in execution time is much more important in practice.

Please keep in mind that the suggestions from Oracle using a fast dedicated network for the interconnect should independent of any kind of parametrization have the highest importance.

Multiple Block Sizes

Finding a balance between the blocksize needed to store a number of records, the concurrency between the different nodes accessing the block and the additional traffic on the
interconnect generated by the worse relation between working and administrative data for smaller blocksizes will minimize the time a sessison has to wait for a datablock needed.

Adjustment of the number of ITL Slots

Through one of the current database objects matched number of database objectsthe wait time will be minimized and deadlocks effectively avoided.
Durch eine an die Verwendung des jeweiligen Datenbankobjekts angepasste Anzahl von ITL-Slots werden Wartezeiten minimiert und Deadlocks erfolgreich vermieden.

Plan stability, stored outlines, and baselines

Stabilization and optimization of the execution plans dependent upon the database version through the specific use and manipulation of stored outlines, and accordingly, the creation of baselines.
Stabilisierung und Optimierung von Ausführungsplänen in Abhängigkeit der Datenbankversion durch die gezielte Verwendung und Manipulation von Stored Outlines, bzw. der Erstellung von Baselines.

Partitioning

Through the use of a key for a hash- or compound partitioning chosen in the application, the access to a central table can be successfully equalized.
Durch die Verwendung geschickt gewählter Schlüssel für eine Hash- oder zusammengesetzte Partitionierung kann der Zugriff auf einige zentrale Tabellen erfolgreich entzerrt werden.

Indexing

The following problem states are shown:
* Index contention due to frequent block splittings.
* The use of unselective and imcomplete indices.
* The advantages and limitations of the application of reverse indices.
* Avoidance of access through the instance-dependent expansion of index values.
Es werden dabei die folgenden Problemstellungen betrachtet:

Use of Sequences

Correct parameterization of the used sequences in the database columns and the set of problems from instance-dependent number circles with the synchronisation and eradication of working processes.
Korrekte Parametrierung der verwendeten Sequenzen auf Datenbankebene und die Problematik von instanzabhängigen Nummernkreisen bei der Synchronisation und Abarbeitung von Geschäftsprozessen.

Invalidation of the SQL area

Although the individual user only moves in a small part of the Siebel CRM application, there are notable delays due to a permanent invalidation of the SQL area with a subsequent reparsing of all database requests.
Obwohl die einzelnen Benutzer sich nur in einem kleinen Teil der Siebel CRM Anwendung bewegen gibt es merkliche Verzögerungen aufgrund einer permanenten Invalidierung der SQL-Area mit einem anschließenden Neuparsen aller Datenbankabfragen.

Reparsing von SQL

Assymetric behaviour of nodes

Note

With consideration of the above specified optimization possibilities, Oracle RAC demonstrates to be the ideal solution for most uses. Through a high scaling factor, a large degree of safety is provided, so that requirements that arise in the future are met.