Extents, segments and blocks in detail

1. Extents, segments and blocks in detail

2. Database structure Database Table spaces Data file physical logical Segment O/S block Extent Oracle block

3. Table spaces • Belongs only to one database • Consists of one or more operating system files (i.e. data files) • Brought online while db is running • Except for the SYSTEM table space or one with an active rollback segment, they can be taken offline while db is running

4. Sizing your table spaces • Identify the max size of each record • Estimate the number of rows in each table at creation/start up • This could be stated as instance volume • Estimate the growth of the tables. • Determine how many years data you are to hold etc. • What is the increase in records per year.

5. Storage clauses for table spaces • Storage parameters • Initial extent size of the first extent • Next extent size of second extent • Pctincrease % increase for each extent SET TO 0 !!!! • Minextents min number of extents • Maxextents max number of extents

6. How extents work • Segments need one or more extents. • If pctincrease is anything other than 0 then the table can grow out of hand. 50 40 30 20 10 0 50 40 30 20 10 0 Table size in MB 1 2 3 4 5 6 7 8 9 10 11 12 13 14 1 2 3 4 5 6 7 8 9 10 11 12 13 14 Pctincrease > 0 pctincrease = 0

7. Creating table spaces Create tablespace products Datafile ‘d:/oradata/product1.dbf’ size 100M Default storage (initial 500K next 500K pctincrease 0 minextents 1) Online; Creates a table space called products which is allocated 100M of disk space in a dbf file called product1.dbf (this is what is backed up) each extent will be 500K in size and will not grow.

8. Parameters for creating Table spaces • Table space name given when created • Datafile specifies the data file or files for the table space • Default storage parameters for all objects in table space • Minimum ensures extents are multiples of this • Online/offline start state • Permanent will hole permanent objects • Temporary holds temp objects e.g those used in sorts etc.

9. Cont … • Storage parameters influence the segment storage allocation • Initial size of first extent • Next size of second extent • Maxextents max number allowed • Minextents min number allocated when segment is created (default = 1) • Pcincrease % by which extent grows

10. Adding data files to table space ALTER TABLESPACE app_data ADD DATAFILE ‘/data/app03.dbf’ SIZE 200m; NOTE: you should always specify the full path of file location or oracle will store them in the default directory of the database server

11. Changing size • Instead of adding space by adding files you can increase the existing file size. • Used to enlarge a tablespace ALTER DATABASE DATAFILE ‘/data/app02.dbf’ RESIZE 200m; NEW SIZE

12. Moving datafiles • The tablespace must be offline • Target datafiles must exist (use OS commands) • Delete original file after moving • Must not be SYSTEM tablespace files or have active rollbacks or temporary segments ALTER TABLESPACE app_data RENAME DATAFILE ‘/data/app01.dbf’ TO ‘/newdata/app01.dbf’;

13. Dropping table spaces • Removes all contents of tablespace from database • If data is in tablespace INCLUDING CONTENTS option must be used • Does not matter if tablespace is read-only or not • Recommend that take tablespace offline to check effect before dropping DROP TABLESPACE app_data INCLUDING CONTENTS;

14. Extents • Set of contiguous number of blocks. Each segment has one or more extents • May NOT span data files but must exist in one file Data blocks • One block corresponds to one or more physical file blocks allocated from physical file • Set at initialisation parameters of file DB_BLOCK_SIZE

15. Types of table spaces • SYSTEM • Data Dictionary information • SYSTEM rollback segment • Required in all dbs or db operations • Should not contain user information but is allowed. • Non-SYSTEM • Rollback segments • Temporary segments • Application data • Application indexes

16. PCTFREE & PCTUSED pctfree pctused Block empty Records inserted Inserts are stopped when pctfree is met Records deleted and amount of free space in block increases Amount of free space in block falls below the pctused and block becomes available for inserts and is added to segments free list, t remains on free list until block reaches pctfree again Pctfree and pctused control the free space available for inserts. Pctused does not apply to indexes

