A High Level Comparison Between Oracle and SQL Server – Part III
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:
The query below will show the information related to the current sessions:
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:
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.