Monday Jan 16, 2012

Upcoming events : RMOUG 2012

Even though the new year has just begun my calendar is already filling up with conferences. The first conference of the year is the Rocky Mountain Oracle User Groups Training days on Febuary 14 - 16 at the Colorado Convention Center in Denver, Colorado. It a great conference with over 700
attendee and some really great speakers. I am going to be delivering three session at this years conference:

[Read More]

Monday Jan 09, 2012

Fixed Objects Statistics and why they are important

Fixed objects are the x$ tables and their indexes. The v$performance views in Oracle are defined in top of X$ tables (for example V$SQL and V$SQL_PLAN). Since V$ views can appear in SQL statements like any other user table or view then it is important to gather optimizer statistics on these tables to help the optimizer generate good execution plans. However, unlike other database tables, dynamic sampling is not automatically use for SQL statement involving X$ tables when optimizer statistics are missing. The Optimizer uses predefined default values for the statistics if they are missing. These defaults may not be representative and could potentially lead to a suboptimal execution plan, which could cause severe performance problems in your system. It is for this reason that we strong recommend you gather fixed objects statistics.[Read More]

Monday Dec 19, 2011

Why was the RULE hint ignored?

Recently I got a call from a customer that had just upgraded to Oracle
Database and was panicking because they thought the Rule Based
Optimizer (RBO) had been removed from Oracle Database 11g. The reason
they thought this was because when they tried to add the RULE hint to a
SQL statement they still got a Cost Based Optimizer (CBO) plan.

[Read More]

Friday Dec 02, 2011

Capturing 10053 trace files continued

In our previous blog post I described how you can use the new diagnostic event infrastructure in Oracle Database 11g to capture an Optimizer trace (10053) for any SQL statement once you have its SQL_ID. The approach I showed using the traditional ‘Alter session set event’ approach. What I forgot to mention (and have been scolded for) was that you can also use the new infrastructure to generate an Optimizer trace for any SQL statement in the cursor cache, without having to re-execute it.[Read More]

Wednesday Nov 16, 2011

How do I capture a 10053 trace for a SQL statement called in a PL/SQL package?

Traditionally if you wanted to capture an Optimizer trace (10053) for a SQL statement you would issue an alter session command to switch on a 10053 trace for that entire session, and then issue the SQL statement you wanted to capture the trace for. Once the statement completed you would exit the session to disable the trace. You would then look in the USER_DUMP_DEST directory for the trace file. But what if the SQL statement you were interested  in was actually called as part of a PL/SQL package. How do generate an Optimizer trace for just that statement?[Read More]

Tuesday Nov 08, 2011

Lies, damned lies, and statistics

There was huge interest in our OOW session on Managing Optimizer Statistics. It seems statistics and the maintenance of them continues to baffle people. However, understanding and managing Optimizer statistics is key to optimal SQL execution. Knowing when and how to gather statistics in a timely manner is critical to maintaining acceptable performance. In order to clarify all of the information surrounding statistics we have put together a two part whitepaper on Optimizer statistics and the management of them.[Read More]

Monday Oct 31, 2011

Upcoming events : UKOUG 2011

UK Oracle User Group Conference 2011, takes place in Birmingham on December 4th - 7th. This year there will be 15 different streams and over 250 sessions to choose from. I am lucky enough to be presenting two session at this years conference.

Hope to see you at the conference!

Maria Colgan+

Monday Oct 24, 2011

Does the use of SQL Plan Management and the DBMS_SPM database package require a tuning or diagnostic pack license?

Recently during the Open World conference a lot of people asked me about what additional licenses are need to use SQL Plan Management. No additional licenses are needed to use SQL Plan Management (SPM) or any of the procedures in the DBMS_SPM package. SPM is available as part of Oracle Database Enterprise Edition 11g.

I believe the confusion arises from the wording in the Oracle Database Licensing documentation  that says the Tuning Pack includes "Automatic Plan Evolution of SQL Plan Management". What this is actually referring to is the interaction between the SQL Tune Advisor's nightly tuning task and SPM. This interaction does require you to have a license to use the Tuning pack.