17. PCTfree • Setting low means less space wasted in block but may not have enough room for future updates • If not enough space to update the entire row is migrated to another block • Migration is a serious performance problem • 10% is normally sufficient but if chaining (migration) is common it should be monitored.

18. Calculation of PCTfree Final record length – initial record length Final record length • Express above as a % • As final and initial record lengths in most systems are approximate then above formula will only be approximate and should be rounded to next integer

19. Automatic segment space management • This is new in oracle 9i and the benefits include (according to oracle) • Ease of use • Better space initialisation • Better concurrency handling • Better performance • It users bitmaps in the data file header to map the blocks, their state and how much amount of data is in each blocks • It replaces freelist, freelist groups and pctused. • Set in create tablespace command (add option SEGMENT SPACE MANAGEMENT AUTO)

20. Extents • An extent is a logical storage unit made up from contiguous data blocks • First allocated when segment is created and then added when all blocks in segment are full • Oracle can manage the extent allocation through DD or locally • The header block of each segment contains a directory of extents in that segment

21. Allocating extents • Oracle allocates an extent when an object is first created or when all blocks in a segment are full • For locally managed tablespaces space for the next extent is allocated by • Searching each data files bitmap making up the tablespace for the contiguous space required • Exceptions raised if none of the files have enough free space.

22. Allocating extents • For dictionary managed tablespaces, space for the next extent is allocated by • Oracle searches the tablespaces for a free extent with an exact match (or match +1) • If no match occurs, Oracles searches free blocks again for a free extent larger than required value • If one found and it is less or equal to 5 blocks bigger than requested size then it will allocate all blocks in free space to segment • If size greater then 5 blocks then only exact requested size is allocated • If step 2 fails then oracle coalesces free space in tablespace and repeats step 2 • If step 3 fails then if auto extendible is on oracle will try to extend file and repeat step 2 • If oracle can not extend file or auto extendible if not set then an exception is raised.

23. Querying extent information • Query the extent information using: • DBA_EXTENTS - This will list the extents allocated in the db for all segments • DBA_FREE_SPACE - This will list information about free extents in each tablespace. This will also give and indication of how badly a tablespace is fragmented • Select tablespace_name, count(*) cnt, max(bytes)/1024 maxbytes, min (bytes)/1024 minbytes, avg (bytes)/1024 avgbytes,sum (bytes)/1024 sumbytes • From dba_free_space • Group by tablespace_name;

24. Segments A logical storage unit made up of one or more extents Every object in DB that requires space to store data is allocated a segment If no segment storage parameters are specified when segment created then default tablespace parameters are used, (or db server defaults) Changes to storage parameters only effect new extents nor extents in existing segments.

25. Querying segment information Use: DBA_SEGMENTS – this includes information on parameter and type of segment V$SORT_SEGMENT – this view contains information about every sort segment in temporary tablespaces Note: in DBA_SEGMENTS lob segments are listed as LOBINDEX for indexes and LOBSEGMENT for data

26. Managing undo segments Undo segments record old values of data that were changed by a transaction They are used to provide read consistent images of the data and to rollback uncommitted transactions when requested by user or at instance recovery. Create UNDO TABLESPACE <name> DATAFILE ‘undo.dbf’ size 30M The undo spaces can be dropped by not until all transactions using them are complete and committed. Must be large enough to handle the workload of all concurrent transactions.

27. Querying UNDO You can query undo information using DBA_ROLLBACK_SEGS – provides information bout undo segments (on and offline) V$ROLLNAME – lists all the undo segments online use USN column to link to V$ROLLSTAT V$ROLLSTAT – this lists undo statistics V$UNDOSTAT – view collects 10 minute snapshots that reflect the performance of the undo tablespaces

28. Sample question • What is row migration? • A single row spread across multiple blocks • Move a table from one tablespace to another • Storing a row in a different block when there is not enough room in the current block for the row to expand • Deleting a row and adding it back to the same table.