Różności …

Update tabeli

1. The client modifies the employee name on the screen. The client process sends a
SQL UPDATE statement over the network to the server process.
2. The server process obtains a System Change Number and reads the data block
containing the target row.
3. The server records row lock information in the data block.
4. The server writes the old image of the data to the redo buffers in memory, and
then writes the changes to a rollback segment and modifies the employee data,
which includes writing the SCN to the ORA_ROWSCN pseudocolumn in Oracle
Database 10g or newer database releases.
5. The server process writes the redo buffers to disk, and then writes the rollback
segments and the changed data to disk. The rollback segment changes are part of
the redo, since the redo log stores all changes coming from the transaction.
6. The HR clerk commits the transaction.
7. Log Writer (LGWR) writes the redo information for the entire transaction,
including the SCN that marks the time the transaction was committed, from the
redo log buffer to the current redo log file on disk. When the operating system
confirms that the write to the redo log file has successfully completed, the transaction
is considered committed.
8. The server process sends a message to the client confirming the commit.

A confilicting write operation

1. Client A modifies the employee name on the screen. Client A sends a SQL
UPDATE statement over the network to the server process.
2. The server process obtains an SCN for the statement and reads the data block
containing the target row.
3. The server records row lock information in the data block.
4. The server process writes the changes to the redo log buffer.
5. The server process copies the old image of the employee data about to be
changed to a rollback segment. Once the server process has completed this
work, the process modifies the employee data, which includes writing the SCN
to the ORA_ROWSCN pseudocolumn in Oracle Database 10g or newer database
releases.
6. Client B modifies the employee name on the screen and sends a SQL UPDATE
statement to the server.
7. The server process obtains an SCN and reads the data block containing the target
row.
8. The server process sees that there is a lock on the target row from the information
in the header of the data block, so it takes one of two actions. If the
isolation level on Client B’s transaction is READ COMMITTED, the server
process waits for the blocking transaction to complete. If the isolation level for
Client B’s transaction is SERIALIZABLE, an error is returned to the client.
9. Client A commits the transaction, the server process takes the appropriate
action, and the server sends a message to Client A confirming the commit.
10. If Client B executed the SQL statement with the READ COMMITTED isolation
level, the SQL statement then proceeds through its normal operation.

Read and write operation.

1. Client A sends a SQL SELECT statement over the network to the server process.
2. The server process obtains an SCN for the statement and begins to read the
requested data for the query. For each data block that it reads, it compares the
SCN of the SELECT statement with the SCNs for any transactions for the relevant
rows of the data block. If the server finds a transaction with a later SCN
than the current SELECT statement, the server process uses data in the rollback
segments to create a “consistent read” version of the data block, current as of the
time the SELECT was issued. This is what provides the multiversion read consistency
(MVRC) and avoids the need for Oracle to use read locks on data. If a row
has been updated since the transaction started, Oracle simply gets the earlier version
of the data for a consistent view.
3. Client B sends a SQL UPDATE statement for a row in the EMP table that has
not yet been read by Client A’s SELECT statement. The server process gets an
SCN for the statement and begins the operation.
4. Client B commits his changes. The server process completes the operation,
which includes recording information in the data block that contained the modified
row that allows Oracle to determine the SCN for the update transaction.
5. The server process for Client A’s read operation comes to the newly modified
block. It sees that the data block contains changes made by a transaction that
has an SCN that is later than the SCN of the SELECT statement. The server process
looks in the data block header, which has a pointer to the rollback segment
that contains the data as it existed when Client A’s transaction started. The rollback
segment uses the old version of the data to create a version of the block as
it existed when the SELECT statement started. Client A’s SELECT statement
reads the desired rows from this consistent version of the data block.

Oracle is one of the only databases on the market that doesn’t use read locks, Oracle will essentially always deliver the lowest amount of database contention.

A workspace is a way to isolate data from changes in the general database environment. Workspace Manager accomplishes this by creating workspace-specific versions of data. When youcreate a workspace, you essentially create a snapshot of the data in the workspace at a specific point in time. Further changes to that data from outside the workspace do not affect the view of the data in the workspace, and changes made to data within the workspace are not seen by users outside the workspace. Changes to data within a workspace are visible only to other workspace. Workspaces allow youto essentially create separate data environments for specialized usage.

