X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

Recent Posts

Who Uses Advanced Compression – The List of Customer Use Cases Might Surprise You!

A very common question, that many organizations ask, is “who uses Advanced Compression”? These same organizations are often surprised to learn that Advanced Compression is used, around the globe, by just about every type of company or agency, including: financial, government, education, healthcare, utilities, insurance, communications, retail, manufacturing and more. Below are some detailed Advanced Compression case studies that discuss how these organizations use Oracle Compression (and Information Lifecycle Management features), as well as the benefits they have seen and any concerns they had about compression. In some cases, these case studies include the Proof-of-Concept (PoC) testing performed by the organization, as well as their testing results. Oracle Advanced Compression Helps Global Fortune 500 Company Meet Storage Savings Initiative (see here) Oracle Advanced Compression reduces business-critical SAP data storage requirements by 40% at Goodman Fielder (see here) Automatic Data Optimization Reduces ILM Development and Administrative Time and Costs at Yapı Kredi Bank (see here) To see more Oracle Compression use cases, please see the links below: Oracle.com: https://www.oracle.com/search/customers (search for “Advanced Compression”) Oracle Technology Network: http://www.oracle.com/technetwork/database/options/compression/learnmore/index.html In my next blog, we will continue discussing common compression questions. The database storage optimization adventure continues!

A very common question, that many organizations ask, is “who uses Advanced Compression”? These same organizations are often surprised to learn that Advanced Compression is used, around the globe, by...

The Relationship of Direct Path/Conventional Path Inserts to Compression – What You need to Know

Performing bulk load operations, and choosing either direct path or conventional path methods (see here), can have a significant influence in regards to load performance with compression. Users performing bulk load insert operations may see slightly slower insert performance, particularly if they are inserting a large number of rows using a conventional path load. The reason why conventional path loads may be slower, for a large number of rows, is that as the new rows are inserted into existing compressed blocks the inserts are performed uncompressed, then as additional inserts are performed on the same block, and the block begins to fill up, the internal threshold will be met and the block will be compressed. If additional space is freed up after the compression then inserts will again be performed on the block, thus leading to compression again, possibly multiple more times for the same block, during the same conventional path load operation. This means that when using conventional path inserts it is possible that the same block is being compressed multiple times during the same bulk load operation – consuming CPU resources and time. If the workload is dominated by conventional path inserts, it is possible there will be some additional overhead -- if a block is recompressed repeatedly. Direct path load operations are preferred (if possible) when operating on larger numbers of rows since, unlike conventional path loads, direct path loads are performed above the high water mark, so blocks are completely filled and compressed only once, and then written to disk. This streamlines the bulk inserts and avoids the multiple compressions of the same block that is possible when performing bulk inserts using conventional path loads. If you are unsure if bulk loads are using direct path or conventional path load methods, please see this Oracle White Paper (pages 7-8) for more information about determining which method is being used. Also, as a reminder, if you are attending the East Coast Oracle Users Conference (ECO) 2018 in September, please do not miss an opportunity to gain insights about Oracle compression and learn how to get the best out of Oracle compression. Details about the topic of my session: Compression Usage Insights – Getting the Best Out of Oracle Compression Understanding how Oracle Database data and index compression works, and what has changed in the latest versions, can help you in getting the best results with Oracle Compression. Join Oracle Product Management in this session to gain insights regarding when compression occurs during bulk loads and DML inserts/updates, when to use Index Key (prefix) Compression versus Advanced Index Compression, how to track compression using AWR’s as well as recent Advanced Row Compression and Hybrid Columnar Compression enhancements with Oracle Database. This session is the perfect opportunity to get all your compression questions answered by Product Management. Tuesday, September 18, 2018, 1:15pm until 2:15pm (Grand Ballroom 2, 3) Hilton North Raleigh/Midtown Hotel 3415 Wake Forest Road Raleigh, NC 27609 For more information, or to see the agenda, or to register (see here) In my next blog, we will continue discussing common compression questions. The database storage optimization adventure continues!

Performing bulk load operations, and choosing either direct path or conventional path methods (see here), can have a significant influence in regards to load performance with compression. Users...

Join Me for the First Ever Advanced Compression Ask Tom Session and at ECO 2018 in Raleigh!

Don't miss the first ever Ask TOM Office Hours session for Oracle Advanced Compression. What is Ask TOM? Ask The Oracle Masters (or AskTOM for short), is where you get definitive answers for your Oracle questions from experts that work directly on the database products within Oracle Corporation. (see here for more information about Ask Tom) Details about the Ask TOM Advanced Compression session: The focus of the Advanced Compression session will be on Basic Compression, OLTP Table Compression and Advanced Row Compression. The Advanced Compression Ask TOM session is scheduled for Thursday, August 9th at 3pm PDT. See here for details! Also, if you are attending the East Coast Oracle Users Conference (ECO) 2018 in September, please do not miss an opportunity to gain insights about Oracle compression and learn how to get the best out of Oracle compression. Details about the topic of my ECO 2018 session: Compression Usage Insights – Getting the Best Out of Oracle Compression Understanding how Oracle Database data and index compression works, and what has changed in the latest versions, can help you in getting the best results with Oracle Compression. Join Oracle Product Management in this session to gain insights regarding when compression occurs during bulk loads and DML inserts/updates, when to use Index Key (prefix) Compression versus Advanced Index Compression, how to track compression using AWR’s as well as recent Advanced Row Compression and Hybrid Columnar Compression enhancements with Oracle Database. This session is the perfect opportunity to get all your compression questions answered by Product Management. Speaker: Gregg Christman, Oracle Product Management Details about date, time and location of my session at ECO 2018: Tuesday, September 18, 2018, 1:15pm until 2:15pm (Grand Ballroom 2, 3) Hilton North Raleigh/Midtown Hotel 3415 Wake Forest Road Raleigh, NC 27609 For more information, or to see the agenda, or to register (see here) In my next blog, we will continue discussing common compression questions. The database storage optimization adventure continues!

Don't miss the first ever Ask TOM Office Hours session for Oracle Advanced Compression. What is Ask TOM? Ask The Oracle Masters (or AskTOM for short), is where you get definitive answers for your...

Using Flashback Data Archive? Reduce Your Storage Requirements by Compressing History Tables

Using Flashback Data Archive? Never heard of Flashback Data Archive? Maybe a brief overview of what Flashback Data Archive is would be useful before we discuss how to compress the history tables managed by Flashback Data Archive. Oracle’s Flashback Data Archive (FDA) feature provides a mechanism for tracking changes to production databases that is secure, efficient, easy to use and application transparent. FDA allows organizations to configure historical data capture in a matter of minutes, providing a centralized and seamlessly queryable historical data store. Flashback Data Archive is configured with a retention time and data archived by Flashback Data Archive is retained, for the retention time specified, in associated history tables that are managed by FDA. Organizations can use Flashback Data Archive in the same manner as other Flashback features to view or restore the data as of a time (or time range) in the past. So where does compression come into play? The source production tables can be compressed using either Advanced Row Compression or Hybrid Columnar Compression. However, the topic of this blog is the compression of the history tables managed by Flashback Data Archive. As the SQL syntax indicates below, to compress history tables, specify OPTIMIZE DATA to enable optimization (compression) for Flashback Data Archive history tables. This instructs the database to optimize the storage of data in history tables using Advanced Row Compression (requires Oracle Advanced Compression). Specify NO OPTIMIZE DATA to instruct the database not to optimize (compress) the storage of data in the history tables.      CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive        TABLESPACE tablespace        [flashback_archive_quota]        [ [ NO] OPTIMIZE DATA ]       flashback_archive_retention        ; The default is NO OPTIMIZE DATA. For more information (and usage examples) about Flashback Data Archive and OPTIMIZE DATA, please see: Oracle® Database Development Guide. (See here) Oracle Flashback Data Archive white paper. (See here) I have no idea what we will discuss in next month’s blog, but I will figure out something before then. Have a great summer! The database storage optimization adventure continues!

Using Flashback Data Archive? Never heard of Flashback Data Archive? Maybe a brief overview of what Flashback Data Archive is would be useful before we discuss how to compress the history tables...

Reduce the Storage Requirements of SecureFiles Data with Advanced LOB Deduplication

In addition to Advanced LOB Compression (which was discussed in last month’s blog (see here)), Advanced Compression provides another feature, Advanced LOB Deduplication, to help reduce the storage requirements of SecureFiles data. Advanced LOB Deduplication eliminates duplicate copies of SecureFiles data. This means that Oracle Database stores one image of the SecureFiles data and replaces the duplicate copies with references to this image. Advanced LOB Deduplication (previously known as SecureFiles LOB Deduplication with Oracle Database 11g) is a feature of Advanced Compression. Consider an email application where 10 users receive an email with the same 1MB attachment.  Without Advanced LOB Deduplication, the database would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application used Advanced LOB Deduplication, it will store the 1MB attachment just once.  That is a 90% savings in storage requirements.  Advanced LOB Deduplication eliminates multiple, redundant copies of SecureFiles data and is completely transparent to applications. Oracle automatically detects multiple, identical SecureFiles data and stores only one copy, thereby saving storage space. Deduplication not only simplifies storage management but also results in significantly better performance, especially for copy operations. Deduplication Insight: Deduplication is done on a per LOB segment basis; duplicates are not detected across multiple LOB segments. While deduplication can be specified at the partition level, duplicate detection does not span across partitions or subpartitions for partitioned SecureFiles columns. The DEDUPLICATE keyword is used to enable Advanced LOB Deduplication checking for SecureFiles. Oracle uses a secure hash index to detect duplicate SecureFile data and stores a single copy for all identical content. The KEEP_DUPLICATES keyword is used to turn off deduplication and retain duplicate copies of SecureFile data. Deduplication is turned off by default. This example demonstrates how to create a table with a SECUREFILE LOB column and LOB deduplication enabled. CREATE TABLE t1 ( a CLOB)     LOB(a) STORE AS SECUREFILE (         DEDUPLICATE         CACHE     ); For more information (and usage examples) about SecureFiles and LOB compression and deduplication, please see: Oracle® Database SecureFiles and Large Objects Developer's Guide. (See here) Oracle SecureFiles Technical White paper. (See here) We will discuss Flashback Data Archive history table compression in next month’s blog. The database storage optimization adventure continues!

In addition to Advanced LOB Compression (which was discussed in last month’s blog (see here)), Advanced Compression provides another feature, Advanced LOB Deduplication, to help reduce the storage...

Reduce the Storage Requirements of SecureFiles Data with Advanced LOB Compression

Advanced LOB Compression (previously known as SecureFiles LOB Compression with Oracle Database 11g) is a feature of Advanced Compression that can help reduce the storage requirements of SecureFiles data. If you are not familiar with SecureFiles, SecureFiles offers a ‘best-of-both-worlds’ architecture for storing unstructured content such as documents, images, spreadsheets and XML files and is specifically engineered to deliver high performance for file data equal to or better than traditional file systems, while retaining the advantages of Oracle Database.  SecureFiles is a superset of the ANSI standard LOB data type and offers easy migration from existing BasicFiles LOBs, the precursor to SecureFiles. With SecureFiles, organizations can manage all relational data and associated file data with Oracle Database using a single security/audit model, a unified backup & recovery process and perform seamless retrievals across all information.  Advanced LOB Compression utilizes industry standard compression algorithms to minimize the storage requirements of SecureFiles data. With Advanced LOB Compression, files such as documents or XML files often achieve a 2x to 3x compression ratio. Compression not only results in savings in storage but can also improve performance by reducing I/O, buffer cache requirements, redo generation and encryption overhead. Random access reads and writes to compressed SecureFiles are achieved without the need to decompress the entire file. Only the sub portion of the compressed file, corresponding to the logical offset being read or written, needs to be decompressed, thus saving CPU and I/O. Advanced LOB Compression automatically avoids compressing data that would not benefit from compression, such as a document that was compressed via a 3rd party tool before being inserted into the database as a SecureFiles file. There are three levels of Advanced LOB Compression:  LOW, MEDIUM, and HIGH.  By default, Advanced LOB Compression uses the MEDIUM level, which typically provides good compression with a modest CPU overhead of 3%-5%. Advanced LOB Compression LOW is optimized for high performance. Advanced LOB Compression LOW maintains about 80% of the compression achieved through MEDIUM, while utilizing typically 3x less CPU. The LOW compression option uses an extremely lightweight compression algorithm that removes the majority of the CPU cost that is typical with file compression. Compressed SecureFiles LOBs at the LOW level generally provides a very efficient choice for SecureFiles LOB storage. SecureFiles LOBs compressed at LOW generally consume less CPU time and less storage than BasicFiles LOBs, and typically help the application run faster because of a reduction in disk I/O. Advanced LOB Compression HIGH achieves the highest storage savings but incurs the most CPU overhead.  While the LOW, MEDIUM, and HIGH options provide varying degrees of compression, remember that the higher the compression, the higher the latency incurred. The HIGH setting incurs more work, but compresses the data better. The COMPRESS keyword is used to enable Advanced LOB Compression. Advanced LOB Compression is separate from table (Advanced Row) or index (Advanced Index/Prefix) compression. Setting table or index compression does not affect Advanced LOB Compression or vice versa. These examples demonstrate how to use CREATE TABLE statements for specific compression scenarios. Creating a SecureFiles LOB Column with LOW Compression CREATE TABLE t1 (a CLOB) LOB(a) STORE AS SECUREFILE( COMPRESS LOW CACHE NOLOGGING ); Creating a SecureFiles LOB Column with MEDIUM (default) Compression CREATE TABLE t1 ( a CLOB) LOB(a) STORE AS SECUREFILE ( COMPRESS CACHE NOLOGGING ); Creating a SecureFiles LOB Column with HIGH Compression CREATE TABLE t1 ( a CLOB) LOB(a) STORE AS SECUREFILE ( COMPRESS HIGH CACHE ); For more information (and usage examples) about SecureFiles and LOB compression, please see: Oracle® Database SecureFiles and Large Objects Developer's Guide. (See here) Oracle SecureFiles Technical White Paper. (See here) In addition to Advanced LOB Compression, Advanced Compression provides another feature, Advanced LOB Deduplication, to help reduce the storage requirements of SecureFiles data. Advanced LOB Deduplication eliminates duplicate copies of SecureFiles data -- Oracle Database stores one image of the SecureFiles data and replaces the duplicate copies with references to this image.  We will discuss Advanced LOB Deduplication in next month’s blog. The database storage optimization adventure continues!

Advanced LOB Compression (previously known as SecureFiles LOB Compression with Oracle Database 11g) is a feature of Advanced Compression that can help reduce the storage requirements of SecureFiles...

A Common Question about Compression and Encryption. Also, Stop by GLOC 2018 to Talk Compression

A very common question is whether Oracle Advanced Compression works with Oracle Advanced Security Transparent Data Encryption (TDE) – and the answer is yes, compression and encryption do work well together. Although it is important to note that it does matter whether you are using tablespace encryption or column encryption. With tablespace encryption, compression happens before encryption. This ensures that organizations receive the maximum space and performance benefits from compression, while also receiving the security of encryption at rest. With column encryption, compression happens after encryption. This means that compression will have minimal effectiveness on encrypted columns. For more about Oracle Advanced Security Transparent Data Encryption (TDE) (see here). In my next blog, we will continue discussing common compression questions. Also, if you are attending the Great Lakes Oracle Conference (GLOC) 2018 in May, please do not miss an opportunity to gain insights about Oracle compression and learn how to get the best out of Oracle compression. Details about my session: Compression Usage Insights – Getting the Best Out of Oracle Compression Understanding how Oracle Database data and index compression works, and what has changed in the latest versions, can help you in getting the best results with Oracle Compression. Join Oracle Product Management in this session to gain insights regarding when compression occurs during bulk loads and DML inserts/updates, when to use Index Key (prefix) Compression versus Advanced Index Compression, how to track compression using AWR’s as well as recent Advanced Row Compression and Hybrid Columnar Compression enhancements with Oracle Database. This session is the perfect opportunity to get all your compression questions answered by Product Management. Speaker: Gregg Christman, Oracle Product Management Details about my session: Thursday, May 17, 2018, 2:45pm until 3:45pm (Session 9 - Room LL10AB) Cleveland Public Auditorium 500 Lakeside Ave Cleveland, OH. 44114 For more information, or to see the agenda, or to register (see here) The database storage optimization adventure continues!

A very common question is whether Oracle Advanced Compression works with Oracle Advanced Security Transparent Data Encryption (TDE) – and the answer is yes, compression and encryption do work well...

Going to the NWOUG 2018 Seattle Training Day? Stop by my Session to Talk About Compression

If you are attending the Northwest Oracle Users Group (NWOUG) Seattle Training Day, please do not miss an opportunity to attend my session and gain insights about Oracle Compression and how to get the best out of Oracle compression. Details about my session: Compression Usage Insights – Getting the Best Out of Oracle Compression Understanding how Oracle Database data and index compression works, and what has changed in the latest versions, can help you in getting the best results with Oracle Compression. Join Oracle Product Management in this session to gain insights regarding when compression occurs during bulk loads and DML inserts/updates, when to use Index Key (prefix) Compression versus Advanced Index Compression, how to track compression using AWR’s as well as recent Advanced Row Compression and Hybrid Columnar Compression enhancements with Oracle Database 12c Release 2. This session is the perfect opportunity to get all your compression questions answered by Product Management. Speaker: Gregg Christman, Oracle Product Management Please check the NWOUG Seattle 2018 Training Day agenda for the time and room of my session. Details about the event: Tuesday, March 6, 2018, 8:00 AM until 5:00 PM The Seattle Marriott Waterfront Hotel Marriott Seattle Waterfront 2100 Alaskan Way Seattle, WA  98121 For more information or to register (see here) I hope to see you in Seattle! In my next blog, we will continue discussing common compression questions. The database storage optimization adventure continues!

If you are attending the Northwest Oracle Users Group (NWOUG) Seattle Training Day, please do not miss an opportunity to attend my session and gain insights about Oracle Compression and how to get the...

Planning or Performing an Advanced Compression Proof of Concept? Start Here!

In my last blog we discussed a common question regarding Advanced Compression. Before we continue discussing the other common questions (in future blogs) I thought it would be useful to point out a new Advanced Compression white paper that I believe is very useful to organizations planning (or currently performing) a proof of concept (PoC) with Advanced Compression. The document is not meant to be a step-by-step guide to performing a compression PoC. Instead, this document provides best practices learned from customer PoC’s and provides insights (about Advanced Compression) to help plan your compression PoC, as well as help you understand the results of your PoC. Some of the topics discussed in the document, that I believe are useful to know for your Advanced Compression PoC, includes: Enabling Advanced Row Compression When Advanced Row Compression Occurs Direct-Path versus Conventional-Path Inserts Index Key Compression and Advanced Index Compression What to Consider for Before PoC Testing Begins Direct-Path and Conventional-Path Loads and AWR RMAN Compression More about Compression, Performance and Compression Ratios What does the Typical Compression PoC Look Like? Compression Advisor Example of Compression PoC as a Multi-Step Process The document is available on the Oracle Technology Network (OTN) at the link below: Oracle Advanced Compression Proof-of-Concept (POC) Insights and Best Practices I hope you find this white paper informative. In my next blog(s) we will discuss additional common compression questions. The database storage optimization continues!

In my last blog we discussed a common question regarding Advanced Compression. Before we continue discussing the other common questions (in future blogs) I thought it would be useful to point out a...

Should You Compress the Entire Database or Not? A Common Compression Question.

