.webp)
.svg)
Program overview
Online
On-site
Hybrid
Oracle Database Performance Tuning
Three days of evidence-led Oracle tuning for DBAs and developers who need slow databases explained rather than guessed at. Covers a repeatable tuning method, AWR, ADDM and ASH, wait events, the cost-based optimiser and execution plans, statistics, indexing, joins, SQL tuning and plan stability, memory and I/O tuning, and contention resolution. Delivered on Oracle 19c and 23ai.
Duration:
3 Days
Level:
Advanced
.avif)
1500+ users onboarded
Who will Benefit from this Training?
Training Objectives
Build a high-performing, job-ready tech team.
Personalise your team’s upskilling roadmap and design a befitting, hands-on training program with Uptut
Key training modules
Comprehensive, hands-on modules designed to take you from basics to advanced concepts
Download Curriculum
- A Method for Tuning
- Defining the problem in measurable terms before touching anything
- Time based analysis: where is the database actually spending time
- Scope: instance wide, session specific or single statement
- Establishing a baseline and proving improvement afterwards
- Diagnostics and Tuning Pack licensing and unlicensed alternatives
- Hands-on: scope and baseline a supplied performance complaint
- AWR, ADDM and Active Session History
- AWR snapshot mechanics, retention and report generation
- Reading an AWR report in the right order
- Top timed events, load profile and instance efficiency sections
- ADDM findings, recommendations and how much to trust them
- Active Session History for problems too short to appear in AWR
- Statspack as an alternative where packs are not licensed
- Hands-on: diagnose a workload from an AWR report alone
- Wait Event Analysis
- Wait event classes and what each one points at
- CPU versus wait time and interpreting the balance
- I/O related events and separating storage from access path problems
- Concurrency, configuration and cluster wait classes
- Idle events and avoiding false leads
- Hands-on: trace a session and attribute its time to specific causes
- The Optimizer and Execution Plans
- Cost based optimisation, cardinality and cost calculation
- Generating plans with EXPLAIN PLAN, DBMS_XPLAN and the cursor cache
- Estimated versus actual rows as the primary diagnostic signal
- Real time SQL Monitoring for long running statements
- Adaptive plans and adaptive statistics behaviour
- Hands-on: capture and interpret plans for a set of problem statements
- Statistics and Cardinality
- Table, column, index and system statistics
- Histograms, skew and when they help or mislead
- Extended statistics for column groups and expressions
- Automatic statistics gathering, staleness and manual intervention
- Dynamic sampling and its cost
- Hands-on: create a misestimation, watch the plan collapse and correct it
- Access Paths and Index Strategy
- Full table scan, index range scan, unique scan, skip scan and fast full scan
- B-tree, bitmap, function based and composite index design
- Clustering factor and why it decides index usefulness
- Index compression, invisible indexes and safe index testing
- Automatic Indexing behaviour and oversight
- Hands-on: redesign an index set and prove the plan and write cost changes
- Joins and Query Transformation
- Nested loops, hash join and sort merge join and their conditions
- Join order selection and the optimiser's search space
- Subquery unnesting, view merging and predicate pushing
- Star transformation and its data warehouse applications
- Diagnosing a bad join choice from plan evidence
- Hands-on: correct a poorly joined multi-table query
- SQL Tuning and Plan Stability
- SQL Tuning Advisor and SQL Access Advisor output
- SQL profiles and what they actually change
- SQL plan baselines for capturing and evolving known good plans
- Hints: appropriate use, common misuse and long term cost
- Bind variable peeking and adaptive cursor sharing
- Hands-on: stabilise a regressing statement using a plan baseline
- Instance Tuning: Memory and I/O
- SGA and PGA sizing, automatic memory management and advisors
- Buffer cache behaviour, hit ratios and why they mislead
- Shared pool, library cache and hard parse reduction
- Redo, checkpoint and log writer tuning
- Storage latency targets, ASM layout and I/O calibration
- Hands-on: resize memory components using advisor evidence and measure the effect
- Contention and Concurrency
- Enqueue waits, row lock contention and blocking sessions
- Buffer busy waits, hot blocks and ITL contention
- Latch and mutex contention and their usual causes
- Sequence, index leaf block and right-hand growth contention
- Deadlock diagnosis from trace files
- Hands-on: reproduce a contention hotspot and design it away
- Application and Design Level Tuning
- Parsing, cursor management and connection pooling behaviour
- PL/SQL context switching and bulk processing gains
- Partitioning for pruning and parallel operations
- Parallel execution configuration and when it makes things worse
- Materialized views, query rewrite and result caching
- Building a tuning runbook and a regression watch process
- Hands-on: capstone tuning of an end to end workload with measured results
Hands-on Experience with Tools
Training Delivery Format
Flexible, comprehensive training designed to fit your schedule and learning preferences
Opt-in Certifications
AWS, Scrum.org, DASA & more
.avif)
100% Live
on-site/online training
.avif)
Hands-on
Labs and capstone projects
.avif)
Lifetime Access
to training material and sessions
Skill-Gap Assessment
Analysing skill gap and assessing business requirements to craft a unique program
1
Personalisation
Customising curriculum and projects to prepare your team for challenges within your industry
2
Implementation
Supplementing training with consulting support to ensure implementation in real projects
3
Why this course
- A method, not a checklist: Teams learn a repeatable diagnostic sequence that starts from where time is actually spent rather than from a list of parameters to change.
- Wait events as the entry point: Every investigation begins with wait analysis, which is what separates a tuned database from a lucky one.
- Plan stability included: SQL plan baselines and profiles are covered, so improvements survive the next statistics gathering run.
- Licensing addressed honestly: The course is clear about which diagnostic features require the Diagnostics and Tuning Pack and offers alternatives where they are not licensed.
Training objectives
- Apply a repeatable, evidence-led method to any performance problem
- Generate and interpret AWR, ADDM and Active Session History output
- Use wait event analysis to identify the dominant bottleneck
- Explain how the cost based optimiser produces and costs a plan
- Read execution plans including real time SQL monitoring output
- Diagnose cardinality misestimation and correct statistics problems
- Select access paths and design indexes for a given workload
- Recognise join methods and understand query transformations
- Stabilise plans using SQL plan baselines, profiles and hints
- Tune instance memory, I/O and parallel execution
- Diagnose and resolve latch, lock and buffer contention
- Identify application and design level causes that no parameter will fix
Who will benefit
- Oracle database administrators responsible for performance
- Application and PL/SQL developers writing performance sensitive code
- Platform and infrastructure engineers supporting Oracle workloads
- Data engineers running large Oracle based batch processes
- Technical leads accountable for application response times
.avif)
Lead the Digital Landscape with Cutting-Edge Tech and In-House " Techsperts "
Discover the power of digital transformation with train-to-deliver programs from Uptut's experts. Backed by 70,000+ professionals across the world's leading tech innovators.
.svg)
.svg)
.svg)
.svg)
.svg)
Recommended Training
Continue your learning journey with these hand-picked courses and bundles
Frequently Asked Questions
2. Will my team get any practical experience with this training?
.svg)
With our focus on experiential learning, we have made the training as hands-on as possible with assignments, quizzes and capstone projects, and a lab where trainees will learn by doing tasks live.
3. What is your mode of delivery - online or on-site?
We conduct both online and on-site training sessions. You can choose any according to the convenience of your team.
4. Will trainees get certified?
Yes, all trainees will get certificates issued by Uptut under the guidance of industry experts.
5. What do we do if we need further support after the training?
We have an incredible team of mentors that are available for consultations in case your team needs further assistance. Our experienced team of mentors is ready to guide your team and resolve their queries to utilize the training in the best possible way. Just book a consultation to get support.