Friday Jul 22, 2016

Enabling Compression for Partitions – What I Forgot to Cover in an Earlier Blog

In an earlier blog I discussed enabling compression for existing tables but failed to discuss enabling compression on partitions. So in this blog we’re going to briefly examine the ways in which users can enable compression for partitions while partitions are online. Sorry if this covers some materials we’ve already looked at, but I wanted to bring this topic together within a single blog for you.[Read More]

Friday Jul 08, 2016

Advanced Row Compression: When do you see compression space savings?

Advanced Row compression is designed to compress table data for all types of operations on table segment, such as inserts, updates and bulk loads (insert direct load). Advanced Row compression will be triggered and will keep the data in the compressed format even on data modification, ensuring optimal compression. [Read More]

Friday Jun 24, 2016

Oracle Advanced Compression Compared to Storage-Based Compression – The Important Differences

Customers often ask if they should use Advanced Compression for their database data instead of storage-based compression. While this blog discusses the points I usually make on this topic, please note that this blog isn’t intended to suggest that Oracle Advanced Compression replace storage-based compression for all general application uses, instead, we’ll discuss why Oracle Advanced Compression is better suited for compressing Oracle Database data.[Read More]

Friday Jun 10, 2016

Row Level Locking with Hybrid Columnar Compression (HCC)

Oracle’s Hybrid Columnar Compression technology is a different and new method for organizing data within a database block. As the name implies, this technology utilizes a combination of both row and columnar methods for storing data. This hybrid approach achieves the compression benefits of columnar storage, while avoiding the performance shortfalls of a pure columnar format. A logical construct called the compression unit (CU) is used to store a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together, compressed and stored in a compression unit. With Oracle Database 11g Release 2, HCC supported compression unit level locking; that is, locking the entire compression unit on an active transaction modifying a single row in the unit.

[Read More]

Friday May 27, 2016

Online Move Partition to Any Compressed Format - A Lesser Known Feature of Advanced Compression

In this blog we’re going to discuss the Online Move Partition to Any Compressed Format feature, one of the many lesser known features included with the Advanced Compression option.

Here are some key points:

You can use the ALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITION statement to move a table partition or subpartition. When you use the ONLINE keyword with either of these statements, DML operations can continue to run uninterrupted on the partition or subpartition that is being moved.

[Read More]

Friday May 13, 2016

Critical Statistics for Hybrid Columnar Compression

There are various Hybrid Columnar Compression (HCC) session level statistics available to help identify space savings, Exadata Offload benefits and other query benefits with this feature. Since the statistic descriptions didn’t make it into the Oracle Reference manual and have been confusing for many since they were introduced in 11.2, I thought it was a good idea to list few of the critical ones that can help you get an idea of benefits of HCC.[Read More]

Friday Apr 29, 2016

Advanced Row Compression – What Not to Compress (and Some Best Practice Insights)

In this blog we’re going back to revisit Advanced Row Compression and discuss what not to compress. The use of Advanced Row Compression requires the Advanced Compression option.

In terms of what not to compress, here are some recommendations that I typically make to anyone using, or looking to use Advanced Row Compression, these include: 1) Advanced Row Compression is NOT supported for use with tables that have LONG data types, 2) If a table/partition is used as a queue, i.e. rows are inserted then deleted and this is done repeatedly, then that table /partition is not a candidate for Advanced Row Compression and 3) Advanced Row Compression is also not supported on tables with row dependencies and on clustered tables.

[Read More]

Friday Apr 15, 2016

Critical Statistics for OLTP Table Compression

There are various OLTP Table Compression session level statistics available to help identify space savings with this feature. These statistics are very important to figure out the benefits of compression, and if compression is indeed helping in your environment. Since the statistic definitions didn't make it into the Reference manual, I thought it was a good idea to list few of the critical ones for OLTP Compression that can help you get an idea of space savings, amount and kind of DML activity in your environment.[Read More]

Monday Apr 11, 2016

Oracle OpenWorld 2016 Call for Papers!

Oracle OpenWorld 2016 Call for Papers has begun!

The Oracle OpenWorld 2016 call for papers is now open! Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held September 18 - 22, 2016 at the Moscone Center in San Francisco. Details and submission guidelines are available on the Oracle OpenWorld Call for Papers web site. The deadline for submissions is Friday, May 9, 11:59 p.m. PDT.

We look forward to checking out your sessions on Oracle Advanced Compression, Hybrid Columnar Compression and Automatic Data Optimization and how these features change the way you do business!

Friday Apr 01, 2016

ADO – Automating Storage Tiering for Information Lifecycle Management

In this blog we’re going to continue the ADO discussion and talk about the role ADO plays in database storage tiering. The use of ADO requires the Advanced Compression option.