In this blog, and my next blogs, we are going to try something a bit different. We are going to discuss some of the most common compression-related questions that I have been asked. A very common question, and a question where the answer has changed over the last few years, is the following: Question: Should we compress the entire database? A few years back my answer, to this question, would have been to suggest that you did not have to compress the entire database and instead could compress the largest tables first, and then decide if you really want to compress your smaller tables later. In many cases, users would compress their largest tables and then often not compress the smaller tables/partitions. In those days, I only really talked about the storage savings related to compression, and thought that the storage savings for a small table, say a few GB’s, may, by itself, not be that significant. While this approach to the database compression question is still one that many users choose to implement, it did fail to take into to account some additional considerations that are very relevant to the question we are discussing. What I had failed to consider, and came to realize over time after talking with users, is that there are indeed benefits to compressing even the smallest tables. First, compressing even a very small table, say just a few GB will often yield performance benefits. Reducing the size of a table/partition by 50% (which is the average compression for Oracle Advanced Row Compression) can benefit query performance by reducing the number of blocks (reducing I/O) and may make the newly even smaller table memory resident in the data buffers (remember that we keep data and indexes compressed in memory). Even if the amount of space saved for a small table/partition is not, by itself, significant, remember that when you add up all the storage saved by even your smallest tables, that can be pretty significant. This storage savings, for the small tables, will also apply to your future storage needs as those small tables continue to grow in the future. No matter if, you choose to compress your smaller tables/partitions or not, there are two best practices related to what tables/partitions not to compress when using Advanced Row Compression, these include: If the table/partition is used as a queue, i.e. rows are inserted into the table, then later most or all of the rows are deleted, then more rows are inserted then deleted, then you shouldn't compress the table Advanced Row Compression is NOT supported for use with tables that have LONG data types I hope you found this discussion informative. In my next blog(s) we will discuss additional common compression questions. The database storage optimization adventure continues!

In this blog, and my next blogs, we are going to try something a bit different. We are going to discuss some of the most common compression-related questions that I have been asked. A very...

Row Level Compression with Advanced Row and Hybrid Columnar Compression (HCC)

In this blog we are going to discuss row level compression with Advanced Row Compression, and now available with Hybrid Columnar Compression (with Oracle Database 12.2), when using Automatic Data Optimization (ADO). In an earlier blog we discussed how Automatic Data Optimization and Heat Map automate the compression of data with Oracle Database 12c. (see here) As was discussed in the earlier blog, with Oracle Database 12c implementing compression on existing tables/partitions can be automated using Automatic Data Optimization. Compression can be specified at the “row” level or the “segment” level. Row level (available only with Advanced Row Compression with Oracle Database 12.1) allows the table to be compressed in place, block-by-block, as all the rows on a block meet the ADO policy condition. With Oracle Database 12.2 this functionality was enhanced to provide row-level support for Hybrid Columnar Compression – with HCC, rows that meet the ADO policy condition are re-located to a HCC Compression Unit. A Compression Unit (CU) is a logical construct used to store a set of hybrid columnar compressed rows. Implementing an automated, row-level compression solution, using Automatic Data Optimization (and Heat Map) is straightforward. The ADO policy examples below enable Advanced Row Compression, and Hybrid Columnar Query Compression, using row-level compression when the ADO policy condition (as monitored by Heat Map) “AFTER 2 DAYS OF NO MODIFICATION” is met. The compression will occur during maintenance windows. This ADO policy enables row-level Advanced Row Compression. ALTER TABLE orders ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 2 DAYS OF NO MODIFICATION; This ADO policy enables row-level Hybrid Columnar Compression. ALTER TABLE sales ILM ADD POLICY COLUMN STORE COMPRESS FOR QUERY ROW AFTER 2 DAYS OF NO MODIFICATION; Tables/partitions can also be compressed at the segment level for both Advanced Row and Hybrid Columnar Compression, this means the entire segment is compressed at the same time. The ADO policy example below enables segment-level Hybrid Columnar Compression. ALTER TABLE orders ILM ADD POLICY COLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENT AFTER 90 DAYS OF NO MODIFICATION; It should be noted that while row level compression does allow you to compress the table/partition without rebuilding the entire segment, it does also mean that the entire segment may not compress for days, weeks or months depending on how long it takes for all rows to meet the ADO policy condition. To compress the entire segment together, you would need to compress at the segment level, which would require rebuilding the table/segment in a compressed format. For now, the database storage optimization adventure continues my next blog.

In this blog we are going to discuss row level compression with Advanced Row Compression, and now available with Hybrid Columnar Compression (with Oracle Database 12.2), when using Automatic...

Row Level Locking with Hybrid Columnar Compression (HCC)

In this blog we’re going to discuss Row Level Locking with Hybrid Columnar Compression, one of the many lesser-known capabilities included with Oracle Advanced Compression. Hybrid Columnar Compression technology utilizes a combination of both row and columnar methods for storing data. A logical construct, called the compression unit (CU), stores a set of hybrid columnar compressed rows. When data is loaded, column values for a set of rows are grouped together and compressed. After the column data for a set of rows is compressed, it is stored in a compression unit. Figure 1: Conceptual Illustration of a Logical Compression Unit (CU)       By default, Hybrid Columnar Compression uses one lock per CU. Optionally; users can choose to enable row level locking for compression units. The default with HCC is NO ROW LEVEL LOCKING, ROW LEVEL LOCKING is explicitly specified during a CREATE TABLE or ALTER TABLE operation. The following is an example of HCC Row Level Locking syntax: CREATE/ALTER TABLE … COMPRESS FOR [compression_type] [ROW LEVEL LOCKING | NO ROW LEVEL LOCKING] Hybrid Columnar compressed data can be modified using conventional Data Manipulation Language (DML) operations, such as UPDATE and DELETE – but HCC is typically best suited for applications with no, or very limited DML operations. With limited (or no) DML operations, CU-level locking can often be utilized without concurrency concerns during normal query-mostly processing. New in Oracle Database 12.2, HCC automatically compresses new data from SQL INSERT ... SELECT statements, without the APPEND hint and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI). With this new INSERT compression users may want to evaluate whether Row-Level Locking is a better solution, in regards to concurrency, than CU-level locking. As always, testing with your own data, and applications, will provide the most realistic performance and functionality comparisons. Row level Locking, with Hybrid Columnar Compression, is available on Oracle Cloud as well as the following Oracle Engineered Systems and Oracle Storage: Exadata, SuperCluster, ZFS and FS1. For more information about HCC, please visit http://www.oracle.com/technetwork/database/database-technologies/hybrid-col-compression/overview/hccoverviewpage-2403631.html For now, the database storage optimization adventure continues my next blog.

In this blog we’re going to discuss Row Level Locking with Hybrid Columnar Compression, one of the many lesser-known capabilities included with Oracle Advanced Compression. Hybrid Columnar Compression...

Compression and Database In-Memory Sessions – Visit us at Oracle Open World 2017

Open World runs from Sunday, 1 October 2017 through Thursday, 5 October 2017. There are a number of Database Compression, and Database In-Memory sessions, that should be very informative, please stop by if you are interested in either of these topics. Here is a list of sessions to help with your planning. Advanced Compression Session: CON6577 – Get the Best Out of Oracle Compression, Wednesday, Oct 04, 12:00 p.m. - 12:45 p.m. | Moscone West - Room 3006, Gregg Christman, Product Manager, Oracle Database In-Memory Labs and Sessions: HOL7584 - Oracle Database In-Memory Hands-on Lab Monday, Oct 02, 6:00 p.m. - 7:00 p.m. | Hilton San Francisco Union Square (Lobby Level) - Plaza Room A Tuesday, Oct 03, 11:30 a.m. - 12:30 p.m. | Hilton San Francisco Union Square (Lobby Level) - Plaza Room A Wednesday, Oct 04, 1:15 p.m. - 2:15 p.m. | Hilton San Francisco Union Square (Lobby Level) - Plaza Room A CON6583 - Memory Without Bounds: Policy-Based Automation in In-Memory Column Store Content, Monday, Oct 02, 3:15 p.m. - 4:00 p.m. | Moscone West - Room 3010, Andy Rivenes, Product Manager, Oracle CON6584 - Oracle Database In-Memory Deep Dive: Past, Present, and Future Tuesday, Oct 03, 11:30 a.m. - 12:15 p.m. | Moscone West - Room 3014 Tirthankar Lahiri, Vice President, Oracle CON6682 - Revolutionize Analytics with Oracle Database In-Memory Tuesday, Oct 03, 12:45 p.m. - 1:30 p.m. | Moscone West - Room 3014, Juan Loaiza, Senior Vice President, Oracle CON6589 - Quick Start Your Oracle Database In-Memory Deployment: Step-by-Step Guide Wednesday, Oct 04, 1:00 p.m. - 1:45 p.m. | Moscone West - Room 3004, Raj Rathee, Product Manager, Oracle CON6590 - Oracle Database In-Memory: Oracle Experts Answer Your Questions, Wednesday, Oct 04, 3:30 p.m. - 4:15 p.m. | Moscone West - Room 3004 The OOW Session Content Catalog is now available! For now, the database storage optimization adventure continues in my October blog.

Open World runs from Sunday, 1 October 2017 through Thursday, 5 October 2017. There are a number of Database Compression, and Database In-Memory sessions, that should be very informative, please stop...

Data Pump and Advanced Compression – How Does this Relationship Benefit You?

In this blog we’re going to discuss Advanced Compression backup compression with Data Pump, one of the many lesser known capabilities included with the Advanced Compression option. The ability to compress the metadata associated with a Data Pump job was first provided in Oracle Database 10g Release 2. In Oracle Database 11g, this compression capability was extended so that table data can be compressed on export; this extended capability is a feature of Advanced Compression.  Data Pump compression is completely independent of Advanced Row Compression (and Hybrid Columnar Compression) – so it doesn't matter how a table is compressed within the database. Data Pump compression only compresses the dumpfile. Data Pump will execute a query to retrieve the table data from the database, and the database will decompress the data before delivering it to Data Pump. If you have set COMPRESS=ALL or COMPRESS=DATA-ONLY, then Data Pump will compress the data in its export file, using an algorithm that is different from any of the table compression algorithms used by Oracle Database. When Data Pump loads a table, the metadata for the table determines whether the data being loaded is compressed for the table.  It does not matter whether the data was compressed in the export database.  All that matters is the compression characteristics of the table in the import database. Data Pump compression is an inline operation, so the reduced dump file size means a significant savings in disk space. Unlike operating system or file system compression utilities, Data Pump compression is fully inline on the import side as well, so there is no need to decompress a dump file before importing it. The compressed dump file sets are automatically decompressed during import without any additional steps by the Database Administrator. The following options are used to determine which parts of a dump file set should be compressed: ALL - Enables compression for the entire export operation. DATA-ONLY - Results in all data being written to the dump file in compressed format. METADATA-ONLY - Results in all metadata being written to the dump file in compressed format.  This is the default. NONE - Disables compression for the entire export operation. For detailed information about using Oracle Data Pump, please visit http://www.oracle.com/technetwork/database/enterprise-edition/index-093639.html An expdp command-line option for Oracle Data Pump Export can be used to control the degree of compression used (BASIC, LOW, MEDIUM or HIGH) for an Oracle Data Pump dump file – the same options can also be specified to the PL/SQL DBMS_DATAPUMP package. The higher the degree of compression, the higher the latency incurred but the better compression ratio achieved. That is, the HIGH option will likely incur more overhead, but should compress the data better. These options enable the DBA to trade off time spent compressing data against the size of the Oracle Data Pump dump file. Compression can really improve performance of data pump operations whenever the export or import is IO-bound. This is often the case when transferring over a dblink using the NETWORK_LINK parameter (a 10GB Ethernet is limited to 4TB/hr maximum – although we have seen customers move 6TB/hr over such a network by applying compression). IO can also be the bottleneck when using high degrees of PARALLEL over a limited number of spindles or IO controllers. Data pump is built to use all the hardware you can throw at it, and performance is all about removing bottlenecks. Compression can really help performance because it can apply CPU to reduce IO when IO is the bottleneck, which is so often the case. The reduction in dump file size will vary based on data types and other factors. Note that when importing using Data Pump, the CREATE TABLE statements will have compression clauses that match the definition in the export file. If a compression clause is missing, then the table inherits the COMPRESSION attributes of the tablespace where the table is stored. If you haven’t thought about how compression can help your backup storage requirements it’s probably worth some time to look into it, but as usual, it’s always best to test on your systems with your data. Thanks for reading my blog and for now the database storage optimization adventure continues. If you’re going to Oracle Open World 2017 please stop by my session titled “Getting the Best Out of Oracle Compression” in room 3006 (Moscone West) from 12:00pm to 12:45pm on 4 October 2017.

In this blog we’re going to discuss Advanced Compression backup compression with Data Pump, one of the many lesser known capabilities included with the Advanced Compression option. The ability to...

What’s the Difference, and When to Use, the Various Types of Data Compression

In this blog we’re going to discuss the various types of data compression, the differences between the compression types and when to use (and not use) the different data compression types. I’ve touched on this topic before in my blog, but I recently ran across the chart below in the Oracle Database documentation and wanted to share it with everyone, so I thought a review of the compression types the chart mentions seemed timely. Data Compression Methods Basic Table Compression If you’re not familiar with Basic Table Compression, then some important points to know about Basic Table Compression are that it’s a free data compression capability and it is included with Oracle Database Enterprise Edition. More than a decade ago, Oracle Database 9i Release 2 introduced Basic Table Compression, which compresses data that is loaded using bulk load operations, but doesn’t compress data that is added/changed through conventional DML operations (INSERT or UPDATE) – if INSERTS and UPDATES are performed on a Basic compressed table/partition over time, then that table/partition would have to be re-compressed to get the changes compressed. USAGE: Basic Table Compression isn’t intended for OLTP applications, and instead, is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified. Advanced Row Compression Oracle Database 11g Release 1 introduced OLTP Table Compression, now called Advanced Row Compression with Oracle Database 12c. Advanced Row Compression is the data compression feature of Advanced Compression that uses the same algorithm as Basic Compression, but differs from Basic Compression in that Advanced Row Compression maintains data compression during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. Advanced Row Compression uses a compression algorithm specifically designed to eliminate duplicate values within a database block, even across multiple columns. The compression ratio achieved in a given environment (also true for Basic Compression) depends on the data being compressed, specifically the cardinality of the data. In general, organizations can expect to reduce their storage space consumption by a factor of 2x to 4x by using Advanced Row Compression (Basic compression produces similar compression ratios). That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data. USAGE: Advanced Row Compression is intended for both OLTP and Data Warehouse applications. Hybrid Columnar Compression Unlike both Basic and Advanced Row Compression, Oracle’s Hybrid Columnar Compression 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 and compressed. After the column data for a set of rows has been compressed, it is stored in a compression unit. To maximize storage savings with Hybrid Columnar Compression, data must be loaded using data warehouse bulk loading (direct path) techniques. Examples of bulk load operations commonly used includes: Insert statements with the APPEND hint, Parallel DML, Direct Path SQL*LDR and/or Create Table as Select (CTAS). In general, organizations can expect to reduce their storage space consumption by a factor of 6x to 15x+ by using Hybrid Columnar Compression. USAGE: Prior to Oracle Database 12c Release 2 Hybrid Columnar Compression wasn’t intended for OLTP applications, and instead, is best suited for data warehouse applications (read-mostly) where data is loaded using bulk load operations and is never (or very rarely) modified. While HCC compressed data can be modified using conventional Data Manipulation Language (DML) operations, such as UPDATE and INSERT - HCC is best suited for applications with no, or very limited DML operations. If frequent UPDATE and INSERT operations are planned on a table or partition, then Advanced Row Compression (a feature of Oracle Advanced Compression) is better suited for such data. DML INSERTED/UPDATED data (when using Hybrid Columnar Compression) typically see a reduced compression ratio (2x to 4x). New in 12.2, Hybrid Columnar Compression automatically compresses new data from SQL INSERT ... SELECT statements, without the APPEND hint and array inserts from programmatic interfaces such as PL/SQL and the Oracle Call Interface (OCI) – meaning that Hybrid Columnar Compression will now maintain the compression for these types of operations where before this release those operations would have caused a reduced level of compression with Hybrid Columnar Compression. The difference regarding “Warehouse” and “Archive” compression are explained in the Hybrid Columnar Compression white paper listed below. For now the database storage optimization adventure continues in my next blog. If you plan on going to DOAG 2017 in Nuremburg (November 2017) please stop by my session on data and Index Compression – more details soon. More information: Advanced Row Compression White Paper: http://www.oracle.com/technetwork/database/options/compression/advanced-compression-wp-12c-1896128.pdf Hybrid Columnar Compression White Paper: http://www.oracle.com/technetwork/database/database-technologies/performance/hybridcolumnarcompression-oow2010-200703.pdf

In this blog we’re going to discuss the various types of data compression, the differences between the compression types and when to use (and not use) the different data compression types. I’ve touched...

Advanced LOB Deduplication and Compression – Lesser Known Features of Advanced Compression

In this blog we’re going to discuss Advanced LOB Deduplication and Advanced LOB Compression, some of the lesser known SecureFiles features of Advanced Compression. SecureFiles offers a ‘best-of-both-worlds’ architecture for storing unstructured content such as documents, images, spreadsheets and XML files and is specifically engineered to deliver high performance for file data equal to or better than traditional file systems, while retaining the advantages of Oracle Database. The following features are available only for SecureFiles and do not apply to BasicFiles.   Advanced LOB Deduplication Advanced LOB Deduplication eliminates multiple, redundant copies of SecureFiles data and is completely transparent to applications. Using Advanced LOB Deduplication, Oracle automatically detects multiple, identical SecureFiles data and stores only one copy, thereby saving storage space. Deduplication not only simplifies storage management but can also help provide better performance, especially for copy operations. The DEDUPLICATE keyword is used to enable Advanced LOB Deduplication checking for SecureFiles. Oracle uses a secure hash index to detect duplicate SecureFiles data and stores a single copy for all identical content. Deduplication is done on a per LOB segment basis, duplicates are not detected across multiple LOB segments. Deduplication is transparent to applications and is turned off by default. Note: Advanced LOB Deduplication was previously named SecureFiles LOB Deduplication with Oracle Database 11g. Example: Creating a SecureFiles LOB column with deduplication on one partition CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB)  LOB(a) STORE AS SECUREFILE (             CACHE ) PARTITION BY LIST (REGION)  (    PARTITION p1 VALUES ('x', 'y')    LOB(a) STORE AS SECUREFILE  (       DEDUPLICATE           ),      PARTITION p2 VALUES (DEFAULT) ); Advanced LOB Compression When using Advanced LOB Compression Oracle detects if SecureFiles data is compressible and will compress using industry standard compression algorithms. If the compression does not yield any savings, or if the data is already compressed, SecureFiles will turn off compression for such LOBs. Compression can result in significant savings in storage and can also improve performance by reducing I/O, buffer cache requirements, redo generation and encryption overhead. Random access reads and writes to compressed SecureFiles are achieved without the need to decompress the entire file. Only the sub portion (compression unit) of the compressed file, corresponding to the logical offset being read or written, needs to be decompressed thus saving CPU and I/O. There are three levels of Advanced LOB Compression:  LOW, MEDIUM, and HIGH.  By default, Advanced LOB Compression uses the MEDIUM level, which typically provides good compression with a modest CPU overhead of 3-5%. Advanced LOB Compression LOW is optimized for high performance. Advanced LOB Compression LOW maintains about 80% of the compression achieved through MEDIUM, while utilizing less CPU. Finally, Advanced LOB Compression HIGH achieves the highest storage savings but incurs the most CPU overhead. The COMPRESS keyword is used to enable Advanced LOB Compression. Compression is turned off by default. Advanced LOB Compression is separate from Advanced Row Compression and has no impact on Advanced Row Compression (and Advanced Row Compression has no impact on Advanced LOB Compression). Note: Advanced LOB Compression was previously named SecureFiles LOB Compression with Oracle Database 11g. Example: Creating a Advanced LOB column with compression on one partition CREATE TABLE t1 ( REGION VARCHAR2(20), a BLOB)     LOB(a) STORE AS SECUREFILE  (                CACHE      )      PARTITION BY LIST (REGION)  (        PARTITION p1 VALUES ('x', 'y')        LOB(a) STORE AS SECUREFILE  (              COMPRESS                  ),           PARTITION p2 VALUES (DEFAULT) ); Advanced LOB Deduplication and Compression can be setup independently or together -- if both features are enabled, Oracle will perform deduplication first and then compression. For more information about SecureFiles and LOB storage, please see the Oracle® Database SecureFiles and Large Objects Developer's Guide. See here: http://docs.oracle.com/database/121/ADLOB/toc.htm But for now the database storage optimization adventure continues in my next blog.

