Office Productivity
Oracle
Oracle SQL
Oracle SQL Performance Tuning Masterclass 2020
4.5 (2,537 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
13,850 students enrolled

Oracle SQL Performance Tuning Masterclass 2020

Become an Expert on Oracle SQL Tuning and Solve All The Performance Problems of Your SQL Queries and the Database!
Bestseller
4.5 (2,537 ratings)
Course Ratings are calculated from individual students’ ratings and a variety of other signals, like age of rating and reliability, to ensure that they reflect course quality fairly and accurately.
13,835 students enrolled
Created by Oracle Master Training • 60,000+ Students Worldwide, Code Star Academy
Last updated 6/2020
English
English [Auto]
Preview this course
Oracle SQL Performance Tuning Masterclass 2020
Current price: $139.99 Original price: $199.99 Discount: 30% off
5 hours left at this price!
Buy now
30-Day Money-Back Guarantee
This course includes
  • 19.5 hours on-demand video
  • 85 articles
  • 9 downloadable resources
  • 2 Practice Tests
  • Full lifetime access
  • Access on mobile and TV
  • Assignments
  • Certificate of Completion
Training 5 or more people?

Get your team access to 4,000+ top Udemy courses anytime, anywhere.

Try Udemy for Business
What you'll learn
  • Learn How to Solve Critical Performance Problems with Step by Step Approach!
  • Learn Advanced Indexing Techniques for Ultimate Database Performance!
  • Learn How to Use Various Performance Tuning Tools for In-Depth Assistance!
  • Maximize the Query Performance by Using Advanced Tuning Tecniques!
  • Learn Oracle Database Architecture by "Tuning" aspects.
  • Identify and Optimize the Performance of poorly performing (bad) SQLs.
  • Understand SQL Tuning Terminologies
  • Learn How to Create "Better" Queries
  • Learn How to Prioritize your SQL Tuning Efforts.
  • Learn the Details of How an SQL Code is Interpreted
  • Learn Useful Hints to improve Performance of your Queries
  • Learn & Improve How to Use Oracle Optimizer and Execution Plans Well
  • Various Tips and Tricks to make the Oracle SQL queries run faster.
  • Oracle Performance Tuning on both Oracle11g and Oracle12c Environment.
  • Learn Efficient Schema Design
  • Lifetime Support from the Authors.
  • Weekly Quizzes & Assesment Tests
  • Working materials and always up to date!
Requirements
  • SQL Knowledge
  • Basic PL/SQL Knowledge
Description

★ Here is the most comprehensive SQL Tuning course that you will not need to take any other courses in anywhere.

★ This course will be continuously getting updated with more lectures, quizzes, assessments to enhance your knowledge on Oracle SQL Tuning.

The most common problem of working with SQL is, performance problems. Most of the developers don't know how to write well-performing queries. So in time, this problem grows and becomes a problem for both the developer and the company.

This course will teach you how to tune your SQL statements and database step by step for a perfect performance! You will learn how to write fast, reliable, and secure queries. Besides, you will learn the database architecture and will be able to optimize the database workloads.

Once you complete this course, you will recognize the database terms better and will use the most appropriate one for the performance.

Once you complete this course, you will be able to:

  • Find the poorly performing queries

  • Learn Advanced Query Tuning Techniques

  • Understand the Database Architecture to perform Better Tuning Techniques

  • Learn Advanced Indexing Techniques to Maximize Database Performance

  • Learn how to use various Performance Tuning Tools

  • Compare BAD vs GOOD Queries with Hands-on Examples.

  • Gather Statistics for the bad SQLs

  • Interpret the Execution Plans with different methods

  • Choose an appropriate SQL tuning approach

  • Guide the Oracle Optimizer for a better execution

  • Use various tuning techniques like using bind variables, different types of indexes, and much more.

  • Improve the performance of the queries & database

  • Gain Complete Knowledge in Oracle SQL and Performance Tuning!


Briefly, the knowledge of SQL Tuning is a "must" for IT Professionals and this course is the magic pill for that you're seeking for Becoming a Performance Expert!

If you want to step ahead in your career, "SQL & Performance Tuning" should be your next step.

See you in the first lecture!

Who this course is for:
  • SQL Developers
  • Data Analysts
  • Data Scientists
  • PL/SQL Developers
  • Oracle DBAs
  • Oracle BI Developers
Featured review
Exam Prep Center
Exam Prep Center ( 9 courses, 3 reviews )
a year ago
I absolutely recommend this course to anyone looking to tune and optimize the performance their SQL queries or the database performance on the Oracle Database. I think this is the most comprehensive and well organized tuning and performance course, in Udemy. Covering all the details, Many thanks to Omer.
Course content
Expand all 226 lectures 20:01:38
+ – SQL & Perfomance Tuning Course Introduction
3 lectures 10:26

This Overview lecture explains what will this course cover

SQL & Performance Tuning Course Overview
Preview 04:52

This lecture is about how to use the Udemy platform. And also includes some useful tips to get most of out the course.

UDEMY 101: How to Use Udemy? +Some Useful Tips (Do not Skip)
05:01

This lecture includes the working document of our SQL and Performance Tuning Course with a warm welcome message.

Welcome! +Course Document
00:33
+ – What is SQL (Performance) Tuning and Why We Need?
2 lectures 12:15

This lecture explains what is sql tuning and why we need to perform some tuning techniques

What is SQL Tuning and Why to Do That?
Preview 05:55

This lecture explains some critical information that you need to know before starting to learn performance tuning

What to Know Before Starting the Course?
Preview 06:20
+ – Oracle Database Architecture
14 lectures 01:08:36
Do You Have a Running Database in Your PC?
00:29

Learning the architecture is really important for performance tuning. This lecture explains why to know it and how much to know among the whole architecture

Why to know the Oracle Database Architecture and how much to know?
02:31

First part of the architecture overview lecture

Oracle Database Architecture Overview (Part 1)
Preview 09:17

Second part of the architecture overview lecture

Oracle Database Architecture Overview (Part 2)
06:04

Data in database are stored in logical data blocks. This lecture explains the Oracle database data blocks in details.

Database Data Blocks in Detail
07:57

One of the important part of the database architecture is PGA. You need to learn PGA to perform better tuning techniques. This lecture explains PGAs in details.

What is PGA?
Preview 05:32

Shared pool is very critical for parsing performance. This lecture explains shared pool and its sub-memory areas with details.

What is Shared Pool?
06:31

All the data is processed in the buffer cache. If you know buffer cache, you can perform better tuning techniques. This lecture includes the buffer cache.

What is Buffer Cache?
05:24

Sometimes redo logs decreases the performance. This lecture explains the redo log buffer in the PGA.

What is Redo Log Buffer?
04:18

As the name implies, undo is for undo. So you will learn how a rollback is done with undo segments

What is Undo?
03:49
How a DML is processed and committed
04:28
Automatic Memory Management
02:06
Oracle Database Storage Architecture
03:57
Logical and Physical Database Structure
06:13

This quiz has questions about Oracle Database Architecture

Quiz - Database Architecture
8 questions
+ – SQL Tuning Basics
14 lectures 01:37:42
When to Tune?
07:40
What is a Bad SQL?
05:08
Effective Schema Design
08:42
Table Partitioning
07:15
How an SQL Statement is Processed?
09:32
Why do we need the Optimizer?
05:41
Optimizer Overview
03:25
Query Transformer
08:44
Selectivity & Cardinality
08:02
What is "cost" in detail?
04:51

In this SQL Performance and SQL Tuning Tutorial, students will learn SQL Plan Generator.

Plan Generator
03:56
Row Source Generator
03:38
SQL Tuning Principles and Strategies
08:10
Query Analysis Strategy
12:58

This assessment test will evaluate how much you learned.

SQL Tuning Basics Assessment Test
12 questions
+ – Execution Plans & Statistics
16 lectures 01:39:02
Execution Plan and Explain Plan in Details
07:24
Generating Statistics (Part 1)
06:16
Generating Statistics (Part 2)
07:15
Generating Statistics (Part 3)
08:50
Generating Statistics (Code Samples)
00:20
Generating Execution Plan
12:06
Generating Execution Plan (Code Samples)
00:09
Autotrace
Preview 12:38
Autotrace (Code Samples)
00:09
V$SQL_PLAN View
Preview 11:11
V$SQL_PLAN (Code Samples)
00:23
Reading the Execution Plans (Part 1)
13:12
Reading the Execution Plans (Part 2)
10:29
Reading the Execution Plans (Code Samples)
00:07
Analyzing the Execution Plans
08:18
Analyzing the Execution Plans (Code Samples)
00:14

You will review your knowledge about generating the statistics in this practice test

Execution Plans & Statistics
10 questions
+ – Table & Index Access Paths
17 lectures 01:32:09

In this lecture, you will learn the indexes with their structures and working styles

What are Indexes and How They work in details?
10:51

In this lecture, you will see an overview of the table and index access paths

Types of Table and Index Access Paths
11:59
Table Access Full
08:35
Table Access Full (Code Samples)
00:11
Table Access by ROWID
06:18
Table Access by ROWID (Code Samples)
00:07
Index Unique Scan
04:48
Index Range Scan
10:40
Index Range Scan (Code Samples)
00:30
Index Full Scan
Preview 09:34
Index Full Scan (Code Samples)
01:00

This lecture explain the details of index fast full scan in SQL Performance Tuning

Index Fast Full Scan
06:37
Index Fast Full Scan (Code Samples)
00:29

This lecture includes the index skip scan access path in SQL Tuning to improve the performance

Index Skip Scan
14:14
Index Skip Scan (Code Samples)
00:25

This lecture explains the index join scan access path in SQL performance tuning

Index Join Scan
05:37
Index Join Scan (Code Samples)
00:12

This Quiz includes the questions about table and index access paths in SQL Performance Tuning

Table & Index Access Paths
10 questions
+ – Optimizer Hints
4 lectures 20:55
What are Hints and Why to Use Them?
04:04

In this lecture you will see how to use the optimizer hints for SQL Performance Tuning by many examples

How to use Hints
15:21
How to use Hints (Code Samples)
01:21
List of Some Useful Hints
00:08

Test yourself about using the hints for performance

Using Hints
5 questions
+ – Join Operations
18 lectures 01:14:48

In this lecture, you will see an overview of the join methods in SQL Performance Tuning.

Join Methods Overview
05:10

This lecture includes the nested loop joins in details in Oracle SQL Performance Tuning

Nested Loop Joins
12:09
Nested Loop Join (Code Samples)
00:31

This lecture explains the sort merge join of the join methods in SQL Tuning Aspect

Sort Merge Joins
10:19

This lecture includes the code samples used in sort merge join lecture, in SQL Performance Tuning Course.

Sort Merge Join (Code Samples)
00:24

This lecture explains the hash joins for SQL Performance Tuning

Hash Joins
11:08

This lecture includes the code samples that we used in the hash joins lecture for SQL Performance Tuning

CODE: Hash Joins
00:09

This lecture explains the cartesian joins for SQL Tuning Aspect

Cartesian Joins
06:28

This lecture includes the codes used in the Cartesian Joins lecture.

CODE: Cartesian Joins
00:03

This lecture includes an overview of the join types in SQL Tuning Aspects

Join Types Overview
03:00

This lecture explains the equijoin and nonequijoin join types in SQL Tuning

Equijoins & Nonequijoins
03:36

The examples used in the equijoins and nonequijoins lecture of SQL Tuning

CODE: Equijoins & Nonequijoins
00:06

In this lecture, you will see the performance aspects of the outer joins

Outer Joins
11:16
CODE: Outer Joins
00:31
Semijoins
06:00
CODE: Semijoins
00:10

This lecture explains the antijoins for performance tuning aspect

Antijoins
03:26
CODE: Antijoins
00:19

This quiz have questions on various join operations, and helps you to test yourself on which one to choose

Join Operations
7 questions
+ – Other Optimizer Operators
24 lectures 01:11:27

In this lecture, you will see how to use the result cache on your queries and how to understand it from the execution plans

Result Cache Operator
11:23
CODE: Result Cache Operator
00:18
View Operator
07:29
CODE: View Operator
00:37

In this lecture, you will see the clusters in database performance aspects

Clusters
14:31
CODE: Clusters
00:31

In this lecture, you will learn how and why the database sorts the rows and how it shows that in the execution plans

Sort Operators
07:07
CODE: Sort Operators
00:10

In this lecture you will see what does INLIST operator do in the execution plans for on sql tuning perspective

INLIST Operator
04:25
CODE: INLIST Operator
00:17

In this lecture you will see what does count stopkey do in the execution plans for on sql tuning perspective

Count Stopkey Operator
03:34
CODE: Count Stopkey Operator
00:06

This lecture explains the first row operator in the explain plans with the MIN and MAX functions.

First Row Operator
05:15
CODE: First Row Operator
00:08

This lecture explains the filter predicate on the explain plans

Filter Operator
01:41
CODE: Filter Operator
00:02

This lecture explains the concatenation operator in the explain plans and the concatenation hint in the query

Concatenation Operator
03:06
CODE: Concatenation Operator
00:04

This lecture explains the union and union all operators in the execution plans for tuning

UNION Operators
02:54
CODE: Union Operators
00:07

In this lecture you will learn the intersect operator and how to tune it

Intersect Operator
05:20
CODE: Intersect Operator
00:18

This lecture explains the minus operator in the explain plans and how to tune it in basic

Minus Operator
01:49
CODE: Minus Operator
00:11

This quiz has questions about the other optimizer operators than explained in this course before

Other Optimizer Operators
5 questions
+ – SQL Tuning Techniques
58 lectures 04:53:49

In this lecture, you will learn how to find a performance problem and how to determine a solution for that problem

How to find a performance problem and its tuning solution?
15:05
Ways of Getting the Execution Plan and the Statistics
16:27

In this lecture, you will learn how to use the real-time sql monitoring tool via the SQL Developer and the Enterprise Manager

Using the Real-Time SQL Monitoring Tool Part 1
10:35

In this lecture, you will learn how to use the real-time sql monitoring tool via the SQL Developer and the Enterprise Manager

Using the Real-Time SQL Monitoring Tool Part 2
13:55

In this lecture, you will learn how to use the real-time sql monitoring tool via the SQL Developer and the Enterprise Manager

Using the Real-Time SQL Monitoring Tool Part 3
12:00
CODE: Using the Real-Time SQL Monitoring Tool
00:17

In this lecture, you will see how to generate the trace files with different methods and how to produce TKRPOF outputs from the trace files. And you will learn how to analyze the TKPROF outputs in details.

Using the Trace Files & TKPROF Utility - Part 1
15:56

In this lecture, you will see how to generate the trace files with different methods and how to produce TKRPOF outputs from the trace files. And you will learn how to analyze the TKPROF outputs in details.

Using the Trace Files & TKPROF Utility - Part 2
20:21

In this lecture, you will see how to generate the trace files with different methods and how to produce TKRPOF outputs from the trace files. And you will learn how to analyze the TKPROF outputs in details.

Using the Trace Files & TKPROF Utility - Part 3
10:22
CODE: Using the Trace Files & TKPROF Utility
00:15

In this lecture, you will see the select considerations for SQL Tuning aspect

Get What You Need Only
07:13
CODE: Get What You Need Only
00:04

This lecture explains how to make the optimizer use your indexes and increase the performance

Index Usage
16:24
CODE: Index Usage
00:37

This lecture explains the performance effects of the concatenations

Using Concatenation Operator
03:35
CODE: Using Concatenation Operator
00:05

This lecture explains the performance problems of using the arithmetic operators

Using Arithmetic Operators
03:15
CODE: Using Arithmetic Operators
00:06

This lecture explains what you should do with like conditions to have a better performance

Using Like Conditions
06:43
CODE: Using Like Conditions
00:13

This lecture explains why to avoid using functions on the indexed columns for SQL Tuning aspect.

Using Functions on the Indexed Columns
04:52
CODE: Using Functions on the Indexed Columns
00:11

This lecture explains how to solve the performance problems of NULL value searches

Handling NULL-Based Performance Problems
08:08
CODE: Handling NULL-Based Performance Problems
00:26

This lecture explains the performance effects of using Exists instead of IN clause

Using EXISTS instead of IN Clause
05:02

This lecture explains the performance comparisons of the TRUNCATE and DELETE commands, and some important things you should know for the TRUNCATE operations.

Using TRUNCATE instead of DELETE command
04:37
CODE: Using TRUNCATE instead of DELETE command
00:06

This lecture explains the performance effects of data type mismatches by sql tuning aspect.

Data Type Mismatch
05:35
CODE: Data Type Mismatch
00:13

This lecture explains how to tune the order by queries for performance

Tuning Ordered Queries
07:09

This lecture explains how to tune the order by queries for performance

CODE: Tuning Ordered Queries
00:14

In this lecture, students are going to learn how to improve the performance of the MIN & MAX queries by sql tuning aspect.

Retrieving the MIN & MAX Values
10:52

In this lecture, students are going to learn how to improve the performance of the MIN & MAX queries by sql tuning aspect.

CODE: Retrieving the MIN & MAX Values
00:15
UNION and UNION ALL Operators (Which one is faster?)
03:17
UNION and UNION ALL Operators (Which one is faster?)
00:07

This lecture explains the drawbacks of using the having clause in our SQL queries for performance aspect, and how to tune such queries.

Avoid Using the HAVING Clause!
05:22
CODE: Avoid Using the HAVING Clause!
00:11
Be Careful on Views!
Preview 10:22
CODE: Be Careful on Views!
00:38
Create Materialized Views
07:26
CODE: Create Materialized Views
00:28
Avoid Commit Too Much or Too Less!
04:36
Partition Pruning
06:10
CODE: Partition Pruning
00:07
Using BULK COLLECT
10:01
CODE: Using BULK COLLECT
01:06
Tuning the Join Order
06:49
CODE: Tuning the Join Order
00:19
Multitable DML Operations
07:19
CODE: Multitable DML Operations
00:51
Using Temporary Tables
07:18
CODE: Using Temporary Tables
00:35
Combining SQL Statements
04:55
CODE: Combining SQL Statements
00:19
Using "WITH" Clause
08:12

This lecture has the code that we used in the related lecture

CODE: Using WITH Clause
00:59
Using Analytical Functions
04:49
CODE: Using Analytical Functions
00:15

This quiz includes the questions about the SQL Tuning Techniques

SQL Tuning Techniques
5 questions