Overview In typical data warehousing environment existence of huge partitioned tables is very common, gathering statistics on such tables is challenging tasks. For partitioned tables there are two typ...
Introduction Execution of a SQL statement in an Oracle Parallel Server environment is slightly different then in a single instance environment. In an OPS environment additional instructions are execut...
Use array processing Array processing refers to Oracle’s ability to insert or select more than one row in a single operation. For SQL, which deals with multiple rows of data, array processing usual...
Utilizing SQL tuning features Each release of Oracle introduces new and improved SQL performance features. In particular the cost-based optimizer contains improvements in each release—many of whic...
Relieving latch contention Operations that affect the contents of the SGA require that a process acquire a latch. A latch is similar to a lock, but instead of preventing two sessions from concurren...
Redo log configuration When a transaction is committed, a physical write to the redo log file must occur. The write must complete before the commit call returns control to the user. Hence, redo log...
Optimizing archiving Archived logs are copies of online redo logs. They can be used to recover a database to point of failure or to another point in time after a backup has been restored. Archive l...
Log file wait events Just as Oracle sessions must wait for database file I/O, they must also wait for log file I/O. Such waits occur whenever a COMMIT statement causes a write to the redo log. The ...
Naming Conventions A naming convention is used when Oracle creates managed database datafiles. The following table shows the naming conventions fo the database datafiles. (Note that these may differ f...
A tablespace is the primary level of logical storage in an Oracle database. All "hard" database objects such as tables, indexes, sequences, and clusters reside in tablespaces. An Oracle database can ...
Improving database writer performance Free buffer or write complete waits can indicate that the database writer process (DBWR) process is experiencing a disk I/O bottleneck. The DBWR is the only pr...
Fragmentation in a tablespace is thought to be a performance killer. This has been proven to be a myth. Fragmentation by itself does not cause performance problems in a multi-user Oracle database. Wha...