In this blog we’re going to discuss Advanced LOB Deduplication and Advanced LOB Compression, some of the lesser known SecureFiles features of Advanced Compression. SecureFiles offers a...

Advanced Compression

Compression Advisor -- A Valuable but Often Overlooked Tool: Insights and Best Practices

In this blog I thought we would discuss compression advisor (DBMS_COMPRESSION) a bit, it’s a free, and often overlooked tool that can be used with Oracle Database Enterprise Edition to estimate compression ratios for Advanced Row Compression, Hybrid Columnar Compression, LOBS and Advanced Index Compression. The DBMS_COMPRESSION PL/SQL package gathers compression-related information within a database environment. This includes estimating the compressibility of both partitioned and non-partitioned tables and gathering row-level compression information on previously compressed tables. Compression Advisor, which supports Oracle Database 9i Release 2 through 11g Release 1, is available for free on the Oracle Technology Network website here. DBMS_COMPRESSION is included with Oracle Database 11g Release 2 and above. For information (and usage examples) about using DBMS_COMPRESSION, please see the Oracle Database documentation here. Compression advisor provides a fairly accurate estimation of the compression ratio that could be expected after compressing your data, indexes or LOBS. For example, Advanced Row Compression users can expect to reduce their storage space consumption by a factor of 2x to 4x by using the Advanced Row Compression feature. That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data. The compression ratio achieved in a given environment depends on the nature of the data being compressed. In general, the typical compression ratios experienced are as follows: Advanced Row Compression:                    2x to 4x  Advanced LOB Compression:                    2x to 3x Hybrid Columnar Compression:                 6x to 15x Advanced Index Compression:                  2x to 3x Here are some general insights and best practices to keep in mind when using compression advisor: If you get this type of message when estimating Hybrid Columnar Compression ratios, then you may want to re-test using the solution below. ORA-12801: error signaled in parallel query server P002 ORA-64307:  Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type Solution: Disable parallel processing for the session (set parallel_max_servers=0) Oracle Advanced Compression is not required in order to use compression advisor. Hybrid Columnar Compression ratios can be estimated on any Oracle Database Enterprise Edition platform. Tables residing in uniform tablespaces can be compressed. However, compression adviser has the restriction that the scratch tablespace cannot be uniform.   In earlier releases Oracle did require 1M rows in a table for estimating Hybrid Columnar Compression ratios with compression advisor – this restriction is lifted in 12.1.0.2. Note: Outside compression advisor there are no restrictions, with Hybrid Columnar Compression, in regards to the minimal amount of data needed (in tables/partitions). Compression Advisor builds two temporary tables (for comparison purposes) as part of the estimation process for Advanced Row Compression (four tables for Hybrid Columnar Compression). Although these temporary tables are removed after compression advisor completes, you will need free space equivalent to the sampling space specified. See this MOS note if you want to determine if rows are in a compressed format using compression advisor. (Doc ID 1477918.1) I hope you found this overview (and insights/best practices) of compression advisor useful. For now the database storage optimization adventure continues in my next blog.

In this blog I thought we would discuss compression advisor (DBMS_COMPRESSION) a bit, it’s a free, and often overlooked tool that can be used with Oracle Database Enterprise Edition to estimate...

Advanced Compression

Advanced Compression Proof-of-Concept (PoC) Insights and Best Practices

I was going to discuss rolling compression using partitioning but I think we’ll save that for a future blog. Instead, I thought we’d discuss what I believe is useful information for anyone currently running an Advanced Compression PoC , or anyone who is planning an Advanced Compression PoC in the future. During my Advanced Compression session, at last year’s Open World, someone mentioned that an Advanced Compression focused document that discussed best practices, and provided some insights regarding performing an Advanced Compression PoC, would be very helpful. That was a great idea, so I’m pleased to provide just such a document, please see this brand new document here: Please note that this document isn’t meant to be a step-by-step guide to performing a compression PoC. Instead, this document is intended to provide best practices learned from customer PoC’s and to provide insights to help plan your compression PoC, as well as help you understand the results of your PoC. Some of the topics covered include: - What is Useful to Know for Your Compression PoC - Enabling Advanced Row Compression - When Advanced Row Compression Occurs - Direct-Path versus Conventional-Path Inserts - Index Key Compression/Advanced Index Compression - What to Consider for Before PoC Testing Begins - Direct-Path and Conventional-Path Loads and AWR - RMAN Compression - More about Compression, Performance and Compression Ratios - What does the Typical Compression PoC Look Like? - Example of Compression PoC as a Multi-Step Process Even if you’re already a user of Advanced Compression, you’ll want to review the Advanced Compression PoC document, it has a number of insights regarding Oracle compression that you will find useful. Please leave comments regarding anything else, that should be included in this document, which you believe would be useful during an Advanced Compression PoC. Also, next month (May), I will be presenting at the Great Lakes Oracle Conference (GLOC) in Cleveland Ohio. If you’re planning on going to GLOC, please stop by for my session titled Insiders Guide to Getting the Most Out of Database Compression and ILM which is scheduled for Thursday, May 18, 2017, at 2:45 pm - 3:45 pm. This is a great opportunity to get all your questions about Oracle Compression and ILM answered by Product Management.

I was going to discuss rolling compression using partitioning but I think we’ll save that for a future blog. Instead, I thought we’d discuss what I believe is useful information for anyone currently...

Advanced Compression

Advanced Row Compression Improvements with Oracle Database 12c Release 2

Thanks to contributing authors: Jesus Maldonado and Ajit Mylavarapu Oracle Database 12c Release 2 includes many new features and our commitment, as always, is to be the best database for any business scenario. Please read more on this here. Oracle Database 12c Release 2 (12.2) is available in the cloud, with Oracle Cloud at Customer, and on-premises. Today we are going to talk about Advanced Row Compression. Advanced Row Compression is a table compression feature of Oracle Advanced Compression. Other Advanced Compression features include: Advanced LOB Compression, RMAN Backup Compression, Data Guard Redo Network Compression, Heat Map and Automatic Data Optimization (ADO). For a background on Advanced Compression, please see this white paper. If you are familiar with Advanced Compression technologies, please skip to the Advanced Row Compression Improvements section, otherwise continue reading. Advanced Row Compression at a Glance Advanced Row Compression uses a unique compression algorithm specifically designed to work with OLTP applications. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. The benefits of using Advanced Row Compression include: - Low overhead on DML operations. In fact, in some cases DMLs are faster when compression is used (more on this later). - Faster scans* - 2X-4X space savings in general** * Scans go faster because there are fewer blocks to read and Oracle’s compression technology makes it as fast as reading uncompressed data ** Exact space savings will depend on the data in the table and the table schema Advanced Row Compression Improvements With Oracle Database 12c Release 2 there are significant enhancements to Advanced Row Compression: Better Chained Rows Support Before Oracle Database 12c, blocks containing many types of chained rows could not be compressed. This limitation has been removed in Oracle Database 12c Release 2. Comparison: Oracle Database 11g Release 2 compared to Oracle Database 12c Release 2 Rows were direct loaded and also conventionally loaded and the compression ratios were compared using the test conditions below: Number of columns: 330 Number of rows: ~1 million Schema: Real-world table from an Oracle Customer‘s schema Results: 2.25X compression ratio in both cases (direct loaded and also conventionally loaded) in Oracle Database 12c Release 2 compared to no compression with Oracle Database 11g Release 2. Partial Compression With Advanced Row Compression, when the block is full, it is compressed. More rows are then added (since more rows can now fit into the block), and the process of recompression is repeated several times until the rows in the block cannot be compressed further. Blocks are usually compressed and reformatted in their entirety, but in some cases in Oracle Database 12c Release 2 the block can be partially compressed, hence resulting in CPU savings and extra compression. The partial compression feature is used on already compressed blocks (i.e. compressed with Advanced Row Compression). It looks for uncompressed rows and transforms those into a compressed form, adding or reusing symbols from the block dictionary - this is faster than recompressing the whole block again. Full block recompression also requires that no rows are locked in the block or, that all the rows in the block are locked by the transaction inserting rows into the block. In the first case, full block recompression is done as a recursive transaction, and in the second case, full recompression is done as an inline transaction. Partial compression gets around these requirements by locking and compressing only those rows that are uncompressed and unlocked - hence it can take place in the presence of other uncommitted transactions in the block. Consider a SQL update operation: When rows cannot be updated in place due to space constraints, the update is broken up into lower level overwrite and insert sub-operations (the initial row piece is overwritten with a stub that points to the new row image inserted into a new block). This is done to avoid ROWID changes to existing rows on update operations. Assuming concurrent transactions, each transaction typically updates a few rows, and the rows inserted into a new block are neither all locked by the same transaction (some rows from previously committed transactions are unlocked) nor are they all unlocked (some transactions are yet to commit). In such a case, full block recompression is not possible - this causes some blocks to be left uncompressed during an update (or, equivalently, during an insert operation). In Oracle Database 12c Release 2, with partial compression, these rows can be compressed. For an in-depth treatment of updates to Advanced Row compressed tables, please see: this post).  In simpler terms, partial compression can now compress rows which could not be compressed before. Let us see some examples. Comparison: Oracle Database 11g Release 2 compared to Oracle Database 12c Release 2 (SCOTT.EMP demo table) Test case updates 1 column. Number of columns: 8 Number of rows: ~500,000 Results: 2.13X compression ratio with Oracle Database 12c Release 2 compared to no compression with Oracle Database 11g Release 2. Comparison: Oracle Database 11g Release 2 compared to Oracle Database 12c Release 2 (Oracle ERP table AP_AE_LINES_ALL) Test case updates 1 column. Number of columns: 56 Number of rows: ~1 million Results: 1.53X compression ratio with Oracle Database 12c Release 2 compared to no compression with Oracle Database 11g Release 2. Background Compression In Oracle Database 11g Release 2, block compression was only triggered using direct load operations or by DML operations: - Direct Load: via import, create table as select, move (alter table, partition move [online]). - DML: via inserts or updates which then trigger compression. With Database 12c Release 2, background space tasks can compress blocks if these find them as candidates. A block can be marked as a candidate to be compressed based on previous DML operations or by other space tasks evaluating the space usage of a block. Compression done in the background is the same as Advanced Row Compression, but triggered by background processes instead of an active SQL session. Another example of background triggered compression is with ADO ADO (Automatic Data Optimization) that is defined based on policies declaratively set by the user. Array Inserts - Quick Multi Inserts Enhancements Insert operations such as INSERT ... AS SELECT and PLSQL/OCI insert loops with bind variables, in many cases, go through a special array insert code path. Typically, these operations generate one redo record containing hundreds of rows to be inserted instead of hundreds of redo records each with a single row. This code path is used when the rows being inserted conform to certain restrictions, such as not requiring to be chained. Before Database 12c Release 2, array inserts caused multiple recompressions per block: A batch of rows is inserted into a block and the block is compressed, the next batch is inserted and the block is recompressed and so on. In Database 12c Release 2, we estimate the number of rows that would fit into a compressed block. All these rows are then buffered, compressed, and a full block image is generated. Note that compression occurs only once or twice (one compression to estimate compression ratio) per block, as opposed to occurring potentially many times as in pre-Database 12c Release 2. This gives us a big benefit in elapsed time. We also obtain a much better compression ratio because we compress many more rows together (hence being able to extract common symbols more effectively). The algorithms also adaptively vary the number of rows to be buffered and increase the number of rows buffered depending on running estimates of how many compressed rows would fit into a block. Comparison: Oracle Database 11g Release 2 compared to Oracle Database 12c Release 2 (Oracle ERP table AP_AE_LINES_ALL) Test case: Quick Multi Insert with Advanced Row Compression Number of columns: 56 Number of rows: ~1 million Results: (Oracle Database 12c Release 2 compared to Oracle Database 11g Release 2) - 2.7X redo size reduction - 5.2X faster data ingestion speed than uncompressed Quick Multi Insert - 3.5X faster IO (reduction in logical block reads) With this enhancement, tables with Advanced Row Compression not only enable much faster scans, but also faster inserts than uncompressed tables, because of reduced logical and physical block gets. My thanks to Jesus and Ajit for the great insights regarding Advanced Row Compression improvements with Oracle Database 12c Release 2. Please join me in April, as the database storage optimization adventure continues with my next blog, in which we will discuss rolling compression, with partitioning, in Oracle Database 11g.          

Thanks to contributing authors: Jesus Maldonado and Ajit Mylavarapu Oracle Database 12c Release 2 includes many new features and our commitment, as always, is to be the best database for any business...

Advanced Compression

Automatic Data Optimization Reduces ILM Development and Administrative Time at Yapı Kredi Bank

In earlier blogs I’ve discussed the use of Heat Map andAutomatic Data Optimization (ADO) for both compression tiering (SeeHere) and storage tiering (SeeHere). Inthis blog we’re fortunate to be able to discuss why Yapı Kredi Bank decided tolook at Automatic Data Optimization to implement an ILM (Information LifecycleManagement) solution. Established in 1944 as Turkey’s firstretail focused private bank with a nationwide presence, Yapı Kredi has played a significant role in Turkey’sdevelopment, setting standards in the sector through its innovative approach,commitment to social responsibility and investment in culture and arts. YapıKredi, the fourth largest private bank in Turkey with TL 248.1 billion ofassets, is one of the 10 most valuable brands in Turkey. Like many organizations, Yapı Kredi sawthat 90% of the data (approximately 90TB) managed by Oracle Database was datawarehouse data, leaving the remainder of the data, approximately 10TB, beingOLTP data which was being actively modified. Yapı Kredi’s DBA’s recognized thatas their data become less active, moving from highly active OLTP data to lessactive, query-mostly data warehouse data it was possible to use different typesof Oracle compression to suit different access patterns. ILM was identified by their organizationand a key goal, but prior to ADO, when organizations wanted to implement an ILMstrategy they would have typically leveraged the Advanced Compression and DataPartitioning options to create a manual database compression and storagetiering solution – a solution which required organizations to have a fairlydeep understanding of their data access and usage patterns, often acrossthousands of tables/partitions – or they created a custom solution. Their DBAs recognized that the datamaintenance tasks associated with a manually implemented ILM solution wouldrequire serious time of DBAs, developers, and even the business usersthemselves. Knowing that if ADO workedas they expected they believed the organization would save development andadministration time, and costs, for the life of the deployment. But how could their DBA’s identify whichtables/partitions, across the database, are best suited for compression, andwhich type of compression? To do so requires that DBA’s have the ability toeasily determine which of their tables/partitions are “hot” (the most activedata) and which have “cooled” (less active historic/archive/reporting data). Interested in learning more about how ILM allowsYapı Kredi to better utilize the organizations existing storage, better managetheir storage growth and help with database performance, then please see thenew Yapı Kredi Case Study SeeHere.

In earlier blogs I’ve discussed the use of Heat Map and Automatic Data Optimization (ADO) for both compression tiering (See Here) and storage tiering (See Here). Inthis blog we’re fortunate to be...

Advanced Compression

Advanced Index Compression High - New with Oracle Database 12c Release 2 on Oracle Cloud

Indexesare used extensively in OLTP databases as they are capable of efficientlysupporting a wide variety of access paths to the data stored in relationaltables, via index key lookups or range scans. Therefore, it is very common tofind a large number of indexes being created on a single table to support a multitudeof access paths for OLTP applications and workloads. This causes indexes tocontribute a greater share to the overall storage of the database when comparedto the size of the base table itself. Withprevious solutions, as discussed in Part1 and Part2 of the Index Compression blog series, index entries, with many duplicatekeys, can be compressed making it possible to reduce both the storage overheadand the access overhead for large index range scans or fast full scans. Compressioncan be very beneficial when the prefix columns of an index are repeated manytimes within a leaf block. However, if the leading columns are very selectiveor if there are not many repeated values for the prefix columns, then indexprefix compression or Advanced Index Compression LOW may not be the bestsolutions. Newwith Oracle Database 12c Release 2 on Oracle Cloud, Oracle has introduced Advanced Index Compression HIGH, geared towards dramaticallyimproving index compression ratios. Advanced Index Compression (which includesboth LOW and HIGH levels) is part of the Advanced Compression option and aims atachieving much higher compression ratios and, at the same time, automatingindex compression. The following graph shows sample compression ratios for twodatasets using Advanced Index Compression. Along with substantially reducingthe storage footprint for the indexes, these workloads also saw substantialimprovement in the system performance. AdvancedIndex Compression HIGH introduces many additional compression techniques, whichimproves the compression ratios significantly while still providing efficientOLTP access. It stores the index key entries in Compression Units (a conceptsimilar to Hybrid Columnar Compression), utilizing more complex compressionalgorithms on a potentially larger number of index keys to achieve higherlevels of compression. Thereare many similarities for Advanced Index Compression HIGH with both AdvancedRow Compression and Hybrid Columnar Compression. A few of the important ones tonote are: · Just as with Hybrid Columnar Compression, Advanced IndexCompression HIGH uses more complex compression algorithms and techniques toachieve higher compression ratios. · Advanced Index Compression HIGH uses a concept similar tointernal threshold, used by Advanced Row Compression, to trigger recompressionof the data block. Newly inserted data is buffered uncompressed in the blockand compressed as the block fullness approaches this internal threshold. Thisis done to amortize the compression cost over multiple DML operations(inserts). With indexes, this internal threshold is geared towards avoidingindex block splits and alleviating the need to allocate additional leaf blocksto the index structure. · Every index block can contain a compressed region and anon-compressed region. The compressed index keys are stored in the compressedregion (Compression Unit), while the newly inserted keys are stored in thenon-compressed region. · Advanced Index Compression HIGH supports row level lockingand full concurrency as with Advanced Row Compression (or Hybrid ColumnarCompression with Row Level Locking enabled). Someof the compression techniques used with Advanced Index Compression HIGH include(but are not limited to): · Intra-columnPrefix Replacement Intra-columnprefix compression exploits the fact that, as a result of index rows beingsorted in key order, there is a high likelihood that a prefix of each keymatches the corresponding prefix of the preceding key even at sub key columnlevel. Replacing the matching prefixes from each row with a reference to thecorresponding symbol gives good compression benefits. Additionally, if thecardinality of the symbol table indexes is low, and a large number of indexkeys have a matching prefix, bit encoding the symbol table references canfurther improve compression benefits. · Length ByteCompression Itis very common to find a large number of rows in an index with short columnlengths. Thus, it is possible to encode these lengths in less than a byte (aswith the uncompressed and prefix compressed index) and hence save space.Additionally, if all key columns in the block have the same length, the blocklevel fixed length can be stored. · Duplicate KeyRemoval Ifthe index block has a large number of duplicates, it is possible to realizesignificant space savings by storing the key exactly once followed by a list ofROWIDs associated with the key in sorted order. Intra-column prefix compressioncan then be applied on top of this transformed representation to furthercompress the now unique set of keys. · ROWID ListCompression ROWIDList Compression is an independent transformation that takes the set of ROWIDsfor each unique index key and represents them in a compressed form, ensuringthat the compressed ROWID representation is logically maintained in the ROWIDorder to allow for efficient ROWID based lookup. · Row DirectoryCompression Thegeneral idea behind Row Directory Compression is to layout the compressed rowscontiguously in the increasing offset order within each 256 byte region of theindex block, which enables maintaining a base offset (once per 256 bytes) and arelative 1 byte offset per compressed row. · Flag and LockByte Compression Generallyspeaking, the index rows are not locked and the flags are similar for all therows in the index block. These lock and flag bytes on disk can be representedmore efficiently provided it is possible to access and modify them. Anymodification to the flag or lock bytes requires these to be uncompressed. Notevery compression technique is applicable to every index. The decision on whichcompression algorithms are applicable to an index is made real-time and candiffer from index-to-index and block-to-block. The net result is generallybetter compression ratios, but at the potential cost of slightly higher CPUresource utilization both to maintain the index structure and index access. TheCPU overhead is kept minimal by using techniques such as buffering large numbersof rows before triggering compression to amortize the compression overhead,turning off compression on a block after the compression gains dropsignificantly, the ability to access compressed index keys and selectivelydecompressing only the keys needed etc. Advanced Index Compression can be enabled easily byspecifying the COMPRESS option for indexes. New indexes can be automaticallycreated as compressed, or the existing indexes can be rebuilt compressed. CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS ADVANCED HIGH; Advanced Index Compression HIGH works well on all supportedindexes, including the ones that were not good candidates for Advanced Index CompressionLOW or prefix key compression. Creating an index using Advanced IndexCompression HIGH reduces the size of all unique and non-unique indexes whilestill providing efficient access to the indexes. For partitioned indexes, you can specify the compressionclause for the entire index or on a partition-by-partition basis. So you canchoose to have some index partitions compressed, while others are not. The following example shows a mixture of compressionattributes on the partitioned indexes: CREATE INDEX my_test_idx ON test(a, b) COMPRESSADVANCED HIGH local     (PARTITION p1 COMPRESSADVANCED LOW,      PARTITION p2 COMPRESS,      PARTITION p3,      PARTITION p4 NOCOMPRESS); The following example shows advanced Index Compression HIGHsupport on partitions where the parent index is not compressed: CREATE INDEX my_test_idx ON test(a, b) NOCOMPRESS local     (PARTITION p1 COMPRESS ADVANCED HIGH,      PARTITION p2 COMPRESS ADVANCED HIGH,      PARTITION p3); The current release of Advanced Index Compression (bothfor LOW and HIGH levels) has the following limitations: · Advanced Index Compression is not supported onBitmap Indexes · You cannot compress your IndexOrganized Tables (IOTs) with Advanced Index Compression · You cannot compress yourFunctional Indexes with Advanced Index Compression You may also want to check out RichardFoote’s blog on Advanced Index Compression. JonathanLewis also gives a great example on how index compression can help withoverall database storage and performance.

