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]

Wednesday Aug 29, 2012

How do I restrict concurrent statistics gathering to a small set of tables from a single schema?

I got an interesting question from one of my colleagues in the performance team last week about how to restrict a concurrent statistics gather to a small subset of tables from one schema, rather than the entire schema. I thought I would share the solution we came up with because it was rather elegant, and took advantage of concurrent statistics gathering, incremental statistics, and the not so well known “obj_filter_list” parameter in DBMS_STATS.GATHER_SCHEMA_STATS procedure.

[Read More]

Thursday Jul 05, 2012

How do I cut and paste commands from your blog?

At the recent ODTUG  Kscope 12 conference several people told me that they really enjoyed our blog on the Optimizer but were frustrated because they couldn’t cut and paste the commands used in the blog posts straight into their environment.

Typically I use screen shots in the blog posts to make the commands clear but it does mean that it is impossible to cut and paste the commands into your environment. In order to get around this I have created a downloadable .sql script for each of our blog posts. You should now see the sentence “You can get a copy of the script I used to generate this post here”, appearing at the bottom of each blog post. Clicking on the link will open the .sql script that contains all of the commands used in the post. You can either save the entire script or just cut and paste the particular command you are interested in!

I have added scripts for all of this year’s blog posts and am slowly making my way through our old posts until we have a script for everything we have posted to date.

Hopefully this will help!

+Maria Colgan

[Read More]

Friday May 18, 2012

How do I compare statistics?

This question came up recently when I was helping a customer migrate a large data warehouse to Oracle Database 11g. Prior to the upgrade, they were using an ESTIMATE_PERCENT of 0.000001, the smallest possible sample size allowed, when they gathering statistics on their larger tables. When I asked why they picked such a tiny sample size they said it was because they needed statistics to be gathered extremely quickly after their daily load both at the partition and at the global level.

Since these large tables were partitioned, I thought they would be an excellent candidate for incremental statistics. However, in order to use incremental statistics gathering you have to let the ESTIMATE_PERCENT parameter default to AUTO_SAMPLE_SIZE. Although the customer saw the benefit of using incremental statistics they were not keen on changing the value of ESTIMATE_PERCENT. So I argued that with the new AUTO_SAMPLE_SIZE in Oracle Database 11g they would get statistics that were equivalent to a 100% sample but with the speed of a 10% sample.  And since we would be using incremental statistics we would only have to gather statistics on the freshly loaded partition and the global statistics (table level statistics) would be automatically aggregated correctly.

So with much skepticism, they tried incremental statistics in their test system and they were pleasantly surprised at the elapse time. However, they didn’t trust the statistics that were gathered and asked me, “how do I compare the statistics I got with AUTO_SAMPLE_SIZE  to the statistics I normally get with an ESTIMATE_PERCENT of 0.000001?”

The answer to that was easy, ‘use DBMS_STAT.DIFF_TABLE_STATS’.
[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 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]

Wednesday Aug 03, 2011

How do I know if the cardinality estimates in a plan are accurate?

It is often recommended when you are tuning a SQL statement to confirm that the Optimizer's cardinality estimates, in the execution plan, are accurate. But how do you do that?

It is probably easier to answer this question with an example. Let's take the following example of a simple two table join.

[Read More]

Tuesday Jul 12, 2011

How do I migrate stored outlines to SQL Plan Management?

Stored outlines were deprecated in Oracle Database 11g and even though we have done a number of posts about SQL Plan Management (SPM) we have never discussed the reasons why you should migrate stored outlines to SQL plan baselines and the steps necessary to do the migration. It is strongly recommended that you migrate existing stored outlines to SPM because:

[Read More]

Thursday May 12, 2011

How do I get an application to use the same execution plan I get in SQL*Plus?

Let me provide a little background to this question before answering it.

This application is a home-grown Java based app and the SQL statement in questions looks like this:


The table T2 has approximately 1.4 million rows and one index IND_T2 on the LAST_UPD and ROW_ID columns. The execution plans are quite simple and involve just the IND_T2 index. The paln in SQL* Plus does an index range scan on the IND_T2 index.

[Read More]

Wednesday Apr 20, 2011

How do I drop an existing histogram on a column and stop the Auto Stats gathering job from creating it in the future?

Before answering this question, I want to provide a bit more background to this scenario. The person who submitted this question has a table with a VARCHAR2 column called ID whose data distribution is skewed. When the Auto Stats job kicks in for this table a histogram is automatically created on this column as it is used in nearly every SQL statement and it has a data skew. However, the values in the ID column are extremely long and the first 32 characters in each ID are identical.[Read More]

Thursday Mar 24, 2011

How do I know what extended statistics are needed for a given workload?

In our previous post we introduced extended statistics, which help the Optimizer improve the accuracy of cardinality estimates for SQL statements that contain predicates involving a function wrapped column (e.g. UPPER(LastName)) or multiple columns from the same table used in filter predicates, join conditions, or group-by keys. So extended statistics are extremely useful but how do you know which extended statistics should be created?

In Oracle Database we introduced Auto Column Group Creation, which automatically determines which column groups are required for a table based on a given workload. Please note this functionality does not create extended statistics for function wrapped columns it is only for column groups. Auto Column Group Creation is a simple three step process:

[Read More]

Tuesday Feb 15, 2011

How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?

I have gotten many variations on this question recently as folks begin to upgrade to Oracle Database 11g and there have been several posts on this blog and on others describing how to use SQL Plan Management (SPM) so that a non-hinted SQL statement can use a plan generated with hints. But what if the hint is supplied in the third party application and is causing performance regressions on your system?

[Read More]

Thursday Feb 10, 2011

New "How do I ..." series

Over the last year or so the Optimizer development team has presented at a number of conferences and we got a lot of questions that start with "How do I ...". Where people were looking for a specific command or set of steps to fix a problem they had encountered. So we thought it would be a good idea to create a series of small posts that deal with these "How do I" question directly. We will use a simple example each time, that shows exactly what commands and procedures should be used to address a given problem. If you have an interesting "How do I .." question you would like to see us answer on the blog please email me and we will do our best to answer them! Watch out for the first post in this series which addresses the problem of "How do I deal with a third party application that has embedded hints that result in a sub-optimal execution plan in my environment?"

Thursday Feb 07, 2008

How do I display and read the execution plans for a SQL statement

Generating and displaying the execution plan of a SQL statement is a common task for most DBAs, SQL developers, and preformance experts as it provides them information on the performance characteristics of a SQL statement. An execution plan shows the detailed steps necessary to execute a SQL statement. These steps are expressed as a set of database operators that consumes and produces rows. The order of the operators and their implentation is decided by the query optimizer using a combination of query transformations and physical optimization techniques.[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.


« July 2016