Expert Oracle SQL: Optimization, Deployment, and Statistics (2014)
PART 1. Basic Concepts
CHAPTER 5. Introduction to Tuning
This chapter introduces the first of the two major themes of this book: tuning a SQL statement. The systematic process described in this chapter is designed to identify an approach to the execution of a SQL statement that ensures that the SQL statement runs in a reasonable amount of time without undue consumption of resources. At this stage I am just going to give you an overview of the process. I will go into a lot more detail in Part 4 of this book.
It is important to understand that the techniques used to identify the approach to getting a SQL statement to run fast may be different from the techniques used to implement or enforce that approach in a production system. We will start to look at deployment in the next chapter.
Understanding the Problem
In so many aspects of life, properly understanding the problem you are trying to solve is most of the work needed in finding a solution to that problem. Tuning a SQL statement is a prime example. The more you understand about the tuning problem the easier it will be to make tuning decisions with confidence.
Understanding the Business Problem
This is a technical book, not a training manual on management technique, but gaining clarity on the business problem is so important that I have to spend a short while discussing it. When you are first asked to look at a performance issue with one or more SQL statements it is very tempting to dive in straight away. But you should take a breath first.
Suppose you are called out of bed because a query in an overnight batch is overrunning. The first question you need to have answered is “So what?” You probably want to express the question a little more politely, but that is the actual question you need answered. What are the business implications of the query running late? Is there a Service Level Agreement (SLA) that needs to be met, and what happens if it is breached? When will the SLA be breached, or has it already been breached?
Understanding the business context is critical to your whole approach to the tuning exercise for several reasons. For one thing, you need to balance the need to get the query done with the need to collect diagnostic information for Root Cause Analysis (RCA), itself important in preventing recurrences of this issue.
A second reason that understanding the business problem is important is so that you can prioritize the allocation of resources. You might observe that the system is extremely busy running other processes. How important are these other processes? Can they be killed? The more severe your problem the more palatable drastic measures will be to the business community and vice versa. On the one hand, sometimes billions of dollars may be on the line, sometimes even human life might be at stake. In critical situations like these you might want to get one or two other people involved. On the other hand, I have been woken at night to discover that there were absolutely no consequences to the query running for a long time; I was just advised “in case I wanted to know.” It is best to get a handle on this sort of thing before you get started.
Remedial performance tuning of production queries is not the only case when understanding the business problem is important. If a developer asks you for advice about a query that runs for 10 minutes you should also start with politely phrased variants of the “so what” question. Is the query intended for interactive use or part of a batch? What is the expectation of the business community? If the query runs once a year as part of a year-end overnight batch then maybe it isn’t important—unless last year’s year-end batch was a disaster of course!
The development scenario leads me to a third reason why it is important to understand the business problem. And that is to know when to stop! Suppose you have an SLA of 75 minutes and you have tuned your query so that it consistently runs in 15 minutes. You can see that with a little more work you might be able to bring the elapsed time down to 12 minutes. Is it worth it? Almost certainly your time would be best spent elsewhere, as the consumers of your service will see little benefit from the extra work.
Getting a complete and authoritative understanding of the business context can be quite difficult and in many cases hopelessly impractical. You can’t spend 30 minutes getting a clear understanding of a problem that might take you 10 minutes to fix; your “professionalism” may itself precipitate an SLA Service Level Agreement (SLA) breach! However, the more urgent the issue appears and the more panic appears to abound, the more need there is to get a clear understanding of what the real issue is so that resources are correctly focused.
A considerable amount of judgment is required here, and we all get better with experience. I will close this topic with a little tip on status reports: you may want to write a short email about what you have been asked to do and why. Sending a copy of such an email to various key people is a good way to give those key people visibility of the assumptions you are being asked to make and provides a useful audit trail.
Understanding the Technical Problem
The technical problem may seem obvious: make the SQL statement run fast. Right? Once again, you need to take a breath.
I once spent two days tuning a query that never finished only to discover that the query was incorrect! The developer was new to the ANSI syntax that was a coding standard in her team and had specified LEFT JOIN each time she meant RIGHT JOIN and vice versa! I could have kicked myself for not checking, and I would like to say that I never made that mistake again, but I can’t! Quite recently a developer deliberately altered a SQL statement before giving it to me to tune. The change was made with the best of intentions. The data on the test system I was given to work on was not representative of production, and the actual production query ran fine on that test system. The query was altered in good faith to reflect the test data, but the change completely changed the options for tuning.
Making sure that the statement you have been asked to look at (or have yourself written) is correct is not the only technical thing you need to get straight. You also need to understand whether the SQL statement is static or dynamic. In other words, you need to know if the statement will change every time it is run or not. You need to understand if this is the only statement that needs tuning or if there are hundreds of similar statements. You need to understand what else is going to be running at the same time so that you can avoid contention and allocate resources appropriately. There are probably other things that might trip you up so don’t take this as an exhaustive list.
Understanding the SQL Statement
In Chapter 1 I introduced factored subqueries because I feel that being able to read a SQL statement and understand what it does is critical to tuning. It may be possible to rewrite a query completely to give precisely the same results. You remember the change from Listing 1-1 to Listing 1-2? Such transformations are very difficult to see if you don’t have the faintest idea what the SQL statement is trying to do. Perhaps there is an ORDER BY clause in the query. Do you know why it is there? Does it fulfill a genuine requirement, or was it put there just because the developer could more easily verify that the output was correct? The ubiquitous “SELECT COUNT (*) FROM” queries are often used just to determine the existence of matching rows, and the exact count is irrelevant. If you wrote the query yourself then hopefully you know the answers to these questions, but if not then spending some time trying to understand what the query does and why will usually speed up the whole tuning process.
Understanding the Data
It is all very well getting your query to run fast on a test system, but is the test data representative of what you will find on the production system? Are the data volumes on your production system likely to grow and, if so, at what rate? I will admit that the specific questions about data may be difficult to frame at the very start of a tuning exercise. Until you have some understanding of what the performance issues are and what the possible execution plans are, it may be difficult to know which particular aspects of your query are sensitive to data volume or data distribution. However, once you discover some aspect of your query that is particularly sensitive to data volume or data distribution it may be best to pause and ask some questions.
Let me give you a real-life example of what can go wrong when you don’t check your assumptions about the data. A regulatory requirement resulted in a table that I shall call AUDIT_TRAIL being added to a database. The rows in in the AUDIT_TRAIL table were populated with columns like USER_ID, PRODUCT_ID, and so on. The software that added rows to the table was tested, and half a dozen rows appeared in the AUDIT_TRAIL table. A report was then written. It took rows from the AUDIT_TRAIL table and joined it with USERS and PRODUCTS tables so that the meaningless USER_ID and PRODUCT_ID columns were replaced by more readable USER_NAME and PRODUCT_NAME columns. The report worked fine!
The very first day that the application was deployed in production some concerns were raised about the report, which seemed to be running a little longer than expected. The second day the concerns were voiced somewhat more strongly, as the report was now taking an unacceptably long time. The reason why the report was taking so long was that something like 100,000 rows were being added to the AUDIT_TRAIL each day! Who knew? Apparently not the tester of the report, who had signed off on the testing after reporting on about six rows!
Understanding the Problem Wrap Up
This discussion of the various aspects of a tuning problem should not be treated either as a mandatory checklist or as a comprehensive list of questions. The reason I have spent this long discussing the issue is to highlight the fact that no SQL tuning assignment is ever as simple as it first appears. If you make a premature start to technical analysis you are likely to end up making false assumptions and delivering a solution that is inapplicable or unsuitable for other reasons. If you do end up delivering a solution that your consumer is happy with you will do so more quickly and efficiently if you ask the right questions first. Finally, I would like to emphasize that once you have started your analysis you should always be wary of making deductions from your observations that are based on unverified assumptions. Don’t be afraid to pause and check your facts.
Analysis
After getting a thorough understanding of the problem statement, the next stage in the tuning process is to analyze the behavior of the SQL statement. This is part of an iterative process; you analyze, make some changes, and reanalyze.
Running the Statement to Completion
Although you can get some understanding of how a SQL statement is working as it is running by using the SQL performance monitor, you can’t really claim to have the full picture until you get the SQL statement to finish. And if you want to be able to try out a few things, you need to get the statement to finish in 5 to 10 minutes at most. Otherwise you will either be twiddling your thumbs for far too long or you will end up having to work on something else at the same time and end up losing track of where you are.
Suppose that a query has been running for two minutes and you look at a SQL performance monitor report and see that a full table scan of table T1 is in progress and that it is only 1% complete. My oh my. You didn’t expect a full table scan to take so long. At this rate it will be 200 minutes before the full table scan completes, and who knows how much longer before the statement as a whole finishes. As a temporary measure you need to reduce the amount of data you are selecting from the table. Listing 5-1 shows one of the numerous methods that can be employed to reduce the data set on which you are operating.
Listing 5-1. Use of the SAMPLE keyword to reduce the size of a dataset
CREATE TABLE t1
AS
SELECT 1 c1
FROM DUAL
CONNECT BY LEVEL <= 100;
CREATE TABLE t2
AS
SELECT 1 c2
FROM DUAL
CONNECT BY LEVEL <= 100;
SELECT *
FROM t1, t2
WHERE t1.c1 = t2.c2;
WITH q1
AS (SELECT *
FROM t1 SAMPLE (5) SEED(0))
SELECT *
FROM q1 t1, t2
WHERE t1.c1 = t2.c2;
DROP TABLE t1;
DROP TABLE t2;
The first query in Listing 5-1 shows a typical query that joins two tables. The second query uses the SAMPLE keyword to select, in this case, 5% of the data in T1 in an attempt to get some kind of result in a reasonable amount of time. The SEED keyword is used to generate reproducible results. Of course, at some point you will have to remove this SAMPLE keyword, but only once you have figured out why the query is taking so long.
Analyzing Elapsed Time
Once the statement has finished, you need to get a breakdown of that elapsed time. In Chapter 4 I explained how to ensure that the view V$SQL_PLAN_STATISTICS_ALL was populated with information from the runtime engine about individual operations in an SQL statement. This is the main source of information for analysis.
When the Elapsed Times Doesn’t Add Up
Sometimes you will look at the data from V$SQL_PLAN_STATISTICS_ALL and find that the total amount of elapsed time is nowhere near the length of time you spent waiting for the query to finish. Here are several reasons why this might be the case:
· Recursive SQL. If an SQL statement under test includes calls to functions that in turn include calls to other SQL statements, then the time spent executing these recursive SQL statements will not be included in the reported time for the statement under test. If a DML statement ends up invoking one or more triggers, then the time spent executing these triggers will be similarly excluded.
· Sending and receiving data to the client process. Once rows have been returned from a query they need to be sent to the client process. If the client process is not able to keep up, then that can hold up proceedings at the server. A slow network or a slow client will show up as numerous “SQL*Net message to client” wait events seen in V$ACTIVE_SESSION_HISTORY.
· Parsing. This is rarely an issue for SQL statements that run for multiple seconds, but I once worked with an application where the CBO took over 4 seconds to devise a query plan! The query plan was quite good and ran in under one second, but that was little consolation!
Most of the time the source of the lost time will soon become quite clear. It is on those rare occasions when you can’t see where the lost time has gone that the 10046 SQL trace that I mentioned in Chapter 4 becomes invaluable.
When the Time Does Add Up
Once the figures in V$SQL_PLAN_STATISTICS_ALL make sense, you need to focus in on the most expensive operation first. Look at what the test script in Listing 5-2 does.
Listing 5-2. Analyzing the performance of a simple two-table join
CREATE TABLE t1 (c1 PRIMARY KEY)
ORGANIZATION INDEX
AS
SELECT ROWNUM c1 FROM DUAL;
CREATE TABLE t2
PCTFREE 99
PCTUSED 1
AS
WITH q1
AS ( SELECT ROWNUM c2
FROM DUAL
CONNECT BY LEVEL <= 100)
SELECT a.c2, RPAD ('X', 2000) vpad
FROM q1 a, q1;
BEGIN
DBMS_STATS.gather_table_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
,'T1'
,no_invalidate => FALSE);
DBMS_STATS.gather_table_stats (SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA')
,'T2'
,no_invalidate => FALSE);
END;
/
INSERT INTO t2 (c2, vpad)
WITH q1
AS ( SELECT ROWNUM c2
FROM DUAL
CONNECT BY LEVEL <= 300)
SELECT a.c2, RPAD ('X', 2000) vpad
FROM q1 a, q1;
SET LINES 200 PAGES 900 TIMING ON
ALTER SESSION SET statistics_level=all;
BEGIN
FOR r IN (SELECT *
FROM t1, t2
WHERE t1.c1 = t2.c2)
LOOP
NULL;
END LOOP;
END;
/
COLUMN id FORMAT 99
COLUMN operation FORMAT a18
COLUMN options FORMAT a11
COLUMN actual_time FORMAT 99.999 HEADING "Actual|Time"
COLUMN object_name FORMAT a17 HEADING "Object|Name"
COLUMN last_starts FORMAT 9999999 HEADING "Last|Starts"
COLUMN actual_rows FORMAT 9999999 HEADING "Actual|Rows"
SELECT id
,LPAD (' ', DEPTH) || operation operation
,options
,last_elapsed_time / 1000000 actual_time
,object_name
,last_starts
,last_output_rows actual_rows
FROM v$sql_plan_statistics_all
WHERE sql_id = 'cwktrs03rd8c7'
ORDER BY id;
Listing 5-2 includes some SQL*Plus-specific syntax and begins by creating two tables. T1 is an index-organized table, and T2 is a regular heap table. After the statistics are gathered on the two tables (superfluous in 12cR1 onwards as I shall explain later in the book) some more rows are added to table T2. The statistics for T2 are now slightly out of date. This is perhaps a little unusual; you can’t always gather statistics on a table every time you insert data into it, but you don’t normally increase the number of rows several fold before re-gathering statistics! The tables are then joined inside a PL/SQL block and, for the purposes of this test, the output is discarded. On my laptop the query took about 25 seconds. The final statement in Listing 5-2 examines some key columns from V$SQL_PLAN_STATISTICS_ALL using the SQL_ID that I determined separately using the techniques described in Chapter 1.
The output from the final query in Listing 5-2 is shown in Listing 5-3.
Listing 5-3. Runtime statistics from a two-table join
Actual Object Last Actual
ID OPERATION OPTIONS Time Name Starts Rows
--- ------------------ ----------- ------- ----------------- -------- --------
0 SELECT STATEMENT 25.157 1 400
1 NESTED LOOPS 25.157 1 400
2 TABLE ACCESS FULL 24.806 T2 1 100000
3 INDEX UNIQUE SCAN .258 SYS_IOT_TOP_97615 100000 400
We can see that operation 1 ostensibly took about 25.16 seconds, but do you remember what I said about the time allocated to children being included in the time for the parent? Operations 2 and 3 took about 24.81 seconds between them, so the NESTED LOOP itself was responsible for just 0.35 seconds. This is to be expected because a NESTED LOOPS join just gets rows from the driving row source and invokes the probe row source for matches. What is perhaps a little more surprising is that even though we can see that operation 3 was invoked 100,000 times, which we know form the LAST_STARTS column, the total elapsed time was still just 0.26 seconds!
Tip With the exception of semi-joins and anti-joins that I will cover in Chapter 11, the number of times the probe row source (the child with the higher ID) of a NESTED LOOPS join is executed (LAST_STARTS) is always precisely the same as the number of rows returned by the driving row source (the LAST_OUTPUT_ROWS column from the child with the lower ID). This is because the second child is probed once for every row returned by the first child. In this case the full table scan of T2 returned 100,000 rows, so the index of T1 was probed 100,000 times.
There is clearly a lot of fancy optimization going on under the covers of those 100,000 probes, which one day might be interesting,1 but right now the main conclusion is that you don’t need to worry about operation 3. No, the big concern is operation 2. Even though it was only executed once, that single full table scan took 24.81 of the 24.16 seconds of our statement’s elapsed time. That full table scan is where we need to focus our attention.
Fixing the Problem
Once you have identified the specific operation in your SQL statement that is taking the most time, the next thing to do is to figure out how to save some of that time. Always remember that at this stage we are just trying to find out what needs to be fixed and not how to deploy the fix in production.
Check the Statistics
Before you go any further you need to know if your object statistics are appropriate. Object statistics are the key input to the CBO, and I will explain why “appropriate” does not necessarily mean “up to date” in Chapter 6. The point is to ensure that you don’t spend a lot of effort tuning a query when the statement’s poor performance is purely down to stale or otherwise inappropriate statistics.
If you are working on a test system that has production-like data and the object statistics on your test system are out-of-date, then the simplest thing may be to just to re-gather them. However, if you have some extended statistics or hand-crafted histograms on your production system it would be best to make sure that the same extended statistics and histograms are present on your test system, and perhaps the best thing would be to copy the statistics from production to your test system.
I’ll be talking a lot more about object statistics in the next chapter and I don’t want to get too bogged down at this point. The main point I want to make here is that you need to consider the state of your object statistics early on in your tuning process.
Let us gather statistics on the tables in Listing 5-2 and rerun the query. Listing 5-4 shows the updated results from V$SQL_PLAN_STATISTICS_ALL.
Listing 5-4. Performance after gathering statistics
Actual Object Last Actual
ID OPERATION OPTIONS Time Name Starts Rows
--- ------------------ ----------- ------- ----------------- -------- --------
0 SELECT STATEMENT 13.390 1 400
1 HASH JOIN 13.390 1 400
2 INDEX FULL SCAN .000 SYS_IOT_TOP_100393 1 1
3 TABLE ACCESS FULL 13.307 T2 1 100000
We can see that the execution plan has changed and the performance has improved. In fact, the improvement is primarily due to the fact that the data is now cached rather than being due to the change in execution plan. That same full table scan is still the source of our problems.
Changing the Code
There are several reasons why rewriting a query in a functionally equivalent way may help the CBO. I’ll mention a couple now. I will cover the topic in more depth in Chapter 16.
Adding Information
There are lots of ways that you can give information to the CBO to help it along. Here is just one example.
Unless a column is guaranteed to always be NOT NULL, you can’t add a NOT NULL constraint for it. However, the specific rows you are selecting at the specific time you are selecting them may be guaranteed to be NOT NULL, and you can add a predicate such as “WHERE c1 IS NOT NULL.” Now the CBO has one more bit of potentially useful information it can include in its preparation of an execution plan. In the case of the simple query in Listing 5-2 there isn’t any useful information that we can add.
Transforming the Query
As I showed in Listings 1-1 and 1-2 there are occasions when you can rewrite a query in an entirely different way and cause the CBO to come up with a completely different execution plan. There doesn’t seem to be any useful transformation that we can apply to the query in Listing 5-2 so let us move on.
Adding Hints
Although I consider hints a perfectly acceptable way to get a SQL statement to run fast on a production system, now is not the time for a debate on this topic. At this stage we are just trying to identify a well-performing execution plan via a set of experiments. You will find that all well-known Oracle experts would agree that for experimental purposes only you should not in any way be inhibited from using hints, documented or undocumented, in your search for the most efficient execution plan.
Like most of you out there I often ask myself questions like “Why on earth has the CBO used a full table scan instead of using the index that I created specifically for queries like this?” Well, before spending days theorizing on the matter, I usually end up adding an INDEX hint to my query and, if possible, running the query again. There are several possible outcomes:
· After adding the INDEX hint the CBO still used a full table scan. This suggests that the use of the index is illegal in some way. I would now have half the answer to my question. The other half would be: “Why is use of the index illegal?”
· The index was used but the query was actually no faster, or possibly was even slower. I now have a new question: “What false assumption did I make that made me believe the index would be faster?”
· The index was used and the elapsed time of the query was reduced. This experimental result leads to a different new question: “What false assumption did the CBO make that made it believe that the index would not be faster?” If there are hundreds of similar SQL statements out there then this new question is extremely important. At this point, though, these concerns don’t worry me too much; I can proceed to tune other parts of the query, leaving the hint in place.
In the case of the query in Listing 5-2 there aren’t yet any indexes on table T2. The only hint that might have helped, a PARALLEL hint, didn’t work on my laptop because the single local hard disk can’t do more than one thing at once. Let us move on.
Making Physical Changes to the Database
The most common physical database change people make to improve query performance is to add one or more indexes. However, there are lots of other physical design changes that may help improve query performance, and I will cover these in Chapter 15. Here are just a few examples.
Provided you have the necessary licenses you might implement partitioning or compression. You might create materialized views or perform some other form of denormalization. Of course, in the specific case shown in Listing 5-2 we could simply remove the PCTFREE 99 that I added deliberately to slow down the full table scan! That may not often be an option in real life, but you might occasionally find that a table has a large amount of free space in its blocks and that by moving the table you can significantly reduce its size and improve access times.
Even when you do decide that adding an index will help query performance, there are still several questions to ask. The first thought in your mind, as with any physical design change, should be to assess the impact that adding the index will have on DML statements. Every time you insert a row into a table, every time you delete a row from a table, and every time you update indexed columns in a table you need to adjust the table’s indexes. The more of those indexes you have, the longer the DML will take. Not only that, but indexes take up space in the buffer cache and on disk, space that may be better used for other purposes.
I am contradicting myself a little bit here. After all you are still just running experiments, so you don’t have to think everything through at this stage. But some physical design changes take a lot of work and impact other testers, so it may be worth giving some thought to the wider impact of your proposed change before you expend a lot of effort on an idea that may be quite impractical in practice.
Let us assume that you are still keen on the idea of adding an index. You then need to ask yourself what columns to index and in what order. Perhaps adding an extra column to your index would help other queries. Another thing to consider is the possibility of using one or more bitmap indexes, which is not a bad option if data is loaded in bulk and there are few, if any, updates later. For narrow tables, changing to an Indexed Organized Table may be an option as well.
The final thing to consider when adding an index is how many columns, if any, to compress. In the case of table T2 in Listing 5-2, we know that our indexed column will be repeated many times, so it seems sensible to compress it. Listing 5-5 adds an index on T2.C2 and shows the amended data from V$SQL_PLAN_STATISTICS_ALL.
Listing 5-5. Adding a compressed index
CREATE INDEX t2_i1
ON t2 (c2)
COMPRESS 1;
Actual Object Last Actual
ID OPERATION OPTIONS Time Name Starts Rows
--- ------------------ -------------- ------- --------------- -------- --------
0 SELECT STATEMENT .004 1 400
1 HASH JOIN .004 1 400
2 NESTED LOOPS .003 1 400
3 NESTED LOOPS .001 1 400
4 STATISTICS COL .000 1 1
5 INDEX FULL SCAN .000 SYS_IOT_TOP_100 1 1
6 INDEX RANGE SCAN .001 T2_I1 1 400
7 TABLE ACCESS BY INDEX ROWID .001 T2 400 400
8 TABLE ACCESS FULL .000 T2 0 0
Wow! Our query now runs in a fraction of a second! We can see that the execution plan has changed almost beyond recognition with the addition of the index. The key point is that our full table scan has been replaced by operation 7, which makes just 400 single-block accesses to T2.
Making Changes to the Environment
If your query seems to be perfectly well tuned and the performance is still unacceptable, what then? Hardware upgrades are always an option, of course, but there are often far less drastic measures available. You may be able to increase the size of the buffer cache to reduce the number of disk reads. You may be able to increase the size of the PGA or set WORAKAREA_SIZE_POLICY to MANUAL so that a sort will complete in memory. We will discuss sorts at length in Chapter 17. You might also want to run the query in parallel, and we will discuss parallel execution plans inChapter 8.
Running the SQL Tuning Advisor
It is an unfortunate fact of life that DBAs are often assigned responsibility for a large number of applications and databases and are nevertheless called in at the last minute, often at antisocial hours, to deal with performance issues about which they have little or no background knowledge. In these cases it will be difficult to build the detailed business and technical context for the performance issue in the way that I have advocated here.
Fortunately, help may be at a hand. The SQL Tuning Advisor performs automated checks on what it can see in the database. It checks if the statistics are stale or misleading. It looks for hidden correlations in columns and does a few other basic checks. I believe that the way it works is to run parts of the SQL statement to see how well the CBO’s theoretical ideas work out in practice.
There is good news and bad news about the SQL Tuning Advisor. The bad news is that the SQL Tuning Advisor is incapable of solving all but the most basic of tuning problems:
· It doesn’t have any more SQL transformations available than the CBO and runs through the same sort of costing calculations as the CBO, and so it often comes up with the same conclusions as the CBO.
· It can recommend a new index, but its advice should be taken with a pinch of salt, as the CBO doesn’t actually create the index and so doesn’t know anything about what index statistics like the clustering factor would be.
Tip Never run the SQL Tuning Advisor when the query involves a temporary table. In real life the application will populate the temporary table before invoking the query, but the SQL Tuning Advisor will not. As a consequence the advice generated will usually be completely wrong.
The good news is that although the SQL Tuning Advisor can only help with the most basic of tuning issues, a lot of tuning issues are, in fact, quite basic! Because of this I sometimes use the SQL Tuning Advisor even when working with an application that I am intimately familiar with. Of course, these days I can often see the solution to a simple tuning issue right away and so my use of the SQL Tuning Advisor is now mostly restricted to lunch time! If I am given a tuning problem just as I am about to head out for something to eat, it is quite straightforward to kick off the SQL Tuning Advisor before I do. Sometimes, the answer is waiting for me by the time I get back from lunch!
You can invoke the SQL Tuning Advisor from Enterprise Manager (EM) but by the time you have logged into EM you could have kicked off the task from SQL*Plus and be at lunch. Listing 5-6 shows a generic SQL script that you can use.
Listing 5-6. Creating a SQL Tuning Advisor task from SQL*Plus
VARIABLE t VARCHAR2(20)
BEGIN
:t := DBMS_SQLTUNE.create_tuning_task (sql_id => '&SQL_ID');
DBMS_SQLTUNE.execute_tuning_task (:t);
END;
/
SET LINES 32767 PAGES 0 TRIMSPOOL ON VERIFY OFF LONG 1000000 LONGC 1000000
SELECT DBMS_SQLTUNE.report_tuning_task (task_name => :t, TYPE => 'TEXT')
FROM DUAL;
EXEC dbms_sqltune.drop_tuning_task(:t);
SET LINES 200
The idea is the same as the SQL Performance Monitor scripts that I showed you earlier. You use the SQL*Plus DEFINE command to specify the SQL_ID and then call the script that you have saved somewhere convenient. Do you see the highlighted section that says TYPE => 'TEXT'? In this case, the only supported type is 'TEXT', and so rather than generating an HTML report for viewing in a browser this script just sends the report to the terminal.
The DBMS_SQLTUNE.CREATE_TUNING_TASK procedure has several options that you might want to look at. For example, the default time allotted for the tuning task is 30 minutes, and that is frequently insufficient. All that happens then when the SQL Tuning Advisor hits its time limit is that it produces a curt report saying that it has run out of time. You might want to increase the timeout, particularly if you are planning a long lunch!
Shall we see what the SQL Tuning Advisor makes of the query in Listing 5-2? Have a look at Listing 5-7.
Listing 5-7. Dropping the index from Listing 5-5 and running the SQL Tuning Advisor
DROP INDEX t2_i1;
DEFINE SQL_ID=cwktrs03rd8c7
@@run_tuning_advisor
--- Edited output
2- Using New Indices
--------------------
Plan hash value: 3119652813
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 333 | 652K| 00:00:05 |
| 1 | NESTED LOOPS | | | | |
| 2 | NESTED LOOPS | | 333 | 652K| 00:00:05 |
| 3 | INDEX FULL SCAN | SYS_IOT_TOP_446365 | 1 | 3 | 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX$$_126C0001 | 333 | | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 333 | 652K| 00:00:05 |
--------------------------------------------------------------------------------------
The @run_tuning_advisor call in Listing 5-7 assumes that the code in Listing 5-6 has been saved in a script called run_tuning_advisor.sql. We can see that the SQL Tuning Advisor suggests the use of the index that we just dropped and that we know helps tremendously. Thankfully, you aren’t required to use the name IDX$$_126C0001 that the SQL Tuning Advisor has used for the index!
Rethink the Requirement
On occasions the cost and complexity of a solution seem to be out of all proportion to the problem being addressed. Is it really worth all of the time and effort? Will running 200 parallel query slaves flat out for half an hour have too much impact on the rest of the system? There are times when these sorts of questions really need to be asked. Perhaps the daily report you have been asked to tune could be run just once on the weekend when the system is otherwise idle. Maybe the query could be simplified and still provide data that is perfectly serviceable to the business consumers.
Although you shouldn’t be afraid to ask these sorts of questions when the time is right, you need to make sure you have your facts straight before you do. You don’t want your boss to have your colleague “take another look” and for him or her to find a quick solution. That would be embarrassing! If you are in any doubt, why not review your findings with a colleague first?
Summary
This chapter has provided an overview of a systematic approach to tuning a SQL statement. You need to begin by getting as thorough an understanding of the problem at hand as is practical. The next step is to get a breakdown of where the time is being spent. Finally, an iterative process of change and retest is used until such time as the performance reaches an acceptable level.
At this stage I haven’t given you much in the way of examples of query transformations, no information on how to hint properly, and only discussed physical database transformations very briefly. These topics will all be discussed in much more detail later in the book.
Always remember that tuning is an experimental process to determine what has gone wrong and why. It has little to do with deployment in the production system. Let us move onto that topic now in Chapter 6.
__________________
1Actually, the reasons that the operation was so fast were, first, that a “consistent get” from a unique index does not actually take out a latch and, second, because most of the time no index entry was found.