Oracle SQL Query Tuning Training in Chennai

Oracle DBA Training in ChennaiOracle SQL Query Tuning Training in Chennai for Developers. Oracle sql tuning using explain plan, Hints, Indexes, Partitioning, oracle sqltxplain, TOAD. GREENS TECHNOLOGY in ADYAR offers Performance Training in Chennai for SQL and PLSQL Developers with in-depth Practical approach towards Using Bind Variables, TK*Prof, DBMS_Profiler, DBMS_Trace, Analyzing SQL with SQL Tuning Advisor and AWR Report.. Advanced Oracle SQL Tuning Training Course for the Developer is exclusively designed with Basics through Advanced Concepts. Study Material, Certification and Interview Guidance are provided during the course. All our training sessions are Completely Practical.

This advanced Oracle SQL tuning is an intensive customized course designed to provide Oracle developers, programmers or analysts with an in-depth understanding of Oracle SQL tuning. Students will learn proven methods for optimizing their SQL and understand how to interpret execution plans for optimal performance. Burleson Consulting instructors offer decades of real world DBA experience in Oracle features, and they will share their secrets in this intense Oracle SQL tuning training.

By the end of this course the student will be able to use advanced SQL techniques including query-requiting, using temporary tables, using hints and changing optimizer setting to achieve faster and more efficient SQL performance. correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including the internals of the Oracle optimizers, materialized views, and techniques for tuning Oracle SQL statements for optimal performance. The overall goal for the class will be to leave the Oracle developer, programmer or analyst with the skills to expose a SQL execution plan, evaluate a plan for optimal execution and how to modify an execution plan for faster performance and throughput.

Oracle SQL Tuning Training and Certification

  • 1Z0-117 Oracle Database 11g Release 2: SQL Tuning

Oracle SQL Query Tuning Training Course Content

Introduction to SQL Tuning

  • Find a workaround to enhance performance
  • Analyze a poorly written SQL statement
  • Create a function based index
  • Redesign a simple table
  • Rewrite the SQL statement

Using SQL Trace Facility and TKPROF

  • Explore a trace file to understand the optimizer’s decisions

Understand Basic Tuning Techniques

  • Rewrite queries for better performance
  • Rewrite applications for better performance
  • Utilize SQL Tuning Advisor using SQL Developer 3.0

Optomizer Fundamentals

  • Explore a trace file to understand the optimizer’s decisions

Understanding Serial Execution Plans

  • Use different techniques to extract execution plans
  • Use SQL monitoring

Optimizer: Table and Index Access Paths

  • Learn about using different access paths for better optimization

Optimizer: Join Operations

  • Use different access paths for better optimization
  • Examine and use the result cache

Other Optimizer Methods

  • Use different access paths for better optimization
  • Examine and use the result cache

Optimizer Statistics

  • Analyze and use system statistics
  • Use automatic statistics gathering

Using Bind Variables

  • Use adaptive cursor sharing and bind peeking
  • Use the CURSOR_SHARING initialization parameter

SQL Plan Management

  • Use SQL Plan Management

Inside the Oracle Optimizer

  • Job of the SQL optimizer
  • Parsing, semantic check, decision tree, generating plan
  • How the Oracle optimizer chooses the “best:” execution plan
  • The Oracle Optimizer SQL cache (library cache)

 Oracle optimization goals

  • The Oracle Optimizer: Rule Based Optimization
  • The Oracle Optimizer: Cost Based Optimization
  • The all_rows vs first_rows_n optimizer modes
  • Generating optimizer Statistics for tables and system

Table Join internals

  • Oracle Joins, Join Methods and Access Paths
  • Nested loops, hash joins, merge joins
  • The star transformation

   Evaluating full-table scans

  • Understanding multi-block reads (db_file_scattered_reads)
  • Threshold between full table scan and index range scan
  • Parallelizing full-table scans
  • When full table scans are evil
  • Using plan91.sql and plan10g.sql

Oracle Index Optimization

  • Oracle Index types
  • Index cardinality
  • SQL with multi-column indexes
  • SQL with bitmap indexes
  • Index unique scans vs. index range scans
  • Finding missing indexes

Oracle rowset sorting

  • When a sort is invoked (order by, group by, etc.)
  • Sorting with indexes vs internal sorting
  • Detecting disk sorts

Day 2: SQL Execution plan evaluation

2-1:  The Goals of SQL tuning

  • Evaluate large-table full-table scans
  • Evaluate table join order
  • Evaluate index access

2 - 2: Exposing the SQL execution plans

  • The autotrace command
  • Using the explain plan facility

Reading an execution plan

  • Evaluating the steps of an execution plan
  • The cost and cardinality columns of an execution plan
  • Adding RAM usage details to an execution plan

Tracing SQL execution plans:

  • Using TKPROF
  • Using the 10046 trace event

 Changing SQL: execution plans

  • Using optimizer directives (hints)
  • Changing plans with histograms
  • Changing plans with dynamic sampling

Freezing execution plans:

  • Freezing plans with multiple hints
  • Using optimizer plan stability
  • Using SQL profiles

1 – Changing SQL Execution Plans

  • Using hints                                                                           
  • Changing the system-wide optimizer mode                         
  • Changing optimizer mode for specific statements               
  • Re-writing SQL queries                                                       
  • Table join order evaluation                                                  
  • Using the ordered hint                                                         
  • Exercise – Add hints to queries

2 – Database-level SQL Tuning

  • Inside the library cache – SQL reusability                        
  • Avoiding SQL re-parsing - using host variables               
  • Cursor Sharing                                                                 
  • Optimizer Plan Stability a.k.a. stored outlines
  • 10g SQL Profiles
  • Parallel DML                                                                     
  • Parallel SQL and partitions                                               
  • SQL and the data warehouse                                             
  • Exercise – invoke parallel query

