Tuning Oracle SQL – Just Another Blog?

Author of Oracle Tuning with SQlTXplain. Read about Oracle Tuning here.

This is a blog about Oracle SQL Tuning.

It’s a vast subject and lots of blogs cover it, so what makes this one any different?

My goal, in writing this blog, is to create an organised body of information on tuning Oracle SQL. We’ll talk about concepts like Selectivity and Cardinality, look at tools, tips and techniques and cover things like optimizer parameters in a logical way. I aim to make sure you can always find the knowledge you need, quickly and easily. On the way we’ll spend a fair bit of time talking about one of my favorite tuning tools, SQLTXPLAIN, known to its friends as SQLT.

I love SQLT because it creates order out of chaos. It takes all the random information (which may even seem unrelated) from the SQL tuning world and creates a consistent whole which can then be explored in a structured and repeatable manner.

When you set out to solve a tuning problem you’re just like a doctor approaching a patient. The patient complains of an illness, but you have a whole body to examine, so you start with the basics.

Is the body actually ill or is it complaining unnecessary?
Are there any signs or symptoms which point to an underlying problem.

When it comes to Oracle SQL tuning, SQLT is the body and tuning problems are the disease or underlying condition.

Doctors learn by watching more experienced doctors as they examine patients, order tests and reach a diagnosis. You can learn about SQL Tuning in the same way. You’ve all learned how to collect trace files, get execution plans and so on, but just like a junior doctor you need to spend time on example cases; to see how the patient looks arrive at a diagnosis and work out a ‘cure’.

So, if you have an interesting patient (in the form of a SQLT report) send it to me! I’ll pick at least one every month and review it online as an example. I won’t reveal any data, except as is absolutely necessary to explain the case.

So that’s the aim of this blog, and that’s why this blog is different from all the other blogs on Oracle SQl Tuning. If you’re still reading, but you’re not sure where to begin, I’d suggest you start with What is SQLT. Thanks for reading!


Join Us for an Advisor Webcast

On September 16th at 11am EST, join us for an ADVISOR WEBCAST: Exadata Smart Scan Deep Dive . This 1 hr session will include in-depth topics. Click to find out what’s covered and how to sign up.

On September 16th at 11am EST,  join us for an ADVISOR WEBCAST: Exadata Smart Scan Deep Dive . This 1 hr session will include in-depth topics about Smart Scan such as:

  • When is Smart Scan used,
  • overview of offloading and even
  • how to validate using different cell statistics.

To attend, please register here. http://lnkd.in/b6bndF6

Top Frequency Histograms

The maximum number of bins in an Oracle frequency histogram is 254 and as we all know there are almost always more than 254 bins for frequency distributions. Oracle 12c has introduced a new concept, to help deal with the problem, the Top Frequency Histogram.

I’ve been toying with the idea of writing some more articles on statistics (maybe an additional chapter in the SQLT book), because it is a subject that keeps coming up and and seems to need more explanation than can be found in the documentation.  Just as I’d got round to it, a new version of Oracle was released with new features, so this time I’m going to jump straight into 12c and talk about that instead.

As this small area of Oracle is vast just by itself, I’ll chew small pieces in the order that they come across my desk.

One of the new statistics features that was of interest to me (because I was always fixing tuning problems caused by situations that this feature is designed to fix) was the  Top Frequency Histogram.

Let’s just quickly recap on what a Frequency histogram is. 254 bins (or fewer) that count the number of values of the specific value. So for example if I had only three rows: 1, 2 and 3 I would have 3 bins (labelled 1, 2 and 3) each with a value of 1. If I had four values of 1,2,3,3, I would still have 3 bins with values 1,1,2. So the last bin indicates that “3” was seen twice. Simple enough.

The maximum number of bins in Oracle is 254 and as we all know there are almost always more than 254 bins for frequency distributions (what would the frequency histogram look like for that!). With 12c we have a new concept, the Top Frequency histogram. In this kind of histogram we assume that even though we have more than 254 distinct values, the popular values are still within 254 bins. Or to put it another way, by ignoring some values we can make a histogram which includes the vast majority of actual values thus giving us a better chance to develop a good execution plan. This type of histogram should be an improvement over HEIGHT BALANCED histograms which were always losing popular values.

So when you next see TOP-FREQUENCY in the HISTOGRAM column of DBA_TAB_COL_STATISTICS you should be reassured.

Three Great Things You Didn’t Know About SQLT

