The Entire Process Figure

1
The Entire Process Figure
2
The Parameter File
Also reffered to as pfile or init-file. Flat
text file that contains database initialization
parameters and other operational parameters. Can
be edited, but changes will not take effect
unless the database is restarted. Oracle
consults this file on start up to locate the
instance control file(s). It contains the
parameters required for the Oracle server to
allocate its SGA.
3
Data Files

• Oracle stores user data is structures called
  Tablespaces. These are logical logical structures
  represented on the operating system level as data
  files.
• Data Files have the following characteristics
• A data file can belong to only one tablespace.
• The Oracle server allocates the specified disk
  space for a tablespace plus a small overhead.
• The data file grows dynamically as its confined
  tablespace grows or manually via DDL command.

4
Control File
A binary file that contains vital database
information for the database to start and
operate. As the database strats in MOUNT mode,
it reads the control file to locate its data
files and redo logs. The control file is updated
continuously during database operation. The
control file provides database consistency
information that can be used in recovery
situations. Missing or corrupted control file
renders a malfunctioning database operation
(better put no operation)
5
Redo Log Files
Also referred to as Online Redo Log Files.
Online redo logs record all changes made in the
database buffer cache except when users specify
direct writes. Online redo logs are used to
recover committed data that has not been written
to data files yet ( that is really their only
function in life). They are created in groups
and are written to in a circular fashion. On
line redo logs can be archived.
6
Rollback Segments
Rollback segments contain the old values of
data that have been changed by uncommitted
transactions. In other words, before changes (
updates, inserts, deletes) are made to the
database, the server process saves the old value
into rollback segments. The rollback data (
before-image) is used to 1- Undo changes to the
database if needed. 2- Insure that other
transactions do not see uncommitted changes
(users see consistent data from a single point in
time). 3- Recover a failed instance.
7
How Oracle Processes A Query
Oracle processes a query in 3 stages 1- Parse
- In this stage the user process sends the
query submitted by the user to the server
process with a request to parse or compile the
query. - The server process checks the validity
of the query. - The server process uses the
shared pool within the SGA to compile the
statement. - The server process then returns
the status of the query (pass/fail). 2-
Execute - The server process during this phase
prepares to retrieve the data. 3- Fetch - The
queried rows are returned to the user during this
phase. Depending on the size of the result set
and the size of the memory used, one or more
fetches may be required.
8
Processing A DML Statement
For an UPDATE statement 1- From the data file,
the server process, reads the data and rollback
blocks ( if the data is not in the buffer
readily). 2- The server process copies the blocks
onto the buffer cache. 3- The server process
places locks on the data. 4- The server process
records the changes to be made in the rollback
value (before-image) and to data (new value) in
the redo log buffer. 5- The server process
records the before image to the rollback block
and updates the data block in the buffer cache.
The changed blocks in the buffer cache ( rollback
information and updated data block buffers) are
marked as dirty buffers. For a DELETE
statement The process is the same where the
content of the before-image in the rollback would
have the column values of the deleted row. For
an INSERT statement Only the inserted row
location information is needed to be stored in
rollback .
9
Processing A DML Statement
UPDATE emp SET salsal1.1 WHERE empno7369
Serverprocess
1Bring data from datafile if not in
buffer. 2Place data in buffer 3Lock data in
dictionary cache 4Both before_image and changed
value are recorded in log buffer 5 New
value is recorded in buffer, old value is
recorded in rollback block (both in the buffer
cache)
10
COMMIT Processing

• Oracle uses fast COMMIT process to guarantee
  that committed changes can be recovered in a case
  of a failure.
• When a transaction is committed, Oracle assigns a
  System Change Number (SCN) to the transaction.
  SCN is a sequential number used by Oracle as an
  internal time stamp without having to depend on
  the OS date and time.
• When a COMMIT is issued
• The server process places a commit record with
  the SCN in the redo log buffer.
• LGWR performs a contiguous write of all redo log
  buffer entries up to and including the commit
  record to to the redo log file.
• User is prompted with COMMIT complete message.
• Locks are released.
• Note Writing dirty blocks to data file is
  independent of the commit action. It can happen,
  for instance, if there are not free buffers for
  new blocks brought from disk.
• Note The absence of a commit record triggers a
  rollback in the event of a failure.

11
Advantage of Fast Commit

• Sequential writes to log files are faster than
  writing on data files that may not be
  contiguous or on different disks.
• Minimal information is needed to record changes
  are written to the log file.
• Writing changed data blocks directly to the
  data file required more time that writing only
  the changes. (low level write).
• On a commit a single write of all changes
  happens as appose to write on every change.

12
Concurrency and Consistency
In a single user database, the user can modify
the data in the database without concern for
other users modifying the data at the same time.
In a multi-user database, simultaneous
transactions can change the same data.
Transactions executing at the same time need to
produce consistent results, therefore, control of
data concurrency and data consistency are vital
in a multi-user database. Concurrency
Multiple users accessing the database at the same
time. Consistency Means that each user sees a
consistent view of the data including
visible changes made by the user's own
transactions and transactions of other users.
To achieve complete consistency transactions
may have to be isolated or serialized. Isolation
of transactions may effect application
performance where a transaction may not perform
an insert on a table being queried by another
transaction. Serialization on the other hand,
tries to make transaction look as if they are
executing serially rather than concurrently.
13
How Oracle Uses System Change Number (SCN)
Oracle uses Rollback Segments to insure
returning only committed data since RBS contain
old values of data that has been changed but not
been committed This is accomplished by use of
System Change Number. As a transaction enters
the execution phase. The System Change Number
(SCN) is determined 10023 in this example.
As data blocks are read on behalf of a query,
only blocks with the current SCN (1023 ) are
read. If blocks with less recent SCN are
requested by the query, they are constructed from
the rollback Segment (Remember RBS hold old
values ) hence all data blocks that are read on
behalf of a query at the time of query
execution are unchanged (RBS) or already
committed (database) data. Changes made to data
(or data blocks) by other transactions after the
query enters the execution phase are not observed
guaranteeing that consistent data is returned
for each query.
14
The Entire Process
Serverprocess
Userprocess
Database
15
Data Blocks, Extents, and Segments