How does ADO storage tiering differ from storage-level tiering? Good question!

Storage-level tiering is blind to database I/O types, so it can't tell if a read is for an OLTP transaction, a DW scan, a stats-gathering job, or a backup (for example). So storage-level tiering can sometimes get it wrong in terms of moving segments of data to the wrong tier at the wrong time. Heat Map is fully aware of different types of database I/O, and different reasons for doing those I/O's, so it will automatically exclude things like RMAN backups, or database maintenance tasks, etc…

Keep in mind that ADO storage tiering operates at the segment level, so when an ADO policy implements storage tiering the entire segment is moved and this movement is one direction, meaning that ADO storage tiering is meant to move colder segments from high performance storage to slower, lower cost storage.

[Read More]

Thursday Mar 31, 2016

Heartland Oracle User Group Conference April 28th in Omaha NE

If you can make it to the Heartland Oracle User Group Conference, on April 28th 2016, then please join me for my 9:15am to 10:30am session on Oracle Compression Best Practices.

In the session I will discuss best practices, gathered from users across the globe, related to index and data compression. I will also discuss how Basic, Advanced Row, Hybrid Columnar and Index Compression are enabled for new and existing database objects and how Automatic Data Optimization (ADO) enables data to be compressed in place. I will also discuss how Automatic Data Optimization can be used to create policies to automate storage tiering and compression tiering -- based on the actual usage of tables and partitions.

If you’re currently using Oracle Compression, including Advanced Compression or HCC, or are considering using Oracle compression or ADO, then this session is a great chance to hear about best practices and get your questions answered (or just stop by and say hello).

Please also see Dan Glasscocks keynote, Noon – 1:00pm, on Oracle’s Cloud Computing Strategy.

For the full agenda, and to register for this event, please see the Heartland OUG website here.

Friday Mar 25, 2016

Compressing your Indexes: Advanced Index Compression (PART 2)

With Advanced Index Compression, it is now possible to simply enable compression for all your B-Tree indexes, and Oracle will automatically compress every index leaf block when beneficial, while taking care of computing the optimal prefix column length for every block. This makes index compression truly local at a block level, where both the compression prefix table as well as the decision on how to compress the leaf block is made locally for every block and aims towards achieving the most optimal compression ratio for the entire index segment.

[Read More]

Friday Mar 18, 2016

ADO Example – Automating Compression Tiering for Information Lifecycle Management

In this blog we’re going to explore what exactly Automatic Data Optimization (ADO) is and the role ADO plays in both database compression tiering and storage tiering. The use of ADO requires the Advanced Compression option.

What does ADO provide for your organization?

ADO allows your organization to create policies that automate data compression and data movement and to implement the tiering of compression and storage. ADO policies are specified at the segment or row level for tables and table partitions. Oracle Database periodically evaluates ADO policies, and uses the information collected by Heat Map to determine which policies to execute. Policies will be evaluated and executed automatically, in the background, during the maintenance window. ADO policies can also be evaluated and executed anytime by a DBA, manually or via a script (the DBMS_ILM package supports immediate evaluation or execution of ADO related tasks, see here).

[Read More]

Monday Mar 14, 2016

Compressing your Indexes: Index Key Compression (PART 1)

Index Key Compression is perhaps one of the oldest compression features within the Oracle database, released with Oracle 8.1.3 (long before Basic Table Compression in 9.2). If used correctly, Index Key Compression has the potential to substantially reduce the overall size of indexes. It helps both multi-column unique indexes and non-unique indexes alike and is also one of the most critical index optimization options available.

[Read More]

Friday Mar 11, 2016

Heat Map – Vital to Automating Information Lifecycle Management (ILM)

In the next few blogs we’re going to explore the benefits of automating Information Lifecycle Management as well as the specific Oracle Database features needed to enable both database compression tiering and storage tiering.

But first, why should you care about database compression tiering and storage tiering?

An organization (or even a single application) does not access all its data equally: the most critical or frequently accessed data needs the best available performance and availability – this data is typically best suited for Tier 1 storage. But to provide this best access quality to all the data is costly, inefficient and is often architecturally impossible. Ideally, organizations need to implement storage tiering, deploying their data on different tiers of storage so that less-accessed (“colder”) data is migrated away from the costliest and fastest storage. This “colder” data remains online and available, but is stored on Tier 2 storage, which is typically lower cost and slower speed, but whose effect on the overall application performance is minimal, due to the rarity of accessing this “colder” data as this data is typically only used occasionally or for reporting purposes.

[Read More]

The Database Storage Optimization blog is written by the Oracle Product Management team and highlights features and issues associated with database storage optimization. The views expressed on this blog are those of the author(s) and do not necessarily reflect the views of Oracle.


« July 2016