Oracle DB

Physical storage structure

• One characteristic of an RDBMS is the independence of logical data structures such as tables, views, and indexes from physical storage structures. Because physical and logical structures are separate, you can manage physical storage of data without affecting access to logical structures. For example, renaming a database file does not rename the tables stored in it.
  An Oracle database is a set of files that store Oracle data in persistent disk storage. This section discusses the database files generated when you issue a CREATE DATABASE statement:
  
• Data files and temp files
  A data file is a physical file on disk that was created by Oracle Database and contains data structures such as tables and indexes. A temp file is a data file that belongs to a temporary tablespace. The data is written to these files in an Oracle proprietary format that cannot be read by other programs.
  
• Control files
  A control file is a root file that tracks the physical components of the database.
  
• Online redo log files
  The online redo log is a set of files containing records of changes made to data.
  

A database instance is a set of memory structures that manage database files. Figure 11-1 shows the relationship between the instance and the files that it manages.

Figure 11-1 Database Instance and Database Files

Description of "Figure 11-1 Database Instance and Database Files"

A database employs a combination of the storage mechanisms. For example, a database could store the control files and online redo log files in a traditional file system, some user data files on raw partitions, the remaining data files in Oracle ASM, and archived the redo log files to a cluster file system. 

Permanent and Temporary Data Files

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.
A temporary tablespace contains schema objects only for the duration of a session. Locally managed temporary tablespaces have temporary files (temp files), which are special files designed to store data in hash, sort, and other operations. Temp files also store result set data when insufficient space exists in memory.

The data file header contains metadata about the data file such as its size and checkpoint SCN. Each header contains an absolute file number and a relative file number. The absolute file number uniquely identifies the data file within the database. The relative file number uniquely identifies a data file within a tablespace.

The control file serves the following purposes:

• It contains information about data files, online redo log files, and so on that are required to open the database.
• It contains metadata that must be accessible when the database is not open.
   For example, the control file contains information required to recover the database, including checkpoints. A checkpoint indicates the SCN in the redo stream where instance recovery would be required to begin (see "Overview of Instance Recovery"). Every committed change before a checkpoint SCN is guaranteed to be saved on disk in the data files. At least every three seconds the checkpoint process records information in the control file about the checkpoint position in the online redo log.

Overview of the Online Redo Log

The most crucial structure for recovery is the online redo log, which consists of two or more preallocated files that store changes to the database as they occur. The online redo log records changes to the data files.

Use of the Online Redo Log

The database maintains online redo log files to protect against data loss. Specifically, after an instance failure the online redo log files enable Oracle Database to recover committed data not yet written to the data files.
Oracle Database writes every transaction synchronously to the redo log buffer, which is then written to the online redo logs. The contents of the log include uncommitted transactions, undo data, and schema and object management statements.
Oracle Database uses the online redo log only for recovery. However, administrators can query online redo log files through a SQL interface in the Oracle LogMiner utility (see "Oracle LogMiner"). Redo log files are a useful source of historical information about database activity.

Logical Storage Structures

This chapter describes the nature of and relationships among logical storage structures. These structures are created and recognized by Oracle Database and are not known to the operating system.

Logical and Physical Storage

• At the finest level of granularity, Oracle Database stores data in data blocks. One logical data block corresponds to a specific number of bytes of physical disk space, for example, 2 KB. Data blocks are the smallest units of storage that Oracle Database can use or allocate.

• An extent is a set of logically contiguous data blocks allocated for storing a specific type of information. 

• A segment is a set of extents allocated for a specific database object, such as a table. For example, the data for the employees table is stored in its own data segment, whereas each index for employees is stored in its own index segment. Every database object that consumes storage consists of a single segment.

• Each segment belongs to one and only one tablespace. Thus, all extents for a segment are stored in the same tablespace. Within a tablespace, a segment can include extents from multiple data files. 

Data Blocks and Operating System Blocks

At the physical level, database data is stored in disk files made up of operating system blocks. An operating system block is the minimum unit of data that the operating system can read or write. In contrast, an Oracle block is a logical storage structure whose size and structure are not known to the operating system.

Data Block Overhead

Oracle Database uses the block overhead to manage the block itself. The block overhead is not available to store user data.

Block header
This part contains general information about the block, including disk address and segment type. For blocks that are transaction-managed, the block header contains active and historical transaction information.

