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.
Maria Colgan of Oracle Corp.
-
1. Creating SQL Plan Baselines
2. SPM-aware Optimizer
3. Evolving SQL Plan Baselines
4. User Interfaces and other features
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
-
Walking through evolving a plan baseline
Kerry just had a new post on dynamic sampling with baselines and profiles.
Comment by bryan grenn — January 8, 2011 @ 1:48 am UTC 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 UTC 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 UTC 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 UTC Jan 8,2011 |
[...] http://kerryosborne.oracle-guy.com/2009/04/oracle-11g-sql-plan-management-sql-plan-baselines/ http://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 UTC Jul 22,2011 |