Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Duration 28 hours
Course Outline
Performance Tuning Methodology
Database Architecture and Instance Components
- Server process architecture
- Memory architecture (SGA, PGA)
- Statement parsing and shared cursor management
- Data files, log files, and parameter file configuration
Command Execution Plan Analysis
- Projected plans (EXPLAIN PLAN, SQLPlus AutoTrace)
- Actual execution plans (V$ SQL_PLAN, XPlan, AWR reports)
Performance Monitoring and Bottleneck Identification
- Real-time instance status monitoring via system dictionary views
- Historical performance data analysis using dictionary views
- Application tracing (SQL Trace, tkprof, TReSess)
Optimization Process
- Cost-based optimization principles and governance
- Decision frameworks for optimization strategies
Controlling the Cost-Based Optimizer
- Session and instance-level parameters
- Optimization hints
- Query plan stability and transformation patterns
Statistics and Histograms
- Impact of statistics and histograms on query performance
- Methods for gathering statistics and histogram data
- Strategies for cardinality estimation and sampling
- Statistics management: lock, copy, edit, automated collection, and change monitoring
- Dynamic data sampling (temporary tables, complex predicates)
- Extended statistics for multi-column and expression-based metrics
- System-wide statistics management
Logical and Physical Database Structure
- Tablespace architecture
- Segment organization
- Extent management
- Block structure
Data Storage Techniques
- Physical table attributes and characteristics
- Temporary table utilization
- Indexed tables
- External tables
- Partitioned tables (range, list, hash, composite)
- Physical reorganization of table data
Materialized Views and Query Rewrite
Data Indexing Strategies
- B-Tree index construction
- Index properties and behavior
- Index types: unique, multi-column, function-based, reverse-key
- Index compression
- Index rebuilding and coalescing
- Virtual indexes
- Private and public schema indexes
- Bitmap indexes and bitmap join operations
Case Study: Full Table Scans
- Impact of table placement and block density on read performance
- Data loading methods: conventional vs. direct path
- Predicate order and evaluation efficiency
Case Study: Index Access Paths
- Index read methods (UNIQUE SCAN, RANGE SCAN, FULL SCAN, FAST FULL SCAN, MIN/MAX SCAN)
- Utilization of function-based indexes
- Index selectivity and clustering factor
- Multi-column indexes and skip scans
- Handling NULL values in indexes
- Index-organized tables (IOT)
- Impact of DML operations on index maintenance
Case Study: Sorting Operations
- In-memory sorting mechanisms
- Sort operations for index creation
- Linguistic sort considerations
- Effect of data entropy on sorting (clustering factor)
Case Study: Joins and Subqueries
- Join algorithms: MERGE, HASH, NESTED LOOP
- Join strategies in OLTP vs. OLAP environments
- Order of join operations
- Outer joins
- Anti-joins
- Semi-joins
- Simple subqueries
- Correlated subqueries
- Views and the WITH clause
Additional Cost-Based Optimizer Operations
- Buffer sort operations
- INLIST iterator
- View access paths
- Filter operations
- Count Stop Key
- Result cache utilization
Distributed Queries
- Query plan analysis for DBLINK usage
- Selecting leading tables in distributed queries
Parallel Processing
Requirements
- Proficiency in SQL fundamentals and familiarity with the Oracle database environment (completion of foundational training such as Native SQL for Programmers, preferably on Oracle 11g, is preferred)
- Practical experience in developing and maintaining Oracle applications
Testimonials (2)
1. I liked the trainer's style of presenting and the patience to explain. 2. I liked that the trainer answered our side questions, even the ones that took the discussion a bit farther from the presentation, which showed flexibility. 3. I liked that there was a practical lab, not just a theoretical part. 4. I liked that it was online.
Roxana - DB Global Technology
Course - Oracle 11g - Application Tuning - Workshop
Trainer expertise on SQL tuning
