Database, SQL and PL/SQL

On Query Tuning

Our technologist tunes queries by helping the optimizer optimize.

By Tom Kyte

November/December 2014

I am frequently asked to tune a query. I don’t have a clear approach, and I tend to fumble around and try this and that and hope that something works. Is there one clear-cut way to tune a query?

This must be the most asked question in discussions involving SQL databases. If there were a 10-step tuning program you could always follow, someone would certainly have automated that process—and called it the optimizer! Tuning—finding the optimal way in which to execute a query—is what the Oracle Database optimizer is tasked with doing, but often it cannot fully achieve this goal because

  • It lacks sufficient information because sufficient information does not exist—yet. For example, the optimizer may not have sufficient information to correctly estimate cardinalities (estimated row counts), and when that happens, inefficient query plans can happen very readily.
  • It lacks sufficient information because it does not understand the data. Constraints have been withheld from the optimizer in a misguided attempt to place all logic outside the database. I’ve talked about this before here.
  • Its plan is the most efficient possible one—there is no better plan—but there is a problem with the organization of the data. See “On Clustering Factor and Validating Keys” for examples of this.
  • The query is performing work—retrieving columns and doing computations—it does not need to do. This frequently happens as the result of using a generic one-size-fits-all view or views of views of views.

In my experience, the vast majority of poorly performing execution plans can be blamed on the first two reasons. This article looks at the first reason in more detail.

In general, when I’m asked to tune a query, the very first things I want to know are

  • The data model. I want to know everything I can know about the data itself: all tables, indexes, constraints, and assumptions.
  • The question being asked of the data. I’ve found more than once that a complex query I’m asked to look at doesn’t even answer the question being asked. Sometimes, instead of tuning a query, you might want to start from scratch with a self-developed query—one that specifically answers the question.

Those are the first bits of information you need to collect and understand. Once you have the definitive query you are going to tune—either the original query or a newly formed one—you need to understand whether the optimizer has sufficient information to correctly estimate cardinalities. Let’s take a look at this in more detail.

Estimating Cardinalities

You might say that estimating cardinalities is where a good query plan goes bad. If the optimizer incorrectly guesses how many rows will flow out of a step of a plan, the result will likely be a bad query plan. For example, if I gave you the simple query SELECT * FROM T WHERE X=5 and told you that table T has 1 million rows, column X has five distinct values, and column X is indexed, what query plan would you come up with? By default, that is the amount of information the optimizer has to work with in most cases. It knows the number of rows in the table, the number of distinct values in the column, and whether the column is indexed. It knows some other things, too—the clustering factor of the index, the number of blocks in the table, the average row length, and so on—but those statistics do not help it guess how many rows the query will work with. In this case, the number of rows in the table and the number of distinct values of column X will be the most important facts for developing the query plan.

So again, what query plan would you come up with? The math you would do in your head would probably involve trying to figure out how many rows will be retrieved from table T. That is, you’d try to estimate the cardinality of the WHERE clause—WHERE X=5. You would likely take the 1,000,000 rows in the table and divide that number by 5 to guess how many rows that WHERE clause will return on average. If there are five distinct values of X and you assume that X is uniformly distributed (because you have to make some assumptions when you don’t have any other information), you would guess that around 200,000 rows would be returned—20 percent of the table. It is highly likely that you would decide to use a full table scan for that query, as would the optimizer in most cases. But what if, after running the query, you observed that the query returned only 5 rows? It is obvious that you should have instead used the index to get 5 rows, but because you guessed 200,000 rows, you made the wrong choice.

In this clear-cut example, you—and the optimizer—“got it wrong” due to insufficient information. The optimizer had no way of knowing that right now X=5 would return only five records. The question then becomes what you can do to tune this query. And the answer is manifold and version-dependent. Typically this list will include the following tuning suggestions:

  • Supply more statically gathered statistics. A histogram on column X would be appropriate in this case. This approach works in all Oracle Database versions, although the types of statistics you can gather have changed dramatically in Oracle Database 11g with the addition of extended statistics.
  • Use dynamic sampling. This enables the optimizer to validate its guesses when it needs to (see “On Dynamic Sampling”). This approach works in Oracle9i Database Release 2 and above.
  • Generate a SQL profile. A SQL profile enables the optimizer to aggressively look at a query, determine what the real cardinality estimates should be, and then remember this information in the data dictionary. The next time the query is hard-parsed, the optimizer will generate a plan with these adjusted cardinalities. This approach works in Oracle Database 10g and above.
  • Run the query again! In some cases, starting in Oracle Database 11g, a feature called Cardinality Feedback (“On Promotion, Restriction, and Data Loading”) causes the query to be reoptimized during the second execution, using the actual observed values—instead of the optimizer’s guesses—as cardinality estimates. In Oracle Database 12c, this facility has been extended via SQL plan directives, making it possible for the optimizer to recognize that it needs additional information and to automatically gather the information the next time statistics are gathered, making the “fix” permanent.