In Oracle Database 11g, the SQL Tuning Advisor, runs automatically during the maintenance window. This automatic SQL tuning task targets high-load SQL statements. These statements are identified by the execution performance data collected in the Automatic Workload Repository (AWR) snapshots and not from SPM. If the SQL Tuning Advisor finds a better execution plan for a SQL statement it will recommend a SQL profile. Some of these high-load SQL statements may already have SQL plan baselines created for them. If a SQL profile recommendation is implemented, the execution plan found by the SQL Tuning Task will be added as an accepted plan to the existing SQL plan baseline. 

The Tuning pack or RAT pack are also required if you want to load plans from a SQL Tuning Set (STS) into SPM. Again this is because an STS can only be created if you have licensed one of these packs. It has nothing to do with SPM.

Maria Colgan+

Thursday Oct 20, 2011

Optimizer Technical Papers

We recently got a comment about our paper on adaptive cursor sharing and cardinality feedback, in which the author wished for more technical papers from the optimizer team. We've actually published papers on a few different topics over the years, so we wanted to summarize and post links to them here. Unfortunately they are not all available online for free, but where possible, we've posted links to the full text.

Cost-Based Query Transformation in Oracle
Rafi Ahmed, Allison W. Lee, Andrew Witkowski, Dinesh Das, Hong Su, Mohamed Zait, Thierry Cruanes: Cost-Based Query Transformation in Oracle. VLDB 2006: 1026-1036
This paper talks about many of the query transformations in Oracle 11g which we have discussed here before, including join elimination, view merging, subquery unnesting, join predicate pushdown, join factorization, plus some others that we have not covered here before.  It also describes the cost-based framework itself, some of the challenges of transforming queries in a cost-based manner, and presents performance results for some experiments on a query workload from the Oracle E-Business Suite.

Optimizer Plan Change Management (full text)
Mohamed Ziauddin, Dinesh Das, Hong Su, Yali Zhu, Khaled Yagoub: Optimizer plan change management: improved stability and performance in Oracle 11g. PVLDB 1(2): 1346-1355 (2008)
This describes SQL plan management, which we discussed on here in a four-part SPM series.  The paper gives a bit more detail about the internals of SPM, plus analyzes the behavior of SPM on a large query workload from Oracle E-Business Suite.

Closing the Query Processing Loop in Oracle 11g
(full text)
Allison W. Lee, Mohamed Zait: Closing the query processing loop in Oracle 11g. PVLDB 1(2): 1368-1378 (2008)
This paper discusses adaptive cursor sharing and cardinality feedback, plus the general concept of using feedback from execution to improve plan selection.  We've covered both of these topics on here before, but the paper also includes an analysis of the performance impact of the two features on a query workload from Oracle E-Business Suite.

Efficient and scalable statistics gathering for large databases in Oracle 11g
Sunil Chakkappen, Thierry Cruanes, Benot Dageville, Linan Jiang, Uri Shaft, Hong Su, Mohamed Zait: Efficient and scalable statistics gathering for large databases in Oracle 11g. SIGMOD Conference 2008: 1053-1064
This paper discusses two major improvements to statistics gathering in 11g -- approximate NDV and incremental statistics gathering.  We've discussed both of these topics here before, but the paper has some additional details of the algorithms, plus results of experiments on a few different data sets.

Enhanced Subquery Optimizations in Oracle, VLDB 2009 (full text)
Srikanth Bellamkonda, Rafi Ahmed, Andrew Witkowski, Angela Amor, Mohamed Zait, Chun Chieh Lin: Enhanced Subquery Optimizations in Oracle. PVLDB 2(2): 1366-1377 (2009)
This paper talks about several of the newer subquery optimizations in Oracle.  We talked about basic subquery unnesting in two posts here a while ago, and alluded to some of the other subquery transformations we perform -- this paper talks about all of those.  It also talks about a new form of join elimination which we introduced in 11gR2.  Nearly all of the topics in this paper are NOT covered here on the blog yet, but we hope to fix that soon!

For future reference, we will be adding a new section on the right side of the page called "Optimizer Technical Papers" with links to all of these papers.

Monday Oct 17, 2011

Open World 2011 recap