Indexes are used extensively in OLTP databases as they are capable of efficiently supporting a wide variety of access paths to the data stored in relationaltables, via index key lookups or range...

Advanced Compression

Oracle Advanced Compression Proof-of-Concept (POC) Insights and Best Practices

I was planning todiscuss three ILM-focused features of Oracle Database 12c, but, I think we’llsave that for January’s blog. Instead, I thought we’d discuss what I believe isuseful information for anyone currently running an Advanced Compression POC ,or anyone who is planning an Advanced Compression POC in the future. During myAdvanced Compression session, at this year’s Open World, someone mentioned thatan Advanced Compression focused document that discussed best practices, andprovided some insights regarding performing an Advanced Compression POC wouldbe very helpful. That was a greatidea, so I’m pleased to provide the link below to just such a document, pleasesee: http://www.oracle.com/ocom/groups/public/@otn/documents/webcontent/3411538.pdf Please note thatthis document isn’t meant to be a step-by-step guide to performing acompression POC. Instead, this document is intended to provide some bestpractices learned from customer POC’s and to provide insights to help plan yourcompression POC, as well as help you understand the results of your POC. Some of thetopics covered include: - What is Useful toKnow for Your Compression POC - Enabling AdvancedRow Compression - When Advanced RowCompression Occurs - Direct-Pathversus Conventional-Path Inserts - Index KeyCompression/Advanced Index Compression - What to ConsiderBefore POC Testing Begins - Direct-Path andConventional-Path Loads and AWR - RMAN Compression - More aboutCompression, Performance and Compression Ratio’s - What does theTypical Compression POC Look Like? - Example ofCompression POC as a Multi-Step Process Even if you’realready a user of Advanced Compression, you might want to review the documentas well, it has a number of insights regarding Oracle compression that you willfind useful. Please leave comments regarding anything else that you think wouldbe useful to include in this document that would help during an AdvancedCompression POC. For now thedatabase storage optimization adventure continues my next blog, in which wewill finally discuss three ILM-focused features of Oracle Database 12c. Happy Holidays!

I was planning to discuss three ILM-focused features of Oracle Database 12c, but, I think we’ll save that for January’s blog. Instead, I thought we’d discuss what I believe isuseful information for...

Advanced Compression

DOAG 2016 - Stop by to Talk about Oracle Compression and Automatic Data Optimization

Going to the DOAG2016 Conference + Exhibition (15-18 November 2016)? Don't miss theopportunity to talk with the Oracle Advanced Compression and Oracle DatabaseIn-Memory Product Management team at oneof our sessions at DOAG. Tuesday, 15November at 3:00pm - Seoul Room Compression and ILM Best Practices, Tips and Insights In this session Oracle productmanagement shares best practices, tips, and insights gathered from compressionusers across the globe, regarding data, index, LOBs, and Oracle RecoveryManager backup compression. Even if you already use Oracle Advanced Compressionor Oracle Hybrid Columnar Compression (or are evaluating these features), thissession provides new insights about how to get best from Oracle compression. If you areinterested in Automatic Data Optimization (ADO) then please join me, andDatabase In-Memory Product Manager Andy Rivenes, as we discuss using ADOwith Database In-Memory as well as on-disk compression tiering and storagetiering. Wednesday, 16 Novemberat 11:00am - Tokio Room Using AutomaticData Optimization with Oracle Database In-Memory and Storage-Based Tiering In this sessionlearn how to define both heat map-based and custom policies to control thepopulation and eviction of objects, as well as the recompression of objects toa higher level within the Oracle Database In-Memory column store. For moreinformation about DOAG 2016 visit: http://www.doag.org/home.html

Going to the DOAG 2016 Conference + Exhibition (15-18 November 2016)? Don't miss the opportunity to talk with the Oracle Advanced Compression and Oracle DatabaseIn-Memory Product Management team at...

Advanced Compression

RMAN and Advanced Compression – How Does this Relationship Benefit You?

In this blogwe’re going to discuss Advanced Compression backup compression, one of the manylesser known capabilities included with the Advanced Compression option. In addition to compressingdata/indexes stored inside the database, Advanced Compression also includes thecapability to compress backed up data. Recovery Manager (RMAN) and DataPump aretwo commonly used tools to backup the data stored inside an Oracle Database. In this blog we’lljust focus on RMAN, we’ll talk about DataPump in the future. Why is backupcompression useful? The storagerequirements for maintaining database backups and the performance of the backupprocedures are directly impacted by database size. Advanced Compressionincludes RMAN compression technology that can dramatically reduce the storagerequirements for backup data. A databasealready using data and/or index compression will generally require less storageand less I/O for the backup because compressed tables/partitions typically havefewer blocks to backup than if they were not compressed. Three levels ofRMAN compression with Advanced Compression Due to RMAN’stight integration with Oracle Database, backup data is compressed before it iswritten to disk or tape and doesn’t need to be uncompressed before recovery –providing a reduction in storage costs and a potentially large reduction inbackup and restore times. RMAN Basiccompression delivers a very good compression ratio, but can sometimes be CPUintensive and CPU availability can be a limiting factor in the performance ofbackups and restores. There are threelevels of RMAN Compression with Advanced Compression: LOW, MEDIUM, and HIGH. The amount of storagesavings increases from LOW to HIGH, while potentially consuming more CPUresources. LOW / MEDIUM / HIGH compression is designed to deliver varyinglevels of compression while typically using less CPU than RMAN BasicCompression. Generallyspeaking, the three levels can be categorized as such: HIGH - Bestsuited for backups over slower networks where the limiting factor is networkspeed MEDIUM- Recommended for most environments. Good combination of compression ratios andspeed LOW - Least impacton backup throughput and suited for environments where CPU resources are thelimiting factor. As indicatedabove, if you are I/O-limited but have idle CPU, then HIGH could work best, asit uses more CPU, but saves the most space and thus gives the biggest decreasein the number of I/O's required to write the backup files. On the other hand,if you are CPU-limited, then LOW or MEDIUM probably makes more sense - less CPUis used, and about 80% of the space savings will typically be realized(compared to Basic). It is important to note that if the backups were I/O-bound before enabling RMAN backup compression, then the backups will likely go faster with compression enabled. If the backups were CPU-bound, then they will likely go slower with compression enabled. Why is backupcompression worth looking at? Many user’s oftensay that the most important performance-related metric for their backups isrecovery time: if they have an urgentneed to recover a database, anything they can do to speed that up is helpful -and if their backups are 1/4th to 1/6th the size they were before compression,that can save a huge amount of time for database recovery. If you haven’tthought about how compression can help your backup/recovery time it’s probablyworth some time to look into it, but as usual, it’s always best to test on yoursystems with your data. For now thedatabase storage optimization adventure continues my next blog, in which wewill discuss three ILM-focused features of Oracle Database 12c.

In this blog we’re going to discuss Advanced Compression backup compression, one of the many lesser known capabilities included with the Advanced Compression option. In addition to compressingdata/index...

ILM

In-Database Archiving – An ILM Feature You’ve Probably Not Heard About

While my Blogstypically cover the features associated with Advanced Compression, I thought itwould be useful to discuss In-Database Archiving, an Information LifecycleManagement (ILM) feature that is new in Oracle Database 12c and not well known. In-DatabaseArchiving allows users, and applications, to set the archive state for individualrows. Rows that have been marked as archived stay where they are, but are notvisible unless the session is enabled to see archived data. To manageIn-Database Archiving for a table, you must enable ROW ARCHIVAL for the tableusing either CREATE TABLE or ALTER TABLE, for example: CREATE TABLEresidents ( id NUMBER, street VARCHAR2(50), CONSTRAINT tbb1_pk PRIMARY KEY (id)) ROW ARCHIVAL; Enablingin-database archiving causes the addition of a system generated hidden columncalled ORA_ARCHIVE_STATE. SELECT column_id, column_name, data_type, data_length, hidden_column FROM user_tab_cols WHERE table_name = 'RESIDENTS' ORDER BYcolumn_id; COLUMN_ID  COLUMN_NAME        DATA_TYPE  DATA_LENGTH HID ---------------------        ---------  ----------- --- 1         ID                 NUMBER         22      NO 2         STREET             VARCHAR2       50      NO            ORA_ARCHIVE_STATE  VARCHAR2       4000    YES By default, this column is populated with thevalue '0' for each row. When the value for ORA_ARCHIVE_STATE is set to ‘0’ thenthat row is visible to applications, meaning that the row is active and visiblevia a standard query. In the example below, all four records haveORA_ARCHIVE_STATE set to ‘0’. SELECT id, street, FROM residents; ID         STREET ------------------------------ 1         Maple St 2         Main St 3          Elm St 4         Poplar St To make rowsinvisible to applications, update the ORA_ARCHIVE_STATE system generated hiddencolumn with the value '1'. In the example below, two records (ID #2 and 4)have been updated and have had their ORA_ARCHIVE_STATE set to ‘1’, making thoserecords invisible to applications, so now the same query would return thefollowing results: SELECT id, street, FROM residents; ID         STREET ------------------------------ 1         Maple St 3         Elm St Note that theORA_ARCHIVE_STATE column can be set to any string value other than '0' toarchive the data, the DBMS_ILM package uses the following constants: *ARCHIVE_STATE_ACTIVE='0' *ARCHIVE_STATE_ARCHIVED='1 Optionally, youcan specify either ACTIVE or ALL for the ROW ARCHIVAL VISIBILITY sessionparameter to make archived rows visible or invisible for the session. If you specify ACTIVE, then the database willconsider only active rows when performing queries on tables that are enabledfor row archival. This is the default. ALTER SESSION SETROW ARCHIVAL VISIBILITY = ACTIVE; If you specifyALL, then the database will consider all rows when performing queries on tablesthat are enabled for row archival. ALTER SESSION SETROW ARCHIVAL VISIBILITY = ALL; As seen above,In-Database Archiving adds a hidden column to the table to specify thevisibility of each row. For every query and DML, Oracle then injects apredicate to limit the query/DML to only the visible rows. This added predicatecould affect the optimizer, indexes, etc. just like any other predicate.Testing with your own data and applications is the best way to decide whetherany possible overhead is acceptable for the benefit provided. For those whoattended my Advanced Compression Insights session at Oracle Open World 2016(CON6410), I have uploaded my slides into the OOW session repository – andthank you for attending my session! But for now thedatabase storage optimization adventure continues, in my next blog we willdiscuss Advanced Compression with RMAN.

While my Blogs typically cover the features associated with Advanced Compression, I thought it would be useful to discuss In-Database Archiving, an Information LifecycleManagement (ILM) feature that...

Advanced Compression

Lets Talk Oracle Compression at Open World - Session CON6410

Don'tmiss the opportunity to chat with the Oracle Database Advanced Compression teamat one of our sessions or at the Oracle demogrounds. Inmy first session I’ll be sharing best practices, tips, and insights I’vegathered from compression users across the globe, regarding data, index, LOBsand Oracle Recovery Manager backup compression. Even if you already use OracleAdvanced Compression or Oracle Hybrid Columnar Compression, this session willprovide new insights about how to get best from Oracle compression and ILM. Tuesday,September 20th at 11:00am Gettingthe Most out of Database Compression and Information Lifecycle Management SessionCON6410 at Moscone South - room 301 Inmy second session join me, and Database In-Memory Product Manager Andy Rivenes, tolearn how to define both heat map-based and custom policies to control thepopulation and eviction of objects, as well as the recompression of objects toa higher level within the Oracle Database In-Memory column store. We’ll alsotalk about on-disk Heat Map and ADO compression tiering and storage tiering. Wednesday,September 21st at 11:00am UsingAutomatic Data Optimization with Oracle Database In-Memory and Storage-BasedTiering SessionCON6412 at Moscone South - room 301 Ifyou have any questions related to Oracle Advanced Compression, you can ask themat the Advanced Compression and Database Storage Optimization demo booth (SDB035) in the Database area of the demogrounds in Moscone South. Members of theAdvanced Compression development team will be there Monday to Wednesday from9:45am until 5:30pm.

Don't miss the opportunity to chat with the Oracle Database Advanced Compression team at one of our sessions or at the Oracle demogrounds. Inmy first session I’ll be sharing best practices, tips, and...

Advanced Compression

Learn More About Advanced Compression at Oracle Open World 2016

Thereis less than 3 weeks to go until Oracle Open World, the largest gathering ofOracle customers, partners, developers, and technology enthusiasts, whichbegins on September 18th in San Francisco. TheAdvanced Compression development group will be there and you will have multipleopportunities to meet up with us in the demogrounds or in one of our technicalsessions listed below: Tuesday,September 20th at 11:00am Gettingthe Most Out of Database Compression and Information Lifecycle Management SessionCON6410 at Moscone South - room 301 Inthis session Oracle product management shares best practices, tips, andinsights gathered from compression users across the globe, regarding data,index, LOBs, and Oracle Recovery Manager backup compression. Even if youalready use Oracle Advanced Compression or Oracle Hybrid Columnar Compression,this session provides new insights about how to get best from Oraclecompression. Wednesday,September 21st at 11:00am UsingAutomatic Data Optimization with Oracle Database In-Memory and Storage-BasedTiering SessionCON6412 at Moscone South - room 301 Inthis session learn how to define both heat map-based and custom policies tocontrol the population and eviction of objects, as well as the recompression ofobjects to a higher level within the Oracle Database In-Memory column store. Ifyou have burning questions related to Oracle Advanced Compression, you can askthem at the Advanced Compression and Database Storage Optimizations demo booth in the Database area ofthe demogrounds in Moscone South. Members of the Advanced Compressiondevelopment team will be there Monday to Wednesday from 9:45am until 5:30pm. Checkout the fullsearchable OOW catalog on-line to start planning your trip today!

There is less than 3 weeks to go until Oracle Open World, the largest gathering of Oracle customers, partners, developers, and technology enthusiasts, which begins on September 18th in San Francisco. T...

Advanced Compression

Advanced Network Compression – A Lesser Known Feature of Advanced Compression

In this blogwe’re going to discuss the Advanced Network Compression feature, one of themany lesser known features included with the Advanced Compression option. Oracle Databasekeeps compressed data compressed in memory and only uncompresses the data thatgets returned to the user. Now, even that final step can be compressed as well. Advanced NetworkCompression reduces the size of Oracle Net Session Data Unit (SDU) transmittedover a data connection, thereby reducing the time required to transmit the SDU.The server compresses before sending on the network, data travels over thenetwork in compressed form, and the client reads and uncompresses it. Some of thebenefits of using Advanced Network Compression are: IncreasedEffective Network Throughput: Compression allows transmission of large datasetsin less time. SQL query response becomes faster due to the reduced transmissiontime, especially in constrained bandwidth environments. Reduced BandwidthUtilization: Compression saves bandwidth by reducing the amount of data to betransmitted, allowing other applications to use it. This also helps in reducingthe cost of providing network bandwidth. To use AdvancedNetwork Compression a number of parameters need to be configured in thesqlnet.ora file, which resides both on the client and database server. One of theseparameters enables you to set the compression level. Two levels of compressionLOW and HIGH can be specified. LOW level uses less CPU but provides lowercompression ratio, whereas HIGH level uses more CPU and provides highercompression ratio. A discussion ofthe parameters that need to be configured can be found in the Advanced NetworkCompression white paper (here)or see the Oracle Database Documentation. It should benoted that enabling compression may not always increase performance, and can beaffected by several factors. The Advanced Network Compression white paperprovides guidelines on when Advanced Network Compression can increaseperformance. If you’re usingAdvanced Compression don’t forget about the added compression capability youhave with this feature. But for now the database storage optimization adventurecontinues in my next blog, in which we will discuss my Advanced Compressionsessions at Oracle Open World this year – I hope to see you there!

In this blog we’re going to discuss the Advanced Network Compression feature, one of the many lesser known features included with the Advanced Compression option. Oracle Databasekeeps compressed data...

Advanced Compression

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

In an earlierblog I discussed enabling compression for existing tables but failed to discussenabling compression on partitions. So in this blog we’re going to brieflyexamine the ways in which users can enable compression for partitions whilepartitions are online. Sorry if this covers some materials we’ve already lookedat, but I wanted to bring this topic together within a single blog for you. For users ofOracle Database 12c, the Online Move Partition to Any Compressed Formatfeature, one of the many lesser known features included with the AdvancedCompression option, can be used to enable compression, for partitions, online. You can use theALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITIONstatement to move a table partition or subpartition. When you use the ONLINEkeyword with either of these statements, DML operations can continue to rununinterrupted on the partition or subpartition that is being moved. When youinclude the UPDATE INDEXES clause, these statements maintain both local andglobal indexes during the move. Please note thatyou don’t need Advanced Compression for any of what I just mentioned. But here’s whatyou need to know about what Advanced Compression does provide: Certain uses ofOnline Move Partition require the Advanced Compression option: specifically, ifthe feature is used to move a partition or subpartition to a compressed formatincluding Advanced Row Compression and Hybrid Columnar Compression. Another way toenable compression, for an existing partition, is to use Online Redefinition(DBMS_REDEFINITION) which also keeps table/partitions online for bothread/write activity during the migration. Online redefinition will clone theindexes to the interim table during the operation. All the cloned indexes areincrementally maintained during the sync (refresh) operation so there is nointerruption in the use of the indexes during, or after, the onlineredefinition. The exception is when online redefinition is used for redefininga partition -- any global indexes are invalidated and need to be rebuilt afterthe online redefinition. See herefor more information regarding the restrictions that apply to the onlineredefinition of tables/partitions. So if you’reusing Oracle Database 12c, and you want to enable compression for partitionsonline, then consider using the Online Move Partition to Any Compressed Formatcapability. If you are using Oracle Database 11g and want to enable compressionfor partitions online, then please consider using Online Redefinition. But for now thedatabase storage optimization adventure continues my next blog, in which wewill discuss Advanced Network Compression, another of the lesser known featuresof Advanced Compression.

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 brieflyexamine the ways in which users...

Advanced Compression

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

Folks have often asked me when is space released forAdvanced Row compressed tables and when can they see benefits of compression. Ithought it was a good idea to describe this in bit more details (I have alreadyhinted this in my earlier blog post on CriticalStats for Advanced Row Compression). Advanced Row compression is designed to compress table datafor all types of operations on table segment, such as inserts, updates and bulkloads (insert direct load). Advanced Row compression will be triggered and willkeep the data in the compressed format even on data modification, ensuringoptimal compression. In order to get immediate benefits and to minimize theoverhead of compression, the data is always compressed in the foregroundprocess that is inserting/updating the data, but Oracle must guarantee thetransactional consistency, ability to rollback transactions and support for consistent reads and ASOF queries. Even though compression benefits and compression techniquesused are similar, depending on the workload on the system and concurrency atthe level of the data blocks, internally 3 different compression types could beinvoked when using Advanced Row compression. 1) Insert Direct Load Compression Insert Direct Load compression istriggered when the data is inserted using the bulk load mechanisms such asinsert with an append hint or using a SQL loader. In this case, the data isbeing inserted above the segment high water mark (a virtual last used blockmarker for a segment) and can be written out to data blocks very efficiently. The compression engine has quite alot of rows to work with and is able to buffer, compress and write outcompressed rows to the data block. As a result, the space savings areimmediate. Rows are never written in an uncompressed format for Insert DirectLoad compression. 2) Recursive Compression Recursive Compression on the otherhand (and same for Inline Compression described next) is invoked onconventional DML operations, such as single row or array inserts and updates.These compression types write out the rows in uncompressed format, and when thedata blocks reach internal block fullness threshold compression is invoked. Under such scenario, Oracle is ableto compress the data block in a recursive transaction which is committedimmediately after compression. The space saved due to compression isimmediately released and can be used by any other transaction. Compression is triggered by theuser DML operation (aka user transaction), but actual compression of datahappens in a recursive transaction, the fate of compression is not tied to thefate of the user transaction. 3) Inline Compression Just like Recursive compression,Inline compression is also designed for conventional DML operations, such assingle row or array inserts and updates, which write out the rows inuncompressed format first. When the data blocks reach an internal blockfullness threshold, compression is invoked, compressing all the uncompressed rowsin the data block. The main difference betweenRecursive compression and Inline compression is around the concurrency in theblock. Inline compression is designed to compress data when all the rows in theblock are actively modified or locked by the current user transaction. In suchscenarios, as all the rows are already locked by the current user transaction,the compression space savings can be associated with this transaction. Hence,compression is in-line with the user transaction triggering it. This is done toensure that there is no loss in the concurrency. The space saved by Inlinecompression can be used by the compressing transaction anytime, but will beavailable for other transactions only when this transaction commits. In otherwords, the space released is held reserved for the compressing user transactionand can only be used by this transaction till it commits. Once it commits, thespace is released for other transactions. Additionally, if the user transactionrolls back, the compression of this data block is also rolled back. In every case compression of data happens in the foregroundprocess trying to modify the data in the block and Oracle ensures thetransaction consistency, row level locking and concurrency at the data blocklevel. When trying to estimate the overall compression benefits, all threecompression types must be accounted for.

