Wednesday Jun 24, 2015

What you need to know about SQL Plan Management and Auto Capture

SQL Plan Management (SPM) is an Oracle database feature that allows you to establish a set of SQL execution plans that will be used even if the database is subject to changes that would otherwise cause execution plan changes to occur. For example, you might have an end-of-day batch run that operates in a business context where there are extreme peaks and troughs in daily volume, or perhaps you are upgrading a database and want to be sure that plans are carried over (at least initially). You do not have to fix execution plans in stone with SPM, you can use plan evolution to automate the process of finding improved plans, improving performance in a controlled way and at your own pace. If you’re not familiar with SPM, a very good place to start is to take a look at Maria Colgan’s four-part blog post on the subject. It gives you all the tools you need to get started.

If you are using SPM in Oracle Database 11gR2 or 12c, or if you are considering whether you should use it, then this blog post is for you. I decided to publish this post because I recently encountered a couple of environments that ran into, let’s say, “difficulties” with SPM when capturing SQL plan baselines automatically and continuously over a very long period of time (more than a year in fact). I’d like to give you a few pointers to avoid running into the same problems and why automatic SQL baseline capture was never intended to be used in that way.

[Read More]

Tuesday May 26, 2015

Space Management and Oracle Direct Path Load

Most of you will be familiar with the concept of direct path load and how it’s an efficient way to load large volumes of data into an Oracle database as well as being a great technique to use when moving and transforming data inside the database. It’s easy to use in conjunction with parallel execution too, so you can scale out and use multiple CPUs and database servers if you need to process more data in less time.

Probably less well known is how the Oracle database manages space during direct path load operations. This is understandable because the Oracle database uses a variety of approaches and it has not always been very obvious which one it has chosen. The good news is that Oracle Database 12c from version onwards makes this information visible in the SQL execution plan and it is also possible to understand what’s happening inside earlier releases with a bit of help from this post and some scripts I’ve linked to at the end.

[Read More]

Friday May 08, 2015

Controlling Access To The In-Memory Column Store Via Hints

It’s been almost a year since I’ve had an opportunity to write a new blog post here due to my crazy schedule now that I'm the In-Memory Maven (thanks to Doug Burns & Graham Wood for the new and improved title). But this morning while I was writing a post for the In-Memory blog about controlling the use of the In-Memory column store (IM column store), I realized that the content on Optimizer hints really belonged over here.

So, I decided to split the blog post in two. I’ve put the information on the initialization parameter that control the use of the IM column store on the In-Memory blog and the information about the Optimizer hint that control the use of the IM column store here.

[Read More]

Monday Jul 07, 2014

The Optimizer & the new Oracle Database In-Memory option

I know its been forever since I've posted a new blog but I am no longer the Optimizer lady. In case you haven't heard already,  I have a new role now as the In-Memory lady (definitely need to work on a better title).

But for this weeks In-Memory blog post I got a chance to combine both my old and my new roles when I wrote about how the In-Memory option and the Optimizer interact. And I thought the readers of this blog might be interested in this topic too.

So, if you are interested in seeing how these these two fascinating pieces of the Oracle technology work together, check out the latest post on the new In-Memory blog.

You should also mark your calendars because of the head of the Optimizer development team, Mohamed Zait, will also discuss this topic at Oracle Open World later this year!

 Hope to catch up with a lot of you then.

Friday Sep 27, 2013

Oracle Open World 2013 - It's a wrap

Thanks to all those that attended the Optimizer sessions and stopped by the Optimizer demopod during this years Oracle OpenWorld. It was an amazing conference this year with lots of great technical sessions and interesting discussion at the demo grounds.

Since so many folks have emailed me requesting a copy of the Optimizer session presentations I thought it would be a good idea to post them here. The presentation were unfortunately were larger than the 2MB file limit so I have broken them multiple files. Enjoy!

Session CON8622 :

Session CON8643 :

Wednesday Sep 25, 2013

Day 4 of Oracle OpenWorld 2013 September 25th

Thanks to all those who stopped by the demogrounds  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 demogrounds, so if you haven't had a chance to stop by yet, do so today. The Optimizer developers will be there from 9:45 am until 4pm.

There are also some great technical session on today, including:

  • Oracle Database In-Memory—The Next Big Thing
    Session GEN9312 Presented by Juan Loaiza at 11:45am in Moscone South - room 103
    This session provides a deep-dive explanation of how the new Oracle Database In-Memory works. It's a must see for everyone interested in performance.

  • Optimizer Statistics: A Fresh Approach
    Session CON7442 Presented by Conor McDonald at Moscone South - room 308

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 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]

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.


« August 2015