Skip to Main Content

Write Great SQL Office Hours

Free tips and training every month! Subscribe for reminders and more from Office Hours. FAQ

Header container

November 24

14:00 UTC   Start Times Around the World

Description

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

Subscribe to give us feedback and be notified of upcoming sessions!

Having trouble watching the video on this page? Open the video in your browser.

Your Experts

Chris Saxon
Chris Saxon, Oracle Developer Advocate for SQL    
Chris Saxon is the quizmaster for the Database Design competitions and an Oracle Developer Advocate for SQL. His job is to help you get the best out of the Oracle Database and have fun with SQL! He's also part of the answer team on Ask TOM and creates YouTube videos at The Magic of SQL channel.
Nigel Bayliss
Nigel Bayliss, Optimizer Product Manager    
I'm the product manager for the Oracle Optimizer. I started out as an Oracle DBA and application developer in 1988, gradually migrating to the field of consulting, specializing in database design and performance management. I joined Oracle Consulting in 1996 where I concentrated on delivering performance-critical, very large database applications. Since then, I've been involved in a wide variety of different roles including development, benchmarking and pre-sales (with a particular emphasis on Oracle Exadata). I am part of the Oracle Data Warehouse Product Management team.