it 21003 database administration section 07. space management managing space: an introduction ...

IT 21003 Database AdministrationIT 21003 Database Administration

Section 07Section 07

Space ManagementSpace Management

Managing Space: An IntroductionManaging Space: An Introduction Organizing database storage is a major Organizing database storage is a major responsibility for the DBAresponsibility for the DBA

Need to ensure tablespaces are set up with appropriate Need to ensure tablespaces are set up with appropriate default storage definitionsdefault storage definitions Objects should be created for optimal space usage and Objects should be created for optimal space usage and performanceperformance Situation must be monitored continuallySituation must be monitored continually

Garbage collection on Oracle requires DBA assistance or Garbage collection on Oracle requires DBA assistance or interventionintervention

DBAs need to understand how extents and blocks DBAs need to understand how extents and blocks are usedare used

The use of extents and blocks can be controlled by the The use of extents and blocks can be controlled by the DBA using a variety of techniquesDBA using a variety of techniques


Database Storage Relationships – E-R DiagramDatabase Storage Relationships – E-R Diagram

Tablespace

Segment

Datafile

Extent

Block

Logical Physical

Logical – Kinda Physical


Structure of Data SegmentsStructure of Data Segments The space occupied by a database object is The space occupied by a database object is called a segmentcalled a segment

Segment types can be:Segment types can be: DataData IndexIndex RollbackRollback TemporaryTemporary

Each segment is contained within a tablespace Each segment is contained within a tablespace and consists of one or more extents of contiguous and consists of one or more extents of contiguous data blocksdata blocks


Structure of Data SegmentsStructure of Data Segments Tablespace: tbspc1Tablespace: tbspc1

Segment 1

Extent1 of Seg1 Extent2 of Seg1 Extent3 of Seg1

Segment 2

Extent1 of Seg2 Extent2 of Seg2 Extent3 of Seg2

Segment 3

Extent1 of Seg3 Extent2 of Seg3

Oracle Data Blocks


Specifying Storage Parameters for Data Specifying Storage Parameters for Data SegmentsSegments

INITIALINITIAL Size of first extent Size of first extent Default is five Oracle blocksDefault is five Oracle blocks

NEXTNEXT Size of the second extent to be allocatedSize of the second extent to be allocated Default is five Oracle blocksDefault is five Oracle blocks

PCTINCREASEPCTINCREASE Factor by which the next extent is larger than the most Factor by which the next extent is larger than the most recent extent to be allocatedrecent extent to be allocated Default is 50% Default is 50%

Space ManagementSpace ManagementSpecifying Storage Parameters for Data SegmentsSpecifying Storage Parameters for Data Segments

MINEXTENTSMINEXTENTS Minimum number of extents to be used by the segmentMinimum number of extents to be used by the segment Default is 1Default is 1

MAXEXTENTSMAXEXTENTS Maximum number of extents that can be allocatedMaximum number of extents that can be allocated Default depends on block sizeDefault depends on block size Maximum value is 2,147,483,645 or approximately 2GBMaximum value is 2,147,483,645 or approximately 2GB

INITRANSINITRANS Minimum number of transactions that have space allocated within Minimum number of transactions that have space allocated within each blockeach block

Space reserved for the Interested Transaction List (ITL)Space reserved for the Interested Transaction List (ITL) Default is 1 for TablesDefault is 1 for Tables Default is 2 for Indexes Default is 2 for Indexes Maximum is 255Maximum is 255


Specifying Storage Parameters for Data Specifying Storage Parameters for Data SegmentsSegments

MAXTRANSMAXTRANS Maximum number of simultaneous transactions that Maximum number of simultaneous transactions that can consume space in a block at any one timecan consume space in a block at any one time Default value is 255Default value is 255 Maximum is 255Maximum is 255

FREELISTSFREELISTS Number of segment freelistsNumber of segment freelists Default value is 1Default value is 1


Sizing ExtentsSizing Extents Tablespaces are made up of datafilesTablespaces are made up of datafiles

The storage within a datafile is regarded as contiguousThe storage within a datafile is regarded as contiguous Datafiles need not be contiguous with each otherDatafiles need not be contiguous with each other

A possible scenarioA possible scenario Tablespace size is 40MBTablespace size is 40MB Made up of three datafiles, 20MB, 10MB, 10MBMade up of three datafiles, 20MB, 10MB, 10MB