Let’s look at this simple case step-by-step. It shows you how to determine whether the optimizer made a miscalculation in the cardinality step, and then, after I gather additional statistics, you’ll see what happens when the optimizer gets a better cardinality estimate.

I’ll start by building a table with the skewed distribution of the data I am interested in, as shown in Listing 1.

Code Listing 1: Creating table T and skewed data

SQL> create table t
  2  as
  3  select case when mod(rownum,200000) = 0 then 5
  4              else mod(rownum,4)
  5          end X,
  6         rpad( 'x', 100, 'x' ) data
  7    from dual
  8  connect by level <= 1000000
  9  /
Table created.
SQL> create index t_idx on t(x);
Index created.
SQL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
SQL> select x, count(*)
  2    from t
  3   group by x
  4   order by x
  5  /
         X   COUNT(*)
———————————  ———————————
         0     249995
         1     250000
         2     250000
         3     250000
         5          5

In this table T, WHERE X=5 will return 5 rows; there is definite skew in this data. I would like to use the index on column X for X=5 but probably not for any other value (0, 1, 2, 3), because queries for those values would return hundreds of thousands of rows. However, when I gather plan statistics and run the queries in Listing 2, I can see that the optimizer made the mistake we all would make, given the information available.

Code Listing 2: Statistics gathered, but optimizer doesn’t see the skew

SQL> select /*+ gather_plan_statistics */
  2         count(data)
  3    from t
  4   where x = 5;
COUNT(DATA)
————————————————
          5
SQL> select *
  2    from table(
  3          dbms_xplan.display_cursor( format=> 'allstats last' )
  4          )
  5  /
PLAN_TABLE_OUTPUT
——————————————————————————————————
SQL_ID  cdwn5mqb0cpg1, child number 0
——————————————————————————————————
select /*+ gather_plan_statistics */
count(data)
from t
where x = 5
Plan hash value: 2966233522
———————————————————————————————————————————————————————————————————————————
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
———————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.08 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.08 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    200K|      5 |00:00:00.08 |
———————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————————
   2 - filter("X"=5)
20 rows selected.

The E-Rows (estimated rows) column shows that the optimizer thought it would get 200,000 rows. The A-Rows (actual rows) column shows that it got only 5. (I got these two columns in my result by using the GATHER_PLAN_STATISTICS hint in the query. This hint doesn’t affect the query plan at all; it affects just the generated code executed at runtime.) The runtime code is instrumented more than usual; it gathered runtime statistics while it was executing. I got this information by using DBMS_XPLAN to display the query plan with the ALLSTATS LAST format. This is a very easy way to quickly see if the optimizer is way off. In this case, I see a huge difference between the actual and estimated rows when the filter X=5 is applied. Using my knowledge of the data (or just running a few queries to learn about the data), I can figure out pretty easily that the optimizer is missing some information.

I fill in the blanks for the optimizer:

SQL> select histogram
  2    from user_tab_columns
  3   where table_name = 'T'
  4     and column_name = 'X';
HISTOGRAM
—————————————
NONE
SQL> exec dbms_stats.gather_table_stats
( user, 'T', no_invalidate=>false );
PL/SQL procedure successfully completed.
SQL> select histogram
  2    from user_tab_columns
  3   where table_name = 'T'
  4     and column_name = 'X';
HISTOGRAM
—————————————
FREQUENCY

Note: I used the NO_INVALIDATE parameter to DBMS_STATS to ensure that the query I am looking at would be hard-parsed—optimized—on the next execution. By default, DBMS_STATS will not invalidate a cursor immediately but, rather, will invalidate affected cursors slowly over time in the background. You would not typically use NO_INVALIDATE.

