Oracle Database 12c Performance Tuning and Optimization

This course will teach you how to perform maintenance and manage performance in Oracle Database 12c, and overlaps heavily with the OCP exam objectives.
Course info
Rating
(70)
Level
Beginner
Updated
Sep 24, 2014
Duration
2h 57m
Table of contents
Introduction to Oracle Database 12c Performance Tuning
Monitoring Oracle Database 12c Performance
Tuning SQL in Oracle Database 12c
Managing Resources With Database Resource Manager
Automating Tasks With the Oracle Database 12c Scheduler
Managing Oracle Database 12c Data Concurrency
Description
Course info
Rating
(70)
Level
Beginner
Updated
Sep 24, 2014
Duration
2h 57m
Description

It seems that most Oracle Database administrators enjoy twiddling and tweaking their production Oracle databases/instances to make them run as efficiently as possible. This course will teach you how to perform maintenance and manage performance. In addition, this course overlaps heavily with the OCP exam objectives.

About the author
About the author

Timothy Warner is a Microsoft Most Valuable Professional (MVP) in Cloud and Datacenter Management who is based in Nashville, TN.

More from the author
More courses by Tim Warner
Section Introduction Transcripts
Section Introduction Transcripts

Introduction to Oracle Database 12c Performance Tuning
Hello and welcome to Pluralsight. My name is Tim Warner and I'd like to welcome you to the course entitled Oracle Database 12c Performance Tuning and Optimization. The name of this first module is Introduction to Oracle Database 12c Performance Tuning. Here's what we'll learn in this module. We have some vocabulary to get out of the way first of all. Specifically, we want to understand Oracles approach to database performance tuning. By the end of this course I want to make sure you have all the necessary vocabulary under your belt, such that if you need to, for instance, open a support ticket with object you can use the proper terms. This is going to help Oracle solve your problems in a lot shorter of a time period and you'll also just be more effective frankly as a DBA or as an Oracle database developer if you know what this stuff's called, if for no other reason you can search Google right? After we have the basics, the vocab down, what else are we going to do in this module? Well I want to make sure you're familiar with the Oracle inbox performance tuning tools. How do we know how to keep an eye on database performance and how to tune that performance if we don't understand the tools used to do so? We'll also be reviewing the common causes of Oracle database performance degradation. Some of that should be familiar in your working lives as DBAs and then finally, we're going to start to drill in specifically on the first component of the Oracle performance tuning tool suite, and that is the Automatic Workload Repository or AWR and the Automatic Database Diagnostic Monitor or ADDM. There's definitely a lot of acronyms to learn as we work through this course, that's going to be part of your challenge. Let's get started.

Monitoring Oracle Database 12c Performance
Hi and welcome to Pluralsight. My name is Tim Warner and you're watching the Module entitled Monitoring Oracle Database 12c Performance. In the previous module we set the stage for Oracle performance tuning an optimization. We looked at some, not all, but some of the relevant vocabulary terms, and we answered the question why in first place it's important to monitor Oracle Database performance. In this module we're going to actually start working with the tools, starting with the ADDM. We want to develop a familiarity with ADDM and also the active session history or ASH analytics. You're going to see in practice, in the demos in this module, that they really blend together. ADDM is good for historical data, for looking at comparison periods, looking at baselines, and looking at system snapshots over time, whereas the ASH analytics functions hand and glove with ADDM, we can look at data as it's actually occurring right now. We're also going to roll in for convenience sake a discussion and short demo on the advisory framework in Oracle Database 12c. If you've been with me through my Pluralsight training courses on Oracle Database thus far, you know that the Oracle Database product is chalk full with self-tuning and self-optimization features, and lots of these advisors that can give you, the DBA, targeted advice on improving performance and stability of the system. A lot of interesting stuff in this module, let's get started.

Tuning SQL in Oracle Database 12c
Hello and welcome to Pluralsight. My name is Tim Warner and this module is entitled Tuning SQL in Oracle Database 12c. What we're going to cover in this module is as follows. First, we're going to review the importance of statistics. Statistics, statistics, statistics. Say that five times quickly and I would think of some pop culture saying regarding the pure reliability of statistics, but I'll leave that for another time. The take home message here is we're going to understand the importance of giving Oracle the ability to gather statistics on all of our schema objects, so that Oracle can perform queries as efficiently and accurately as possible. We're then going to turn our attention to the advisory framework that introduced to you in the previous module. We're going to spend time with the SQL Tuning Advisor, taking a look at high-load structured query language statements, giving them to Oracle, creating objects out of them, giving them over to Oracle, having Oracle parse and analyze them, and give us targeted advice on how we can restructure that SQL for better performance. We'll also look at the SQL Access Advisor that sometimes is confused with the Tuning Advisor. There is a little bit of overlap. Specifically, you may see references, or should I say recommendations, to create indexes using either of those tools, but as we'll learn, SQL Access Advisor doesn't work necessarily at the SQL statement level, but instead functions at a higher level of abstraction and looks at your schema objects as a whole. Some interesting stuff in this module, let's get started.

Managing Resources With Database Resource Manager
Hello and welcome to Pluralsight. Tim Warner here welcoming you to the module entitled Managing Resources With Database Resource Manager. We have three main objectives for this module. The first is to understand what features Database Resource Manager brings to the table for us as Oracle Database 12c administrators. As we're going to see very shortly, DBRM, as it's called affectionately for short in abbreviation form, bridges the gap between Oracle Database and the host operating system, such that you can provision certain sessions to have particular levels of service and also prevent some sessions from overreaching and consuming too many system resources. Of course, you have to understand how to actually do the work right? It's one thing to understand how cool a feature like Database Resource Manager is, it's another to actually perform it. There are, and I'm sure this is no surprise to you, a number of components to this technology. It's crucial that you understand the purpose of each and how they fit together. Once you have your resource plan or plans in place it's time to monitor that to make sure that it's working, so we'll close this module with a bit on monitoring DBRM, and as a matter of fact, we're going to continue our discussion of Resource Manager into the next module because, as it happens, DBRM in daily practice relies quite heavily upon the Oracle Database Scheduler. Let's get started.

Managing Oracle Database 12c Data Concurrency
Hello and welcome to Pluralsight. Tim Warner here. The name of this module is Managing Oracle Database 12c Data Concurrency. This is the final module in this series. I hope you've enjoyed it thus far. We're going to go out with a bang by understanding some of the mechanics behind the Oracle multi-user model. Now any relational database system worth its salt supports multiple concurrent sessions. The goals of this module are number one, to understand how Oracle allows multiple users to access the same data. Short answer, there are built-in locking mechanisms to support that. This gives us the ability to do data concurrency, which as I said, is multiple users accessing the same data simultaneously, and also data consistency, in which Oracle and the DBA working together can minimize the possibility of one user's session, stepping on another user's session data. The centerpiece of this module, as far as skills, deals with how do you as a DBA step in to monitor and resolve locks, blocks, and waits that are invariably a part of any DBAs life. If you've worked with locks and blocks before on other systems like SQL Server or MySQL, the principles here are basically the same, it's just a case of understanding the Oracle tools. Let's get started.