Oracle Performance Tuning for Developers

This course introduces developers to the fundamental principles of performance tuning when using Oracle as their backend database.
Course info
Rating
(227)
Level
Intermediate
Updated
Jun 24, 2014
Duration
7h 1m
Table of contents
Why Performance Tuning Matters
Oracle Architecture and Performance Basics
Connections and Connection Pools
Bind Variables
Statement Level Performance Tuning
Execution Plans in Depth
Indexing Essentials
Advanced Indexing Techniques
Application Indexing Practices
Monitoring Oracle Applications
Pitfalls and Practices
Description
Course info
Rating
(227)
Level
Intermediate
Updated
Jun 24, 2014
Duration
7h 1m
Description

Oracle is one of the most popular database systems in use today. Yet many developers do not understand how Oracle works or the principles behind developing high performance Oracle applications. This course targets the .NET or Java developer who is writing an application with a backend Oracle database. Topics include: understanding how Oracle is processing your SQL statements through generating and interpreting execution plans, to designing and using indexes to speed up the data access of your application. Also included is how to use Oracle dynamic performance views to view real time information about Oracle performance, including which SQL statements are the most costly to run. The use of bind variables, connection pooling, and some application best practices round out the topics covered in the course.

About the author
About the author

David Berry is a software engineer with over 15 years of experience developing applications in languages such as Java and C#. Throughout his career, he has worked extensively with enterprise database systems including Oracle and SQL Server.

More from the author
More courses by David Berry
Section Introduction Transcripts
Section Introduction Transcripts

Oracle Architecture and Performance Basics
Hello, my name is David Berry. Welcome in this module on Oracle Architecture and Performance Basics. In this module, we are going to cover some of the foundational concepts you need to be familiar with in order to understand Oracle Performance and to effectively performance tune your application. There are three primary areas this module will cover. First, we'll discuss some concepts around Oracle Architecture and especially around how Oracle uses memory. If you're going to write effect Oracle applications, it is important to know a little bit about how Oracle's architected so you can better understand why Oracle works they way that it does. Second, we're going to discuss different Performance Metrics. Usually, when we think of performance of a function or of a SQL statement. We're just thinking about the amount of time it takes to perform that operation. Well, this is important. There are some other Performance Metrics that we should understand that provide more insight into how efficient our statement is and better guide our tuning efforts. Finally, in this module, we're going to talk about performance testing your application and especially about what makes a good Performance Test Database. You want to make sure to have a good Performance Test Database available, so that you can have confidence that any results you obtain from your tuning and testing efforts will translate into your production environment.

Bind Variables
Hello. My name is David Berry. Welcome to this module on using bind variables in Oracle applications. One of the most important practices in writing applications that use an Oracle database is to always use bind variables in your SQL statements. While easy to do, far too often this practice is not understood, or overlooked. The advantage of using bind variables is that if a query is executed multiple times, Oracle does not need to expend resources to create an execution plan each time but instead can reuse the original execution plan. This saves a lot of resources, especially CPU resources on the database server because most applications execute a relatively small number of statements over and over again. Just as important, when Oracle is able to reuse an execution plan you avoid creating contention around some segments of shared memory in the Oracle database. If this contention does occur, it creates a bottleneck around how fast SQL statements can be processed throughout the entire database. Which not just increases the amount of time it takes to process each statement, but also can greatly diminish the overall throughput of your Oracle database. Finally, using bind variables also provides protection against SQL injection attacks. And while this is a security benefit rather than a performance benefit, it is still an important consideration. Especially because SQL injection attacks always seem to be near the top of the list of application security threats. In this module, we are going to explore not just how to use bind variables in your application, but also to help you gain an understanding of why their impact on performance is so great.

Execution Plans in Depth
Hello. My name is David Berry. Welcome to this Pluralsight module on Oracle Execution Plans In Depth. In the previous module, we introduced the Concept of Execution Plans. And how to Read an Execution Plan. In this module, we are going to understand some of the most common operations, performed within an Execution Plan In Depth. By understanding, what these operations are doing, you'll be better equipped to interpret, what an Execution Plan is telling you and apply this information to tuning your SQL statement. We are going to cover, these nine operations in this module, as these are the most common operations that you will encounter. At the time of this recording, there're close to 200 different operations that can be performed. But most of this operations are relatively uncommon. Understanding, the nine operations shown here, will give you a solid foundation for understanding most of the Execution Plans you will encounter. If you do run across another operation in your Execution Plan, you can always type the name of that operation into you favorite Search engine to get a full description of what that operation is doing. And in these cases, you will usually find that the concepts presented here will help you understand what these other operations are doing.

Advanced Indexing Techniques
Hello. My name is David Berry. Welcome to this module on Advanced Indexing Techniques. In the last module, we covered the essentials of indexing. In this module, we're going to move into some of the more advanced techniques that you can apply in order to maximize the performance of your data access layer. First up, we're going to talk about covering indexes. This is a technique where Oracle can get all the data it needs from the index, avoiding a table operation which consequently reduces IO and the amount of time it takes a query to run. Second, we're going to look at function based indexes, and we're going to do so in two parts. Function based indexes are a technique that is unique to Oracle. So, if you come from another database, you may not be familiar with their application. We'll work through two scenarios or function based indexes to provide an elegant solution to common problems. The scenarios being conducting a case and sensitive search and an index over a subset of rows in a table. Through these examples, you'll be well equipped to design your own function based indexes to address any special situations you might have. Third, we'll talk about index compression which is a way that Oracle can save space by replacing a repeated part of an index key with a special prefix value. As it turns out, this will also provide a performance boost in many situations. Finally, we'll talk about invisible indexes which can be useful to employ when you want to create a new index to try out or drop an existing index, and you need to have some more control over how and when this is done.

Application Indexing Practices
Hello. My name is David Berry. Welcome to Pluralsight and this module on Application Indexing Practices. Over the last two modules on indexing, we've covered a lot of ground about the type of indexes in Oracle and how they work. In this module, we're going to focus a little more closely on how to apply indexes to your application database so you can effectively index that database. In the first part of this module, we'll talk about indexing strategies. The primary focus here will be to help you understand what columns you should be indexing in your database to make sure your application runs efficiently. In the second part of this module, we'll talk about the costs associated with indexes and overindexing. Having too many indexes can also be a performance killer. So we'll talk about how you can find out what indexes your application is using and which ones it isn't, so you aren't paying overhead on those indexes that you aren't using. Finally, we'll wrap up by talking about the reasons why Oracle might not be using your index. There is nothing more frustrating than when you've created an index and it doesn't get used and you can't figure out why. So we'll talk about the most common reasons this occurs.

Pitfalls and Practices
Hello. My name is David Berry. Welcome to this module on Pitfalls and Practices in Developing Oracle Applications. Over the prior modules in this course, we've looked extensively at topics such as reading execution plans and how to index your application. In this module, we'll switch gears a little bit and take a look at some of the practices you should employ when developing your application. First, we'll discuss why you should design your application such, that any reporting you do is separate from your transactional database. Next, we'll discuss why you need to pay attention to how much data your application is loading and especially when this data is being loaded. So you can avoid creating lengthy procedures that load a bunch of data upfront, only to never see this data being used in the application. Third, we'll discuss transaction SCO. And the default commit behavior of the ODP. NET and JVDC drivers. By understanding when you should commit, you will end up with the right functionality in your application and potentially a performance boost. Finally, we'll talk about a couple of common issues that people encounter when using object relational mappers in the development of their application. And what you can do to recognize these issues and solve them so your ORM doesn't cause a degradation in performance.