Does the Explain Plan command really show the execution plan that will be used?

When it comes to SQL tuning we often need to look at the execution plan for a SQL statement to determine where the majority of the time is spent. But how we generate that execution plan can have a big impact on whether or not the plan we are looking at is really the plan that is used.

The two most common methods used to generate the execution plan for a SQL statement are:

EXPLAIN PLAN command – This displays an execution plan for a SQL statement without actually executing the statement.

V$SQL_PLAN A dynamic performance view introduced in Oracle 9i that shows the execution plan for a SQL statement that has been compiled into a cursor and stored in the cursor cache.

My preferred method is always to use V$SQL_PLAN (even though it requires the statement to at least begin executing) because under certain conditions the plan shown by the EXPLAIN PLAN command can be different from the plan that will actually be used when the query is executed.

So, what can cause the plans to differ?

Continue reading

Posted in Beginners, Optimizer | Tagged , , , | Leave a comment

Oracle Autonomous Database – What you need to know

At OpenWorld, Larry Ellison announced  an Autonomous Database cloud (service), which is self-driving, self-securing, and self-repairing.

Although there was a lot of excitement surrounding the announcement, there were also a lot of questions.

How does it work exactly?

When and where will it become available?

How will it impact the role of the DBA?

So, we thought it would be a good idea to share several videos to help address these questions and concerns.

In the first video, Juan Loaiza gives you a peek behind the curtain of the Autonomous Database. He explains how it delivers full end-to-end automation for mission-critical workloads, including automation in provisioning, patching, securing, monitoring, optimizing, and more.

In the second video, Cetin Özbütün shares details on the first Autonomous Database service to become available later this year, the Autonomous Data Warehouse. This service is extremely easy to use as you simply load data and begin running queries. Oracle will take care of everything else for you.

And finally yours truly explains which aspect of the DBA role will be done automatically (mundane custodial tasks), allowing the DBAs to spend more time innovating and helping the business to better leverage their data.

As we get closer the official launch of new services we will share more information. So stay tuned!

Posted in Autonomous Database, OOW | Tagged , , | 3 Comments

Oracle Open World 2017 Wrap Up!

Thanks to all the customers, Oracle Aces, Development Champions and partners that made this year’s Open World an amazing event!

The conference was four, jam-packed days of great technical sessions and interesting discussions especially around the new Autonomous Database.

I was lucky enough to be present three sessions at this year’s event with two great co-speakers @dominic_giles and @GeraldVenzl . We’ve uploaded all of the slides, which are now publicly available on the Oracle Open World session catalog.

Simply click on the download arrow on the right hand side of each catalog entry to get a copy of the slides.

Don’t worry if you didn’t get a chance to collect a SQLMaria sticker. I’ll have more stickers at the East Coast Oracle Users Conference in November and at the UKOUG Technology Conference in December.

 

Posted in OOW | Tagged | Leave a comment

Day 4 of Oracle Open World 2017

It’s the last day of Oracle OpenWorld and we have saved the very best for last. So hopefully you are still awake and functioning at this stage!

You can start the day off at 12 noon in Moscone West, room 3020 with the Maria & Gerald Venzl show! In this session we will share the Oracle Database Features Every Developer Should Know About. Using clear, easy to follow examples we will explain how you can put these features to work in whatever project you are doing.

Continue reading

Posted in Events, OOW | Tagged | 2 Comments

Day 3 of Oracle OpenWorld 2017

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

At 11:30 am today I give my first technical session, with the wonder Dominic Giles of Swingbench fame!

Dom and I will be talking about the Oracle Database & the Internet of Things in Moscone West, room 3011. In this session we will provide step-by-step instructions for deploying a high-ingest, mission critical IoT workload on Oracle D atabase. There will be even some demos to proving the impact of each recommendations. So don’t missing it!

You can also get a SQLMaria laptop sticker at the session!

Continue reading

Posted in Events, OOW | Tagged , | Leave a comment

Day 2 of Oracle Open World 2017

Oracle OpenWorld started yesterday and San Francisco is just buzzing with Oracle folks and the excitement generated by the new Oracle Autonomous Database announcements made by Larry Ellison during last night’s keynote!

