Advanced Oracle SQL and PLSQL Tuning Training Course

Oracle Advanced SQL and PL/SQL Performance Tuning Training in Chennai teaches you Oracle SQL tuning and how to formulate and tune SQL statements for optimal performance.

Student will be able to tune advanced SQL queries including correlated subqueries and outer joins. The student will also become familiar with all of the major SQL tuning techniques for Oracle, including global parameter and statistics changes, adding missing indexes and adjusting optimizer statistics. The student will also see the internals of the Oracle optimizers, and see proven techniques for tuning Oracle SQL statements for optimal performance..

Advanced Oracle SQL Tuning Course Topics

Introduction to SQL Tuning

• Intro to the class
• History of SQL
• Evolution of SQL
• Exercise - declarative SQL

Internal processing of SQL statements

• Parsing SQL Syntax 
• SQL Semantic Analysis 
• Generating the execution plan 
• Using optimizer plan stability 
• Using the v$sql view  
• Using the v$sql_plan view 
• Exercise – Query the library cache 

Optimizer Statistics

• Purpose of statistics
• Types of statistics (table, column, system)
• Histogram statistics
• Dynamic sampling
• using dbms_stats
• Exporting/importing statistics
• Statistics management
• Exercise – gather system stats

Optimizer modes and goals

• Management issues with system-wide optimization
• Different modes of SQL optimization
• Bi-modal databases
• Rule-based optimization
• Cost-based optimization
• All rows optimization
• First_rows optimization
• Exercise – display and change optimizer_mode

Table joining internals

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

SQL Tuning and full-table scans

• Basics of file I/O
• Sequential reads vs. scattered reads
• When full scans are best
• RAM caching in the SGA
• Automating table caching
• Solid State Disks
• Tracking full-scans over time with AWR
• Exercise – Query v$sql

DAY 2 – Execution plan internals

Oracle parallel query and parallel DML

• Parallel and SMP processing
• Parallel query optimal degree
• Parallel query management (system, session, statement)
• Parallel DML
• Parallel parallelism
• Exercise: Run a parallel query

Exposing execution  plans 

• Evaluating Large-table, full-table scans 
• Index Usage Analysis 
• Reports on system-wide SQL execution 
• Exercise – run autotrace options
• 2-3 - Altering 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 – Optimizer costing models 

Tuning SQL with hints

• Optimizer directives
• Scope of hints (session-level, statement level)
• Broad hints (optimizer mode) vs. narrow hints
• Good Hints vs. bad hints
• Using the ORDERED and LEADING HINTS
• Forcing index usage
• Using hints in subqueries
• Exercise – change an execution  plan with a  hint

Oracle Index Optimization

• B-Tree indexes  
• Bitmap Indexes  
• Function-based Indexes 
• Clustered indexes 
• Index-only tables 
• Exercise – Create a function-based index

Tuning Oracle sorting

• When a sort is invoked (order by, group by, etc.)
• Sorting with indexes vs internal sorting
• Detecting disk sorts
• Sorted Hash Clusters
• Exercise: Force two sort methods

DAY 3 – Advanced SQL Tuning Topics

Monitoring SQL Performance

• Measuring end-to-end response time
• Measuring SQL throughput
• Using v$session_longops
• Optional Exercise – Run plan9i.sql

Oracle DML Tuning          

• DML Tuning is not for neophytes             
• Oracle DML tuning           
• Optimizing Oracle SQL insert performance           
• High Impact insert Tuning Techniques    
• Tips for batch inserts      
• Low-impact insert techniques (% and % faster)  
• Tuning insert speed with the nologging option   
• Reverse key indexes and insert performance     
• Blocksize and insert performance             
• Oracle Delete & Update Tuning      
• High impact update techniques (over % faster):  
• Low-impact techniques (between % and % faster)           
• Using bulk binds for PL/SQL updates       
• Oracle subquery factoring (with clause) for DML          
•      

Tuning with materialized views and temporary objects

• Materialized views
• Global temporary tables
• Using scalar subqueries (WITH clause)
• Simplifying complex SQL with temporary objects
• Exercise – Re-write complex query using temporary tables and WITH clause

Tuning subqueries 

• Subquery Tuning and SQL            
• Types of SQL Subqueries              
• Tuning Guidelines for Subqueries             
• Avoiding SQL Subqueries              
• Subqueries in the where Clause                
• In vs. exists Subqueries 
• Same Results, Different Syntax and Plans             
• Non-correlated subquery:           
• Outer Join:          
• Correlated Subquery:    
• Tuning Scalar Subqueries              
• Scalar Subquery Performance    
• Removing Subqueries for Fast SQL Performance               
• Internals of Temporary Tables   
• Correlated vs. Non-correlated Subqueries             
• Tuning Correlated Subqueries    
• Automatic Rewriting not exists Subqueries          
• Automatic Rewriting exists Subqueries  
• Rewriting Non-equality Correlated Subqueries  
• Rewriting exists Subqueries with the rank Function         
• Subquery Hint Tuning    
• Subquery Tuning with Index Hints            
• Tuning Subqueries With the push_subq Hint       
• Table Anti-Join Hints       
• The merge_aj Hint          
• SQL Tuning With the hash_aj Hint    
• Exercise: Tune an anti-join         