Before I gathered table statistics for the second time, there was no histogram on column X. Now, statistics having been gathered, there is a histogram in place. The histogram gives the optimizer much more information about column X in the table. (For more information on histograms and statistics, I recommend this paper). With the histogram in place, I see a big difference in the optimizer plan and query execution, as shown in Listing 3.

Code Listing 3: Better query plan after skew is identified

SQL> select /*+ gather_plan_statistics */
  2         count(data)
  3    from t
  4   where x = 5;
COUNT(DATA)
————————————————
          5
SQL> select *
  2    from table(
  3          dbms_xplan.display_cursor( format=> 'allstats last' )
  4          )
  5  /
PLAN_TABLE_OUTPUT
——————————————————————————————————
SQL_ID  cdwn5mqb0cpg1, child number 0
——————————————————————————————————
select /*+ gather_plan_statistics */
count(data)
from t
where x = 5
Plan hash value: 1789076273
————————————————————————————————————————————————————————————————————————
| Id | Operation                    | Name  | Starts | E-Rows | A-Rows |
————————————————————————————————————————————————————————————————————————
|  0 | SELECT STATEMENT             |       |      1 |        |      1 |
|  1 |  SORT AGGREGATE              |       |      1 |      1 |      1 |
|  2 |   TABLE ACCESS BY INDEX ROWID| T     |      1 |    182 |      5 |
|* 3 |    INDEX RANGE SCAN          | T_IDX |      1 |    182 |      5 |
————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
————————————————————————————————————————————————————————————————————————
   3 - access("X"=5)
21 rows selected.

The plan is now using an index range scan instead of a full table scan, as you would expect for a small number of rows. Note that the E-Rows column value is not perfect, though. The guess made by the optimizer will rarely, if ever, be 100 percent dead-on, and I would not expect it to be. What is important is that the number is close to being correct. As long as the guess is close enough, the optimizer will choose the right access path, join order, and so on.

One thing you might be wondering about, however, is how just gathering statistics a second time made any difference. And why did the optimizer know to generate histograms the second time but didn’t know this the first time? The answers lie in the fact that the database has been spying on me—watching my predicates and remembering them in the data dictionary. When I ran the query with X=5 in it, the database remembered that, and the next time it gathered statistics, it took the facts that X is used to estimate cardinalities in predicates and that the data values in X are skewed and it automatically generated histograms. This will happen only if you leave the METHOD_OPT parameter in the DBMS_STATS package call at its default value. (For a full example of this feature and information on how it works, see “On Joins and Query Plans.”)

This was a rather simple example: a single column predicate on a column with skewed data. What about a more complex case? What about a multicolumn predicate? Suppose you have a predicate on a table such as WHERE X = 5 and Y = 10. How do you go about estimating a cardinality for that combination? The optimizer can have a lot of information on X (including histograms) and it can have a lot of information on Y, but it doesn’t know anything by default about X and Y at the same time. In real life, there are often correlations between attributes in a table. Consider a car make and a car color. Do you often see pink Audis? How about lime-green BMWs? Probably not, but you might see a pink or lime-green Volkswagen Beetle. In a table containing information about shoes—along with demographic information such as gender—there is almost certainly a correlation between gender and shoe type. There are too many examples of correlated attributes to list.

So, how can you help the optimizer out here? How can you help it get a better estimated cardinality? Back to my list of tuning suggestions, you could use dynamic sampling, SQL profiles, Cardinality Feedback, or static statistics—known as extended statistics in Oracle Database 11g and above.

Here’s a demonstration of the static statistics method:

I start by creating a table with some multicolumn skewed data. In the new table T, FLAG1 and FLAG2 are columns that have only two values: Y and N. The way I’ve generated the data, however, ensures that almost all rows are created with FLAG1 <> FLAG2 (FLAG1 is not equal to FLAG2) and very few rows are created where FLAG1 = FLAG2. FLAG1 and FLAG2 values are each uniformly distributed—50 percent of FLAG1 and FLAG2 values are Y, and the other 50 percent are N. However, when you look at the values together—as a pair—a very definite data skew appears, as shown in Listing 4.

Code Listing 4: Creating a new table T and a new data skew