We had a great time talking to everyone at Open World 2011 both at our Demogrounds booth and  in our  technical sessions.  We got asked a lot of interesting questions and we plan to address them in future blog posts... so stay tuned.

The slides from our session on managing optimizer statistics are now available on Oracle Open World Content Catalog. The slides for our session on preventing suboptimal plans should be posted soon.

We are still working on a good mechanism for sharing the exercises from the hands - on lab but we will blog more about that once its in place.

If you didn't get to attend the conference, but have a question about the Optimizer you would like to see answered on the blog, you can submit it through via email.

We look forward to see you all next year at Open World 2012

Maria Colgan+

Wednesday Oct 05, 2011

Day 5 of Oracle Open World 2011 October 6th

Its the last day of Oracle Open World and we have saved the very best for last. So hopefully you are still awake and functioning at this stage!

We present our top tip session today at 12 noon in Moscone south, room 104. The session is called  Oracle Optimizer: Tips for Preventing Suboptimal Execution Plans (Session 14069). This session is a continuation from a session we did last year called Top Tips for getting optimal SQL execution every time. This year we answers questions like why an index wasn't used, why partition eliminations did not occur, why statistics were ignored, and many more. With clear how-to examples, you will learn to identify and quickly resolve these issues without the need for optimizer hints or initialization parameters.

 We hope you have enjoy the conference so far and will stop by our session this afternoon.

Maria Colgan+

Tuesday Oct 04, 2011

Day 4 of Oracle Open World 2011 October 5th

Thanks to all those who stopped by the demoground  to chat with the Optimizer developers and to check out what is new in the Oracle Optimizer over the last two days. Remember today is the last day of the demoground, so if you haven't had a chance to stop by yet do so today. The Optimizer developers will be there today from 9 am until 4pm.

To day at Open World we will be giving a sessions on Optimizer statistics. The session starts at 10:15 am and is called Oracle Optimizer: Best Practices for Managing Optimizer Statistics (Session 13961) and is in Moscone South, room 103. In this session we will discuss the features introduced in Oracle Database 11g to improve the quality and efficiency of statistics-gathering as well as strategies for managing statistics in various database environments.  This is your opportunity to finally understand Optimizer statistics and when and how you should gather them.

Looking forward to seeing you at our session or at the demoground!

Monday Oct 03, 2011

Day 3 of Oracle Open World 2011 October 4th

Hopefully you enjoyed yesterday, the first full day of technical sessions at Oracle Open World and are ready for more today! The Optimizer team are not presenting a session today but we at the Oracle Database Demo Grounds all day. Demo Grounds open at 9:45 am and run until 5:30pm.  So stop by and find out what's new with the Optimizer and the statistics that feed it!

Sunday Oct 02, 2011

Day 2 of Oracle Open World 2011 October 3rd

Oracle Open World started yesterday and San Francisco is just buzzing with Oracle folks! If you are attending the conference don't miss the opportunity to chat with the Optimizer development team at one of our technical sessions, or at the Oracle Demo grounds.

Our first technical session happens today at 12:30pm. Our hands on lab, Oracle Optimizer: Prevent Suboptimal Execution Plans (Session 29380) is scheduled at the Marriott Marquis hotel in Salon 12 / 13. In this hands on lab you will get an opportunity to solve 4 SQL tuning problems either on your own or with the help of the Optimizer development team. The problems will include scenario like, why was my index not selected, why statistics were ignored, and many more.

Hope you can join us at the hands-on lab!

Wednesday Aug 03, 2011

How do I know if the cardinality estimates in a plan are accurate?

It is often recommended when you are tuning a SQL statement to confirm that the Optimizer's cardinality estimates, in the execution plan, are accurate. But how do you do that?

It is probably easier to answer this question with an example. Let's take the following example of a simple two table join.

[Read More]

The Oracle Optimizer blog is written by members of the Optimizer development team. The goal of this blog is to provide an insight into the workings of the Optimizer and the statistics it relies on. The views expressed on this blog are our own and do not necessarily reflect the views of Oracle and its affiliates. The views and opinions expressed by visitors on this blog are theirs solely and may not reflect ours.


« March 2015