1 / 27

Storage Structure and Relationships

Storage Structure and Relationships. Objectives. After completing this lesson, you should be able to do the following: Describe the logical structure of the database List the segment types and their uses List the keywords that control block space usage Obtain storage structure information.

vanida
Download Presentation

Storage Structure and Relationships

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Storage Structure and Relationships

  2. Objectives • After completing this lesson, you should be able to do the following: • Describe the logical structure of the database • List the segment types and their uses • List the keywords that control block space usage • Obtain storage structure information

  3. Storage and Relationship Structure Database PROD TABLESPACES SYSTEM USER_DATA RBS TEMP DATA FILES DISK2/ USER1.dbf DISK3/ USER2.dbf DISK1/ UNDO1.dbf DISK1/ TEMP.dbf DISK1/SYS1.dbf SEGMENTS RBSeg S_DEPT DataSeg S_EMP DataSeg S_DEPT(cont'd) DataSeg S_EMPFIRST_NAME Index IndexSeg RBS1 RBSeg RBS2 RBSeg RBS1(cont'd) RBSeg RBS2(cont'd) RBSeg Temp TempSeg D.D.Table DataSeg D.D.Index IndexSeg EXTENTS 1 2 1 2 1 2 1 1 2 2 1 FREE 1 1 2 2 1 Oracle DATA BLOCKS

  4. Types of Segments Table Table partition Cluster Index

  5. Types of Segments Index-organizedtable Index partition Undosegment Temporarysegment

  6. Types of Segments LOBsegment Nested table Bootstrapsegment

  7. Storage Clause Precedence Oracle default Tablespace Segment

  8. Extent Allocation and Deallocation • An extent is a chunk of space used by a segment within a tablespace. • An extent is allocated when the segment is: • Created • Extended • Altered • An extent is deallocated when the segment is: • Dropped • Altered • Truncated

  9. Used and Free Extents Data file File header Used extent Free extent

  10. Database Block • Minimum unit of I/O • Consists of one or more operating system blocks • Set at tablespace creation • DB_BLOCK_SIZE is the default block size

  11. Multiple Block Size Support • A database can be created with a standard block size and up to four nonstandard block sizes. • Block sizes can have any power-of-two value between 2KB and 32KB.

  12. Standard Block Size • Set at database creation using the DB_BLOCK_SIZE parameter; cannot be changed without re-creating the database • Used for SYSTEM and TEMPORARY tablespaces • DB_CACHE_SIZE specifies the size of the DEFAULT buffer cache for standard block size: • Minimum size = one granule (4MB or 16MB) • Default value = 48 MB

  13. Nonstandard Block Size • Configure additional caches with the following dynamic parameters: • DB_2K_CACHE_SIZE for 2 KB blocks • DB_4K_CACHE_SIZE for 4 KB blocks • DB_8K_CACHE_SIZE for 8 KB blocks • DB_16K_CACHE_SIZE for 16 KB blocks • DB_32K_CACHE_SIZE for 32 KB blocks • DB_nK_CACHE_SIZE is not allowedif nK is the standard block size. • Minimum size for each cache is one granule.

  14. Creating NonstandardBlock Size Tablespaces CREATE TABLESPACE tbs_1 DATAFILE 'tbs_1.dbf' SIZE 10M BLOCKSIZE 4K; DESCRIBE dba_tablespaces Name Null? Type ------------------ -------- ------------ TABLESPACE_NAME NOT NULL VARCHAR2(30) BLOCK_SIZE NOT NULL NUMBER ...

  15. Multiple Block Sizing Rules • All partitions of a partitioned object must reside in tablespaces of the same block size. • All temporary tablespaces, including the permanent ones that are being used as default temporary tablespaces, must be of standard block size. • Index-organized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from the base table.

  16. Database Block Contents Header Free space Data

  17. Block Space Utilization Parameters INITRANS MAXTRANS PCTFREE PCTUSED

  18. Data Block Management • Two methods are available for managing data blocks: • Automatic segment-space management • Manual management

  19. Automatic Segment-Space Management • It is a method of managing free space inside database segments. • Tracking in-segment free and used space is done using bitmaps as opposed to using free lists. • This method provides: • Ease of management • Better space utilization • Better performance for concurrent INSERT operations

  20. Automatic Segment-Space Management • Bitmap segments contain a bitmap that describes the status of each block in the segment with respect to its available space. • The map is contained in a separate set of blocks referred to as bitmapped blocks (BMBs). • When inserting a new row, the server searches the map for a block with sufficient space. • As the amount of space available in a block changes, its new state is reflected in the bitmap.

  21. Configuring AutomaticSegment-Space Management • Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces. • After a tablespace is created, the specifications apply to all segments created in the tablespace. CREATE TABLESPACE data02 DATAFILE ‘/u01/oradata/data02.dbf’ SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

  22. Manual Data Block Management • Allows you to configure data blocks manually using parameters such as: • PCTFREE • PCTUSED • FREELIST • The only method available in previous Oracle versions

  23. Inserts (off freelist) Inserts Insert (on freelist) Block Space Usage PCTFREE=20PCTUSED=40 80% 1 2 80% Inserts 40% 3 4

  24. Obtaining Storage Information • Information about storage can be obtained by querying the following views: • DBA_EXTENTS • DBA_SEGMENTS • DBA_TABLESPACES • DBA_DATA_FILES • DBA_FREE_SPACE

  25. Summary • In this lesson, you should have learned how to: • Use tablespaces to: • Separate segments to ease administration • Control the user’s space allocation • Categorize segments by the type of information stored in the segment • Determine extent sizes using the storage clause • Control block space utilization • Obtain storage structure information

  26. Practice 9 Overview • This practice covers identifying and obtaining information on the various types of storage structures in the database.

More Related