Size of largest possible extent is 20MB, not 40MB, because Size of largest possible extent is 20MB, not 40MB, because extents cannot span datafiles extents cannot span datafiles


Sizing ExtentsSizing Extents Storage structures are allowed unlimited numbers Storage structures are allowed unlimited numbers of extentsof extents

Not allowed for objects within the SYSTEM tablespaceNot allowed for objects within the SYSTEM tablespace Limited number of extents based on DB_BLOCK_SIZELimited number of extents based on DB_BLOCK_SIZE

Space ManagementSpace ManagementSizing ExtentsSizing Extents

To size extents we need the following information:To size extents we need the following information: How will the table be used?How will the table be used? How will it grow (insert activity)?How will it grow (insert activity)? Volatility?Volatility? Types of queries?Types of queries?

Reasons for allowing tables to occupy multiple extentsReasons for allowing tables to occupy multiple extents Access using an indexAccess using an index

Involves a direct lookup on a table, no need for blocks to be Involves a direct lookup on a table, no need for blocks to be contiguouscontiguous

Full-Table scans in a mult-iuser environmentFull-Table scans in a mult-iuser environment No real impact because disk arm will be servicing requests across all No real impact because disk arm will be servicing requests across all usersusers

Full-Table scans in a single-user environmentFull-Table scans in a single-user environment Performance impact, but not if extents are reasonably largePerformance impact, but not if extents are reasonably large


Sizing ExtentsSizing Extents Extents should be sized in multiples ofExtents should be sized in multiples of

DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT (DBFMRC)DB_BLOCK_SIZE * DB_FILE_MULTIBLOCK_READ_COUNT (DBFMRC)

Oracle brings DBFMRC blocks into memory in one Oracle brings DBFMRC blocks into memory in one fetchfetch Example:Example: DB_BLOCK_SIZE is 4KBDB_BLOCK_SIZE is 4KB DBFMRC is set at the default of 8DBFMRC is set at the default of 8 4KB * 8 = 32KB 4KB * 8 = 32KB Extents should therefore be in multiples of 32KBExtents should therefore be in multiples of 32KB


Sizing ExtentsSizing Extents Beware of setting DBFMRC too highBeware of setting DBFMRC too high

32-Bit OS’s will bring in a maximum of 64KB of data in 32-Bit OS’s will bring in a maximum of 64KB of data in one fetchone fetch

Setting DBFMRC such that DBFMRC * DBS is greater than Setting DBFMRC such that DBFMRC * DBS is greater than 64KB has no effect on performance64KB has no effect on performance 64-Bit OS’s can cope with higher values64-Bit OS’s can cope with higher values

Many systems run with DB_BLOCK_SIZE = 8KB or Many systems run with DB_BLOCK_SIZE = 8KB or 4KB and DBFMRC = 84KB and DBFMRC = 8

4KB * 8 = 32KB4KB * 8 = 32KB 8KB * 8 = 64KB8KB * 8 = 64KB

Space ManagementSpace ManagementLarge ExtentsLarge Extents

Can be more difficult to manageCan be more difficult to manage If a table extends, there may well be enough total free space within If a table extends, there may well be enough total free space within the tablespace for the amount of storage requiredthe tablespace for the amount of storage required

But not enough contiguous storage for the new large extentBut not enough contiguous storage for the new large extent An error is returnedAn error is returned Tablespace needs to be reorganized or extendedTablespace needs to be reorganized or extended

Reduce the frequency of dynamic extensionReduce the frequency of dynamic extension Possible performance improvement because dynamic allocation of Possible performance improvement because dynamic allocation of space is an expensive activity, generating a lot of recursive SQLspace is an expensive activity, generating a lot of recursive SQL

A tablespace can be dedicated (designed) for a single A tablespace can be dedicated (designed) for a single objectsobjects

If the object has equal-sized extents of 10MB, a sensible size for If the object has equal-sized extents of 10MB, a sensible size for the tablespace could be 100MB plus the size of one Oracle data the tablespace could be 100MB plus the size of one Oracle data block per datafileblock per datafile

Accounts for the one-block overhead for each file headerAccounts for the one-block overhead for each file header