Figure 12-8 ROWID Format

An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:

• OOOOOO
  The data object number identifies the segment. A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster, have the same data object number.
  
• FFF
  The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).
  
• BBBBBB
  The data block number identifies the block that contains the row (block AAAABS in Example 12-1). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace.
  
• RRR
  The row number identifies the row in the block.
  
  

  Chained and Migrated Rows

  Oracle Database must manage rows that are too large to fit into a single block. The following situations are possible:
  
• The row is too large to fit into one data block when it is first inserted.
  In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW, a VARCHAR2(4000) column in a 2 KB block, or a row with a huge number of columns. Row chaining in these cases is unavoidable.
  
• A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.
  In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.
  
• A row has more than 255 columns.
  Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks.
   

Overview of Extents

An extent is a logical unit of database storage space allocation made up of contiguous data blocks. Data blocks in an extent are logically contiguous but can be physically spread out on disk because of RAID striping and file system implementations.

Allocation of Extents

By default, the database allocates an initial extent for a data segment when the segment is created. An extent is always contained in one data file.
Although no data has been added to the segment, the data blocks in the initial extent are reserved for this segment exclusively. The first data block of every segment contains a directory of the extents in the segment.

User Segment Creation

By default, the database uses deferred segment creation to update only database metadata when creating tables and indexes

Creation of a User Segment

Temporary Segments

When processing a query, Oracle Database often requires temporary workspace for intermediate stages of SQL statement execution. Typical operations that may require a temporary segment include sorting, hashing, and merging bitmaps. While creating an index, Oracle Database also places index segments into temporary segments and then converts them into permanent segments when the index is complete.

Undo Segments

Oracle Database maintains records of the actions of transactions, collectively known as undo data. Oracle Database uses undo to do the following:

• Roll back an active transaction
• Recover a terminated transaction
• Provide read consistency
• Perform some logical flashback operations

Oracle Database stores undo data inside the database rather than in external logs. Undo data is stored in blocks that are updated just like data blocks, with changes to these blocks generating redo. In this way, Oracle Database can efficiently access undo data without needing to read external logs.

Overview of Tablespaces

A tablespace is a logical storage container for segments. Segments are database objects, such as tables and indexes, that consume storage space. At the physical level, a tablespace stores data in one or more data files or temp files.

The SYSTEM Tablespace

The SYSTEM tablespace is a necessary administrative tablespace included with the database when it is created. Oracle Database uses SYSTEM to manage the database.
The SYSTEM tablespace includes the following information, all owned by the SYS user:

• The data dictionary
• Tables and views that contain administrative information about the database
• Compiled stored objects such as triggers, procedures, and packages

Introduction to the Oracle Database Instance

A database instance is a set of memory structures that manage database files. A database is a set of physical files on disk created by the CREATE DATABASE statement. The instance manages its associated data and serves the users of the database.
Every running Oracle database is associated with at least one Oracle database instance. Because an instance exists in memory and a database exists on disk, an instance can exist without a database and a database can exist without an instance.

Database Instance Structure

When an instance is started, Oracle Database allocates a memory area called the system global area (SGA) and starts one or more background processes. The SGA serves various purposes, including the following:

• Maintaining internal data structures that are accessed by many processes and threads concurrently
  
• Caching data blocks read from disk
  
• Buffering redo data before writing it to the online redo log files
  
• Storing SQL execution plans
  
  Main components of an Oracle database instance.
  

 Database Instance

Database Instance Configurations

Introduction to Oracle Database Memory Structures

When an instance is started, Oracle Database allocates a memory area and starts background processes. The memory area stores information such as the following:

• Program code
  
• Information about each connected session, even if it is not currently active
  
• Information needed during program execution, for example, the current state of a query from which rows are being fetched
  
• Information such as lock data that is shared and communicated among processes
  
• Cached data, such as data blocks and redo records, that also exists on disk
   

Basic Memory Structures

The basic memory structures associated with Oracle Database include:

• System global area (SGA)
  The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
  
• Program global area (PGA)
  A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.  When a server process executes SQL or PL/SQL code, the process uses the private SQL area to store bind variable values, query execution state information, and query execution work areas. 
  A work area is a private allocation of PGA memory used for memory-intensive operations. For example, a sort operator uses the sort area to sort a set of rows. Similarly, a hash join
  
  One PGA exists for each server process and background process. The collection of individual PGAs is the total instance PGA, or instance PGA. Database initialization parameters set the size of the instance PGA, not individual PGAs.
  