Folks have often asked me when is space released for Advanced Row compressed tables and when can they see benefits of compression. Ithought it was a good idea to describe this in bit more details...

Advanced Compression

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

Customers oftenask if they should use Advanced Compression for their database data instead ofstorage-based compression. While this blog discusses the points I usually makeon this topic, please note that this blog isn’t intended to suggest that OracleAdvanced Compression replace storage-based compression for all generalapplication uses, instead, we’ll discuss why Oracle Advanced Compression isbetter suited for compressing Oracle Database data. Storage-basedcompression/deduplication and Oracle Advanced Compression are two technologiesthat share the concept of compression and together, as well as separately, canoften provide significant storage cost savings. While storage-based solutionsare ideally suited to provide compression for a wide range of applications anduses, storage-based compression solutions can be characterized as being“database-unaware” -- meaning that they lack the application awareness that isneeded to truly optimize the storage and use of Oracle Database data. Withdeduplication and compression enabled at the storage level, the two operationsare executed with no knowledge of the use or value of incoming data. Without true application awareness of thedata value and use, duplicate data is deduplicated, and any compressible datais compressed, without regard for rehydration or decompression impacts on dataretrieval. Because aneffective storage-based deduplication/compression strategy requires fullyhydrated and uncompressed data to be sent to the storage device, the databasedata would either have to be not compressed or would have to be uncompressedbefore going to storage. If the database is not compressed, then all theadvantages of compression (reduced storage, reduced I/O, reduced memory use inthe buffer cache etc...) won’t be available on the database servers. AdvancedCompression is integrated at all levels of the stack - it gives compressionbenefits at storage level, compression benefits to buffer cache, compressionbenefits in making table scans faster, backups faster, database cloning fasterand it can help entire tables/partitions fit completely in memory. Additionally, the data compression format andalgorithms are specifically designed so that queries can run directly oncompressed data (without having to uncompress it first) - making scans andreporting run faster. On the otherhand, storage-based compression (and de-duplication) gives some of the storagebenefits, but gives nothing for the rest - no benefits to the buffer cache(since data remains uncompressed in memory), scans are not faster (likelyslower), database backup is not faster (likely slower), database cloning is notfaster (likely slower) and the memory footprint of the database remainsunchanged. Queries will now have to decompress before accessing the data,making them likely run slower. But again, justto be clear, the point of this blog isn’t to suggest that Oracle AdvancedCompression replace storage-based compression for all general application uses– Advanced Compression can co-exist with storage solutions, we just believethat our database-aware compression is better suited for the database than 3rdparty storage-based compression. But for now thedatabase storage optimization adventure continues my next blog, in which wewill discuss enabling compression on existing partitions, a topic which Imistakenly forgot to include in an earlier blog.

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 makeon this topic, please note...

Hybrid Columnar Compression

Row Level Locking with Hybrid Columnar Compression (HCC)

HybridColumnar Compression (HCC) is a very unique feature in that it gives a verybig gain in compression ratios (10x on average), makes table scans run a lotfaster (up to 10x), and all this not for a subset of DW scenarios, but forevery DW use case. HCC is used by most if not all Exadata DW customers andPOCs. The only real limitation of HCC infirst release was that it was not suited for data that is actively beingmodified (thought it can be still used in such environments as part of an ILMstrategy). As of Oracle Database 11g Release 2, Hybrid Columnar Compression didn’tsupport row level locking like with other table formats in Oracle, such as non-compressedor Advanced Row compressed tables. Oracle’s Hybrid Columnar Compression technology is adifferent and new method for organizing data within a database block. As thename implies, this technology utilizes a combination of both row and columnarmethods for storing data. This hybrid approach achieves the compressionbenefits of columnar storage, while avoiding the performance shortfalls of apure columnar format. A logical construct called the compression unit (CU) isused 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 ina compression unit. With Oracle Database 11g Release 2,HCC supported compression unit level locking; that is, locking the entirecompression unit on an active transaction modifying a single row in the unit. Row level locking is a requirement formixed OLTP and DW environments and is a definite requirement for OLTPapplications. For mixed workloads, ILM approach likely works fine, but needs tobe used much more cautiously. Lack of row level locking was a major restrictionfor HCC, and as a result HCC was mainly targeted for Data Warehouseenvironments. With Oracle Database 12c, HCC tables now support row level locking.In order to do so, it needs to be able to store the locking information on perrow basis (including whether or not a row is locked, and if locked the lockingtransaction information). The approach is similar to locking in non-compressedor Advanced Row compressed tables as described next. For non-compressed or Advanced Row compressedtables Oracle stores a lock byte (index into the list of active transactions inthe data block) in the row header to indicate whether or not the row islocked, and if locked then information about the locking transaction. Thisapproach works well when there can be high concurrency and many activetransactions in a block at a given time. But in the most typical casesallocating a byte per row is an over kill. For HCC tables, the approach is similarbut a more efficient. Rather than allocating the lock byte upfront, Oracleallocates lock bit(s) on demand and based on number of active and anticipatedtransactions on a compression unit. It could pre-allocate enough bits to support anticipated concurrency on the block (dependent on INITRANS setting for the table), OR with a CU level lock for the entire compression unit.  Since most of the times there won’tbe any transactions modifying data in the CUs, there is no need to allocate anybits per row for locking. If there are active transactions and a need forrow level locking, Oracle allocates more bit(s) per row to be able to supportrow level locking. 1 bit per row will be able to support 1 active transaction,2 bits supports up to 3 concurrent transactions, 3 bits supports up to 7concurrent transactions and so on... The maximum need is to support 255 activetransactions per compression unit, as the number of ITLs (interestedtransaction list) in the data blocks is limited to 255. That is, we will needmaximum of 1 byte per row. As you see, this approach doesn’tpre-allocate 1 byte per row; but allocates it on demand. It is possible to support255 concurrent transactions (like uncompressed or Advanced Row compressedblocks); have CU level locking; or anywhere in between. In the worst case,Oracle needs 1 byte per row to support this; but in most practical cases, wewill be much better than that. The next question that comes to one’smind is where does the space for allocating locking bit(s) come from? Just likenon-compressed tables use PCTFREE to extend the ITLs as needed, Oracle usesPCTFREE in the blocks to extend ITLs and allocate any additional lock bits forHCC tables. This is the most natural place to allocate the space from. And where are lock bits stored? Thelock information is stored uncompressed in the CU header. Today Oracle alreadystores other information for rows in the CU header. Lock bit(s) are stored inthe CU header along with other per row information. The ability to have row level lockinghas further widened the applicability of HCC’s columnar and compressiontechnology to OLTP or mixed workload environments. It has allowed formaking the use of HCC wide spread and operationally complete. Row Level Lockingfor Hybrid Columnar Compression is part of AdvancedCompression Option which enables the highest levels of data compression andprovides enterprises with tremendous cost-savings and performance improvementsdue to reduced I/O.

Hybrid Columnar Compression (HCC) is a very unique feature in that it gives a very big gain in compression ratios (10x on average), makes table scans run a lotfaster (up to 10x), and all this not for...

Advanced Compression

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

In this blogwe’re going to discuss the Online Move Partition to Any Compressed Formatfeature, one of the many lesser known features included with the AdvancedCompression option. Here are some keypoints: You can use theALTER TABLE...MOVE PARTITION statement or ALTER TABLE...MOVE SUBPARTITIONstatement to move a table partition or subpartition. When you use the ONLINEkeyword with either of these statements, DML operations can continue to rununinterrupted on the partition or subpartition that is being moved. If you do notinclude the ONLINE keyword, then DML operations are not permitted on the datain the partition or subpartition until the move operation is complete. When youinclude the UPDATE INDEXES clause, these statements maintain both local andglobal indexes during the move. Please note thatyou don’t need Advanced Compression for any of what I just mentioned. But here’s whatyou need to know about what Advanced Compression does provide: Certain uses ofOnline Move Partition require Advanced Compression: specifically, if thefeature is used to move a partition or subpartition to a compressed formatincluding Basic, Advanced Row, or Hybrid Columnar Compression. So remember, ifyou’re using Advanced Compression don’t forget about the added capability youhave when using this feature, you can compress your partitions during the move. To see my short(about 3 minutes) video on the Online Move Partition to Any Compressed Formatfeature, please clickhere (ACO OTN page). But for now thedatabase storage optimization adventure continues in my next blog, in which wewill compare Advanced Compression and hardware-based 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 keypoin...

Hybrid Columnar Compression

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. Statistic Name Description EHCC Analyzer Calls Number of times HCC Analyzer was called to pick the optimal compression algorithms EHCC Archive High CUs compressed Number of HCC CUs compressed at Archive High level EHCC Archive High CUs decompressed Number of HCC CUs decompressed at Archive High level. Every decompression is counted EHCC Archive Low CUs compressed Number of HCC CUs compressed at Archive Low level EHCC Archive Low CUs decompressed Number of HCC CUs decompressed at Archive Low level. Every decompression is counted EHCC Query High CUs compressed Number of HCC CUs compressed at Query High level EHCC Query High CUs decompressed Number of HCC CUs decompressed at Query High level. Every decompression is counted EHCC Query Low CUs compressed Number of HCC CUs compressed at Query Low level EHCC Query Low CUs decompressed Number of HCC CUs decompressed at Query Low level. Every decompression is counted EHCC CUs compressed Total number of HCC CUs compressed EHCC CUs decompressed Total number of HCC CUs decompressed. Every decompression is counted EHCC Rows Compressed Total number of rows compressed using HCC compression algorithms EHCC Rows Not Compressed Total number of rows not compressed with HCC EHCC Total Rows Decompressed Total number of rows decompressed counting every decompression EHCC Compressed Length Total number of bytes compressed using HCC compression EHCC Decompressed Length Total number of bytes decompressed. Every decompression is counted EHCC Check CUs Decompression Number of times CUs decompressed by block checking EHCC Dump CUs Decompressed Number of times CUs decompressed for block dumps EHCC ROWID CUs Decompressed Number of times ROWID CUs were decompressed Cell CUS sent compressed Number of CUs sent compressed from Exadata cells to server to be processed Cell CUs sent head uncompressed Number of CUs sent uncompressed from Exadata cells to server to be processed Cell CUs sent head piece Number of CUs sent head piece from Exadata cells to server to be processed EHCC CUs no rows pass minmax Number of CUs for which can be pruned since no row passes min-max values EHCC Columns Decompressed Number of column CUs decompressed due to selective decompression EHCC Pieces Buffered for Decompression Number of CUs buffered for decompression EHCC Used on ZFS Tablespace Tracks HCC usage on ZFSSA storage EHCC Used on Pillar Tablespace Tracks HCC usage on Pillar storage

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...

Advanced Compression

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

In this blogwe’re going back to revisit Advanced Row Compression and discuss what not tocompress. The use of Advanced Row Compression requires the Advanced Compressionoption. In terms of whatnot to compress, here are some recommendations that I typically make to anyoneusing, or looking to use Advanced Row Compression, these include: 1) Advanced Row Compression is NOT supportedfor use with tables that have LONG data types, 2) If a table/partition is usedas 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 and3) Advanced Row Compression is also not supported on tables with rowdependencies and on clustered tables. In AdvancedCompression with Oracle Database 11g, there had been a limitation where OLTPTable Compression (renamed to Advanced Row Compression in Oracle Database 12c)was not supported for use with tables/partitions that had more than 255 columns(this limitation was removed for conventional operations in 12c). Here are someadditional best practice suggestions (insights) for Advanced Row Compression: Space usagereduction with Advanced Row Compression gives the best results where the mostduplicate data is stored (low cardinality). Sorting data (on the columns withthe most duplicates) prior to bulk loads may increase the compression ratio (atthe expensive of additional load preparation). Larger blocksdon’t always ensure higher Advanced Row Compression ratios. Testing with yourown data is suggested if you want to determine if larger/smaller block sizeswill have an impact on your compression ratio. LOBs are beststored in SecureFiles, and if an organization has licensed the Oracle AdvancedCompression option, they can use Advanced LOB Compression and Deduplication topotentially reduce the amount of storage required for LOBs. I’m also oftenasked if an organization should consider implementing compression at thetablespace level (Advanced Row Compression can be implemented at the partition,table or tablespace level). Regarding whether or not to compress at theTablespace level: For customapplications, I typically suggest compressing at the Tablespace level, butorganizations should consider turning off compression on very high traffic orvery small tables, such as tables used as queues. For commercialpackaged applications, where typically the number of objects can be very large,I typically suggest object selection instead of exclusion. Often the tophundred largest tables and indexes consume the majority of the database space.Compressing those objects, while excluding high traffic objects like tablesused as queues, will give the majority of the compression benefits. Other objects can be compressed over time asneeded. But for now thedatabase storage optimization adventure continues in the next blog, in which wewill discuss, Online Move Partition to Any Compressed Format, a lesser knownfeature of Advanced Compression (but one you will want to know about if you usepartitioning).

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 whatnot to...

Advanced Compression

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. Note that OLTP Table Compression got renamed to Advanced Row Compression in Oracle 12c and is part of Advanced Compression Option. Statistic Name Description HSC Compressed Segment Block Changes Total number of block changes to Tables/Heaps Segments(Compressed only). HSC Heap Segment Block Changes Total number of block changes to Tables/Heaps Segments (Compressed or Non-Compressed). HSC IDL Compressed Blocks Number of blocks compressed during Insert Direct Load  - actual number of blocks. HSC OLTP Compressed Blocks Number of blocks compressed during DML (inserts and updates) activity - actual number of blocks. HSC OLTP Non Compressible Blocks Blocks marked as Final (Not to be compressed again unless substantial changes to the data in the block). HSC OLTP Space Saving Number of bytes saved in total using OLTP Table Compression. Take the delta on every compression and adds. HSC OLTP inline compression Number of Inline Compressions. Inline Compression is compression of a block inline in the user transaction. Space released is associated (held reserved) with the user transaction and can only be used by this transaction till the user transaction commits. Once the user transaction commits, the space is released for other transactions. Total number of block compressions  = Inline + Recursive + IDL Compressions HSC OLTP recursive compression Number of Recursive Compressions. Recursive Compression is compression of a block in a recursive transaction which is immediately committed. Space released is not associated (not reserved) with the user transaction triggering compression and can be used by any transaction immediately. Total number of block compressions  = Inline + Recursive + IDL Compressions HSC OLTP positive compression Number of times compression was beneficial (post compression block had more free space). HSC OLTP negative compression Number of times compression was negative (post compression block had less free space) and was reverted back. HSC OLTP Drop Column Number of compression attempts due to drop column. HSC OLTP Compression skipped rows Number of rows that are skipped for compression (could be deleted or non-compressible etc.). Heap Segment Array Inserts Number of array inserts into Heap Segments. Heap Segment Array Updates Number of array updates into Heap segments.

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...

Advanced Compression

ADO – Automating Storage Tiering for Information Lifecycle Management