Initial ExtentsInitial Extents The first extent for each object has a one-block The first extent for each object has a one-block overhead, containing no data, known as the overhead, containing no data, known as the segment header blocksegment header block

Segment header is the first block and contains extent Segment header is the first block and contains extent information for all other extents belonging to the segment information for all other extents belonging to the segment (extent map)(extent map) Finite number of entries can fit in a header blockFinite number of entries can fit in a header block

Actual number depends on block sizeActual number depends on block size Tables defined with UNLIMITED EXTENTS have chained Tables defined with UNLIMITED EXTENTS have chained header blocks to hold the extent informationheader blocks to hold the extent information

On highly concurrent systems, transactions may On highly concurrent systems, transactions may queue for latches on the header blockqueue for latches on the header block

Space ManagementSpace ManagementInserting New RowsInserting New Rows

Extents are typically allocated when an INSERT Extents are typically allocated when an INSERT operation cannot find enough space in the existing operation cannot find enough space in the existing blocksblocks

A transaction maintains a list of free blocks it has A transaction maintains a list of free blocks it has accessedaccessed

These are blocks that have been used by the transaction and These are blocks that have been used by the transaction and still have space available for insertsstill have space available for inserts If the new insert cannot fit in any of these blocks, the segment If the new insert cannot fit in any of these blocks, the segment freelist of blocks is searchedfreelist of blocks is searched

If no blocks are available on the freelist, Oracle If no blocks are available on the freelist, Oracle uses a new, unused block from the current extent; if uses a new, unused block from the current extent; if no blocks are available, it resorts to dynamic no blocks are available, it resorts to dynamic extentsionextentsion

This means allocating a new extentThis means allocating a new extent

Space ManagementSpace ManagementInserting New Rows: Allocation of New ExtentsInserting New Rows: Allocation of New Extents

The size of the new extent is determined by the current The size of the new extent is determined by the current values of NEXT and PCTINCREASEvalues of NEXT and PCTINCREASE

Formula is:Formula is: (1 + PCTINCREASE/100) * NEXT(1 + PCTINCREASE/100) * NEXT

Oracle searches the free-space map for an extent of free Oracle searches the free-space map for an extent of free space the required size rounded up to the nearest five space the required size rounded up to the nearest five blocksblocks If an extent of the exact required size is not found, Oracle If an extent of the exact required size is not found, Oracle searches the free-space map for a piece of free space larger searches the free-space map for a piece of free space larger than that requiredthan that required

If it finds a large piece, it splits the free space as follows:If it finds a large piece, it splits the free space as follows: If the piece is within five blocks of the required size, the whole amount If the piece is within five blocks of the required size, the whole amount is allocatedis allocated If it is larger, the free space is split and the required amount is If it is larger, the free space is split and the required amount is allocated, leaving behind a smaller piece of free spaceallocated, leaving behind a smaller piece of free space


Free-Space AllocationFree-Space Allocation If a new extent of 28MB is required, and a piece If a new extent of 28MB is required, and a piece of free space of size 40MB is foundof free space of size 40MB is found

Used ExtentUsed Extent Free-Space Extent of 40MBFree-Space Extent of 40MB Used ExtentUsed Extent

Used ExtentUsed Extent New Extent 28MBNew Extent 28MB FreeFree Used ExtentUsed Extent

Free Space is split in appropriate fashion


Free-Space AllocationFree-Space Allocation If no piece of free space large enough can be If no piece of free space large enough can be foundfound

Free-space map is searched and adjacent free extents Free-space map is searched and adjacent free extents coalesced in an attempt to produce a large enough piece coalesced in an attempt to produce a large enough piece of contiguous spaceof contiguous space

SMON coalesces free-space fragments periodicallySMON coalesces free-space fragments periodically

Oracle then searches the free-space map looking Oracle then searches the free-space map looking forfor

An exact matching size extentAn exact matching size extent A larger piece of free space and acts as described A larger piece of free space and acts as described beforebefore

If no space of the required size is available, Oracle returns an If no space of the required size is available, Oracle returns an error if AUTOEXTEND is off for all datafiles in the tablespaceerror if AUTOEXTEND is off for all datafiles in the tablespace


