Oracle Performance Tuning for Developers (2014)

MP4 | AVC 111kbps | English | 1024x768 | 15fps | 7h 01mins | AAC stereo 128kbps | 877 MB

Genre: Video Training

This course introduces developers to the fundamental principles of performance tuning when using Oracle as their backend database.

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.

01. Why Performance Tuning Matters 21:17

Introduction 2:50

Performance Tuning is Not Magic 6:00

Performance Tuning Approach 3:32

Performance Tuning Principles 3:54

Sample Database 4:59

02. Oracle Architecture and Performance Basics 48:51

Introduction 1:25

System Scalability 3:41

Oracle Architecture Overview 4:35

Buffer Cache 2:56

Shared Pool 2:13

Program Global Area 2:24

Oracle Architecture Wrap Up 1:58

Performance Metrics 10:03

Performance Tuning and Database Size 5:55

Building Effective Performance Test Databases 3:36

Performance Tuning Database use Scenarios 9:59

03. Connections and Connection Pools 28:15

Introduction 4:55

Demo: Connecting With and Without Connection Pools 2:47

Using a Connection Pool in .NET 3:26

Using a Connection Pool in Java 3:40

Connection Pool Best Practices 6:14

Demo: Blocking Connections 4:35

Summary 2:34

04. Bind Variables 43:02

Introduction 1:46

The Shared SQL Area 8:17

Contention and Latch Waits 10:07

Matching SQL Statements 6:13

Using Bind Variables in Applications 6:13

Sample Application to Compare Approaches 6:16

Evaluation of Impacts of Bind Variables 4:06

05. Statement Level Performance Tuning 34:45

What is an Execution Plan 4:28

Getting an Execution Plan 2:59

What Does an Execution Plan Contain? 2:54

Reading an Execution Plan 4:25

Analyzing an Execution Plan 2:29

Autotrace Introduction 4:10

Autotrace Example in SQL Developer 4:24

Autotrace Example in SQL*Plus 3:34

Data Size and Execution Plans 4:31

Summary 0:47

06. Execution Plans in Depth 46:19

Introduction 1:16

Table Access Operations 7:38

Demo: Table Access Operations 5:18

Index Lookup Operations 5:42

Demo: Index Lookup Operations 5:18

Index Full Scan Operations 3:26

Join Operations 8:33

Tuning SQL Statements 9:04

07. Indexing Essentials 48:38

Why Indexing Matters 6:19

B-Tree Indexes 4:54

Bitmap Indexes 6:42

Index Column Order Matters 4:52

Demo: Index Column Order 3:58

Index Skip Scan Operations 3:13

Index Selectivity 7:55

Selectivity for Composite Indexes 3:00

Demo: Index Selectivity 4:43

Determining Index Column Order 2:56

08. Advanced Indexing Techniques 46:22

Module Outline 1:42

Covering Indexes 7:55

Function Based Indexes Introduction 7:42

Demo - Function Based Indexes: Case Insensitive Search 4:03

Selective Row Indexing With Function Based Indexes 6:13

Demo - Function Based Indexes: Selective Indexing 3:11

Index Compression 6:29

Invisible Indexes 3:26

Demo: Invisible Indexes 4:03

Summary 1:32

09. Application Indexing Practices 42:16

Introduction 1:13

What Should I Index? 6:53

Indexing Costs and Overhead 4:39

Demo: Indexing Overhead 4:17

Similar Indexes 1:49

Monitoring Index Usage 4:26

Why isn't Oracle Using My Index? 1:01

Missing Leading Edge of Index 1:28

Index not Selective Enough 1:29

Using a Like Clause and a Leading Wildcard 2:38

Like Clauses and Index Selectivity 3:05

Function in the Where Clause 2:49

Data Type Conversion in the Where Clause 2:26

Outdated Database Statistics 2:47

Summary 1:10

10. Monitoring Oracle Applications 35:11

Motivation for Performance Monitoring 6:10

Required Permissions 6:19

Introduction to Queries 0:50

Session Information 2:08

Session Resource Usage 1:15

What Statements are Running Right Now? 1:15

Finding the Worst Performing Statements 4:38

Statements Conducting Full Scan Operations 1:31

Retrieving Execution Plans 0:48

Monitoring Index Usage 2:14

Monitoring Hard Parsing and SQL Using Literal Values 2:59

Table Information 2:46

Index Information 0:52

Summary 1:19

11. Pitfalls and Practices 26:45

Module Introduction 1:27

Separating Transactional and Reporting Databases 4:05

Loading too Much Data 3:08

Committing Data too Frequently 6:20

ORMs and Abstraction From the Database 3:14

ORMs and the n+1 Selects Issue 5:30

Summary 2:59