OCP 12c Using Performance Enhancements in Oracle 12c

You can upgrade your current OCP certification to 12c with a single test. This course dives into the new features of performance enhancements in Oracle 12c, which you need to know to pass the test.
Course info
Level
Advanced
Updated
Jun 23, 2017
Duration
2h 9m
Table of contents
Course Overview
Real-time Database Operation Monitoring
SQL Tuning - Using Adaptive Execution Plans
SQL Tuning - Adaptive Statistics
SQL Tuning - Adaptive SQL Plan Management (SPM)
Resource Manager in the Multitenant Architecture
Multi-process Multi-threaded Architecture and Flash Cache
Index and Table Enhancements
Online Operation Enhancements
Wrapping up Oracle 12c Performance Enhancements
Description
Course info
Level
Advanced
Updated
Jun 23, 2017
Duration
2h 9m
Description

Each version of Oracle comes with new features. You have to know these features to test for your OCP certification. In this course, OCP 12C Using Performance Enhancements in Oracle 12c, you'll learn foundational knowledge related to performance enhancements in the 12c version of the database. First, you'll explore how to go beyond monitoring single SQL statements with the use of database operation sets. Next, you'll learn about Oracle's ability to adapt SQL plans on the fly responding to real-time statistics. Additionally, you'll discover how to better manage Oracle's utilization of server resources through implementation of the multi-threaded environment and the resource manager. Finally, you'll learn how to use the new tools available to the developer and database administrator, to perform system changes while users still access the database objects being changed. When you're finished with this course, you'll have the skills and knowledge of the performance enhancements that are part of the Oracle 12c database, and be closer to having all the knowledge you need for the OCP 12c upgrade test.

About the author
About the author

As a teenager, Tim found a love for teaching, learning, writing, and computers. He believes that everyone should be a lifelong learner. Tim has been teaching for nearly 21 years, either full or part-time. Tim is an Oracle Database Administrator with over 17 years of experience. He works out of Pittsburgh PA and lives in West Virginia with his wife and kids.

More from the author
Effective Reporting with Power BI
Intermediate
2h 16m
Feb 12, 2019
Oracle 12.2 Upgrade
Intermediate
2h 9m
May 16, 2018
More courses by Tim Boles
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone! My name is Tim Boles, and welcome to my course, OCP 12c Using Performance Enhancements in Oracle 12c Database. I'm a senior manager of database services at Hitachi Consulting. I've been working for over 19 years with Oracle Database technologies. When Oracle releases new versions of the database, it is important to get to know the new features. Learning about new performance enhancements in Oracle 12c will help you decide if your systems can take advantage of these new features, and it will help you prepare to upgrade your Oracle certification to the current version. In this course, we're going to introduce you to several new features that will bring a better performance from your Oracle 12c Database. We'll show you how to do more than monitor single SQL statements with real-time database operation monitoring. We'll take a look under the covers at how the optimizer is picking better explain plans on the fly. We'll see how to achieve the best resource utilization to the Resource Manager in switching to the multi-threaded architecture. Finally, we'll check out how to perform DDL while users are still accessing the objects being changed. By the end of this course, you'll know the benefits and some of the limitations of the new performance enhancements of Oracle 12c. These will help you prepare to take the OCP exam 1Z0-060. From here, you should feel comfortable diving into other Oracle OCP 12c courses that I've created on Pluralsight. I hope you'll join me on this journey to learn the new performance enhancements of Oracle with OCP 12c using performance enhancements in the Oracle 12c course at Pluralsight.

Real-time Database Operation Monitoring
This course is part of a series designed to help DBAs that currently have an OCP certification from a previous version of Oracle to learn the technical objectives of the upgrade to OCP 12c test. This course continues the story of Mark, a senior Globomantics DBA, and his research into the new features of Oracle 12c Database. He hopes to gain insight into these features to upgrade his Oracle certification from 11g to 12c. In the process, he hopes to make determinations on which of these new features to implement and how they may impact the standard operational procedures of the DBA team at Globomantics. The team is going to review the performance enhancements that have been highlighted in the Oracle documentation. The areas they want to judge impacted in the new features are the ones that they are most often dealing with such as database monitoring, resource management, and the online redefinition features. The first area that Mark's team wants to look into is any new enhancements in database monitoring. There is a new enhancement called real-time database operations monitoring. They've heard it described as a superset of the real-time SQL monitoring, active session history, and the automatic database diagnostic monitoring tools. In this module, we're going to cover what real-time database operations is, the architecture behind the real-time database monitoring, and creating monitoring, and reporting on database operations.