• In this section
• We will examine the relation between blocks,
  extents, and segments.
• We will see how data is stored and transported
  between components of Oracle engine.
• We will examine manual and automatic extension
  on tablespaces

16
Relation between Blocks, Extents, and Segments
Oracle stores data in blocks. Blocks are the
smallest storage unit in the database. Oracle
data blocks are also called Oracle blocks,
logical blocks, and pages. One data block
corresponds to a specific number of bytes of disk
space on the operating system level.
17
Relation between Blocks, Extents, and Segments
The next level of logical database space is an
extent. An extent is a specific number of
contiguous data blocks allocated for storing a
specific type of information. Blocks needed to
create an extent must be contiguous, so Oracle
goes to work looking for contiguous blocks when
creating a new extent.
18
Relation between Blocks, Extents, and Segments
The level of logical database storage above an
extent is called a segment. A segment is a set
of extents. There four different types of
segments Data Segments Index Segments Rollback
Segments Temporary Segments Oracle allocates
space for segments in units of one extent. When
the existing extents of a segment are full,
Oracle allocates another extent for that segment.
Because extents are allocated as needed, the
extents of a segment may or may not be contiguous
on disk.
A segment and all its extents are stored in one
tablespace. Within a tablespace, a segment can
include extents from more than one file, that is,
the segment can span datafiles. However, each
extent can contain data from only one datafile.
19
Relation between Blocks, Extents, and Segments
Table space ABC is depicted in the above figure
as spanning over over 2 datafiles ABC1.DBF, and
ABC2.DBF. A segment and all its extents are
stored in one tablespace. Within a tablespace, a
segment can include extents from more than one
file, that is, the segment can span datafiles.
However, each extent can contain data from only
one datafile. In other words Extents A, B, C,
and D belong to a segment. Each extent contain
data from only one file, but the segment (
created from extents) can span one or more data
file.
20
Examine Extents and Segments
CREATE TABLE accounting (account_name
VARCHAR(2) NOT NULL, account_descrip
VARCHAR2(30), Account_NUM NUMBER(13)
) STORAGE ( INITIAL 300K NEXT 100K
MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 50) This table is going to be
created with INITIAL extent of 300K. Oracle will
look for contiguous blocks to create the first
extent. About 150 blocks for a system with block
size of 2K. The big question (Does a big block
system solve the problem)? The next extent is of
a 50K NEXT Each time one extent is created
MINEXTENTS No limit on how extents can be created
in this tablespace MAXEXTENTS Exponential
increments in 50 for third and subsequent
extents PCTINCREASE
21
Data Block
Header (Common and Variable) The header contains
general block information, such as the block
address and the type of segment (for example,
data, index, or rollback). Table Directory This
portion of the data block contains information
about the tables having rows in this block. Row
Directory This portion of the data block contains
information about the actual rows in the block
(including addresses for each row piece in the
row data area). Once the space has been
allocated in the row directory of a data block's
overhead, this space is not reclaimed when the
row is deleted. Therefore, a block that is
currently empty but had up to 50 rows at one time
continues to have 100 bytes allocated in the
header for the row directory. Oracle reuses this
space only when new rows are inserted in the
block.
Overhead The data block header, table directory,
and row directory are referred to collectively as
overhead. Some block overhead is fixed in size
the total block overhead size is variable. On
average, the fixed and variable portions of data
block overhead total 84 to 107 bytes. Row
Data This portion of the data block contains
table or index data. Rows can span blocks.
22
Data Block
Free Space Free space is allocated (reserved) for
insertion of new rows and for updates to rows
that require additional space (for example, when
a trailing null is updated to a non-null value).
Whether issued insertions actually occur in a
given data block is a function of current free
space in that data block and the value of the
space management parameter PCTFREE. PCTFREE and
PCTUSED parameter will be discussed in a later
slide. In data blocks allocated for the data
segment of a table or cluster, or for the index
segment of an index, free space can also hold
transaction entries. A transaction entry is
required in a block for each INSERT, UPDATE,
DELETE, and SELECT...FOR UPDATE statement
accessing one or more rows in the block. The
space required for transaction entries is
operating system dependent however, transaction
entries in most operating systems require
approximately 23 bytes.
Note Trailing Nulls of a column are not loaded
into the block when the row is inserted
23
PCTFREE and PCTUSE
CREATE TABLE accounting (account_name
VARCHAR(2) NOT NULL, account_descrip
VARCHAR2(30), Account_NUM NUMBER(13)
) STORAGE ( INITIAL 300K NEXT 100K
MINEXTENTS 1 MAXEXTENTS UNLIMITED
PCTINCREASE 50) PCTFREE 5 PCTUSED 75
PCTFREE Specifies the percentage of space in each
data block of the table. The value of PCTFREE
must be a value from 0 to 99. A value of 0 allows
the entire block to be filled by inserts of new
rows. The default value is 10. This value
reserves 10 of each block for updates to
existing rows. PCTUSED Specifies the minimum
percentage of used space that Oracle maintains
for each data block of the table for new inserted
rows. The sum of PCTFREE and PCTUSED must be
equal to or less than 100. You can use PCTFREE
and PCTUSED together to utilize space within a
table more efficiently.