Storage Management in Oracle -1
In this article we will learn about Storage Management .
A Database is a collection of Logical and Physical Phases.In Logical phase we have DB objects.
A Tablespace is a logical storage container in Oracle that comes at the top of the storage hierarchy and is made up of one or more data files. These files might be cooked files in a file system, raw partitions, ASM-managed database files, or files on a clustered file system.
- In Oracle Database Space demanding objects are ‘Segments’.
- A Segment only be created under One Tablespace
- A Tablespace can support one or more segments.
- A segment is made up of Extents.
- An Extent is made up of one or more Contiguous Oracle Blocks(COB).
- A Segment can have his extents in one or more Datafiles which belongs to same tablespace.
- When all the extents filled up with data tehn oracle on its own offers another extent .It is known as Dynamic Space Allocation (DSA)
- DSA is good as the show continuous
- To much DSA could be back in performance as that could be extent scattered all over the place.
Being production database DBA, We must keep eye on dynamic tables so that the dependency on DSA can be minimize.
BLOCKS:
A block is the smallest unit of space allocation in Oracle. Blocks are where your rows of data, or index entries, or temporary sort results will be stored.
A block is what Oracle generally reads from and writes to disk. Blocks in Oracle are generally one of four common sizes: 2KB, 4KB, 8KB, or 16KB (although 32KB is also permissible in some cases; there are restrictions in place as to the maximum size by operating system).
The Operating System Block size is the minimum unit of operation (read/write) by the OS and is a property of the OS file system. While creating an Oracle database we have to choose the “Data Base Block Size” as a multiple of the Operating System Block size. The minimum unit of operation (read/write) by the Oracle database would be this ―Oracle block‖, and not the OS block.
Note: The default block size for a database does not have to be a power of two. Powers of two are just a convention commonly used. You can, in fact, create a database with a 5KB, 7KB, or nKB block size, where n is between 2KB and 32KB. I do not advise making use of this fact in real time; though stick with 2KB, 4KB, 8KB, or 16KB as your block size. Once set, the ―Data Base Block Size‖ cannot be changed during the life of the database. The relationship between segments, extents, and blocks
A segment is made up of one or more extents, and an extent is a contiguous allocation of blocks. Starting with Oracle9i Release 1, a database may have up to six different block sizes in it
Note: This feature of multiple block sizes was introduced for the purpose of making transportable tablespaces usable in more cases. The ability to transport a tablespace allows a DBA to move or copy the already formatted data files from one database and attach them to another. For example, to immediately copy all of the tables and indexes from an Online Transaction Processing (OLTP) database to a Data Warehouse (DW).
However, in many cases, the OLTP database might be using a small block size, such as 2KB or 4KB, whereas the DW would be using a much larger one (8KB or 16KB). Without support for multiple block sizes in a single database, you would not be able to transport this information. Tablespaces with multiple block sizes should be used to facilitate transporting tablespaces and are not generally used for anything else.
There will be the database default block size, which is the size that was specified in the initialization file during the CREATE DATABASE command. The SYSTEM tablespace will have this default block size always, but you can then create other tablespaces with nondefault block sizes of 2KB, 4KB, 8KB, 16KB and, depending on the operating system, 32KB.
The total number of block sizes is six if and only if you specified a nonstandard block size (not a power of two) during database creation. Hence, for all practical purposes, a database will have at most five block sizes: the default size and then four other non-default sizes.
Any given tablespace will have a consistent block size, meaning that every block in that tablespace will be the same size. A multi-segment object, such as a table with a LOB column, may have each segment in a tablespace with a different block size, but any given segment (which is contained in a tablespace) will consist of blocks of exactly the same size. All blocks, regardless of their size, have the same general format, which looks something like below figure
Header :
Header contains the general information about the data i.e. block address, and type of segments (table, index etc). It also contains the information about table and the actual row (address) which that holds the data.
The next two block components, table directory and row directory, and are found on the most common types of database blocks, those of HEAP organized tables, but suffice it to say that most tables are of this type.
Table-Directory
The table directory, if present, contains information about the tables that store rows in this block (data from more than one table may be stored on the same block).
Row-Directory
The row directory contains information describing the rows that are to be found on the block. This is an array of pointers to where the rows are to be found in the data portion of the block. These three pieces of the block are collectively known as the block overhead, which is space used on the block that is not available for your data, but rather is used by Oracle to manage the block itself. The remaining two pieces of the block are straightforward: there will possibly be free space on a block, and then there will generally be used space that is currently storing data.
Free Space
Space allocated for future update/insert operations. Generally affected by values of PCTFREE and PCTUSED parameters.
Data
Actual row data.
FREELIST, PCTFREE and PCTUSED
While creating / altering any table/index, Oracle used two storage parameters for space control.
PCTFREE – The percentage of space reserved for future update of existing data.
PCTUSED – The percentage of minimum space used for insertion of new row data. This value determines when the block gets back into the FREELISTS structure.
FREELIST – Structure where Oracle maintains a list of all free available blocks.
Oracle will first search for a free block in the FREELIST and then the data is inserted into that block. The availability of the block in the FREELIST is decided by the PCTFREE value. Initially an empty block will be listed in the FREELIST structure, and it will continue to remain there until the free space reaches the PCTFREE value. When the free space reach the PCTFREE value the block is removed from the FREELIST, and it is re-listed in the FREELIST table when the volume of data in the block comes below the PCTUSED value. Oracle use FREELIST to increase the performance. So for every insert operation, oracle needs to search for the free blocks only from the FREELIST structure instead of searching all blocks
EXTENTS :
An extent is a logically contiguous allocation of space in a file. (files themselves, in general, are not contiguous on disk; otherwise, we would never need a disk defragmentation tool). Also, with disk technologies such as Redundant Array of Independent Disks (RAID), we might find a single file is not only not contiguous on a single disk, but also spans many physical disks. Each extent in turn is made up of OBs which are contiguous meaning that if we have lot of free space in the Tablespace‘s datafile, still if it is not contiguous enough to fit our extent, the operation fails.
At the time of Segment creation, we define how many extents and also what should be the size of each extent. For e.g. if we have only 1 extent for our Table-Segment “EMP”, which is only 20KB, may be it gets filled up after inserting some records. Then Oracle automatically allocates another extent for this Segment which is known as “Dynamic Space Allocation” Oracle strongly doesn’t recommend this “DSA”, as more and more extents we have for this Segment, we’ll be spending longer times for selecting data from this Table-Segment, since it has so many extents which may not be contiguous and even spread across the.
This is the reason for time we spend on big-reports. To answer this problem, DBA should have clear understanding of at least High-Activity Segments, so that he can plan the Extents for that Segment properly. For an object to grow beyond its initial extent, it will request another extent be allocated to it. This second extent will not necessarily be located right next to the first extent on disk it may very well not even be allocated in same file as the first extent. The second extent may be located very far away from the first extent, but the space within an extent is always logically contiguous in a file. Extents vary in size from one Oracle data block to 2GB.
SEGMENTS :
Segments are the major organizational structure within a tablespace. Segments are simply our database objects that consume storage – objects such as tables, indexes, rollback segments, and so on. When we create a table, we create a table segment. When we create a partitioned table, we create a segment per partition. When we create an index, we create an index segment, and so on. Every object that consumes storage is ultimately stored in a single segment. There are rollback segments, temporary segments, cluster segments, index segments, and so on.
Note: It might be confusing to read ―Every object that consumes storage is ultimately stored in a single segment.‖ We will find many CREATE statements that create multi segment objects. The confusion lies in the fact that a single CREATE statement may ultimately create objects that consist of zero, one, or more segments.
For example, CREATE TABLE T (x int primary key, y clob) will create four segments: one for TABLE T, one for index that will be created in support of primary key, and two for CLOB (one segment for CLOB is LOB index and other segment is LOB data itself). On the other hand, CREATE TABLE T (x int, y date) cluster MY_CLUSTER, will create no segments. Oracle objects which consume (demand) space (bytes) are known as segments.
These segments are:
- Table Segments
- Index Segments
- Rollback Segments
- Undo Segments
- Temporary Segments
Segments are created in Tablespaces which are made up of Extents. Each segment should at least have 1 extent. There is no upper limitation for extents if we define as “unlimitedmaxextents”. These extents are created in that particular TS‘s ―datafile” (where ever we have created Segment). As we already understood, one Segment can have many extents and these extents need not be next-to-next (contiguous). But each Extent is made up of contiguous OBS.
Having more than 20 extents for a Segment performance comes down, since our Report may need to use all these extents to collect data. So DBA‘s primary responsibility is making sure no Segments are consuming more than 20 extents. So at creation of Segment we need to provide above ―Storage Parameters‖ detail to Oracle and depending on space availability, Oracle may either create this Segment or may fail because insufficient space.
Continue in next article…..
Loading...