SQL Tuning - Using Adaptive Execution Plans
Welcome to our session on using adaptive execution plans. Oracle 12c brings with it the adaptive query optimization feature. This can be thought of as the joining of power of the adaptive execution plans and adaptive statistics together enabling an optimizer to make runtime adjustments to execution plans. Since adaptive query optimization has two parts, we'll divide it up into two different sessions. The rest of this session will focus on the adaptive execution plans. In the next session, we'll tackle the adaptive statistics. In this session, we're going to take a little deeper look into the adaptive execution plans aspect of the adaptive query optimization. This adaptive execution plan allows the optimizer to defer the decision on the final plan for a statement until it executes. This could affect join methods, bitmap index pruning, and parallel distribution methods. This session will look at Mark's investigation into the ability of the optimizer to adapt a plan based on information it learns during the execution. This may lead to possible performance boosts. Mark and his team at Globomantics are very interested in this feature because they have read articles on its benefits, as well as some of the problems with it. They want to get a good base knowledge of the architecture and how to monitor this adaptive execution plan. They want to see how it affects join methods, parallel distributions, and bitmap index pruning.

SQL Tuning - Adaptive Statistics
The optimizer depends on statistics to create execution plans. In general, better statistics mean better execution plans. Oracle 12c provides new features that provide the optimizer better statistics. The adaptive query optimization feature depends on adaptive execution plans and adaptive statistics. In the last session, we reviewed adaptive execution plans and how they affect join methods, bitmap index pruning, and parallel distribution methods. This session is going to talk about a group of features that go together to make up adaptive statistics. The optimizer depends on statistics in order to create a good execution plan. Sometimes a query is too complex to rely on table statistics alone. There are three features that help the optimizer choose the best plan for a statement. This session will review the use of dynamic statistics, automatic reoptimization, and SQL plan directives. These all add to the optimizer's ability to choose the best execution plan for a SQL statement. So in general, what are these new features? Well in Oracle 12c, what was once known as dynamic sampling has been enhanced to become dynamic statistics. For cases when adaptive execution plans are not feasible, there is automatic reoptimization. And additional information that the optimizer uses beyond statistics are stored in what is referred to as SQL plan directives.

SQL Tuning - Adaptive SQL Plan Management (SPM)
Welcome to the module, SQL Tuning - Adaptive SQL Plan Management. This module continues the story of Mark, a senior Oracle Database administrator at Globomantics and his review of the Oracle 12c Database new features. Mark and his team know about the SQL Plan Management feature that was introduced in 11g. They are interested in the part that the SPM Evolve Advisor plays within SQL Plan Management feature. In this session, we'll do a quick review of SQL Plan Management. And then we'll give an overview of the SQL Plan Management Evolve Advisor. And then we'll do a demonstration on how to do a manual evolution of a SQL plan.

Multi-process Multi-threaded Architecture and Flash Cache
Hi! I'm Tim Boles, and this session should help anyone wanting to know more about the multithreaded architecture and the Flash Cache of Oracle 12c. This module continues the story of Mark, a senior Oracle database administrator at Globomantics, and his review of the Oracle 12c database new features. His team is really excited to learn more about this multithreaded architecture that occurs in the Linux environment and the utilization of Flash Cache in the new features. In this session, there'll be an overview of the multithreaded architecture, a demonstration showing how to set it up and then see the differences a multithreaded architecture makes. Then we'll go to an overview of the Flash Cache and the changes that occur with 12c with that feature.

Index and Table Enhancements
Oracle 12c introduces two creative enhancements to index and tables, the invisible column and the multiple indexes on the same columns. In this module, we'll continue to follow our friend, Mark, and his team's review of Oracle 12c enhancements. The viewer will be introduced to invisible columns and a demonstration of how to work with invisible columns. We'll talk about multiple indexes on the same set of columns. That will be followed by a demo working with those multiple indexes.

Online Operation Enhancements
Hi! My name is Tim Boles, and this session continues our story of the DBAs at Globomantics and their review of the Oracle 12c new features. Today they're looking at the online operation enhancements that are part of the Oracle 12c release. In past versions of Oracle, limitations existed on performing online redefinition of tables. This session introduces the viewer to the new enhancements in the online redefinition abilities of Oracle 12c that overcome some of those limitations. Mark and the DBAs at Globomantics were excited when the online redefinition capabilities of Oracle first came out. It enables them to perform maintenance in high availability environments even when the users are still working on the system. While the users continue to perform DML statements, Globomantics DBAs can make changes to the table. Instead of a long maintenance period, there is a very minimal time where the users cannot access the table because it is locked in exclusive mode. The team is excited to see Oracle has expanded this capability somewhat in the Database 12c version. We're going to cover several enhancements to Oracle's online redefinition capability in 12c--enhancement to performing redefinition of multiple partitions, changes for virtual private databases redefinition, there's a new lock timeout period, a new procedure called REDEF_TABLE, and some new online database description language capabilities.