Course Highlights
  • 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!
Curriculum

3 Topics
SQL & Performance Tuning Course Overview
UDEMY 101: How to Use Udemy? +Some Useful Tips (Do not Skip)
Welcome Gift! + Course Document

2 Topics
What is SQL Tuning and Why to Do That?
What to Know Before Starting the Course?

15 Topics
Do You Have a Running Database in Your PC?
Why to know the Oracle Database Architecture and how much to know?
Oracle Database Architecture Overview (Part 1)
Oracle Database Architecture Overview (Part 2)
Database Data Blocks in Detail
What is PGA?
What is Shared Pool?
What is Buffer Cache?
What is Redo Log Buffer?
What is Undo?
How a DML is processed and committed
Automatic Memory Management
Oracle Database Storage Architecture
Logical and Physical Database Structure
Quiz - Database Architecture

15 Topics
When to Tune?
What is a Bad SQL?
Effective Schema Design
Table Partitioning
How an SQL Statement is Processed?
Why do we need the Optimizer?
Optimizer Overview
Query Transformer
Selectivity & Cardinality
What is "cost" in detail?
Plan Generator
Row Source Generator
SQL Tuning Principles and Strategies
Query Analysis Strategy
SQL Tuning Basics Assessment Test

17 Topics
Execution Plan and Explain Plan in Details
Generating Statistics (Part 1)
Generating Statistics (Part 2)
Generating Statistics (Part 3)
Generating Statistics (Code Samples)
Generating Execution Plan
Generating Execution Plan (Code Samples)
Autotrace
Autotrace (Code Samples)
V$SQL_PLAN View
V$SQL_PLAN (Code Samples)
Reading the Execution Plans (Part 1)
Reading the Execution Plans (Part 2)
Reading the Execution Plans (Code Samples)
Analyzing the Execution Plans
Analyzing the Execution Plans (Code Samples)
Execution Plans & Statistics

18 Topics
What are Indexes and How They work in details?
Types of Table and Index Access Paths
Table Access Full
Table Access Full (Code Samples)
Table Access by ROWID
Table Access by ROWID (Code Samples)
Index Unique Scan
Index Range Scan
Index Range Scan (Code Samples)
Index Full Scan
Index Full Scan (Code Samples)
Index Fast Full Scan
Index Fast Full Scan (Code Samples)
Index Skip Scan
Index Skip Scan (Code Samples)
Index Join Scan
Index Join Scan (Code Samples)
Table & Index Access Paths

5 Topics
What are Hints and Why to Use Them?
How to use Hints
How to use Hints (Code Samples)
List of Some Useful Hints
Using Hints

19 Topics
Join Methods Overview
Nested Loop Joins
Nested Loop Join (Code Samples)
Sort Merge Joins
Sort Merge Join (Code Samples)
Hash Joins
CODE: Hash Joins
Cartesian Joins
CODE: Cartesian Joins
Join Types Overview
Equijoins & Nonequijoins
CODE: Equijoins & Nonequijoins
Outer Joins
CODE: Outer Joins
Semijoins
CODE: Semijoins
Antijoins
CODE: Antijoins
Join Operations

25 Topics
Result Cache Operator
CODE: Result Cache Operator
View Operator
CODE: View Operator
Clusters
CODE: Clusters
Sort Operators
CODE: Sort Operators
INLIST Operator
CODE: INLIST Operator
Count Stopkey Operator
CODE: Count Stopkey Operator
First Row Operator
CODE: First Row Operator
Filter Operator
CODE: Filter Operator
Concatenation Operator
CODE: Concatenation Operator
UNION Operators
CODE: Union Operators
Intersect Operator
CODE: Intersect Operator
Minus Operator
CODE: Minus Operator
Other Optimizer Operators

