SQL Tuning

Learn how to formulate and tune SQL statements for optimal performance
4.3 (160 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.
2,684 students enrolled
Instructed by Amarnath Reddy IT & Software / Other
$100
Take This Course
  • Lectures 62
  • Contents Video: 3 hours
    Other: 3 mins
  • Skill Level Intermediate Level
  • Languages English
  • Includes Lifetime access
    30 day money back guarantee!
    Available on iOS and Android
    Certificate of Completion
Wishlisted Wishlist

How taking a course works

Discover

Find online courses made by experts from around the world.

Learn

Take your courses with you and learn anywhere, anytime.

Master

Learn and practice real-world skills and achieve your goals.

About This Course

Published 3/2015 English

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

What are the requirements?

  • Database/SQL knowledge is a must to understand this course.

What am I going to get from this course?

  • 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

What is the target audience?

  • Database developers, DBAs and SQL developers

What you get with this course?

Not for you? No problem.
30 day money back guarantee.

Forever yours.
Lifetime access.

Learn on the go.
Desktop, iOS and Android.

Get rewarded.
Certificate of completion.

Curriculum

Section 1: SQL Tuning Introduction
Why SQL Tuning?
Preview
03:13
Prerequisites for SQL Tuning
Preview
02:22
SQL Processing
Preview
04:07
SOFT parse vs HARD parse
Preview
01:44
2 questions

Which Parse is better?

Section 2: Optimizer and its Execution Plan
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
Section 3: Accessing Tables
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
Section 4: Explain Plan
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
Section 5: Simple Tuning Rules
SELECT consideration
02:01
Using Table Aliases
02:16
Using WHERE rather than HAVING
02:57
Simple Rules
3 questions
Section 6: Index Suppression
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
Section 7: Performance Improvement Considerations
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
Section 8: Considerations while using SQL in programming
Reduce the number of Trips to the database
01:52
Issue frequent COMMIT statements
01:33
Using BULK COLLECT
02:38
Section 9: Join Methods
Join Methods
01:13
Nested Loop Join
05:02
Hash Join
02:28
Sort Merge Join
03:35
Section 10: HINTS to the database
Why HINTS?
04:26
Forcing a specific Join Method
03:30
HINTS list
3 pages
Section 11: House Keeping
Invalid Optimizer Statistics
05:10
Checking SQL statements which are performing BAD
04:58
Section 12: Design Considerations
Effective Schema Design
04:22
Separate Tablespace for Data and Index
03:25
Index Organized Tables
04:30
Partitioned Tables
05:32
Bitmap Indexes
05:59

Students Who Viewed This Course Also Viewed

  • Loading
  • Loading
  • Loading

Instructor Biography

Amarnath Reddy, 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.

Ready to start learning?
Take This Course