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!
There’s only a month to go until Oracle Open World, the 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!
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.
In last week’s post I described how to use SQL Monitor to determine what is happening during the execution of long running SQL statements. Shortly after the post went up, I got some requests on both social media and via the blog comments asking, “If it is possible to get the same information from a traditional text based execution plan, as not everyone has access to SQL Monitor?”.
The answer is yes, it is possible to see a lot of the information showed in SQL Monitor by viewing the execution plan via the DBMS_XPLAN.DISPLAY_CURSOR function. In order to call this function you will need SELECT or READ privilege on the fixed views V$SQL_PLAN_STATISTICS_ALL, V$SQL and V$SQL_PLAN, otherwise you’ll get an error message.
The DBMS_XPLAN.DISPLAY_CURSOR function takes three parameters:
- SQL ID – default null, means the last SQL statement executed in this session
- CURSOR_CHILD_NO – default 0
- FORMAT – Controls the level of details that will be displayed in the execution plan, default TYPICAL.
The video below demonstrates how you can use the FORMAT parameter within the DBMS_XPLAN.DISPLAY_CURSOR function to show you information about a what’s happened during an execution plan including the bind variable values used, the actual number of rows returned by each step, and how much time was spent on each step.
Under the video you will find all of the commands used, so you can cut and paste them easily.
How do I see the actual number of rows and elapse time for each step in the plan?
I’m often asked what is the best tool for viewing execution plans and for me the answer is always SQL Monitor (included in the Oracle Tuning Pack). It really is the most invaluable tool if you need to determine what is happening during the execution of any long running SQL statements.
In order to help you get the very most out of using SQL Monitor, I wanted to share with you some of the tips and tricks I‘ve learnt over the years from the original Database Manageability team, especially Cecilia Grant!
So, why do I love it so much?
There are so many reasons to love SQL Monitor; it’s hard for me to know where to begin. So, instead of giving you an exhaustive list I’ve put together a short video to demonstrate how I use SQL Monitor (be sure to set your resolution to 720p).
How do I control what statements are monitored?
Since it’s introduction in Oracle Database 8i, the DBMS_STATS package is Oracle’s preferred method for gathering statistics. With each new database release the DBMS_STATS package is extended to accommodate new approaches to gather statistics and new types of statistics.
Over the years, application developers and DBAs have written hundreds of scripts using the DBMS_STATS package to help gather and manage optimizer statistics effectively. However, once written these scripts are rarely modified to take advantage of the improvements in the DBMS_STATS package, which can result in suboptimal statistics.
Oracle Database 12 Release 2 makes it a lot easier to be able to manage this vast collection of scripts by includes a new DBMS_STATS preference called PREFERENCE_OVERRIDES_PARAMETER. When this preference is set to TRUE, it allows preference settings to override the parameter values specifically set in a DBMS_STATS command.
Over the last month or so I’ve been on the road presenting at different user group conferences and meeting with customers. I’ve gotten a number of requests via blog comments to share the slides for the sessions I presented.
One of the most requested sessions is actually one I delivered, as a joint session with @GeraldVenzl, called Oracle Database 12c and DevOps.
In this session Gerald takes on the role of the lead developer on a project to deploy a RESTful web-based application, while I play the role of the DBA. Through the course of the session, we learn to work together to find a solution that will allow our (fictitious) company to embrace a more agile development approach, as well as the latest technology trends without exposing the business to painful availability or security vulnerabilities.
Although the slides don’t do our witty repartee justice , hopefully they will inspire you to try out some of the technologies we describe in the session!
Below is the other session that I got a lot of requests for was the keynote session I gave at this year’s Georgia Oracle Users Group Tech Days 2017 , “What to expect from Oracle Database 12c”.
June 16th 2017 marks the 40th anniversary of the founding of Oracle, which actually began life as Software Development Laboratories.
Larry Ellison, Bob Miner, and Ed Oates set out to build the worlds best relational database and what an amazing 40 years of Database innovations it has been. From availability to security, Oracle Database has got you covered and we have only just begun!
To mark this great occasion, we’ve put together a short video highlighting all of the amazing features and functionality that has been added to the database over the last 40 years.
Feel free to leave you birthday wishes or comments for the Oracle Database Engineering Team in the comments section below.
The month of June is shaping up to be jam packed with great Oracle conference all across the global and I’m fortunate enough to have the opportunity participate in 3 of them and hope to catch up with a lot of folks in person.
So where can you find me?
Oracle Gebruikersclub Holland Tech Experience 2017
On June 15th and 16th some of the best and brightest members of the Oracle community in the Netherlands will gather for a two day conference that will include sessions on the Oracle Database, SQL, Fusion Middleware and much more. I’m fortunate enough to be delivering 3 sessions at this event:
Posted in Events
Back in April, I shared with you a new white paper that described a set of best practices or management techniques to facilitate IoT workloads with the Oracle Database. Since then there have been a number of updates in this space that I thought were worthy of a follow up post.
Just in case you haven’t found the time to read the white paper yet, don’t panic because you can now listen to it! Continue reading