59 Topics
How to find a performance problem and its tuning solution?
Ways of Getting the Execution Plan and the Statistics
Using the Real-Time SQL Monitoring Tool Part 1
Using the Real-Time SQL Monitoring Tool Part 2
Using the Real-Time SQL Monitoring Tool Part 3
CODE: Using the Real-Time SQL Monitoring Tool
Using the Trace Files & TKPROF Utility - Part 1
Using the Trace Files & TKPROF Utility - Part 2
Using the Trace Files & TKPROF Utility - Part 3
CODE: Using the Trace Files & TKPROF Utility
Get What You Need Only
CODE: Get What You Need Only
Index Usage
CODE: Index Usage
Using Concatenation Operator
CODE: Using Concatenation Operator
Using Arithmetic Operators
CODE: Using Arithmetic Operators
Using Like Conditions
CODE: Using Like Conditions
Using Functions on the Indexed Columns
CODE: Using Functions on the Indexed Columns
Handling NULL-Based Performance Problems
CODE: Handling NULL-Based Performance Problems
Using EXISTS instead of IN Clause
Using TRUNCATE instead of DELETE command
CODE: Using TRUNCATE instead of DELETE command
Data Type Mismatch
CODE: Data Type Mismatch
Tuning Ordered Queries
CODE: Tuning Ordered Queries
Retrieving the MIN & MAX Values
CODE: Retrieving the MIN & MAX Values
UNION and UNION ALL Operators (Which one is faster?)
UNION and UNION ALL Operators (Which one is faster?)
Avoid Using the HAVING Clause!
CODE: Avoid Using the HAVING Clause!
Be Careful on Views!
CODE: Be Careful on Views!
Create Materialized Views
CODE: Create Materialized Views
Avoid Commit Too Much or Too Less!
Partition Pruning
CODE: Partition Pruning
Using BULK COLLECT
CODE: Using BULK COLLECT
Tuning the Join Order
CODE: Tuning the Join Order
Multitable DML Operations
CODE: Multitable DML Operations
Using Temporary Tables
CODE: Using Temporary Tables
Combining SQL Statements
CODE: Combining SQL Statements
Using "WITH" Clause
CODE: Using WITH Clause
Using Analytical Functions
CODE: Using Analytical Functions
SQL Tuning Techniques

29 Topics
Why Indexing is Important?
Index Selectivity & Cardinality
B-Tree Indexes in Details
CODE: B-Tree Indexes in Details
Bitmap Indexes in Details
CODE: Bitmap Indexes in Details
Bitmap Operations
Composite Indexes and Order of Indexed Columns
CODE: Composite Indexes and Order of Indexed Columns
Covering Indexes
CODE: Covering Indexes
Reverse Key Indexes
Bitmap Join Indexes
CODE: Bitmap Join Indexes
Combining Bitmap Indexes
CODE: Combining Bitmap Indexes
Function-Based Indexes
CODE: Function-Based Indexes
Index-Organized Tables
CODE: Index-Organized Tables
Cluster Indexes
CODE: Cluster Indexes
Invisible Indexes
CODE: Invisible Indexes
Index Key Compression- Part 1
Index Key Compression- Part 2
CODE: Index Key Compression
Full-Text Searches
CODE: Full-Text Search Indexes

14 Topics
Tuning Star Queries
CODE: Tuning Star Queries
Using Bind Variables
CODE: Using Bind Variables
Beware of Bind Variable Peeking
CODE: Beware of Bind Variable Peeking
Cursor Sharing
CODE: Cursor Sharing
Adaptive Cursor Sharing
CODE: Adaptive Cursor Sharing
Adaptive Plans
CODE: Adaptive Plans
Dynamic Statistics (Dynamic Sampling)
CODE: Dynamic Statistics (Dynamic Sampling)

18 Topics
About the Database Installation
The Ways to Get a Database
Option 1: Installing VMware and VirtualBox on Windows
Option 1: How to Install the Virtual Box on Mac OS X?
Option 1: Downloading Virtual Machine Image (19c)
Option 1: Downloading Virtual Machine Image(If the previous lecture didn't work)
Option 1: Configuring the VMware Virtualization Software
Option 1: Configuring the Oracle VirtualBox Virtualization Software
Option 1: Configuring SQL Developer inside of the Virtual Machine
Option 2: What is Pluggable Database?
Option 2: Downloading and Installing the Oracle Database
Option 2: Unlocking the HR Schema
Option 2: Configuring and Using Oracle SQL Developer
Option 2: Installing Sample Schemas in Oracle Database
Extra: 12c Installation
Option 2: How to Unlock the HR Schema in the Oracle Database 12c?
Option 2: Oracle Database 12c Installation into Your Computer
Option 2: Configuring and Using Oracle SQL Developer for Oracle Database 12c

1 Topic
Bonus Lecture

  Write a Review

Oracle SQL Performance Tuning Masterclass (2024)

Go to Paid Course