In this blogwe’re going to continue the ADO discussion and talk about the role ADO plays indatabase storage tiering. The use of ADO requires the Advanced Compressionoption. How does ADOstorage tiering differ from storage-level tiering? Good question! Storage-level tieringis blind to database I/O types, so it can't tell if a read is for an OLTPtransaction, a DW scan, a stats-gathering job, or a backup (for example). So storage-leveltiering can sometimes get it wrong in terms of moving segments of data to thewrong tier at the wrong time. Heat Map is fully aware of different types ofdatabase I/O, and different reasons for doing those I/O's, so it willautomatically exclude things like RMAN backups, or database maintenance tasks,etc… Keep in mind thatADO storage tiering operates at the segment level, so when an ADO policyimplements storage tiering the entire segment is moved and this movement is onedirection, meaning that ADO storage tiering is meant to move colder segmentsfrom high performance storage to slower, lower cost storage. ADO Insight: Ifan ADO compression policy AND a storage tiering policy both qualify, thedatabase will execute both in a single segment reorganization step. ADO-based storagetiering is not based upon the ADO condition clause (i.e. after “x” days of NOMODIFICATION) as is compression tiering and instead, is based upon tablespacespace pressure. The justification for making storage tiering dependent on"space pressure" is exactly as you might imagine, the belief thatusers will want to keep as much data as possible on their high performance (andmost expensive) storage tier, and not move data to a lower performance storagetier until it is absolutely required. Two important ADOparameters for storage tiering. The value for theADO parameter TBS_PERCENT_USED specifies the percentage of the tablespace quotawhen a tablespace is considered full. The value for TBS_PERCENT_FREE specifiesthe targeted free percentage for the tablespace. When the percentage of thetablespace quota reaches the value of TBS_PERCENT_USED, ADO begins to movesegments (that have TIER TO policies) so that percent free of the tablespacequota approaches the value of TBS_PERCENT_FREE. This action by ADO is a besteffort and not a guarantee. You can set ILMADO parameters with the CUSTOMIZE_ILM procedure in the DBMS_ILM_ADMIN PL/SQLpackage (see here), for example: BEGIN DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_USED,85): DBMS_ILM_ADMIN.CUSTOMIZE_ILM(DBMS_ILM_ADMIN.TBS_PERCENT_FREE,25): END; In this example,when a tablespace reaches the used threshold (85%) defined by the user, thedatabase will automatically move the coldest table/partition(s) in thetablespace to the target tablespace until the tablespace quota has at least 25percent free. Of course this only applies to tables and partitions that have a"TIER TO" ADO policy defined. This frees up space on your tier 1storage for the segments that would truly benefit from the performance whilemoving colder segments, that don’t need Tier 1 performance, to lower cost Tier2 storage. ADO Insight: Thisdoes not apply to storage tiering policies with the Read Only option specified-- they are based on Heat Map based condition clause and tiering policies withconditions based on custom functions. In both these cases, the user hasexplicitly instructed ADO on the event upon which the policy is to be executed. All of thisbehavior can be overridden with custom conditions on "TIER TO"policies: the DBA can write their own conditions using PL/SQL and implementsegment movement based on any conditions they want to encode. But for now thedatabase storage optimization adventure continues in my next blog, in which wewill discuss something totally different, best practices for Oracle compressionPoC’s.

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 ADOstorage...

Advanced Compression

Compressing your Indexes: Advanced Index Compression (PART 2)

As we discussed in the Part1 of this blog series, index entries with many duplicate keys (or duplicateprefix columns in the index keys) can be compressed making it possible toreduce both the storage overhead and the access overhead for large index rangescans or fast full scans. CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS; IndexKey Compression (also known as prefix compression)eliminates duplicate copies of pre-defined number of index prefix columns atthe index leaf block level and is an effective way to permanently reduce the indexsize, both on disk and in cache. The number of prefix columns to consider for compression isspecified by the DBA at the index create time (or rebuild time) and is constantfor all index leaf blocks. Compression can be very beneficial when the prefix columnsof an index have many repeated rows within a leaf block. However, when the leading columns are very selective, or ifthere are not many repeat for the prefix columns, it is possible to makeindexes larger than their uncompressed equivalent due to the overhead to storethe prefix entries. Thus, it is very critical to compress the right indexes andwith correct number of prefix columns. This approach for Index Key Compression has the followingdown sides: Requires the DBA to have a deep understanding of their data in order to choose the most optimal prefix column count Specified prefix column count may not be optimal to produce the best compression ratio for every block in the index Requires running ANALYZE INDEX to obtain the optimal prefix column count, which produces the optimal count for the index as a whole. This is not at the granularity of a block, so may not yield the best compression ratio. Additionally, running ANALYZE INDEX takes an exclusive lock on the table, effectively making the table “offline” for this period Possible to get negative compression, as pointed out earlier, such as in the case where the specified prefix columns are unique in a block You need to be very selective on which indexes to compressand correctly set the prefix column count for these indexes. Oracle protectsyou under certain obvious conditions, but it is your responsibility, as a DBA,to compress the indexes in the right manner. So, why doesn’t Oracle automatically decide which indexes tocompress and automatically computed the prefix column count within compressedindexes? Additionally, why does Oracle use the static prefix count approach inwhich all the leaf blocks are compressed with the same prefix count? If you have been struggling with such questions, the answeris in Advanced Index Compression. Advanced Index Compression ispart of Advanced Compression Option in 12.1.0.2 andaims to automate index compression so that a DBA is no longer required to specify the number ofprefix columns to consider for compression. The correct and most optimal numbers of prefix columns willbe computed automatically on block by block bases, and thus produce the bestcompression ratio possible. It is now possible to have different index leafblocks compressed differently (with different prefix column count) or not becompressed at all, if there are no repeating prefixes. The illustration belowshows logical structure of three consecutive index leaf blocks, each compresseddifferently. Advanced Index Compression can be enabled easily byspecifying the COMPRESS option for indexes. New indexes can be automaticallycreated as compressed, or the existing indexes can be rebuilt compressed. CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESSADVANCED LOW; Note that there is no need to provide the number of columnsin the prefix entries with Advanced Index Compression as this will be computedautomatically for every leaf block. Advanced Index Compression works well on all supportedindexes, including the ones that were not good candidates for prefix keycompression. Creating an index using Advanced Index Compression reduces thesize of all unique and non-unique indexes (or at least doesn’t increase thesize due to negative compression) and at the same time improves the compressionratio significantly while still providing efficient access to the indexes. For partitioned indexes, you can specify the compressionclause for the entire index or on a partition-by-partition basis. So you can chooseto have some index partitions compressed, while others are not. The following example shows a mixture of compressionattributes on the partitioned indexes: CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED LOW local     (PARTITION p1 COMPRESS ADVANCED LOW,      PARTITION p2 COMPRESS,      PARTITION p3,     PARTITION p4 NOCOMPRESS); The following example shows advanced IndexCompression support on partitions where the parent index is not compressed: CREATE INDEX my_test_idx ON test(a, b) NOCOMPRESS local     (PARTITION p1 COMPRESS ADVANCED LOW,      PARTITION p2 COMPRESS ADVANCED LOW,      PARTITION p3); The current release of Advanced Index Compressionhas following limitations: Advanced Index Compression is not supported on BitmapIndexes You cannot compress your Index Organized Tables (IOTs) withAdvanced Index Compression You cannot compress your Functional Indexes with Advanced Index Compression  With this feature for indexes, it is now possible to simplyenable compression for all your B-Tree indexes, and Oracle will automaticallycompress every index leaf block when beneficial, while taking care of computingthe optimal prefix column length for every block. This makes index compressiontruly local at a block level, where both the compression prefix table as wellas the decision on how to compress the leaf block is made locally for everyblock and aims towards achieving the most optimal compression ratio for theentire index segment. A very informative video by Dominic Giles, demonstrating theease of use and performance characteristics of Advanced Index Compression, isavailable here.

As we discussed in the Part 1 of this blog series, index entries with many duplicate keys (or duplicate prefix columns in the index keys) can be compressed making it possible toreduce both the...

Advanced Compression

ADO Example – Automating Compression Tiering for Information Lifecycle Management

In this blog we’re going to explore whatexactly Automatic Data Optimization (ADO) is and the role ADO plays in bothdatabase compression tiering and storage tiering. Theuse of ADO requires the Advanced Compression option. Whatdoes ADO provide for your organization? ADO allows yourorganization to create policies that automate data compression and datamovement and to implement the tiering of compression and storage. ADO policiesare specified at the segment or row level for tables and table partitions.Oracle Database periodically evaluates ADO policies, and uses the informationcollected by Heat Map to determine which policies to execute. Policies will be evaluated and executedautomatically, in the background, during the maintenance window. ADO policiescan also be evaluated and executed anytime by a DBA, manually or via a script (the DBMS_ILM package supports immediate evaluation orexecution of ADO related tasks, see here). Let’s look atsome examples, using both Advanced Row Compression and Hybrid ColumnarCompression, as part of an ADO solution for data compression tiering (we’lllook at using ADO for storage tiering in the next blog). ADO PolicyConditions ADO policiesspecify what conditions (of data access as tracked by Heat Map) will initiatean ADO operation – conditions such as no access, or no modification or creationtime – and when the policy will take effect – for example, after “n” days ormonths or years. Organization can also create custom ADO conditions based upontheir specific business rules. ADO INSIGHT: ADO “conditions” cannot be mixed. Forexample, if the first ADO condition, on a table/partition, uses the condition“no modification”, then the other conditions used for the same table/partitionmust use the same condition type. In our example, we have a table named“orders” that was initially created without any compression. We have turned onHeat Map and are tracking the usage of this table over time. It is theintention, of our organization, to wait until the majority of the post-loadactivities, that are performed initially on the table, complete and then thetable will be compressed, using Advanced Row Compression, without moving the table(meaning the table will be compressed in place). Once the tables cools down,and begins to be primarily used for reports/queries, we’ll then compress thetable with HCC Query High. When the table has become “cold”, and is onlyoccasionally queried (used for reporting purposes), we’ll then compress it evenfurther with HCC Archive High. The example uses the ADO condition “nomodification”. ADO Compression Tiering: Advanced RowCompression The ADO policy below enables AdvancedRow Compression, and since we specified “row” versus “segment” levelcompression, the tables’ blocks will be individually compressed when all therows on the block meet the ADO compression policy that is specified (that beingAFTER 2 DAYS OF NO MODIFICATION) ALTER TABLEorders ILM ADD POLICYROW STORE COMPRESS ADVANCED ROWAFTER 2 DAYS OF NO MODIFICATION; This policy allows the post-loadactivity to subside on the table before compression is implemented. Fororganizations with SLA’s around the load times, this allows the table to becreated and populated as quickly as possible, before implementing compression. Whenusing Advanced Row Compression, compression can be specified at the “row” levelor the “segment” level. Row level allows the table to be compressed in place,block-by-block, as all the rows on a block meet the ADO policy condition. Whenusing either Advanced Row or Hybrid Columnar Compression, tables/partitions canalso be compressed at the segment level, which means the table/partition is rebuilt using compression. ADO Compression Tiering: Hybrid ColumnarCompression (HCC Query High) The next policy, that was specified bythe DBA, will be automatically enforced by the database (at the segment level)when Heat Map determines there has been no data modifications for 90 days – thepolicy changes the compression level of the table to a higher level ofcompression (HCC Query High) when the data is being used primarily forqueries/reporting. ALTER TABLEorders ILM ADD POLICYCOLUMN STORE COMPRESS FOR QUERY HIGH SEGMENTAFTER 90 DAYS OF NO MODIFICATION; Changing the compression fromAdvanced Row Compression, to Hybrid Columnar Compression, occursduring a maintenance window after the specified ADO policy criteria has beenmet. ADO Compression Tiering: Hybrid ColumnarCompression (HCC Archive High) When this table further “coolsdown” additional storage and performance gains (we’ll talk about moving the data toTier 2 storage in the next blog) can also realized when ADO automaticallycompresses the data to the highest level possible (HCC Archive High) withOracle. In this example, this data is still needed for query purposes, but is nolonger being actively modified and only occasionally queried or used forreporting. This cold/historic data is an ideal candidate for HCC Archive High compression. After 180 days of no modificationbeing made to the data, this ADO policy will be applied. ALTER TABLEorders ILM ADD POLICYCOLUMN STORE COMPRESS FOR ARCHIVE HIGH SEGMENTAFTER 180 DAYS OF NO MODIFICATION; With the final ADO compressiontiering policy criteria being satisfied, the data is now compressed to the HCCArchive High level and could also be moved to lower cost storage (Tier 2). Thisallows active data to remain on higher performance tiers and also allows the historicdata, which remains online, to still be accessed by applications as needed andensures a smaller footprint for the historic data. In this simple ILM example OracleDatabase, using Heat Map, automatically evaluated the ADO policies to determinewhen the table was eligible to be moved to a higher compression level and thenimplemented the specified compression automatically, with no additional burdenplaced on database administrators or storage management staff. Although thisexample uses a table, data partitioning could also have been used and all theADO policies specified could have been enforced at the partition-level. This example uses the “bestpractice” approach of compressing using both Advanced Row Compression andHybrid Columnar Compression. Advanced Row Compression (as well as Heat Map andADO) requires the Advanced Compression option. While HCC doesn’t require theAdvanced Compression option, it does have other requirements, please see herefor a blog discussion of HCC. While compression tiering best practice doesinclude the use of HCC, if an organization doesn’t have access to HCC, thenthey would use only Advanced Row Compression in their ADO policies. But for now the database storageoptimization adventure continues in the next blog, in which we will discuss howAutomatic Data Optimization automates database storage tiering with OracleDatabase.

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. Theuse of ADO requires the...

Index Compression

Compressing your Indexes: Index Key Compression (PART 1)

Folks have often commented to me that Table Compression (andAdvanced Row Compression) are  great at helpingto reduce the storage footprint of an Oracle database by a factor of 2x-5x, butOracle shouldn’t forget about the indexes. In my experience, indexes often take up to 50% of the totaldatabase space and it is not uncommon to have 10-20 indexes on a single table(in extreme cases, I have seen upward of 100 indexes per table). The good news is indexes are the first class citizens inOracle and we have NOT forgotten about them. IndexKey Compression is perhaps one of the oldest compression features withinthe Oracle database, released with Oracle 8.1.3 (long before Basic TableCompression in 9.2). If used correctly, Index Key Compression has the potentialto substantially reduce the overall size of indexes. It helps both multi-columnunique indexes and non-unique indexes alike and is also one of the mostcritical index optimization options available. Index Key Compression allows us to compress portions of the key values in an index segment (or Index Organized Table), by reducing the storage inefficiencies of storing repeating values. It compresses the data by splitting the index key into two parts; the leading group of columns, called the prefix entry (which are potentially shared across multiple key values), and the suffix columns (which is unique to every index key). As the prefixes are potentially shared across multiple keys in a block, these can be stored more optimally (that is, only once) and shared across multiple suffix entries, resulting in the index data being compressed. Index Key compression is done in the leaf blocks of a B-Treeindex. The keys are compressed locally within an index leaf block, that is,both the prefix and suffix entries are stored within same block. Suffix entriesform the compressed representation of the index key. Each one of thesecompressed rows refers to the corresponding prefix, which is stored in the sameblock. By storing the prefixes and suffixes locally in the same block, eachindex block is self-contained and in order to construct the complete key thereis no additional block IO involved. It is a very cheap memory only operation. So how does one enable compression on indexes? This can be achieved easily by specifying the COMPRESSoption for the index. New indexes can be automatically created as compressed,or the existing indexes can be rebuilt compressed. CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS; By default the prefix consists of all indexed columns fornon-unique indexes, and all indexed columns excluding the last one for uniqueindexes. In the above example, the default prefix would be (col1,col2). In this case, if the index leaf contains the keys, say (A, B, X), (A, B,X), (A, B, Y), (A, C, X), (A, C, Z), (A, D, X) etc., the prefix column values (A,B) and (A, C) occur repeatedly in the keys and can be compressed. Alternatively you can also specify the prefix length, whichis the number of columns in the prefix. CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS [<prefix_col_length>] The <prefix_col_length> after the COMPRESS keyworddenotes how many columns to compress. The default (and the maximum) prefixlength for a non-unique index is the number of key columns, and the maximumprefix length for a unique index is the number of key columns minus one. If you specify a prefix length of 1 in the above example,then the prefix is only (col1) and the suffix is (col2, col3). For the samelist of values, the prefix value (A) occurs repeatedly in the keys and can becompressed. Prefix entries are written to the index block only if theindex block does not already contain that prefix. They are available forsharing across multiple suffix entries immediately after being written andremain available until the last referencing suffix entry is deleted and cleanedout from the block. Keep in mind that although key compression reduces thestorage requirements of an index by sharing parts of keys across multipleentries, there is a small CPU overhead to reconstruct the key column valuesduring index lookup or scans (which is minimized by keeping the prefixeslocally in the block). A more optimal representation of an index, especially if itstays permanently compressed without any subsequent overhead on the maintenanceoperations, is a good thing. Not only will it have a positive impact on thestorage and space savings, but has secondary benefits such as better cacheefficiency, fewer leaf blocks and less deep tree resulting in potentially fewerlogical IOs and hence a cheaper execution plans. Index Key compression can be useful in many differentscenarios, such as: In case of a non-unique index where ROWID is appended to make the key unique. If such an index is compressed using key compression, the duplicate key is stored as a prefix entry in the index block without the ROWID. The remaining rows become suffix entries consisting of only the ROWID. In case of unique multicolumn index (key compression is not possible for unique single column index because there is a unique piece but there are no prefix grouping pieces to share). In case of Index Organized Tables, the same considerations as unique multicolumn indexes apply. This is all great. But going back to my comment on "Ifused correctly...", so where is the problem? Compression can be very beneficial when the prefix columnsof an index are repeated many times within a leaf block. However, if theleading columns are very selective or if there are not many repeated values forthe prefix columns, then we have a problem. In these scenarios, Oracle still creates prefix entriesstoring all unique combinations of compressed column values within a leafblock. The index rows will refer to the prefix entry, which is hardly shared(if at all) by other index rows. So, it’s possible that compression in thesecases is not beneficial, and could end up bloating the index size due to theoverhead of storing all of the prefix entries. For index compression to be beneficial, you should ensurelow cardinality columns are the leading columns in a concatenated index.Otherwise you run the risk of getting negative compression such that leaf blockscan no longer store as many keys as their non-compressed counterparts. There is also no point in compressing a single column uniqueindex or compressing every column in a concatenated, multi-column unique index.In these cases compression will result in an index structure that increases insize rather than decreasing (negative compression) due to all the overheadsassociated with having prefix entries for each and every index row. The key to getting good index compression is identifyingwhich indexes will benefit from it and how to specify <prefix_col_length>correctly. There is no easy way to figure this out without knowing yourdata well (which often requires you to analyze all your data) and knowing howthe data is going to evolve over time. On the positive side, Oracle does try and protect you undercertain obvious cases. For example, you are not able to create a compressedindex on a single column unique index. Nor are you allowed to specify a prefixlength equal to or greater than the number of columns for unique index, (rememberthe default prefix length for a unique concatenated index is the number ofindexed columns minus one) etc. You may also want to check out RichardFoote’s blog series on index compression. JonathanLewis also gives a great example on how index compression and compressionin general can help with overall database storage and performance. We will talk more on how to overcomethe issues with Index Key Compression and always guarantee a positivecompression with Advanced Index Compression,a feature of Advanced Compression, in Part 2 of thisblog.

Folks have often commented to me that Table Compression (and Advanced Row Compression) are  great at helping to reduce the storage footprint of an Oracle database by a factor of 2x-5x, butOracle...