Free-Space AllocationFree-Space Allocation If a new extent is allocated, Oracle updates the If a new extent is allocated, Oracle updates the segment header, free-space map, and Data segment header, free-space map, and Data Dictionary accordinglyDictionary accordingly Oracle locally managed tablespaces are managed Oracle locally managed tablespaces are managed by bitmapsby bitmaps

No management of free-space map because all extents No management of free-space map because all extents are equally sizedare equally sized


Coalescing Free SpaceCoalescing Free Space

Used (Table Used (Table A)A)

FreeFree Used (Table Used (Table B)B)

FreeFree Used (Table Used (Table A)A)

Table B gets dropped


FreeFree FreeFree FreeFree Used (Table Used (Table A)A)

10MB 20MB 8MB

10MB 20MB 8MB

Free-space extents coalesced during a search for free space


FreeFree Used (Table Used (Table A)A)

38MB


Controlling Space Usage within Data BlocksControlling Space Usage within Data Blocks Each segment has a list of free blocks maintained Each segment has a list of free blocks maintained automatically by server processes affecting the automatically by server processes affecting the segmentsegment

A segment freelist is a set of blocks available for new A segment freelist is a set of blocks available for new rows to be insertedrows to be inserted

Two parameters are used to control which blocks Two parameters are used to control which blocks appear on the freelistappear on the freelist

PCTFREE specifies the amount of space in each PCTFREE specifies the amount of space in each blocks that is unavailable for insertsblocks that is unavailable for inserts

Reserved for updates of existing rows already within the blockReserved for updates of existing rows already within the block

PCTUSED determines how much data should be PCTUSED determines how much data should be removed from a block before the block is put back on the removed from a block before the block is put back on the freelistfreelist


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE + PCTUSED <= 100%PCTFREE + PCTUSED <= 100% PCTFREE large (~40), PCTUSED small (~20)PCTFREE large (~40), PCTUSED small (~20)

Blocks remain off the freelist for long periodsBlocks remain off the freelist for long periods Can lead to waste of spaceCan lead to waste of space Saves processing costs because blocks do not thrash Saves processing costs because blocks do not thrash on and off the freeliston and off the freelist Allows space for updates Allows space for updates Fewer rows per block that can impact full-table scans Fewer rows per block that can impact full-table scans but reduce possible block contentionbut reduce possible block contention

PCTFREE

PCTUSED


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE small (~5), PCTUSED very large (~90)PCTFREE small (~5), PCTUSED very large (~90)

Gives efficient use of space due to high block Gives efficient use of space due to high block occupancyoccupancy Leads to high processing costs because blocks are Leads to high processing costs because blocks are frequently moved on and off the freelistfrequently moved on and off the freelist Not much space for updates of existing rowsNot much space for updates of existing rows

PCTFREEPCTUSED


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE large (~40), PCTUSED large (~50)PCTFREE large (~40), PCTUSED large (~50)

Gives poor use of space due to low block occupancyGives poor use of space due to low block occupancy Allows space for updatesAllows space for updates Processing costs could be high because blocks are Processing costs could be high because blocks are frequently moved on and off the freelistfrequently moved on and off the freelist

PCTFREEPCTUSED


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE small (~10), PCTUSED fairly small PCTFREE small (~10), PCTUSED fairly small (~40)(~40)

These are the default valuesThese are the default values Gives relatively efficient use of space due to high block Gives relatively efficient use of space due to high block occupancyoccupancy Not much space for large-scale updatesNot much space for large-scale updates Processing costs are low because blocks are not Processing costs are low because blocks are not frequently moved on and off the freelistfrequently moved on and off the freelist

PCTFREE

PCTUSED


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE small (~10), PCTUSED fairly small PCTFREE small (~10), PCTUSED fairly small (~40)(~40)

The Block accepts both updates and inserts until it The Block accepts both updates and inserts until it reaches 10 percent free; it then allows only updatesreaches 10 percent free; it then allows only updates Once the block has dropped back down to 40% used, Once the block has dropped back down to 40% used, the block allows inserts againthe block allows inserts again


Setting PCTFREE and PCTUSEDSetting PCTFREE and PCTUSED PCTFREE can determine the free space in an PCTFREE can determine the free space in an index when it is createdindex when it is created

Can be set high to prevent excessive block splitting and Can be set high to prevent excessive block splitting and reorganizationreorganization

