27-01-2006

Table space is divided into two categories:

1. Locally managed

2. Dictionary managed

Locally managed concept was introduced in Oracle 8i. At the time of introduction it was optional. Before Oracle 8i there was no concept of Locally managed. However over the years scenario has changed. From Oracle 9i onwards Locally managed has become default where as Dictionary managed has become optional.

What is Locally managed and what is Dictionary managed?

By default initial extents are 1. However roll back segment by default has minimum 2 extents.

Definition: When the segment is created i.e. allocation i.e. which system is allocated to which table space, if this information is managed by Data Dictionary then it is called Dictionary management.

Another way of maintaining this allocation information is that to specify it in header of datafile. This is called Locally Managed.

If your system table space is locally managed then all your table spaces in database has to be locally managed.

If your system table space is dictionary managed the n your table space has two options:

1. Either to go Dictionary managed way.

2. Or to go with Locally managed way.

It is possible to change table space from dictionary managed to locally managed and vice versa provided your table space management is Dictionary managed.

Both individual data files and table space can be:

1. Off line.

2. On line.

3. Read only.

Off line means you cannot access any data. It is equivalent that no table space is present

On line means we can read as well write.

Read Only means that we can only read but cannot write.

#: Schema is defined as set of objects owned by an user account

It is possible for an user schema to span over multiple table spaces.

I: What is the difference between user account and schema?

A: User account is an account in database with certain sets of privileges according to which he can perform certain operations.

Schema is the set of objects owned by the user.

EG: Creating an account in Bank is similar to creating or having an account in Oracle. On the other hand facilities which you get after creating an account in Bank like check book, ATM, different add ons, Loans etc are similar to the concept of objects in database.

*: Object is any thing created by user.

Any object in database is identified by schema name and object name.

If user u1 creates a table say T and if there is another user say u2 then even he can create a table of same name because Table created by user u1 is identified as u1.T where as table created by user u2 is identified as u2.T. However if either user u1 or u2 further tries to create a same table of same name then it won't be allowed to do so because then there will be a clash i.e one table of same name already exits.

#: There is no concept of transfer of ownership in Oracle.

Memory Architecture:

Two Important concepts in Memory architecture are:

1. SGA (Shared Global Area)

2. PGA (Privileged Global Area)

SGA: All the users working on Oracle share the same memory and hence it is called as SGA.

Different portions of memory architecture in Oracle are as follows:

1. Data buffer pool (buffer cache)

2. Redo log buffer

3. Large buffer

4. Fixed SGA

5. Data dictionary cache

6. Shared pool area

7. Library cache ( Shared SQL area or Cursor area)

Let us study each portion one by one:

Buffer Cache ( Data buffer pool): Buffer cache is used to store the data blocks brought from data files.

The buffer pool is divided internally to blocks i.e. it formats itself. The size of block of memory has to be similar to size of data block. Before Oracle 10g there used to be similar size of data blocks through the database, but from Oracle 10g onwards you can have data blocks of 5 standards sizes, thus Oracle memory can be formatted in 5 sizes.

Buffer cache can be divided further to following categories:

1. Dirty Buffer.

2. Free Buffer.

3. Pinned Buffer.

Dirty buffer: It is that portion of Buffer cache in memory structure of Oracle which indicates that it is ready to accept the data.

Free buffer: When data is inserted into Dirty buffer, then processing starts over the data. Thus blocks in which processing of data takes place is called called free buffer. The bottom line is that “ On insertion of data in dirty buffer it becomes free buffer as long as processing is taking place.”

Pinned buffer: After the data is stored and if in future we want to edit it then the block in buffer cache in which modification takes place is called as Pinned buffer.

Thus same block in Buffer cache can act as three different buffers.

Oracle uses LRU (Least Recently Used) algorithm for storing data in memory.

Explanation of LRU Algorithm: ( Hypothetical Concept )

Consider a pulley. Over which there is a rope. Imagine that data is placed on the rope. Rope is infinite and is following continuously. It is obvious that data is also falling, by falling it means that it is being written on hard disk. Thus as data gets older it shifts towards least recently used end and if a new data comes in memory or an older data on memory is again edited then it shifts towards Most Recently used end.

This is an hypothetical example. It doesn't exist in reality.

*: Oracle is designed keeping commit in mind and not rollback.

Introduction to Oracle Memory Structures

Oracle uses memory to store information such as the following:

• Program code

• Information about a 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 that is shared and communicated among Oracle processes (for example, locking information)

• Cached data that is also permanently stored on peripheral memory (for example, data blocks and redo log entries)

The basic memory structures associated with Oracle include:

• System Global Area (SGA), which is shared by all server and background processes and holds the following:

  • Database buffer cache

  • Redo log buffer

  • Shared pool

  • Large pool (if configured)

• Program Global Areas (PGA), which is private to each server and background process; there is one PGA for each process. The PGA holds the following:

  • Stack areas

  • Data areas

Figure 7-1 Oracle Memory Structures

System Global Area (SGA) Overview

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

• Database buffer cache

• Redo log buffer

• Shared pool

• Java pool

• Large pool (optional)

• Data dictionary cache

• Other miscellaneous information

Part of the SGA contains general information about the state of the database and the instance, which the background processes need to access; this is called the fixed SGA. No user data is stored here. The SGA also includes information communicated between processes, such as locking information.

Database Buffer Cache

The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.

The database buffer cache and the shared SQL cache are logically segmented into multiple sets. This organization into multiple sets reduces contention on multiprocessor systems.

Organization of the Database Buffer Cache

The buffers in the cache are organized in two lists: the write list and the least recently used (LRU) list. The write list holds dirty buffers, which contain data that has been modified but has not yet been written to disk. The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use. Pinned buffers are currently being accessed.

When an Oracle process accesses a buffer, the process moves the buffer to the most recently used (MRU) end of the LRU list. As more buffers are continually moved to the MRU end of the LRU list, dirty buffers age toward the LRU end of the LRU list.

The first time an Oracle user process requires a particular piece of data, it searches for the data in the database buffer cache. If the process finds the data already in the cache (a cache hit), it can read the data directly from memory. If the process cannot find the data in the cache (a cache miss), it must copy the data block from a datafile on disk into a buffer in the cache before accessing the data. Accessing data through a cache hit is faster than data access through a cache miss.

Before reading a data block into the cache, the process must first find a free buffer. The process searches the LRU list, starting at the least recently used end of the list. The process searches either until it finds a free buffer or until it has searched the threshold limit of buffers.

If the user process finds a dirty buffer as it searches the LRU list, it moves that buffer to the write list and continues to search. When the process finds a free buffer, it reads the data block from disk into the buffer and moves the buffer to the MRU end of the LRU list.

If an Oracle user process searches the threshold limit of buffers without finding a free buffer, the process stops searching the LRU list and signals the DBW0 background process to write some of the dirty buffers to disk.

The LRU Algorithm and Full Table Scans

When the user process is performing a full table scan, it reads the blocks of the table into buffers and puts them on the LRU end (instead of the MRU end) of the LRU list. This is because a fully scanned table usually is needed only briefly, so the blocks should be moved out quickly to leave more frequently used blocks in the cache.

You can control this default behavior of blocks involved in table scans on a table-by-table basis. To specify that blocks of the table are to be placed at the MRU end of the list during a full table scan, use the CACHE clause when creating or altering a table or cluster. You can specify this behavior for small lookup tables or large static historical tables to avoid I/O on subsequent accesses of the table.