Oracle Expert Blog

About Oracle Advisors

2012-01-28T22:16:00.003-05:00

The Oracle Advisory Framework

Oracle's Advisory Framework encompasses a set of performance tuning advisors that allow for an enhanced dimension of the cause of the problem, the problem involved, and the solution, with the most appropriate steps to be pursued at each stage. The framework is widely integrated with the Automatic Database Diagnostic Monitor (ADDM) and Automatic Workload Repository (AWR) reporting capabilities. I will carefully discuss at a later time how to approach the most typical scenarios in a production environment

Among the most important components of the advisory framework, a DBA can look at the following, namely:

Memory Advisor

Provides advice for SGA memory structures and PGA memory areas, very practical when using Automatic Memory Management. The SGA memory structures involved include the shared pool, the database buffer cache, the large pool, the Java pool, the streams pool.

SQL Tuning Advisor

This advisory component provides SQL Profiling, SQL Access Paths, statistics analysis, and structure analysis. This advisor can use the DBMS_SQLTUNE package to create, execute, and report a given task.

SQL Access Advisor

This advisor provides an analysis of the overall system performance through workload specifications, focusing on each segment structure, in particular indexes and materialized views in very critical scenarios. The specified workload could involve one of the following:

A simple SQL statement

A SQL statement tuning set

Current contents of the SQL Cache

Any hypothetical workload obtained through DDL of certain object sets.

Oracle Segment Advisor
This advisory framework component allows to plan on the capacity of a specific segment as needed, and forecast its future growth.

Undo Advisor

This Advisor is useful in the control of undo management and the necessary custom requirements to run specific processes.

MTTR Advisor

This advisory component establishes the appropriate settings to establish the appropriate windows of time for an optimal mean time to recovery, to minimize downtime, in general.

These advisors can be used either through the Oracle Enterprise Manager Control interface or through the DBMS_ADVISOR package, as convenient in each scenario. Advisors have more value when they can also be utilized with the resource manager and specific plan directives that can effectively produce the desirable custom outcomes.

Oracle Unveils Solaris 11 in New York

2011-11-10T14:39:00.014-05:00

Oracle Solaris 11, simply extraordinary

Yesterday, November 9, I attended the launch of Oracle Solaris 11. The presentations were lead by keynote speakers, Oracle President Mark Hurd, and Executive Vice President, John Fowler, who introduced the new features of what Oracle now calls the first operating system fully intended for cloud computing.

Among the key features highlighted, I can highlight the following:

Co-engineered with Oracle Stack
Native support to run with SuperCluster, T-4, and Exadata/Exalogic servers and ZFS storage appliance for optimal Cloud elasticity
ZFS Virtualized Pool Storage
Built for Cloud Infrastructures
Leading transactional capability through Oracle Sun servers benchmarks
Fault-tolerance automatic troubleshooting monitoring
Enhanced built-in security for cloud authoring and authentication
Native virtualization for the cloud
Stack-driven fault tolerance
High-availability capability
Unified Upgrade/Patch transparent method and strategy
Built-in Support for Solaris10 environment
Multi-tenancy built-in support
Zone/Container Resource Management
Unlimited Boot Environments

After the keynotes presentation, a panel of Oracle Sun engineers followed, involving an interesting discussion on the several years Oracle Sun Solaris 11 took to complete.

Finally, a customer panel followed the engineers' discussion, in which customers related their previous experience was recounted. Customers also listed their expectations on how they will upgrade to and work with Solaris 11.

Over 700 customers worldwide have already adopted Solaris11 for their production environments, by the time its Oracle official launch took place at Gothan Center in the City of New York on November 9, 2011.

Excerpts from the Oracle Solaris 11 Launching Event

John Fowler makes his keynote speech on Solaris11

An Oracle Sun engineer discusses a new feature in Solaris 11.

AnSteve Wilson, Oracle VP of Engineering, discusses a new feature in Solaris

An Oracle Sun engineer discusses a new feature in Solaris 11.

For a complete view of the New York launch presentations, you are welcome to visit the Oracle website at:

http://www.oracle.com/us/corporate/events/solaris11launch/index.html

The Present and Future of Datawarehousing

2011-09-29T19:01:00.005-04:00

About Oracle Data Integrator
While companies continue to hire novice people in the data warehousing field, i.e., IT professionals with three years of experience or less, the set of compliance regulations have significantly increased, thus, giving this field an additional dimension of complexity. This is confirmed by a recent Gartner Research report.

Conventional data warehousing is about extracting, transforming, and loading data from one or several data sources, to attain integration through a variety of staging business processes, an possibly into a data mart. The process of ETL (Extract-Transform-Load) is quite traditional and conveys a set of constraints, such as, in most instances where the transformation is performed row-by-row, such that load can take place. E-LT (Extract-Load-Transform) is a modern approach where customized bulk load can occur without the need to be constrained by individual row-by-row transformations.

Oracle Data Integrator provides support for up to 10000 different concurrent data sources independently from their type, including a variety of structured and unstructured data sources, such as, relational and object relational databases, XML, flat files including csv and tab-delimited among others, various multi-dimensional data sources, message queuing and various other data source types.

Oracle Data Integrator is an ideal data warehousing tool to work with technologies such as Exadata and Oracle Solaris SuperCluster T4-4 (just released), as it provides seamless designing capabilities and transformations through bulk E-LT. Besides, Oracle Data Integrator is quite versatile in interaction with other tools such as Oracle Golden Gate, allowing for fast advanced replication, including both full replication and thin provisioning.

Other important capabilities available with Oracle Data Integrator involve the possibility of performing both complex traditional ETL (Extract-Transformation-Load) and fast bulk E-TL (Extract-Transform-Load). While the former allows row-by-row transformation, the latter allows bulk on-the-fly transformation, for which this stage can essential occur at any time in the datawarehousing timeline. This capability in conjunction with Oracle's demonstrated datawarehousing leadership by quadrant positions ODI as the datawarehousing tool of the future not only for conventional datawarehousing environments but also for the Big Data landscape. This feature also enhances its unique ability to reach optimal data quality while attaining outstanding performance and throughput and minimizing latency.

In addition to this, as part of the Oracle Fusion Middleware, ODI is extremely hot-pluggable. It can friendly interact with Oracle WebLogic, BPEL Process Manager, TopLink, JDeveloper, and Oracle Identity Management.

Concluding Remarks

Oracle Data Integrator is a comprehensive, solid datawarehousing tool that allows the fast and comprehensive, design, implementation, and development of a datawarehousing and reporting datamarts through the appropriate usage of bulk E-LT processing, highly relevant for the Big Data and general datawarehouse performance. Among the top benefits to adopt ODI, it is possible to mentioned reduced DW cost with best ROI, great flexibility, outstanding data quality, and data integrity and accuracy with increased productivity.

Solid State/Flash and Optical Storage Technologies

2011-05-29T10:22:00.002-04:00

The Solid State Disk Single Level Caching and the Future of Database Technology

The implementation of faster more resilient database architectures on the basis of flash drives using Single-Level Caching (SLC) is key to the engineering of new databases technologies. At the present time, database languages like SQL (Oracle, SQL Server, DB2) and QUEL (Ingres) can analyze, query, mine, dredge, and project and forecast based on (the behavior) of data, i.e., information that is static or persistent by nature, even in the event when it only takes a few seconds or a fraction of a second. SLC caching will at some point to track the more dynamic behavior of some data, e.g., the brownian motion of particles in some kind of physics chaos, such as the agitation of some fluid, or the movement of electrones and other subatomic particles; the real-time measurement of heartbeats and other vitals signs could be further measure with a more consistent level of granularity. However, the most important contribution of SLC, which currently provides the best performance among Solid State Drive technologies, is to provide the possibility to interconnect to active or dynamic live automata in general, that can mimic the exact behavior of the brownian motion, vital measurements, stock market behavioral simulators and other live simulation scenarios. Because of this, I believe that iSCSI technology will complete the dominance of the market as they can provide better interaction with “live dynamic data” store in the newly flash storage technology in interaction with other small computer interfaces and devices. Multipathing in conjunction with Oracle technologies like Automatic Storage Mangement (ASM) will provide further capability and support, such that Fiber Channel, FICON in general, can also provide support for such new technologies technologies. Perhaps, optical storage will add another component of managing “real-time live data”, and the LG Hybrid drive released by Hitachi in the last quarter of 2010 is probably the most recent development in that respect.

While there are some Oracle cartridges capable to interact with SQL, such as in Text, Multimedia, and spatial technology, or an XML-driven implementation such as xQuery, there will be a need to provide an API such that SQL can interact with the real-time nature of the dynamic behavior described for such “live data”, which could probably be store in real-time as a tuple describing the state and behavior of an live automata object-data with greater precision than today's object-relational models.

Thanks to Oracle Professionals at Collaborate11

2011-05-20T21:13:00.000-04:00

I would like to break this blog protocol to thank all attendees at my presentations at Collaborate11, in particular, those attending my presentation on Engineering Oracle ASM Optimal Storage I/O Performance with Intelligent Data Placement on April 12, a presentation on Intelligent Storage for High-Performance Computing, for such a great feedback. I look forward to see you again at the next great Oracle event.
Once more thank you!

Presenting on Forecasting Analytics at Collaborate11, Orlando, Florida

2011-04-13T15:23:00.002-04:00

I will be presenting Perspectives on Forecasting Analytics at Collaborate11, on Thursday, April 14 from 11:00AM until noon. This business intelligence venue will take place in Room West 303C.

Everybody is welcome to attend.

Visit adnresearch.com

You are invited to my Intelligent Storage Presentation at Collaborate11

2011-04-11T14:37:00.011-04:00

Please join Anthony D Noriega at Collaborate11, at the Orlando Orange Convention Center in Orlando, Florida on Tuesday, April 12, 4:30PM to 5:30PM, Room West 308C for his presentation on Intelligent Storage for High-Performance Computing: "Engineering Oracle ASM Optimal I/O Performance with IDP."

Everyone is welcome!

Performance Tuning for the Storage Layer

2011-03-23T17:26:00.008-04:00

Intelligent Storage for High-Performance Computing (HPC)

The following is an excerpt of my intelligent storage paper using ASM technologies, involving ASM Cluster Files System (ACFS), ASM Dynamic Volume Manager (ADVM), and Intelligent Data Placement (IDP).

The articles proposes an array of recommendations and practical strategies to optimize server performance from the storage layer up. The discussion focuses on the usage of traditional disk drives (HDD) and Solid State Drives (SSD).

Implementation on Traditional HDD Disk Arrays

This is done by creating a custom template and applying to appropriate ASM datafile and subsequently determining relevance to logical objects. The SQL API is shown in the example below:

ALTER DISKGROUP adndata1 ADD TEMPLATE datafile_hot
ATTRIBUTE ( HOT MIRRORHOT);

ALTER DISKGROUP adndata1 MODIFY FILE '+DATA/ADN3/DATAFILE/tools.256.765689507'
ATTRIBUTE ( HOT MIRRORHOT);

Dual Pairing Drive (Hybrid) Solution

This solution seeks the following goals:

SSD for performance

Whenever SSD become part of the storage performance tuning strategy, it is necessary to plan on a more consistent space management, meaning that overprovisioning is an critical requirement to attain optimal storage elasticity. This is primarily due to the asymmetry of disk read/write speed on this technology. Depending on the disk controller used by its vendor, an SSD can have a 10:1 normalized ratio when experimentally tests have been conducted by various vendors where reading tasks represent about 65% of the entire transactional workload. Overprovisioning, in general, can also account for a reduction in manual rebalancing tasks when they seem to be required, such as moving an ASM file from one disk group to another either via PL/SQL packages or RMAN, could result in the disruption of production tasks. Similarly, overprovisioning can prevent the moving of logical objects across the database to attain better performance optimization.

Thus, both DRAM SSDs and NAND SSDs have much better IOPS costs than high performance hard disks (HDDs). Besides, Flash SSDs are very well suited to read-only applications and mobility applications, and they are highly cost-effective in industries such as media, where its default storage format UFS (Universal File System) is widely utilized.

Besides ASM can reciprocally help SSD technology by ensuring that the writing of data is spread out evenly across the storage space over time. Consequently, to facilitate wear leveling, SSD controllers implement a virtual-to-physical mapping of Logical Block Addressing (LBA) to Physical Block Addressing (PBA) on the Flash media and sophisticated error correction codes.

HDD for capacity

Traditional hard disk drives (HDDs) can be used to attain the implementation of low-cost high-capacity volumes. Therefore, the hard disks have a cost per gigabyte which is significantly better than solid state.

Hybrid Pools: Implementation on HHDD

Where Disk Groups are transparently built on the mix of these technologies.

In this scenario, it is important to establish the following considerations:

Establish the hybrid ASM read preferred read groups

SQL> ALTER SYSTEM SET ASM_PREFERRED_READ_FAILURE_GROUPS= 'HYBRID.HHDD';

Decide how to best use the SSD/Flash technology for speed and performance of database online redo logs.1 Set the logs on pure SSD drives. In doing so, this could imply that the Oracle DBA Architect might need at least to create a disk group or set of disk groups solely on SSD drives2.

Consistently, tables can be associated with tablespaces that have been designed on the basis of ASM data files, which have already been set as mirror hot or mirror cold templates, accordingly. These maintenance activities can occur as a baseline, for instance, when the database is created (first loaded), migrated, or upgraded accordingly, and systematically when a relevant event occurs. For instance, a partitioned table, using partition pruning, will require to change the most active partition due to a partition interval or time range issue, which will make a different or new partition the most dynamic segment, which in turn may require to be set in a mirror hot sector for optimal performance. In general, business algorithms can be customized, implemented, and set for periodic ASM storage maintenance in order to attain optimal performance continuously.

Introducing Best Practices for Oracle ASM IDP and Dynamic Storage Tuning

The models presented here can easily be enabled in conjunction with practical industry cloud storage implementations, which apply to both the Open Cloud Computing Interface (OCCI) and Cloud Data Management Interface (CDMI) standards. For Oracle practitioners, it is important to use and apply existing environment knowledge, such as derived from Real application Testing (RAT) prior to accomplish the actual implementation.

The Hot-or-Cached Storage Optimization Model (HoCSOM)

In this model, the purpose is to explicitly customize access and optimize performance for specific database objects, in particular, tables, indexes, materialized views, and all types of object partitions, namely, table, index or materialized view partitions, which have critical current relevance directly impacting performance tuning.

The following rules are reasonably practical in order to attain an adaptive optimal tuning, as follows:

Classify the set of database objects in at least three hierarchical tiers based on segment size and segment utilization levels.

Implement operating procedures to attain a smart database object allocation in the appropriate physical and logical space in an optimal fashion with a dynamic Oracle Automatic Storage Management perspective, as follows:

Tier 1 should include a small number of objects, in comparison to the total database object conglomerate, which require special storage handling due to size and activity.

Tier 2 will include average table in both size and usage or large tables with low level of usage, and average actively small tables.

Tier 3 will include smaller tables, and rather inactive tables, in general.

Tier-Based Storage Optimization Model (TBSOM)

This model will either utilize a combination of SSD (NAND/Flash) and Traditional HDD disk arrays in order to implement the relevant object-storage devices (OSD), i.e., Oracle ASM disk and disk groups.

The Flash capability serves partly as a way to overprovision the SSD technology when update of blocks could become expensive, as they require that the entire block is first deleted and then rewritten in full. The Flash acts as a mechanism that buffers transactions to avoid SSD latency. In some scenarios, reducing the actual size of the SSD for ASM is useful to both ASM failgroup mirroring and overprovisioning for the purpose diminishing the latency effect caused by writing on SSD devices, in particular, for updates, deletes, and inserts on used blocks. It is also important to remember that in most SSD technologies the hot sectors of the drives or volumes are automatically cached, i.e., in Oracle technology there would also be in the buffer cache or the shared pool.

This model requires the setting of the ASM_PREFERRED_READ_FAILURE_GROUPS accordingly, but does not require the implementation and application of customized MIRRORHOT templates.