The key to workspaces is the support of multiple versions of the same data. To use workspaces to version data in a table, youmu st first enable the table for versioning. Workspace Manager can version-enable one or more user tables in the database. users. The unit of versioning is a row. Versioned rows are stored in the same table as the original rows. Oracle implements workspaces by adding metadata to the rows of a table. The timestamp allows users in a workspace to go back to any point in time and view the database from the perspective of changes made in that workspace up to another point in time.

Workspace operations:

Rollback
Youcan roll back changes to a workspace to return the workspace to the point
in time when the workspace was created. Youcan also designate savepoints,
which allow you to roll back the changes in a workspace to a subsequent point
in time.
Refresh
Refreshing a workspace means bringing the data in a workspace into agreement
with the same data in the overall database.
Merge
A merge operation rolls changes made in a workspace into its parent workspace.

Transaction Processing

ACID properties of a transaction:

Atomic
The entire transaction succeeds or fails as a complete unit.
Consistent
A completed transaction leaves the affected data in a consistent or correct state.
Isolated
Each transaction executes in isolation and doesn’t affect the states of others.
Durable
The changes resulting from committed transactions are persistent.

OLTP

High transaction volumes and large user populations. Online transaction processing implies direct and conversational interaction between the transaction processing system and its users. (223)

DRM (Database Resource Manager) works by leveraging consumer groups you’ve identified and enabling you to place limits on the amount of computer resources that can be used by that group. (Predicting resource utilization, Switching consumer groups, Limiting number of connections)

Multiuser Concurrency

Database systems must protect data integrity.

The transaction is the bedrock of data integrity in multiuser databases and the foundation of all concurrency schemes. A transaction is defined as a single indivisible piece of work that affects some data. Once a transaction is committed, the changes made by that transaction become permanent and are made visible to other transactions and other users.

Transactions always occur over time, although most transactions occur over a very short period of time. Since the changes made by a transaction aren’t official until the transaction is committed, each individual transaction must be isolated from the effects of other transactions. The mechanism used to enforce transaction isolation is the lock. A lock prevents users from modifying data.

Aby zapobiec możliwości nadpisania zmian jednej transakcji przez drugą równoczesną ale np. dłużej trwającą transakcję Oracle używa dwóch rodzajów blokad (locks):

An exclusive lock (inaczej write lock) is applied and held while changes are made to data in the course of a transaction and released when the transaction is ended by either a COMMIT or a ROLLBACK statement. A write lock can be held by only one user at a time, so only one user at a time can change that data.

A read lock (shared lock) can be held by any number of users who are merely reading the data, since the same piece of data can be shared among many readers. However, a read lock prevents a write lock from being placed on the data, as the write lock is an exclusive lock. Oracle uses read locks only when a SQL operation specifically requests them with the FOR UPDATE clause in a SELECT statement.

The interference caused by conflicting locks is called contention.

Serialization – serializable transactions appear as though they have been executed in a series of distinct, ordered transactions. When one transaction begins, it is isolated from any changes that occur to its data from subsequent transactions. To the user, a serializable transaction looks as though it has the exclusive use of the database for the duration of the transaction. Serializable transactions are predictable and reproducible, the two cardinal virtues of data integrity.

Multiversion Read Consistency MVRC – Multiversion read consistency guarantees that a user sees a consistent view of the data she requests. If another user changes the underlying data during the query execution, Oracle maintains a version of the data as it existed at the time the query began. If there were transactions underway but uncommitted at the time the query began, Oracle will ensure that the query ignores the changes made by those transactions. The data returned to the query will reflect all committed transactions at the time the query started.

This feature has two dramatic effects on the way queries impact the database. First, Oracle doesn’t place any locks on data for read operations. This means that a read operation will never block a write operation. A user gets a complete “snapshot” view of the data, accurate at the point in time that the query began.

Isolation levels - describe how a transaction will interact with other transactions and how it will be isolated from other transactions.

An application programmer can set an isolation level at the session level (ALTER SESSION) or transaction level (SET TRANSACTION).

READ COMMITTED – Enforces serialization at the statement level. This means that every statement will get a consistent view of the data as it existed at the start of that statement. However, since a transaction can contain more than one statement, it’s possible that nonrepeatable reads and phantom reads can occur within the context of the complete transaction. The READ COMMITTED isolation level is the default isolation level for Oracle.

SERIALIZABLE – Enforces serialization at the transaction level. This means that every statement within a transaction will get the same consistent view of the data as it existed at the start of the transaction.

