Sql Performance Tuning Masterclass

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

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

What to Know Before Starting the Course? Preview 06:20

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

Do You Have a Running Database in Your PC? Preview 00:30

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

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

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

First part of the architecture overview lecture

Oracle Database Architecture Overview (Part 2) Preview 06:04

Second part of the architecture overview lecture

Database Data Blocks in Detail Preview 07:57

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

What is PGA? Preview 05:32

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 Shared Pool? Preview 06:31

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

What is Buffer Cache? Preview 05:24

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 Redo Log Buffer? Preview 04:18

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

What is Undo? Preview 03:49

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

How a DML is processed and committed Preview 04:28

Automatic Memory Management Preview 02:06

Oracle Database Storage Architecture Preview 03:57

Logical and Physical Database Structure Preview 06:13

Quiz - Database Architecture

This quiz has questions about Oracle Database Architecture

When to Tune? Preview 07:40

What is a Bad SQL? Preview 05:08

Effective Schema Design Preview 08:42

Table Partitioning Preview 07:15

How an SQL Statement is Processed? Preview 09:32

Why do we need the Optimizer? Preview 05:41

Optimizer Overview Preview 03:25

Query Transformer Preview 08:44

Selectivity & Cardinality Preview 08:02

What is "cost" in detail? Preview 04:51

Plan Generator Preview 03:56

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

Row Source Generator Preview 03:38

SQL Tuning Principles and Strategies Preview 08:10

Query Analysis Strategy Preview 12:58

SQL Tuning Basics Assessment Test

This assessment test will evaluate how much you learned.

Execution Plan and Explain Plan in Details Preview 07:24

Generating Statistics (Part 1) Preview 06:16

Generating Statistics (Part 2) Preview 07:15

Generating Statistics (Part 3) Preview 08:50

Generating Statistics (Code Samples) Preview 00:20

Generating Execution Plan Preview 12:06

Generating Execution Plan (Code Samples) Preview 00:09

Autotrace Preview 12:38

Autotrace (Code Samples) Preview 00:09

V$SQL_PLAN View Preview 11:11

V$SQL_PLAN (Code Samples) Preview 00:23

Reading the Execution Plans (Part 1) Preview 13:12

Reading the Execution Plans (Part 2) Preview 10:29

Reading the Execution Plans (Code Samples) Preview 00:07

Analyzing the Execution Plans Preview 08:18

Analyzing the Execution Plans (Code Samples) Preview 00:14

Execution Plans & Statistics

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

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

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

Types of Table and Index Access Paths Preview 11:59

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

Table Access Full Preview 08:35

Table Access Full (Code Samples) Preview 00:11

Table Access by ROWID Preview 06:18

Table Access by ROWID (Code Samples) Preview 00:07

Index Unique Scan Preview 04:48

Index Range Scan Preview 10:40

Index Range Scan (Code Samples) Preview 00:30

Index Full Scan Preview 09:34

Index Full Scan (Code Samples) Preview 01:00

Index Fast Full Scan Preview 06:37

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

Index Fast Full Scan (Code Samples) Preview 00:29

Index Skip Scan Preview 14:14

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

Index Skip Scan (Code Samples) Preview 00:25

Index Join Scan Preview 05:37

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

Index Join Scan (Code Samples) Preview 00:12

Table & Index Access Paths

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

What are Hints and Why to Use Them? Preview 04:04

How to use Hints Preview 15:21

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

How to use Hints (Code Samples) Preview 01:21

List of Some Useful Hints Preview 00:08

Using Hints

Test yourself about using the hints for performance

Join Methods Overview Preview 05:10

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

Nested Loop Joins Preview 12:09

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

Nested Loop Join (Code Samples) Preview 00:31

Sort Merge Joins Preview 10:19

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

Sort Merge Join (Code Samples) Preview 00:24

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

Hash Joins Preview 11:08

This lecture explains the hash joins for SQL Performance Tuning

CODE: Hash Joins Preview 00:09

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

Cartesian Joins Preview 06:28

This lecture explains the cartesian joins for SQL Tuning Aspect

CODE: Cartesian Joins Preview 00:03

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

Join Types Overview Preview 03:00

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

Equijoins & Nonequijoins Preview 03:36

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

CODE: Equijoins & Nonequijoins Preview 00:06

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

Outer Joins Preview 11:16

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

CODE: Outer Joins Preview 00:31

Semijoins Preview 06:00

CODE: Semijoins Preview 00:10

Antijoins Preview 03:26

This lecture explains the antijoins for performance tuning aspect

CODE: Antijoins Preview 00:19

Join Operations

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

Result Cache Operator Preview 11:23

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

CODE: Result Cache Operator Preview 00:18

View Operator Preview 07:29

CODE: View Operator Preview 00:37

Clusters Preview 14:31

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

