Here’s another of my little catalogues of articles – this one on SQL Plan Baselines.
Be a little careful as you read through these notes – there are various changes in internal mechanisms, storage, etc. as you go through different versions of Oracle, so check which version the author is writing about.
Oracle Corp.
Creating SQL Plan Baselines 1/4
SPM-aware Optimizer 2/4
Evolving SQL Plan Baselines 3/4
User Interfaces and other features 4/4
SQL Plan Management in 19c (white paper)
Christian Anognini:
An interesting observation on evolving baselines for delete statements
Kerry Osborne
1. Do SQL Plan Baselines use hints (pt.1)
2. Do SQL Plan Baselines use hints (pt. 2)
3. SQL Plan Management – SQL Plan Baselines
4. Why isn’t Oracle using my outline/ SQL profile / SQL Plan Baseline
5. Dynamic Sampling and profiles/baselines
6. Licence differences between profiles and baselines
Tim Hall
A description and walk-through on SQL Plan Baselines
Jason Arneil
Kerry just had a new post on dynamic sampling with baselines and profiles.
Comment by bryan grenn — January 8, 2011 @ 1:48 am GMT Jan 8,2011 |
Bryan,
Thanks for that – I’ve added the link (and the follow-up link).
Comment by Jonathan Lewis — January 8, 2011 @ 10:27 am GMT Jan 8,2011 |
[…] This post was mentioned on Twitter by jarneil, Surachart Opun. Surachart Opun said: SQL Plan Baselines: http://t.co/bEui5K3 […]
Pingback by Tweets that mention SQL Plan Baselines « Oracle Scratchpad -- Topsy.com — January 8, 2011 @ 3:45 pm GMT Jan 8,2011 |
Interestingly, Kerry didn’t understand quite a long time that SQL Profiles are not supposed to freeze execution plans. Most of DBA Instructors at Oracle University would learn you this basic fact, probably thanks to execlent Joan-Fransois Verrier, who was preraing most of materials for courses.
I can rember situation, when a SQL statement with subobtiomal exectuiion plan was tuned by SQL TUNING ADVISOR (STA) and an underlying SQL profile was created. As data growed and distributaion of them changed, the same SQL statement had became unefficient, STA had benn ran again, finding different (better again) SQL Profile.
SQL Baselines one of the best features in Oracle database introduced for last 10 years. I do not like automatic evolving and automatic tuning,, but I am now finally able to change execuition plans without changing source code of an application in supported way. Excellent for black box application tuning! I was not very keen on hacking Outlines and SQL profiles, to be honest..
Comment by Pavol Babel — January 8, 2011 @ 11:32 pm GMT Jan 8,2011 |
[…] http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/ https://jonathanlewis.wordpress.com/2011/01/07/sql-plan-baselines/ GA_googleAddAttr("AdOpt", "1"); GA_googleAddAttr("Origin", "other"); GA_googleAddAttr("theme_bg", […]
Pingback by Outline, SQL profile and SQL Plan Management(SPM) « Oracle Explorer: Standing on the shoulders of giants — July 22, 2011 @ 6:00 am BST Jul 22,2011 |