Oracle OLTP features.
Nonescalating row-level locking
Multiversion read consistency - Oracle provides statement-level and transaction-level data consistency withoutrequiring read locks. Oracle uses rollback segments toreproduce data as it existed at the time the query started.
Shared SQL - The parsing of a SQL statement is fairly CPU-intensive. Oracle caches parsedand optimized SQL statements in the shared SQL area within the shared pool.
Bind Variables and Shared SQL
Oracle’s shared SQL is a key feature for building high-performanceapplications. In an OLTP application, similar SQL statements may be used repeatedly,but each SQL statement submitted will have different selection criteria contained in theWHERE clause to identify the different sets of rows on which to operate. Oracle canshare SQL statements, but the statements must be absolutely identical.To take advantage of this feature for statements that are identical except for specificvalues in a WHERE clause, you can use bind variables in your SQL statements. Thevalues substituted for the bind variables in the SQL statement may be different, but thestatement itself is the same.
Youcan write the application to use bind variables
UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;
Stored outlines - The route a SQL statement takes during executionis critical for high performance. Once application developers and DBAshave tuned a SQL statement for maximum efficiency, they can force the Oracleoptimizer to use the same execution plan regardless of environmental changes.
Multi-Threaded Server/shared server – allow Oracle to support larger user populations.
Database Resource Manager - The DRM allocates CPU and parallelism resources to consumer groups based on resource plans. For example, you can allocate 80 percent of the CPU resources to order-entry users, with the remaining 20 percent allocated to users asking for reports. If the order-entry users aren’t using all the allocated resources, the reporting users can use more than their allotted percentage. With the DRM, you can dynamically alter the details of the plan without shutting down the instance. Oracle9i also added to the Database Resource Manager the ability to proactively estimate the amount of CPU that an operation will require. If an operation looks as if it will exceed the maximum CPU time specified for a resource group, the operation will not be executed, which can prevent inappropriately large operations from even starting.
The DRM now allows a DBA to specify the number of active sessions availableto a consumer group.
Real Application Clusters (235)