Advanced Compression

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.In addition to storage tiering, it is also possible to use different types of compression to suit different access patterns. For example, colder data may be compressed more at the cost of slower access. As we have briefly discussed in earlier blogs, Oracle Database provides several types of compression (Advanced Compression and HCC) for use with an organizations data as that data moves through its lifecycle - from hot to active to less active to historical/cold.But how do organizations identify which tables/partitions, across the database, are best suited for compression or storage tiering? To do so requires that the organization have the ability to easily determine which of their tables/partitions are “hot” (the most active data) and which have “cooled” (less active historic/reporting data) down.Heat Map -- fine grained data usage tracking.Heat Map is a feature of the Advanced Compression option, with Oracle Database 12c, that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.Enabling Heat MapYou can enable and disable heat map tracking at the system or session level with the ALTER SYSTEM or ALTER SESSION statement using the HEAT_MAP clause (parameter). For example, the following SQL statement enables Heat Map tracking for the database instance.ALTER SYSTEM SET HEAT_MAP = ON;When Heat Map is enabled, all accesses are tracked by the in-memory activity tracking module. The default value for the HEAT_MAP initialization parameter is OFF. So I’ve turned on Heat Map, now what?Patience will be needed here, it will take some time (could be days or weeks) before enough activity has occurred for Heat Map to track and gather the information needed to enable organization to understand the individual usage levels of their tables and partitions. Heat map tracking data is viewed with V$*, ALL*, DBA*, and USER* heat map views – we’ll talk more about these views in future blogs, but for now, please see the Oracle Database documentation for more information.Now no longer solely dependent upon the collective knowledge of the organization, Heat Map enables organizations to understand where their segments are (Hot Warm or Cold) in terms of the tables/partitions movement through its lifecycle. Using the usage information being automatically gathered and managed by Heat Map, organizations can create the ILM policies (enforced by ADO) which will automate database compression tiering and storage tiering, allowing them to better utilize the organizations existing storage, better manage their storage growth and help with database performance.But for now the database storage optimization adventure continues in the next blog, in which we will discuss how Automatic Data Optimization works with Heat Map and the vital role this feature also plays in terms of automating Information Lifecycle Management, specifically, database compression and storage tiering with Oracle Database.

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...

Advanced Compression

Automating Information Lifecycle Management (ILM)

In the last couple blogs we’ve discussed both Advanced Row Compression and Hybrid Columnar Compression a bit – we will discuss them more, in greater detail, in upcoming blogs. But for now, we’re going to discuss a new topic, automating Information Lifecycle Management, which will include the data compression features we’ve already briefly discussed.Before Oracle Database 12c, when organizations wanted to implement an ILM strategy they would have typically leveraged the Advanced Compression and Data Partitioning options to create a manual database compression and storage tiering solution – a solution which required organizations to have a fairly deep understanding of their data access and usage patterns, often across thousands of tables/partitions.What became very clear, to these organizations after implementing a manual database compression tiering and/or storage-tiering solution, was that the ideal ILM solution was one that is automated, relying not solely upon the collective knowledge of the organization, especially given that some vital owners of that knowledge had long since changed positions or even left the organization.But in order to automate database compression tiering and storage tiering the database has to know what the current usage levels are for the numerous tables and partitions under its management, across possibly hundreds or thousands of tables and partitions. To be effective, it is crucial that the database know where each table and/or partition is in terms of its usage, is the table/partition still being actively modified, or has it cooled down and is primarily historic/archive data now? And then the other question, once data usage tracking was automated then what? Tracking usage is only part of the answer in terms of automating database compression tiering and storage tiering, the other part of the answer is being able to take action (and automating these actions is key) in regards to the database compression and/or storage tiering of these tables and partitions.The answers to ILM automation are these Oracle Database features: Heat MapHeat Map automatically tracks data usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map enables a detailed view of how data is being accessed, and how access patterns are changing over time. Automatic Data OptimizationADO enables organizations to create policies for data compression and data movement and to implement tiering of compression and storage. Oracle Database periodically evaluates ADO policies, and uses the information collected by Heat Map to determine which operations to execute. All ADO operations are executed automatically and in the background, with no user intervention required. But before we dive deeper into the best practices around automating database compression tiering and storage tiering we first need to understand Heat map in greater detail, and after that we’ll look at the best practices around ADO as well – so stay tuned. (or see here for more details if you just can’t wait…)But for now the database storage optimization adventure continues in the next blog, in which we will discuss the vital role that the Heat Map feature plays in terms of automating Information Lifecycle Management, specifically, database compression and storage tiering with Oracle Database.

In the last couple blogs we’ve discussed both Advanced Row Compression and Hybrid Columnar Compression a bit – we will discuss them more, in greater detail, in upcoming blogs. But for now, we’re going...

Hybrid Columnar Compression

Hybrid Columnar Compression Common Questions

Similar to last week, in this blog we’regoing to discuss some of the most common, user asked, questions related to HybridColumnar Compression (HCC), these questions include: Howto enable Hybrid Columnar Compression for existing tables and partitions? Whichapplications (OLTP or Data Warehouse) are best for Hybrid Columnar Compression? DoesHybrid Columnar Compression require a separate license? For existingtables and partitions, there are three best practice approaches to enablingHybrid Columnar Compression. Both methods enable Hybrid Columnar Compressionfor existing data and will compress future data loaded via bulk loads, thedifference between the methods is whether the table remains accessible whileHCC compression is being enabled. - Online Redefinition (DBMS_REDEFINITION) Table remains online for both read/writeactivity while the statement is executing. See herefor more information regarding the restrictions that apply to the onlineredefinition of tables. - ALTER TABLE … MOVE PARTITION … ONLINE Partition/subpartition remains onlinefor both read/write activity while the statement is executing. - ALTER TABLE … MOVE COLUMN STORE COMPRESSFOR … ALTER TABLE MOVE does not permit DML,against the table, while the statement is executing. Which method should you choose? Well,it’s really up to you. If you want to keep tables and partitions online andavailable while compression is being enabled, then using online redefinition orALTER TABLE MOVE PARTITION ONLINE would be best. If you are taking somedowntime to compress, then use ALTER TABLE MOVE – but no matter which is used,all will provide the same level of compression. The question regarding which type ofapplication is best suited for HCC actually isn’t really a common question, butit should be because it is important to understand the answer to this question.HCC compression can be used in both OLTP and Data Warehouse applications, but,and this is important, it is recommended that HCC be enabled only tables orpartitions with no, or infrequent, DML INSERT/UPDATE operations. While data inHybrid Columnar compressed tables can be modified using conventional DataManipulation Language (DML) operations - INSERT, UPDATE, DELETE - performingsuch operations could result in a reduction of the HCC compression ratio – andif performed frequently, could degrade the HCC compression ratio over time(requiring the table/partition be re-compressed). If frequent DML operationsare planned on a table or partition, then Advanced Row Compression is bettersuited for such data. Whether or not HCC requires a separatelicense is also often misunderstood by users. To use HCC you must be running OracleDatabase Enterprise Edition (11.2.0.3 and above) and, this is also important,you must be using a HCC supported Oracle platform, including Exadata,SuperCluster, ZFS Storage Appliance orFS Flash Storage System. Much more information regarding Hybrid ColumnarCompression best practices is available. Please see this Hybrid ColumnarCompression whitepaper (pages 6 to 8) for more information. The database storage optimizationadventure continues in the next blog, in which we will discuss how Advanced RowCompression and Hybrid Columnar Compression are directly related to InformationLifecycle Management (ILM) best practices.

Similar to last week, in this blog we’re going to discuss some of the most common, user asked, questions related to Hybrid Columnar Compression (HCC), these questions include: Howto enable Hybrid...

Advanced Compression

Advanced Compression option - Common Questions

In this blog we’re going to discuss someof the most common, user asked, questions related to Advanced Row Compression,these questions include: - How to enable Advanced Row Compression for existing tables and partitions? - Whichapplications (OLTP or Data Warehouse) are best for Advanced Row Compression? - Whattypes of tables shouldn’t be compressed? For existingtables and partitions, there are three best practice approaches to enablingAdvanced Row Compression: - ALTER TABLE … ROW STORE COMPRESS ADVANCED Enables AdvancedRow Compression for all future DML -- however, the existing data in the tablewill remain uncompressed. - Online Redefinition (DBMS_REDEFINITION) Enables AdvancedRow Compression for future DML and also compress existing data - keeps thetable online for both read/write activity during the migration. See here for more information regarding the restrictions that apply to the online redefinitionof tables. - ALTER TABLE … MOVE ROW STORE COMPRESS ADVANCED Enables AdvancedRow Compression for future DML and also compress existing data.  The ALTER TABLE...MOVE statement does not permit DML againstthe table while the statement is executing. So you may be asking, which is best formy organization? Well, it depends. If you want to keep tables and partitionsonline and available while compression is being enabled, then using onlineredefinition would be best. If you are taking some downtime to compress, thenuse ALTER TABLE MOVE – but no matter which is used both will provide the samelevel of compression. For users of Oracle Database 12c, the AutomaticData Optimization (ADO) feature of Advanced Compression can be used to compressa table or partition in place (no moving of the table or partition needed).We’ll discuss this new way to compress in a future blog, so stay tuned. Advanced Row Compression is ideallysuited for both OLTP and data warehouse applications. This is important, tokeep in mind, as we begin to discuss (in future blogs) using Advanced RowCompression (and Hybrid Columnar Compression) to deploy a compression tieringsolution. When talking with users, and asked ifthey should compress all the tables and partitions in their database, I tellthem that the general recommendation is to compress all the tables in thedatabase with one exception: if the table is used as a queue, i.e. rows areinserted into the table, then later deleted, then more rows are inserted thendeleted -- you shouldn't compress a table used for this purpose. Much more information regarding AdvancedCompression best practices is available. Please see this Oracle AdvancedCompression whitepaper (pages 8 and 9) for more information. The database storage optimizationadventure continues in the next blog, in which we will discuss similarquestions related to Hybrid ColumnarCompression.

In this blog we’re going to discuss some of the most common, user asked, questions related to Advanced Row Compression, these questions include: - How to enable Advanced Row Compression for existing...

Advanced Compression

Advanced Compression option Compared to Basic Table Compression

Welcome to the Database StorageOptimization blog – a blog covering the capabilities of the AdvancedCompression option, including: data,index, network and backup compression as well as database compression tieringand storage tiering. But don’t worry; we’ll also discuss othercompression capabilities, such as Oracle’s Hybrid Columnar Compression (HCC). Why start with the topic of the AdvancedCompression option compared to Basic Table Compression? Well, because this is both a frequentlyasked question – and a fundamental question regarding Oracle compression. If you’re not familiar with Basic TableCompression, then some important points to know about Basic Table Compressionare that it’s a free data compression capability and it is included with OracleDatabase Enterprise Edition. More than a decade ago, Oracle Database 9iRelease 2 introduced Basic Table Compression, which compresses data that is loadedusing bulk load operations, but doesn’t compress data that is added/changedthrough conventional DML operations (INSERT or UPDATE) – if INSERTS and UPDATESare performed on a Basic compressed table/partition over time, then thattable/partition would have to be re-compressed to get the changes compressed.This means that Basic Table Compression isn’t intended for OLTP applications,and instead, is best suited for data warehouse applications (read-mostly) wheredata is loaded using bulk load operations and is never (or very rarely)modified. So why is this important in relation to theAdvanced Compression option? Well, for two important reasons. The first is that the data compressionfeature of Advanced Compression does ensure that DML changes are compressed. OracleDatabase 11g Release 1 introduced OLTP Table Compression, now called AdvancedRow Compression, which maintains data compression during all types of datamanipulation operations, including conventional DML such as INSERT andUPDATE. The second reason, and an important part ofthis Database Storage Optimization blog going forward, is that the AdvancedCompression option brings a number of database storage optimizationcapabilities to the table, beyond just data compression, that folks often don’trealize are available. The Database Storage Optimization blog willdiscuss all of these capabilities, so you know exactly what each capabilitydoes, why you should care (and who knows, maybe for some you won’t care) aswell as provide some usage tips, tricks and best practices – and answer yourquestions. The capabilities that will be covered inupcoming blogs include: Advanced RowCompression Hybrid ColumnarCompression (HCC) Heat Map Automatic DataOptimization (ADO) Advanced IndexCompression Advanced NetworkCompression Online MovePartition Advanced LOBCompression/Deduplication RMAN Compression DataPumpCompression Data Guard RedoTransport Compression And by the way, sorry about the delay innew content (lastblog was March 2015), you’ll see much more activity from Andy and me in thecoming weeks. The database storage optimization adventurecontinues in the next blog, in which we will discuss: Advanced Row Compression.

Welcome to the Database Storage Optimization blog – a blog covering the capabilities of the Advanced Compression option, including: data,index, network and backup compression as well as...

Advanced Compression

Row-Level (Block) Compression Tiering

I was at the Hotsos Symposium giving a presentation on updates in Advanced Row Compression tables and mentioned that there are times when it can be more efficient to allow high volume inserts and updates to take place on uncompressed tables, and then enable an Automatic Data Optimization (ADO) policy to compress the blocks in those tables later, in the background, after most activity has slowed based on Heat Map data. A question was asked about whether that would actually save any space. Unfortunately I didn’t answer the question very well and probably confused the asker, so here’s my attempt at explaining how this really works. As I explained in my presentation, Advanced Row Compression uses a “batch-like” algorithm to compress blocks and is triggered by an insert that causes the block to be “full”. Inserts and updates are made in an uncompressed format so they exhibit the same performance as if they were being performed on an uncompressed block. It is only when an insert triggers a compression that any actual compression is performed on the block. This has the advantage of amortizing the overhead of compression across many inserts and updates. However, during periods of very high volume inserts and updates even this amortized overhead may be too much. If the high volume DML activity is cyclical and not constant then it may make more sense to use row-level, or more properly, block-level compression tiering instead. The reasoning goes like this, leave the table uncompressed so that inserts and updates will be made without compression. This will give the highest possible performance for high volume periods of activity. Then, with ADO, create a policy that will enable row based compression after a period of no modification or activity, depending on your circumstances. Those blocks where all of the rows meet the policy will then be compressed (hence the block-level compression tiering). This will free up space in those blocks for more inserts and this cycle can continue indefinitely on the blocks in the table. What will happen is that blocks will continue to be compressed and filled until they cannot be compressed any further, the same basic process that occurs in tables that have Advanced Row Compression enabled.  It will just not happen in real time or on blocks that are active. Over time space will be saved as blocks are compressed, but no actual space will be returned from the segment, just the row density of the blocks in the table will increase up to the limits of the compression. This will of course save space in the long run, but based on some other questions there seems to be the desire to see space returned from the segment based on the compression. This will only happen when using segment level compression. I have also not mentioned partitioning or whether this applies to generic situations. This is not a one size fits all approach. This is just one tool that can be used when trying to leverage the options available with advanced compression and ADO and Heat Map.

I was at the Hotsos Symposium giving a presentation on updates in Advanced Row Compression tables and mentioned that there are times when it can be more efficient to allow high volume inserts and...

ILM

Tiered Storage

Since this is a blog related to oracle database storage optimization this post is an attempt to explain what we mean by tiered storage. You would think that everyone would have the same definition of tiered storage, but I've found that not to be the case. So, what is tiered storage and how does it relate to an Oracle database storage solution?Tiered storage is the idea of creating different classes of storage with different performance, capacity and price points. The data lifecycle, in an Information Lifecycle Management (ILM) Architecture recognizes that data has different lifecycle characteristics centered around modification and access. Based on this knowledge a database storage architecture can be created to take advantage of that lifecycle if the right tools and infrastructure are available.This is where Oracle Database 12c Automatic Data Optimization (ADO) and Heat Map come into play. These are the tools that are needed to provide a database centric ILM strategy. But wait a minute, storage vendors have already built tiering into their devices, why do we need anything in the database? The answer lies in the nature of how database data is accessed. Storage is aware of blocks, or files, but has no information about the meaning of the data in those blocks or files. The Oracle database on the other hand, understands that a table or partition is a logical entity, and with Heat Map can track that entity's usage characteristics. Why is that important? Again, the answer lies in what happens to all of the data associated with a segment at the storage level. Since storage doesn't understand database data, it can't know that the data it just moved to slower storage will be needed every 30 days for the month end closing cycle. Heat Map on the other hand will know this, and moving the segment can be avoided and not cause a performance impact. The converse is true as well, an ADO policy can be created that can identify segments that should be eligible to be moved to a different storage tier sooner than might otherwise occur if space pressure dictates. The policy can be created with the understanding of the data's lifecycle, something that a hardware device cannot do.To conclude, I would like to stress that the storage tiers that I've been describing here are specifically for active Oracle database files. Since storage "tiering" seems to be an overloaded term, I want to differentiate the use of storage tiering in a database environment to help achieve Information Lifecycle Management as opposed to storage tiering that is referred to by storage vendors to differentiate between production storage versus development/test storage or even backup storage.

Since this is a blog related to oracle database storage optimization this post is an attempt to explain what we mean by tiered storage. You would think that everyone would have the same definition of...

ILM

Automatic Data Optimization – How Do You Know It's Working?

This post is about Automatic Data Optimization (ADO) and how to tell if your policies are working. It's fairly easy to display ADO policies and even to see if they've run. It's not quite as straight forward though, to see if anything actually happened. Unfortunately there is no "log" of what events or actions actually occurred when the policy ran. There are a couple of events that can be set, but I've had only hit or miss success with them detailing what actually happened. For segment level compression and storage tiering it's fairly easy to just look at the results. It's a little harder with policies involving advanced row compression. That's because Oracle is intermixing compressed blocks with uncompressed ones. The easiest way that I've found is to use the function dbms_compression.get_compression_type. This function will return a value that can be used to determine the compression for each row input[1]. This may not be practical on a very large number of rows, but I think that once you've prototyped the behavior you can be pretty sure that the feature is actually working in practice. The following example was run on database version 12.1.0.1.0 and will create a table similar to emp, add some rows to it, enable an ADO policy to compress rows that have not been modified, and then invoke that policy and show what happens. The one "trick" performed is to set the "POLICY CONDITION" to 1 to change the interpretation of DAYS to SECONDS. This is documented in the PL/SQL Packages and Types Reference for the DBMS_ILM_ADMIN package. The CUSTOMIZE_ILM procedure can be used to set the POLICY CONDITION parameter to a 1. Note that this is only done to make testing simpler. SQL> col name format a40;SQL> select * from dba_ilmparameters;NAME VALUE---------------------------------------- ----------ENABLED 1JOB LIMIT 10EXECUTION MODE 3EXECUTION INTERVAL 15TBS PERCENT USED 85TBS PERCENT FREE 25POLICY CONDITION 07 rows selected.SQL> exec dbms_ilm_admin.customize_ilm(dbms_ilm_admin.POLICY_TIME, dbms_ilm_admin.ILM_POLICY_IN_SECONDS);PL/SQL procedure successfully completed.SQL> select * from dba_ilmparameters;NAME VALUE---------------------------------------- ----------ENABLED 1JOB LIMIT 10EXECUTION MODE 3EXECUTION INTERVAL 15TBS PERCENT USED 85TBS PERCENT FREE 25POLICY CONDITION 17 rows selected.SQL> Next a new table is created, populated, row compression status is checked and then an ADO policy is created. CREATE TABLE emp3 ( empno number, salary number, deptid number, name VARCHAR2(100) )/SQL> desc emp3Name Null? Type----------------------------------------- -------- ----------------------------EMPNO NUMBERSALARY NUMBERDEPTID NUMBERNAME VARCHAR2(100)SQL>insert into emp3(empno, salary, deptid, name)select empno, sal, deptno, ename from emp;commit;begin for i in 1..5 loop INSERT INTO emp3 SELECT empno, salary, deptid, name FROM emp3; commit; end loop;end;/SQL> select count(*) from emp3; COUNT(*)---------- 448SQL> @row_compressionCOMPRESSION_TYPE NUM_ROWS-------------------------------------------------- ----------No Compression 448SQL>alter table emp3 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 1 DAY OF NO MODIFICATION;set lines 200set pages 9999col policy_name format a11;col object_owner format a10;col object_name format a20;col object_type format a20;col subobject_name format a20;col enabled format a7;select * from user_ilmobjects;POLICY_NAME OBJECT_OWN OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE ----------- ---------- -------------------- -------------------- -------------------- P137 SCOTT EMP3 TABLE INHERITED_FROM ENABLED -------------------- ------- POLICY NOT INHERITED YEScol policy_name format a11;col tablespace format a20;col enabled format a7;select * from user_ilmpolicies;POLICY_NAME POLICY_TYPE TABLESPACE ENABLED----------- ------------- -------------------- -------P137 DATA MOVEMENT YEScol policy_name format a11;col tier_tablespace format a20;col compression_level format a20;col tier_status format a20;col custom_function format a20 wrapped;select * from user_ilmdatamovementpolicies;POLICY_NAME ACTION_TYPE SCOPE COMPRESSION_LEVEL TIER_TABLESPACE TIER_STATUS ----------- ----------- ------- -------------------- -------------------- -------------------- P137 COMPRESSION ROW ADVANCED CONDITION_TYPE CONDITION_DAYS CUSTOM_FUNCTION ---------------------- -------------- -------------------- LAST MODIFICATION TIME 1 Next we'll force the running of the ADO policy and check to see if any rows were compressed. Remember that we specified no modifications in the last 1 day and that days are being interpreted as seconds. declare v_executionid number;begin dbms_ilm.execute_ilm ( owner=>'SCOTT', object_name=>'EMP3', policy_name=>'P137', execution_mode=>dbms_ilm.ilm_execution_online, task_id=>v_executionid);end;/SQL> @row_compressionCOMPRESSION_TYPE NUM_ROWS-------------------------------------------------- ----------Basic compression level 448SQL> We see that all of the rows have been compressed. Based on our policy this is what we would expect, but why does the compression type specify "Basic compression level"? Apparently there is a bug, bug number 17947871, which reports basic level compression rather than advanced row compression. If the policy is defined at the segment level then "Advanced compression level" is reported which is more along the lines of what you would expect. However, since the two compression types are equivalent in practice this should not be a problem. One other comment is that my example wound up choosing all of the rows to compress. In reality we would expect that only some of the table blocks would have rows that would meet the policy criteria. Remember that the entire block is compressed so all of the rows in the block must meet the policy criteria for the block to be eligible for compression. NOTE: The row_compression.sql script is available here.

