|SQL Plan Management in Oracle Database 11g Release 1 - Maintain consistent SQL performance regardless of changes in optimizer version, optimizer statistics, schema changes, system settings and SQL profile creation.|
Thomas said...It's very good article
Ratnesh Pune said...really good article.
karthik said...Much appreciated!
Leo James said...Very well explained. Comprehensive but clear.
Mohamed Amin said...Thank you very mush , I wan unable to understand it from Oracle New future books, but you make it more clear than oracle
Abdo Allah said...You said "If the SQL plan baseline doesn't contain a matching plan, the optimizer evaluates the accepted plans in the SQL plan baseline and uses the one with the lowest cost"
is there a conflict between the first and second part?
First you said SQL plan doesn't contain a matching plan in SQL plan baseline .
second you said the optimizer takes one with the lowest cost from SQL plan baseline
Please clarify because I am confused.
I've reworded it so I think you will understand it now. It meant when an accepted sql plan baseline is found that matches the plan the optimizer just produced. There may be other accepted sql plan baselines for the statement that do not match the plan just produced by the optimizer.
Abdo Allah said... said...Thanks Tim , it is more clear now
Regis said...Nicely explained and very powerfull tool in SQL tuning...:)-
Surya Rao said...DBMS_SPM.drop_sql_plan_baseline (
sql_handle => 'SYS_SQL_7b76323ad90440b9',
plan_name => NULL);
In the above, there is a typo. The value used in the sql_handle parameter is incorrect. It shows an SQL Plan name being used. The SQL_HANDLE is of the form -- SQL_9864220dde6f034a
No it's not. If you look at the data coming out of the queries, you can see this is the SQL_HANDLE being produced in this system. The format may have changed between 11gR1 and 11gR2, which maybe the source of you confusion. :-)
Daniel said...Great article, thank you very much.