Różności …

Partition based parallelism.
Atable with 12 partitions (for example, one partition for each month of the year) canhave a maximum number of 12 PEs for an update or delete. An update to only onemonth of data would have no parallelism because it involves only one partition.

Oracle can execute an INSERT statement of the form INSERT INTO tableXSELECT…FROM tableY in parallel for nonpartitioned and partitioned tables.

Memory resources.

An Oracle instance uses the database server’s memory resources to cache theinformation accessed to improve performance. Oracle utilizes an area of shared memory called the System Global Area (SGA) and a private memory area for each server process called the Program Global Area (PGA).

Oracle Database 11g adds automatic memory management of the SGA and PGA.

Oracle uses the SGA for the following operations:

Caching of database blocks containing table and index data in the database buffer cache

Caching of parsed and optimized SQL statements, stored procedures, and data dictionary information in the shared pool

Buffering of redo log entries in the redo log buffer before they’re written to disk.

Using automatic shared memory management, the database automatically allocates memory for the following SGA pools: database buffer cache, shared pool, large pool, Java pool, and Streams pool. Youhave to specify only the total amount of memory required by setting the SGA_TARGET initialization parameter.

You canalso specify the minimum amount of memory for any of the SGA pools while usingautomatic SGA sizing using the following initialization parameters: DB_CACHE_SIZE, SHARED_POOL_SIZE, LARGE_POOL_SIZE, JAVA_POOL_SIZE, and STREAMS_POOL_SIZE. A few SGA pools, specified using such parameters as LOG_BUFFER, DB_KEEP_CACHE_SIZE, and DB_RECYCLE_CACHE_SIZE, are still manually sized.

The Databae Buffer Cache DB_CACHE_SIZE

If youdecide to disable SGA_TARGET by setting it to 0, youwill need to manuallyset initialization parameters for the memory pools (unless you want to use previoussizes). For the database buffer cache, you would assess the percentage of the databaseblocks requested by users read from the cache versus from the disk. Thispercentage is termed the hit ratio. If response times are too high and this ratio islower than 90% (as a rule of thumb), increasing the value of the initialization parameter DB_CACHE_SIZE can increase performance.

Not all operations read from the database buffer cache: large full-table scans are limited to a small number of buffers to avoid adversely impacting other users by dominating the cache. Parallel table scans completely bypass thebuffer cache.

The Shared Pool SHARED_POOL_SIZE

The shared pool is accessed to cache the SQL sent to the database and for the data dictionary information required to executethe SQL.
Because of its central role in database operations, a shared pool that is too small may have a greater impact on performance than a database buffer cache that is too small.

A shared pool that is too small will cause poor performance for a variety of reasons,
affecting all users. These reasons include the following:
• Not enough data dictionary information can be cached, resulting in frequent
disk access to query and update the data dictionary.
• Not enough SQL can be cached, leading to memory “churn,” or the flushing of
useful statements to make room for incoming statements. A well-designed application
issues the same statements repeatedly.
• Not enough stored procedures can be cached, leading to similar memory churn
and performance issues for the program logic stored and executed in the
database.

Shared pool sizes inthe 150–250 MB range are not uncomm on for large, active databases.

The redo log buffer LOG_BUFFER

While the redo log buffer consumes a very small amount of memory in the SGA relative to the database buffer cache and the shared pool, it’s critical for performance. The redo log buffer is flushed to the redologs on disk when a transaction is committed (normally) or when the redo log bufferis one-third full. In a busy database, transactions may generate enough redo to fill theremaining unfenced portion of the redo log buffer before the I/O to the disks for thefenced area of the redo log buffer is complete. If this happens, the transactions willhave to wait for the I/O to complete because there is no more space in the redo logbuffer. This situation can impact performance.

The statistic “redo buffer allocation retries” can be used to understand this situation.

SELECT name, value FROM V$SYSSTAT WHERE name = ‘redo buffer allocation retries’;

Ideally, the value of “redo bufferallocation retries” should be close to 0.

PGA

Each server has a Program Global Area (PGA), which is a private memory area thatcontains information about the work the server process is performing. There is onePGA for each server process.
The PGA consists of a working memory area (nie można ustawiać rozmiaruO for things such as temporary variables used by the server process, memory for information about the SQL the server process is executing, and memory for sorting rows as part of SQL execution.

Memory for SQL statements
When a server process executes a SQL statement for a user, the server process tracks the session-specific details about the SQL statement and the progress by executing it in a piece of memory in the PGA called a private SQL area, also known as a cursor. The private SQL area contains the session-specific information about the executionof the SQL statement within the session, such as the number of rows retrieved so far.

OLTP systems typically have a “working set” of SQL statements that each user submits. For example, a user who enters car rental reservations uses the same forms inthe application repeatedly. Performance will be improved if the user’s server processhas enough memory in the PGA to cache the SQL those forms issue.

Memory for sorting within the PGA
Each server process uses memory in its PGA for sorting rows before returning themto the user. If the memory allocated for sorting is insufficient to hold all the rows that need to be sorted, the server process sorts the rows in multiple passes called runs. The intermediate runs are written to the temporary tablespace of the user, whichreduces sort performance because it involves disk I/O. Sizing the sort area of the PGA was a critical tuning point in Oracle database releasesprior to Oracle Database 10g.
As of Oracle Database 10g, the database provides automatic sizing for the PGA.   Bydefault, this memory management is enabled, and sizing for PGA work areas is basedon 20 percent of the SGA memory size.

TimesTen

Baza danych całkowicie w pamięci RAM. TimesTen is mostappropriate for a high-load OLTP environment that requires extremely high throughputand real-time responsiveness.

A TimesTen instance can be used as a cache for an Oracle database. You load a subset of Oracle tables into the TimesTen instance, and the Cache Connect to Oraclefeature keeps the data synchronized.

CPU

Poorly written SQL is the number one cause of performance problems.

Oracle Database 10g introduced the SQL Tuning Advisor, a tool that can notonly recognize poorly written SQL, but also create an optimizer plan to circumventthe problem and replace the standard optimization plan with the improvedplan.