Oracle architecture - August 28, 2008 at 17:00 PM by Amit Satpute
Explain different types of segment. Data segment, Index segment, Rollback
segment and temporary segment.
Answer
There are four types of segments used in Oracle databases:
- data segments
- index segments
- rollback segments
- temporary segments
Data Segments:
There is a single data segment to hold all the data of every non
clustered table in an oracle database. This data segment is created when you
create an object with the CREATE TABLE/SNAPSHOT/SNAPSHOT LOG command. Also, a
data segment is created for a cluster when a CREATE CLUSTER command is issued.
The storage parameters control the way that its data segment's extents are
allocated. These affect the efficiency of data retrieval and storage for the
data segment associated with the object.
Index Segments:
Every index in an Oracle database has a single index segment to hold
all of its data. Oracle creates the index segment for the index when you issue
the CREATE INDEX command. Setting the storage parameters directly affects the
efficiency of data retrieval and storage.
Rollback Segments
Rollbacks are required when the transactions that affect the database
need to be undone. Rollbacks are also needed during the time of system
failures. The way the roll-backed data is saved in rollback segment, the data
can also be redone which is held in redo segment.
A rollback segment is a portion of the database that records the actions of
transactions if the transaction should be rolled back. Each database contains
one or more rollback segments. Rollback segments are used to provide read
consistency, to rollback transactions, and to recover the database.
Types of rollbacks:
- statement level rollback
- rollback to a savepoint
- rollback of a transaction due to user request
- rollback of a transaction due to abnormal process termination
- rollback of all outstanding transactions when an instance terminates
abnormally
- rollback of incomplete transactions during recovery.
Temporary Segments:
The SELECT statements need a temporary storage. When queries are
fired, oracle needs area to do sorting and other operation due to which
temporary storages are useful.
The commands that may use temporary storage when used with SELECT are:
GROUP BY, UNION, DISTINCT, etc.
Explain SGA memory structures: Shared Pool, Database buffer Cache, Redo log
Cache, Large Pool Java Pool.
Answer
SGA (System Global Area) is a dynamic memory area of an Oracle Server.
In SGA,the allocation is done in granuels. The size of the SGA is dependent on
SGA_MAX_SIZE parameter.
The memory structures contained by SGA are:-
Shared Pool -
this memory structure is divided into two sub-structures which are Library
Cache and Data Dictionary Cache for storing recently used PL/SQL statements and
the recent data definitions. The maximum size of the Shared Pool depends on the
SHARED_POOL_SIZE parameter.
Database Buffer Cache –
This memory structure improves the performance while fetching or
updating the recently used data as it stores the recently used datafiles. The
size of this block is decided by DB_BLOCK_SIZE.
Redo Log Buffer –
This memory structure is used to store all the changes made to the database and
it's primarily used for the data recovery purposes. The size of this block is
decided by LOG_BUFFER.
Java Pool –
This memory structure is used when Java is installed on the Oracle
server. Size that can be used is stored in parameter named JAVA_POOL_SIZE.
Large Pool –
This memory structure is used to reduce the burden of the Shared Pool, as the
Session memory for the Shared Server, as the temporary storage for the I/O and
for the backup and restore operations or RMAN. Parameter that stores the
maximum size is LARGE_POOL_SIZE.
What is SQL Loader? Explain the files used by SQL Loader to load file. i.e
Loader control file, Input datafile, Log File, Bad File, Discard file.
Answer
SQL*Loader is a bulk loader utility used for moving data from external
files into the Oracle database. SQL*Loader supports various load formats,
selective loading, and multi-table loads.
When a control file is fed to an SQL*Loader, it writes messages to the log file,
bad rows to the bad file and discarded rows to the discard file.
Control file
The SQL*Loader control file contains information that describes how
the data will be loaded. It contains the table name, column datatypes, field
delimiters, etc.
controlfile.sql should be used to generate an accurate control file for a given
table.
Log File
The log file contains information about the SQL*loader execution. It
should be viewed after each SQL*Loader job is complete.
Explain the methods provided by SQL Loader.
Answer
Conventional Path Load
Direct Path Load
|