Dynamic Views
Apart from the data dictionary views, Oracle
has a number of views called Dynamic Performance Views that provide
insight into the memory structures of the instance and the contents of the
control file. Dynamic performance views are different from the data dictionary
views in the sense that they do not sit on top of any underlying static tables;
rather they return real-time information from the server’s memory. This data
starts accumulating as the instance starts up, builds up over the instance’s
lifetime and is lost when the instance is shut down or restarted. Another
difference is that the DBA can access the data dictionary views only when the
database is open and online, whereas dynamic performance views are accessible
as soon as the instance is up and running. Some dynamic performance views are
only accessible when the database is mounted. These views are often called V$
views because their names start with V$. As an example, the following query
shows the initialisation parameters used by Oracle instance:
SELECT
NAME, DESCRIPTION, VALUE FROM v$system_parameter
The query below will show the information
related to the current sessions:
SELECT
USERNAME, COMMAND, SCHEMANAME, OSUSER, MACHINE FROM V$SESSION
From SQL Server 2005, Dynamic Management
Views or DMVs also expose a large number of information from the instance’s
memory. Like Oracle dynamic performance views, DMVs also do not represent any
underlying tables and repopulated every time the instance is restarted. DMVs
belong to the sys schema and starts with the prefix “dm_”. In the code below,
the current sessions against the SQL Server instance is queried:
SELECT
* FROM sys.dm_exec_requests
Logical and Physical Storage Structures
Whether it is a database in SQL Server or a
tablespace in Oracle, the physical form it exists under the host operating
system is a set of files. Objects like tables, views or indexes are logical
structures within the database that only the database engine can recognise. These
logical objects are arranged in a proprietary format in the physical files
which can also be read by the database storage engine alone. Needless to say,
the relationship between the logical objects and the physical file contents is
also maintained by the database engine.
SQL Server has not changed much from its
early versions as far as pages and extents are concerned. A database page
is the smallest unit of storage allocation and I/O in SQL Server and it is 8 KB
in size. Within the page, the header section takes up 96 bytes. The page header
contains relevant information about the page such as its type, the amount of
free space available etc. After the header, data rows are stored in the page,
followed by any free space available. At the very end of the page is the row
offset section. Each data row in the page has an entry in the row offset
section. The offset shows how far the first byte of the row is from the start
of the page.
There are different types of pages in SQL
Server and each has a specific function. Ordinary data rows are contained in
data pages. Index entries are stored in index pages. Special types of pages
called image or text are used to store special types of data such as xml,
image, text ,varchar(max) or nvarchar(max). There are also other different
kinds of pages such as GAM, SGAM, IAM, PFS etc. We will see each one of them
shortly.
While pages are the smallest unit of I/O and
storage, space is allocated in terms of extents. A SQL Server database
extent is fixed in size: it contains eight physically contiguous pages,
resulting in a 64 KB structure. Each MB of database space is thus made up of 16
extents.
Now extents can further divided into two
types: uniform or mixed. In an uniform extent, all eight pages
come from the same object such as a table or index. In a mixed extent, pages
can belong to different objects. When a table or index is created, it is
generally allocated pages from mixed extents. As the table or index grows with
data such that it contains enough rows to span across eight pages or more, it
switches to uniform extent allocation.
SQL Server allocates extents to objects and
keeps track of the free ones through specialised database pages. The pages are
not like your ordinary data pages with rows, they act as bitmaps where each bit
represents an extent.
Global Allocation Map or
GAM pages keep track of which extents are free and which ones are not.
Each GAM page can keep track of 64,000 extents or 4 GB of data. Within the GAM,
if a bit has a value of 0, it means the corresponding extent is in use and has
been allocated to an object. A value of 1 means the extent is free. When you
thinking about it, the concept is similar to how Oracle manages it extents
locally (discussed shortly).
The Shared Global Allocation Map or SGAM
pages have the same functionality as the GAM pages, except it works for mixed
extents. The bitmap is used in a reverse direction: if a bit in GAM page is set
to 1, it is set to 0 in SGAM – meaning either the extent free or not in use.
The concept of bitmap is also used in the Page
Free Space or PFS pages. This page records if an individual page has
been allocated to an object and if so, how much free space is available there.
The bitmap values show if the page is 50 percent, 85 percent, 95 percent full
or above 95 percent full. SQL Server uses this information when it has to
allocate space for a data row.
Finally, the Index Allocation Map or IAM
pages are used by SQL Server to allocate and keep track of the extents used by
indexes. When a clustered index is defined on a table, the actual data rows are
sorted in the order of the indexed field and stored in data pages in that
order. The index itself is implemented through index pages. The data pages are
connected to each other using a doubly linked list and the whole index is
arranged as a B-Tree.
When there is no clustered index defined on
the table, it is known as a heap and data rows in a heap are not
arranged in any particular order. The data pages belonging to heap tables are
also not arranged in any order and they are not connected via linked lists. The
way SQL Server finds the table pages is by scanning the IAM pages. Essentially,
SQL Server starts by scanning the first IAM page for the heap and finds all the
extents defined within that page. It then moves to the next IAM page for the
heap and repeats this process. The process is repeated until all IAM pages
within the database file(s) are scanned.
A SQL Server data file has a predefined
sequence of all these specialised pages. The schematic diagram of the order is
shown below:
At the very beginning of the data file, the
header page is located, followed by the PFS page. This is followed by the GAM
page, SGAM page and the IAM page. Using this architecture, SQL Server knows
where to look when it needs to search the data file.
For Oracle, the highest form of logical
structure within the database is the tablespace. Within a tablespace there are
logical structures present, called segments. A segment is any logical
object that requires storage space. There can be a number of segment types within
the tablespace such as tables, indexes, undo etc. The relationship between a
tablespace and a segment is one-to-many: a tablespace will have a number of
segments; a particular segment can belong to only one tablespace.
Now the way storage space is allocated to
segments is through what is known as extents. The extent in Oracle is
very similar in concept with extents in Microsoft SQL Server. When a segment is
created in a tablespace, it is allocated at least one extent. When more space
is required by the segment as it grows, more extents are allocated. The
relationship between segments and extents is also one-to-many: an extent can
belong to only one segment.
The relationship between logical objects and
physical structures in Oracle is also implemented through extents. Extents are
the allocation units in an Oracle database files. And here as well, the
relationship between a database file and an extent is one-to-many: a database
file will contain many extents and any one particular extent will belong to one
particular file, it will not span across more than one file.
So we have the following picture for Oracle
so far: a database will contain tablespaces and each tablespace will have one
or more data files for its object storage. Each tablespace will also contain
multiple segments and these segments will contain one or more extents in turn.
The extents are the building blocks of the data files: every extent will belong
to a particular segment and reside in a particular data file. The relationship
between logical segments and physical data files is thus many-to-many: a
segment can have multiple extents, each extent belonging to one particular data
file.
Now if we go further down the level of
granularity, data in extents is stored in contiguous data blocks. A data
block is the minimum unit of I/O and storage in Oracle and it is similar to the
concept of Microsoft SQL Server pages. And if you are wondering if the
relationship between an extent and a data block is one-to-many, you are right.
One of the interesting differences between a
SQL Server data pages and Oracle data block is in their sizing. In SQL Server,
the size of the page has remained fixed throughout its versions: it is 8 KB and
cannot be changed. With Oracle, there is a default block size specified for
tablespaces when the database is created and you can change it at that time.
You can also create a tablespace with a different block size than the rest of
the database. However, once the tablespace is created, you cannot change its
block size.
Another storage concept that makes Oracle
different than SQL Server is how extents are managed. As extents are allocated
to segments, their management can be done either through the data dictionary or
locally within the tablepace.
Historically, Oracle used to use dictionary
managed extent allocation. The data dictionary is the central repository of
system metadata in Oracle, much like SQL Server’s system tables. When a
tablespace uses dictionary managed extents, every creation, allocation,
de-allocation and dropping of extents would be logged in the system tables of
the data dictionary, resulting in I/O against the tables.
This mechanism is no longer encouraged by
Oracle as it would prefer DBAs to use the more efficient locally managed
extent allocation. With this scheme of things, the list of free extents in
each tablespace is maintained by a bitmap in the tablespace itself. This is
very much like SQL Server’s GAM pages. Each bit in the bitmap represents a
database block or extent (if the tablespace is using uniform sized, automatic
extent management). And as extents are allocated or de-allocated, the bitmap is
also updated.
Dictionary managed extent allocation is still
there for backward compatibility and the DBA can choose either one of these
methods when creating a tablespace.
We will not go into the storage structure of
indexes but will wrap up the Oracle side by pointing out one final difference
with SQL Server. As Oracle is available in a number of platforms, its database
will make use of the file system of the underlying operating system: in Windows
for example, Oracle can be installed on volumes managed by the NTFS. Oracle
also offers a mechanism where it can manage the physical storage by itself,
independent of any operating system. This is implemented through an advanced
mechanism called Advanced Storage Management or ASM. Microsoft
SQL Server does not have this autonomy: its storage engine makes use of Windows
file system as the product is only available for the Windows platform.