Databases Reference
In-Depth Information
Advisor, AWR, SQL Management Base (SMB), SPA, SQL Tuning Advisor, and SQL
Tuning Sets (STS).
Storage Management
Automatic Storage Management (ASM) is provided as part of the Oracle Database. First
introduced with Oracle Database 10
g
, ASM is used to manage pools of storage in des‐
ignated disk groups that store the database files. The database data is evenly distributed
(striped) across disks in a disk group for optimal performance. Data is typically mirrored
using ASM for availability. Because an ASM interface is provided through Enterprise
Manager, the database administrator can perform this critical management task.
Data in large Oracle Databases is often partitioned to provide a higher degree of man‐
ageability and availability. For example, you can take individual partitions offline for
maintenance while other partitions remain available for user access. This partitioning
capability is provided by the Partitioning Option and was introduced for Oracle8. Since
then, the types of partitioning supported have continued to grow in sophistication.
In data warehousing implementations, partitioning is sometimes used to implement
rolling windows based on date ranges. Other partitioning types include hash partition‐
ing (used to divide data into partitions using a hashing function and providing an even
distribution of data), list partitioning (enables partitioning of data based on discrete
values such as geography), interval partitioning (used to automatically create new fixed
ranges as needed during data insertions), reference partitioning (where a child table
inherits the partitioning strategy of the parent table), and virtual column partitioning
(defined by an expression linked to one or more columns). Many of these partitioning
types can be used in combination as “composite” partitions. Examples of composite
partitions in Oracle Database 12
c
include range-range, range-hash, range-list, list-
range, list-hash, and list-list, hash-hash, and interval-reference.
One of the goals of storage management is to minimize the amount of physical disk
required. Compression techniques are often combined with partitioning strategies as it
often makes sense to compress data on older nonchanging partitions to avoid perfor‐
mance hits during updates. Since Oracle9
i
Release 2, the Oracle Database has included
basic compression that typically provides two to four times compression for read-only
or inactive tables and partitions. The Advanced Compression Option provides two to
four times compression for OLTP databases where updates occur and has been available
since Oracle Database 11
g
. Hybrid Columnar Compression is available for Exadata
Storage and provides about 10 times compression for data warehouses and 15 times
compression for archiving data.
High Availability
Oracle defines a Maximum Availability Architecture (MAA) that addresses recovery
time objectives, recovery point objectives, and service level agreement (SLA) objectives