SQL> create table t
  2  as
  3  select case when mod(rownum,2) = 0 then 'Y' else 'N' end flag1,
  4         case when mod(rownum,2) = 0
  5              then case when mod(rownum,1000) = 0 then 'Y' else 'N' end
  6              else case when mod(rownum,1000) = 1 then 'N' else 'Y' end
  7          end flag2,
  8         rpad( 'x', 100, 'x' ) data
  9    from dual
 10  connect by level <= 1000000
 11  /
Table created.
SQL> create index t_idx on t(flag1,flag2);
Index created.
SQL> exec dbms_stats.gather_table_stats( user, 'T' );
PL/SQL procedure successfully completed.
SQL> select flag1, flag2, count(*)
  2    from t
  3   group by flag1, flag2
  4   order by flag1, flag2
  5  /
F F   COUNT(*)
— —   ———————————
N N       1000
N Y     499000
Y N     499000
Y Y       1000

As you can see, in the new table T, out of 1,000,000 records, only 2,000 are such that FLAG1 = FLAG2. For the vast majority of the records, FLAG1 <> FLAG2. But pretend for a moment that you are the optimizer and are asked to calculate how many rows would come back from a predicate WHERE FLAG1 = ‘N’ AND FLAG2 = ‘N’. What math would you go through to estimate that? You have all the default statistics: number of rows in the table, number of distinct values in every column, and so on. Now what estimated cardinality would you come up with? (Remember, you are the optimizer! You did not see the table getting created.)

Just about the only logical thing you could do is figure out what the probability of WHERE FLAG1 = ‘N’ being true is and what the probability of WHERE FLAG2 = ‘N’ being true is, and—assuming that they are independent of each other—simply multiply those two probabilities together. That is, you would use basic “math statistics” to compute the probability that both events would be true. Now because FLAG1 has only two values, the probability of FLAG1 = ‘N’ being true is 50 percent. Even if you had a histogram on FLAG1, this would be the case, because FLAG1 is uniformly distributed. The same is true for FLAG2 = ‘N’—there is a 50 percent probability. So the odds are that FLAG1 = ‘N’ AND FLAG2 = ‘N’ is going to be 50 percent times 50 percent, or 25 percent. This is borne out by the optimizer and demonstrated in Listing 5.

Code Listing 5: Optimizer doesn’t see the skew

SQL> select /*+ gather_plan_statistics */
  2         count(data)
  3    from t
  4   where flag1 = 'N'
  5     and flag2 = 'N'
  6  /
COUNT(DATA)
————————————————
       1000
SQL> select *
  2    from table(
  3         dbms_xplan.display_cursor( format=>'allstats last' )
  4         )
  5  /
PLAN_TABLE_OUTPUT
——————————————————————————————————
SQL_ID  9zttsauwykda8, child number 0
———————————————————————————————————————————————————————
select /*+ gather_plan_statistics */        count(data)   from t  where
flag1 = 'N'    and flag2 = 'N'
Plan hash value: 2966233522
———————————————————————————————————————————————————————————————————————————
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
———————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.11 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.11 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    250K|   1000 |00:00:00.11 |
———————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————————
   2 - filter(("FLAG1"='N' AND "FLAG2"='N'))
20 rows selected.

The E-Rows column value is 250,000 rows—25 percent of the data—in Listing 5. The optimizer overestimates the number of rows FLAG1 = FLAG2 will return by many orders of magnitude; conversely, it will underestimate the number of rows FLAG1 <> FLAG2 will return as well.

I regather statistics—as I did last time—as shown in Listing 6.

Code Listing 6: Statistics have been regathered, but optimizer doesn’t see the skew

SQL> exec dbms_stats.gather_table_stats( user, 'T', no_invalidate=>false );
PL/SQL procedure successfully completed.
SQL> select /*+ gather_plan_statistics */
  2         count(data)
  3    from t
  4   where flag1 = 'N'
  5     and flag2 = 'N'
  6  /
COUNT(DATA)
———————————————
       1000
SQL> select *
  2    from table(
  3         dbms_xplan.display_cursor( format=>'allstats last' )
  4         )
  5  /