Useful for volatile indexesUseful for volatile indexes Cannot be reset and has no effect as the index is updatedCannot be reset and has no effect as the index is updated Use purely as an initial setting or starting pointUse purely as an initial setting or starting point

Oracle manages the occupancy of index blocks such Oracle manages the occupancy of index blocks such that they remain, on average, 70 percent full, thus that they remain, on average, 70 percent full, thus overriding the PCTFREE settingoverriding the PCTFREE setting PCTUSED cannot be specified for indexesPCTUSED cannot be specified for indexes

Space ManagementSpace ManagementRegaining Free Space: Deallocating ExtentsRegaining Free Space: Deallocating Extents

Oracle maintains a High Water Mark (HWM) for Oracle maintains a High Water Mark (HWM) for each segment in every tableeach segment in every table

The point to which actual data has grown within the The point to which actual data has grown within the segmentsegment Cannot return space in blocks below the HWM to free Cannot return space in blocks below the HWM to free space without a reorganizationspace without a reorganization

Possible to reclaim space allocated to a segment Possible to reclaim space allocated to a segment but never usedbut never used

This is space above the HWMThis is space above the HWM ALTER INDEXALTER INDEX sales_ind sales_ind DEALLOCATE UNUSEDDEALLOCATE UNUSED;; The minimum size of the remaining space must still satisfy the The minimum size of the remaining space must still satisfy the size determined by INITIAL and MINEXTENTSsize determined by INITIAL and MINEXTENTS ALTER TABLEALTER TABLE orders orders DEALLOCATE UNUSED KEEPDEALLOCATE UNUSED KEEP 20M; 20M; The KEEP clause can override MINEXTENTS and INITIAL, The KEEP clause can override MINEXTENTS and INITIAL, causing the values to be reset accordinglycausing the values to be reset accordingly


Regaining Free Space Below the HWMRegaining Free Space Below the HWM The dbms_space_unused_space procedure can The dbms_space_unused_space procedure can be used only to return information about unused be used only to return information about unused blocks and the position of the HWMblocks and the position of the HWM Deleting rows does not regain space – HWM Deleting rows does not regain space – HWM remains unchangedremains unchanged

Must use Export/Import to reorganize the table storageMust use Export/Import to reorganize the table storage

Create a new table as follows:Create a new table as follows: CreateCreate new_orders new_orders As SelectAs Select * * FromFrom orders; orders; Drop TableDrop Table orders; orders; RenameRename new_orders new_orders ToTo orders; orders;

Causes a loss of GRANTS, etc on the original tableCauses a loss of GRANTS, etc on the original table Needs space for the new copyNeeds space for the new copy


ROWIDROWID ROWIDs are pseudo data types used by Oracle to ROWIDs are pseudo data types used by Oracle to uniquely address rows in the databaseuniquely address rows in the database

Have a number of componentsHave a number of components

Shown as 18-Character strings using base-64 Shown as 18-Character strings using base-64 encodingencoding Extended ROWID FormatExtended ROWID Format

000000000000 FFFFFF BBBBBBBBBBBB SSSSSS

Data Object Number

Relative file Number

Block Number

Slot Number

AAAASRAAAAASRA AFAAFA AAAAACAAAAAC AADAAD

Example ROWID Number


ROWIDsROWIDs Datafiles have absolute and relative addressesDatafiles have absolute and relative addresses

Observe file_id and relative_fno in the dba_files tableObserve file_id and relative_fno in the dba_files table

A ROWID does not contain the absolute file A ROWID does not contain the absolute file addressaddress

Datafiles are identified only within a particular Datafiles are identified only within a particular tablespacetablespace The object number determines the tablespace, so that The object number determines the tablespace, so that ROWID can uniquely define the fileROWID can uniquely define the file

Object Number + Relative File NumberObject Number + Relative File Number

Relative addressing allows many more files to be Relative addressing allows many more files to be used by an Oracle databaseused by an Oracle database

Can have 64 million operating-files Can have 64 million operating-files


Data Block Format Data Block Format

Fixed HeaderFixed Header

Transaction EntryTransaction Entry



Row DirectoryRow Directory

PCTFREE

PCTUSED


Block OrganizationBlock Organization HeaderHeader

