Day 1: New Performance Features
December 8, 2020
As we reach the end of 2020, it's a great time to think about how you want to grow your skills in the new year. This could mean learning new technology, extending your role at work to include activities you didn't normally perform or just brushing up on the newest features in Oracle Database. Over the next 12 days, Viscosity will be sharing a daily article on our favorite Oracle database 19c new features; perhaps this could be a jump start to your plans for 2021.
Oracle continues to innovate and add new features constantly. In the area of performance, there have been a number of enhancements or changes over the past eight years, since the introduction of 12c and the child version 19c. Let's start by taking a look at the database optimizer.
The Oracle Optimizer is complex to say the least; its job is to calculate many different ways to access data objects and then pick the best path. For any given request, there are a minimum of thousands of possible methods, and for some request's tens or hundreds of thousands. Between Oracle version 11g and 19c, there were over 400 optimizer techniques added and many existing techniques updated. Optimizer changes facilitate both new features in the databases and new features in operating systems and hardware.
Optimizer techniques are designed around many things, and even though Oracle maintains compatibility from a syntax and data consistency level, performance is not guaranteed to remain the same. Some SQL will perform better, while some will perform worse with each major change in the database version.
Moving from Oracle Database 11g (pre-2011) to Oracle Database 19c (2019) can cause dramatic shifts in the optimizer and all of its main functions. Adjusting applications to support the most current processor, operating system, network, storage, and database technology requires reviewing the assumptions that went into the original design of the application. Comparing the speed and architecture of CPU's, spinning disks, and network from 10+ years ago to today; you can see the need for software updates and to keep current.
Below are some key, specific features or enhancements to Oracle Database in 12c through 19c that affect performance directly or indirectly. It is probable that you will need to change how you manage your Oracle database to take advantage of many of these features. It is also important to note, that a few of these are licensed features, while most are included in the base product.
- In-Memory Aggregation – improves performance of star queries
- In-Memory Column Store – stores data in a columnar format in ……