How to Use SQL Plan Baselines and SQL Profiles
Vložit
- čas přidán 4. 07. 2024
- 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 - Věda a technologie
Awesome Presentation, not sure why so less hits for this. This Video needs to be publicized more.
Is there anyway I can download the scripts and material.
What scripts were being used in the demo? Thnx. Great tutorial.
Can you please explain how SPM is integrated with Adaptive Cursor Sharing. So SPM can take advantage when cursor marked as bind aware and can change plan based on different bind value.
awesome presentation, do we always need to set fixed= yes or just accepted=yes?
you can't set it accepted and you should not always use fixed. everuthing depends on circumstances
this is terrible explanation.