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.
Course Outline
Methodology of Application Tuning
- Identifying the Problem
- Diagnosing the Cause
- Applying the Solution
The SQL Command Execution Process
- The Parsing and Cursor Sharing Process
- Adaptive Cursors
Analysis of the Query Execution Plan
- Ways to Obtain Hypothetical and Actual Query Plans (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace)
- Marking the Order of Algorithm Steps
- Interpreting the Contents of the Plan Tree
- Adaptive Plans
The Cost-Based Optimization Process and Controlling the Cost-Based Optimizer
- Properties of Cost-Based and Rule-Based Optimization
- Session and Instance Parameters
- Hints
- Query Plan Patterns (Outlines)
- Query Plan Management (Baselines)
- Patching Commands
- Profiles and SQL Tuning Advisor
Statistics and Histograms
- The Impact of Statistics and Histograms on Performance
- Methods for Collecting Statistics and Histograms
- Strategies for Counting and Estimating Statistics, Ad Hoc Sampling
- Managing Statistics: Locking, Copying, Editing, Automating Collection, Monitoring Changes
- Multicolumn Statistics, Expression-Based Statistics
- System and Dictionary Statistics
- Adaptive Statistics
Optimizing Full Scans through Proper Space Management
- When to Use a Full Scan
- Space Allocation in Blocks and Segments, High Water Mark, PCTFREE
- The Impact of DML Operations and Space Allocation Methods on Read Performance
- Data Loading via Conventional and Direct Paths
- Physical Data Reorganization, Truncation, Defragmentation, Reconstruction
Optimizing Full Scans through Physical Isolation of "Hot Data"
- Temporary Tables
- Partitioning
- Materialized Views
Optimizing Full Scans through Data Compression
- OLTP Compression
- OLAP Compression
Index Optimization for Reading
- The Concept of ROWID
- B-TREE Index Construction
- Comparing the Efficiency of Accessing Data via B-TREE Indexes and FULL SCAN
- The Impact of Indexes on DML Operations
- Strategies for Creating and Dropping Indexes
- "Good" and "Bad" Indexes, the Impact of Physical Data Distribution Entropy on Index Usage Costs
- Index Properties and Statistics
- Types of Reads: UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX
- Types of Indexes: Unique, Functional, Multicolumn, Reverse Key, Local/Global, Virtual, Private
- NULL Values in Indexes
- Index-Organized Tables (IOT)
- Bitmap and Join Indexes
Sorting Process Optimization
- In-Memory Sorting
- Index Sorting
- Linguistic Sorting
Join and Subquery Optimization
- Join Methods: MERGE, HASH, NESTED LOOP
- Joins in OLTP and OLAP Systems
- Star Joins
- Join Order
- Outer Joins
Performance Monitoring and Identifying Process Bottlenecks through SQLTrace
- Tracing a Database Session/Process
- Tracing an Application Session/User in a Database Connection Pooling Model
- The TkProf Tool, TrcSess
PL/SQL Code Performance
- Using Literals in SQL Commands
- Cursor Sharing Rules
- Adaptive Cursors - Proper Communication Between SQL and PL/SQL
- Cursors and Bulk Operations
- Built-In Functions in SQL
- Function Result Caching - Passing Parameters by Value/Reference
- Inlining Short Procedures at Compile Time
- Compiler Optimization Levels
- Native Compilation
- Other Aspects of PL/SQL Optimization
Requirements
Fluent use of SQL and PL/SQL. Practical experience working with Oracle or another relational database management system.
28 Hours
Testimonials (7)
The form of conducting and the substantive nature of the trainer
Lukasz - Sygnity
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
The instructor gave the opportunity to perform examples in his environment, but did not require it. Thanks to this, there was no problem that I couldn't keep up (during other trainings with other trainers, I sometimes couldn't keep up with more complicated issues)
Pawel - Sygnity
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
Practical knowledge of the instructor
Piotr - Sygnity
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
Method of translating the material and examples shown.
Marek Jakimowicz
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
Extensive knowledge based on the instructor's experience
Angelika
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
Widely experiments and curiosities from the instructor's experience to support theoretical knowledge.
Przemyslaw Piatek - SOFTLOG Sp. z o.o. Sp.k.
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
Large and practical knowledge backed up by examples from everyday work.
Marek Gregorczyk - Biuro Projektowania Systemow Cyfrowych sp. z o.o.
Course - Tuning SQL i PL/SQL dla programistów
Machine Translated