Troubleshooting bad SQL

• Troubleshooting Problem SQL    
• The Holistic Approach to SQL Tuning        
• Troubleshooting Oracle SQL Bugs              
• What is Bad SQL?             
• Identifying Problem SQL               
• Troubleshooting with v$sql_plan              
• SQL Troubleshooting with v$sql_plan_statistics 
• Finding indexing opportunities  
• Exercise: Find top SQL hogs

Advanced Optimizer Statistics

• Histograms
• Exporting/importing statistics
• Statistics management
• Exercise – Analyze schema and tables

Oracle Advanced PL/SQL Performance Tuning Training Course Topics

PLSQL Training Class 1 - Introducing PL/SQL

• Introduction
• What is PL/SQL and Why Should I use It?
• PL/SQL Architecture
• Overview of PL/SQL Elements
• Blocks
• Variables and Constants
• Using SQL in PL/SQL
• Branching and Conditional Control
• Looping Statements
• Goto
• Procedures, Functions and Packages
• Records
• Object Types
• Collections
• Associative Arrays (Index-By Tables)
• Nested Table Collections
• Varrays
• Collection Methods
• Triggers
• Error Handling
• My Ideal Environment

PLSQL Training Class 2 - Writing Efficient PL/SQL

• Introduction
• Bind Variables
• Using Bind Variables
• The cursor_sharing Parameter
• Dynamic Binds using Contexts
• SQL Injection
• Bulk Binds
• Using Rowids when Updating
• Short-circuit Evaluations and Ordering Logic
• Implicit vs. Explicit Cursors
• Declarations, Blocks, Functions and Procedures in Loops
• Duplication of Built-in String Functions
• Minimize Datatype Conversions
• The Trigger Compilation Myth
• Efficient Function Calls
• Using the NOCOPY Hint
• Using PLS_INTEGER and BINARY_INTEGER Types
• Using BINARY_FLOAT and BINARY_DOUBLE Types
• Native Compilation of PL/SQL
• Decoupling (cheating) for Performance

PLSQL Training Class 3 - Arrays and Bulk Binds

• Populating Collections Using Bulk Operations
• Bulk collect
• Bulk Collect from an Explicit Cursor
• Chunking Bulk Collections Using the LIMIT Clause
• Manually Limiting Bulk Collection Volumes
• Bulk Collection of DML Results
• FORALL
• Bulk INSERT Operations
• Bulk UPDATE Operations
• Bulk DELETE Operations
• Sparse Collections
• Host Arrays in Bulk Operations
• BULK_ROWCOUNT
• Handling Exceptions in Bulk Operations
• Unhandled Exceptions
• Handled Exceptions
• Bulk Operations that Complete
• Dynamic SQL and Bulk Operations

PLSQL Training Class 4 - Caching session Data

• Using Arrays for Lookup Tables
• Using Package Variables to Store Global Data
• Using Contexts to Store Global Data

PLSQL Training Class 5 - PL/SQL Memory Management

• Introduction
• Bind Variables and the Shared Pool
• The NOCOPY Hint and Memory Usage
• Bigger is Better for VARCHAR2 Variables
• Using Packages Correctly
• Pinning Packages in the Shared Pool
• Conclusion

PLSQL Training Class 6 - Cursor Variables and REF CURSOR Types

• Introduction
• Defining Cursor Variables
• Cursor Variables as Parameters
• Cursor Attributes and Cursor Variable Usage
• Host Variables as Cursor Variables
• Dynamic SQL and Variant Resultsets
• Restrictions When Using Cursor Variables
• Cursor Expressions

PLSQL Training Class 7 - Table Functions and Pipelining

• Introduction
• Pipelining Table Functions
• Parallel Enabled Table Functions
• Creating Transformation Pipelines
• Deterministic
• Miscellaneous Information

PLSQL Training Class 8 - Monitoring and Profiling PL/SQL

• Producing Performance Baselines
• Monitoring Specific Code
• Code Instrumentation (application tracing)
• The DSP Package
• dbms_application_info
• dbms_session
• dbms_system
• dbms_profiler
• dbms_trace
• SQL trace, trcsess and tkprof
• Generating SQL Trace Files
• trcsess
• tkprof
• Trace Example
• Execution Plans
• plan_table
• autotrace
• Explain Plan
• utlxpls.sql
• dbms_xplan
• Identifying the Impact of Code at the Database Level
• Dynamic Performance Views (V$)
• sessions.sql
• top_sessions.sql
• top_sql.sql
• longops.sql
• session_waits.sql
• session_events_by_sid.sql and system_events.sql
• session_stats.sql and system_stats.sql
• session_io.sql
• open_cursors_by_sid.sql
• locked_objects.sql
• STATSPACK
• Automatic Workload Repository (AWR)
• ADDM
• Using Oracle Enterprise Manager