practical space management in data warehouse environments
DESCRIPTION
Practical Space Management in Data Warehouse Environments. Hamid Minoui Database Specialists, Inc. www.dbspecialists.com [email protected]. Objectives. To point out data warehouse space management issues Suggest resolutions Recommend space management methodologies - PowerPoint PPT PresentationTRANSCRIPT
1
Practical Space Management in Data
Warehouse EnvironmentsHamid Minoui
Database Specialists, Inc.
www.dbspecialists.com
2
Objectives To point out data warehouse space
management issues Suggest resolutions Recommend space management
methodologies Provide proactive prevention strategies Cover both Oracle 9i and Oracle 10g
space management features
3
Characteristics of a Data Warehouse
The data:– Large amount of data loads and ETL operations– Very large size (Terabytes)– Change in structure of source data– Contains lots of historical data– Data massaging and aggregations– Multiple sources of data– Dynamic nature of data
4
Characteristics of a Data Warehouse (continued) Maintenance activities:
– Space management– Table re-organizations– Index rebuilds– Partition maintenance– Refresh maintenance on materialized views– Job and scheduling management
5
Characteristics of a Data Warehouse (continued) Typical issues:
– Data integrity issues– Data security issues– Space issues– Query performance issues– Duplicate rows
6
Characteristics of a Data Warehouse (continued) Database features frequently used:
– Materialized views (MV)– Bitmap indexes and bitmap-join indexes– Index organized tables (IOT)– Parallel execution– Table and index partitioning– Table and index compression– Load utilities and facilities
7
Other Characteristics Star schemas, snow flakes or 3rd Normal Form Have dimensions and hierarchy Frequent need to collect statistics Use of bulk and parallel loads Variety in the generated queries Dynamic nature of queries Divided into areas (staging, ODS, and target area) Often associated with smaller data marts
8
Performance Tuning and Resolutions
Frequent query tuning Star transformation De-normalization Pre-aggregations via materialized views B*Tree, IOT, function based, bitmap, bitmap-
join indexes Use of database resource management
9
Why is Space a Coveted Resource in a Data
Warehouse? Lots of disk space is consumed Stores all enterprise data Segments are mostly large Many indexes Years of historical data kept online Many versions of the same data Duplicated and de-normalized data Various levels and dimensions of data (monthly,
weekly, daily)
10
Why is Space a Coveted Resource in a Data
Warehouse? Enough reserve space needed: – For daily/weekly/monthly growth– Recall offline old data when needed– Data correction– Materialized views and their growth– Emergency needs– Data files and tablespace growth– Temporary tablespaces
11
Reacting to Space Issues Down sides:
– Often, not enough time to react– Delay in the load– Wasted resources to reload
Up sides:– Loads are usually scheduled– Once data is loaded, most of it won’t change
12
Issues with Database Backups
in a Data Warehouse Too many files to backup every night Backup takes a long time to complete System resources busy during backup Possible licensing issues with third-party
backup software Restoring and recovery after a failure can take
a long time
13
A Typical Backup Strategy Make non-current table spaces READONLY every
month Perform a special backup of READONLY
tablespaces Exclude the READONLY table spaces from regular
hot backups Never backup temporary tablespaces
Caveat: You must wait until all transactions are committed
14
Avoiding Unnecessary Redo Log Generation
Create some tables and all indexes with NOLOGGING for any segment that can be re-generated without doing a database recovery:
• SQL*Loader with direct path load• CREATE TABLE AS SELECT from external or transient tables• INSERT using +append hint• Use global temporary tables
insert /* +append */ into transiant_tableselect * from source_table ;
create table transient_tableas select * from source_table ;
15
Speeding Up Bulk Load Operations
Before the load:– Make all non-unique indexes unusable– Disable the primary and unique constraints if the
source data is trusted– Disable all triggers on the table– Set the session to skip unusable indexes
16
Speeding Up Bulk Load Operations
Implement the load:– Use append and parallel hints with insert– Commit the transaction
After the load:– Rebuild indexes– Enable triggers and constraints
17
Space Issues in Data Warehouses
Permanent tablespaces (data, indexes) Temporary tablespaces (temp segments) UNDO segments and tablespace
18
Space Issues with Permanent Tablespaces Caused by:
– Poor extent sizing– Setting maxextents– PCT_INCREASE > 0– Small data files (tablespaces)– User quota on tablespace
19
Space Issues with Temporary Tablespace
Caused by:– Not enough space for the sort segments– Other temp segments such as global temporary
tables– Multiple users sharing the same temporary space– Multiple queries with sort requirements running at
any time
20
Space Issues with Temporary Tablespace
Partially resolved by:– Oracle 9i - Dynamic PGA memory allocation
• PGA_AGGREGATE_TARGET=<integer value>• WORKAREA_SIZE_POLICY=AUTO
– Oracle 10g - Tablespace Group assignment
21
Space Issues Associated with Undo Segments
Long running queries causing ORA-1555 (snapshot too old)
Small UNDO tablespace Small rollback segments
22
Database Block Size (DB_BLOCK_SIZE)
Should seriously be considered An important decision with new data
warehouse projects Inappropriate value can be disastrous and
detrimental Small value can:
– Impact I/O efficiency for majority of queries– Negatively influence overall database performance
23
Appropriate DB_BLOCK_SIZE Value
Multiple of the OS block size As large as your I/O subsystem can handle in
a single read As large as supported by Oracle Best benefit from larger block size if:
– Database is configured on raw devices, or– Direct I/O is available to you.
24
Benefits of Larger DB_BLOCK_SIZE Value
Efficiency with index scan– A larger block size reduces the number of reads required to
probe an index and scan a range of values from its leaf blocks
Less memory requirement for buffer cache– Fewer buffers needed for index branch blocks
Better compression ratio for tables, indexes Improvement in block density
– Amount of space used by fixed portion
of bock header is reduced
25
Benefits of Larger DB_BLOCK_SIZE Value
Blocks can accommodate longer rows; less chance for row chaining
Less occurrence of ORA-1555– Increase in size of the transaction table in undo
segments header blocks
Fewer writes required for data loads– Because of the reduced block level overhead, less
redo logs are generated when blocks are modified sequentially
26
Disks, I/O and Database Files Configuration
A poorly configured I/O subsystem can badly impact I/O performance
Poor I/O performance can impair a data warehouse
Configure disk and distribute data for read and write efficiency
Use raw I/O if possible, otherwise use direct I/O Make use of asynchronous I/O, parallel read and
parallel writes
27
Disks, I/O and Database Files Configuration
Stripe and Mirror or Mirror and Stripe the disks – RAID-1+0 or RAID-0+1
Evenly spread your data and Stripe And Mirror Everything (SAME) on many disks
Reserve room on file systems for auto extendable files
28
Managing the UNDO Segments
Manual undo (rollback segments) management– Pre Oracle 9i practices– Too many manual interventions by DBA
29
Managing UNDO (continued)
Automatic Undo Management (AUM) – Much better – Highly recommended– Allows controlling retention of committed
transactions undo information (UNDO_RETENTION)
– Better monitoring statistics– Infrequent occurrence of ORA-1555– SMON periodically manage space and shrinks
undo segments
30
UNDO_RETENTION Parameter Setting
Set to a value equal to the time used by the longest running query
Undo is ‘expired’ when retention time is reached Expired undo will be de-allocated if needed by
new transactions Unexpired undo are re-used if space is needed
(undo reuse) Default value is 300 seconds
31
Undo Reuse and Undo Stealing
Undo Reuse:Unexpired undo of the same segment will be reused
Undo Stealing:Unexpired undo of another segment is used
Undo reuse is more common. Occurs when– UNDO tablespace is too small, or– UNDO_RETENTION value is too large
32
Monitoring the UNDO Segments Statistics
Statistics are gathered in V$UNDOSTAT every 10 minutes
Helps sizing UNDO tablespaces and tune UNDO_RETENTION
Statistics are retained for 7 days
33
V$UNDOSTAT
BEGIN_TIME Beginning time for this interval
END_TIME Ending time for this interval
UNDOTSN Tablespace ID of the last active undo within the interval
UNDOBLKS Number of consumed undo blocks within the period
MAXQUERYLEN The longest length of time (in seconds) a query took to complete within this period
TXNCOUNT Total number of transactions executed with the period
34
V$UNDOSTAT (continued)
UNXPSTEALCNT Number of attempts to obtain undo space by stealing unexpired extents from other undo segments
UNXPBLKRELCNT Number of unexpired blocks released from undo segments to be used by other transactions
SSOLDERRNT Number of times ORA-1555 occurred with the period
NOSPACERRCNT Number of times space was unavailable in the undo tablespace when requested and failed
35
Tuning UNDO_RETENTION Oracle 9i:
– Manually adjust to the time taken by the longest query
SELECT MAX (MAXQUERYLEN) FROM V$UNDOSTAT;
Oracle 10g: – Automatically tracked and tuned by RDBMS
36
The UNDO Tablespace Created at DB creation or with CREATE UNDO
TABLESPACE Use V$UNDOSTAT for sizing and monitoring Space issues if UNDO_RETENTION is too large Use AUTOEXTEND RETENTION_GUARANTEE clause Sizing formula:
Undo Segment Space Required (MB) = (undo_retention * undo_blcks/secs * DB_BLOCK_Size)/1024
37
Database Fragmentation Issues
Best to reduce or eliminate fragmentation to avoid wastage and improve performance– Tablespace level (or file level) fragmentation– Segment level fragmentation– Block level fragmentation
38
Tablespace Level Fragmentation
Bubble Fragmentation– Free block of space not large enough for another
extent
Honeycomb Fragmentation– Free un-coalesced space next to each other but
considered separate
39
Segment Level Fragmentation
Space allocated to segment is not fully utilized (wasted)– Space above the high water mark (unused blocks)– Free segment blocks below the high water mark
40
Block Level Fragmentation Blocks are not empty but there is space within
a block that is not used Caused by:
– Setting of PCTFREE and PCTUSED– Deletions– Row migrations
41
Tablespace Planning Use locally managed tablespaces (LMTs) with
UNIFORM size extents– 64K bitmaps on file header are used to manage
extents– Improves performance and significantly reduces
overhead associated with updating dictionary tables (recursive SQL)
– No need to use ST enqueue– No more tablespace fragmentation
42
Tablespace Planning Use Automatic Segment Space Management
(ASSM)– Set at the tablespace level– Tablespace must be locally managed– Uses bitmap instead of freelist to manage space
within segments
43
Benefits of ASSM No more need for FREELISTS, FREELIST
GROUPS and PCTUSED Reduces segment level and block level
fragmentations Reduces the number of buffer free waits Adds efficiency to space usage Provides better use of space within the blocks
44
LMT Considerations The bitmap is 64K
– Make the size of each file a multiple of UNIFORM extent+64K
Storage parameters– Avoid setting them– If already defined on segments reorganize, or
rebuild with storage parameters matching tablespace
45
Multiple Tablespace Size Models
SAFE (methodology) Group segments according to size (3 groups) Use 3 tablespace model having different
UNIFORM extents Assign each group to one of the size model Develop a naming convention
Segment Size Extent Size Size Model
< 128 M 128 KB Small
>= 128 M & < 4 GB 4 MB Medium
>= 4 GB 128 MB Large
46
Tablespaces for Different Types of Segments
Separate indexes and tables– Better manageability– Different type of usage– Reduces wastage (indexes are rebuilt often in data
warehouses)
47
Adjust Settings of PCTFREE and PCTUSED Parameters Avoid using default values Set according to usage Most of the times PCTFREE=0 and
PCTFREE=99 should be enough If ASSM, no need for PCTUSED More compact data in blocks reduces waste
and improves I/O
48
Use Index Organized Tables (IOTs)
When most of the columns are indexed When associated tables are used Columns are pre-sorted Makes better use of space and improve
performance Good for certain data warehouse tables
49
Table Compression– Introduced in Oracle 9i R2– Improves read only operations and factors out
repetitive values within a block– Replaces duplicate values in a block with a
reference to a symbol table in the block– Very low CPU overhead to reconstruct the block– Significantly fewer blocks, leading to better I/O– Very flexible (not all blocks are compressed)– Associated with bulk load operations
50
Table Compression To compress a table use:
ALTER TABLE t1 MOVE compress;
To compress a table partition use:ALTER TABLE T1 MOVE PARTTION P1 compress;
Alternative way CTAS compress:CREATE TABLE T1 compressAS SELECT * FROM T1_UNCOMPRESSED;
Table or partition not available (locked) during move
Use DBMS_REDEFINITION for online move
51
To Get the Best Results To achieve the best compression ratio:
1. Analyze the table to get column statistics SELECT COLUMN_NAME, NUM_DISTINCT, NUM_NULLS, AVG_COL_LENFROM DBA_TAB_COLUMNS ;
2. Identify best candidate columns for sorting as columns with• Lowest number of distinct values (low NUM_DISTINCT)• Least amount of null values (low NUM_NULLS)• Longest average length (high AVG_COL_LEN)
3. CTAS compress and use order by candidate_column
52
Table Compression Limitations
Can not be used on LOB field Can not be used for IOTs Can not compress tables with bitmap indexes With Oracle 9i, cannot drop or add columns to
compressed tables
53
Index Key Compression Introduced in Oracle 8i Compression of leading index columns Indexes are grouped into a suffix and prefix
entry– Suffix entry made out of unique pieces – Prefix entry consist of the grouping piece
Can offer significant space savings and better I/O performance
54
Index Key Compression Example
Current year’s Car Inventory table, index CAR_IND indexes columns are: Type, Color, Model
Before compression:
<SUV><Black><Rock Climber> <Sedan><Blue><Charisma>
<SUV><Black><Jungle Cruiser> <Sedan><Blue><Fantasy>
<SUV><Black><Mountaineer> <Sedan><Blue><Starlet>
…. …
55
Index Key Compression Example (continued)
ALTER INDEX CAR_IND compress 3; After compression:
<SUV><Black> <Rock Climber> <Jungle Cruiser> <Mountaineer>
<Sedan><Blue> <Charisma><Fantasy><Starlet>
….
56
Index Key Compression Partitioned indexes cannot be compressed Bitmap indexes cannot be compressed Can be defined on IOT Slight CPU overhead during index scan Consumes much less space Increases I/O throughput and buffer cache
efficiency Ideal for data warehouses
57
Identifying Keys to Compress
1. Validate or analyze the indexVALIDATE INDEX INDX1;
2. Query the index_stats viewSELECT NAME, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVEFROM index_stats;
3. Examine outputNAME OPT_CMPR_COUNT OPT_CMPR_PCTSAVE------- -------------- ----------------------INDX1 2 57
58
De-Allocating Unused Space Segment Level:
– Blocks above the segment high water mark (unused blocks)
– Space below the segment high water mark (free blocks)
Tablespace Level– Free space within tablespace– Data file level– Unallocated space above the highest allocated
extent (file high water mark)
59
Identify Segment Space Usage
DBMS_SPACE.UNUSED_SPACE– Information about amount of unused space in
segment and position of high water mark
DBMS_SPACE.FREE_BLOCKS– Information about the number of blocks on the
freelist groups
DBMS_SPACE.SPACE_USAGE– Information about the space usage of blocks under
the high water mark
60
De-Allocate Segment Free Space
Unused blocks-ALTER [TABLE | INDEX | CLUSTER] segment_name
DEALLOCATE UNUSED [KEEP nK ]
– De-allocates only space above segment high water mark, retaining space specified by KEEP
Other Unused space-–Pre Oracle 10g – Reorganize table, rebuild index
• Table move, export/import, DBMS_REDEFINITION interface)
–Oracle 10g – Online segment shrink
61
Two-Phase Online Segment Shrink
ALTER TABLE table SHRINK SPACE;– Phase 1:
• A series of DELETE and INSERT statements applied to move data to the beginning of the segment
• DML-compatible changes are held on rows and blocks
– Phase 2:• High water mark adjusted to the appropriate location.• Exclusive lock is held• Unused blocks (above high water mark) are de-allocated
62
One-Phase Online Segment Shrink
ALTER TABLE table SHRINK SPACE COMPACT;
With COMPACT keyword only the first phase is executed.
To implement phase 2, issue it without COMPACT keyword at a later time
63
One-Phase Online Segment Shrink (continued)
Restrictions– Row movement must be enabled– Triggers based on ROWID of table must be
disabled
In data warehouses, locking might not be a problem on some tables
64
De-allocating Space at the Tablespace Level
Caused by tablespace fragmentation– Index rebuilds, table moves, partition move, etc.– Not having UNIFORM size extents
65
De-allocating Space at the Data File Level
File size larger than the last block used in the file
Size over-estimated Auto extended
66
Shrinking Data Files
The statement:ALTER DATABASE DATAFILE ‘file_name’ resize n (K | M);
– Attempts to size the data file to exactly n K (or M)– It is safe. It will fail with ORA-03297, if there are
blocks of data beyond the requested resize value
ORA-03297: File contains nnn blocks of data beyond requested resize value.
67
Steps to Shrink Data Files to High Water Mark Position
1) Create a temporary table preferably a GTT
CREATE global temporary table SPACE_ADMIN_GTTON COMMIT PRESERVE ROWS ASSELECT FILE_NAME, TABLESPACE_NAME, BYTES, BYTES, BYTESFROM DBA_DATAFILES WHERE 1=0;
2) Create another table with name of tablespace to shrink
CREATE GLOBAL TEMPORAY TABLE SHRINKING_TBS_GTTON COMMIT PRESERVE ROWSASSELECT TABLESPACE_NAME FROM DBA_TABLESPACESWHERE TABLESPACE_NAME in (‘TBS1’,’TBS2’,’TBS3’);COMMIT;
68
Steps to Shrink Data Files to High Water Mark Position
(continued)3) Get DB_BLOCK_SIZE
column value new_val blksizeselect value from v$parameterwhere name = 'db_block_size';
69
Steps to Shrink Data Files to High Water Mark Position
(continued)4) Calculate the file’s high water mark and save
INSERT INTO SPACE_ADMIN_GTTSELECT file_name, tablespace_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savingsFROM DBA_DATA_FILES a, ( SELECT file_id, max(block_id+blocks-1) hwm FROM DBA_EXTENTS GROUP BY file_id ) bWHERE a.file_id = b.file_id(+)AND a.tablespace_name IN (SELECT tablespace_name FROM SHRINKING_TBS_GTT);COMMIT;
70
Steps to Shrink Data Files to High Water Mark Position
(continued)5) Generate ALTER DATABASE commands
column cmd format a95 word_wrappedset trimspool onSPOOL c:\TMP\dbf_resize.sqlSELECT 'alter database datafile '''||file_name||''' resize ' || smallest || 'm;' cmdFROM SPACE_ADMIN_GTTWHERE savings >= 5;SPOOL OFF
71
Automatically Resolving Space Issues
Oracle 9i Feature called RESUMABLE SPACE ALLOCATION
Allows an active session to be suspended if a space issue is encountered
The session resumes automatically when – Space issue is fixed– A timeout period (default: 2 hours) is reached
Beneficial for data warehouse environments
72
Steps for Resumable Space Allocation
1. DBA grants RESUMABLE privilege to user
2. User makes session resumable withALTER SESSION ENABLE RESUMABLE ;
3. If session encounters space problem, it is suspended
73
Steps for Resumable Space Allocation
4. If AFTER SUPSPEND TRIGGER exists, it gets executed
5. If trigger does not exit (or disabled) or if the trigger does not fix the space problem, session remains suspended
6. Session resumes when space problem is fixed or timeout value is reached
74
Other Helpful Space-Related Features
Oracle-Managed Datafiles (OMF) DBA_ADVISOR family of views Oracle10g Workload Repository (AWR) and
segment advisor Oracle 10g Grid Control for monitoring
75
Conclusion Oracle is consistent in offering new space
management related features in every release Should be used by DBAs for best practices They enhance performance, reduce waste,
improve availability, reduce frequency of failures, and provide better monitoring
Data warehouse operations that rely heavily on space and I/O performance benefit the most from these features
76
Contact Information
Hamid MinouiDatabase Specialists, Inc.388 Market Street, Suite 400San Francisco, CA 94111
Tel: 415/344-0500Email: [email protected]: www.dbspecialists.com