Oracle Database 12c Administration Workshop 8 - 6 PDF

Slide 3: With Oracle Managed Files (OMF), you can specify operations in terms of database objects rather than file names. The Oracle Database server can manage free space within a tablespace with bitmaps. This is known as a “locally managed” tablespace. In addition, free space within segments locat...

Slide 3: With Oracle Managed Files (OMF), you can specify operations in terms of database objects rather than file names. The Oracle Database server can manage free space within a tablespace with bitmaps. This is known as a “locally managed” tablespace. In addition, free space within segments located in locally managed tablespaces can be managed using bitmaps. This is known as Automatic Segment Space Management. The bitmapped implementation eliminates much space-related tuning of tables, while providing improved performance during peak loads. Additionally, the Oracle Database server provides automatic extension of data files, so the files can grow automatically based on the amount of data in the files. When you create a database, proactive space monitoring is enabled by default. (This causes no performance impact.) The Oracle Database server monitors space utilization during normal space allocation and deallocation operations and alerts you if the free space availability falls below the predefined thresholds (which you can override). Advisors and wizards assist you with space reclamation. For capacity planning, the Oracle Database server provides space estimates based on table structure and number of rows and a growth trend report based on historical space utilization stored in the Automatic Workload Repository (AWR). Slide 6: Space management involves the management of free space at the block level. With Automatic Segment Space Management, each block is divided into four sections, named FS1 (between 0 and 25% of free space), FS2 (25% to 50% free), FS3 (50% to 75% free), and FS4 (75% to 100% free). Depending on the level of free space in the block, its status is automatically updated. That way, depending on the length of an inserted row, you can tell whether a particular block can be used to satisfy an insert operation. Note that a “full” status means that a block is no longer available for inserts. In the slide example, the block on the left is an FS3 block because it has between 50% and 75% free space. After some insert and update statements, PCTFREE is reached (the dashed line) and it is no longer possible to insert new rows in that block. The block is now considered as a “full” or FS1 block. The block is considered for insertion again, as soon as its free space level drops below the next section. In the preceding case, it gets status FS2 as soon as the free space is more than 25%. Slide 7: In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, the Oracle Database server stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of data type LONG or LONG RAW. Row chaining in these cases is unavoidable. However, in the second case, a row that originally fit into one data block is updated, so that the overall row length increases, and the block’s free space is already completely filled. In this case, the Oracle Database server migrates the data for the entire row to a new data block, assuming that the entire row can fit in a new block. The database preserves the original row piece of a migrated row to point to the new block containing the migrated row. The ROWID of a migrated row does not change. When a row is chained or migrated, input/output (I/O) performance associated with this row decreases because the Oracle Database server must scan more than one data block to retrieve the information for the row. The Segment Advisor finds the segments containing migrated rows that result from an UPDATE. Slide 8: Free-space bitmaps are one method used to track allocated sectors by some file systems. The simplest form of free-space bitmap is a bit array, i.e. a block of bits. In this example, a zero would indicate a free sector, while a one indicates a sector in use. The Oracle Database server automatically and transparently coalesces the free space of a data block when: An INSERT or UPDATE statement attempts to use a block with sufficient free space for a new row piece The free space is fragmented, so that the row piece cannot be inserted in a contiguous section of the block After coalescing, the amount of free space is identical to the amount before the operation, but the space is now contiguous. Oracle Database 12c: Administration Workshop 8 - 6 Answer: a Oracle Database 12c: Administration Workshop 8 - 7 Copyright © 2013, Oracle and/or its affiliates. All rights reserved. Quiz When a row is chained or migrated, the I/O performance associated with this row decreases because the Oracle Database server must scan more than one data block to retrieve the information for the row. a. True b. False Free space can be managed automatically inside database segments. The in-segment free or used space is tracked with bitmaps. To take advantage of this feature, specify Automatic Segment Space Management when you create a locally managed tablespace. Your specification then applies to all segments subsequently created in this tablespace. Automatic space management segments have a set of bitmap blocks (BMBs) describing the space utilization of the data blocks in that segment. BMBs are organized in a tree hierarchy. The root level of the hierarchy, which contains references to all intermediate BMBs, is stored in the segment header. The leaves of this hierarchy represent the space information for a set of contiguous data blocks that belong to the segment. The maximum number of levels inside this hierarchy is three. Slide 10: A partitioned table is a special table that is divided into segments, called partitions, that make it easier to manage and query your data. By dividing a large table into smaller partitions, you can improve query performance, and you can control costs by reducing the number of bytes read by a query. Table and cluster segments: Each nonclustered table has a data segment. All table data is stored in the extents of the table segment. For a partitioned table, each partition has a data segment. Each cluster has a data segment. The data of every table in the cluster is stored in the cluster’s data segment. Index segment: Each index has an index segment that stores all of its data. For a partitioned index, each partition has an index segment. Undo segment: Oracle Database maintains information to reverse changes made to the database. This information consists of records of the actions of transactions, collectively known as undo. Undo is stored in undo segments in an undo tablespace. Temporary segment: A temporary segment is created by the Oracle Database server when a SQL statement needs a temporary database area to complete execution. When the statement finishes execution, the extents in the temporary segment are returned to the system for future use. Slide 12: This slide shows you how to check the DEFERRED_SEGMENT_CREATION parameter. Then a table is created without segments, which you can verify by querying the USER_SEGMENTS data dictionary view. After the insert of a row, you query this view again to see that the segment now exists. You can also query the SEGMENT_CREATED column of the USER_TABLES, USER_INDEXES, or USER_LOBS views. For nonpartitioned tables, indexes, and LOBs, this column shows YES if the segment is created. The SYS.SEG$ data dictionary table stores the storage parameters that you specified during table or index creation. Slide 14: Additional features are implemented in Oracle Database to save space. All UNUSABLE indexes and index partitions are created without a segment. This functionality is completely transparent for you.