SQLT is an amazing tool with a host of user friendly features. This post covers three I’ve found particularly useful; how to use SQLT on a test database where it isn’t installed, how to get rid of histograms and how, when your system is large, you can get the information you need, faster.

English: Labeled (SQL) database icon with shor...
English: Labeled (SQL) database icon with shortcut identification (Photo credit: Wikipedia)

SQLT is a fabulous tool. You probably already know that, or you wouldn’t be reading this!  What you may not know is that in addition to all the wonderful information SQLT provides, it also has a lot of user friendly features. For example:


(1) You Can Get Rid of Those Pesky Histograms


Hidden away, deep in the bowels of SQLT there is a utility to remove the column histogram from one column on a table or partition. You can also cascade this to all partitions. At the bottom of this script is a call to


exec delete_column_hgrm
(p_owner=> ‘<OWNER>’,
p_tabname=> ‘<TABLE_NAME>’,
p_colname=>'<COLUMN NAME>’,
p_cascade_parts=>'<TRUE| FALSE>’,
p_no_invalidate=>'<TRUE| FALSE>’,
p_force=>'<TRUE| FALSE>’);


You can find this script in <SQLT Install area>/utl/xgram and it is called sqlt_delete_column_hgrm.sql. There are cases where you are experimenting with a test case where this may be useful to determine if you can ‘fix’ an execution plan.


(2) I Don’t Want It Fast! I Want It XPRESS


When collecting information, SQLT can sometimes take a long time. This is because there is a lot of information to collect. On very large systems there is so much information to collect, SQLT can take hours to run.  If you really can’t wait or don’t have the patience, you may need the express version of the common methods. In version of SQLT (March 2013) there are a couple of utilities called




These are the EXPRESS versions of the XTRACT and XECUTE routines, which execute the scripts in sqltcommon11.sql


The command to call this is


@sqltxprext <sql-id>


Please note in some early versions of SQLT you need to change this line in sqltcommon11.sql


EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, ‘NONE’);




EXEC ^^tool_administer_schema..sqlt$a.set_sess_param(‘ash_reports’, 0);


(3) You don’t need SQLT?


If you want to build a test case to display some behavior of your SQL your first option is to use




which you can get from the SQLT zip file. This is described in detail in Chapter 11 of my book, but the important thing to know is that you need the installation of SQLT on your test database to be able to build this test case. Then you can use all the utilities built in to SQLT to explore the execution plan. If you need to move your test case to a database which does not have SQLT installed then you are stuck. There is no way to install the TC file into this database. Luckily Carlos (at the request of many support engineers and development) provided a way to do this. There is an alternative file




The TCX file (Test Case Xpress) is for precisely this purpose. It can be used to install the test case into your disposable database, WITHOUT SQLT being installed before you start. It does not require the SQLT repository or schema objects (functions and so on) to be able to do its work. Once you unzip the TCX file, you need only have access to SYS on your disposable database and run


SQL> @install


This does all the usual magic required. There are no other steps. The SQL is available, the schema is created and the statistics are imported. A truly useful feature.


There are many other cool features in SQLT and I hope to document at least some of them in future posts, so stop  by often or sign up for posts by email.


Oracle Database Statistics Gathering

What do you know about Oracle Database statistics gathering? What would you like to know? There is a section in the Database Performance Tuning Guide, but how many people take the time to read it? And, once you know what the various procedures are and what they do, how do you decide which ones to use and when? I’ve seen a lot of tuning problems that are statistics related, and I’d like to explore the area more thoroughly, so I’m asking for your help.

It always surprises me that statistics doesn’t have its own manual in the Oracle documentation set. In the current version of the Oracle Database Performance Tuning Guide, (which I’ve mentioned on more than one occasion should be mandatory reading)  Chapter 13 is called  “Managing Optimizer Statistics”,  but let’s be honest,  how many people have actually read this section? Life as a DBA these days is far too busy and hectic to have time to read an Oracle Manual. A much better choice is to read a well targeted book on the subject.

For example, in the first paragraph of this section of the manual it describes the difference between


Do you know what the practical difference is between these two? My memory was jogged because I had a problem this week with a statistics gathering procedure that was running out of time in the maintenance window and was leaving some crucial tables without statistics and that lead to other performance problems. The solution was  either to run a one off procedure to gather what you want and do that manually or to adjust the percentage being collected and thus allow the automated job to complete in the time allocated, or possibly a combination of both. Too often I see automated statistics being collected with no other thought for the statistics gathering process. When to collect, how to collect, what to do when the statistics are not as good as you expected. There are a few paragraphs in Chapter 13 of the manual, but by no means enough to give you guidance. I’m often left thinking