3a – SQL and Boolean expressions

  • Equality conditions                                                        
  • Using the LIKE, BETWEEN, EXISTS and NOT            
  • Using the decode and CASE operators                        
  • Exercise – Using Decode and CASE

3b – Using Built-in Functions (BIF’s)

  • Basics of BIFs                                                                  
  • Function-based indexing                                                  
  • Creating a custom BIF using PL/SQL
  • The effect of BIFs on SQL execution
  • Exercise – Create a custom BIF and use it in SQL

 4a – Aggregate Functions

  •             Grouping in several levels                                                   
  • Grouping and NULLS
  • CUBE and ROLLUP
  • Performance and grouping
  • Exercise – Using rollup and cube

4b – Tuning SQL subqueries

  • Correlated subqueries                                                        
  • Non-correlated subqueries                                                 
  • Replacing subqueries with joins                                                         
  • Exercise – Tune a complex query

2 - Optimizing SQL RAM Resources

  • PGA Overview
  • SQL Sorting
  • SQL hash joins
  • Super-sizing PGA regions                                                                      

5 – Oracle execution plan costing

  • The “best” execution plan (first_rows vs. all_rows)          
  • Rule-based optimization                                                  
  • Cost-based optimization                                                  
  • Gathering statistics                                                          
  • Using histograms                                                             
  • When the optimizers fail                                                   
  • Exercise – Change the optimizer mode

6 – Table joining techniques

  • Sort-merge joins                                                                  
  • Nested Loop joins                                                                
  • Hash joins                                                                             
  • STAR joins                                                                            
  • Bitmap joins                                                                          
  • Exercise – Change table join techniques & evaluate performance

DAY 3 – Advanced Oracle SQL tuning

  • 1 – Indexing techniques
  • B-Tree indexes                                                                     
  • Bitmap Indexes                                                                    
  • Function-based Indexes                                                      
  • Clustered indexes                                                                
  • Index-only tables                                                                   
  • Exercise – Create and use bitmap index

2 – Table structures and SQL Performance

  • Effect of PCTFREE & PCTUSED on DML                      
  • Table high-water mark                                                     
  • Table striping                                                                   
  • Table partitioning                                                             
  • Row-re-sequencing and Oracle SQL I/O performance     
  • Oracle9i Online table redefinition
  • Exercise – res-sequence table rows
  • 3 – Oracle SQL Tuning Techniques- Book Chapter 15, page 551-559

    • Overview of time-series SQL tuning
    • SQL execution metrics
    • Re-writing SQL syntax
    • Replace SQL with PL/SQL
    • Adding hints
    • 10g SQLAccess advisor - SQL profiles
    • 11g SQL Performance analyzer (SPA)

    4a - Time-series SQL Tuning

    • Predictive modeling for SQL performance
    • BSTAT-ESTAT, STATSPACK and AWR
    • Trending SQL operations (full scans hash joins, &c)
    • Holistic workload SQL tuning (11g SPA, Quest benchmark factory)

    5 – Finding Suboptimal SQL

    • Evaluating Large-table, full-table scans                          
    • Index Usage Analysis                                                      
    • Displaying reports on system-wide SQL execution         
    • Exercise – run access.sql  

    6 – Oracle SQL Tuning Silver Bullets

    • Materialized Views to pre-aggregate and pre-join          
    • Adding indexes                                                               
    • Exercise – Create a materialized view
    • Course wrap-up

    Optional SQL tuning topics:

    1 – Using object-oriented SQL

  • Nested Tables                                                                      
  • VARRAYS within tables                                                      
  • Using object ID’s in SQL                                                     
  • Using abstract data types (ADTs)                                      
  • Exercise – Create and use an ADT
  • Oracle Performance Tuning Training Course Highlights:

    We are committed to provide Complete Oracle Performance Tuning Realtime and Practical Trainings.

    • 1) Two days free trial - If candidate likes this course, these days are adjusted in his actual schedule.
    • 2) Live Project Exposure of Fortune companies.
    • 3) Training by Subject Matter experts from CMM Level 5 companies
    • 4) Running in two major financial cities of India – Chennai and Mumbai
    • 5) Worldwide online training of Oracle Performance Tuning professional and corporate classes at affordable fees.
    • 6) Our basic course worth more than the advanced course of other institutes/freelancers.
    • 7) Free Interview preparations.
    • 8) 100% free assistance for Oracle certifications.
    • 9) 100 % guarantee in succeeding the certification at affordable fees.
    • 10) Also provide online training to students of foreign countries.
    • Learn Oracle Performance Tuning training from the Best Oracle Institute in Chennai

    Oracle Training Course Syllabus

    This course is designed for all major roles on a DW/BI project, including Mainframe Developers, Software Testing Professional, Oracle SQL and PL/SQL Developers, project managers, business analysts, data modelers and database administrators, architects, and ETL or BI application designers/developers.

    Oracle DBA Advanced Topics Training course

    Oracle Training Duration

    • Regular classroom based training available for this course on Both Weekdays / Weekends
    • Fast Track (1-1): No of hours per day can be as per convenience of participant.
    • Weekend classroom classes are available for this course.

    Oracle Training Locations in Chennai

    Greens Technology Oracle Training Institute
    15 First Street Padmanabha Nagar, Adyar, Chennai
    Tel: +91- 89399 15577
          +91- 89399 25577
    Email: contact@greenstechnologys.com

    Greens Technology Trainers:

    Worked earlier in Oracle Corporation, IBM, Google, Verizon, CSC, Satyam etc.

    Oracle Training Reviews

    Greens Technology Reviews given by our students already completed the training with us. Please give your feedback as well if you are a student.

    for More Reviews please continue to, Oracle Reviews (Page 2) >>