• User Global Area (UGA)
  The UGA is memory associated with a user session, such as logon information, and other information required by a database session. Essentially, the UGA stores the session state.
• Software code areas
  Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.
   

Overview of the System Global Area-

The most important SGA components are the following:

• Database Buffer Cache
• Redo Log Buffer
• Shared Pool
• Large Pool
• Java Pool
• Streams Pool
• Fixed SGA

The database buffer cache, also called the buffer cache, is the memory area that stores copies of data blocks read from data files. A buffer is a main memory address in which the buffer manager temporarily caches a currently or recently used data block. All users concurrently connected to a database instance share access to the buffer cache.

Oracle Database uses the buffer cache to achieve the following goals:

• Optimize physical I/O
  The database updates data blocks in the cache and stores metadata about the changes in the redo log buffer. After a COMMIT, the database writes the redo buffers to disk but does not immediately write data blocks to disk. Instead, database writer (DBW) performs lazy writes in the background.
  
• Keep frequently accessed blocks in the buffer cache and write infrequently accessed blocks to disk
  
  

  Redo Log Buffer

  The redo log buffer is a circular buffer in the SGA that stores redo entries describing changes made to the database. Redo entries contain the information necessary to reconstruct, or redo, changes made to the database by DML or DDL operations. Database recovery applies redo entries to data files to reconstruct lost changes.

Shared Pool

The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool.

•  
• Database
• Database is a collection of data. And this data is stored in form of tables at logical level, and in the datafiles at the physical level. There are some other files as well like Redo log files, Control files, Initialization files which stores important information about the database.
• It is a collection of physical operating system files i.e physical structure of Oracle Database like Controlfile , redolog file and datafiles.
• An Oracle database consists of one or more logical storage units called tablespaces, which collectively store all of the database's data.
• Instance
• To view or update data stored in tables/datafiles, Oracle must start a set of background processes, and must allocate some memory to be used during database operation. The background processes and memory allocated by Oracle together make up an Instance.
• A set of Oracle processes and a SGA (allocation of memory)
• RAM allocated for Oracle + some programs runs in support
• Parameters necessary for initial database creation
• DB_BLOCK_SIZE 
• Sets the size of the Oracle database blocks stored in the
  database files and cached in the SGA. The range of values
  depends on the operating system, but it is typically powers
  of two in the range 2048 to 16384. Common values are 4096
  or 8192 for transaction processing systems and higher values
  for database warehouse systems
• DB_NAME
• Set the name of the database and the domain name of the
  database, respectively. Although they can be changed at a
  later time, it is highly advisable to set these correctly before
  the initial creation. The names chosen must be reflected in
  the SQL*Net configuration as well.

• Create Database Statement
• The first SQL statement that is executed after startup of the initial instance is the
  CREATE DATABASE statement. This creates the initial system tablespace, creates the
  initial redo logfiles, and sets certain database options.
• CONNECT SYS/ORACLE AS SYSDBA
  STARTUP NOMOUNT pfile=/u01/admin/init_create.ora
  CREATE DATABASE "dbname"
  DATAFILE '/u01/oradata/system01.dbf' size 200M
  LOGFILE '/u02/oradata/redo01.dbf' size 100M,
  '/u02/oradata/redo02.dbf' size 100M
  CHARACTER SET "WE8ISO8859P1"
  NATIONAL CHARACTER SET "UTF8"
  EXTENT MANAGEMENT LOCAL
  DEFAULT TEMPORARY TABLESPACE mytemp TEMPFILE ’temp.f’ SIZE 1000M MAXDATAFILES = 50
  MAXLOGFILES = 5; 
• TableSpaces
• After creating the initial database, several extra tablespaces must be created. All
  databases should have at least three tablespaces in addition to the system
  tablespace: 
• a temporary tablespace, which is used for things like sorting; 
• a rollback tablespace, which is used to store rollback segments or is designated as the automatic undo management segment; and at least 
• one tablespace for actual application use.
• Creating tables for good performance
• When installing applications, an initial step is to create all necessary tables and
  indexes. When you create a segment, such as a table, Oracle allocates space in the
  database for the data. If subsequent database operations cause the data volume to
  increase and exceed the space allocated, then Oracle extends the segment.
  When creating tables and indexes, note the following:
• Specify automatic segment-space management for tablespaces
  This allows Oracle to automatically manage segment space for best
  performance.
• Set storage options carefully (PCTFREE)
• INITRANS
• Each datablock has a number of transaction entries that are used for row locking purposes. 
• Index creation
• The most efficient way to create indexes is to create them after data has been loaded.
  By doing this, space management becomes much simpler, and no index
  maintenance takes place for each row inserted.
• Note: During index creation on tables that contain data, the data must be sorted. This
  sorting is done in the fastest possible way, if all available memory is used for
  sorting. Oracle recommends that you enable automatic sizing of SQL working areas
  by setting the PGA_AGGREGATE_TARGET initialization parameter 
• Memory for sorting data
• Oracle recommends that you enable automatic sizing of SQL working areas
  by setting the PGA_AGGREGATE_TARGET initialization parameter.
•  The memory for the SQL work area can also be controlled with the SORT_AREA_
  SIZE initialization parameter. Oracle does not recommend using the SORT_AREA_SIZE parameter unless the instance is configured with the shared server
  option.
• A system with 512 Mb memory runs an Oracle instance with a 100 Mb SGA, and the
  operating system uses 50 Mb. The memory available for sorting is 362 Mb, which
  equals 512 minus 50 minus 100. If the system has four CPUs running with four
  parallel slaves, then each of these will have 90 Mb available. 10 Mb is set aside for process overhead, so SORT_AREA_SIZE should be set to 80 Mb. This can be done
  either in the initialization file or for each session with the following statement:
  ALTER SESSION SET SORT_AREA_SIZE = 80000000;
•  
•  

Memory Configuration and Use : Proper sizing and effective use of the Oracle memory caches greatly improves database performance. This chapter explains how to allocate memory to Oracle
memory caches, and how to use those caches.

• Oracle stores information in memory caches and on disk. Memory access is much
  faster than disk access. Disk scans (physical I/O) take a significant amount of time,
  compared with memory access, typically in the order of 10 milliseconds. Physical
  I/O also increases the CPU resources required, because of the path length in device
  drivers and operating system event schedulers. For this reason, it is more efficient
  for data requests for frequently accessed objects to be satisfied solely by memory,
  rather than also requiring disk access.
  
  A performance goal is to reduce the physical I/O overhead as much as possible,
  either by making it more likely that the required data is in memory or by making
  the process of retrieving the required data more efficient.

Oracle Memory Caches: The main Oracle memory caches that affect performance are:

•  Shared pool - library cache and the dictionary
  cache. The library cache stores the executable (parsed or compiled) form of recently
  referenced SQL and PL/SQL code. The dictionary cache stores data referenced from
  the data dictionary.  A cache miss on the data dictionary cache or library cache is more expensive than a miss on the buffer cache.
•  Large pool -
•  Java pool
•  Buffer cache - Oracle uses the buffer cache to store blocks read from disk.
•  Log buffer
•  Process-private memory (for example, used for sorting, hashing, and so on)

The size of these memory caches is configurable using initialization configuration
parameters. The maximum amount of memory usable by the instance is determined at instance
startup by the initialization parameter SGA_MAX_SIZE.

Reduce paging
Paging occurs when an operating system transfers memory-resident pages to disk
solely to allow new pages to be loaded into memory


Redo Log Buffer
Server processes making changes to data blocks in the buffer cache generate redo
data into the log buffer. LGWR begins writing to copy entries from the redo log
buffer to the online redo log if any of the following are true:
n The log buffer becomes one third full.
n LGWR is posted by a server process performing a COMMIT or ROLLBACK.

n DBWR posts LGWR to do so.

What are the uses of Rollback Segment?

- Rollback segments undo changes when a transaction is rolled back
- They also ensure that transactions leave the uncommitted changes unnoticed.
- They can be used to recover the database to a consistent state in case of failures.
- ROLLBACK recovers the changes resulting from the SQL statements in the transaction.

Redo log buffer:
Records all changes made to the database data blocks
Primary purspose is recovery
changes recorded within are called redo entries


create tablespace users datafile  "/ora01/oracle/oradata/booktst_users_01.dbf" size 50m;


create table subjects (
  subject_id    number not null,
  subject_name  varchar2(30) not null,
  description   varchar2(4000)
) tablespace users;