Average size is about 85-100 bytesAverage size is about 85-100 bytes Made up of a fixed header and variable header (transaction Made up of a fixed header and variable header (transaction entries)entries)

Row DirectoryRow Directory Two per rowTwo per row Contains address of start of each row within the blockContains address of start of each row within the block


INITRANS and MAXTRANSINITRANS and MAXTRANS Setting INITRANS reserves space for transaction Setting INITRANS reserves space for transaction entries in the blockentries in the block

This space is not available for data storageThis space is not available for data storage If INITRANS is too low, transactions may be placed in If INITRANS is too low, transactions may be placed in the headerthe header

Setting MAXTRANS allows the specified number Setting MAXTRANS allows the specified number of concurrent transactions to place their entries in of concurrent transactions to place their entries in the block only if there is enough free spacethe block only if there is enough free space


Row Storage Row Storage

LastNameLastName FirstNameFirstName MiddleNameMiddleName AddressAddress StateState

SmithSmith JosephJoseph AdamAdam 123 State 123 State StreetStreet

OhioOhio

Row Row OverheadOverhead

Number of Number of ColumnsColumns

(5)(5)

Column 1 Column 1 LengthLength

(15)(15)

Column 1 Column 1 DataData

(SMITH)(SMITH)

Column 2 Column 2 LengthLength

(15)(15)

Column 2 Column 2 DataData

(JOSEPH)(JOSEPH)

……

Table Columns

Row


Row StorageRow Storage Row Overhead is made up of:Row Overhead is made up of:

Row HeaderRow Header Offset in transaction listOffset in transaction list Number of Columns in the row that contain dataNumber of Columns in the row that contain data

Rows with > 255 columns will have additional row overhead Rows with > 255 columns will have additional row overhead informationinformation

Space ManagementSpace ManagementStorage Patterns for Data TypesStorage Patterns for Data Types

VARCHAR and VARCHAR2VARCHAR and VARCHAR2 1 byte per alphanumeric character1 byte per alphanumeric character Maximum is 4,000 bytesMaximum is 4,000 bytes

CHARCHAR Fixed LengthFixed Length Maximum is 2,000 bytesMaximum is 2,000 bytes

DATEDATE 7 bytes for all date formats7 bytes for all date formats

NUMBERNUMBER Up to 38 digits of precisionUp to 38 digits of precision Stored as a single base-100 digit per byteStored as a single base-100 digit per byte

Therefore no rounding errors in financial dataTherefore no rounding errors in financial data

Space ManagementSpace ManagementStorage Patterns for Data TypesStorage Patterns for Data Types

ROWIDROWID 10 bytes10 bytes

LOBLOB Locaters about 20 bytesLocaters about 20 bytes

VARRAYVARRAY User definedUser defined

User-Defined TypesUser-Defined Types

Space ManagementSpace ManagementUpdating RowsUpdating Rows

When an update causes a change in the length of When an update causes a change in the length of a data itema data item

Oracle builds a whole new row in the blockOracle builds a whole new row in the block The space occupied by the original row is labeled as The space occupied by the original row is labeled as free spacefree space

This is known as block fragmentationThis is known as block fragmentation

Oracle delays reorganization of storage within the Oracle delays reorganization of storage within the block until no more space is available within the block until no more space is available within the blockblock If an update does not change the length of a data If an update does not change the length of a data item, the row is updated in situ (in place)item, the row is updated in situ (in place)

No block re-organization is necessaryNo block re-organization is necessary

Space ManagementSpace ManagementRow Chaining and Row MigrationRow Chaining and Row Migration

Ordinarily, Oracle does one fetch for the retrieval Ordinarily, Oracle does one fetch for the retrieval of a single rowof a single row A row that occupies multiple blocks impacts A row that occupies multiple blocks impacts performance because more than one I/O may be performance because more than one I/O may be required to fetch the rowrequired to fetch the row

Typically happens with rows containing LOB columnsTypically happens with rows containing LOB columns Known as Row ChainingKnown as Row Chaining

Not much can be done about thisNot much can be done about this


Updates to rows may not fit within the free space Updates to rows may not fit within the free space in the blockin the block

In this case the whole row is migrated into another In this case the whole row is migrated into another block, leaving behind a pointer to the chained blockblock, leaving behind a pointer to the chained block This Row Migration causes extra disk I/O per row This Row Migration causes extra disk I/O per row because the ROWID of the migrated row is not changedbecause the ROWID of the migrated row is not changed

