How to Use SQL Plan Baselines and SQL Profiles

The Oracle optimizer has many tools and tricks for finding the best plan for each query. But in some cases, it may struggle to find a fast plan. Luckily Oracle Database has many tools to help the optimizer pick a good plan for each query.

This session gives an overview of plan baselines and profiles to assist the optimizer. It shows you how they work, explains the differences between them and why you would use one over the other.

Highlights of this session include:

01:20 - SQL Profiles, how to create them, why, and what they are
05:00 - SQL Plan Management and baselines, how to create them, why, and what they are
07:30 - SQL Profiles alternative usage - tricks to constraint SQL plans
11:00 - Licensing for SQL Plan Management
12:25 - Using SQL baselines when migrating databases
14:00 - Why should you avoid hints in SQL?
19:15 - Evolving plans with SQL plan management
22:30 - Demo showing how to create SQL Profiles and SQL Baselines and how they're different
29:08 - Using SPM with Real Application Testing
31:00 - Dealing with large data changes
34:10 - Using SQL Profiles and SQL Plan Baselines together
38:20 - Dealing with variable numbers of bind variables for an IN list (e.g. IN (:b1, :b2), IN (:b1, :b2, :b3)
40:30 - How many SQL plan baselines can you have?
42:35 - Managing profiles when changing the columns in a table
45:25 - Using SPM on a few SQL statements
47:05 - Using SPM with Database In-Memory
48:50 - Using SPM on overloaded databases
50:40 - How to get in touch with Nigel with your SPM questions
52:00 - Final recap and comparison