Combining both Models: Hybrid Tuning Storage Optimization Model (HTSOM)

The two models can be combined, since there is no constraint that implies so, in general. However, this should be accomplished following the recommendation of Oracle Sun or any third-party vendor accordingly.

This model will require both the setting of the ASM_PREFERRED_READ_FAILURE_GROUPS and the implementation and application of customized MIRRORHOT templates, as needed for optimized database object allocation and storage performance. When using the combined model, the goal is to set the MIRRORHOT data files on the ASM Preferred Read Failure Groups to implement an optimal storage configuration.

Scenarios and Strategies

The following scenarios are both applicable to OLTP and Data Warehousing, as well as hybrid databases.

Working with Different Disk Array Pool Types

When this scenario occurs, it is important to establish the ASM preferred read disk groups, which enables a transparent way to establish storage hierarchies.

Working with Large Logical Database Objects

The models presented here allows for the tuning of the physical model at the storage level in a highly customized fashion, allowing for an optimal allocation. Thus, the Hot-or-Cached Storage Optimization Model presented allows for a large logical database object to have the best residence for optimal performance.

Working with Oracle Partitioning

When using partitioning technology, the following operations are possible or likely to occur:

Creating a new partition onto a tablespace using a MIRRORHOT ASM datafile, accordingly.

Moving a partition to a hot ASM datafile tablespace, onto an ASM datafile previously associated with a MIRRORHOT template and ASM data file.

Exchanging a partition when it becomes current to be associated with the appropriate MIRRORHOT tablespace ASM datafile.

Working with RMAN Backup and Recovery

RMAN can serve to manage ASM data files and move them between disk groups, as needed to set these files onto Oracle ASM Preferred Read Disk Groups or onto a MIRRORHOT ASM data file.

Using Oracle ASM ACFS and ADVM

The usage of these technologies should be enabled in order to apply Intelligent Data Placement (IDP) API to attain optimal storage performance tuning. This is done via the init.ora ASM parameters COMPATIBLE.RDBMS, COMPATIBLE.ASM, and COMPATIBLE.ADVM. By setting these parameters to 11.2 or later, the Oracle ASM Intelligent Data Placement API will be fully available for dynamic usage either manually or in an automated fashion. However, the compatibility can also be explicitly set when creating or altering a disk group via the SQL*Plus command line interface. Consequently, ASM volumes built on specific disk groups will inherit the settings and properties established for those disk groups, and the principles presented can be extended and applied at the ACFS and ADVM levels.

CREATE DISKGROUP DATA1
NORMAL REDUNDANCY
FAILGROUP ADNFG1
DISK '/dev/sda1', '/dev/sda2','/dev/sda3', '/dev/sda4'
FAILGROUP ADNFG2
DISK '/dev/sdb1', '/dev/sdb2','/dev/sdb3', '/dev/sdb4'
ATTRIBUTE 'compatible.rdbms' = '11.2',
'compatible.asm' = '11.2';

ALTER DISGROUP DATA1 ATTRIBUTE 'COMPATIBLE.ASM' = '11.2';

The compatible attributes can only be advanced to a more recent version.

Maintenance Operations

Maintenance operations on ASM IDP for storage optimization are capable to either maximize or optimize performance through the usage of IDP API. For instance, the activation of a new large table partition by setting the newly created partition into a Oracle ASM data file residing on a hot sector will significantly have a positive performance tuning effect over the physical model and the top active SQL statements.

Heating Up Database Logical Objects

Database logical objects can be heated up at creation time by specifying the appropriate hot tablespace. But they can also be altered and moved to the appropriate tablespace using the MIRRORHOT ASM data file. Likewise, this can happen as well when using object partitions at creation, exchange, or move time.

Changing Logical Objects Tiers

This can happen, for instance, when a highly active table or index partition utilization level changes due to an event, such as, for instance, the start of a new fiscal year, which implies that the current partition will no longer be used as often, such that the next current partition will gain a higher relevance. Therefore, an object partition, such as a table partition, local or global index partition or materialized view partition, could well be downgraded to a lower performance tier when it is affected by such an event. This is in order to take advantage of the partition pruning effect in order to use only the hot sectors when appropriate.

Moving ASM Data Files

Moving ASM data files across the ASM disk groups is usually a costly task, and normally not recommended unless strictly necessary. However, this can be accomplished using the ASMCMD utility CLI interface, via the usage of RMAN DATAFILE COPY command, or via the PL/SQL utilizing the the DBMS_FILE_TRANSFER.COPY_FILE and DBMS_FILE_TRANSFER.PUT_FILE methods or the DBMS_DISKGROUP package.

RMAN> SQL 'ALTER DATABASE DATAFILE 7 OFFLINE';

Likewise, for Oracle Managed Files (OMF), the DBA could use a similar command to the one shown below:

RMAN> COPY DATAFILE
'+DATA/ADN3/DATAFILE/tools.256.765689507'
TO '+DATA/ADN7';

For non-Oracle managed files, the DBA must include both the directory and file name when declaring the target in the COPY DATAFILE clause:

RMAN> COPY DATAFILE
'+DATA/ADN3/DATAFILE/tools_01.dbf'
TO '+DATA1/ADN7/DATAFILE/tools_01.dbf';

The DBA will need to rename the file accordingly regardless of the method used to move the file, as shown below:

Using RMAN
RMAN> SWITCH DATAFILE
'+DATA/ADN3/DATAFILE/tools.256.765689507'
TO COPY;

Using SQL

SQL> ALTER DATABASE RENAME FILE
2 '+DATA/ADN3/DATAFILE/tools.256.765689507'
3 TO '+DATA1/ADN7/DATAFILE/tools.264.765695815';

When applicable, perform the appropriate recovery:

Recover the new ASM database file:

SQL> RECOVER DATAFILE '+DATA1/ADN7/DATAFILE/tools.264.765695815';

Bring the new ASM database file ONLINE

SQL> ALTER DATABASE DATAFILE '+DATA1/ADN7/DATAFILE/tools.264.765695815' ONLINE;

Delete the old ASM database file from its original location

SQL> ALTER DISKGROUP DATA DROP FILE '+DATA/ADN3/DATAFILE/tools.256.765689507';

Overprovisioning SSD Drives

Overprovisioning is normally already built into the SSD technology to compensate for the lack of symmetry between reads and writes. Statistics show that under a typical 65% reads and 35%, there is a trend for 10:1 r/w performance ratio, meaning that reads are about 10 times faster than writes. In many database scenarios, the latency is caused because a block involved in a transaction must be completely erased before it can be rewritten. Performance on a SSD drive various based on the drive level of utilization. Thus, it will be excellent Fresh Out of Box (FOB), it will decay and go into a transient state (Transition) based on the manufacturer and will attain a Steady State with maturity. Therefore, when building pools of dual drives, i.e., hybrid arrays, the architect should follow the vendor's specific recommendations, and not assume that pros or cons apply in exactly the same way for all vendors.

Overprovisioning the drive means that it will have a cache allowing the buffering (holding of old blocks and writing of new blocks) to minimize the SSD latency. This cache can be implemented in Flash technology or using the same SSD NAND semi-conductor technology as well.

Overprovisioning on Hybrid Array Pools and SSD Pools

Overprovisioning is usually accomplished by adding a Flash cache to the SSD pool to overcome the latency of rewriting a block, which is much slower than originally reading it. This is usually part of the controller cache. In these architectures, flash memory is used by the controller for the purpose of assisting in garbage collection and alleviating some SSD write-related latency issues.

Overprovisioning on HHDD (Flash and HDD)

In this scenario a flash cache is used to buffer a certain amount of transactional data as it is written. This is built-in in many appliances, such as on EMC2 and NetApp storage appliances and in some systems such as the newest Apple Mac's and Oracle Sun Exadata database machines.

Integration with Other Technologies

The following technologies could be associated to further improve performance in conjunction with the storage optimization model presented, namely:

Integration with Cache Management
Using Multiple-block-size Database
Using Coherence (Oracle Data Grid)
Using Oracle Times-Ten.

Recently, Hitachi introduced the LG Hydrive a Dual (Hybrid) drive using an Optical Disk Drive (ODD) and Flash memory as part of its cache component.

Expected Results

Studies based on historic measurement of Jim Gray's 5 minute rule up to today's trends, show that not only we can attain a 25% improvement with ASM disk groups based conventional HDD arrays, but appliance-driven solutions using dynamic storage management over hybrid solutions have reached at least 62% improvement and up to 1000% times, i.e., 10 times better performance, based on 100% solid state disk groups. The study was held by IBM on OLTP databases using appliance-driven storage management and custom algorithm for tuning. This study did not yet utilized the Hot-or-Cached Storage Optimization model, and were fully guided by hot block level tuning. Therefore, the possibilities are endless and the market is opened to mix, match, and customize every possible solution.

There will be a significant cost reduction based on the power consumption with the usage of SSD technology, and the optimal usage of IOPS utilization.

Concluding Remarks

With Oracle11g Release 2, enterprise storage can now benefit of ASM elasticity, and the ability to customize storage optimal performance for a specific database, by engineering ASM production maintenance practices that optimize object allocation onto hot sectors, which use tablespaces related to ASM data files utilizing MIRRORHOT templates.

At the time when this paper is written, due to econometric reasons, hybrid models using both SSDs and HDDs technologies are more practical, very convenient, and highly cost-effective for most industries and enterprise sizes. While SSD technology can reduce the cost associated with energy due to various factors, until the price of solid state technologies drops significantly, the hybrid dual disk drive, using SSD/Flash for performance and HDD for capacity, will remain the de facto standard to implement ASM-based optimal storage architectures and infrastructures. Although, there are some industry-wide reliability pros and cons on SSD disks, it is expected that sometime in the future, when economics of scale will allow so, SSDs will become the predominant storage technology used with ASM.

In general, Oracle ASM IDP can improve I/O performance with dynamic load balancing. Oracle DBAs and database architects should plan any reorganization tasks for optimal performance giving greater relevance to the object-level rather than ASM file level, while also considering preferred read disk groups as a transparent method to establish storage hierarchies.

N.B. The enitre paper can be read at and retrieved from my new website under construction, namely:

http://www.adnresearch.com/resources/2011_726_noriega_ppr.pdf

2010-10-25T20:45:00.009-04:00

Engineering Dynamic Optimal Storage with Oracle ASM

The possibility of customizing storage performance is no longer left to the many products which have the capability to dynamically reorganize most dynamic or active data block onto hot volume sectors, i.e., those sectors whose band width and overall response time entices better I/O performance, usually supporting enterprise applications.

In the case of Oracle databases most active blocks are normally determined by segments associated with logical objects, such as tables. Indexes or materialized views. Furthermore, the capability of aligning storage performance with more dynamic objects is quite relevant to ASM technology, and a core value in Exadata’s database machine elasticity. ASM unique’s capability to perform rebalancing operations at the ASM file level and based on its unique extend indexing allows for an optimal rebalancing not only on conventional rebalancing operations but on a smart implementation engineered on the basis on Intelligent Data Placement.

The key idea is to be able to categorize segments associated with logical objects both by size (top-down) and in relation to their level of utilization, in particular, with respect to top most queries and database statistics. Both a size indicator and a level of usage index can be implemented. Furthermore a compound index can be finalized by finding the product of both size indicator and index value, which can be then subject to a mathematical norm, whose outcome will allow an Oracle DBA or Architect to categorize that segment as a candidate for Mirror Hot sector or Mirror Cold, otherwise. Three levels can be implemented with the highest level being applicable to the Mirror Hot sectors, and the lowest to the Mirror cold sectors.

[Compund Index] = [Size Indicator] x [Usage Level Index]

Based on the compound index, an object with a high compound index value should use the best slots in the hot sectors, which actually used the best band width and head speed in the physical disks building the ASM drives.

This is done by creating a custom template and applying to appropriate ASM datafile and subsequently determining relevance to logical objects. The SQL API is shown in the example below:

ALTER DISKGROUP adndata1 ADD TEMPLATE datafile_hot
ATTRIBUTE ( HOT MIRRORHOT);

ALTER DISKGROUP adndata1 MODIFY FILE '+data/adn3/datafile/tools.255.765689507'
ATTRIBUTE ( HOT MIRRORHOT);

Concluding Remarks

With Oracle11g Release 2, enterprise storage can now benefit of ASM elasticity, and the ability to customize storage optimal performance for a specific database, by engineering ASM production maintenance practices that optimize object allocation onto hot sectors, which use tablespaces associated with ASM data files utilizing MIRRORHOT templates.

Join me at Oracle OpenWorld 2010, Moscone Center, San Francisco, CA

2010-09-19T12:47:00.003-04:00

You are welcome to attend my presentation at the IOUG Forum.

ID#: S313474
Title: IOUG: Oracle Automatic Storage Management Load Balancing
Date: 19-SEP-10
Time: 16:00-17:00 PST
Venue: Moscone West L2 (Level 2)
Room: Rm 2007

2010-06-02T18:33:00.004-04:00

ASM Load Balancing

Anthony D. Noriega, MBA, MSCS, BSSE, OCP

Abstract

ASM brings the ability to leverage integrated volume management, usage, and hard drive access optimization beyond underlying formatting, by also reaching a minimum level of I/O contention. Understanding ASM processes, in particular RBAL, and the various strategies to attain optimization during regular operations requiring load balancing in various aspects is carefully discussed, and presented with key details, in particular adjusting the ASM Power Limit. Various scenarios involving specific command usage are introduced and clearly explained with focus on driving the best load balancing approaches and desirable outcomes, while monitoring ASM instance accordingly. Other tasks such as rolling ASM upgrades and ASM repairs are covered in relation to load balancing goals.

Background and Conceptual Framework

Both an Oracle ASM instance and an Oracle Database instance are built on the same technology. Like a database instance, an Oracle ASM instance has memory structures (System Global Area) and background processes. Besides, Oracle ASM has a minimal performance impact on a server. Rather than mounting a database, Oracle ASM instances mount disk groups to make Oracle ASM files available to database instances.

An ASM instance has two background processes, namely:

RBAL, which coordinates rebalance activities for disk groups.
ARBx, x=0,1,2..., which actually performs the balance of extents movements.

In the database instances, there are three background process to support ASM, namely:

RBAL, which performs global opens on all disks in the disk group.
ASMB, this process contact CSS using the group name and acquires the associated ASM connect string. The connect string is subsequently used to connect to the ASM instance.
O00x, a group slave processes, with a numeric sequence starting at 000.

Exhibit 1. ASM Global Architecture (Oracle ASM Instance with Oracle Database Instance.)

ASM and Oracle Clusterware

To share a disk group among multiple nodes, the DBA must install Oracle Clusterware on all of the nodes, regardless of whether Oracle RAC is installed on these nodes. Besides, Oracle ASM instances that are on separate nodes do not need to be part of an Oracle ASM cluster. However, if the Oracle ASM instances are not part of an Oracle ASM cluster, they cannot communicate with each other. Likewise, multiple nodes which are not part of an Oracle ASM cluster are unable to share a disk group.

Failure groups are defined for a disk group when creating an Oracle ASM disk group, after which, it is not possible to alter the redundancy level of the disk group.
Oracle ASM metadata resides within the disk group and contains information that Oracle ASM uses to control a disk group, including:

· The disks that belong to a disk group
· The filenames of the files in a disk group
· The location of disk group data file extents
· The amount of space that is available in a disk group
· A redo log recording information about atomically changing metadata blocks
· Oracle ADVM volume information

When Oracle ASM instances are clustered using Oracle Clusterware, there is one Oracle ASM instance for each cluster node. In contrast, when there are several database instances for different databases on the same node, then these database instances share the same single Oracle ASM instance.

Oracle ASM Striping

There are two primary purposes for Oracle ASM striping, namely:

· To balance loads across all of the disks in a disk group
· To reduce I/O latency

Coarse-grained striping provides load balancing for disk groups while fine-grained striping reduces latency for certain file types by spreading the load more widely.

Extents

