Top 60 Oracle Blogs

Recent comments

engineered systems

Oracle 19c: Automatic Indexing. Part 2. Testing Automatic Indexing with Swingbench

This is the second of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c.
I have used Dominic Giles' Swingbench utility to create a realistic and repeatable OLTP load test using the Sales Order Entry (SOE) benchmark.  This post explains how I set up and ran the test, and what results I obtained.

Oracle 19c: Automatic Indexing. Part 1. Introduction

This is the first of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c, available on engineered systems only. Initially, I simply wanted to see what it does and to understand how it worked.
Next, I wanted to see how good it is. I created a test based on Dominic Giles' Swingbench Sales Order Entry benchmark. Having dropped the secondary indexes (ones not involved in key constraints), I wanted to see which Automatic Indexing would recreate and whether that would reinstate the original performance.

References and Acknowledgements 

This blog is not intended to provide a comprehensive description of Automatic Indexing.  I explain some things as I go along, but I have referenced the sources that I found helpful.

Oracle 19c Real-Time and High-Frequency Automatic Statistics Collection

I gave this presentation at the UKOUG Techfest 19 conference.  This video was produced as a part of the preparation for that session.  The slide deck is also available on my website.

It takes a look at the pros and cons of these new 19c features.  They are only available on Engineered Systems.  Both features aim to address the challenge of using data that has been significantly updated before the statistics maintenance window has run again.

Data Warehouse Design: Engineered Systems Considerations

This post is part of a series that discusses some common issues in data warehouses.

On an engineered system, a key feature is that Bloom filters are pushed to storage cells during smart scan,  Additionally, a Bloom filter computed from a join column of a one table can be used against another table.  Storage index can skip I/O against the large fact table based on a Bloom filter calculated from a small dimension table (see Tanel Poder's Blog: Combining Bloom Filter Offloading and Storage Indexes on Exadata)
This shifts the balance away from Star Transformation, so you are far less likely to want to add bitmap indexes.

Choosing the Right Compression

Choosing the right database table compression matters.  It affects the size of the objects and also the performance of your application.  Compression makes objects smaller, and for many systems, this is an objective in itself, but it also affects performance.  As compressed objects are smaller, less physical I/O is required to access them.  However, that saving is paid for in CPU.  More CPU is required to compress the data when it is written, and then again (albeit usually less CPU) to decompress it when you read it.  The question is whether the saving in I/O pays for the additional CPU.

There are a number of forms of compression to choose between.  They have different characteristics.  The resulting objects are different sizes, and they require different amounts of CPU to compress and decompress.

Oracle Database Appliance — What Does It Mean for You and Your Business?

When I first heard about Oracle Database Appliance and what it does, I got really excited — I saw great potential in this product. When we got our hands dirty and started testing the appliance, I become confident that this product will be a hit. Now it’s finally the time when I can share my [...]