SQL Tuning Premium class

Amarnath Panyam

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
62 Videos (3h 9m)
    • Why Sql Tuning

      3:13
    • Prerequisites for SQL Tuning

      2:22
    • SQL Processing

      4:07
    • Soft parse vs Hard parse

      1:44
    • Cost based Optimization

      2:08
    • Gathering Statistics

      3:13
    • Execution Plan

      2:11
    • SQL Tuning Tools

      2:22
    • Running Explain Plan

      2:57
    • What is my Address

      1:40
    • Types of Table Accesses

      1:44
    • TABLE ACCESS FULL

      2:19
    • TABLE ACCESS BY ROWID

      2:23
    • INDEX UNIQUE SCAN

      3:03
    • INDEX RANGE SCAN

      3:17
    • Choosing between FULL and INDEX scan

      1:46
    • Execution Plan

      1:12
    • What should you look for

      3:24
    • What is COST

      1:40
    • Rules of Execution Plan Tree

      2:40
    • Traversing through the tree

      3:40
    • Reading Execution Plan

      2:59
    • Execution Plan Example 1

      2:15
    • Execution Plan Example 2

      3:09
    • Execution Plan Example 3

      4:34
    • Execution Plan Example 4

      6:33
    • Select consideration

      2:01
    • Use Table Aliases

      2:16
    • Use Where rather than HAVING

      2:57
    • Index suppression reasons

      1:48
    • Use of <> operator

      3:55
    • Use of SUBSTR function

      2:37
    • Use of Arithmetic operators

      2:13
    • Use of TRUNC function on Date columns

      2:16
    • Use of || operator

      2:29
    • Comparing a character column to a numeric value

      2:12
    • Use of IS NULL and IS NOT NULL

      3:00
    • Function based Index

      3:03
    • Use UNION instead of OR

      2:35
    • Use UNION ALL instead of UNION

      2:34
    • Minimize table lookups

      2:49
    • EXISTS vs IN

      2:28
    • Use EXISTS instead of DISTINCT

      2:53
    • Reading same table multiple times

      5:00
    • Use Truncate instead of Delete

      3:32
    • BIND variables

      3:24
    • Reduce the number of trips to the database

      1:52
    • Issue Frequent COMMIT statements

      1:33
    • BULK collect

      2:38
    • Join Methods

      1:13
    • Nested Loop Join

      5:02
    • HASH join

      2:28
    • SORT MERGE join

      3:35
    • Why HINTS?

      4:26
    • Forcing joining method

      3:30
    • Invalid Optimizer statisics

      5:10
    • Checking SQL statements which are performing BAD

      4:58
    • Effective Schema Design

      4:22
    • Seperate tablespace for Data and Index

      3:25
    • Index Orgazined tables

      4:30
    • Partitioned Tables

      5:32
    • Bitmap Indexes

      5:59

About This Class

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

17

Students

--

Projects

0

Reviews (0)

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.