CODE: Clusters Preview 00:31

Sort Operators Preview 07:07

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

CODE: Sort Operators Preview 00:10

INLIST Operator Preview 04:25

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

CODE: INLIST Operator Preview 00:17

Count Stopkey Operator Preview 03:34

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

CODE: Count Stopkey Operator Preview 00:06

First Row Operator Preview 05:15

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

CODE: First Row Operator Preview 00:08

Filter Operator Preview 01:41

This lecture explains the filter predicate on the explain plans

CODE: Filter Operator Preview 00:02

Concatenation Operator Preview 03:06

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

CODE: Concatenation Operator Preview 00:04

UNION Operators Preview 02:54

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

CODE: Union Operators Preview 00:07

Intersect Operator Preview 05:20

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

CODE: Intersect Operator Preview 00:18

Minus Operator Preview 01:49

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

CODE: Minus Operator Preview 00:11

Other Optimizer Operators

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

How to find a performance problem and its tuning solution? Preview 15:05

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

Ways of Getting the Execution Plan and the Statistics Preview 16:27

Using the Real-Time SQL Monitoring Tool Part 1 Preview 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 Preview 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 Preview 12:00

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

CODE: Using the Real-Time SQL Monitoring Tool Preview 00:17

Using the Trace Files & TKPROF Utility - Part 1 Preview 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 Preview 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 Preview 10:22

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.

CODE: Using the Trace Files & TKPROF Utility Preview 00:15

Get What You Need Only Preview 07:13

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

CODE: Get What You Need Only Preview 00:04

Index Usage Preview 16:24

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

CODE: Index Usage Preview 00:37

Using Concatenation Operator Preview 03:35

This lecture explains the performance effects of the concatenations

CODE: Using Concatenation Operator Preview 00:05

Using Arithmetic Operators Preview 03:15

This lecture explains the performance problems of using the arithmetic operators

CODE: Using Arithmetic Operators Preview 00:06

Using Like Conditions Preview 06:43

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

CODE: Using Like Conditions Preview 00:13

Using Functions on the Indexed Columns Preview 04:52

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

CODE: Using Functions on the Indexed Columns Preview 00:11

Handling NULL-Based Performance Problems Preview 08:08

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

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

Using EXISTS instead of IN Clause Preview 05:02

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

Using TRUNCATE instead of DELETE command Preview 04:37

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

CODE: Using TRUNCATE instead of DELETE command Preview 00:06

Data Type Mismatch Preview 05:35

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

CODE: Data Type Mismatch Preview 00:13

Tuning Ordered Queries Preview 07:09

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

CODE: Tuning Ordered Queries Preview 00:14

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

Retrieving the MIN & MAX Values Preview 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 Preview 00:15

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

UNION and UNION ALL Operators (Which one is faster?) Preview 03:17

UNION and UNION ALL Operators (Which one is faster?) Preview 00:07

Avoid Using the HAVING Clause! Preview 05:22

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

CODE: Avoid Using the HAVING Clause! Preview 00:11

Be Careful on Views! Preview 10:22

CODE: Be Careful on Views! Preview 00:38

Create Materialized Views Preview 07:26

CODE: Create Materialized Views Preview 00:28

Avoid Commit Too Much or Too Less! Preview 04:36

Partition Pruning Preview 06:10

CODE: Partition Pruning Preview 00:07

Using BULK COLLECT Preview 10:01

CODE: Using BULK COLLECT Preview 01:06

Tuning the Join Order Preview 06:49

CODE: Tuning the Join Order Preview 00:19

Multitable DML Operations Preview 07:19

CODE: Multitable DML Operations Preview 00:51

Using Temporary Tables Preview 07:18

CODE: Using Temporary Tables Preview 00:35

Combining SQL Statements Preview 04:55

CODE: Combining SQL Statements Preview 00:19

Using "WITH" Clause Preview 08:12

CODE: Using WITH Clause Preview 00:59

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

Using Analytical Functions Preview 04:49

CODE: Using Analytical Functions Preview 00:15

SQL Tuning Techniques

This quiz includes the questions about the SQL Tuning Techniques

Why Indexing is Important? Preview 05:27

In this lecture, you will have a solid understanding of why indexing is very important in databases.

Index Selectivity & Cardinality Preview 05:31

B-Tree Indexes in Details Preview 12:13

In this lecture, you're going to learn Oracle B-Tree Indexes in details.

CODE: B-Tree Indexes in Details Preview 00:27

Bitmap Indexes in Details Preview 19:28

CODE: Bitmap Indexes in Details Preview 00:34

Bitmap Operations Preview 07:21

In this lecture, we'll have a look at the bitmap operations.

Composite Indexes and Order of Indexed Columns Preview 10:07

CODE: Composite Indexes and Order of Indexed Columns Preview 00:17

