Tuesday Sep 24, 2013

Day 3 of Oracle OpenWorld 2013 September 24th

Hopefully you enjoyed yesterday, the first full day of technical sessions at Oracle OpenWorld and are ready for more today!

Today we give our second technical session, Oracle Optimizer Boot Camp: 10 Optimizer Tips You Can’t Do Without (Session CON8643) at 5:15pm, in Moscone South - room 104.

In this session Jonathan Lewis will join us to explain the process of analyzing and solving 10 of the most common SQL execution performance problems. A must see for all those interested in the Optimizer and SQL tuning or England versus Ireland match ups!

The Optimizer team will also be at the Oracle Database Demogrounds all day.  Demogrounds open at 9:45 am and run until 6pm. So stop by and find out what's new with the Optimizer and the statistics that feed it.

Monday Sep 23, 2013

Day 2 of Oracle OpenWorld 2013 September 23rd

Oracle OpenWorld started yesterday and San Francisco is just buzzing with Oracle folks and the excitement generated by the new Oracle Database In-Memory announcements made by Larry Ellison during last nights keynote!

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, Oracle Optimizer: What’s New in Oracle Database 12c? Session CON8622 is on today at 3:15pm in Moscone North, room 131.

This session provides a deep-dive explanation of how the new adaptive approach to query optimization works. We will use a real-world demo to show the different components of the adaptive model and how they help the optimizer learn additional information during query execution that can be used to improve not only the performance of the current SQL statement but all statements.

Members of the Optimizer team will also be available from 9:30am to 6pm at the demogrounds, to answer any Optimizer questions you might have. This may also be your last change to snag the limited edition Optimizer bumper stickers!

+Maria Colgan

Sunday Sep 22, 2013

Day 1 of Oracle OpenWorld 2013 September 22nd

The biggest day of the Oracle calendar has arrived. Oracle OpenWorld 2013 officially starts today!

The show kicks off with ton's of great technical sessions selected by the Oracle User Groups including several sessions on the optimizer:

  • 11:45 AM - 12:45 PM The Query Optimizer in Oracle Database 12c: What’s New?
    Session UGF3062 delivered by Christian Antognini in Moscone West - room 3002

  • 1:00 PM - 2:00 PM Solving Critical Customer Issues with the Oracle Database 12c Optimizer
    Session UGF5498 delivered by Nicolas Jardot in Moscone West - room 3002

And of course, Larry's keynote is this evening 5:15pm – 7:00pm, Moscone North. A must see, as he is bound to make some exciting announcements to get the show started! One of which may be very close to my heart but you will have show up to find out what it is!

All right I will give you a hint .........

Hope to see ya there!

Friday Sep 13, 2013

What's new in 12c: Adaptive joins part 2

In our earlier post on adaptive joins we explained how this new 12c functionality works and said we would follow up this post with a real-world demo.

[Read More]

Sunday Sep 08, 2013

Oracle OpenWorld 2013 - Time to plan your schedule

There are only two weeks to go until Oracle Open World, the largest gathering of Oracle customers, partners, developers, and technology enthusiasts, which begins on September 22nd in San Francisco. Of course the Optimizer development group will be there and you will have multiple opportunities to meet the team, in one of our technical sessions, or at the Oracle demogrounds.

This year the Optimizer team has 2 technical sessions;

  • Monday, September 23rd at 3:15pm  Oracle Optimizer: What’s New in Oracle Database 12c?
    Session CON8622
    at Moscone North - room 131
    This session provides a deep-dive explanation of how the new adaptive approach to query optimization works. We will use a real-world demo to show the different components of the adaptive model and how they help the optimizer learn additional information during query execution that can be used to improve not only the performance of the current SQL statement but all statements.

  • Tuesday, September 24th at 5:15pm Oracle Optimizer Bootcamp: 10 Optimizer tips you can't do without
    Session CON8643 at Moscone South - room 104
    Jonathan Lewis will join us to explain the process of analyzing and solving 10 of the most common SQL execution performance problems. These problems include poor cardinality estimates, bind peeking issues, the selection of sub-optimal access methods, and many more. Through clear how-to examples, you will learn how to identify and quickly resolve these issues and add 10 new tricks to your SQL tuning arsenal.

If you have burning Optimizer or statistics related questions, you can ask them at the Optimizer demo booth in the Database area of the demogrounds. Members of the Optimizer development team will be there Monday to Wednesday from 9:45am until 5:30pm. You may even be able to pick up an optimizer bumper sticker.

The full searchable OOW catalog is on-line, or you can browse the speakers by name. So start planning your trip today!

Sunday Aug 25, 2013

What's new in 12c: Adaptive joins

As we promised in our previous post, we are starting a blog series describing all of new Optimizer and statistics related functionality on Oracle Database 12c. We begin the series with an in-depth look at adaptive plans, one of the key features in the new adaptive query optimization framework.[Read More]

Tuesday Jun 25, 2013

Oracle Database 12c is here!