READ ONLY – this level explicitly prohibits any write operations and provides an accurate view of all the data at the time the transaction began.

Because of their differing spans of control, these two isolation levels also react differently when they encounter a transaction that blocks their operation with an exclusive lock on a requested row. Once the lock has been released by the blocking transaction, an operation executing with the READ COMMITTED isolation level will simply retry the operation. Since this operation is concerned only with the state of data when the statement begins, this is a perfectly logical approach.

On the other hand, if the blocking transaction commits changes to the data, an operation executing with a SERIALIZABLE isolation level will return an error indicating that it cannot serialize operations. This error makes sense, because the blocking transaction will have changed the state of the data from the beginning of the SERIALIZABLE transaction, making it impossible to perform any more write operations on the changed rows. In this situation, an application programmer will have to add logic to his program to return to the start of the SERIALIZABLE transaction and begin it again.

Lost updates
The most common type of integrity problem occurs when two writers are both
changing the same piece of data, and one writer’s changes overwrite the other
writer’s changes. This is the problem that exclusive locks are designed to prevent.
Dirty reads
Occur when a database allows a transaction to read data that has been changed
by another transaction but hasn’t been committed yet. The changes made by the
transaction may be rolled back, so the data read may turn out to be incorrect.
Many databases allow dirty reads to avoid the contention caused by read locks.
Nonrepeatable reads
Occur as a result of changes made by another transaction. One transaction
makes a query based on a particular condition. After the data has been returned
to the first transaction, but before the first transaction is complete, another
transaction changes the data so that some of the previously retrieved data no
longer satisfies the selection condition. If the query were repeated in the same
transaction, it would return a different set of results, so any changes made on the
basis of the original results may no longer be valid. Data that was read once can
return different results if the data is read again later in the same transaction.
Phantom reads
Also occur as a result of changes made by another transaction. One transaction
makes a query based on a particular condition. After the data has been returned
to the first transaction, but before the first transaction is complete, another
transaction inserts into the database new rows that meet the selection criteria for
the first transaction. If the first SQL statement in a transaction returned the
number of rows that initially satisfied the selection criteria, and then performed
an action on the rows that satisfied the selection criteria later in the transaction,
the number of rows affected would be different from the initial number of rows
indicated, based on the inclusion of new phantom rows.

Oracle Concurrency Features - three features are used by Oracle to implement multiversion read consistency.

Rollback segments – are structures in the Oracle database that store “undo” information for transactions in case of rollback. When a transaction starts changing some data in a block, it first writes the old image of the data to a rollback segment. A rollback segment is different from a redo log. The redo log is used to log all transactions to the database and recover the database in the event of a system failure, while the rollback segment provides rollback for transactions and read consistency. Blocks of rollback segments are cached in the System Global Area just like blocks of tables and indexes.

To preserve the integrity of the data in the database and enforce any type of serialization, it is critical to keep track of the order in which actions were performed. Oracle uses the System Change Number as an absolute determinant of the order of transactions. Oracle uses the SCN information in the redo log to reproduce transactions in the original and correct order when applying redo. Oracle also uses the SCN to determine when to clean up information in rollback segments that are no longer needed.

Since Oracle Database 10g, there is a pseudocolumn on each row that
contains the SCN, ORA_ROWSCN. You can use this to quickly determine
if a row has been updated since it was retrieved by comparing
the value read from this pseudocolumn at the start of a transaction
with the value read from this pseudocolumn at the end of the
transaction.

Locks in data blocks – A database must have a way of determining if a particular row is locked. Most
databases keep a list of locks in memory, which are managed by a lock manager
process. Oracle keeps locks with an area of the actual block in which the row is
stored. A data block is the smallest amount of data that can be read from disk for
an Oracle database, so whenever the row is requested, the block is read, and the
lock is available within the block.

Nonescalating row locks – other databases will sometimes escalate locks to a higher level of granularity within the database (na całą tabelę). With Oracle, the
lock indicator is stored within the data block itself, so there is no increase in overhead for a lock manager when the number of locks increases. Consequently, there is never any need for Oracle to escalate a lock.

A lock manager called the Distributed Lock Manager (DLM) has historically been used with Oracle Parallel Server to track locks across multiple instances of Oracle. This is a completely different and separate locking scheme that doesn’t affect the way Oracle handles row locks. The DLM technology used in Oracle Parallel Server was improved and integrated into a core product in Oracle9i, Real Application Clusters. (213)

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.