Covering Indexes Preview 08:00

CODE: Covering Indexes Preview 00:12

Reverse Key Indexes Preview 03:37

Bitmap Join Indexes Preview 09:53

This lecture explains the performance effects of the bitmap join indexes

CODE: Bitmap Join Indexes Preview 00:29

Combining Bitmap Indexes Preview 08:31

This lecture explains the important of combining the bitmap indexes to have a better performance

CODE: Combining Bitmap Indexes Preview 00:25

Function-Based Indexes Preview 09:19

This lecture explains the performance effects of using functions in your queries and the way to solve these problems

CODE: Function-Based Indexes Preview 00:20

Index-Organized Tables Preview 16:47

In this lecture, you'll be learning about Index Organized Tables (IOT) in Oracle.

CODE: Index-Organized Tables Preview 00:29

In this lecture, you'll get the source code of the Index Organized Tables (IOT) lectures.

Cluster Indexes Preview 08:30

This lecture explains how to create indexes for the Clusters to improve the SQL performance

CODE: Cluster Indexes Preview 00:31

Invisible Indexes Preview 07:31

In this lecture, you will learn how to use the invisible indexes in SQL Tuning aspect

CODE: Invisible Indexes Preview 00:09

Index Key Compression- Part 1 Preview 05:23

This lecture explains the index key compression term in details, for the SQL performance aspect

Index Key Compression- Part 2 Preview 11:13

This lecture explains the index key compression term in details, for the SQL performance aspect

CODE: Index Key Compression Preview 00:26

Full-Text Searches Preview 20:20

CODE: Full-Text Search Indexes Preview 00:34

Tuning Star Queries Preview 07:17

This lecture explains the performance effect of joining the tables and how to improve the performance of star queries

CODE: Tuning Star Queries Preview 00:35

Using Bind Variables Preview 08:16

This lecture explains the benefits of using the bind variables for SQL Tuning aspect

CODE: Using Bind Variables Preview 00:40

Beware of Bind Variable Peeking Preview 05:53

This lecture explains what to be aware of bind variable peeking not to decrease the performance

CODE: Beware of Bind Variable Peeking Preview 00:16

Cursor Sharing Preview 14:14

This lecture explains how the Oracle database share the cursors to use the same execution plans for improving the SQL performance

CODE: Cursor Sharing Preview 00:42

Adaptive Cursor Sharing Preview 16:31

This lecture explains how the Oracle Database shares the cursor in an intelligent way

CODE: Adaptive Cursor Sharing Preview 00:27

Adaptive Plans Preview 12:43

This lecture explains what is the meaning of "This plan is adaptive" in your execution plans

CODE: Adaptive Plans Preview 00:12

Dynamic Statistics (Dynamic Sampling) Preview 16:07

This lecture explains how to sample dynamic statistics for SQL Tuning aspect to improve the performance of your queries

CODE: Dynamic Statistics (Dynamic Sampling) Preview 00:22

About the Database Installation Preview 03:11

Option 1: Having the Database with the Oracle VirtualBox Software Preview 16:29

If you want to have a completely working database without any installation, just use our virtualbox option

Option 1: How to Install the Virtual Box on Mac OS X? Preview 01:51

This lecture explains how to install the Virtual Box software on a Mac OS.

Option 2: Oracle Database 12c Installation into Your Computer Preview 09:20

If you want to have the latest version of the database, follow these steps

Option 2: How to Unlock the HR Schema in the Oracle Database 12c? Preview 01:36

This lecture demonstrates how to unlock HR schema in the Oracle Database.

Option 2: Configuring and Using Oracle SQL Developer for Oracle Database 12c Preview 10:12

If you installed the database directly into your computer, you need to make some configurations before starting

Option 2: Installing SH Schema on Oracle Database Preview 06:50

This lecture explains how to install SH schema on the Oracle Database step by step.

Extra: 19c Installation Preview 00:56

Here you will find how to download, install and configure the Oracle Database 19c

Option 2: What is Pluggable Database? Preview 03:13

In this lecture, you're going to learn what the pluggable database (multitenant architecture) is in Oracle Database.

Lesson keywords: "multitenant architecture", "oracle pluggable database"

Option 2: Downloading and Installing the Oracle Database Preview 18:18

In this lecture, students are going to learn the oracle database 19c installation.

Lecture keywords: "oracle", "oracle sql", "oracle database", "oracle database installation", "oracle database 19c installation".

Option 2: Unlocking the HR Schema Preview 07:34

In this lecture, our students are going to learn how to unlock the HR schema to practice throughout the course.

Option 2: Configuring and Using Oracle SQL Developer Preview 22:14

This lecture covers how to download and configure Oracle SQL Developer software for the first use and how to connect to the Oracle Database 19c.

Bonus Lecture Preview 00:25