Oracle Database 12c was officially release today and is now available for download. Along with the software release comes a whole new set of collateral that explains in detail all of the new features and functionality you will find in this release.

The Optimizer page on Oracle.com has all the juicy details about what you can expect from the Optimizer in Oracle Database 12c. Direct links are below.

[Read More]

Thursday May 02, 2013

Upcoming event: Kscope 13

Kscope 13, is the annual conference of the Oracle Development Tools User Group, taking place  June 23 - June 27, in New Orleans. This is a great conference for Oracle developers and architects, offering some of the best content by renowned experts.

I am luck enough to be involved in five sessions this year around the Oracle Optimizer and performance. Below are details on the session I will be presenting. I hope you have an opportunity to check out some of these sessions if you plan to attend the conference!

[Read More]

Monday Apr 15, 2013

How does the METHOD_OPT parameter work?

In last week’s post we addressed the most popular question surrounding statistics gathering, what sample size should be used? Once that decision has been made, folks typically turn their attention to histograms and the time honored question of, do I need histograms or not?

Regardless of which side of this religious debate you come down on, you are going to need to figure out what to set the METHOD_OPT parameter to in your statistics gathering command.

This post explains in detail what the METHOD_OPT parameter controls and how it can be used to influence which columns get statistics and what type of statistics they get.

[Read More]

Monday Apr 08, 2013

How does AUTO_SAMPLE_SIZE work in Oracle Database 11g?

When it comes to gathering statistics, one of the most critical decisions you have to make is, what sample size should be used? A 100% sample will ensure accurate statistics but could take a really long time. Where as a 1% sample will finish quickly but could result in poor statistics.

The ESTIMATE_PERCENT parameter in the DBMS_STATS.GATHER_*_STATS procedures controls the sample size used when gathering statistics and it default values is AUTO_SAMPLE_SIZE.

In an earlier blog post, we talked about the new implementation of AUTO_SAMPLE_SIZE in Oracle Database 11g in terms of its improvements in the speed and accuracy of statistics gathering compared to the old AUTO_SAMPLE_SIZE.

In this post, we will take a closer look at the how the new AUTO_SAMPLE_SIZE algorithm works and how it affects the accuracy of the statistics being gathered.

[Read More]

Wednesday Mar 13, 2013

Oracle Open World 2013 Call For Papers!

Believe it or not, it's time once again to start thinking about Oracle OpenWorld! The Oracle OpenWorld 2013 call for papers opens March 13th. Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held September 22 - 26, 2013 at the Moscone Center in San Francisco. Details and submission guidelines are available on the Oracle OpenWorld Call for Papers web site. The deadline for submissions is April 12th 2012 at 11:59 pm PDT. We look forward to checking out your sessions on the Optimizer, SQL Plan Management, and statistics!

This also means you need to start thinking of new slogans for this years optimizer bumper sticker! Details on how to submit an entry will follow shortly.

[Read More]

Monday Feb 11, 2013

How do adaptive cursor sharing and SQL Plan Management interact?

We've received a lot of questions about how adaptive cursor sharing (ACS) and SQL plan management (SPM) interact.  We discussed this briefly in one of the original SPM posts, but in this post, we'll explain the concepts of how the two features interact, and show an example. 

The simplest way to reason about the interaction is to remember that they are responsible for two different tasks.  ACS controls whether or not a child cursor is shared on a particular execution.  For each execution of the query, ACS considers the current bind values and decides if an existing child cursor can be shared or if the optimizer should be given the chance to find a better plan for the current bind values.  SPM controls which plans the optimizer may choose.  If a child cursor is bind-aware, the decision to share or not is made irrespective of whether the query is controlled by SPM.  But once the query and its current bind values are sent to the optimizer for optimization, SPM constrains the optimizer's choice of plans, without regard to whether this query is being optimized due to ACS.

[Read More]

Tuesday Feb 05, 2013

Upcoming events : RMOUG 2013

I'm really looking forward to the  Rocky Mountain Oracle User Groups Training days next week, at the Colorado Convention Center, as it will bring several new challenges for me.  I am going to be involved in three very different sessions at this years conference:

[Read More]

Wednesday Jan 09, 2013

How does SQL Plan Management match SQL statements to SQL plan baselines?

Happy New Year to all of our readers!

As more and more of your systems migrate to Oracle Database 11g, SQL Plan Management (SPM) is becoming increasingly popular as a mechanism to maintain plan stability and subsequently system performance. However, one of the key frustrations folks encounter when using SPM is that the SQL plan baseline they painstakingly created is not used.

[Read More]

Wednesday Nov 21, 2012

How do I create statistics to make ‘small’ objects appear ‘large’ to the Optmizer?

I recently spoke with a customer who has a development environment that is a tiny fraction of the size of their production environment. His team has been tasked with identifying problem SQL statements in this development environment before new code is released into production.

The problem is the objects in the development environment are so small, the execution plans selected in the development environment rarely reflects what actually happens in production.

[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.


« June 2016