PLAN_TABLE_OUTPUT
———————————————————————————————————————————————————————
SQL_ID  9zttsauwykda8, child number 0
———————————————————————————————————————————————————————
select /*+ gather_plan_statistics */        count(data)   from t  where
flag1 = 'N'    and flag2 = 'N'
Plan hash value: 2966233522
———————————————————————————————————————————————————————————————————————————
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   |
———————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.11 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.11 |
|*  2 |   TABLE ACCESS FULL| T    |      1 |    249K|   1000 |00:00:00.11 |
———————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
———————————————————————————————————————————————————————————————————————————
   2 - filter(("FLAG2"='N' AND "FLAG1"='N'))
20 rows selected.

There’s no real change here; no new statistics were generated, because FLAG1 and FLAG2 values are not skewed. DBMS_STATS looks only for single-column skewed values currently, although SQL plan directives change that a bit in Oracle Database 12c. (See this paper for some insight into SQL plan directives.) What I need are statistics on FLAG1 and FLAG2—the pair of columns—not just on each column individually. Starting with Oracle Database 11g, I can do that with extended statistics. With these new statistics, I can gather statistics on multiple columns simultaneously as well as expressions and functions on columns. This gives the optimizer information about these column groups and expressions. For example, if I gather these new statistics on FLAG1 and FLAG2:

SQL> begin
  2    dbms_stats.gather_table_stats(
  3    user, 'T',
  4    method_opt =>
       'for columns(flag1,flag2)',
  5    no_invalidate => false
  6    );
  7  end;
  8  /
PL/SQL procedure successfully completed.

the next time I run a query involving FLAG1 and FLAG2 in the predicate—in Listing 7—I’ll see very different results.

Code Listing 7: Statistics are gathered on FLAG1 and FLAG2, and the optimizer sees the skew

SQL> select /*+ gather_plan_statistics */
  2         count(data)
  3    from t
  4   where flag1 = 'N'
  5     and flag2 = 'N'
  6  /
COUNT(DATA)
————————————————
       1000
SQL> select *
  2    from table(
  3         dbms_xplan.display_cursor( format=>'allstats last' )
  4         )
  5  /
PLAN_TABLE_OUTPUT
————————————————————————————————————————
SQL_ID  9zttsauwykda8, child number 0
——————————————————————————————————————————————————————
select /*+ gather_plan_statistics */        count(data)   from t  where
flag1 = 'N'    and flag2 = 'N'
Plan hash value: 1789076273
—————————————————————————————————————————————————————————————————————————
| Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |
—————————————————————————————————————————————————————————————————————————
|   0 | SELECT STATEMENT             |       |      1 |        |      1 |
|   1 |  SORT AGGREGATE              |       |      1 |      1 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |      1 |   1173 |   1000 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |      1 |   1173 |   1000 |
—————————————————————————————————————————————————————————————————————————
Predicate Information (identified by operation id):
—————————————————————————————————————————————————————————————————————————
   3 - access("FLAG1"='N' AND "FLAG2"='N')
21 rows selected.

Again, the cardinality estimate—the E-Rows value—is not perfect, but it is closer than close enough, and that is what we are all looking for.

To reiterate, in my experience, this is how you want to approach “tuning” a query, in this order:

  1. Make sure you understand the schema and the data.
  2. Verify that the estimated cardinalities are at least “close.” You need to understand the data to verify them, or you can use the GATHER_PLAN_STATISTICS (or real-time SQL monitoring in Oracle Enterprise Manager) to verify the estimated cardinalities. If the cardinalities are very far off, look at what statistics might be missing or “wrong” (way out of date in regard to how the data is now) and consider using SQL profiles, dynamic sampling, and so on to produce better cardinality estimates.
  3. Ensure that constraints are implemented. The optimizer uses all of them to figure out how to best approach your query.
  4. Consider how the data might be organized on disk. That can have a major impact on your query’s performance.
Next Steps

 ASK Tom
Tom Kyte answers your most difficult technology questions. Highlights from that forum appear in this column.

 READ more Tom

 DOWNLOAD Oracle Database 12c

 LEARN more about Oracle Database 12c

 FOLLOW Tom on Twitter

FOLLOW Oracle Database
 on Twitter
 on Facebook

DISCLAIMER: We've captured these popular historical magazine articles for your reference. Links etc contained within these article possibly won't work. These articles are provided as-is with no guarantee that the information within them is the best advice for current versions of the Oracle Database.