SQL Tuning
4.3 (301 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,561 students enrolled
Wishlisted Wishlist

Please confirm that you want to add SQL Tuning to your Wishlist.

Add to Wishlist

SQL Tuning

Learn how to formulate and tune SQL statements for optimal performance
4.3 (301 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
3,561 students enrolled
Created by Amarnath Reddy
Last updated 7/2015
English
Learn Fest Sale
Current price: $10 Original price: $100 Discount: 90% off
2 days left at this price!
30-Day Money-Back Guarantee
Includes:
  • 3 hours on-demand video
  • 1 Supplemental Resource
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Identify poorly performing SQL
  • Understand how the Query Optimizer makes decisions about how to access data
  • Define how optimizer statistics affect the performance of SQL
  • List the possible methods of accessing data, including different join methods
  • Modify a SQL statement to perform at its best
View Curriculum
Requirements
  • Database/SQL knowledge is a must to understand this course.
Description

Good SQL Developers are in high demand and demand 100k+ salary in the IT industry.

SQL performance tuning is an art to master - for all of us!!!

Many SQL Developers have tried to understand why a particualr SQL was running slow - including me - but have failed over and over again because we never tried to understand how SQL works? We were thinking it is database administrators Job!!!!

If we have to advance in our career and earn a good salary, we need these SQL tuning skills.

We know how it feels if someone talks about SQL Tuning and I dont want you to feel the same.

I took baby steps in introducing you to the optimizer and helping you write an effecient SQL.

This course takes a systematic approach to planning, analyzing, debugging and troubleshooting common query-related performance problems and will provide you with the skills necessary to write scalable, high performance SQL.

The SQL tuning methodology I used:

  • Identify a problem SQL statement
  • Determine how Oracle is executing SQL statement and why Oracle chose that way.
  • Investigate whether alternative executon plans would be better.
  • Update the SQL statement to push Oracle towards the better plan.


Trust me, I will catch your hand and take you step by step!!!

See you inside,

Amarnath Panyam

Who is the target audience?
  • Database developers, DBAs and SQL developers
Students Who Viewed This Course Also Viewed
Curriculum For This Course
Expand All 62 Lectures Collapse All 62 Lectures 03:08:27
+
SQL Tuning Introduction
4 Lectures 11:26
+
Optimizer and its Execution Plan
5 Lectures 12:51
Cost based Optimization
02:08

Gathering Statistics
03:13

Execution Plan
02:11

SQL Tuning Tools
02:22

Running Explain Plan
02:57

Optimizer statistics
3 questions
+
Accessing Tables
7 Lectures 16:12
What is my Address?
01:40

Types of Table Accesses
01:44

Table Access FULL
02:19

Table Access by ROWID
02:23

Index Unique Scan
03:03

Index Range Scan
03:17

Choosing between FULL and INDEX scan
01:46

Access Paths
3 questions
+
Explain Plan
10 Lectures 32:06
Execution Plan
01:12

What should you look for?
03:24

What is COST?
01:40

Rules of Execution Plan Tree
02:40

Traversing through the Tree
03:40

Reading Execution Plan
02:59

Execution Plan Example #1
02:15

Execution Plan Example #2
03:09

Execution Plan Example #3
04:34

Execution Plan Example #4
06:33
+
Simple Tuning Rules
3 Lectures 07:14
SELECT consideration
02:01

Using Table Aliases
02:16

Using WHERE rather than HAVING
02:57

Simple Rules
3 questions
+
Index Suppression
9 Lectures 23:33
Index Suppression reasons
01:48

Use of <> operator
03:55

Use of SUBSTR function
02:37

Use of Arithmetic operators
02:13

Use of TRUNC function on Date columns
02:16

Use of || operator
02:29

Comparing a character column to a numeric value
02:12

Use of IS NULL and IS NOT NULL
03:00

Function based Indexes
03:03

Index Suppression SQL
4 questions
+
Performance Improvement Considerations
7 Lectures 21:52
Use UNION instead of OR
02:35

Use UNION ALL instead of UNION
02:34

Minimize Table lookups in a Query
02:49

EXISTS vs IN
02:28

Use EXISTS instead of DISTINCT
02:54

Reading same table multiple times?
05:00

Use TRUNCATE instead of DELETE
03:32
+
Considerations while using SQL in programming
3 Lectures 06:03
Reduce the number of Trips to the database
01:52

Issue frequent COMMIT statements
01:33

Using BULK COLLECT
02:38
+
Join Methods
4 Lectures 12:18
Join Methods
01:13

Nested Loop Join
05:02

Hash Join
02:28

Sort Merge Join
03:35
+
HINTS to the database
3 Lectures 07:56
Why HINTS?
04:26

Forcing a specific Join Method
03:30

HINTS list
3 pages
2 More Sections
Frequently Bought Together
About the Instructor
Amarnath Reddy
4.5 Average rating
2,065 Reviews
16,300 Students
9 Courses
Oracle Architect

Amarnath has a Masters Degree in computer science with around 15 years of experience in the IT industry. He has successfully designed and implemented projects for clients in South Africa, India and USA. Currently his main focus is on Datawarehouse Architecture.

Amarnath is currently working for a fortune 500 company in USA and has pioneered the data architecture discipline. His Innovative and simple architectural designs have won accolades in the company.

Today, he brings leadership and technical expertise to enable customers to optimize and modernize their IT environments leveraging Business Intelligence tools, Big Data, social, mobile, and the Internet of Things.