3.3 Manage Database storage ----------------------- - Logical and physical structure of database and their relationships. Tablespaces and OS files. Tables. - Data structures: Database - tablespaces - segments (files) - extents - blocks. Tablespaces: - each tablespace contains one or more operating system files; - each tablespace contains zero or more segments, each of them consists of one or more extents consisting of Oracle blocks. - tablespaces can go offline or online (except SYSTEM). any tablespace containing active rollback segments can not be taken offline. SYSTEM tablespace must always be online because the data dictionary must always be available. - each object is in only one tablespace. - useful commands: drop tablespace [including contents] [cascade constraints]; cascade constraints -removes references to primary keys. alter tablespace rename datafile '' to ''; alter tablespace offline [normal|temporary|immediate] normal - performs checkpoint for all datafiles in this tablespace temporary - performs checkpoint for all online datafiles in the tablespace; immediate - does not ensure that tablespace files are available and does not perform checkpoint; TEMPORARY AND IMMEDIATE ONLY IN ARCHIVELOG MODE!!! - When Oracle compacts data in block: Oracle will compact data blocks when Insert or Update statement attempts to use a block that contains enough free space to contain a new row piece, yet that space is fragmented so that the row piece cannot be inserted in a contiguous section of the block. Oracle does not dynamically compress space at the tablespace level (dropping and recreating a tablespace and then importing in objects to de-fragment a tablespace does not count as dynamic compression.) - Typical tablespace layout: SYSTEM, USER_DATA(+USER_INDX), TEMP, RBS, TOOLS. Assigning default and temporary tablespace to users, distribution of tablespace across disks. - Proper table and index sizing ([COR95] p.57). - Size in blocks required to hold table: greatest (4, ceil (ROW_COUNT / ((round (((AVAIL_IN_BLOCK - (initrans * 23)) * ((100 - PCT_FREE) / 100)) / ADJ_ROW_SIZE)))) * 2) where AVAIL_IN_BLOCK - actual number of bytes available in block (1958 if DB_BLOCK_SIZE=2048) 23 - number of bytes used in each initrans value PCT_FREE - percentage free specified for table ADJ_ROW_SIZE - estimated adjusted row size of each row in the table ROW_COUNT - estimated number of rows for the table initrans - initial number of transactions for the table - Size in blocks required to hold index: greatest (4, 1.01 * ((ROW_COUNT / ((floor((( DB_BLOCK_SIZE - BLOCK_OVERHEAD - (initrans * 23)) * (1 - (PCT_FREE)/100.))) / ((10+uniqueness) + number_col_index + total_col_length))))) * 2 ) where ROW_COUNT - estimated number of rows for the table DB_BLOCK_SIZE - database block size BLOCK_OVERHEAD - inventory space in each block (113 if DB_BLOCK_SIZE = 2048 initrans - initial number of transactions for the table PCT_FREE - percentage free specified for index uniqueness - 1 if index is unique, 0 - if not number_col_index - number of columns in the index total_col_length - estimated length of the index entry - Monitoring space allocated to tables and indexes ([COR95] pp.59-62). - Space for tables To populate the statistic columns in the DBA_TABLES the following command should be used: analyze table compute statistics; Then number of empty blocks in the table's extent can be extracted by: select Empty_blocks from SYS.Dba_tables where Owner = and Table_name=; To determine the percentage of blocks allocated, used and empty run the following script >>> Begin of script select A.Blocks "Total Blocks Allocated", ((A.Blocks - B.Empty_Blocks) / A.Blocks) * 100 "% Blocks Used", (B.Empty_blocks / A.Blocks) * 100 "% Blocks Empty" from SYS.DBA_SEGMENTS A, SYS.DBA_TABLES B where A.Owner = B.Owner and A.Segment_name = B.Table_name B.Owner =
and B.Table_name = >>> End of script - Space for indexes To analyze and validate index: analyze index validate structure; This command can also detect invalid index format or bad index structure and index entries that do not match row data, or a bad index. This commands also populate INDEX_STATS and INDEX_HISTOGRAM tables. INDEX_STATS tables should be used to determine the deleted space in the index. If deleted space is too big the index should be dropped and re-created again. - Free space in indexes. Oracle will NOT REUSE index space in blocks whose corresponding data rows have been deleted!!! This space is called as "dead entries". You should monitor each index by: 1. validate index 2. Examine INDEX_STATS dictionary view: select Lf_rows, /* Leaf rows */ Lf_rows_len, /* Length of leafs */ Del_lf_rows, /* Deleted leaf rows */ Del_lf_rows_len /* Length */ from INDEX_STATS where name = ''; 3. If Del_lf_rows_len / Lf_rows_len * 100 > 20% then drop and re-create the index to reclaim unused space. - Minimizing fragmentation and disk contention. Extent fragmentation: - data dictionary segments have zero fragmentation propensity and never fragment free space; - application data segments have zero fragmentation propensity if properly designed. Keep production and test systems separately; - rollback segments usually have zero fragmentation propensity. It depends on number extends above OPTIMAL size; - temporary segments have high fragmentation propensity because they are dropped by SMON as soon as the sort they are associated with has completed. SMON regularly compacts contiguous entries in the free extent table (FET$). Monitoring of fragmentation: - via DBA_FREE_SPACE, DBA_SEGMENTS, DBA_EXTENTS tables. - third party tools (PLATINUM). To minimize contention: separate redo logs(!) on dedicated disk; separate data from indexes, ... You should separate groups of segments representing objects with different behavioral characteristics among different tablespaces. It's very difficult task! Separate groups by: - separating segments with different backups needs; - separating segments with different day-to-day uses; - separating segments with different life spans; - Storage objects: TABLES, CLUSTERS and INDEXES. Segments, extents, blocks. Allocation of extents in tablespace. Maximum number of extents per segment. Extent space utilization parameters: INITIAL, NEXT, MINEXTENTS, MAXEXTENTS, PCTINCREASE. WARNING on cluster usage: ------- Transactions on clustered tables are 6-8 TIMES SLOWER then the same transactions on unclustered tables. Transactions on clustered tables generate ORDER OF MAGNITUDE greater rollback and redo information then un-clustered tables - Extent space utilization. - Table needs more space and more extents then MAXEXTENTS cannot be allocated. If you have not yet reached the maximum extents for a table, and wish to increase MAXEXTENT parameter use: alter table storage (maxextents 240); - Maximum possible number of extents per segment for versions below 7.3 depends on block size of database (version 7.3 has no limits): The first block of each segment contains an extent map for each extent. The maximum number of extents allowed is therefore a function of the database block size of each extent map entry and can be calculated as: MAXEXTENT = ((DB_BLOCK_SIZE - 24) / 2 - 44) / 8 This is a common extent mapping table: ORACLE BLOCK SIZE GREATEST MAXEXTENT VALUE 512 25 1K 57 2K 121 4K 249 8K 505 10K 633 Thereby, the MAXEXTENTS value may not be accurate because the MAXEXTENTS can not exceed the GREATEST MAXEXTENT VALUE. - Monitoring of extent allocation. What is the max. number of extents that MY_TABLE can occupy?: select Maxextents from SYS.DBA_TABLES where Table_name='MY_TABLE' and owner='ITSME'; How many extents does it have now?: select count(*) from SYS.DBA_EXTENTS where Segment_name='MYTABLE' and Segment_owner='ITSME'; DBA must track extent allocation and fix situations where tables have more then 10 extents allocated to them. DBA should monitor expansion of tables in your database and plan for table reorganization before you run out of extents. If the platform permits it, set your Oracle block size to 4K (4,096 bytes) or even 8K (8,192). DBA should know well how to re-create the database. However, for some OLTP applications 2K block will give the better result. - Runaway size (PCTINCREASE) Oracle uses 50% as default for PCTINCREASE. The size of extents can grow EXPONENTIALLY. Always use PCTINCREASE 0 (alter table storage (pctincrease 0) if you have no special ideas. For main table use manual adjusting of the extents by mean of alter table alter table allocate extent rather then adjusting the STORAGE parameter for the table. - INIT, NEXT ... parameters for temporary tables must be specified on tablespace level. - Database blocks. Block layout. - Control space usage. Block space utilization parameters (PCTUSED, PCTFREE, INITRANS, MAXTRANS). Row chaining and row migration. Effects of changing storage parameters. - Quotas for users on tablespaces: Q.: Assume that a user's temporary tablespace is SYSTEM; he has quota on two tablespaces USERS and DATA but not on SYSTEM. Will he be able to do on-disk sorts ? If yes, explain how. If no, explain the simplest way to "fix" this. A.: Yes, he will be able to do on-disk sorts since the temporary segments created for this are owned by SYS. - Allocating extents in Oracle v7.1.x - Without ALTERing PCTINCREASE: V71_RULE1: ORACLE v7.1.x will translate the specified extent size to number of oracle blocks and round them to the next multiplicand of 5. V71_RULE2: It will then try to allocate the number of blocks resulting from V71_RULE1 V71_RULE3: If it can't allocate the number of blocks resulting from V71_RULE1, if will try to allocate the exact number of blocks as requested. V71_RULE4: If it can't allocate the number of blocks resulting from V71_RULE1 and V71_RULE3, an error ORA-1547 will be issued V71_RULE5: If V71_RULE2 or V71_RULE3 succeed, Oracle v7.1 checks if <5 blocks are left free in the tablespace, and allocates them too in this case. - Allocating extents when altering PCTINCREASE. The algorithm for calculating the size of the new extent to be allocated in rdbms v7 uses simply the actual allocated size of previous extent: CALC_SIZE(ext#0) = round_to_5(INITIAL) CALC_SIZE(ext#1) = round_to_5(NEXT) CALC_SIZE(ext#n) = round_to_5(PHYS_SIZE(ext#n-1)*(1.0 + PCTINCREASE/100)) where round_to_5(X) rounds X to next multiple of 5 Oracle blocks and PHYS_SIZE(ext#m) = select Blocks from DBA_EXTENTS where Owner = Table_owner and Segment_name = Table_name and Extent_id = m - Extent fragmentation and block fragmentation. - Monitoring rows Give SQL queries for the following - a) The actual number of rows beginning in each block of a table: >>> BEGIN OF SCRIPT Select substr(rowid,1,8)||substr(rowid,15,4) "BLOCK", count(*) "ROWS" from group by substr(rowid,1,8)||substr(rowid,15,4) >>> END OF SCRIPT b) The number of blocks that actually contain data (as opposed to the number of blocks allocated for the table.): >>> BEGIN OF SCRIPT Select count (distinct(substr(rowid,1,8)||substr(rowid,15,4))) from ; >>> END OF SCRIPT You can also monitor these via ANALYZE TABLE statement which provides all the necessary information in a database table. - monitoring views for storage parameters (dba_tablespaces, dba_tables, user_tables,...) - Managing datafiles Q: How do you tell what the MAXDATAFILES parameter was set to upon database creation? In V6 and Oracle7?? A: Within V6, you can connect as 'sys' and do the following query to see the max datafiles parameter the database was created under: select count(*) from x$kcfio; Within Oracle7, you can dump the control file to a trace file and see what the create control file command has for maxdatafiles. To dump the control file use: ALTER DATABASE BACKUP CONTROLFILE TO TRACE To avoid recreating database always set MAXDATAFILES parameter to a very high number. Default is too low.