Real World Oracle Tuning

Real World Oracle Tuning
Including Oracle 12c enhancements and new features

This practical seminar is designed to provide its participants with a simple methodology and a clear understanding of the Oracle tuning process.
We will learn how to best identify our problematic SQL statements and tune them for optimal performance.
Join us and get to know the most powerful and useful tools the Oracle database provides for performance tuning and see the implementation of these tools in real-world examples.
We will also get to know some of the best Oracle 12c new features for better performance.

Course topics:

Using an efficient and powerful tuning methodology

Identifying the high load SQL statements

GUI performance tools (OEM), AWR report, Oracle Tracing

Tools for retrieving execution plans and execution statistics

Autotrace, DBMS_XPLAN, EXPLAIN PLAN FOR, Developers Graphical tools

Understanding execution plans

How to read execution plans? – What should we look for to identify core issues?

Affecting execution plans to resolve performance issues
Hints, Optimizer statistics, Optimizer Parameters, re-writing the SQL and more

Indexes – a practical overview

Indexes vs. Full Scans for data access
When should we use indexes and when should we avoid them?
How can we identify inefficient index usage, and how can we fix it?
Oracle 12c enhancements and new features for index usage

Can we really trust executions plans?

Execution plan real time statistics – Moving from theory to actual

Using SQL Monitoring and the “Gather plan statistics” hint
(View Actual values of the execution compared to the optimizer estimated ones)
Oracle 12c enhancements to SQL Monitoring

Golden tips and tricks for optimizing performance

Stabilizing a good plan for my query using SQL Plan Baselines
Adding a hint to my query without changing the SQL in my code (Magic?)
Generating the Oracle performance reports (AWR, ASH etc) from developer client tools, and using them efficiently
Using the Oracle Result Cache to optimize performance
Additional tips and tricks for better performance

Execution Plans enhancements in Oracle 12c

Adaptive Execution plans
Adaptive Statistics and re-optimizations
SQL Plan Directives

Oracle 12c new features for better performance

Additional selected Oracle 12c new features for better performance

Real world examples of analyzing and resolving performance issues

These will be scattered throughout the course