New Performance Features
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 requests, tens or hundreds of thousands. Between Oracle version 11g and 19c, there were over 400 optimizer techniques added, and many existing techniques were 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 CPUs, spinning disks, and networks from 10+ years ago to today, you can see the need for software updates and keeping 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 the performance of star queries
- In-Memory Column Store – stores data in a columnar format in memory for the fastest possible access
- In-Memory Virtual Columns – virtual columns that calculated data on tables can now be stored in memory
- In-Memory FastStart – In-Memory Column Store data can be saved to disk for faster repopulation at database startup
- In-Memory "Base Level" – Version 19.8 now includes In-Memory, with up to a 16GB column store, without having to license the Database In-Memory option. If you are running Oracle Database 19c Enterprise Edition, apply the 19.8 Release Update and set INMEMORY_FORCE initialization parameter to BASE_LEVEL, and set the INMEMORY_SIZE parameter to a value up to 16GB. For RAC databases, you get a 16GB column store on each RAC instance.
- Adaptive Query Optimization – the optimizer will use performance results from query executions to better optimize future runs of the same query
- Adaptive SQL Plan Management – the nightly performance tuning jobs will automatically accept new SQL profiles that perform better
- Automatic Column Group Detection – statistics can be gathered on column groups that are used during query joins; Oracle now automatically detects these groupings based on workload
- Concurrent Execution of UNION and UNION ALL branches – during SQL execution, steps of the plan involved in UNION and UNION ALL can now be run in parallel
- Concurrent Statistics Gathering – statistics can now be gathered on multiple partitions in the same table or multiple tables simultaneously
- Dynamic Statistics – the optimizer now decides if object statistics are inefficient for the query (not just missing) and will use dynamic statistics if needed, and those statistics will persist for other SQL statements
- Automatic Degree of Parallelism – enhanced to look at more database characteristics when determining the degree of parallelism
- Optimizer Statistic Histograms – two new types of histograms have been introduced for columns: top frequency histogram, hybrid histogram
- Online Statistics Gathering for Bulk Loads – allowing statistics to be gathered during the data load process
- Session-Private Statistics for Global Temporary Tables – statistics for global temporary tables are stored at the session level as opposed to being the same for all sessions
- SQL Plan Directives – SQL compilation and execution statistics are gathered and saved to disk to provide more information to the optimizer for execution plan generation
- Partitioned External Tables – ability to access Hive tables from HDFS based data store. Fully embracing Big Data technology, Oracle external table partitions can now be based on a combination of types including: ORACLE_DATAPUMP, ORACLE_LOADER, ORACLE_HDFS, ORACLE_HIVE
- Optimizer Statistics Advisor – the advisor looks at how statistics have been collected over time and recommends changes
- Enhanced SQL Plan Management – uses a larger pool of plan information for plan baselines and enhanced selective plan capturing
- Cursor-Duration Temporary Tables Cached in Memory – when the same SQL fragment is used multiple times in a query, the results for that fragment are now stored in a temporary table cached in memory
- Tracking Index Usage – new view V$INDEX_USAGE_INFO provides real-time information on index usage prior to being flushed to the DBA_INDEX_USAGE table, which stores the time when the index was used last, the total number of times the index has been accessed, and much more. Since version 12.2, foreign key index usage is now tracked.
- Real-Time Database Operations Monitoring (DBOP Monitoring) – data is now persistent in the AWR, inclusive of SQL and PL/SQL, and support multi-session capture
- Real-Time Materialized Views – stale materialized views can be queried to gain real-time data as Oracle will calculate the delta from the MV logs
- Oracle Database Sharding – data is horizontally partitioned in multiple databases that do not share hardware or software, unlike RAC, which has shared hardware and software
- Automatic Indexing (Only on the Exadata) – automation of index creating, rebuilding, and dropping. Executing SQL is analyzed by an artificial intelligence algorithm that identifies potential new indexes or making unused indexes invisible. Based on SQL execution elapsed time, the algorithm will flip the invisible indexes to usable indexes or vice versa.
- Automatic SQL Plan Management – searches the AWR looking for the highest load SQLs and alternate plans; then implementing SQL plan baselines that provide the best performance
- Real-Time Statistics (only available on the Exadata) – Running large mission-critical systems requires capturing the most current statistics. Statistics against DML operations for insert, update, or delete can be captured for data in real-time, this is especially useful for data loading operations.
- SQL Quarantine (only available on the Exadata) – statements previously terminated by resource manager due to excessive consumption of processor and I/O are put into quarantine and prevented from being executed again, providing another layer of performance protection
- Memoptimized Rowstore / Fast Ingest – inserts can be stored in a temporary buffer in the large pool and then written to disk in a deferred manner to increase performance
Looking for faster daily data loads? Spending less time running statistics? Making SQL statement performance more consistent? Chances are you have not tried to use most of these, and yet they are just sitting in your newly upgraded database, waiting for you to use them. These are just some of the benefits when upgrading to 19c if you start using the new features.
One of the sayings we have at Viscosity is our customers "have four aces in their pocket.” Over the next 11 days, the talented staff at Viscosity, along with our Oracle ACEs, will address more Oracle Database 18c and 19c new features. Continue to join us next year as we continue our Oracle Database 19c hands-on lab workshops.
Happy Holidays!