Indexes still point to the original blockIndexes still point to the original block

PCTFREE can be used to control row migration PCTFREE can be used to control row migration by reserving space for updates to existing rows in by reserving space for updates to existing rows in blocksblocks

Space ManagementSpace ManagementDetecting and Handling Row MigrationDetecting and Handling Row Migration

Row Migration can and should be avoidedRow Migration can and should be avoided ANALYZE can be used to detect chained or ANALYZE can be used to detect chained or migrated rowsmigrated rows

The script utlchain.sql needs to be run in the account The script utlchain.sql needs to be run in the account used to perform the analysisused to perform the analysis

Builds the chained_rows tableBuilds the chained_rows table Use Use Analyze TableAnalyze Table student student List Chained RowsList Chained Rows;;

This puts data into the chained_rows tableThis puts data into the chained_rows table

To see the details of the chained_rows tableTo see the details of the chained_rows table SelectSelect * * FromFrom chained_rows chained_rows WhereWhere table_name = ‘STUDENT’; table_name = ‘STUDENT’;

Space ManagementSpace ManagementEliminating Migrated RowsEliminating Migrated Rows

This is a hard way to do itThis is a hard way to do it Create a temporary table and copy into it all the Create a temporary table and copy into it all the rows found in the chained_rows tablerows found in the chained_rows table

Create TableCreate Table student_mig student_mig AsAs SelectSelect * * FromFrom student student Where Rowid InWhere Rowid In ( (SelectSelect head_rowid head_rowid FromFrom chained_rows); chained_rows);

Delete the migrated rows from the original tableDelete the migrated rows from the original table Delete FromDelete From student student Where Rowid InWhere Rowid In

((SelectSelect head_rowid head_rowid FromFrom chained_rows); chained_rows);

Now insert the rows into the original table from the Now insert the rows into the original table from the temporary tabletemporary table

Insert IntoInsert Into student student SelectSelect * * FromFrom student_mig; student_mig;


Can also be observed in user_tablesCan also be observed in user_tables Need to run this statement firstNeed to run this statement first

Analyze TableAnalyze Table table_name table_name ComputeCompute | | Estimate StatisticsEstimate Statistics;; This populates columns in the user_tables viewThis populates columns in the user_tables view

Tables and Indexes can be analyzed this wayTables and Indexes can be analyzed this way Analyzing tables with the COMPUTE option takes Analyzing tables with the COMPUTE option takes longer than a full table scanlonger than a full table scan

It performs a sort and requires temporary storageIt performs a sort and requires temporary storage

Statistics are found in:Statistics are found in: user_tablesuser_tables user_indexesuser_indexes user_tab_columnsuser_tab_columns

Space ManagementSpace ManagementAnalyzing ObjectsAnalyzing Objects

To Analyze you need to own the object or have To Analyze you need to own the object or have the privilege Analyze Any system privilegethe privilege Analyze Any system privilege Analyzing will invalidate any currently parsed SQL Analyzing will invalidate any currently parsed SQL statements on the tablestatements on the table Oracle9i has dbms_stats that can be used to Oracle9i has dbms_stats that can be used to analyze and monitor tablesanalyze and monitor tables

Space ManagementSpace ManagementEstimating Space RequirementsEstimating Space Requirements

Tables created with badly designed storage Tables created with badly designed storage parameters cause performance problems like:parameters cause performance problems like:

Frequent Dynamic ExtensionFrequent Dynamic Extension Large numbers of extents causing problems for full-Large numbers of extents causing problems for full-table scanstable scans Extents too large to fit into any free-space patternExtents too large to fit into any free-space pattern Row Chaining and MigrationRow Chaining and Migration Over-allocation of space leading to the purchase of Over-allocation of space leading to the purchase of unnecessary hardwareunnecessary hardware

A practical approach is to preload the table with A practical approach is to preload the table with some sample data, analyze the table, and discover some sample data, analyze the table, and discover the number of rows per block the number of rows per block

Calculate a 5% overhead for safetyCalculate a 5% overhead for safety

Space ManagementSpace ManagementQuestions?Questions?

it 21003 database administration section 07. space management managing space: an introduction ...

Documents