Monday Mar 12, 2012

Upcoming events : Oracle User Group Norway Conference

On March 22nd the  Oracle user group in Norway will take to the sea for their annual conference! This year for the first time they are starting the conference on dry land on Wednesday March 21st, followed by a two day trip from Oslo to Germany and back on a ferry. I am presenting the following three sessions at the conference.[Read More]

Friday Mar 09, 2012

Oracle OpenWorld 2012 Call For Papers

I can't believe it's time once again to start thinking about Oracle OpenWorld! The Oracle OpenWorld 2012 call for papers opens March 14th. Oracle customers and partners are encouraged to submit proposals to present at this year's Oracle OpenWorld conference, which will be held September 30 - October 4, 2012 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 9th 2012 at 11:59 pm PDT. We look forward to checking out your sessions on the Optimizer, SQL Plan Management, and statistics!

[Read More]

Friday Mar 02, 2012

Additional Information on SQL Patches

Our last post on SQL patches generated a lot of comments and questions.  Today I will address a couple of questions that are quick to answer.  For those that require more discussion, we'll post more details in the next few weeks.[Read More]

Monday Feb 27, 2012

Using SQL Patch to add hints to a packaged application

In my last post, I showed how you can use the BIND_AWARE hint to skip the monitoring phase of adaptive cursor sharing.  If you have a packaged application, you might be wondering how you can use this hint if you can't edit a query directly.  In this post I'll who you how to do just that, for any hint (or set of hints).[Read More]

Friday Feb 17, 2012

Upcoming events : Hotsos Symposium 2012

I have been lucky enough to be invited to the tenth annual Hotsos Symposium in Irving Texas on March 4-8. The Hotsos Symposium differs from other Oracle conferences because it is a dedicated Oracle performance conference and attracts some of the top performance speakers like, Tom Kyte, Cary Millsap, and Jonathan Lewis. If you get a chance you should really check it out. I have two sessions at this years symposium.[Read More]

Monday Feb 13, 2012

How do I force a query to use bind-aware cursor sharing?

Way back in our original post on adaptive cursor sharing, a commenter asked if there was a way to skip the monitoring for a query and go straight to bind-aware cursor sharing.  We mentioned then that we would add a hint for this purpose, but we never addressed the new hint here on the blog.  Starting in, the BIND_AWARE hint can be used to force bind-aware cursor sharing from the first execution.[Read More]

Wednesday Jan 25, 2012

My cursor wasn’t shared because of STB_OBJECT_MISMATCH. What does that mean?

I got a question recently about cursor sharing and why a cursor wasn’t shared due to an STB_OBJECT_MISMATCH.I thought the question and subsequent investigation might make an interesting blog post. Let me begin by outlining the question in detail.[Read More]

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+


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.


« November 2015