This post is about Automatic Data Optimization (ADO) and how to tell if your policies are working. It's fairly easy to display ADO policies and even to see if they've run. It's not quite as straightfor...

ILM

ADO and Two Way Storage Tiering

We get asked the following question about Automatic DataOptimization (ADO) storage tiering quite a bit. Can you tier back to theoriginal location if the data gets hot again? The answer is yes but not withstandard Automatic Data Optimization policies, at least not reliably. That'snot how ADO is meant to operate. ADO is meant to mirror a traditional view ofInformation Lifecycle Management (ILM) where data will be very volatile when firstcreated, will become less active or cool, and then will eventually cease to beaccessed at all (i.e. cold). I think the reason this question gets asked isbecause customers realize that many of their business processes are cyclicaland the thinking goes that those segments that only get used during month endor year-end cycles could sit on lower cost storage when not being used.Unfortunately this doesn't fit very well with the ADO storage tiering model. ADO storage tiering is based on the amount of free and usedspace in the source tablespace. There are two parameters that control thisbehavior, TBS_PERCENT_USEDand TBS_PERCENT_FREE. Whenthe space in the tablespace exceeds theTBS_PERCENT_USED value then segments specified in storage tieringclause(s) can be moved until the percent of free space reaches the TBS_PERCENT_FREE value. It is worthmentioning that no checks are made for available space in the targettablespace. Now, it is certainly possible to create custom functions to controlstorage tiering, but this can get complicated. The biggest problem is insuringthat there is enough space to move the segment back to tier 1 storage, assumingthat that's the goal. This isn't as much of a problem when moving from tier 1to tier 2 storage because there is typically more tier 2 storage available. Atleast that's the premise since it is supposed to be less costly, lowerperforming and higher capacity storage. In either case though, if there isn'tenough space then the operation fails. In the case of a customized function, the question becomesdo you attempt to free the space so the move can be made or do you just stopand return false so that the move cannot take place? This is really the crux ofthe issue. Once you cross into this territory you're really going to have toimplement two-way hierarchical storage and the whole point of ADO was toprovide automatic storage tiering. You're probably better off using heatmap and/or business access requirements and building your own hierarchicalstorage management infrastructure if you really want two way storage tiering.

We get asked the following question about Automatic Data Optimization (ADO) storage tiering quite a bit. Can you tier back to theoriginal location if the data gets hot again? The answer is yes but not...

Advanced Compression

Updates in Row Compressed Tables

Updates in compressed tables, both basic tablecompression and advanced row compression, are not well understood and thisarticle will attempt to explain how they work. In order to do this somebackground will be discussed about how Oracle performs row based compressionand why the structure of the data is so important to the effectiveness of thecompression. More importantly, space usage will be detailed for various insertand update scenarios and the performance impacts that occur when updatingcompressed tables. Basic table compression was first introduced in Oracle 9iRelease 2 and it only supported direct path inserts to achieve compression. In laterreleases ALTER TABLE … MOVE operations and online table redefinition are alsosupported. It produces better compression than advanced row compression, uses less CPU and generates less undo and redo, but additional single row insertsand updates are not compressed, and updates can lead to significant rowmigration. This is a particular problem because the default setting for PCTFREEis 0 which leaves no space in the block for updates which require decompression. Advanced row compression, or OLTP table compression as itwas known in 11g, allows single row inserts and updates to be compressed, andis therefore more suitable for an OLTP environment. Advanced row compressionrequires the Advanced Compression Option and works by only attempting compression when the block is full. This makes thecompression batch like and means that inserts and updates are made uncompressedinto the block. If the block is successfully compressed then it is eligible formore inserts or updates. If the block is full then the PCTFREE setting governshow much free space is available for updates, but at this point the updateswill no longer be compressed because the compression mechanism will not betriggered. Due to the batch nature of the compression algorithm, advanced rowcompression will generally not achieve compression as good as basic tablecompression because there will typically be uncompressed rows leftover in theblock. Both basic table compression and advanced row compressionuse the same compression algorithm. Compression is achieved by replacingduplicate values with a pointer to a symbol table entry stored at the beginningof the block. The duplicate values can occur in one or more columns and therecan be multiple entries in the symbol table. All of the information needed todecompress a row is stored in the database block itself so Oracle doesn’t haveto access any other information, and since the data is part of the block,decompression really just consists of substituting the symbol table entry orentries so it is very fast. It is not at all like uncompressing a zip file. The format of the compression in a block looks like the following (note that this was created from a block dump): block_row_dump:tab 0, row 0, @0x1f1f <- Symbol table entrytl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 02 d1 fa 00 64 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42tab 1, row 0, @0x1f1b <- Compressed rowtl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 00 In this example, we see that we have a symbol table entry at tab 0, row 0. All symbol table entries are associated with table 0 and row data is part of table 1 in row compression blocks. The actual row data starts at tab 1, row 0 in the example above. We see from the “tl” entry that the symbol table entry has a total length of 105 bytes, and the compressed row has a total length of only 4 bytes. If we look a little more closely at this example we see that the “bindmp” (binary dump) field for the compressed row has exactly 4 bytes which is also the same as the total length of the row. In this example the first byte is the flag byte, the second byte the lock byte, the third byte the column count and the fourth byte the entry in the symbol table. Update Tests Note: All tests have been performed on an Oracle 12c (12.1.0.1) database and all table definitions and SQL used to perform the tests has been included in the file located here. A series of tests has been performed to show how well Oracle performs compression and how it handles various update scenarios. The first set of tests involve three tables, one with no compression (table T1), one with basic table compression (table T2) and the third with advanced row compression (table T3). First we’ll load the tables and verify the space usage, and then we will update all of the rows in each table and show the resulting space usage. To keep things simple the tables have only one column and the values used are highly compressible, just a series of 5000 rows with 100 A’s and 5000 rows with 100 B’s. The result after creating and loading the tables: TABLE_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPET1 10 ASSM 256 194 62 0 DISABLEDT2 0 ASSM 24 18 0 6 ENABLED BASICT3 10 ASSM 24 24 0 0 ENABLED ADVANCED We see that the best compression is obtained with basic table compression (T2). Advanced row compression is not quite as good but with a PCTFREE of 10 versus 0 for basic table compression (the defaults) it is still pretty good (T3). Now let’s update the tables, and after updating the 5000 rows with 100 A’s to 100 B’s we get the following: TABLE_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPET1 10 ASSM 256 194 62 0 DISABLEDT2 0 ASSM 96 96 0 0 ENABLED BASICT3 10 ASSM 32 32 0 0 ENABLED ADVANCED We see that the space usage for the table without compression has not changed, but the two tables with compression have changed quite a bit. So what’s happened? Updates in Basic Table Compression Tables We already know that updates are not going to be compressed in tables using basic table compression, but why is our test table now 4 times larger and the used space over 5 times larger?  The answer lies in how Oracle processes the updates on compressed tables. If we consider that Oracle will not re-compress the rows, or at least the columns that were updated, and if we notice that basic table compression tables are created with a default PCTFREE value of 0 then we can be suspicious that Oracle may have chained the rows into new blocks. Where else can it put the updated column values? The following proves our theory: SQL> analyze table T2 list chained rows;Table analyzed.SQL> select table_name, count(*) from chained_rows group by table_name;TABLE_NAME COUNT(*)------------------------------ ----------T2 5000 ----------sum 5000SQL> We see that Oracle has indeed chained our 5000 updated rows. If we dump one of the blocks we see that Oracle has actually migrated the rows to new blocks, and since the migrated rows won’t be compressed we can begin to see why so much space was used. Block header dump: 0x008000a3tab 1, row 0, @0x1f0etl: 9 fb: --H----- lb: 0x2 cc: 0nrid: 0x008000b2.0bindmp: 20 02 00 00 80 00 b2 00 00 The “nrid” field points us to the migrated row. If we look at that row: Block header dump: 0x008000b2block_row_dump:tab 0, row 0, @0x1d7atl: 110 fb: ----FL-- lb: 0x1 cc: 1hrid: 0x008000a3.0col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 We see that the total length of the column is 110 bytes so no compression. We also see that the hrid field points back to our original row so we know these two pieces go together.The last question is what if we increase our PCTFREE to a higher value? Can we avoid row migrations and how much space is required? The answer is that it takes a PCTFREE of 90 (I tested in increments of 10) to avoid row migrations. TABLE_NAME PCT_FREE SPC_MGMT BLOCKS USED_BLOCKS FREE_BLOCKS UNUSED_BLOCKS COMPRESS TYPET1 10 ASSM 256 194 62 0 DISABLEDT2 0 ASSM 96 96 0 0 ENABLED BASICT2A 90 ASSM 256 177 0 79 ENABLED BASICT3 10 ASSM 32 32 0 0 ENABLED ADVANCED Table T2A is the test table and the problem with this is that just as much space is required as the non-compressed table T1. Granted this is an extreme example, but the takeaway is that tables that are compressed with basic table compression should be rarely, if ever, updated. Updates in Advanced Row Compression Tables We have seen that updates are not compressed with basic table compression tables so what about advanced row compression tables? After all, the whole point of advanced row compression is to support OLTP workloads with single row inserts and updates. If we recall our simple test above, our advanced row compression table grew from 24 blocks to 32 blocks after our update. Our update just changed the values in 5000 of the rows to the values of the other 5000 rows. In a perfect scenario the space shouldn’t have grown at all. So what’s going on? I realized that the original test had just inserted 5000 rows with 100 A’s and then 5000 rows with 100 B’s. I was concerned that this might have caused problems with the update since most blocks would have had either all As or all Bs with only one symbol table entry. So I performed a second test that looped 5000 times inserting a row with 100 A’s and then a row with 100 B’s. The second test effectively interleaves the rows and therefore insures that most blocks will have both rows with As and rows with Bs. The total space usage was the same for both tables, but there was a slight difference in the number of rows per block, and that appears to be due to the space required in each block for two symbol tables versus only one (at least that’s the only difference that I could find). After the updates the space usage for both tests was also the same, 24 blocks versus 32 blocks. However, in both cases the end result is the same as what occurred in the basic table compression test, the updates are not compressed. There is a difference with advanced row compression in that it uses a default PCTFREE value of 10 rather than 0. This means that some rows can be updated in place, and for the rest of the rows we see the same row migrations, but now those row migrations are compressed! This explains why the space usage is quite a bit less than with basic table compression. Here’s an example block dump from the second table with interleaved rows: Block header dump: 0x008001a5block_row_dump:tab 0, row 0, @0x1f2ftl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 00 00 fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 0, row 1, @0x1ec6tl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 01 13 fa 00 64 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42tab 1, row 0, @0x1206tl: 106 fb: --H-FL-- lb: 0x2 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 02 01 fa 00 64 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42tab 1, row 1, @0x1ebetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01< data skipped >tab 1, row 15, @0x1e86tl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01tab 1, row 16, @0xf17tl: 9 fb: --H----- lb: 0x2 cc: 0nrid: 0x008001ff.f0bindmp: 20 02 00 00 80 01 ff 00 f0tab 1, row 17, @0x1e7etl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01tab 1, row 18, @0xf0etl: 9 fb: --H----- lb: 0x2 cc: 0nrid: 0x008001ff.f1 bindmp: 20 02 00 00 80 01 ff 00 f1 The block dump excerpt above shows us that we have two symbol table entries for our 100 character A’s and B’s at tab 0, row 0 and tab 0, row 1. We also see that our first data row at tab1, row 0 is uncompressed at 106 bytes, but the very next row is compressed at only 4 bytes. This alternating behavior continues through the rest of the block dump, but when we get to row 16 we see our first row migration. We can assume that this occurs as we exhaust the free space that was reserved by the PCTFREE setting of 10, and based on our data interleaving this seems to make sense. The row migration for row 18 was followed to its destination block below. Note that the block header DBA matches the "nrid" DBA (next rowid), and the row number f1 in hex is row 241 in decimal (the "nrid" field is of the format <data block address>.<row number> in hex). This entry is also shown below: Block header dump: 0x008001ffblock_row_dump:tab 0, row 0, @0x1d7ftl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 01 df fa 00 64 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42tab 1, row 241, @0x140btl: 10 fb: ----FL-- lb: 0x1 cc: 1hrid: 0x008001a5.12col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 0c 01 01 00 80 01 a5 00 12 00 It’s very interesting to note that the migrated row is compressed. The “tl” field, or total length, is only 10 bytes for a 100 byte column, and we see that there is a symbol table entry at tab 0, row 0. So this begs the question, are updates ever compressed in the same block? The answer lies in how advanced row compression works. Compression is only attempted when the free space in the block is exhausted. If a compression can take place then the rows in the block are compressed. Before the first compression, an update is really the same as an insert because there are no compressed rows to update. However, after compression has taken place, if a row that has been compressed is updated, then the resulting row can be re-compressed only if another compression is triggered. This is a key point, another compression has to be triggered by filling the block again. If however, all free space has been exhausted or subsequent inserts don’t trigger another compression then the rows that have been updated will stay uncompressed and subsequent updates will cause row migrations once all space in the block is exhausted (i.e. the reserved space from the PCTFREE setting). I said inserts in the previous sentence because in my testing I only observed compression being triggered by inserts. Let’s show an example of an update getting compressed. The idea here is that as a block is loaded it will reach a point where all free space will be exhausted. Assuming that inserts are what is filling the block a compression will be triggered. Once the block is compressed then more rows can be added to the block. Depending on the data and how compressible it is, there can be many compressions triggered. This test will trigger an initial compression, update compressed rows, and then trigger additional compressions with more inserts. We will then dump the block to prove that our updated rows were indeed compressed. Based on dumping table T1 we find that we get about 68 uncompressed rows per block. A new table is created with 50 rows of 100 A’s, 3 rows of 100 B’s and 20 more rows of 100 A’s. This gives us a total of 73 rows and they will only fit in one block with compression. We can verify that compression has been invoked by dumping the block and checking the rows. Block header dump: 0x008000a5block_row_dump:tab 0, row 0, @0x1f2ftl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 00 40 fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 0, row 1, @0x1ec6tl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 00 03 fa 00 64 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42tab 1, row 0, @0x1ec2tl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 1, @0x1ebetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 49, @0x1dfetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 50, @0x1dfatl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01tab 1, row 51, @0x1df6tl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01tab 1, row 52, @0x1df2tl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42bindmp: 2c 00 01 01tab 1, row 53, @0x1deetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00 This dump shows us that we have two symbol table entries, all rows are compressed except rows 67 through 72 (not shown). And we see that our three rows with B’s are at rows 50, 51 and 52 and they are compressed with total lengths of 4 bytes. Next we’ll update the three rows of B’s to A’s and verify that the updates have taken place. Block header dump: 0x008000a5tab 1, row 49, @0x1dfetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 50, @0x1ad4tl: 106 fb: --H-FL-- lb: 0x2 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 02 01 fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 1, row 51, @0x1a6atl: 106 fb: --H-FL-- lb: 0x2 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 02 01 fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 1, row 52, @0x1a00tl: 106 fb: --H-FL-- lb: 0x2 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 02 01 fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 1, row 53, @0x1deetl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00 We see that rows 50, 51 and 52 have been updated and are no longer compressed because their total lengths are now 106 bytes. They also have not been migrated so they reside completely within the block. We do see that the surrounding rows are still compressed and have not been changed. Lastly we insert an additional 600 rows of 100 A’s with a commit after each, and see if we can trigger additional compressions. Block header dump: 0x008000a5block_row_dump:tab 0, row 0, @0x1f2ftl: 105 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 02 2e fa 00 64 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41tab 1, row 0, @0x1f2btl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 50, @0x1e63tl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 51, @0x1e5ftl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00tab 1, row 52, @0x1e5btl: 4 fb: --H-FL-- lb: 0x0 cc: 1col 0: [100] 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41bindmp: 2c 00 01 00 Now all rows are compressed up to row 557 with rows 558 through 566 remaining uncompressed. The inserts have caused a new block to be added with an additional 106 rows (not shown). We also see that there are no row migrations (from analyzing for chained rows – not shown). Another way of verifying whether compressions have been triggered is to examine the "HSC" statistics for the session performing the DML (See Master Note for OLTP Compression (Doc ID 1223705.1) for a list of the specific statistics associated with row compression). In this case the statistics were captured below: NAME VALUE---------------------------------------------------------------- ----------HSC Compressed Segment Block Changes 620HSC Heap Segment Block Changes 621HSC OLTP Compressed Blocks 1HSC OLTP Non Compressible Blocks 1HSC OLTP Space Saving 54271HSC OLTP positive compression 20HSC OLTP recursive compression 20heap block compress 20 We see that the statistic “HSC OLTP positive compression” has a value of 20 which means that 20 successful compressions were performed during the insertion of the additional 600 rows. Now these weren’t all performed on the original block because an additional block was added to hold the additional rows with a total of 567 rows in the first block and 106 rows in the second. An inspection of the second block shows that 67 rows were compressed and the remaining 39 uncompressed (block dump not shown). This issue of inserts triggering compression when a space threshold is reached also helps to explain why advanced row compression rarely compresses as well as basic table compression. There are usually uncompressed rows that could have been compressed left over in the blocks of advanced row compression tables and this affects the space savings as compared to basic table compression. Summary So we have seen that updates can be compressed in tables using advanced row compression. This seems to be most beneficial for tables that have a mixture of DML activity rather than with tables that are loaded first and then updated. This is a key take away about the behavior of advanced row compression because we have seen that it takes an insert to trigger a block compression. Although updates are never compressed with basic table compession, if a table is going to be loaded and not updated then basic table compression can achieve the best compression possible. If a table will experience updates then advanced row compression should be used. If there are issues with single row insert performance on tables with advanced row compression then the table should be loaded first without compression, and once most insert activity is complete then it can be compressed with advanced row compression.

Updates in compressed tables, both basic table compression and advanced row compression, are not well understood and this article will attempt to explain how they work. In order to do this somebackgrou...

Oracle

Integrated Cloud Applications & Platform Services