“yes that’s what that procedure CAN do, but should I use it?”

It’s like being given all the pieces of a jigsaw and being told that it makes a really nice picture of something, but not what the something actually is. Yes, I like to know what the different parameters of all the dbms_stats procedures do, but which ones should I use and under what circumstances? There is no guidance on this.

It got me thinking there was a need for guidance in this area. There are over 80 procedures in the DBMS_STATS package. I’m sure that by now, everyone has stopped using ANALYZE (except for older systems and for verifying structures) but even a common procedure such as DBMS_STATS.GATHER_TABLE_STATS has some obscure options. For example the method_opt parameter is often used like this

method_opt=> for columns size 1

But there are many many other choices. And how about the granularity option which can have a value of ‘ALL’, ‘AUTO’, ‘GLOBAL’ etc? Given this complexity,  how many of you choose to set everything on AUTO and blame Oracle when it doesn’t work out?

I mention all this on Statistics because my next book is going to be on this very subject, and it would be really useful to know if anyone had any stories they’d like to share or suggestions on topics  in this area that need to be covered.

Here’s my list of  tentative chapter headings, if you’ve any ideas or suggestions for the book, just post a comment and I’ll do my best to reply.

  • What are statistics
  • Different kinds of statistics (Object, Dictionary, System)
  • What Automation does for you
  • How automation can go wrong
  • Granularity
  • Partitions
  • Setting preferences
  • When to gather less than everything
  • When to check results against SQLT
  • How to export and import statistics
  • Histograms
  • Pending Statistics
  • Complex statistics
  • Strategies for Statistics gathering

Severity One Killer

When there’s a major change in the performance of your database the culprit can usually be found by looking at any changes made around the time that performance degraded, but what if nothing has changed? What do you do then? There are lots of options, but only one that will turn you into a hero!

Making good use of SQLTXplain can turn a DBA into a hero

I’m a perfectionist as much as the next person (maybe more so), but I’ve been a DBA long enough to know that when management want results, they do not tolerate perfectionists. Picture this scenario; database performance is through the floor, key jobs are not completing on time, but nothing has changed – obviously, Do you

(a) Get volume 3 of the tuning guide and refer to the section on SQL Performance
(b) Use Enterprise Manager / Cloud Control to tune the SQL
(c) Have a meeting to discuss the possible causes and solutions
(d) Call Oracle Support and log a bug in their optimizer
(e) Call Oracle Support and complain about the performance?
(f) Sit at your terminal / PC and madly type commands in the hope that something will change, while your manager glares at you?

The correct answer, of course, is all of the above, but if I had to choose one option I’d choose (e). Then at least you have a team on your side, which means you don’t need to read the manual , option (a),  nor do you need to use EM, as alternatives are available, a meeting to discuss options is not needed as you’ll be speaking to an expert in support. A bug in the optimizer is an outside possibility, but if it is a bug, support will coordinate that for you, and of course they’ll ask you to collect information which covers option (f).

One of the best tools for dealing with bad performance is SQLT and it has embedded in it a nice routine which ‘captures’ the performance of a particular SQL ID and imposes it on a SQL. So,  if your SQL has regressed – which is code for the performance is now worse than it was – you can flash back the query to a previous execution plan with a SQL Profile. I like to call this technique a Severity 1 killer. It changes a severity 1 tuning problem into a nice exercise in finding out why the performance regressed. Use SQLT with the following steps

SQL> exec sqltxplain.sqlt$a.set_param(‘custom_sql_profile’,’Y’)’
SQL> @sqltprofile abcde nnnnnnnnnn

This enables the required feature then creates a profile to attach to the SQL. This sets the plan back to the plan as it was at some previous time. You choose the time of this by selecting the appropriate plan hash value. The values in the second command above are SQLT ID (abcde) and the plan hash values (nnnnnnnnnn). You can even transfer these SQL Profiles from one system to another. See MOS note 457531.1, which covers this procedure.

This turns you into a hero, who then has time to analyze the root cause.  Management are then your best friends for ever.  It’s great being a DBA on the front line, but you need the tools to do the job and the ability to cope with pressure.

Good luck and remember, support are your friends.