If you are interested in more information on the Autonomous Database and how it’s changing the roll of the DBA, be sure to check out the “Preview of Oracle Autonomous Database” that Juan Loaiza‘s and I will present on Wednesday at 3:30pm in Moscone West,  room 3014.

But what about session picks for today?

Continue reading

Posted in AskTom, Events, OOW | Tagged | Leave a comment

Day 1 of Oracle OpenWorld 2017

The biggest day of the Oracle calendar has arrived.

Oracle OpenWorld 2017 officially starts today!

The show kicks off with ton’s of great technical sessions selected by the Oracle User Groups including several great sessions on Oracle Database 12c, Data Management and Database In-Memory.

Continue reading

Posted in Events, OOW | Tagged | Leave a comment

Harnessing the Power of Optimizer Hints

Last week I was lucky enough to have participated in the Trivadis Performance Days 2017 conference and several people have asked if I would share the slides from one of my sessions.

The session in question was called “Harnessing the power of optimizer hints”.  Although I am not a strong supporter of adding hints to SQL statements for a whole host of reasons, from time to time, it may become necessary to influence the plan the Optimizer chooses.

The most powerful way to alter the plan chosen is via Optimizer hints. But knowing when and how to use Optimizer hints correctly is somewhat of a dark art.

In this session I explained how Optimizer hints are interpreted, when and where they should be used, and why they sometimes appear to be ignored.

Be warned this session won’t make your a hinting master over night and I’m not advocating you should try and hint every problematic SQL statement you encounter!

Posted in Optimizer | Tagged , , | Leave a comment

Oracle OpenWorld 2017 – Time to plan your schedule

 

There’s only a month to go until Oracle Open Worldthe largest gathering of Oracle customers, partners, developers, and technology enthusiasts, which begins on October 1st in San Francisco. Of course the database development groups will be there and you will have multiple opportunities to meet the teams, in one of our 456 technical sessions, or at the Oracle Database demogrounds (The EXchange).

This year there are a couple of new things I want to bring to your attention as well highlighting some of the sessions I think you should consider checking out!

First up, locations!

All of the Oracle Database technical sessions will take place in Moscone West this year, rather than Moscone South, since parts of it are still under construction.  You will also find the Database Demogrounds on the ground floor of Moscone West.

Speaking of the demoground, it has a new name this year, The EXchange and it has a new look too. You will find a dedicated developers lounge, where you can meet the experts, take a workshop or grab a beer. We will also have mini-theaters in demoground area, so stay tuned for more details on what sessions will happen there closer to the event.

The Database Hands-on-Labs also have a new location this year. They are going to be at the Hilton San Francisco Union Square (Lobby Level). The map below will tells you where to find everything you will need (right click on the map to open it in a new tab if you want a bigger image).

Now that the full searchable OOW catalog is available on-line, it’s time to start planning your schedule!

I’ve put together a short list of my top 4 sessions for each day that I’m hoping to attend to give you a little inspiration!

Continue reading

Posted in OOW | Leave a comment

Controlling where objects are populated into memory on RAC

Last week I spent some time beta testing Oracle Database 12c RAC on docker (more on this in a future post) and decided to take a trip down memory lane and play around with Oracle Database In-Memory.

One of the things I looked at was the new FOR SERVICE sub-clause of the INMEMORY DISTRIBUTE clause.

ALTER TABLE customers INMEMORY PRIORITY HIGH DISTRIBUTE FOR SERVICE IM1;

This new sub-clause allows you to control exactly where an object (table or partition) is populated into memory in a RAC environment based on where a database service is allowed to run. If the service is stopped then the objects distributed for that service are automatically removed from the IM column store(s).

If you recall each RAC node has it’s own In-Memory column store (IM column store). By default, when a table is populated into memory in a RAC environment it will be automatically distributed across all of the IM column stores in the cluster. That is to say, a piece of the table will appear in each RAC node, effectively making the IM column store a shared-nothing architecture.

This is not always desirable, especially if you only run certain applications or workloads on a subset of RAC node.

The video below demonstrates how to use the new syntax and what gv$ performances views you should look at to monitor what is going on in the IM column store in a RAC environment. You’ll find a copy of the script I used in the video at the bottom of this post.

Continue reading

Posted in Database In-Memory, Oracle Database 12c New Features | Tagged , | Leave a comment