The contents of Oracle ASM files are stored in a disk group as a collection of extents that are stored on individual disks within disk groups. Each extent resides on an individual disk. Extents consist of one or more allocation units (AU), and can use variable size to enable support for larger Oracle ASM data files, reduce SGA memory requirements for very large databases, and improve performance for file create and open operations. The initial extent size equals the disk group allocation unit size and it increases by a factor of 4 or 16 at predefined thresholds.

The extent size of a file varies as follows:

· Extent size always equals the disk group AU size for the first 20000 extent sets (0 - 19999).
· Extent size equals 4*AU size for the next 20000 extent sets (20000 - 39999).
· Extent size equals 16*AU size for the next 20000 and higher extent sets (40000+).

Initialization Parameters

The following initialization parameter file should be well known when balancing ASM load, namely:

ASM_DISKGROUPS

The ASM_DISKGROUPS is a dynamic parameter specifies a list of the names of disk groups that an Oracle ASM instance mounts at startup. In particular, Oracle ignores the value set for ASM_DISKGROUPS when specifying the NOMOUNT option at startup or when issuing the ALTER DISKGROUP ALL MOUNT statement. Besides, its default value is a NULL string.
The ASM_DISKGROUPS parameter is dynamic. When using a server parameter file (SPFILE), Oracle ASM automatically adds a disk group to this parameter when the disk group is successfully created or mounted. Similarly, Oracle ASM automatically removes a disk group from this parameter when the disk group is dropped or dismounted.

The following is an example of setting the ASM_DISKGROUPS parameter dynamically:

SQL> ALTER SYSTEM SET ASM_DISKGROUPS = ADNDATA1, ADNFRA;

When using a text initialization parameter file (PFILE), this parameter is edited in order to add the name of a disk group such that it is mounted automatically at instance startup.
The following is an example of the ASM_DISKGROUPS parameter in the initialization file:

ASM_DISKGROUPS = ADNDATA1, ADNFRA
ASM_DISKSTRING

The ASM_DISKSTRING initialization parameter specifies the comma-delimited list of strings that limits the set of disks that an Oracle ASM instance discovers. The discovery strings can include wildcard characters.

This discovery string format depends on the Oracle ASM library and the operating system that are in use.

For instance, on Linux servers that not using ASMLIB, to limit the discovery process to only include disks that are in the /dev/adndisks directory, set the ASM_DISKSTRING initialization parameter to:

/dev/adndisks/*

Oracle ASM cannot use a disk unless all of the Oracle ASM instances in the cluster can discover the disk through one of their own discovery strings, i.e., all disks must be discoverable by all of the nodes in the cluster.

ASM_POWER_LIMIT

The ASM_POWER_LIMIT initialization parameter specifies the default power for disk rebalancing. The default value is 1 and the range of allowable values is 0 to 11 inclusive. Indeed, setting a value of 0 disables rebalancing. Likewise, higher values enable the rebalancing operation to perform tasks faster, but could lead to higher I/O overhead as well.

ASM_PREFERRED_READ_FAILURE_GROUPS

The ASM_PREFERRED_READ_FAILURE_GROUPS initialization parameter value is a comma-delimited list of strings that specifies the failure groups that should be preferentially read by the given instance.

DB_CACHE_SIZE

The setting for the DB_CACHE_SIZE parameter determines the size of the buffer cache. The DB_CACHE_SIZE does not need to be set when using automatic memory management.

DIAGNOSTIC_DEST

The DIAGNOSTIC_DEST initialization parameter specifies the directory where diagnostics for an instance are located. It defaults to the $ORACLE_BASE directory for the Oracle grid infrastructure installation.

INSTANCE_TYPE

The INSTANCE_TYPE initialization parameter must be set to Oracle ASM for an Oracle ASM instance.

LARGE_POOL_SIZE

The setting for the LARGE_POOL_SIZE parameter is utilized for large allocations. In general, the default value for this parameter is suitable for most environments, e.g., 16M. This parameter is not be set when using automatic memory management (AMM).

PROCESSES

The PROCESSES initialization parameter affects Oracle ASM, but the default value is usually suitable. However, if multiple database instances are connected to an Oracle ASM instance, the following formula is quite useful:
PROCESSES = 50 + 50*N
where N is the number database instances connecting to the Oracle ASM instance.

REMOTE_LOGIN_PASSWORDFILE

The REMOTE_LOGIN_PASSWORDFILE initialization parameter specifies whether the Oracle ASM instance checks for a password file.

SHARED_POOL_SIZE

The setting for the SHARED_POOL_SIZE parameter determines the amount of memory required to manage the instance. There is no need to set this parameter when using automatic memory management.

The configuration guidelines for parameters to properly plan SGA sizing on the database instance are as follows:

· PROCESSES: Add 16 to the current or estimated database instance value
· LARGE_POOL_SIZE: Add an additional 600K to the current or estimated database instance value
· SHARED_POOL_SIZE: Add up the values from the following queries to obtain the current database storage size that is either on Oracle ASM or stored in Oracle ASM. Next, determine the redundancy type and calculate the SHARED_POOL_SIZE using the aggregated value as input.

SELECT SUM(bytes)/(1024*1024*1024) AS TOT_DATA_BYTES
FROM V$DATAFILE;

SELECT SUM(bytes)/(1024*1024*1024) AS TOT_TEMP_BYTES
FROM V$TEMPFILE
WHERE status='ONLINE';

SELECT SUM(bytes)/(1024*1024*1024) AS TOT_LOG_BYTES
FROM V$LOGFILE L1, V$LOG L2
WHERE L1.group#=L2.group#;

· When using normal redundancy disk groups, every 50 GB of space requires about 1 MB of extra shared pool plus 4 MB

· When using high redundancy disk groups, about every 33 GB of space requires about 1 MB of extra shared pool plus 6 MB

· When using external redundancy disk groups, every 100 GB of space requires about 1 MB of extra shared pool plus 2 MB.

AU_SIZE

Determines the size of the allocation unit for the disk group. For information about allocation unit size and extents query the AU_SIZE disk group attribute in the ALLOCATION_UNIT_SIZE column of the V$ASM_DISKGROUP view.

COMPATIBLE.ASM

Specifies the minimum software version for any Oracle ASM instance that uses a disk group.

COMPATIBLE.RDBMS

Specifies the minimum software version for any database instance that uses a disk group.

COMPATIBLE.ADVM

Determines whether the disk group can contain Oracle ASM volumes.
Optimizing Oracle ASM Disk Discovery

In ASM technology, disk discovery is the mechanism used to find the operating system names for disks Oracle ASM can access. Disk discovery occurs when the instance is initialized. Oracle ASM discovers and examines the contents of all of the disks that are in the paths that you designated with values in the ASM_DISKSTRING initialization parameter. Disk discovery also takes place when:

· Running the following SQL statements such as, namely:
· Mount a disk group with ALTER DISKGROUP ... MOUNT
· Add a disk to a disk group with CREATE or ALTER DISKGROUP...ADD DISK
· Online a disk with ALTER DISKGROUP ... ONLINE DISK
· Resize a disk in a disk group with ALTER DISKGROUP...RESIZE DISK
· Query with SELECT ... FROM V$ASM_DISKGROUP or V$ASM_DISK views
· Running Oracle Enterprise Manager or Oracle ASM Configuration Assistant (ASMCA) operations that invoke the SQL statements previously mentioned.
· Running ASMCMD commands that perform the same operations as the SQL statements as listed before.

Once Oracle ASM successfully discovers a disk, the disk appears in the V$ASM_DISK view. The disk header can have a value of MEMBER for disks that belong to a disk group; CANDIDATE or PROVISIONED for disks that were discovered, but that have not yet been assigned to a disk group; or FORMER for disks that previously belonged to a disk group and were dropped accordingly from the disk group. In Linux environments, for instance, ASMLIB API can facilitate disk discovery.

Important Disk Discovery Rules

The following rules for discovering Oracle ASM disks can affect most rebalancing operations:

· Oracle ASM can discover up to 10,000 disks.
· Oracle ASM only discovers disk partitions, excluding partitions that include the partition table.
· From the installation point of view, candidate disks are those that have the CANDIDATE, PROVISIONED, or FORMER header status, and they can be added to disk groups without using the FORCE flag.

When adding a disk, the FORCE option must be used if Oracle ASM recognizes that the disk was managed by Oracle, listed as FOREIGN in the V$ASM_DISK view. In this scenario, the FORCE keyword can be used to add the disk to a disk group.
MEMBER disks can normally be added to a disk group by specifying the FORCE flag, if the disks are not part of a currently mounted disk group.
In addition, Oracle ASM is able to identify issues such as multiple paths on the same disk, which causes the mount to fail. Besides, proper setting of the ASM_DISKSTRING parameter can lead to discovery time improvement.

ASM Maintenance and Load Balancing

Adding Disks to a Diskgroup

The following statement successfully adds disks /dev/diskA3 through /dev/diskA5 to adnata1. Since no FAILGROUP clauses are included in the ALTER DISKGROUP statement, each disk is assigned to its own failure group.

ALTER DISKGROUP adndata1 ADD DISK
'/dev/diskA3' NAME diskA3,
'/dev/diskA4' NAME diskA4,
'/dev/diskA5' NAME diskA5;

The NAMEclauses assign names to the disks, otherwise they would have been assigned system-generated names.

ALTER DISKGROUP adndata1 ADD DISK
'/dev/diskA*';

An alter diskgroup statement can fail when either a disk is already part of another diskgroup or when the search string matches disks that are already contained in other disk groups.
Similarly, the following statement would successfully add /dev/diskD1 through /dev/diskD7 to disk group adnata2. This statement runs with a rebalance power of 5, and does not return until the rebalance operation is complete.

ALTER DISKGROUP adndata2 ADD DISK
'/dev/diskD*'
REBALANCE POWER 4 WAIT;

If /dev/diskC3 was previously a member of a disk group that no longer exists, then it is possible to use the FORCE option to add the disk as a member of another disk group. The following statement illustrates this scenario. For this statement to succeed, adndata3 cannot be mounted.

ALTER DISKGROUP adndata3 ADD DISK
'/dev/diskC3' FORCE;

Managing Disk Drops from a Diskgroup

With its DROP DISK clause the ALTER DISKGROUP statement handles the task of dropping disks from a disk group. The option to drop all of the disks in specified failure groups is also possible using the DROP DISKS IN FAILGROUP clause.

In general, an important load balancing drill occurs when a disk is dropped, for which the disk group is rebalanced by moving all of the file extents from the dropped disk to other disks in the disk group. When performing both add and drop disk operations, the best approach is to perform them using the same ALTER DISKGROUP statement. Indeed, this has the benefit of rebalancing data extents only one time and emphasizing that there is enough space for the rebalance operation to succeed. In many scenarios, it is recommended to reduce the ASM_POWER_LIMIT in a commensurate fashion to leverage the overhead involved.

The ALTER DISKGROUP...DROP DISK SQL statement returns to SQL prompt before the drop and rebalance operations are complete, so the DBA must wait until the HEADER_STATUS column for this disk in the V$ASM_DISK view changes to FORMER before reusing, removing or disconnecting the dropped disk. Querying V$ASM_OPERATION view is useful to determine the amount of time remaining for the drop/rebalance operation to complete.

When specifying the FORCE clause for the drop operation, the disk is dropped even if Oracle ASM cannot read or write to the disk, and it may not be applied to drop a disk from an external redundancy disk group. Thus, a DROP FORCE operation leaves data at decreased redundancy until the subsequent rebalance operation completes. This increases the possibility of data loss if a subsequent disk failure occurs during rebalancing.

The following statements demonstrate how to drop disks from the disk group adndata1.
The following example drops diskA5 from disk group adnata1.

ALTER DISKGROUP adnata1 DROP DISK diskA5;

The following example drops diska5 from disk group adndata1, and also illustrates how multiple actions are possible with one ALTER DISKGROUP statement.

ALTER DISKGROUP adndata1 DROP DISK diskA5
ADD FAILGROUP failgroup1 DISK '/dev/diskA7' NAME diskA7;

ASM Storage Management Optimization with Intelligent Data Placement

Intelligent Data Placement enables setting disk regions on Oracle ASM disks for optimal performance, such that frequently accessed data is placed on the outermost (hot) tracks which have greater speed and higher bandwidth while files with similar access patterns are set physically close in order to reduce latency. Intelligent Data Placement also enables the placement of primary and mirror extents into different hot or cold regions.

Intelligent Data Placement settings can be specified for a file or in disk group templates. The disk region settings can be modified after the disk group has been created. The disk region setting can improve I/O performance by placing more frequently accessed data in regions furthest from the spindle, while reducing cost by increasing the usable space on a disk.

Intelligent Data Placement works best for the following:

· Databases with data files that are accessed at different rates. A database that accesses all data files in the same way is unlikely to benefit from Intelligent Data Placement.
· Disk groups that are more than 25% full. If the disk group is only 25% full, the management overhead is unlikely to be worth any benefit.
· Disks that have better performance at the beginning of the media relative to the end. Because Intelligent Data Placement leverages the geometry of the disk, it is well suited to JBOD (just a bunch of disks). In contrast, a storage array with LUNs composed of concatenated volumes masks the geometry from Oracle ASM.

The COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes must be set to 11.2 or higher to use Intelligent Data Placement.

Intelligent Data Placement can be managed with the ALTER DISKGROUP ADD or MODIFY TEMPLATE SQL statements and the ALTER DISKGROUP MODIFY FILE SQL statement.

· The ALTER DISKGROUP TEMPLATE SQL statement includes a disk region clause for setting hot/mirrorhot or cold/mirrorcold regions in a template:

ALTER DISKGROUP adndata1 ADD TEMPLATE datafile_hot
ATTRIBUTE (
HOT
MIRRORHOT);

· The ALTER DISKGROUP ... MODIFY FILE SQL statement that sets disk region attributes for hot/mirrorhot or cold/mirrorcold regions:

ALTER DISKGROUP adndata1 MODIFY FILE '+data/adn3/datafile/tools.255.765689507'
ATTRIBUTE (
HOT
MIRRORHOT);

Performing Disks Resizing in Disk Groups

The RESIZE clause of ALTER DISKGROUP enables you to perform the following operations:

· Resize all disks in the disk group
· Resize specific disks
· Resize all of the disks in a specified failure group

If a new size in the SIZE clause is omitted, then Oracle ASM uses the size of the disk as returned by the operating system. The new size is written to the Oracle ASM disk header and if the size of the disk is increasing, then the new space is immediately available for allocation. Similarly, when the size is decreasing, rebalancing must relocate file extents beyond the new size limit to available space below the limit. If the rebalance operation successfully relocates all extents, then the new size is made permanent, otherwise the rebalance fails.

The following SQL statement illustrates a sample scenario:

ALTER DISKGROUP adndata1
RESIZE DISKS IN FAILGROUP failgroup1 SIZE 150G;

Undoing Disks Drops in Disk Groups

The UNDROP DISKS clause of the ALTER DISKGROUP statement enables you to cancel all pending drops of disks within disk groups. If a drop disk operation has completed, then this statement cannot be used to restore it. This statement cannot be used to restore disks that are being dropped as the result of a DROP DISKGROUP statement, or for disks that are being dropped using the FORCE clause.

ALTER DISKGROUP adndata1 UNDROP DISKS;

Preparedness for Manual Rebalancing of Disk Groups

Manual rebalance of files in a disk group is possible using the REBALANCE clause of the ALTER DISKGROUP statement. This would normally not be required, because Oracle ASM automatically rebalances disk groups when their configuration changes.
Significantly important is the fact that the POWER clause of the ALTER DISKGROUP...REBALANCE statement determines the degree of parallelism, and subsequently the speed of the rebalance operation. It can be set to a value from 0 to 11. A value of 0 halts a rebalancing operation until the statement is either implicitly or explicitly re-run. The default rebalance power is set by the ASM_POWER_LIMIT initialization parameter. Likewise, the power level of an ongoing rebalance operation can be changed by entering the rebalance statement with a new level.

The ALTER DISKGROUP...REBALANCE command by default returns immediately so other commands can run while the rebalance operation takes place asynchronously in the background. It is practical to query the V$ASM_OPERATION view for to check the status of the rebalance operation.

The DBA can specify the WAIT keyword to cause the ALTER DISKGROUP...REBALANCE command to wait until the rebalance operation is complete.

Additional rules for the rebalance operation include the following:

· An ongoing rebalance command is restarted if the storage configuration changes either when you alter the configuration, or when the configuration changes due to a failure or an outage. Furthermore, when the new rebalance fails because of a user error, a manual rebalance may be required.
· The ALTER DISKGROUP...REBALANCE statement runs on a single node even if you are using Oracle Real Application Clusters (Oracle RAC).
· Oracle ASM can perform one disk group rebalance at a time on a given instance. Therefore, when multiple rebalances on different disk groups occur, Oracle processes these tasks serially. However, it is possible to initiate rebalances on different disk groups on different nodes in parallel.

Rebalancing continues across a failure of the Oracle ASM instance performing the rebalance.
· The REBALANCE clause (with its associated POWER and WAIT/NOWAIT keywords) can also be used in ALTER DISKGROUP commands that add, drop, or resize disks.
· Oracle restarts the processing of an ongoing rebalance operation if the storage configuration changes. If the next rebalance operation fails because of a user error, then a manual rebalance may be required.

The following SQL statement illustrates how to manually rebalance the disk group adndata2.
ALTER DISKGROUP adndata2 REBALANCE POWER 6 WAIT;

Tuning Rebalance Operations

When the POWER clause is omitted in an ALTER DISKGROUP statement, or when rebalance is implicitly run by adding or dropping a disk, then the rebalance power defaults to the value of the ASM_POWER_LIMIT initialization parameter, which can be adjusted dynamically.
When a rebalance is in progress because a disk is manually or automatically dropped, increasing the power of the rebalance shortens the duration for redundant copies of data on the dropped disk to be reconstructed on other disks.

The V$ASM_OPERATION view provides information for adjusting ASM_POWER_LIMIT and the resulting power of rebalance operations, including an estimate in the EST_MINUTES column of the amount of time remaining for rebalance completion.

Managing Capacity in Disk Groups

When Oracle ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. When the space left is insufficient, some files might end up with reduced redundancy which translates into one or more extents in the file not being mirrored at the expected level. Therefore, the REDUNDANCY_LOWERED column in the V$ASM_FILE view provides accurate information about files with reduced redundancy.

The following guidelines help ensure that there is enough space to restore full redundancy for all disk group data after the failure of one or more disks.

· For Normal redundancy disk group, it is best to have enough free space in the disk group to tolerate the loss of all disks in one failure group whose size could be equivalent to the size of the largest failure group.

· For High redundancy disk group, it is optimal to have enough free space to cope with the loss of all disks in two failure groups. The amount of free space should be equivalent to the sum of the sizes of the two largest failure groups.

The V$ASM_DISKGROUP view contains the following columns that contain information to help manage capacity:

· REQUIRED_MIRROR_FREE_MB indicates the amount of space that must be available in a disk group to restore full redundancy after the worst failure that can be tolerated by the disk group without adding additional storage. This requirement ensures that there are sufficient failure groups to restore redundancy. Also, this worst failure refers to a permanent failure where the disks must be dropped, not the scenario where the disks go offline and then back online.

The amount of space displayed in this column takes the effects of mirroring into account. The value is computed as follows:

· Normal redundancy disk group with more than two failure groups
The value is the total raw space for all of the disks in the largest failure group. The largest failure group is the one with the largest total raw capacity. For instance, when each disk is in its own failure group, the value would be the size of the largest capacity disk.

· High redundancy disk group with more than three failure groups
The value is the total raw space for all of the disks in the two largest failure groups.

· USABLE_FILE_MB indicates the amount of free space, adjusted for mirroring, that is available for new files to restore redundancy after a disk failure.
TOTAL_MB is the total usable capacity of a disk group in megabytes.
FREE_MB is the unused capacity of the disk group in megabytes, without considering any data imbalance.

With fine grain striping using 128 KB, the storage is preallocated to be eight times the AU size. The data file size may appear slightly larger on Oracle ASM than on a local file system because of the preallocation.

Oracle ASM Redundancy Levels

The Oracle ASM redundancy levels are, namely:

External redundancy

With this approach, essentially Oracle ASM does not provide mirroring redundancy and relies on the storage system to provide RAID functionality. Any write error causes a forced dismount of the disk group. All disks must be located to successfully mount the disk group.

Normal redundancy

Oracle ASM provides two-way mirroring by default, which means that all files are mirrored so that there are two copies of every extent.

High redundancy

Oracle ASM provides triple mirroring by default. A loss of two Oracle ASM disks in different failure groups is tolerated.

Oracle Automatic Storage Management Cluster File System (ACFS)

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) is a multi-platform, scalable file system, and storage management technology that extends Oracle Automatic Storage Management (Oracle ASM) functionality to support customer files maintained outside of the Oracle Database. Oracle ACFS supports many database and application files, including executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other supported files are video, audio, text, images, engineering drawings, and other general-purpose application file data.

Oracle ACFS establishes and maintains communication with the Oracle ASM instance to participate in Oracle ASM state transitions including Oracle ASM instance and disk group status updates and disk group rebalancing. Oracle Automatic Storage Management with Oracle ACFS and Oracle ASM Dynamic Volume Manager (Oracle ADVM) delivers support for all customer data and presents a common set of Oracle storage management tools and services across multiple vendor platforms and operating system environments on both Oracle Restart (single-node) and cluster configurations.

Oracle Automatic Storage Management Cluster File System (Oracle ACFS) and Oracle ASM Dynamic Volume Manager (Oracle ADVM) extend Oracle ASM support to include database and application executables, database trace files, database alert logs, application reports, BFILEs, and configuration files. Other supported files are text, images, audio, video, engineering drawings, and other general-purpose application file data.

Expected Limits of Oracle ADVM

The limits of Oracle ADVM are now discussed: The default configuration for an Oracle ADVM volume is four columns of 64 MB extents in length and a 128 KB stripe width. Oracle ADVM writes data as 128 KB stripe chunks in round robin fashion to each column and fills a stripe set of four 64 MB extents with 2000 stripe chunks before moving to a second stripe set of four 64 MB extents for volumes greater than 256 megabytes. Note that setting the number of columns on an Oracle ADVM dynamic volume to 1 effectively turns off striping for the Oracle ADVM volume.
On Linux platforms Oracle ASM Dynamic Volume Manager (Oracle ADVM) volume devices are created as block devices regardless of the configuration of the underlying storage in the Oracle ASM disk group.

The Oracle ASM instance is started during the Grid Infrastructure installation process whenever the Oracle Clusterware Registry (OCR) and voting files are configured within an Oracle ASM disk group. In that case, the Oracle ACFS drivers are initially loaded during Grid Infrastructure Installation based on the resource dependency. Besides, the Oracle ASM instance can also be started using the Oracle ASM Configuration Assistant and the Oracle ACFS drivers are loaded based on that action. In steady state mode, the Oracle ACFS drivers are automatically loaded during Oracle Clusterware initialization when the Oracle High Availability Services Daemon (OHASD) calls the start action for the Oracle ASM instance resource that also results in loading the Oracle ACFS drivers due to the resource dependency relationship. The start action for the Oracle ACFS drivers’ resource attempts to load the Oracle ACFS, Oracle ADVM, and Oracle Kernel Services (OKS) drivers into the native operating system.

In particular, the policy for the Oracle ACFS drivers is that they remain loaded until Oracle Clusterware is shut down. Likewise, the ora.drivers.acfs resource is managed automatically by Oracle High Availability Services Daemon (OHASD) and its state cannot be manually manipulated by srvctl or crsctl.

Managing Volumes in a Disk Group

An Oracle DBA can create Oracle ASM Dynamic Volume Manager (Oracle ADVM) volume in a disk group. The volume device associated with the dynamic volume can then be used to host an Oracle ACFS file system.

The compatibility parameters COMPATIBLE.ASM and COMPATIBLE.ADVM must be set to 11.2 or higher for the disk group.

The following examples illustrate possible ALTER DISKGROUP VOLUME SQL statements, Oracle ADVM volume management, including the functionality to add, modify, resize, disable, enable, and drop volumes:

SQL> ALTER DISKGROUP adndata1 ADD VOLUME voladn1 SIZE 12G;
Diskgroup altered.

SQL> ALTER DISKGROUP adndata1 RESIZE VOLUME voladn1 SIZE 16G;
Diskgroup altered.

SQL> ALTER DISKGROUP adndata1 DISABLE VOLUME voladn1;
Diskgroup altered.

SQL> ALTER DISKGROUP adndata1 ENABLE VOLUME voladn1;
Diskgroup altered.

SQL> ALTER DISKGROUP ALL DISABLE VOLUME ALL;
Diskgroup altered.

Oracle ACFS Registry Resource Management

The Oracle ACFS registry resource is supported only for Oracle grid infrastructure cluster configurations; it is not supported for Oracle Restart configurations.

The Oracle ACFS registry resource (ora.registry.acfs) is created by the root script that is executed following Grid Infrastructure installation. The start action for the Oracle ACFS mount registry is set online, if successful, otherwise to offline. The state of the Oracle ACFS registry resource is determined only by the active state of the mount registry. The online status is independent of any registry contents or the current state of any individual registered file systems that may exist within the Oracle ACFS registry.

In addition to activating the local node state of the mount registry, the Oracle ACFS registry resource start action assists in establishing a clusterwide Oracle ACFS file name space. On each node, the resource start action scans the contents of the clusterwide mount registry and mounts any file systems designated for mounting on the local cluster member. Before mounting a registered file system, the resource start action confirms that the associated file system storage stack is active and will mount the disk group, enable the volume file, and create the mount point if necessary to complete the mount operation.

The check action for the Oracle ACFS registry resource assists in maintaining the clusterwide Oracle ACFS file system name space. On each node, the check action scans the contents of the mount registry for newly created entries and mounts any Oracle ACFS file systems registered for mounting on the local node. Consequently, a new Oracle ACFS file system can be created and registered on one node of the cluster, and is automatically mounted on all cluster members designated by the Oracle ACFS registry entry.

The Oracle ACFS registry resource stop action is usually called during the Grid Infrastructure shutdown sequence of operations. To transition the registry resource to an offline state, all file systems on this cluster member that are configured with Oracle ADVM devices must be dismounted.

The Oracle ACFS registry resource clean action is called implicitly if the resource stop action fails to transition the resource to the offline state. In that scenario, the registry resource clean action can be called to effectively force the resource offline. The registry resource clean action scans the operating system internal mount table searching for any file system that is mounted upon an Oracle ADVM device. If any is found, the resource clean action attempts to umount the file system as in the resource stop action.

Each time Oracle Clusterware is started on a cluster node, the Oracle ACFS startup operations for the node consult the cluster mount registry and attempt to mount all Oracle ACFS file systems that are registered with that node. After each file system addition to the mount registry, the newly registered file system is automatically mounted on each node designated by the registry entry. If a registered file system is automatically mounted and is later dismounted, it is not automatically remounted until the system is rebooted or the Oracle Clusterware is restarted. It can be manually remounted using the mount command or Oracle Enterprise Manager.

The Oracle ACFS cluster mount registry action routines attempt to mount each Oracle ACFS file system on its registered mount point and create the mount point if it does not exist. The registry action routines also mount any Oracle ASM disk groups and enable any Oracle ADVM volumes required to support the Oracle ACFS mount operation. In the event that a file system enters into an offline error state, the registry action routines attempt to recover the file system and return it to an on-line state by dismounting and remounting the file system.

ACFS Individual File System Resource Management

Oracle ACFS individual file system resource is supported only for the Oracle grid infrastructure cluster configurations; it is not supported for Oracle Restart configurations.

Oracle ASM Configuration Assistant (ASMCA) facilitates the creation of Oracle ACFS individual file system resources (ora.diskgroup.volume.acfs). During database creation with Database Configuration Assistant (DBCA), the individual file system resource is included in the dependency list of its associated disk group so that stopping the disk group also attempts to stop any dependent Oracle ACFS file systems.

Typically, an Oracle ACFS individual file system resource is created for use with application resource dependency lists.

Besides, an Oracle ACFS file system that is to be mounted from a dependency action should not be included in the Oracle ACFS mount registry.

The start action for an Oracle ACFS individual file system resource is to mount the file system. This individual file system resource action includes confirming that the associated file system storage stack is active and mounting the disk group, enabling the volume file, and creating the mount point if necessary to complete the mount operation. If the file system is successfully mounted, the state of the resource is set to online; otherwise, it is set to offline.
The check action for an individual file system resource verifies that the file system is mounted. It sets the state of the resource to online status if mounted, otherwise the status is set to offline.
Tracking Performance Rebalancing Factors

The following factors could be used a factorial design to determine the impact of load balancing on ASM instances:

Factor 1: Redundancy

Factor 2: Clusterware

Factor 3: Allocation Unit

Factor 4: Striping Types

Factor 5: Extent design

Factor 6: Compatibility Issues

Factor 7: Support for ACFS

Factor 8: Support for ADVM

The design is left as a proposal for various interest groups and organizations, such as SNIA, which could work with various platforms, SAN architectures, and a variety of relevant storage technologies as well.

Exhibit 2. ASM Instance Stack and Storage System Infrastructure displaying disk groups

Concluding Remarks

Upon completion of this paper, the following remarks are widely applicable:

· ASM's rebalance process is very easy, and it happens without the intervention of the DBA or System Administrator. ASM simply rebalances a diskgroup whenever a disk is added or dropped.

· Rather than restriping all the data, ASM only needs to move an amount of data by using index techniques to spread extents on the available disks.

· In some failed scenarios, the DBA can manually perform rebalancing operations using the SYSDBA privilege for all destructive operations. In other scenarios, the SYSOPER privileges allows similar goals in non-destructive tasks.

· It is important to combine operations, such as ADD and DROP a DISK, at the same time, in order to minimize the overhead on both the ASM and database instances, and consequently or normal production operations.

· Finally, adjusting the ASM_POWER_LIMIT can enable controlled rebalancing while operations requiring data movement, control of redundancy and other-relevant are being performed.

· The above mentioned conclusions are valid for a native ASM environment, and one supporting ACFS and ADVM.

As ASM technology becomes a widely RAC infrastructure standard, with about 65% utilization on production environments, and nearly 25% of standalone database architectures, the future of this storage technology is certainly bright and it opens new doors to emerging technologies.

2009-07-19T18:25:00.007-04:00

Business Intelligence and Statistical Error

Surface Response Model: From a Bull/Cow Fertility Test
(in my Advanced Design of Experiments Class)

Statisticians often study errors of Type I to refer to the probability of rejecting the null hypothesis when it is in fact true and Type II when accepting that null hypothesis when it is false. Beyond that point, systematic error can occur in general when wrong or inaccurate information is entered into a database and subsequently propagated to various other areas of information systems replication and reporting. While the most common type of data error is inadmissibly the human error, there are other reasons why decision support systems, and business intelligence systems may be subject to erroneous decision or reporting, other common causes are inappropriate forecasting strategies, for instance inappropriate categorization and choosing the wrong sources of data verification and validation or relevant resources. The well-known belief that “garbage in, garbage out” will drive quality into inconsistency and in many other instances into wrong data derivation. In some data quality for a calibration procedure project I worked on, a procedural error was entered not by the code but by a random reading of data files, which caused for the procedure to introduce inappropriate measurements based on the timestamp associated with the data files. After I found why the algorithm, in spite of being accurate, would introduce an error, the lead researcher refused to accept an improved algorithm that would have eliminated the error, simply because this would have meant to redo the entire experiment. Therefore, the cell calibration test introduced a small percent of inconsistent data, which did not invalidate the regression, correlation, and factorial design model used to validate the test. The entire data went to an Oracle database, where it was analyzed, mined, and presented for calibration test of the CDMA wireless protocol. Indeed, it is obvious that time management was a key factor in making this decision. Having also worked directly with statisticians, biostatisticians and operations research analysts and managers, where my role was a DBA, data analyst, software engineer, software developer or simply consultant, I believe that such a decision might have been contested by others or at least carefully revised.

There are other scenarios, where a DBA role may be the underlying reason or perhaps not so directly. For instance, if a company adopts a DBA corporate approach, such that the CURSOR_SHARING parameter is set to SIMILAR, this may perform consistently in most databases, but could become an issue in many OLTP scenarios, in particular, in queries widely using bind variables. This is because the SQL bind peeking will not be so much concerned with the actual value of the bind variables. In those scenarios, setting CURSOR_SHARING to EXACT is more practical, since it avoids the typical scenario where the cached statement becomes the “SQL bind peeking bible” matching the query and reparsing of the statement never actually occurs. In these queries were used in BI environments, it would obvious that there is the potential and probability that at some point erroneous reporting occurs. Therefore, there is the possibility that a large amount of BI effort, such as applications, and tool-driven implementation can span into a significant level where some rows have been neglected for displaying. In some cases, I have tested, the rendering of result set from re-indexing of tables on additional columns can lead for this misbehavior of the SQL bind peeking and statement parsing to take place, which occurs mostly in OLTP databases.

While error and error propagation of various types is always possible, achieving data quality and integrity can only be measure in relation to the impact. For instance, in a research environment the error may have great or no significance, but it could be devastating in mission-critical environments such as medical and financial systems. Error tracking then becomes a pro-active objective to attain data integrity, consistency, and quality that is to be handled not only by manual human control and resilient data consistency but quality corporate auditing controls as well. In most instances, internal controls are more valuable than external controls such as outsourced data verification and validation, simply because internal resources usually know more about the applications, data domains, and data contents overall.

In some instances, database-driven data mining and BI tools, such as Discoverer, can be complemented not only their own visualization capabilities but also with the usage of other methodologies such as, for instance, surface response models and artificial intelligence mining. While visualization is unlikely to eliminate or resolve any existing database error, i.e., data quality or consistency, it can certainly congruently derive data trend and convey confirming or rejecting the desirable results to verify or validate. Therefore, visualization models are helpful to regression, correlation, and statistical tests, simply because they can verify and validate the data quality with one simple view. While BI models that include functions such as CUBE and ROLLUP, which can further aggregate and categorize data, visualization techniques are powerful tools for decision making in that they provide truthful real-life validation of data trends. Both database and statistical tools and models can expand their data mining capabilities with analysis of central tendency, — such as kurtosis and skewness—, and measures of variability such as mean deviation, variance and standard deviation, and further studies on analysis of variance, but it the end it goes for good to present to have chosen a visualization tool that provides the greatest expectations overall.

A Comprehensive Guide to Oracle Partitioning with Samples

2009-06-16T02:15:00.030-04:00

The research presented focuses on partitioning strategy options for each currently available Oracle version, in particular Oracle11g. The study guideline is on implementing the new partitioning options available in 11g, while enhancing performance tuning for the physical model and applications involved. Therefore, basic strategies— such as range, hash and list—, composite partitioning strategies including all possible combinations of Basic strategies, and Partition Extensions such as Reference and Interval partitioning strategies are covered. The research covers both middle-to-large-size and VLDB databases with significant implications for consolidation, systems integration, high-availability, and virtualization support. Topics covered emphasize subsequent performance tuning and specific application usage such as IOT-partitioning and composite partitioning choices.

1. Introduction

Encountering that performance for a table whose segments have exceeded a good number of Gigabytes or even Terabytes is probably the main consideration to partition that table. Indeed, that table will require special segment access control, and thus, special index architecture and administration. Ultimately, the usage of multiple block size tablespaces and buffer caches become a database technology that greatly enhances a partitioning approach.
In principle, tables (including materialized views), indexes, and Index-Organized Tables (IOT) are the objects used as target in any valid partitioning strategy utilized. Partitioning strategies can involve primarily Basic, Composite and Partition Extensions.

2. Partitioning Strategies

Oracle Partitioning strategies include, namely:

2.1 Basic Partitioning (Single-Level)

This strategy involves one of the following options: Partition by Range (Establishes ranges within the domain used as partitioning key), List (Provides a list of values matching one partition in the partition key domain and a default partition for those not matched) or Hash (which transforms the partitioning key value and maps it to given partition).

2.2 Composite Partitioning

The following combinations of basic partitioning result into valid composite partitioning strategies, namely:

Range-Range
Range-List
Range-Hash
List-List
List-Range
List-Hash
Interval Partitioning

The Interval Partitioning strategy is fundamentally a special implementation of Range partitioning, which maps primarily a DATE, TIMESTAMP data type to a numeric interval, using the INTERVAL keyword, use as a partition range marker. The functions NUMTOYMINTERVAL and NUMTODSINTERVAL are commonly used. Interval partitioning can occur as a single-level strategy or composite option in combination with all other options, namely, Range, Hash and List.

2.3 Partition Extensions

This partitioning strategy involves:

2.3.1 Reference Partitioning

This strategy normally uses the referential integrity constraint between to table, and uses the key in the details table to attain partition on the referenced key, which points to a candidate primary key in another partitioned table, the master table. The referential integrity constraint must be enabled and enforced.

2.3.2 Virtual Column Partitioning

This option permits to partition of a column on a virtual column, which is usually the outcome of a mathematical operation on two or more actual columns on the same table. This option extends every basic partitioning strategy.

3. Object Partitioning

In addition to the overall partitioning strategies, it is important to look at partitioning from the database objects to be partitioned, which include:

3.1 Tables (including Materialized Views)

Tables strictly support each Basic and Composite strategy and all Partition Extension, constrained by any SQL DDL rule.

3.2 Indexes

Indexes can have local or global partitions. Locally partitioned indexes can be pre-fixed (if they partitioned on the left portion of the key) or non-prefixed, otherwise. A local index has a one-to-one correspondence with the underlying table, and can reside in the same or on a different tablespace, which could even be of a different block size, a strategy that often enhances database performance. Index Partitioning can support Basic Partitioning strategies in general.

3.3 Index-Organized Tables

Like tables, Index-Organized Tables support all partitioning Basic strategies, and –in general– the partitioning key must be a subset of the primary key.

4. Creating Partitions

It is exceptionally good to have the experience of creating partitions under any possible strategy, namely:

4.1 Creating Range-Partitioned Tables

This sample code creates a table with four partitions and enables row movement:

CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
( PARTITION grad_date_70s
VALUES LESS THAN (TO_DATE('01-JAN-1980','DD-MON-YYYY')) TABLESPACE T1
, PARTITION grad_date_80s
VALUES LESS THAN (TO_DATE('01-JAN-1990','DD-MON-YYYY')) TABLESPACE T2
, PARTITION grad_date_90s
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE T3
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE T4 )
ENABLE ROW MOVEMENT;

4.2 Creating Global Indexes

Creating a range-partitioned global index is similar to creating range-partitioned table. The following example creates a range-partitioned global index on final_gpa for CREDENTIAL_EVALUATIONS. Each index partition is named but is stored in the default tablespace for the index.

CREATE INDEX ndx_final_gpa ON credential_evaluations (final_gpa)
GLOBAL PARTITION BY RANGE(final_gpa)
( PARTITION c1 VALUES LESS THAN (2.5)
, PARTITION c2 VALUES LESS THAN (3.0)
, PARTITION b1 VALUES LESS THAN (3.4)
, PARTITION b2 VALUES LESS THAN (3.7)
, PARTITION a1 VALUES LESS THAN (3.9)
, PARTITION a2 VALUES LESS THAN (MAXVALUE));

4.3 Creating an Interval Range-Partitioned Table

An Interval Range-Partitioned table is a special case of a Range Partitioned Table. The following are important features and relevant issues:

The INTERVAL clause of the CREATE TABLE statement sets interval partitioning for the table. At least one range partition must be specified using the PARTITION clause.
The range partitioning key value determines the high value of the range partitions (transition point) and the database automatically creates interval partitions for data beyond that transition point.
For each interval partition, the lower boundary is the non-inclusive upper boundary of the previous range or interval partition.
The partitioning key can only be a single column name from the table and it must be of NUMBER or DATE type.
The optional STORE IN clause lets you specify one or more tablespaces.
The following sample code sets four partitions with varying widths. It also specifies that above the transition point of January 1, 2009, partitions are created with a width of one month.

CREATE TABLE rental_costs (
item_id NUMBER(6),
time_intv DATE,
unit_cost NUMBER(12,2),
unit_price NUMBER(12,2) )
PARTITION BY RANGE (time_intv)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION pca VALUES LESS THAN (TO_DATE('1-1-2006', 'DD-MM-YYYY')) tablespace t2,
PARTITION pcb VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')) tablespace t4,
PARTITION pcc VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')) tablespace t8,
PARTITION pcd VALUES LESS THAN (TO_DATE('1-1-2009', 'DD-MM-YYYY')) tablespace t12);

The high bound of partition pcd establishes the transition point. pcd and all partitions below it, namely, (pca, pcb, and pcc) are in the range section while all partitions above it fall into the interval section.

4.4 Creating Hash-Partitioned Tables

Hash-Partitioned tables map the insertion location for any row via a hashing algorithm that determines the appropriate tablespace for the partition key instance. The following example illustrates a typical case:

CREATE TABLE school_directory
(stid NUMBER PRIMARY KEY,
lname VARCHAR2 (50),
fname VARCHAR2 (50),
phone VARCHAR2(16),
email VARCHAR2(128),
class_year VARCHAR2(4))
PARTITION BY HASH (stid) PARTITIONS 4 STORE IN (t1, t2, t3, t4);

The PARTITION BY HASH clause of the CREATE TABLE statement identifies that the table is to be hash-partitioned. The PARTITIONS clause can then be used to specify the number of partitions to create, and optionally, the tablespaces to store them in. Otherwise, PARTITION clauses can be used to name the individual partitions and their tablespaces . The only attribute needed to specify for hash partitions is TABLESPACE. All of the hash partitions of a table must share the same segment attributes (except TABLESPACE), which are inherited from the table level.

4.5 Creating List-Partitioned Tables

List-partitioned tables follow the following rules among others:

A PARTITION BY LIST clause is used in the CREATE TABLE statement to create a table partitioned by list, by specifying lists of literal values, (the discrete values of the partitioning columns qualifying rows matching the partition’s single column partitioning key.) In fact, there is no sense of order among partitions.

The DEFAULT keyword is used to describe the value list for a partition that will accommodate rows that do not map into any of the other partitions.
Optional subclauses of a PARTITION clause can specify physical and other attributes specific to a partition segment. If not overridden at the partition level, partitions inherit the attributes of their parent table.

CREATE TABLE regional_rentals
(divno NUMBER,
divname VARCHAR2(40),
rentals_quarterly NUMBER(12, 2),
state VARCHAR2(2))
PARTITION BY LIST (state)
(PARTITION pnw VALUES ('OR', 'WA', 'WY') TABLESPACE T1,
PARTITION psw VALUES ('AZ', 'CA', 'UT') TABLESPACE T3,
PARTITION pne VALUES ('CT', 'NY', 'NJ') TABLESPACE T5,
PARTITION pse VALUES ('FL', 'GA', 'SC') TABLESPACE T7);

4.6 Creating Reference-Partitioned Tables

In many cases, it is recommended to use referential integrity constraint in a strategic way to partition a master-detail table scenario, accordingly. The following concerns are quite relevant:

The PARTITION BY REFERENCE clause is used with the CREATE TABLE statement, specifying the name of a referential constraint, which becomes the partitioning referential constraint used as the basis for reference partitioning in the table. The referential integrity constraint must be enabled and enforced.
It is possible to set object-level default attributes, and optionally specify partition descriptors that override the object-level defaults on a per-partition basis.

When providing partition descriptors, the number of partitions described should match the number of partitions or subpartitions in the referenced table, i.e., the table will have one partition for each subpartition of its parent when the parent table is composite; otherwise the table will have one partition for each partition of its parent.

Besides, no partition bounds can be set for the partitions of a reference-partitioned table.

The partitions of a reference-partitioned table can be named, inheriting their name from the respective partition in the parent table, unless this inherited name conflicts with one of the explicit names given. In this scenario, the partition will have a system-generated name.

Partitions of a reference-partitioned table will collocate with the corresponding partition of the parent table, if no explicit tablespace is set accordingly.

The following sample code illustrates the creation of a reference-partitioned table preceded by the creation a of master table and a details table. The referential integrity constraint is highlighted as a key partition marking strategy.

4.6.1 Master Table DDL

CREATE TABLE order_hist
( ord_id NUMBER(16),
ord_date TIMESTAMP WITH LOCAL TIME ZONE,
ord_mode VARCHAR2(8),
cust_id NUMBER(9),
ord_status VARCHAR2(4),
ord_total NUMBER(12,2),
act_mgr_id NUMBER(9),
promo_id NUMBER(8),
CONSTRAINT ord_pk PRIMARY KEY(ord_id) USING INDEX TABLESPACE INDX )
PARTITION BY RANGE(ord_date)
( PARTITION pq1 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-APR-2008 07:00:00 -5:00' ,
'DD-MON-YYYY HH:MI:SS TZH:TZM')),
PARTITION pq2 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JUL-2008 07:00:00 -5:00' ,
'DD-MON-YYYY HH:MI:SS TZH:TZM')),
PARTITION pq3 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-OCT-2008 07:00:00 -5:00' ,
'DD-MON-YYYY HH:MI:SS TZH:TZM')),
PARTITION pq4 VALUES LESS THAN (TO_TIMESTAMP_TZ('01-JAN-2009 07:00:00 -5:00' ,
'DD-MON-YYYY HH:MI:SS TZH:TZM')) );

4.6.2 Details Table DDL

CREATE TABLE order_details
( ord_id NUMBER(16) NOT NULL,
line_item_id NUMBER(3) NOT NULL,
prod_id NUMBER(8) NOT NULL,
unit_price NUMBER(12,2),
qty NUMBER(8),
CONSTRAINT ord_det_fk
FOREIGN KEY(ord_id) REFERENCES order_hist(ord_id)
)
PARTITION BY REFERENCE(ord_det_fk);

4.7 Creating Local Partitioned Indexes

When creating a local index for a table, the database constructs the index such that it is equipartitioned (with a one-to-one correspondence) against the underlying table.

Similarly, the database also ensures that the index is maintained automatically when maintenance operations are performed on the underlying table. This sample code creates a local index on the SCHOOL_DIRECTORY table:

CREATE INDEX ndx_gd ON school_directory (email) LOCAL
PARTITIONS 4 STORE IN (t1, t2, t3, t4);

Naturally, it is possible to optionally name the hash partitions and tablespaces into which the local index partitions are to be stored, otherwise, the database uses the name of the corresponding base partition as the index partition name, and stores the index partition in the same tablespace as the table partition.

4.8 Creating a Hash-Partitioned Global Index

The following sample code illustrates the creation of a global hash-partitioned index.

CREATE UNIQUE INDEX ndx_sch_dir ON school_directory (stid,phone,email) GLOBAL
PARTITION BY HASH (stid,phone)
(PARTITION psp1 TABLESPACE t1,
PARTITION psp2 TABLESPACE t2,
PARTITION psp3 TABLESPACE t4,
PARTITION psp4 TABLESPACE t8);

The syntax is similar to that used for a hash partitioned table.
In most instances, hash-partitioned global indexes can improve the performance of indexes where a small number of leaf blocks in the index have high contention in multiuser OLTP environments.

Hash-partitioned global indexes can also limit the impact of index skew on monotonously increasing column values. Queries involving the equality and IN predicates on the index partitioning key can efficiently use hash-partitioned global indexes.

4.9 Creating Range-Hash Partitioned Tables

Range-Hash partitioned tables are probably the most common type among the composite partitioning strategies. In general, to create a composite partitioned table, use the PARTITION BY [ RANGE LIST ] clause of a CREATE TABLE statement. Next, you specify a SUBPARTITION BY [ RANGE LIST HASH ] clause that follows similar syntax and rules as the PARTITION BY [ RANGE LIST HASH ] clause. The PARTITION and SUBPARTITION or SUBPARTITION. In fact, it is important to consider the following issues, namely:

The partitions of a range-hash partitioned table are logical structures only, as their data is stored in the segments of their subpartitions.
As with partitions, these subpartitions share the same logical attributes.
Unlike range partitions in a range-partitioned table, the subpartitions cannot have different physical attributes from the owning partition, but they can reside another tablespace.
Attributes specified for a range partition apply to all subpartitions of that partition.
Specify different attributes for each range partition.
Specify a STORE IN clause at the partition level if the list of tablespaces across which the subpartitions of that partition should be spread is different from those of other partitions.

CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
SUBPARTITION BY HASH (grad_id) SUBPARTITIONS 8 STORE IN (T1,T2,T3,T4)
( PARTITION grad_date_70s
VALUES LESS THAN (
TO_DATE('01-JAN-1980','DD-MON-YYYY'))
, PARTITION grad_date_80s
VALUES LESS THAN (
TO_DATE('01-JAN-1990','DD-MON-YYYY'))
, PARTITION grad_date_90s
VALUES LESS THAN (
TO_DATE('01-JAN-2000','DD-MON-YYYY')), PARTITION grad_date_00s
VALUES LESS THAN (
TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);

4.10 Creating Range-List Partitioned Tables

Range-List partitioned tables are subject to range rules at the first partitioning level and list rules at second, list partitioning level, accordingly.

CREATE TABLE q_territory_sales
( divno VARCHAR2(12), depno NUMBER,
itemno VARCHAR2(16), accrual_date DATE,
sales_amount NUMBER, state VARCHAR2(2),
constraint pk_q_dvdno primary key(divno,depno)
) TABLESPACE t8 PARTITION BY RANGE (accrual_date) SUBPARTITION BY LIST (state)
(PARTITION q1_2000 VALUES LESS THAN (TO_DATE('1-APR-2000','DD-MON-YYYY'))
( SUBPARTITION q1_2000_nw VALUES ('OR', 'WY'),
SUBPARTITION q1_2000_sw VALUES ('CA', 'NM'),
SUBPARTITION q1_2000_ne VALUES ('NY', 'CT'),
SUBPARTITION q1_2000_se VALUES ('FL', 'GA'),
SUBPARTITION q1_2000_nc VALUES ('SD', 'WI'),
SUBPARTITION q1_2000_sc VALUES ('TX', 'LA‘) ),
PARTITION q2_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY'))
( SUBPARTITION q2_2000_nw VALUES ('OR', 'WY'),
SUBPARTITION q2_2000_sw VALUES ('CA', 'NM'),
SUBPARTITION q2_2000_ne VALUES ('NY', 'CT'),
SUBPARTITION q2_2000_se VALUES ('FL', 'GA'),
SUBPARTITION q2_2000_nc VALUES ('SD', 'WI'),
SUBPARTITION q2_2000_sc VALUES ('TX', 'LA‘)
), PARTITION q3_2000 VALUES LESS THAN (TO_DATE('1-OCT-2000','DD-MON-YYYY'))
( SUBPARTITION q3_2000_nw VALUES ('OR', 'WY'),
SUBPARTITION q3_2000_sw VALUES ('CA', 'NM'),
SUBPARTITION q3_2000_ne VALUES ('NY', 'CT'),
SUBPARTITION q3_2000_se VALUES ('FL', 'GA'),
SUBPARTITION q3_2000_nc VALUES ('SD', 'WI'),
SUBPARTITION q3_2000_sc VALUES ('TX', 'LA')
), PARTITION q4_2000 VALUES LESS THAN ( TO_DATE('1-JAN-2001','DD-MON-YYYY'))
( SUBPARTITION q4_2000_nw VALUES ('OR', 'WY'),
SUBPARTITION q4_2000_sw VALUES ('CA', 'NM'),
SUBPARTITION q4_2000_ne VALUES ('NY', 'CT'),
SUBPARTITION q4_2000_se VALUES ('FL', 'GA'),
SUBPARTITION q4_2000_nc VALUES ('SD', 'WI'),
SUBPARTITION q4_2000_sc VALUES ('TX', 'LA')
) );

4.11 Creating List-Hash Partitioned Tables

This example shows the CAR_RENTALS table that is list partitioned by territory and subpartitioned using hash by customer identifier.

CREATE TABLE car_rentals
( car_id VARCHAR2(16)
, account_number NUMBER
, customer_id NUMBER
, amount_paid NUMBER
, branch_id NUMBER
, territory VARCHAR(2)
, status VARCHAR2(1))
PARTITION BY LIST (territory)
SUBPARTITION BY HASH (customer_id) SUBPARTITIONS 8
( PARTITION p_nw VALUES ('OR', 'WY') TABLESPACE T1
, PARTITION p_sw VALUES ('AZ', 'CA') TABLESPACE T2
, PARTITION p_ne VALUES ('NY', 'CT') TABLESPACE T3
, PARTITION p_se VALUES ('FL', 'GA') TABLESPACE T4
, PARTITION p_nc VALUES ('SD', 'WI') TABLESPACE T5
, PARTITION p_sc VALUES ('OK', 'TX') TABLESPACE T6);

4.12 Creating List-Range Partitioned Tables

The following sample code shows a car_rentals table that is list by territory and subpartitioned by range using the rental paid amount. Note that row movement is enabled.

CREATE TABLE car_rentals
( car_id VARCHAR2(16)
, account_number NUMBER
, customer_id NUMBER
, amount_paid NUMBER
, branch_id NUMBER
, territory VARCHAR(2)
, status VARCHAR2(1) )
PARTITION BY LIST (territory)
SUBPARTITION BY RANGE (amount_paid)
( PARTITION p_nw VALUES ('WA', 'WY')
( SUBPARTITION snwlow VALUES LESS THAN (1000)
, SUBPARTITION snwmid VALUES LESS THAN (10000)
, SUBPARTITION snwhigh VALUES LESS THAN (MAXVALUE) )
, PARTITION p_ne VALUES ('NY', 'CT')
( SUBPARTITION snelow VALUES LESS THAN (1000)
, SUBPARTITION snemid VALUES LESS THAN (10000)
, SUBPARTITION snehigh VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_sw VALUES ('CA', 'AZ')
( SUBPARTITION sswlow VALUES LESS THAN (1000)
, SUBPARTITION sswmid VALUES LESS THAN (10000)
, SUBPARTITION sswhigh VALUES LESS THAN (MAXVALUE)
)
, PARTITION p_se VALUES ('FL', 'GA')
( SUBPARTITION sselow VALUES LESS THAN (1000)
, SUBPARTITION ssemid VALUES LESS THAN (10000)
, SUBPARTITION ssehigh VALUES LESS THAN (MAXVALUE)
)
);

4.13 Creating List-List Partitioned Tables

CREATE TABLE car_rentals_acct
( car_id VARCHAR2(16)
, account_number NUMBER
, customer_id NUMBER
, amount_paid NUMBER
, branch_id NUMBER
, territory VARCHAR(2)
, status VARCHAR2(1)
, rental_date TIMESTAMP WITH LOCAL TIME ZONE
, constraint pk_car_rhist primary key(car_id,account_number,branch_id,rental_date)
)
PARTITION BY LIST (territory)
SUBPARTITION BY LIST (status)
( PARTITION p_nw VALUES ('WA', 'WY')
( SUBPARTITION snw_low VALUES ('C')
, SUBPARTITION snw_avg VALUES ('B')
, SUBPARTITION snw_high VALUES ('A')
)
, PARTITION p_ne VALUES ('NY', 'CT')
( SUBPARTITION sne_low VALUES ('C')
, SUBPARTITION sne_avg VALUES ('B')
, SUBPARTITION sne_high VALUES ('A')
)
, PARTITION p_sw VALUES ('CA', 'AZ')
( SUBPARTITION ssw_low VALUES ('C')
, SUBPARTITION ssw_avg VALUES ('B')
, SUBPARTITION ssw_high VALUES ('A')
)
, PARTITION p_se VALUES ('FL', 'GA')
( SUBPARTITION sse_low VALUES ('C')
, SUBPARTITION sse_avg VALUES ('B')
, SUBPARTITION sse_high VALUES ('A')
)
, PARTITION p_ne VALUES ('NY', 'CT')
( SUBPARTITION sne_low VALUES ('C')
, SUBPARTITION sne_avg VALUES ('B')
, SUBPARTITION sne_high VALUES ('A')
)
, PARTITION p_sw VALUES ('CA', 'AZ')
( SUBPARTITION ssw_low VALUES ('C')
, SUBPARTITION ssw_avg VALUES ('B')
, SUBPARTITION ssw_high VALUES ('A')
)
, PARTITION p_se VALUES ('FL', 'GA')
( SUBPARTITION sse_low VALUES ('C')
, SUBPARTITION sse_avg VALUES ('B')
, SUBPARTITION sse_high VALUES ('A')
);

4.14 Creating Range-Hash Partitioned Tables Using a Subpartition Template

The following sample code illustrates how to use a subpartition template to create a composite Range-Hash Partition Table.

CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2)
)
PARTITION BY RANGE (grad_date)
SUBPARTITION BY HASH (grad_id)
SUBPARTITION TEMPLATE
( SUBPARTITION S_a TABLESPACE t1,
SUBPARTITION S_b TABLESPACE t2,
SUBPARTITION S_c TABLESPACE t3,
SUBPARTITION S_d TABLESPACE t4
)
( PARTITION grad_date_70s
VALUES LESS THAN ( TO_DATE('01-JAN-1980','DD-MON-YYYY'))
, PARTITION grad_date_80s
VALUES LESS THAN ( TO_DATE('01-JAN-1990','DD-MON-YYYY'))
, PARTITION grad_date_90s
VALUES LESS THAN ( TO_DATE('01-JAN-2000','DD-MON-YYYY'))
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY'))
);

4.15 Creating a Multicolumn Range-Partitioned Table

The following sample code illustrates the use of a multicolumn partitioned approach for table BI_AUTO_RENTALS_SUMMARY. This example shows a multicolumn range-partitioned table, storing the actual DATE information in three separate columns: year, month, and day with partition quarterly granularity. The purpose of this type of partition is to avoid dispersion of data within the range and balance the physical usage of each partition entries for performance tuning optimization purposes.

CREATE TABLE bi_auto_rentals_summary
( acctno NUMBER,
rental_date TIMESTAMP WITH LOCAL TIME ZONE,
year NUMBER,
month NUMBER,
day NUMBER,
total_amount NUMBER,
CONSTRAINT pk_actdate PRIMARY KEY (acctno, rental_date))
PARTITION BY RANGE (year,month)
(PARTITION prior2008 VALUES LESS THAN (2008,1),
PARTITION pq1_2008 VALUES LESS THAN (2008,4),
PARTITION pq2_2008 VALUES LESS THAN (2008,7),
PARTITION pq3_2008 VALUES LESS THAN (2008,10),
PARTITION pq4_2008 VALUES LESS THAN (2009,1),
PARTITION p_current VALUES LESS THAN (MAXVALUE,1));

4.16 Creating a Virtual Column Based Partitioned Table

In the context of partitioning, a virtual column can be used as any regular column.

All partition methods are supported when using virtual columns, including interval partitioning and all different combinations of composite partitioning.
There is no support for calls to a PL/SQL function on the virtual column used as the partitioning column.

The next sample code shows the DIRECT_MARKETING table partitioned by range-range using a virtual column for the subpartitioning key. The virtual column calculates the difference between the historic average sales and the forecasted potential sales. As a rule, at least one partition must be specified.

CREATE TABLE direct_marketing
( promo_id NUMBER(6) NOT NULL
, cust_id NUMBER NOT NULL
, campaign_date DATE NOT NULL
, channel_code CHAR(1) NOT NULL
, campaign_id NUMBER(6) NOT NULL
, hist_avg_sales NUMBER(12,2) NOT NULL
, sales_forecast NUMBER(12,2) NOT NULL
, discrepancy AS (sales_forecast - hist_avg_sales ) )
PARTITION BY RANGE (campaign_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(discrepancy) SUBPARTITION TEMPLATE
( SUBPARTITION p_low VALUES LESS THAN (5000)
, SUBPARTITION p_avg VALUES LESS THAN (15000)
, SUBPARTITION p_high VALUES LESS THAN (100000)
, SUBPARTITION p_max VALUES LESS THAN (MAXVALUE ) )
(PARTITION p_campaign_prior_2009 VALUES LESS THAN
(TO_DATE('01-JAN-2009','dd-MON-yyyy')) )
ENABLE ROW MOVEMENT COMPRESS PARALLEL NOLOGGING;

The reader could note that the hist_avg_sales and sales_forecast are two real columns in the DIRECT_MARKETING table of NUMBER type, which are used in the description of the virtual column.

4.17 Using Compression and Partitioning

The following important issues apply to partitioning when using compression as well:

For heap-organized partitioned tables, compress some or all partitions using table compression.
The compression attribute can be declared for a tablespace, a table, or a partition of a table.
Whenever the compress attribute is not specified, it is inherited like any other storage attribute.

The following sample code creates a list-partitioned table with both compressed and uncompressed partitions. The compression attribute for the table and all other partitions is inherited from the tablespace level.

CREATE TABLE credential_evaluations
( eval_id VARCHAR2(16) primary key
, grad_id VARCHAR2(12)
, grad_date DATE
, degree_granted VARCHAR2(12)
, degree_major VARCHAR2(64)
, school_id VARCHAR2(32)
, final_gpa NUMBER(4,2))
PARTITION BY RANGE (grad_date)
SUBPARTITION BY HASH (grad_id) SUBPARTITIONS 8 STORE IN (T1,T2,T3,T4)
( PARTITION grad_e_70s
VALUES LESS THAN (TO_DATE('01-JAN-1980','DD-MON-YYYY')) TABLESPACE T1 COMPRESS
, PARTITION grad_date_80s
VALUES LESS THAN (TO_DATE('01-JAN-1990','DD-MON-YYYY')) TABLESPACE T2 COMPRESS
, PARTITION grad_date_90s
VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')) TABLESPACE T3 NOCOMPRESS
, PARTITION grad_date_00s
VALUES LESS THAN (TO_DATE('01-JAN-2010','DD-MON-YYYY')) TABLESPACE T4 NOCOMPRESS)
ENABLE ROW MOVEMENT;

4.18 Using Partitioned Index Key Compression

The following recommendations apply to partitioned-index key compression, namely:

Compress some or all partitions of a B-tree index using key compression.
Key compression is applicable only to B-tree indexes.
Bitmap indexes are stored in a compressed manner by default.
An index using key compression eliminates repeated occurrences of key column prefix values, thus saving space and I/O.

This sample code creates a local partitioned index with all partitions except the most recent one compressed:

CREATE INDEX ndx_grad_date ON credential_evaluations (grad_date)
COMPRESS LOCAL
(
PARTITION grad_date_70s,
PARTITION grad_date_80s,
PARTITION grad_date_90s,
PARTITION grad_date_00s NOCOMPRESS
);

Likewise, it is not possible to specify COMPRESS (or NOCOMPRESS) explicitly for an index subpartition. The compression setting in a partition is inherited for a child subpartition attribute. Each index subpartition of a parent partition inherits its key compression setting.

4.19 Creating Range-Partitioned Index-Organized Tables

In general, it is possible to partition index-organized tables, and their secondary indexes, by the range method. The following sample code creates the range-partitioned index-organized table NEW_MKTG_CAMPAINGS. The INCLUDING clause specifies that all columns after period_code are to be stored in an overflow segment. There is one overflow segment for each partition, all stored in the same tablespace (T11). Optionally, OVERFLOW TABLESPACE is specified at the individual partition level, in which case some or all of the overflow segments could have separate TABLESPACE attributes.

CREATE TABLE new_mktg_campaigns
( campaign_id NUMBER(8)
, period_code INTEGER CONSTRAINT rck CHECK (period_code BETWEEN 1 AND 26)
, campaign_name VARCHAR2(20)
, projected_sales NUMBER(12,2)
, campaign_desc VARCHAR2(4000),
PRIMARY KEY (campaign_id, period_code)
) ORGANIZATION INDEX
INCLUDING period_code OVERFLOW TABLESPACE T11
PARTITION BY RANGE (period_code)
(PARTITION VALUES LESS THAN (10) TABLESPACE t1,
PARTITION VALUES LESS THAN (20) TABLESPACE t2 OVERFLOW TABLESPACE t9,
PARTITION VALUES LESS THAN (MAXVALUE) TABLESPACE t13);

4.20 Creating Hash-Partitioned Index-Organized Tables

Another option for partitioning index-organized tables is to use the hash method. In the following example, the FUTURE_MKTG_CAMPAINGS index-organized table is partitioned by the hash method.

CREATE TABLE future_mktg_campaigns
( campaign_id NUMBER(8)
, period_code INTEGER CONSTRAINT fnock CHECK (period_code BETWEEN 1 AND 26)
, campaign_name VARCHAR2(20)
, projected_sales NUMBER(12,2)
, campaign_desc VARCHAR2(2000),
PRIMARY KEY (campaign_id, period_code)
)
ORGANIZATION INDEX
INCLUDING period_code OVERFLOW TABLESPACE T11
PARTITION BY HASH (period_code)
PARTITIONS 8
STORE IN (T1,T2,T3,T4,T5,T6,T7,T8)
OVERFLOW STORE IN (T9,T10,T11);

4.21 Creating a List-Partitioned Index-Organized Table

The other option for partitioning index-organized tables is to use the list method.

CREATE TABLE current_mktg_campaigns
( campaign_id NUMBER(8)
, period_code INTEGER CONSTRAINT fpclst_ck
CHECK (period_code BETWEEN 1 AND 26)
, campaign_name VARCHAR2(20)
, projected_sales NUMBER(12,2)
, campaign_desc VARCHAR2(4000),
PRIMARY KEY (campaign_id, period_code))
ORGANIZATION INDEX
INCLUDING period_code OVERFLOW TABLESPACE T11
PARTITION BY LIST (period_code)
(PARTITION A VALUES (2, 4, 8, 10,12,14,16) TABLESPACE t12,
PARTITION B VALUES (1,3,5,7,9,11,13,15,17) TABLESPACE t14
OVERFLOW TABLESPACE t15,
PARTITION C VALUES (DEFAULT) TABLESPACE t10);

4.22 Creating Composite Interval-RangeHashList Partitioned Tables

In comparison to previous examples, the composite interval range-partitioned tables subpartitioned by any of range, hash or list provides a very powerful strategy in using a time domain, such as those associate with a DATE OR TIMESTAMP column. The following sample code shows the PRO_MARKETING_CAMPAIGNS table as interval-partitioned using monthly intervals on campaign_date, with hash subpartitions by period_code.

CREATE TABLE pro_marketing_campaigns
( campaign_id NUMBER(8)
, campaign_name VARCHAR2(20)
, campaign_date DATE
, period_code INTEGER CONSTRAINT fcopck CHECK (period_code BETWEEN 1 AND 26)
, projected_sales NUMBER(12,2)
, campaign_desc VARCHAR2(4000),
PRIMARY KEY (campaign_id, period_code))
PARTITION BY RANGE (campaign_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY HASH (period_code) SUBPARTITIONS 4
( PARTITION p_prior_2009 VALUES LESS THAN (TO_DATE('01-JAN-2009','dd-mon-yyyy')))
PARALLEL COMPRESS FOR ALL OPERATIONS;

4.23 Using Encryption in Partitioned Tables

While encryption is supported in partitioned tables, it is not possible to partitioned a table on an encrypted column under any partitioning strategy.

Exhibit 1. Partitioning Key and Encryption on the Same Column are Incompatible.[1]

[1] Removing the encryption option will allow for the table to be created.

4.24 Using Multiple Block Size Caches

When using multiple block size databases, i.e., those using more than one tablespace block size and associated buffer cache, there are many congruent features to take advantages of to a higher performance optimization.

Exhibit 2. Using multiple Block Size to Optimize Index Performance.

Creating indexes on a tablespace with a larger block size will increasing performance in DSS and in most OLTP scenarios.

The following sample code creates the CREDENTIAL_TABLES in the 8k block size T1,T2,T3,and T4 tablespaces, and local indexes on the 16k T18,T20,T22,T24 tablespaces, as cached respectively.

5. Partitioning- Related Data Dictionary Views

The following exhibit illustrates the list of data dictionary views that handle partitioning-related metadata.

Exhibit 3. Oracle Partitioning-related Data Dictionary Views

6. Maintenance Operations of Partitions and Subpartitions

Exhibit 4. Maintenance Operations of Subpartitions

Exhibit 5. Maintenance Operations of Index Partitions.

The following operations support the UPDATE INDEXES clause:

ADD PARTITION SUBPARTITION
COALESCE PARTITION SUBPARTITION
DROP PARTITION SUBPARTITION
EXCHANGE PARTITION SUBPARTITION
MERGE PARTITION SUBPARTITION
MOVE PARTITION SUBPARTITION
SPLIT PARTITION SUBPARTITION
TRUNCATE PARTITION SUBPARTITION

As of Oracle10g, SKIP_UNUSABLE_INDEXES is an initialization parameter with a default value of TRUE. This setting disables error reporting of indexes and index partitions marked UNUSABLE. To avoid choosing an alternative execution plan to evading the unusable elements, set this parameter to FALSE.
The following set of exhibits illustrates various scenarios of maintenance operations of partitions and subpartitions.

Exhibit 6. Adding a partition to a range partition table when MAXVALUE is not specified.

Exhibit 7. Coalescing Operation Reserved for the Hash Partitioning strategy.

Exhibit 8. The Coalescing Maintenance Operation is Reserved to the Hash Partitioning Strategy.

Exhibit 9. Truncating a Partitioning and Resetting the High Water Mark by Dropping the Storage for Exchange Preparedness.

Exhibit 10. Exchanging the Empty Partition with a Preloaded Table.

Exhibit 11. Exchanging the Empty Partition with a Preloaded Table and Updating the Indexes (Rebuild Operation).

Exhibit 12. Moving a Table Partition (When a Composite Partition is Being Used) is not Supported

Exhibit 13. Moving a Table Partition when Supported.

Exhibit 14. Moving two Table Partitions into a New System-Generated Partition.

Exhibit 15. System-Generated Merged Partition Uses Default Tablespace.

Exhibit 16. Partitions PSW and PSE are Merged into Partition PSS in the Same Tablespace.

The next exhibit shows the query and result set showing that the merged partition is set in the same tablespace as one of the merged partitions. In contrast, the previous example shows that the system-named partition was set in a default tablespace, since neither the target partition name nor the tablespace name was provided.

Exhibit 17. Merged Partitions List for Table REGIONAL_RENTALS.

Exhibit 18. Splitting a Range Partition with Date Datatype Partition Key.

Exhibit 19. Splitting a Range Partition with Date Partition Key.

Exhibit 20. Truncating a Table Partition with Various Options.

Exhibit 21. Rebuilding a Global Index Partitions.

7. Manageability

DBAs can use Oracle Enterprise Manager Database and Grid Control to create, maintain, and verify accuracy of SQL, using the Schema tag and then selecting the desired partitioning options on the relevant object, namely, tables, indexes, and index-organized tables. There is extensive support to use standard, unstructured, and user-defined datatypes.

Exhibit 22. Manageability Applied to Partitioned Index-Organized Tables.

Exhibit 23. Creating a Sample Partitioned Index-Organized Table using Database Control.

8. Partitioning to Attain the Best Availability, Manageability and Performance

The following are valid partitioning options to improve availability, manageability and optimize performance, namely:

8.1 Partition Pruning

The following concerns regarding partition pruning can be highlighted:

Partition pruning is a foundational performance feature to both DSS and OLTP, enabling the Oracle Database to perform operations only on those partitions that are relevant to the SQL.
The optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions.

Partition pruning greatly optimizes time and resources when retrieving data from disk, thus improving query performance.

When partitioning an index and a table on different columns (with a global partitioned index), then partition pruning also eliminates index partitions even when the partitions of the underlying table cannot be eliminated.

Either static or dynamic pruning could be used, depending on SQL statement.

Static pruning occurs at compile-time, with the information about the partitions accessed beforehand while dynamic pruning occurs at run-time.

Partition pruning affects the statistics of the objects involved and therefore also the execution plan of the statement.
Oracle Database prunes partitions when using range, LIKE, equality, and IN-list predicates on the range or list partitioning columns, and when using equality and IN-list predicates on the hash partitioning columns.

When using composite partitioned objects, Oracle can prune at both levels using the relevant predicates.

8.2 Partition-Wise Joins

The next paragraph asserts the most important issues regarding partition-wise joins, as follows:

Partition-wise joins minimize query response time by reducing the amount of data exchanged among parallel execution servers when joins execute in parallel, thus reducing response time and improving the use of both CPU and memory resources.
Oracle Database can perform partial partition-wise joins only in parallel.
Unlike full partition-wise joins, partial partition-wise joins require partitioning only one table on the join key.
The partitioned table is referred to as the reference table. The other table may or may not be partitioned. Partial partition-wise joins are more common than full partition-wise joins.
To execute a partial partition-wise join, the database dynamically repartitions the other table based on the partitioning of the reference table. Then, the execution becomes similar to a full partition-wise join.
In Oracle Real Application Clusters (RAC) environments, partition-wise joins also avoid or at least limit the data traffic over the interconnect, which is the key to achieving good scalability for massive join operations.

The performance advantage that partial partition-wise joins have over joins in non-partitioned tables is that the reference table is not moved during the join operation.

The parallel joins between non-partitioned tables require both input tables to be redistributed on the join key. This redistribution operation involves exchanging rows between parallel execution servers.

This is a CPU-intensive operation that can lead to excessive interconnect traffic in RAC environments.

8.3 Full Partition-Wise Joins

A full partition-wise join divides a large join into smaller joins between a pair of partitions from the two joined tables. To use this feature, you must equipartition both tables on their join keys, or use reference partitioning. For example, consider a large join between the DIRECT_MARKETING table and the CUSTOMERS table on cust_id.

SELECT c.cust_lname COUNT(*)
FROM direct_marketing dm, customers c
WHERE dm.cust_id = c.cust_id
AND dm.campaign_init_date = c.campaign_date
AND dm.campaign_date BETWEEN TO_DATE('01-JUL-2008', 'DD-MON-YYYY') AND
(TO_DATE('01-OCT-2008', 'DD-MON-YYYY'))
GROUP BY c.cust_lname HAVING COUNT(*) > 200;

Furthermore, the most commonly highlights are listed below:

Partition-wise joins reduce query response time and optimizing CPU and memory resources by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel.
In RAC environments, partition-wise joins also avoid or at least limit the data traffic over the interconnection, which is the key to achieving good scalability for massive joins.

To avoid remote I/O, both matching partitions should have affinity to the same node.
Partition pairs should be spread over all nodes to use all CPU resources available and avoid bottlenecks.
Nodes can host multiple pairs when there are more pairs than nodes, e.g., for an 8-node system and 16 partition pairs, each node receives two pairs.

8.3.1 Full Partition-Wise Joins: Composite - Single-Level

This method is a variation of the single-level - single-level method. In this scenario, one table (typically the larger table) is composite partitioned on two dimensions, using the join columns as the subpartition key.

8.3.2 Partial Partition-Wise Joins: Single-Level Partitioning

The number of partitions determines the maximum degree of parallelism, because the partition is the smallest granule of parallelism for partial partition-wise join operations.

8.3.3 Full Partition-Wise Joins: Composite - Composite

When necessary, it is possible to also partition a table by a composite method.
It is possible to get full partition-wise joins on all combinations of partition and subpartition partitions: partition - partition, partition-subpartition, subpartition-partition, and subpartition-subpartition.

8.4 Index Partitioning

The rules for partitioning indexes are similar to those for tables:

An index can be partitioned unless:

- The index is a cluster index.

- The index is defined on a clustered table.

It is possible to mix partitioned and nonpartitioned indexes with partitioned and nonpartitioned tables:
A partitioned table can have partitioned or nonpartitioned indexes.
A nonpartitioned table can have partitioned or nonpartitioned indexes.
Bitmap indexes on nonpartitioned tables cannot be partitioned.
A bitmap index on a partitioned table must be a local index.

Nonprefixed indexes are particularly useful in historical databases.

The three Oracle-supported Local Index partitioning types are:

8.4.1 Local Partitioned Indexes

In a local index, all keys in a particular index partition refer only to rows stored in a single underlying table partition. A local index is created by specifying the LOCAL attribute. Other important aspects of local partitioned indexes are as follows:

Oracle constructs the local index so that it is equipartitioned with the underlying table.

Oracle also maintains the index partitioning automatically when partitions in the underlying table are added, dropped, merged, or split, or when hash partitions or subpartitions are added or coalesced, ensuring that the index remains equipartitioned with the table.

A local index can be created UNIQUE if the partitioning columns form a subset of the index columns. This restriction guarantees that rows with identical index keys always map into the same partition, where uniqueness violations can be detected.
Only one index partition needs to be rebuilt when a maintenance operation other than SPLIT PARTITION or ADD PARTITION is performed on an underlying table partition.
The duration of a partition maintenance operation is proportional to partition size.
Local indexes support partition independence.
Local indexes support smooth roll-out of old data and roll-in of new data in historical tables.
Oracle can take advantage of the fact that a local index is equipartitioned with the underlying table to generate improved query access plans.
Local indexes simplify the task of tablespace incomplete recovery. In order to recover a partition or subpartition of a table to a point in time, the corresponding index entries must be recovered to the same point in time.
Oracle Database PL/SQL Packages and Types Reference for a description of the DBMS_PCLXUTIL package

8.4.1.1 Local Prefixed Indexes

A local index is prefixed if it is partitioned on a left prefix of the index columns.

8.4.1.2 Local Nonprefixed Indexes

A local index is nonprefixed if it is not partitioned on a left prefix of the index columns. Therefore, it is not possible to have a unique local nonprefixed index unless the partitioning key is a subset of the index key.

8.4.2 Global Partitioned Indexes

In a global partitioned index, the keys in a particular index partition may refer to rows stored in more than one underlying table partition or subpartition. It is also possible to highlight the following features:

A global index can be range or hash partitioned, though it can be defined on any type of partitioned table.

A global index is created by specifying the GLOBAL attribute.
Index partitions can be merged or split as necessary.
Normally, a global index is not equipartitioned with the underlying table and usually nothing could prevent this. An index that must be equi-partitioned with the underlying table should be created as LOCAL.
A global partitioned index contains a single B-tree with entries for all rows in all partitions. Each index partition may contain keys that refer to many different partitions or subpartitions in the table.
The highest partition of a global index must have a partition bound all of whose values are MAXVALUE.

The following are distinctive features differentiating global indexes on prefixing, namely:

A global partitioned index is prefixed if it is partitioned on a left prefix of the index columns.
Global prefixed partitioned indexes can be unique or nonunique.
Nonpartitioned indexes are treated as global prefixed nonpartitioned indexes.loba

The most practical aspects of global partitioned indexes are emphasized in the following statements:

Global partitioned indexes are harder to manage than local indexes.
When the data in an underlying table partition is moved or removed (SPLIT, MOVE, DROP, or TRUNCATE), all partitions of a global index are affected. Thus, global indexes do not support partition independence.
When an underlying table partition or subpartition is recovered to a point in time, all corresponding entries in a global index must be recovered to the same point in time. Because these entries may be scattered across all partitions or subpartitions of the index, mixed in with entries for other partitions or subpartitions that are not being recovered, there is no way to accomplish this except by re-creating the entire global index.
When deciding how to partition indexes on a table, consider the mix of applications that need to access the table.
There is normally a trade-off between performance and availability, and manageability.

8.5 Guidelines to Index Partitioning

The most important guidelines for OLTP and DSS are listed below respectively, as follows:

8.5.1 For OLTP applications

Global indexes and local prefixed indexes provide improved performance over local non-prefixed indexes because they minimize the number of index partition probes.
Local indexes support more availability when there are partition or subpartition maintenance operations on the table.
Local non-prefixed indexes are very useful for historical databases.

8.5.2 For DSS applications

Local non-prefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.
For historical tables, indexes should be local if possible. This limits the impact of regularly scheduled drop partition operations.
Unique indexes on columns other than the partitioning columns must be global because unique local non-prefixed indexes whose key does not contain the partitioning key are not supported.

8.6 Tuning and Mixing objects in Multiple Block Size Database Models

The option to set index in larger block size tablespaces and caches has proven to greatly improve performance in Decision Support Systems (DSS), as introduced in the author’s paper “OMBDB, Oracle Multiple Block Size Databases: An Innovative Paradigm for Datawarehousing Architectures”, as verified by several field researchers and leading consultants. This option has also performed quite well in many Online Transaction Processing (OLTP) Systems.

9. Partitioning and Table Compression

When using partitioning and compression options together, it is possible to use compress in most cases, and the clause COMPRESS FOR ALL OPERATIONS can be specified at table creation time. Partitions can be individually set as compressed or not compressed, as needed.

Likewise, when using table compression on partitioned tables with bitmap indexes, the DBA needs to do the following before introducing the compression attribute for the first time:

1. Mark bitmap indexes unusable.
2. Set the compression attribute.
3. Rebuild the indexes.

10. Partition Strategy Recommendations

10.1 When to Use Range or Interval Partitioning

Range partitioning is a convenient method for partitioning historical data. Besides, other reasons include:

The boundaries of range partitions define the ordering of the partitions in the tables or indexes.
Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created when the data is inserted into the partition.
Range or interval partitioning is often used to organize data by time intervals on a column of type DATE.

For instance, keeping the past 48 months’ worth of data online, Range partitioning simplifies this process. To add data from a new month, the DBA will load it into a separate table, clean it, index it, and then add it to the range-partitioned table using the EXCHANGE PARTITION statement, all while the original table remains online. After adding the new partition, the DBA can drop the trailing month with the DROP PARTITION statement.

10.2 When to Use Hash Partitioning

There are scenarios when it is not trivial into which partition data should reside, although the partitioning key can be identified. With hash partitioning, a row is placed into a partition based on the result of passing the partitioning key into a hashing algorithm. The next guidelines should be followed carefully:

When using this approach, data is randomly distributed across the partitions rather than grouped together.
Hence, this is a great approach for some data, but may not be an effective way to manage historical data.
Partition pruning is limited to equality predicates.
Hash partitioning also supports partition-wise joins, parallel DML and parallel index access.
Hash-partitioning is beneficial when the DBA needs to enable partial or full parallel partition-wise joins with very likely equi-sized partitions or distribute data evenly among the nodes of an MPP platform using RAC, thus minimizing interconnect traffic when processing internode parallel statements.

10.3 When to Use List Partitioning

It is recommended to use list partitioning when you want to specifically map rows to partitions based on discrete values.

10.4 When to Use Composite Partitioning

Composite partitioning offers the benefits of partitioning on two dimensions. From a performance perspective, it benefits from partition pruning on one or two dimensions depending on the SQL statement, taking advantage of both full or partial partition-wise joins on either dimension, as needed.

It can benefit from parallel backup and recovery of a single table (manageability perspective).

The DBA can split up backups of your tables and you can decide to store data differently based on identification by a partitioning key.

The database stores every subpartition in a composite partitioned table as a separate segment.

Thus, the subpartitions may have properties that differ from the properties of the table or from the partition to which the subpartitions belong.

10.5 When to Use Composite Range-Hash Partitioning

Composite range-hash partitioning is particularly common for tables that store history, are very large as a result, and are frequently joined with other large tables. The following are relevant issues, namely:

Composite range-hash partitioning provides the benefit of partition pruning at the range level.
Opportunity to perform parallel full or partial partition-wise joins at the hash level. Specific cases can benefit from partition pruning on both dimensions for specific SQL statements.
Composite range-hash partitioning can also be utilized for tables that traditionally use hash partitioning, but also use a rolling window approach.

10.6 When to Use Composite Range-List Partitioning

Composite range-list partitioning is mostly used for large tables that store historical data and are usually accessed on more than one dimension.

10.7 When to Use Composite Range-Range Partitioning

Composite range-range partitioning is helpful for applications that store time-dependent data on more than one time dimension. Likewise, business cases for composite range-range partitioning could include ILM scenarios, and applications that store historical data and need to categorize its data by range on another dimension.

10.8 When to Use Composite List-Hash Partitioning

Composite list-hash partitioning is applicable to large tables that are usually accessed on one dimension, but because of their size need yet to take advantage of parallel full or partial partition-wise joins.

10.9 When to Use Composite List-List Partitioning

Composite list-list partitioning is helpful for large tables that are often accessed on different dimensions. The DBA can explicitly map rows to partitions on those dimensions on the basis of discrete values.

10.10 When to Use Composite List-Range Partitioning

Composite list-range partitioning is advantageous for large tables that are accessed on different dimensions. For the most commonly used dimension, the DBA can explicitly map rows to partitions on discrete values. In general, list-range partitioning is likely to be used for tables that use range values within a list partition; in contrast range-list partitioning is mostly used for discrete list values within a range partition. Besides, list-range partitioning is less likely to be used to store historical data, although equivalent scenarios all work. Range-list partitioning can be implemented using interval-list partitioning, while list-range partitioning does not support interval partitioning.

10.11 When to Use Interval Partitioning

Interval partitioning can be used for every table that is range partitioned and uses fixed intervals for new partitions. The database automatically creates interval partitions as data for that partition is loaded. Until this happens, the interval partition exists but no segment is created for the partition.

The benefit of interval partitioning is that there is no need to create your range partitions explicitly. Therefore, a DBA could consider using interval partitioning unless there is a need to create range partitions with different intervals, or a need to specific partition attributes when creating range partitions. When upgrading an application it is recommended to use range partitioning or composite range-rangehashlist partitioning, accordingly.

10.12 When to Use Reference Partitioning

Reference partitioning is effective in the following scenarios:
When denormalizing or planning to denormalize, a column from a master table into a child table in order to get partition pruning benefits on both tables.
If two large tables are joined often, then the tables are not partitioned on the join key, but you want to take advantage of partition-wise joins.
Indeed, reference partitioning implicitly enables full partition-wise joins.
If data in multiple tables has a related life cycle, then reference partitioning can provide significant manageability benefits.
Partition management operations against the master table are automatically cascaded to its descendents. For example, when adding a partition to the master table, that creation is automatically propagated to all its descendents.
In order to use reference partitioning, the DBA has to enable and enforce the foreign key relationship between the master table and the reference table in place.
It is also possible to cascade reference-partitioned tables based on the data model used.

10.13 When to Partition on Virtual Columns

Virtual column partitioning enables partitioning on an expression, which may use data from other columns, and perform calculations with these columns.

The most relevant highlights are:

There is no support for PL/SQL function calls on a virtual column definitions as a partitioning key.
Virtual column partitioning supports all partitioning methods as well as performance and manageability features.
Virtual columns could be used when tables are frequently accessed using a predicate that is not directly captured in a column, but can be derived, in order to get partition pruning benefits.
The virtual column does not require any storage.

11. Oracle Database Partitioning for ILM

The Oracle Database Partitioning option provides an uniquely ideal platform for implementing an ILM solution offering:

11.1 Application Transparency

There is no need to customize applications
Data can easily be moved and accessed at the different stages of its lifecycle.
Flexibility required to quickly adapt to any new regulatory compliance.

11.2 Fine-grained

View data at a very fine-grained level as well as group related data together, whereas storage devices only see bytes and blocks.

11.3 Low-Cost

Low cost storage is a key factor in implementing ILM.

11.4 Enforceable Compliance Policies

It is imperative to show to regulatory bodies that data is being retained and managed in accordance with the regulations defining security and audit policies, which enforce and log all access to data. In general, Enforceable Compliance Policies where Oracle Partitioning becomes mission-critical involve:

Data Retention

Immutability
Privacy
Auditing
Expiration

12. Oracle Partitioning for Datawarehousing

Datawarehouses often require techniques both for managing large tables and providing good query optimization.

Oracle Partitioning is beneficial in attaining the following Datawarehousing goals, namely:

12.1 Scalability

Partitioning is effective scaling a data warehouse by dividing database objects into smaller pieces, enabling access to smaller, more manageable objects. Providing direct access to smaller objects addresses the scalability requirements of data warehouses:

Bigger Database
Bigger Individual tables: More Rows in Tables
More Users Querying the System
More Complex Queries.

12.2 Performance

12.2.1 Partition Pruning

Partition pruning is an essential performance feature since the optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions when building the partition access list.

Besides, partition pruning greatly reduces the amount of data retrieved from disk and shortens processing time, thus improving query performance and optimizing resource utilization.

12.2.1.1 Basic Partition Pruning Techniques

The optimizer utilizes a wide variety of predicates for pruning. The three predicate types, equality, range, and IN-list, are the most commonly used cases of partition pruning.

12.2.1.2 Advanced Partition Pruning Techniques

Oracle also prunes in the presence of more complex predicates or SQL statements involving partitioned tables. For instance, when a partitioned table is joined to the subset of another table, constrained by a WHERE clause condition.

12.3 Manageability

Manageability is greatly improved of the usage of Oracle Enterprise Manager Database and Grid Control and the usage of supplied packages.

13. Partitioning Materialized Views

The underlying storage for a materialized view is a table structure, and therefore partitioning materialized views is quite similar. When the database rewrites a query to run against materialized views, the query can take advantage of the same performance features as those queries running against tables MV’s directly benefit from. Similarly, a rewritten query may eliminate materialized view partitions and it can take advantage of partition-wise joins, when joins back to tables or with other materialized views are necessary.
The next sample code illustrates how to effectively create a compressed materialized view partitioned by hash, which uses an aggregation on period_code.

Exhibit 24. SQL DDL Creating a Partition Materialized View

13.1 Partition Exchange Load (PEL)

Partitions can be added using Partition Exchange Load (PEL). When using PEL, a separate identical table to a single partition is created, including the same indexes and constraints, if any.

13.2 Partitioning and Materialized View Refresh Strategies

Full refresh
Fast (incremental) refresh based on materialized view logs against the base tables
Manually using DML, followed by ALTER MATERIALIZED VIEW CONSIDER FRESH

Besides, to enable query rewrites, set the QUERY_REWRITE_INTEGRITY initialization parameter.

Likewise, in order to manually keep materialized views up to date, the init.ora parameter QUERY_REWRITE_INTEGRITY must be set to either TRUSTED or STALE_TOLERATED. When using materialized views and base tables with comparable partitioning strategies, then PEL can be an extremely powerful way to keep materialized views up-to-date manually. Here is how PEL can work:

Create tables to enable PEL against the tables and materialized views
Load data into the tables, build the indexes, and implement any constraints
Update the base tables using PEL
Update the materialized views using PEL.

Besides, there is a need for a DBA to execute ALTER MATERIALIZED VIEW CONSIDER FRESH for every materialized view updated using this strategy.
Likewise, partitioning also effectively addresses OLTP features and characterization such as, namely:

Short response time
Small transactions
Data maintenance operations
Large user populations
High concurrency
Large data volumes
High availability
Lifecycle related data usage

13.3 Storage Management

The following approaches can be used, namely:

13.3.1 High Availability: Implementing storage redundancy.

Hardware-based mirroring
Using ASM for mirroring
Software-based mirroring not using ASM.

13.3.2 Performance: optimum throughput from storage devices, multiple disks must work in parallel.

Hardware-based striping
Software-based striping using ASM
Software-based striping not using ASM

13.3.3 ILM

In an Information Lifecycle Management environment, it is not possible to use striping across all devices, because all data would then be distributed across all storage pools, in contrast with different storage pools typically involving different performance characteristics.

13.3.4 Partition Placement

The following scenarios imply special considerations for partition placement, namely:

Using Bigfile Tablespaces (due to one actual big file is used.)
Customization (since other Oracle options are used in conjunction with it.)
Oracle Exadata (a new VLDB Oracle database infrastructure with superfast performance capabilities. )

14. Partitioning Support for LOB

Oracle Partitioning support the storage of LOB types, such as BLOBs and BFILE types, and user-defined object datatypes.

The next exhibits illustrate some sample code involving large objects (LOBs) and user-defined object datatypes, as shown:

Exhibit 25. One option for LOB support can be attained via Index Organized Tables, storing LOBs in a separate tablespace.

Exhibit 26. Partitioning Support for a CLOB Stored as Basicfile with Chunk and Retention set.

Exhibit 27. Partitioning Support for Object User-Defined Datatypes.

Exhibit 28. Partitioning Support for Object User-Defined Datatypes using a primary key (see next 2 exhibits).

Exhibit 29. Partitioning Support for Object User-Defined Datatypes using the object Identifier as a primary key.

Exhibit 30. Partitioning Support for Object User-Defined Datatypes using the object Identifier as a primary key.

Exhibit 31. Partitioning Support for Nested Tables using Object User-Defined Datatypes

Exhibit 32. Partitioning Support for Varrays.

Exhibit 33. Partitioning Support for XML.

15. Best Practices

In relation to best practices, it could be relevant to apply important considerations, tips and techniques, and specific constraints.

15.1 Special Considerations

Use Oracle partitioning strategic recommendations for each database system environment accordingly.
When in doubt refer to sample code, forum discussions, and case studies.
Consolidate recommendations made in this presentation into a practical enterprise policy framework.
From the business and functional point of view, a partitioning strategy is normally identified with a functional goal-seeking perspective, and therefore it needs to be mapped to an Oracle partitioning technical recommendation or specific partitioning strategy matching those business requirements, regulatory compliance , or system’s platform, among others.

15.2 Tips and Techniques

Using multiple block size caches can increase load throughput in DSS, in particular, when using indexes in a block size larger than the table.
This is more important volumes are based on a (Stripe and mirror everything) SAME-approach (i.e., RAID 0+1).
Likewise, performance optimization and contention reduction can be attained in OLTP systems using the same approach, when the appropriate partitioning strategy is being used, in accordance to the strategic recommendations previously made.

15.3 Constraints

As previously stated, there is no support for LONG and LONG RAW data types on any Oracle partitioned object or any partitioning strategy discussed.
Likewise, an encrypted column cannot serve as partitioning key.
When migrating to Oracle11g or any other recent release, consider changing LONG and LONG RAW datatypes into CLOB, BLOB accordingly for current and future release forward compatibility and improved manageability.
A VARRAY of XML data types cannot be set in a partitioned table (via an SQL DDL statement.)
Certain datatypes have size and storage constraints such as LOBs or large VARCHAR2 definitions.

16. Concluding Remarks

Upon completion of this resarch study, the author has arrived to the following remarkable conclusions based on his experience in the field:

Oracle partitioning provides effective strategies to attain time and resource optimization, including CPU and memory.

Oracle Partitioning option is extremely practical to achieve regulatory compliance.
Oracle partitioning is mission-critical to attain most needed scalability, manageability, performance, and high-availability in any system platform.
Oracle partitioning can easily mix with other technologies, in particular, with multiple block size caches, which enable performance optimization by allowing optimal usage of I/O and memory resources with no contention.

A Comprehensive Guide to Oracle Partitioning with Samples

2009-06-15T02:08:00.005-04:00

Following up with my series of “Blogs through Pictures” (BTP), I am publishing my slides from the NYOUG Summer General Meeting, which shined for its large attendance and great good enthusiasm.

As a pointed out to my audience during my presentation, the first fourteen slides outline the rest of the presentation where I discuss the topic more in detail with good examples and explicit, tested sample code.

2008-12-16T14:59:00.044-05:00

Presenting at the NYOUG December 2008 Meeting at St. John University

Tuning the Large Pool for RMAN

This is a visual summary of my presentation at St. John University on December 10, 2008. The focus of this presentation is the tuning of the Large Pool by finding the optimal sizing that maximizes I/O throughput and related I/O tasks, and best shared memory segment, and time utilization.