As discussed in Chapter 16 on plan stability, Oracle’s CBO can seem to change plans at random intervals, causing a great deal of frustration. However, these changes are not random at all, and we reviewed the reasons behind plan changes and examined how you can identify when plans change and why they change. This chapter’s focus, as you can probably guess, covers various techniques for controlling execution plans. I probably should say “influencing” instead of “controlling,” because there is really no foolproof method of locking in an execution plan. The tools we have at our disposal to help provide plan stability have evolved quite significantly in the more recent versions of Oracle. In this chapter, we take a walk through time and look at when plan control started and where it stands today in Oracle 12c.
Plan Control: Solving the Problem
When you have access to the code (and the change control requirements are not too stringent), you can make changes to get the plan you want. Mechanisms such as hints, changing the structure of the query itself, or using literals in key locations to avoid bind variable peeking issues are all viable options.
Note See a later section in this chapter, “Plan Control: Without Access to the Code,” for help in exerting control over execution plans when you do not have the luxury of being able to modify the code that is being executed.
Discussions about controlling execution plans can turn into a religious debate. Questions concerning the degree and mechanism of control can all degenerate from spirited debate to questioning our rival’s ancestry. There is a strong argument for letting the optimizer do what it was written to do. After all, it’s one of the most complex pieces of software in existence, with countless man-hours invested in its programming. In many (I dare say, most) situations, the optimizer is capable of coming up with very serviceable execution plans. However, this assumes that the database is configured correctly, that the statistics are accurate, that the data model is reasonable, that the SQL is written correctly, and so on. This is a lot of assumptions, and rarely are they all true.
So, one camp says fix the configuration, the stats, the data model, or whatever else is leading the optimizer astray. On the surface, this seems like a perfectly reasonable position that is hard to argue. If the statistics are not accurate, there most likely will be numerous issues, even if they aren’t readily apparent. So, fixing inaccurate statistics can improve things in many areas at the same time.
The flip side is that fixing some things is almost an insurmountable task. Changing the data model, for example, presents a substantial challenge even in a small application, and it is certainly not something that can be done with any degree of expediency. Likewise, changing configuration parameters can have sweeping effects that often require other adjustments. Because of these issues, there is a group that says, “Let’s just focus on fixing the slow-running process.” People in this camp tend to want to zero in on a SQL statement and fix it. This argument is also hard to argue with because we are often under the gun to provide relief to the users of the system.
The key, in my mind, is to weigh the costs—time to implement and risk—against the potential benefits. I tend to be a pragmatist and therefore rarely get into debates about which approach is “right.” I am quite comfortable with making a decision to implement a solution that provides quick relief, even if there is a long-term solution that needs to be implemented at some point in the future. I consider myself akin to an emergency room doctor. When a patient’s heart stops, I expect the doctor to break out the defibrillator and get the patient stabilized, not lecture him on proper lifestyle and put him on a diet and new exercise regime. There’s plenty of time for that later. On the other hand, after the guy has had a triple bypass, it would be foolish for his cardiologist to tell him that he should keep doing what he’s always been doing, just because the technology exists to give him another chance at life.
The bottom line is that, even when we have a system that is configured well with accurate statistics, we still occasionally run across plan stability issues. So, let’s put the philosophical issues aside and talk about the basic tools we have at our disposal for controlling execution plans.
Prior to version 8, changing the structure of a query was basically the only tool available for influencing execution plans (other than making physical changes to the database objects, such as adding or dropping indexes). Modifying SQL structure is still a valid technique, but because the optimizer has become so adept at transforming queries, it is not nearly as useful as it once was. Nevertheless, being aware of alternative forms that return the same set of rows gives us an advantage when we are trying to get the optimizer to pick a certain plan. Alternative forms can open up or close off options from which the optimizer has to choose.
Making Appropriate Use of Literals
Although it’s been drummed into our head for years that we should use bind variables, they are not appropriate in every situation. In fact, anywhere we have skewed data distributions and need histograms to get the best plans, we should use literals as opposed to bind variables, at least for the special cases (such as the values you want to make sure the optimizer is aware of). It is not necessary to choose one approach or the other for each statement, either. It is perfectly reasonable to code conditional logic that branches to a SQL construct that uses literals when the values are highly selective (or highly unselective). All the other values can be covered by a single version of the statement that uses a bind variable. Of course, if the number of very popular (or very nonpopular) values is high, we have to weigh the cost of the coding effort, the impact on the shared pool, and the additional impediment to scalability caused by the additional parsing for all these unique SQL statements. As my granddad used to say, “There’s no such thing as a free puppy.”
Giving the Optimizer Some Hints
One of the oldest and most basic methods of controlling execution plans is embedding optimizer instructions directly into the statement. Unfortunately, the name of this feature, Hint, is somewhat misleading. The word hint makes it sound like it is a mild suggestion that the optimizer can consider or ignore as it pleases. Nothing is further from the truth. Hints are actually directives to the optimizer. As long as the hint is valid, the optimizer obeys it. In general, hints are not well understood. One reason is that they are not particularly well documented. Worse than this, however, is that they return no error or warning message when an invalid hint is specified. In cases when there is a syntax error or object names are mistyped or the combination of hints cannot be applied together, they are simply silently ignored. So, it is difficult to know if a hint is even recognized as valid, much less whether it is doing what it is supposed to do. This lack of error or warning messages is probably the biggest reason for confusion about what they do.
There is a way to determine whether an error has occurred that prevents a hint from being used. By generating an optimizer trace (event 10053), you can review a section, labeled Dumping Hints, of the trace file that is emitted. To capture an optimizer trace, set event 10053 as follows:
alter session set events '10053 trace name context forever, level 1':
After the event is set, you simply execute the statement with the hint in it you wish to evaluate. However, even the collection information doesn’t provide much help regarding why the hint is invalid. Listing 17-1 shows an excerpt from an optimizer trace file for a query that is attempting to apply FULL hints to the dept and emp tables.
Listing 17-1. Optimizer Trace (10053) File Excerpt
...
Dumping Hints
-------------------------------------------------------------
atom_hint=(@=0x31211124 err=0 resol=1 used=0 token=448 org=1 lvl=3 txt=FULL ("DEPT") )
atom_hint=(@=0x3120e100 err=0 resol=1 used=1 token=448 org=1 lvl=3 txt=FULL ("EMP") )
...
The err value shows a 1 if an error is noted in the hint or, as in this example, a 0 if no error is present. The other bit of information that can be useful is the used indicator. Using the same 0/1 indicator, a value of 1 indicates that the hint is used during the evaluation of the plan. However, just because the optimizer uses the hint during plan evaluation doesn’t mean the chosen plan operations include one specified by the hint. So, there’s no real way to know why a hint is or isn’t used.
Hints can be used to tell Oracle to do a full table scan instead of using an index, or to do a nested loops join, or to use a specific index, or all the above. Each of these access path-oriented hints effectively reduces the universe of possible options the optimizer can consider when coming up with an execution plan for a statement. Hints can also be used to alter optimizer settings, object statistics, and even internal optimizer calculations. These kinds of hints alter the way the optimizer does its work or the calculations that it makes, but they do not limit directly the choices the optimizer has in terms of access paths. By the way, you can get a list of valid hints along with the version in which they were introduced via the V$SQL_HINT view. Listing 17-2 shows the valid_hints.sql script.
Listing 17-2. The valid_hints.sql Script
SQL> @valid_hints
Enter value for hint: pq
Enter value for version:
NAME VERSION
---------------------------------------------------- --------
PQ_DISTRIBUTE 8.1.5
PQ_MAP 9.0.0
PQ_NOMAP 9.0.0
PQ_CONCURRENT_UNION 12.1.0.1
NO_PQ_CONCURRENT_UNION 12.1.0.1
PQ_REPLICATE 12.1.0.1
NO_PQ_REPLICATE 12.1.0.1
PQ_FILTER 12.1.0.1
PQ_SKEW 12.1.0.1
NO_PQ_SKEW 12.1.0.1
PQ_DISTRIBUTE_WINDOW 12.1.0.1
11 rows selected.
Hints can be applied to individual statements by embedding them inside comments that begin with a plus sign (+) Any comment immediately following a SELECT, UPDATE, INSERT, or DELETE keyword that begins with + is evaluated by the optimizer. The comment can contain multiple hints (separated by spaces). The documentation also states that comment text can be interspersed with hints. I don’t recommend this technique, however, because not all hints are documented and you may inadvertently put in a word that that has significance to the optimizer. There can only be one hint–comment per query block. Subsequent comments that start with + are not evaluated by the optimizer. If you use an alias for an object name in your SQL statement, all hints must refer to the object by its alias. Also note that if you specify an owner name in your statement, the hint should not include the owner name (use an alias; it makes it easier). Listing 17-3 shows a couple examples.
Listing 17-3. Examples of Hints
--Valid:
select /* real comment */ /*+ gather_plan_statistics full (a) */ avg(sal)
from emp a where deptno = 10;
select /*+ gather_plan_statistics full (a) */ /* real comment */ avg(sal)
from emp a where deptno = 10;
select /*+ gather_plan_statistics full (emp) */ /* real comment */ avg(sal)
from emp where deptno = 10;
--Invalid
-- don't use owner in hint
select /*+ gather_plan_statistics full (scott.emp) */ /* real comment */ avg(sal)
from scott.emp where deptno = 10;
-- if you use a table alias it must be used in the hint
select /*+ gather_plan_statistics full (emp) */ /* real comment */ avg(sal)
from emp a where deptno = 10;
-- apparently the word comment has a special meaning – disabling the hints
select /*+ real comment gather_plan_statistics more comment full (a) */ avg(sal)
from emp a where deptno = 10;
-- the 2nd hint will not be evaluated as a hint
select /*+ gather_plan_statistics */ /*+ full (a) */ /* real comment */ avg(sal) from emp a where deptno = 10;
The format of hints is actually more complicated than the abbreviated version we usually see. The simplified format we normally see is used to specify tables in which the hints are embedded directly in the query blocks where the table occurs. This is not always desirable or even possible, so Oracle has a way of declaring hints that specify where the table is located in the SQL structure. This becomes important when specifying hints that affect objects inside of views, for example, and, as we see later on, for the hint-based mechanisms that Oracle uses to try to improve plan stability. The documentation refers to a “global hint format,” which basically means the query block in which an object resides is specified within the hint. Any hint that applies to one or more tables can make use of this global format. The query block names can be specified manually with a hint (QB_NAME) or can be assigned automatically by the system. The system-generated names are not always intuitive. In simple statements, they often take the form of SEL$1, SEL$2, and so forth (or UPD$1 or DEL$1 for update and delete statements, respectively). Listing 17-4 shows some examples of query block naming using the FULL hint.
Listing 17-4. Examples of Hints Using Query Block Naming
select /*+ full (a) */ avg(sal)
from emp a where deptno = 10;
select /*+ full (@SEL$1 a@SEL$1) */ avg(sal)
from emp a where deptno = 10;
select /*+ full (a@SEL$1) */ avg(sal)
from emp a where deptno = 10;
select /*+ full (@SEL$1 a) */ avg(sal)
from emp a where deptno = 10;
select /*+ qb_name (MYQB) full (a@MYQB) */ avg(sal)
from emp a where deptno = 10;
All five of the previous statements are equivalent. The first @SEL$1 is the query block where the hint should be applied. The term @SEL$1 is the fully qualified table alias. In this case, the whole query block name is redundant. There is only one table and one query block. In general, even when there are multiple query blocks, specifying the query block and then fully qualifying the alias is not necessary. There are situations, though, when you may need both.
There are a couple of ways to determine the correct query block name when system-assigned query block names are in play. One is to use DBMS_XPLAN with the ALIAS parameter. The other is to look at the data in the other_xml column of v$sql that contains all the hints Oracle thinks are necessary to recreate the plan. These hints are fully qualified. Listing 17-5 shows examples of both techniques.
Listing 17-5. Examples of Determining the Correct Query Block Name
SQL> @sql_hints
SQL> select
2 extractvalue(value(d), '/hint') as outline_hints
3 from
4 xmltable('/*/outline_data/hint'
5 passing (
6 select
7 xmltype(other_xml) as xmlval
8 from
9 v$sql_plan
10 where
11 sql_id like nvl('&sql_id',sql_id)
12 and child_number = &child_no
13 and other_xml is not null
14 )
15 ) d;
Enter value for sql_id: f30tq1uck3171
Enter value for child_no: 0
OUTLINE_HINTS
-------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "DEPARTMENTS"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5DA710D3" "DEPARTMENTS"@"SEL$1")
INDEX(@"SEL$5DA710D3" "EMPLOYEES"@"SEL$2" ("EMPLOYEES"."DEPARTMENT_ID" "EMPLOYEES"."JOB_ID"))
LEADING(@"SEL$5DA710D3" "DEPARTMENTS"@"SEL$1" "EMPLOYEES"@"SEL$2")
USE_MERGE(@"SEL$5DA710D3" "EMPLOYEES"@"SEL$2")
13 rows selected.
SQL>@dcplan
Enter value for sql_id: f30tq1uck3171
Enter value for child_no: 0
Enter value for format: TYPICAL -BYTES +ALIAS
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID f30tq1uck3171, child number 0
-------------------------------------------------------------
select /* not-in */ department_name from hr.departments where
department_id not in (select department_id from hr.employees)
Plan hash value: 3403053048
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | MERGE JOIN ANTI NA | | 17 | 1 (0)|
| 2 | SORT JOIN | | 27 | 0 (0)|
| 3 | TABLE ACCESS BY INDEX | DEPARTMENTS | 27 | 0 (0)|
| | ROWID BATCHED | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 0 (0)|
|* 5 | SORT UNIQUE | | 107 | 1 (0)|
| 6 | INDEX FULL SCAN | EMP_JOB_DEPT_IX | 107 | 1 (0)|
-------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$5DA710D3
3 - SEL$5DA710D3 / DEPARTMENTS@SEL$1
4 - SEL$5DA710D3 / DEPARTMENTS@SEL$1
6 - SEL$5DA710D3 / EMPLOYEES@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
Notice that the query block names in this example are more complex than the simple SEL$1, although the aliases still use the SEL$1 format to reference their original position in the statement. The complex query block names are the result of transformations done by the optimizer. Listing 17-6 shows what happens when you run the same query with query transformation turned off.
Listing 17-6. The Same Query with Query Transformation Turned Off
SQL>@dcplan
Enter value for sql_id: 79ps58yga4hfr
Enter value for child_no: 0
Enter value for format: TYPICAL -BYTES +ALIAS
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 79ps58yga4hfr, child number 0
-------------------------------------------------------------
select /* not-in */ /*+ no_query_transformation */ department_name from
hr.departments where department_id not in (select department_id from
hr.employees)
Plan hash value: 89172749
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 17 (100)|
|* 1 | FILTER | | | |
| 2 | TABLE ACCESS FULL| DEPARTMENTS | 27 | 3 (0)|
|* 3 | INDEX FULL SCAN | EMP_JOB_DEPT_IX | 2 | 1 (0)|
-------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / DEPARTMENTS@SEL$1
3 - SEL$2 / EMPLOYEES@SEL$2
Predicate Information (identified by operation id):
-------------------------------------------------------------
1 - filter( IS NULL)
3 - filter(LNNVL("DEPARTMENT_ID"<>:B1))
Notice that the more complicated query block names have disappeared. Furthermore, when you specify your own query block names, you still get a generated name if a transformation takes place. This makes sense if you think about it. Transformations can completely change the structure of the query, turning a statement with a subquery (such as this example) into a join, for example. This combines two query blocks into a single new block. It is for this reason that I prefer to use the fully qualified alias rather than the hint format that includes a query block name as the first element of the hint. For comparison, Listing 17-7 shows another plan dump in which transformations are allowed and the query blocks are named explicitly.
Listing 17-7. Explicitly Named Query Blocks
SQL>@dcplan
Enter value for sql_id: g0pu554n4z3cq
Enter value for child_no: 0
Enter value for format: TYPICAL -BYTES +ALIAS
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID g0pu554n4z3cq, child number 0
-------------------------------------------------------------
select /* not-in2 */ /*+ qb_name(outer) */ department_name from
hr.departments dept where department_id not in (select /*+
qb_name(inner) */ department_id from hr.employees emp)
Plan hash value: 3403053048
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | MERGE JOIN ANTI NA | | 17 | 1 (0)|
| 2 | SORT JOIN | | 27 | 0 (0)|
| 3 | TABLE ACCESS BY INDEX | DEPARTMENTS | 27 | 0 (0)|
| | ROWID BATCHED | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 0 (0)|
|* 5 | SORT UNIQUE | | 107 | 1 (0)|
| 6 | INDEX FULL SCAN | EMP_JOB_DEPT_IX | 107 | 1 (0)|
-------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F38A2936
3 - SEL$F38A2936 / DEPT@OUTER== The alias remains intact even though
4 - SEL$F38A2936 / DEPT@OUTER a query block name has been generated
6 - SEL$F38A2936 / EMP@INNER due to transformation.
Predicate Information (identified by operation id):
-------------------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
Notice that the aliases retained their original names even though the query block was renamed as result of the transformation. The transformation can be verified by a 10053 trace, which details the decision-making process that the optimizer goes through when determining an execution plan. Listing 17-8 shows an excerpt from the trace file for the previous statement.
Listing 17-8. An Excerpt from the Trace File
Registered qb: OUTER 0xf64c3e34 (HINT OUTER)
QUERY BLOCK SIGNATURE
signature (): qb_name=OUTER nbfros=1 flg=0
fro(0): flg=4 objn=73928 hint_alias="DEPT"@"OUTER"
Registered qb: INNER 0xf64c1df0 (HINT INNER)
QUERY BLOCK SIGNATURE
signature (): qb_name=INNER nbfros=1 flg=0
fro(0): flg=4 objn=73933 hint_alias="EMP"@"INNER"
. . .
JPPD: Applying transformation directives
query block OUTER transformed to SEL$F38A2936 (#1)
. . .
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT /*+ QB_NAME ("OUTER") */ "DEPT"."DEPARTMENT_NAME" "DEPARTMENT_NAME" FROM
"HR"."EMPLOYEES" "EMP","HR"."DEPARTMENTS" "DEPT" WHERE
"DEPT"."DEPARTMENT_ID"="EMP"."DEPARTMENT_ID" AND NOT EXISTS (SELECT /*+ QB_NAME ("INNER") */
0 FROM "HR"."EMPLOYEES" "EMP" WHERE "EMP"."DEPARTMENT_ID" IS NULL)
. . .
Dumping Hints
-------------------------------------------------------------
atom_hint=(@=0xf6473d1c err=0 resol=1 used=1 token=1003 org=1 lvl=2 txt=QB_NAME ("OUTER") )
====================== END SQL Statement Dump ======================
The trace file shows the original query blocks along with the objects in them. It shows that the first query block (named OUTER) was transformed into SEL$F38A2936. And it shows the final version of the statement that was executed. Notice that, in the final version, the original subquery is gone. It has been merged (unnested) into the OUTER query as a join, and a new subquery has been introduced that checks to see if department_id is null. Finally, there is the Dumping Hints section (as shown in Listing 17-1) that shows hints that have been evaluated.
Plan Control: Without Access to the Code
One of the most frustrating problems we face is not being able to fix bad code. Our inability to change the code occurs for many reasons. In some cases, we are dealing with packaged applications in which the code is just not available. In other cases, the politics of an organization can dictate lengthy delays in making changes to code. Regardless of the reasons, Oracle specialists often find themselves in the unenviable position of being asked to make things better without touching the code. Fortunately, Oracle provides many options for doing just that.
Both changing statistics and modifying database parameters come to mind as effective techniques for effecting execution plan changes. These techniques can cause sweeping changes that affect many SQL statements. Obviously, the statistics need to be as accurate as possible. It is very difficult to get reasonable performance if the statistics are not correct. The database also needs to be configured correctly, although from a stability standpoint, it is not imperative that every parameter be set to an “optimal” value. In fact, there are often tradeoffs that must be made. But, the good news is that stability can usually be accomplished regardless of the configuration, as long as it stays consistent.
Changing access paths (in other words, adding or removing indexes) can also be an effective tool. Of course, this is also a sweeping change that can affect many SQL statements (maybe for the better, maybe not). Certainly, adding an index imposes additional overhead to DML operations. This approach generally requires a fair amount of testing to ensure that statements other than the one you are attempting to fix are not impacted negatively.
Among the most effective approaches, though, are techniques that focus on modifying execution plans of individual statements. Oracle has provided various mechanisms for accomplishing this throughout the years, such as Stored Outlines, SQL Profiles, SQL Plan Baselines, SQL Patches, and, now with Oracle 12c, automatic generation of SQL Plan Directives (discussed in Chapter 16). These techniques provide laserlike specificity by limiting their effect to a single statement (or in some cases, a set of statements). Although these constructs are extremely powerful, they are not particularly well understood. They also suffer from some quirkiness. For example, despite what the documentation implies regarding Outlines locking execution plans, there are situations when creating an Outline on an existing statement, instead of locking in the current plan, actually causes the plan to change.
This quirk is not limited to the older Outline construct. It has been carried forward to the newer SQL Profiles and SQL Baselines as well. And if you think about it, the basic mechanism of these constructs (applying hints) is somewhat suspect. The more complicated a SQL statement is, the more options the optimizer has and the more difficult it becomes too narrow down the choices to a single plan. Nevertheless, it is a widely used technique and probably the best tool at our disposal for controlling the plan of a single statement. So let’s discuss each of these options in a little more detail.
Option 1: Change the Statistics
If statistics are inaccurate, they should be fixed. For the optimizer to do its job, we must give it the information it needs to make good decisions. In terms of plan stability, just changing the stats is not sufficient. In general, it’s the method of gathering them that needs to be addressed. Although a complete discussion of statistics gathering is out of the scope of this chapter, there are a few things that are important to know now:
- The default stats-gathering job in 10g generates histograms on most columns. This is usually not a good thing. Versions 11g and 12c do a much better job of gathering histograms where they are appropriate.
- Histograms generated in 10g with small sample sizes are often not very accurate (DBMS_STATS.AUTO_SAMPLE_SIZE often chooses very small sample sizes that result in inaccurate histograms). However, in 11g and above, using DBMS_STATS.AUTO_SAMPLE_SIZE is improved so that we can achieve a near-100 percent sample size quality estimate while taking only as much time as a 10 percent or less sample would take to complete.
- Histograms are most useful for columns in which the values are not distributed evenly.
- Bind variables and histograms do not work well together if the data distribution is uneven.
- Extended statistics, also known as column-group statistics, should be created as needed to let the optimizer know how to characterize related columns properly that are used frequently in query predicates together. In 12c, Oracle has automated the creation of column-group statistics but you still need to create your own in prior versions.
- Statistics should be gathered (or set) often enough to make sure that column max and min values are close to reality. This is especially important with large tables for which it takes a while for the default stats job to determine it’s time to regather (more than 10 percent of the rows have been modified). Plans can change radically (and unexpectedly) when the values specified in WHERE clauses are above or below the range the optimizer thinks is there.
- Partitions should be prepopulated with representative stats if they are to be queried before the normal statistics-gathering job has had a chance to run; otherwise, you may get the dreaded “Why do my jobs always run slow on Mondays?” syndrome.
- Most important, be intimately familiar with how statistics are generated on your systems.
The bottom line is that object statistics need to be accurate. If they are way out of whack, there may be little choice but to address the issue before attempting any other measures. Of course, as in all triage situations, we may have to take some expedient actions to save the patient.
One last thing on stats: Oracle provides the ability to set the values manually for the object statistics that the optimizer uses. Setting statistics manually for an object is a perfectly valid technique in some situations. For example, setting a maximum value manually for a frequently queried column that is increasing constantly and running ahead of standard statistics gathering might be a perfectly reasonable thing to do. Building your own histogram with the values that are important to your application is also possible and may be a reasonable approach if you can’t get the normal stats-gathering procedures to do what you want. Listing 17-9 shows a couple scripts that set column statistics manually.
Listing 17-9. Scripts That Set Column Statistics Manually
SQL> @col_stats
Enter value for owner: KRM
Enter value for table_name: LITTLE_SKEW
Enter value for column_name:
COLUMN_NM DATA_TYPE AVGLEN NDV ... LAST_ANAL LOW_VALUE HIGH_VALUE
--------- ---------- ------ ------ ... --------- ----------- -----------
PK_COL NUMBER 5 99,999 ... 03-AUG-13 1 1000002
COL1 NUMBER 4 2 ... 03-AUG-13 1 999999
COL2 VARCHAR2 8 1 ... 03-AUG-13 TESTING TESTING
COL3 DATE 8 1 ... 03-AUG-13 08-nov-2008 08-nov-2008
COL4 VARCHAR2 2 2 ... 03-AUG-13 N Y
SQL> @set_col_stats_max
Enter value for owner: KRM
Enter value for table_name: LITTLE_SKEW
Enter value for column_name: COL2
Enter value for minimum:
Enter value for maximum: XXXXXXX
PL/SQL procedure successfully completed.
SQL> @col_stats
Enter value for owner: KRM
Enter value for table_name: LITTLE_SKEW
Enter value for column_name:
COLUMN_NM DATA_TYPE AVGLEN NDV ... LAST_ANAL LOW_VALUE HIGH_VALUE
--------- --------- ------ -------- ... --------- ------------ -----------
PK_COL NUMBER 5 99,999 ... 03-AUG-13 1 1000002
COL1 NUMBER 4 2 ... 03-AUG-13 1 999999
COL2 VARCHAR2 8 1 ... 13-AUG-13 TESTING XXXXXXX
COL3 DATE 8 1 ... 03-AUG-13 08-nov-2008 08-nov-2008
COL4 VARCHAR2 2 2 ... 03-AUG-13 N Y
SQL> @set_col_stats
Enter value for owner: KRM
Enter value for table_name: LITTLE_SKEW
Enter value for col_name: COL1
Enter value for ndv: 10
Enter value for density: 1/10
Enter value for nullcnt: 0
PL/SQL procedure successfully completed.
SQL> @col_stats
Enter value for owner: KRM
Enter value for table_name: LITTLE_SKEW
Enter value for column_name:
COLUMN_NM DATA_TYPE AVGLEN DENSITY NDV...LAST_ANAL LOW_VALUE HIGH_VALUE
--------- --------- ------ ------- ------...--------- ----------- -----------
PK_COL NUMBER 5 .000010 99,999...03-AUG-13 1 1000002
COL1 NUMBER 4 .100000 10...13-AUG-13 1 999999
COL2 VARCHAR2 8 .000005 1...13-AUG-13 TESTING XXXXXXX
COL3 DATE 8 .000005 1...03-AUG-13 08-nov-2008 08-nov-2008
COL4 VARCHAR2 2 .000005 2...03-AUG-13 N Y
These scripts make use of the DBMS_STATS.SET_COLUMN_STATS procedure to set the column-level statistics manually . The set_col_stats_max.sql script is probably the more useful of the two. Notice, also, that the call to the procedure modifies the last_analyzed field.
Don’t be afraid of this technique. Remember, you know your data and how your applications use it (often better than Oracle does). Oracle provides you the tools to set the statistics as you see fit. Keep in mind, though, that if you do decide to make manual changes to statistics, you have to decide how to integrate those changes into the normal statistics-gathering routine in place on your systems. Don’t make the mistake of fixing some statistics issue manually and then have the standard stats-gathering job come along and wipe out your work a week later.
Option 2: Change Database Parameters
This is a SQL book, so I won’t discuss this technique in depth. In general, I am very hesitant to attempt to modify plans by manipulating database parameters at the system level except in situations when something is completely misconfigured and I have a reasonable amount of time to test. There are a few parameters that show up on the frequent offenders list such as optimizer_index_cost_adj, optimizer_index_caching, optimizer_mode, cursor_sharing, and db_file_multiblock_read_count. Basically, anything with a nondefault value is suspect in my mind, particularly if there is not a well-defined reason why it’s set to a nondefault value. The biggest problem with changing parameters is that they affect the optimizer’s calculations for every single statement in the system, which means that every single statement is reevaluated and the optimizer may come up with a new plan. Maybe this is what you want, but changing parameters certainly provides the opportunity for many plans to change, which is, by definition, the opposite of increasing stability.
Option 3: Add or Remove Access Paths
There are definitely times when a new index improves performance of a query significantly, and occasionally the statement is important enough to create one in a production system. But, the problem with adding an index is that a single index can change the execution plans of a number of statements. Assuming the statistics are in good shape, adding an index rarely has a significant negative effect on a query. Nevertheless, indexes should be tested with a representative workload prior to introduction into production. Also, don’t forget that adding an index most definitely adds overhead to DML that affects the columns you index.
And while I’m on the subject, removing unneeded indexes can improve DML statements significantly. It’s actually more common to see tables that are overindexed than ones that are underindexed. That’s because it’s scarier to remove an index than to create one. As a result, it usually takes an Act of Congress to get one removed from a production system. One of the main reasons for this is that it can take a lot of time to recreate an index on a large table. Beginning in 11g, there is a feature that makes this process more palatable, by the way. Indexes can be marked as invisible, which means the optimizer doesn’t consider them when determining execution plans. So you can see how your application behaves in production less the index you intend to drop, without actually dropping it. Invisible indexes continue to be maintained so you won’t see any improvement in DML speed as a result of making an index invisible, but you can make it visible again simply by issuing an alter index statement, if dropping the index turns out to have been a bad idea. Refer back to Chapter 12 for a more detailed review of invisible indexes.
So, adding (or removing) an index is a technique that can be used to modify execution plans, but it is not a particularly useful one when it comes to plan stability issues. If plans change, you need to solve the issue that is causing them to change or prevent them from changing. So, although I hate to say never, adding or removing an index is unlikely to prevent a plan from changing.
Option 4: Apply Hint-Based Plan Control Mechanisms
Oracle Database 11g and above implements several plan control mechanisms that rely on optimizer hints. The hint-based mechanisms supported in Oracle Database 11g and above are as follows:
- Outlines (deprecated in 11g)
- SQL Profiles
- SQL Baselines
- SQL Patches
These mechanisms are each designed with slightly different goals in mind, but they use the same basic approach of giving the application a set of hints that is named and associated with a SQL statement. The hints are then applied behind the scenes to any matching statement that is executed.
Outlines, or Stored Outlines as they are sometimes called, were introduced shortly after the CBO. They are the oldest of the hint-based mechanisms and should not be used in 11g and above. The documentation and marketing material when they were introduced also referred to the new feature as “Plan Stability.” The design goal was to “lock” a specific plan for a statement. This was done by using the CREATE OUTLINE statement to parse a SQL statement (including coming up with an execution plan), determine a set of hints that should be sufficient to force the optimizer to pick that plan, and then store the hints. The next time a matching SQL statement was processed by the database, the hints were applied behind the scenes before the execution plan was determined. The intention was that the set of hints would be sufficient to allow one and only one plan for the given statement, regardless of the optimizer settings, statistics, and so on.
By the way, matching basically means that the text of the statement matches. Originally, Outlines had to match character for character, just like the normal rules for sharing SQL statements; but, for some reason, Oracle later decided that the matching algorithm should be somewhat relaxed. What this means is that in any version you’re likely to run into today, whitespace is collapsed and differences in case are ignored. So (at least as far as Outlines are concerned), "select * from dual" is the same as "SELECT * FROM DuAl". You still get two different statements in the shared pool, but they use the same Outline, if one exists.
With 9i, Oracle started to enhance this feature by adding the ability to edit the Outlines themselves, but they never really completed the job. In fact, they pretty much quit doing anything with the feature after 10gR1. The script that creates the DBMS_OUTLN package ($ORACLE_HOME/rdbms/admin/dbmsol.sql), for example, has not been updated since early in 2004 (with the exception of a tweak to keep it working in 11g). At any rate, the feature has worked pretty well over the years; in fact, it still works in 12c, although the documentation has been warning us for the last several years that the feature has been deprecated and is no longer being maintained.
The first version of the feature required you to create an Outline by specifying the statement inline in a CREATE OUTLINE statement. Here’s an example:
SQL> create or replace outline junk for category test on
2 select avg(pk_col) from skew a where col1 > 0;
Outline created.
This syntax was a bit unwieldy because of having to specify the complete SQL statement as part of the command. Fortunately, a way to create an Outline was introduced later that allowed an Outline to be created on a statement that already existed in the shared pool. The CREATE_OUTLINE procedure was added to the DBMS_OUTLN package to do this. In general, it was a better approach because it was much easier to identify a cursor (with a hash value) than to cut and paste a long SQL statement to the command line. It also allowed you to see the plan arrived at by the optimizer prior to creating the Outline. Listing 17-10 shows the definition of the procedure and an example of its use.
Listing 17-10. CREATE_OUTLINE
PROCEDURE CREATE_OUTLINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
HASH_VALUE NUMBER IN
CHILD_NUMBER NUMBER IN
CATEGORY VARCHAR2 IN DEFAULT
SQL> select sql_id, hash_value, child_number from v$sql
2 where sql_text like 'select avg(pk_col) from skew where col1 = 136133'
3 /
SQL_ID HASH_VALUE CHILD_NUMBER
------------- ---------- ------------
fh70fkqr78zz3 2926870499 0
SQL> exec dbms_outln.create_outline(2926870499,0,'DEFAULT'),
PL/SQL procedure successfully completed.
SQL> select category, ol_name, hintcount hints, sql_text from outln.ol$;
CATEGORY OL_NAME HINTS SQL_TEXT
-------- ----------------------------- -------------------------------------
DEFAULT OUTLINE_11.2.0.3 6 select /*+ index(a SKEW_COL2_COL1)
DEFAULT SYS_OUTLINE_10081416353513714 6 select avg(pk_col) from skew where
TEST JUNK 6 select avg(pk_col) from skew a wher
So you can see that the Outline was created in the DEFAULT category with a very ugly name and that it has 6 hints assigned to it. Let’s have a quick look at the hints:
SQL> @outline_hints
Enter value for name: SYS_OUTLINE_10081416353513714
Enter value for hint:
NAME HINT
----------------------------- --------------------------------------
SYS_OUTLINE_10081416353513714 IGNORE_OPTIM_EMBEDDED_HINTS
SYS_OUTLINE_10081416353513714 OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
SYS_OUTLINE_10081416353513714 DB_VERSION('11.2.0.3')
SYS_OUTLINE_10081416353513714 ALL_ROWS
SYS_OUTLINE_10081416353513714 OUTLINE_LEAF(@"SEL$1")
SYS_OUTLINE_10081416353513714 INDEX_RS_ASC(@"SEL$1"
"SKEW"@"SEL$1"("SKEW"."COL1"))
6 rows selected.
In 10g and above, v$sql_plan has a column called other_xml. This column is a clob, and all the rows are null except the top record in the plan, which contains a mishmash of stuff, including the database version, the parsing schema name, the plan hash value, and so on. But, the most interesting bit is that the complete set of hints that is to be assigned to an Outline, if one is created using the DBMS_OUTLN.CREATE_OUTLINE procedure, is also contained in that column. Of course, it’s all in XML format, so you have to do an XML-type query to get it to come out nicely (or you can just use the sql_hints.sql script):
SQL> @sql_hints
Enter value for sql_id: fh70fkqr78zz3
Enter value for child_no: 0
OUTLINE_HINTS
-----------------------------------------------------------
OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
DB_VERSION('11.2.0.3')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
Outlines definitely suffer from some quirkiness. In fact, I have described them previously as “half baked.” Here are a few of things you should be aware of:
- Outlines aren’t used unless you set the USE_STORED_OUTLINESpseudoparameter, which can be set at the session level or the system level. Setting this at the session level only makes sense to me for testing purposes. The value can be TRUE, FALSE, or a category name. (More about categories in a minute.) The default value is FALSE. This means that even if an Outline is created, it won’t be used. The really irritating thing about USE_STORED_OUTLINES is that it is not a full-fledged parameter, so you can’t see what it’s set to by selecting from the v$parameter view or its underlying X$ views (where the hidden parameters are exposed). More important, this quirk means that the USE_STORED_OUTLINES setting does not persist across instance bounces. This issue prompted an official bug and enhancement request (see Oracle Support Note 560331.1). The official response was to suggest a database trigger to set the value when an instance is started (see outline_startup_trigger.sql in the example download for the recommended trigger).
- The DBMS_OUTLN.CREATE_OUTLINE procedure uses the old hash_value identifier as opposed to the newer sql_id that was introduced in 10g. Although most of the internal structures were updated to use sql_id, Outlines never were. This is just a slight irritation because it means you have to find the hash value to use the DBMS_OUTLN.CREATE_OUTLINE procedure. (See the create_outline.sql script in the example download for a way to get around this.)
- The DBMS_OUTLN.CREATE_OUTLINE procedure is a bit buggy. It often results in error 1330, which disconnects your session from Oracle. There is an Oracle Support Note describing this issue (Note 463288.1) that references a bug (Bug 5454975) that was supposed to be fixed in 10.2.0.4. Anyway, the bottom line is that you should execute the command to enable stored Outlines at the session level (ALTER SESSION SET USE_STORED_OUTLINES=TRUE) before attempting to create an Outline with the DBMS_OUTLN.CREATE_OUTLINE procedure (again, see the create_outline.sql script).
- The DBMS_OUTLN.CREATE_OUTLINE procedure does not allow a name to be specified for an Outline. Instead, it creates a system-generated name. This is another minor irritation because Outlines can be renamed easily enough with the ALTER OUTLINE command (see the create_outline.sql script yet again for a way to do this when creating an outline).
- Outlines are grouped together into categories. Each Outline is assigned to a single category. The default category is DEFAULT. If USE_STORED_OUTLINES is set to TRUE, Outlines in the DEFAULT category are used. If USE_STORED_OUTLINES is set to some other text string, only Outlines in the category that matches the value of USE_STORED_OUTLINES are used.
- As with all hints, Outline hints are directives that are obeyed unless they are invalid. Invalid hints are silently ignored. An invalid hint does not necessarily cause other hints in the Outline to be ignored or disabled, however.
Despite their minor flaws, Outlines have been a standard method for influencing execution plans for the past decade and, prior to 10g, they were the only option available. They also work with RAC, so if you create an Outline (or Profile or Baseline, for that matter), it is picked up across all the nodes in the cluster. If you find yourself working on a 9i database, don’t discount their usefulness. If you’re working on 10g and above, read on, because there are other options available.
Note I find it useful to include the sql_id and the plan_hash_value of a statement in the name of Outlines (and SQL Profiles and Baselines). For Outlines, I have used a convention of
OL_sqlid_planhash, which makes it very easy to track the object back to a SQL statement and see what the original plan was that I was trying to “lock in.” See the create_outline.sql script for an example.
SQL Profiles were introduced in 10g. They are the second iteration of Oracle’s hint-based mechanisms for influencing execution plans. SQL Profiles are only documented as a part of the SQL Tuning Advisor (STA), so the only documented way to create a SQL Profile is to run an STA job. In some cases, STA offers to create a SQL Profile for you. The task of STA is to analyze a SQL statement and determine whether there is a better plan. Because it is allowed as much time as it needs, the advisor can sometimes find better execution plans than the optimizer, because it actually validates the optimizer’s original estimates by running various steps in a plan and comparing the actual results with the estimates. When it’s all done, if STA has found a better plan, it offers to implement a SQL Profile that hopefully causes the optimizer to generate a new and better plan.
Those offered SQL Profiles are simply a collection of hints (much like Outlines), and they almost always contain a lightly documented hint (OPT_ESTIMATE) that allows the optimizer to scale its estimates for various operations. Essentially, it’s a fudge factor. The problem with this hint is that, far from locking a plan in place, it locks an empirically derived fudge factor in place, which still leaves the optimizer with a lot of flexibility when it comes to choosing a plan. It also sets up a commonly occurring situation in which the fudge factors stop making sense because things change over time. It is common for SQL Profiles generated by STA to work well for a while and then lose their effectiveness, thus the observation that SQL Profiles tend to sour over time.
Regardless of their intended purpose, the fact remains that SQL Profiles provide a mechanism for applying hints to SQL statements behind the scenes in the same basic manner as Outlines. In fact, it appears that the code is actually based on the earlier Outline code. Of course, SQL Profiles have some additional features that provide some distinct advantages, such as the following:
- SQL Profiles are turned on by default in 10g and above. They can be disabled by setting SQLTUNE_CATEGORY to FALSE. This parameter behaves in much the same way as the USE_STORED_OUTLINE parameter; however, it is a real parameter that is exposed via v$parameter and it retains its value across bounces. The value can be TRUE, FALSE, or a category name.
- SQL Profiles are assigned to categories just like Outlines. Each SQL Profile is assigned to a single category. The default category is DEFAULT. If SQLTUNE_CATEGORY is set to TRUE, outlines in the DEFAULT category are used. If SQLTUNE_CATEGORY is set to some other text string, only SQL Profiles in the category that matches the value of SQLTUNE_CATEGORY are used. As with Outlines, this parameter can be changed with an ALTER SESSION statement that allows SQL Profiles to be tested without enabling them for the whole database (more on this later).
- The DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure creates a SQL Profile for a given SQL statement. Any set of hints may be passed to the procedure. Although this procedure is not mentioned in the documentation (at least as of 12.1.0.1), it is used by STA and migration procedures. It is also referenced by at least one Oracle Support document (Note 215187.1) as a way of creating what I call a manual SQL Profile. This is a giant leap forward from Outlines. With the IMPORT_SQL_PROFILE procedure, you can create any hints you want and apply them to any statement you want.
- SQL Profiles have the ability to ignore literals when it comes to matching SQL statements. Think of this as being similar to the cursor_sharing parameter. This means you can have a SQL Profile that matches multiple statements that differ only in their use of literals—without having to set the cursor_sharing parameter for the whole instance. This attribute of a SQL Profile is called FORCE_MATCHING. When you create a SQL Profile, you tell it whether you want to set this attribute. If the attribute is set to TRUE, the Profile applies to all statements that have the same signature, regardless of the literals used in the statement.
- There is a view (DBA_SQL_PROFILES) that exposes the SQL profiles that have been created.
- As with all hints, SQL Profile hints are directives that are obeyed unless they are invalid. Invalid hints are silently ignored. An invalid hint does not necessarily cause other hints in the SQL Profile to be ignored or disabled, however.
- SQL Profiles appear to be able to apply most, if not all, valid hints.
STA is not the answer to plan stability issues. However, occasionally it is capable of finding a better plan than the one the optimizer comes up with for the reasons I already discussed. Sometimes I create a tuning task for a problem statement to see what suggestions STA might have. The example download for this book contains a number of scripts to help with this task (look for create_tuning_task.sql and accept_sql_profile.sql).
If STA recommends a SQL Profile, do yourself a favor and create it in an alternate category (TEST, for example). This allows you to review the hints and test the performance before making your users the guinea pigs in your experiment.
The hints can provide valuable information regarding where the optimizer is having problems. Remember that the OPT_ESTIMATE hint applies a scaling factor to various calculations based on its more thorough analysis.
Anywhere STA comes up with a very large or very small scaling factor is a direct pointer to a place in the plan where the optimizer is having trouble. Such a scaling factor can often point out a problem with statistics or, in some cases, a shortcoming of the optimizer itself. If it is an optimizer shortcoming and if the optimizer is going to keep making the same error no matter how the data change, then leaving an STA SQL Profile in place may be perfectly reasonable.
If, on the other hand, you’re looking for a way to lock in a specific plan, then you may want to consider creating another hint-based object (Profile, Baseline, or Patch) that contains directive hints instead of the OPT_ESTIMATE hint. This is fairly easy to accomplish, because all of these mechanisms can exist on the same statement. For example, you could accept the STA SQL Profile and then create a Baseline on the same statement. You could also use the lock_STA_profile.sql script from the example download to do away with the OPT_ESTIMATE-based profile and replace it with a SQL Profile using directive-type hints.
Listing 17-11 shows an example of a couple of scripts for finding SQL Profiles and statements that are using them.
Listing 17-11. Scripts for Finding SQL Profiles
SQL> @sql_profiles.sql
Enter value for sql_text:
Enter value for name:
NAME CATEGORY STATUS FORCE SQL_TEXT
------------------------------ --------- -------- ----- -----------------
PROFILE_fgn6qzrvrjgnz DEFAULT DISABLED NO select /*+ index(
PROFILE_8hjn3vxrykmpf DEFAULT DISABLED NO select /*+ invali
PROFILE_69k5bhm12sz98 DEFAULT DISABLED NO SELECT dbin.insta
PROFILE_8js5bhfc668rp DEFAULT DISABLED NO select /*+ index(
PROFILE_bxd77v75nynd8 DEFAULT DISABLED NO select /*+ parall
PROFILE_7ng34ruy5awxq DEFAULT DISABLED NO select i.obj#,i.t
SYS_SQLPROF_0126f1743c7d0005 SAVED ENABLED NO select avg(pk_col
PROF_6kymwy3guu5uq_1388734953 DEFAULT ENABLED YES select 1
PROFILE_cnpx9s9na938m_MANUAL DEFAULT ENABLED NO select /*+ opt_pa
PROF_79m8gs9wz3ndj_3723858078 DEFAULT ENABLED NO /* SQL Analyze(25
PROFILE_9ywuaagwscbj7_GPS DEFAULT ENABLED NO select avg(pk_col
PROF_arcvrg5na75sw_3723858078 DEFAULT ENABLED NO select /*+ index(
SYS_SQLPROF_01274114fc2b0006 DEFAULT ENABLED NO select i.table_ow
18 rows selected.
SQL> @find_sql_using_profile.sql
Enter value for sql_text:
Enter value for sql_id:
Enter value for sql_profile_name:
SQL_ID PLAN_HASH SQL_PROFILE
------------- ---------- ------------------------------
bqfx5q2jas08u 3755463150 SYS_SQLPROF_01281e513ace0000
SQL_TEXT
-------------------------------------------------------------
SELECT TASK_LIST.TASK_ID FROM (SELECT /*+ NO_MERGE(T) --
ORDERED */ T.TASK_ID FROM (SELECT * FROM DBA_ADVISOR_
TASKS ORDER BY TASK_ID DESC) T, DBA_ADVISOR_PARAMETERS_
PROJ P1, DBA_ADVISOR_PARAMETERS_PROJ P2 WHERE T.
ADVISOR_NAME='ADDM' AND T.STATUS = 'COMPLETED' AND
T.EXECUTION_START >= (SYSDATE - 1) AND T.HOW_CREATED
= 'AUTO' AND T.TASK_ID = P1.TASK_ID AND P1.PARAMETER_
NAME = 'INSTANCE' AND P1.PARAMETER_VALUE = SYS_CONTEXT
('USERENV','INSTANCE') AND T.TASK_ID = P2.TASK_ID AND
P2.PARAMETER_NAME = 'DB_ID' AND P2.PARAMETER_VALUE
= TO_CHAR(:B1 ) ORDER BY T.TASK_ID DESC) TASK_LIST
WHERE ROWNUM = 1
The sql_profiles.sql script queries DBA_SQL_PROFILES; the find_sql_using_profile.sql queries v$sql. The SQL Profiles with names that begin with SYS_SQLPROF are generated by STA; the others are created manually using the DBMS_SQLTUNE.IMPORT_SQL_PROFILE procedure.
Now that we’ve reviewed the basics of SQL Profiles, let’s create one. To do this, we use a script called create_1_hint_profile.sql that simply prompts for a sql_id and a hint, and then creates a SQL tProfile for the statement containing the hint. As you review the example in Listing 17-12, note how we have a SQL that uses an INDEX SKIP SCAN operation in the plan and we use a Profile to change the plan to use a FULL scan operation.
Listing 17-12. The create_1_hint_profile.sql Script
SQL> select /* test 1 hint */ avg(pk_col) from skew a where col1 = 222222;
AVG(PK_COL)
--------------------------------
15722222
1 row selected.
SQL> @find_sql
Enter value for sql_text: select /* test 1 hint */ avg(pk_col) from skew % 222222
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO
------------- ----- ---------- ----- --------- --------
0pvj94afp6faw 0 2650913906 1 .10 876
SQL_TEXT
-------------------------------------------------------------
select /* test 1 hint */ avg(pk_col)
from skew a where col1 = 222222
1 row selected.
SQL> @dcplan
Enter value for sql_id: 0pvj94afp6faw
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 0pvj94afp6faw, child number 0
-------------------------------------------------------------
select /* test 1 hint */ avg(pk_col) from skew a where col1 = 222222
Plan hash value: 2650913906
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 34 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 32 | 34 (0)|
|* 3 | INDEX SKIP SCAN | SKEW_COL2_COL1 | 32 | 5 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------
3 - access("COL1"=222222)
filter("COL1"=222222)
21 rows selected.
SQL> -- So it's using an index skip scan
SQL>
SQL> -- Now lets create a SQL Profile with a FULL hint
SQL>
SQL> @create_1_hint_sql_profile
Enter value for sql_id: 0pvj94afp6faw
Enter value for profile_name (PROFILE_sqlid_MANUAL): PROF_0pvj94afp6faw_FULL
Enter value for category (DEFAULT):
Enter value for force_matching (false):
Enter value for hint: FULL( A@SEL$1 )
Profile PROF_0pvj94afp6faw_FULL created.
SQL> select /* test 1 hint */ avg(pk_col) from skew a where col1 = 222222;
AVG(PK_COL)
--------------------------------
15722222
1 row selected.
SQL> @find_sql
Enter value for sql_text: select /* test 1 hint */ avg(pk_col) from skew a where col1 %
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- --------- ----- --------- ------- ------------------------
0pvj94afp6faw 0 568322376 1 6.34 162,309 select /* test 1 hint */
avg(pk_col) from skew a
where col1 = 222222
1 row selected.
SQL> -- Well it has a different plan hash value and it took a lot longer
SQL>
SQL> @dcplan
Enter value for sql_id: 0pvj94afp6faw
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
--------------------------------------------------------
SQL_ID 0pvj94afp6faw, child number 0
--------------------------------------------------------
select /* test 1 hint */ avg(pk_col) from skew a where col1 = 222222
Plan hash value: 568322376
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 28360 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| SKEW | 32 | 28360 (1)|
--------------------------------------------------------
Predicate Information (identified by operation id):
--------------------------------------------------------
2 - filter("COL1"=222222)
Note
--------------------------------------------------------
- SQL profile PROF_0pvj94afp6faw_FULL used for this statement
23 rows selected.
SQL> -- So it is using the SQL Profile and it did change to a FULL SCAN
SQL>
SQL> -- Let's check the hints in the SQL Profile
SQL>
SQL> @sql_profile_hints
Enter value for profile_name: PROF_0pvj94afp6faw_FULL
HINT
---------------------------------------------------------
FULL( A@SEL$1 )
1 rows selected.
SQL> -- Let's check the hints in the OTHER_XML field of V$SQL_PLAN
SQL>
SQL> @sql_hints
Enter value for sql_id: 0pvj94afp6faw
Enter value for child_no: 0
OUTLINE_HINTS
------------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
6 rows selected.
Notice that the hint was specified using the fully qualified alias for the skew table, FULL (A@SEL$1). This was done on purpose because Profiles and Baselines are more picky about object identification than those normal hints that are embedded in the SQL statement text. For example, it would be perfectly acceptable to use FULL (A) in the text of the SQL statement; but, if you put that into a SQL Profile, the optimizer does not know what to do with it (and so it silently ignores it). Notice also that the complete syntax for the FULL hint would also include the query block name as shown in the output from the sql_hints.sql script. Remember that this is the set of hints that Oracle thinks is necessary to recreate the plan, and thus is the set of hints that is used if you create an Outline on the statement. You may wonder how I knew that SEL$1 was the correct query block name to use. The answer is: Experience. And you know how I got the experience? By making lots of mistakes! Actually, because I know that the default query block names are SEL$1, UPD$1, and DEL$1, and this is a very simple query with only one query block and very little (if any) way that the optimizer could transform it to something else, it was a pretty good guess. But why guess when you can know? If you use DBMS_XPLAN.DISPLAY_CURSOR with the alias option, you can see exactly what the query block name and fully qualified aliases are (see Listing 17-13).
Listing 17-13. DBMS_XPLAN.DISPLAY_CURSOR with the Alias Option
SQL> @dcplan
Enter value for sql_id: 0pvj94afp6faw
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE +ALIAS
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID 0pvj94afp6faw, child number 0
-------------------------------------------------------------
select /* test 1 hint */ avg(pk_col) from skew a where col1 = 222222
Plan hash value: 568322376
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 28360 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| SKEW | 32 | 28360 (1)|
--------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / A@SEL$1
Predicate Information (identified by operation id):
-------------------------------------------------------------
2 - filter("COL1"=222222)
Note
-------------------------------------------------------------
- SQL profile PROF_0pvj94afp6faw_FULL used for this statement
29 rows selected.
Creating a SQL Profile to “Lock in” a Plan
SQL Profiles can also duplicate the functionality of Outlines, but without all the quirks, so you can create a SQL Profile using the same hints that an Outline uses (in other words, the ones in the other_xml column). The goal is to have all the hints necessary to “lock in” the plan. There is no way to guarantee the plan will never be able to change, but the technique works fairly well. It is actually quite easy to create a SQL Profile using the hints that an Outline would use, and of course there is a script in the example download to help you out (create_sql_profile.sql). Listing 17-14 shows an example.
Listing 17-14. The create_sql_profile.sql Script
SQL> select /* NOT IN */ department_name
2 from hr.departments dept
3 where department_id not in (select department_id from hr.employees emp);
no rows selected
SQL> @find_sql
Enter value for sql_text: select /* NOT IN */ department_name%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- -------------------
875qbqc2gw2qz 0 4201340344 3 .00 9 select /* NOT IN */
department_name
1 row selected.
SQL> @dcplan
Enter value for sql_id: 875qbqc2gw2qz
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------
SQL_ID 875qbqc2gw2qz, child number 0
-------------------------------------------------------------------------
select /* NOT IN */ department_name from hr.departments dept
where department_id not in (select department_id from hr.employees emp)
Plan hash value: 4201340344
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | MERGE JOIN ANTI NA | | 17 | 1 (0)|
| 2 | SORT JOIN | | 27 | 0 (0)|
| 3 | TABLE ACCESS BY INDEX | DEPARTMENTS | 27 | 0 (0)|
| | ROWID BATCHED | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 0 (0)|
|* 5 | SORT UNIQUE | | 107 | 1 (0)|
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1 (0)|
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
25 rows selected.
SQL> @create_sql_profile
Enter value for sql_id: 875qbqc2gw2qz
Enter value for child_no (0):
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE):
SQL Profile PROF_875qbqc2gw2qz_4201340344 created.
SQL> select /* NOT IN */ department_name
2 from hr.departments dept
3 where department_id not in
4 (select department_id from hr.employees emp);
no rows selected
SQL> @find_sql
Enter value for sql_text: select /* NOT IN */ department_name%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- -------------------
875qbqc2gw2qz 1 4201340344 1 .01 17 select /* NOT IN */ department_name
1 row selected.
SQL> @dcplan
Enter value for sql_id: 875qbqc2gw2qz
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 875qbqc2gw2qz, child number 1
----------------------------------------------------------------------
select /* NOT IN */ department_name from hr.departments dept
where department_id not in (select department_id from hr.employees emp)
Plan hash value: 4201340344
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 (100)|
| 1 | MERGE JOIN ANTI NA | | 17 | 1 (0)|
| 2 | SORT JOIN | | 27 | 0 (0)|
| 3 | TABLE ACCESS BY INDEX | DEPARTMENTS | 27 | 0 (0)|
| | ROWID BATCHED | | | |
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | 0 (0)|
|* 5 | SORT UNIQUE | | 107 | 1 (0)|
| 6 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
Note
-----
- SQL profile PROF_875qbqc2gw2qz_4201340344 used for this statement
29 rows selected.
SQL> @sql_profile_hints
Enter value for profile_name: PROF_875qbqc2gw2qz_4201340344
HINT
-------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$5DA710D3")
UNNEST(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$2")
INDEX(@"SEL$5DA710D3" "DEPT"@"SEL$1" ("DEPARTMENTS"."DEPARTMENT_ID"))
FULL(@"SEL$5DA710D3" "EMP"@"SEL$2")
LEADING(@"SEL$5DA710D3" "DEPT"@"SEL$1" "EMP"@"SEL$2")
USE_MERGE(@"SEL$5DA710D3" "EMP"@"SEL$2")
12 rows selected.
So, this is handy if you have a SQL statement in the shared pool with a plan that you like.
Creating a SQL Profile Using AWR
But, what if you have a statement that goes bad and there is no longer a copy of the good plan in the shared pool? No problem, as long as your AWR retention allows you to get back to a previous execution that used a plan you like, because all the hints are stored in the other_xml column of the dba_hist_sql_plan table along with the rest of the plan data. So, it is a relatively simple matter to create a SQL Profile using those hints to restore your previous plan (while you go looking for the reason it went south in the first place). Of course there is a script for that one as well (create_sql_profile_awr.sql). Listing 17-15 shows an example of its use (note that this example was run in 10g because it’s easier to get the optimizer to behave badly in 10g than in 11g or 12c).
Listing 17-15. The create_sql_profile_awr.sql Script
SYS@LAB1024#x003E; @awr_plan_change
Enter value for sql_id: 05cq2hb1r37tr
SNAP_ID NODE BEGIN_INTERVAL_TIME PLN_HSH_VAL EXECS AVG_ETIME AVG_LIO
------- ---- ---------------------- ----------- ----- --------- ---------
9532 1 12-AUG-13 15.00.09.212 68322376 1 90.339 162,298
9534 1 12-AUG-13 10.00.08.716 1 51.715 162,298
9535 1 13-AUG-13 18.00.10.280 4 23.348 162,298
9536 1 15-AUG-13 16.00.05.439 3723858078 1 622.170 9,218,284
SYS@LAB1024#x003E;
SYS@LAB1024#x003E; -- statement 05cq2hb1r37tr has taken a turn for the worse
SYS@LAB1024#x003E; -- let's get it back to plan 568322376
SYS@LAB1024#x003E;
SYS@LAB1024#x003E; @create_sql_profile_awr
Enter value for sql_id: 05cq2hb1r37tr
Enter value for plan_hash_value: 568322376
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE):
SQL Profile PROF_05cq2hb1r37tr_568322376 created.
SYS@LAB1024#x003E; @sql_profile_hints
Enter value for profile_name: PROF_05cq2hb1r37tr_568322376
HINT
-------------------------------------------------------------
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('10.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "A"@"SEL$1")
5 rows selected.
This approach is very handy if you have a statement that ran well at some point and AWR captured it.
Creating a SQL Profile by Using Another SQL Plan
What if you need to tune a statement from scratch, but you don’t have access to the code? Well, SQL Profiles have one more trick up their sleeve. Because we have already demonstrated that we can build SQL Profiles with any set of hints and associate them with any SQL statement, and because we have shown we can use other_xml as a source of hints, why not move a set of hints from one statement to another? This allows you to take a statement and manipulate it to get the plan you want (via hints, alter session statements, and so forth) and then create a SQL Profile on your unmanipulated statement using the hints from your manipulated statement. And, of course, there is a script in the example download to do this (move_sql_profile.sql). There are several steps to this process. First, we need to identify the statement and get its sql_id, then we need to make a copy of it to manipulate, then we need to create a SQL Profile on the new manipulated version, and, last, we need to move the hints to the original statement. Listing 17-16 shows an example.
Listing 17-16. The move_sql_profile.sql Script
SQL> select count(*) from skew where col3 = '01-jan-10';
COUNT(*)
-------------------------------------------------------------
0
1 row selected.
SQL> @find_sql
Enter value for sql_text: select count(*) from skew where col3 = %
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- -----------------
4cp821ufcwvgc 0 3438766830 1 .39 675 select count(*)
from skew where
col3 = '01-jan-10'
1 row selected.
SQL> @dcplan
Enter value for sql_id: 4cp821ufcwvgc
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
SQL_ID 4cp821ufcwvgc, child number 0
-------------------------------------------------------------
select count(*) from skew where col3 = '01-jan-10'
Plan hash value: 3438766830
-------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 3 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| COL3_INDEX | 1 | 3 (0)|
-------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------------
2 - access("COL3"='01-jan-10')
19 rows selected.
So we have identified our statement and found the sql_id. Now let’s create another version of the statement and force it to use a different index. We do this by adding a hint to the select statement text (Listing 17-17).
Listing 17-17. Adding an Inline Hint to the Select Statement Text
SQL> -- let's create a statement that does the same
SQL> -- thing but uses a different index
SQL>
SQL> select /*+ index (skew skew_col3_col2_col1) */ count(*)
2 from skew where col3 = '01-jan-10';
COUNT(*)
-------------------------------------------------------------
0
1 row selected.
SQL> @find_sql
Enter value for sql_text: select /*+ index (skew skew_col3_col2_col1) */ count(*)%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- ---------------------
09gdkwq1bs48h 0 167097056 1 .06 8 select /*+ index(skew
skew_col3_col2_col1)
*/ count(*) from skew
where '01- jan-10'
1 row selected.
SQL> @dcplan
Enter value for sql_id: 09gdkwq1bs48h
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 09gdkwq1bs48h, child number 0
----------------------------------------------------------------------
select /*+ index (skew skew_col3_col2_col1) */ count(*) from skew
where col3 = '01-jan-10'
Plan hash value: 167097056
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SKEW_COL3_COL2_COL1 | 1 | 4 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------
2 - access("COL3"='01-jan-10')
20 rows selected.
In Listing 17-17, you created a new statement (SQL_ID: 09gdkwq1bs48h) that has the same structure but uses a different execution plan (because of the hint). The next step is to create a SQL Profile on the new statement. We do this with the create_sql_profile.sql script, as shown in Listing 17-18.
Listing 17-18. The create_sql_profile.sql Script
SQL> -- now let's create a profile on our new statement
SQL>
SQL> @create_sql_profile
Enter value for sql_id: 09gdkwq1bs48h
Enter value for child_no (0):
Enter value for profile_name (PROF_sqlid_planhash):
Enter value for category (DEFAULT):
Enter value for force_matching (FALSE):
SQL Profile PROF_09gdkwq1bs48h_167097056 created.
SQL> select /*+ index (skew skew_col3_col2_col1) */ count(*)
2 from skew where col3 = '01-jan-10';
COUNT(*)
-------------------------------------------------------------
0
1 row selected.
SQL> @find_sql
Enter value for sql_text: select /*+ index (skew skew_col3_col2_col1) */ count(*)%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- ---------------------
09gdkwq1bs48h 0 167097056 1 .01 16 select /*+ index(skew
skew_col3_col2_col1)
*/ count(*) from skew
where '01- jan-10'
1 row selected.
SQL> @dcplan
Enter value for sql_id: 09gdkwq1bs48h
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 09gdkwq1bs48h, child number 0
----------------------------------------------------------------------
select /*+ index (skew skew_col3_col2_col1) */ count(*) from skew
where col3 = '01-jan-10'
Plan hash value: 167097056
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SKEW_COL3_COL2_COL1 | 1 | 4 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------
2 - access("COL3"='01-jan-10')
Note
----------------------------------------------------------------------
- SQL profile PROF_09gdkwq1bs48h_167097056 used for this statement
24 rows selected.
The last step is to move the newly created SQL Profile to the original statement. We do this with the move_sql_profile.sql script in Listing 17-19. Then, we verify that the SQL Profile is being used and has the desired effect.
Listing 17-19. The move_sql_profile.sql Script
SQL> -- let's attach that same SQL Profile on to our original statement
SQL>
SQL> @move_sql_profile
Enter value for profile_name: PROF_09gdkwq1bs48h_167097056
Enter value for sql_id: 4cp821ufcwvgc
Enter value for category (DEFAULT):
Enter value for force_matching (false):
PL/SQL procedure successfully completed.
SQL> select count(*) from kso.skew where col3 = '01-jan-10';
COUNT(*)
-------------------------------------------------------------
0
1 row selected.
SQL> @find_sql
Enter value for sql_text: select count(*) from kso.skew where col3 = %
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- ----------------
4cp821ufcwvgc 0 167097056 1 .12 16 select count(*)
from skew where
col3 = '01-jan-10'
1 row selected.
SQL> @dcplan
Enter value for sql_id: 4cp821ufcwvgc
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
SQL_ID 4cp821ufcwvgc, child number 0
----------------------------------------------------------------------
select count(*) from skew where col3 = '01-jan-10'
Plan hash value: 167097056
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | INDEX RANGE SCAN| SKEW_COL3_COL2_COL1 | 1 | 4 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------
2 - access("COL3"='01-jan-10')
Note
----------------------------------------------------------------------
- SQL profile PROFILE_4cp821ufcwvgc_moved used for this statement
23 rows selected.
As you can see, the move worked and the new plan is in effect for the original statement. Moving SQL Profiles from one statement to another is a very useful technique and very easy to do. It basically allows you to manipulate a SQL statement until you get the plan you want and then attach the plan to a statement you can’t touch. There are a few restrictions you should be aware of, however:
- You cannot change the structure of the statement. Remember that SQL Profile hints are very specific when it comes to query block names. Anything that changes the query blocks does not work.
- You cannot change any object aliases. Remember that all hints must reference objects by alias names (if aliases exist in the statement). Adding, removing, or changing an alias name in your manipulated statement creates hints that won’t match the original, and so they are silently ignored.
Using FORCE_MATCHING with SQL Profiles
As mentioned earlier, SQL Profiles have a FORCE_MATCHING attribute that allows you to create a Profile with the ability to ignore literals, similar to how the cursor_sharing parameter works—converting literals to bind variables. Therefore, if you have a SQL Profile that matches multiple statements that differ only in their use of literals, setting the FORCE_MATCHING attribute of the Profile to TRUE allows the Profile to be used for all the statements, not just the one SQL that matches exactly the SQL text used originally to create the Profile. This is a fantastic option in the case when you either can’t or don’t want to change the cursor_sharing parameter at the instance level. And, it is a feature unique to SQL Profiles in that Baselines don’t use such an attribute.
There is one “gotcha” with the use of FORCE_MATCHING, however. This option works great if you’re working with SQL that has been generated from a tool and is formulated with all literals. But, if the SQL you wish to create a FORCE_MATCHING Profile for includes both literal strings and bind variables, you’ll run in to a bit of a problem, as shown in Listing 17-20. First, let’s test two queries that differ only by the literal string used. Notice that the FORCE_MATCHING_SIGNATURE (from v$sql) for each query is the same.
Listing 17-20. SQL Profile Using the FORCE_MATCHING Restriction
SQL>
SQL>variable v1 varchar2(10)
SQL>exec :v1 := 'Sunday';
PL/SQL procedure successfully completed.
SQL>
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011 ;
CT
-------
365
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012 ;
CT
-------
366
SQL>
SQL>select /* getfm */ sql_id, plan_hash_value, force_matching_signature,
2 substr(sql_text,1,200) sql_text
3 from v$sql
4 where upper(sql_text) like '%KMFMTST00%'
5 and sql_text not like '%/* getfm */%' ;
SQL_ID PLAN_HV FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ---------- ------------------------ ------------------------
6sz5sqg1yu2u7 3996576519 9139782190997132164 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2011
88bgq57sjbtkt 3996576519 9139782190997132164 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2012
Now, let’s make a change to the SQL to add a bind variable in addition to the literal. Notice what happens to the FORCE_MATCHING_SIGNATURE:
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2011
2 and mcal_day_name = :v1 ;
CT
-------
52
SQL>select /* kmfmtst00 */ count(*) ct from km1 where mcal_year = 2012
2 and mcal_day_name = :v1 ;
CT
-------
53
SQL>
SQL>select /* getfm */ sql_id, plan_hash_value, force_matching_signature,
2 substr(sql_text,1,200) sql_text
3 from v$sql
4 where upper(sql_text) like '%KMFMTST00%'
5 and sql_text not like '%/* getfm */%' ;
SQL_ID PLAN_HV FORCE_MATCHING_SIGNATURE SQL_TEXT
------------- ---------- ------------------------ ------------------------
6sz5sqg1yu2u7 3996576519 9139782190997132164 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2011
88bgq57sjbtkt 3996576519 9139782190997132164 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2012
48rxh2r545xqy 3996576519 5839486434578375421 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2011
and mcal_day_name = :v1
6q610fykrr4d3 3996576519 8791659410855071518 select /* kmfmtst00 */
count(*) ct from km1
where mcal_year = 2012
and mcal_day_name = :v1
As you can see, when we add a bind variable to the SQL, it causes FORCE_MATCHING_SIGNATURE to become unique, which means that if we were to create a SQL Profile and set the FORCE_MATCHING attribute to TRUE on the statement that uses both a bind variable and a literal, the Profile does not work. Well, actually, it does work, but only on the one specific statement we used to create the Profile. Just make sure to keep this in mind when creating a Profile with FORCE_MATCHING_SIGNATURE set.
So, to wrap up the section on SQL Profiles, let me state that I believe they provide a very powerful tool for controlling execution plans. The ability to match multiple statements via the FORCE_MATCHING attribute and the ability to attach any set of hints to a statement via the IMPORT_SQL_PROFILE procedure sets SQL Profiles apart as one of the most useful tools in our tool belt. But remember, they are a tool and should be used carefully and consciously. If you use a Profile to remedy a problem situation but you don’t take time to go back and evaluate why there was a problem in the first place, you’ll end up with a lot of patches over a leak that may continue to get worse and worse. Always try to find and fix the root cause of the problem so that you can disable or drop Profiles after the problem is corrected. In this way, you can be assured the leak has been fixed, not just patched.
Oracle Database 11g provided a new method of dealing with plan instability. The third iteration of Oracle’s hint-based mechanisms for influencing execution plans is called a SQL Plan Baseline (Baseline, for short). With Baselines, the design goal has morphed into eliminating backward movement (“performance regressions,” as the Oracle documentation calls them)—in other words, not allowing a statement to switch to a plan that is significantly slower than the one it has already been executing. This new mechanism depends on Baselines, which look very much like SQL Profiles; in fact, they are actually stored in the same structure in the data dictionary.
Baselines are, at their core, a set of hints given a name and attached to a specific SQL statement. They are associated with a SQL statement using the same “normalized” text matching as Outlines and SQL Profiles. Here are some key features of Baselines:
- Baselines are used by default if they exist. There is a parameter to control whether they are used (OPTIMIZER_USE_SQL_PLAN_BASELINE). It is set to TRUE by default.
- Baselines are not created by default. So, like the older Outlines or SQL Profiles, you must do something to create them.
- The concept of categories has disappeared from Baselines.
- Unlike Outlines and Profiles, you can have multiple plans within a Baseline for each SQL statement. In an even more confusing twist, there’s a concept of a preferred set of Baselines called the fixed set.
- One of the key features of Baselines is that they are the first hint-based mechanism to have knowledge of the plan that was used to create them. That is to say, they store a plan_hash_value along with the hints. So, if a Baseline is applied to a statement and the optimizer doesn’t come up with the same plan_hash_value that it had when the Baseline was created, all the hints are thrown out and the optimization is redone without any of the hints.
Note It doesn’t actually happen in this order, but the point is that this mechanism is very different from Outlines and Profiles, in which the optimizer has no idea what plan the hints were trying generate. With Baselines, it does.
There is a view called dba_sql_plan_baselinesthat exposes the Baselines that have been created.
Just like Outlines and SQL Profiles, Baselines apply to all instances in a RAC environment. They are not localized to a specific instance.
SQL PLAN MANAGEMENT INFRASTRUCTURE
Baselines are a part of the SQL Plan Management (SPM) infrastructure introduced in 11g. The concept of SPM is to have a Baseline associated with every statement that runs through the database. The optimizer then uses the Baselines to attempt to recreate the original plans from which they were created.
Every time a statement is parsed, the optimizer goes through its normal process, including coming up with an execution plan. It then checks to see whether the plan it just came up with is already stored in a Baseline. If it is, the optimizer uses that plan. If it’s not, the optimizer uses the Baseline plan and stores the alternate plan in the history for later evaluation with the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function (assuming that the database is configured to do this).
The approach of saving plans for later evaluation sounds like a great idea to limit instability resulting from unexpected plan changes. The only real downside to this approach is that seeding the Baselines can be a difficult task, and it is not done by default. Although the result is that many shops have not fully embraced this feature yet, I see it being used more and more, at least for key SQL statements.
You can do pretty much the same things with Baselines that you can do with Outlines and SQL Profiles. For example, you can find a list of them, see what hints are contained by them, see what their status is, see which SQL statements are using them, and so on. Listing 17-21 shows a quick example using a few scripts from the example download.
Listing 17-21. Using Baselines
SQL> @find_sql_using_baseline
Enter value for sql_text:
Enter value for sql_id:
Enter value for plan_hash_value:
SQL_ID PLAN_HASH SQL_PLAN_BASELINE AVG_ETIME SQL_TEXT
------------- ---------- ------------------------------ --------------------------------
04s94zftphcgb 2650913906 SQL_PLAN_3mmrpt1hutfzs7456d135 .00 select sum(pk_col) from
12417fbdsfaxt 2333976600 SQL_PLAN_0j493a65j2bamc0e39d1a .01 SELECT SQL_HANDLE FROM DB
2us663zxp440c 329476029 SQL_PLAN_6dny19g5cvmaj059cc611 .04 /* OracleOEM */ select at
3972rvxu3knn3 3007952250 SQL_PLAN_05a32329hrft07347ab53 .00 delete from sdo_geor_ddl_
3007952250 SQL_PLAN_05a32329hrft07347ab53 .00 delete from sdo_geor_ddl_
62m44bym1fdhs 3137838658 SQL_PLAN_2jvcuyb2j5t1g4d67c3d9 .00 SELECT ID FROM WWV_FLOW_M
3137838658 SQL_PLAN_2jvcuyb2j5t1g4d67c3d9 .00 SELECT ID FROM WWV_FLOW_M
6abthk1u14yb7 2848324471 SQL_PLAN_5y7pbdmj87bz3ea394c8e .00 SELECT VERSION FROM V$INS
2848324471 SQL_PLAN_5y7pbdmj87bz3ea394c8e .00 SELECT VERSION FROM V$INS
9xw644rurr1nk 2848324471 SQL_PLAN_ba7pvw56m6m1cea394c8e .00 SELECT REGEXP_REPLACE(VER
aukfj0ur6962z 2366097979 SQL_PLAN_adx60prqvaaqhf8e55c8a .00 SELECT VALUE V FROM WWV_F
2366097979 SQL_PLAN_adx60prqvaaqhf8e55c8a .00 SELECT VALUE V FROM WWV_F
b1um9gxnf22a3 1475283301 SQL_PLAN_1kj53db9w5gzga4a6b425 .00 select count(*) from sqll
d56r760yr1tgt 2650913906 SQL_PLAN_dn32tuq14sj5q7456d135 .01 select sum(pk_col) from
f1b04310fhv7a 2650913906 SQLID_AR5DZ1STDPFC6_2650913906 .00 select sum(pk_col) from
fg5u3ydzcqzvw 3291240065 SQL_PLAN_3ndjuqr0f58a716c3d523 .03 select spb.sql_handle, sp
3291240065 SQL_PLAN_3ndjuqr0f58a716c3d523 .03 select spb.sql_handle, sp
17 rows selected.
SQL> @baselines
Enter value for sql_text: %skew%
Enter value for name:
Enter value for plan_name:
SQL_HANDLE PLAN_NAME SQL_TEXT ENABLED ACC FI
---------------- ------------------ ------------------- ------- --- --
SYS_SQL_17fbdf94 SQL_PLAN_1gyyzkj90 select avg(pk_col) YES NO NO
SQL_PLAN_1gyyzkj90 select avg(pk_col) YES NO NO
SYS_SQL_36bf1c88 SQL_PLAN_3dgswj3vr select avg(pk_col) YES NO NO
SQL_PLAN_3dgswj3vr select avg(pk_col) NO YES NO
SYS_SQL_39cef5c8 SQL_PLAN_3mmrpt1hu select sum(pk_col) YES YES NO
SYS_SQL_3a363ab5 SQL_PLAN_3ndjuqr0f select spb.sql_hand YES YES NO
SYS_SQL_3c55382b SQL_PLAN_3sp9s5cpk select sum(pk_col) YES YES NO
SYS_SQL_94dc89c0 SQL_PLAN_99r49s08j select avg(pk_col) YES NO NO
SQL_PLAN_99r49s08j select avg(pk_col) YES NO NO
SYS_SQL_d0686c14 SQL_PLAN_d0u3c2kat select avg(pk_col) YES YES NO
SYS_SQL_da0c59d5 SQL_PLAN_dn32tuq14 select sum(pk_col) YES YES NO
SYS_SQL_f1140cdd DODA select sql_id, chil YES YES NO
SYS_SQL_f5cd6b7b SQLID_F1B04310FHV7 select sum(pk_col) YES YES NO
13 rows selected.
SQL> @baseline_hints
Enter value for baseline_plan_name: SQLID_F1B04310FHV7A_2650913906
OUTLINE_HINTS
-----------------------------------------------------------------------IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.1')
DB_VERSION('12.1.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_SS(@"SEL$1" "SKEW"@"SEL$1" ("SKEW"."COL2" "SKEW"."COL1"))
6 rows selected.
The naming of Baselines is not particularly friendly. The sql_handle is a unique identifier for a SQL statement whereas the sql_plan_name is a unique identifier for a plan. By the way, the sql_plan_name is also called sql_plan_baseline in the v$sql view.
There are many ways to create Baselines. They can be created automatically for every statement that is executed by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE. They can also be created for statements in a SQL Tuning Set using the LOAD_PLANS_FROM_SQLSET function, or they can be migrated from Outlines using the MIGRATE_STORED_OUTLINE function. These mechanisms are primarily designed for seeding Baselines when doing migrations.
Creating a Baseline for an individual statement that is already in the cursor cache can be accomplished via the DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function. All the function needs is a sql_id and a plan_hash_value. Optionally, a parameter can be used to define the baseline as FIXED. If it’s FIXED then it gets priority over any other Baselines for that statement, except other FIXED Baselines. Confused? Well, it’s not exactly the most straightforward setup. Keeping it simple, I’d think one FIXED Baseline is plenty. After all, you’re looking to minimize plan changes. So, with that said, let’s look at an example of creating a Baseline for a single statement in Listing 17-22.
Listing 17-22. Creating a Baseline for a Single Statement
SQL> select sum(pk_col) from skew where col1=666666;
SUM(PK_COL)
-----------
517333312
SQL> @find_sql
Enter value for sql_text: %66666%
Enter value for sql_id:
SQL_ID CHILD PLAN_HASH EXECS AVG_ETIME AVG_LIO SQL_TEXT
------------- ----- ---------- ----- --------- ------- ------------------
dv1qm9crkf281 0 2650913906 1 .08 45 select sum(pk_col)
from skew
col1=666666
SQL> @create_baseline
Enter value for sql_id: dv1qm9crkf281
Enter value for plan_hash_value: 2650913906
Enter value for fixed (NO):
Enter value for enabled (YES):
Enter value for plan_name (ID_sqlid_planhashvalue):
Baseline SQLID_DV1QM9CRKF281_2650913906 created.
SQL> select sql_handle, plan_name, sql_text
2 from dba_sql_plan_baselines where sql_text like '%66666%';
SQL_HANDLE PLAN_NAME SQL_TEXT
------------------------ ------------------------------ -------------------
SYS_SQL_8a22ceb091365064 SQLID_DV1QM9CRKF281_2650913906 select sum(pk_col)
from skew
1 row selected.
SQL> select sum(pk_col) from skew where col1=666666;
SUM(PK_COL)
-----------
517333312
1 row selected.
SQL> /
SUM(PK_COL)
-----------
517333312
1 row selected.
SQL> @dcplan
Enter value for sql_id: dv1qm9crkf281
Enter value for child_no:
Enter value for format: BASIC +ROWS +COST +PREDICATE
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
SQL_ID dv1qm9crkf281, child number 1
----------------------------------------------------------------------------
select sum(pk_col) from skew where col1=666666
Plan hash value: 2650913906
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 34 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| SKEW | 32 | 34 (0)|
|* 3 | INDEX SKIP SCAN | SKEW_COL2_COL1 | 32 | 5 (0)|
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
----------------------------------------------------------------------------
3 - access("COL1"=666666)
filter("COL1"=666666)
Note
----------------------------------------------------------------------------
- SQL plan baseline SQLID_DV1QM9CRKF281_2650913906 used for this statement
46 rows selected.
Listing 17-22 shows the use of the create_baseline.sql script that creates a Baseline on an existing statement in the shared pool. The script also renames the Baseline to something more meaningful (SQLID_sqlid_planhash by default). This renaming works only in 11gR2 and above, by the way; 11gR1 allows you to rename a Baseline, but there is a bug that causes a statement that uses a renamed Baseline to fail. Consequently, the create_baseline.sql script does not rename Baselines if the version is not 11.2 or higher.
Creating SQL Baselines from AWR
Baselines can also be used to retrieve a plan from the AWR history, although it’s not quite as straightforward as getting the plan from the cursor cache. Listing 17-23 shows an of example of doing this with the create_baseline_awr.sql script.
Listing 17-23. The create_baseline_awr.sql Script
SQL> @find_sql_awr
Enter value for sql_text: %cursor%skew%
Enter value for sql_id:
SQL_ID SQL_TEXT
------------- -------------------------------------------------------------
3ggjbbd2varq2 select /*+ cursor_sharing_exact */ avg(pk_col) from skew
where col1 = 1
48up9g2j8dkct select /*+ cursor_sharing_exact */ avg(pk_col) from skew
where col1 = 136135
2z6s4zb5pxp9k select /*+ opt_param('cursor_sharing' 'exact') */ avg(pk_col)
from skew where
13krz9pwd6a88 select /*+ opt_param('cursor_sharing=force') */ avg(pk_col)
from skew
4 rows selected.
SQL> @dplan_awr
Enter value for sql_id: 3ggjbbd2varq2
Enter value for plan_hash_value:
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 3ggjbbd2varq2
-------------------------------------------------------------------------
select /*+ cursor_sharing_exact */ avg(pk_col) from skew where col1 = 1
Plan hash value: 568322376
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 28366 (100)| |
| 1 | SORT AGGREGATE | | 1 | 24 | | |
| 2 | TABLE ACCESS FULL| SKEW | 3149K| 72M| 28366 (1)| 00:05:41 |
---------------------------------------------------------------------------
15 rows selected.
SQL> @find_sql
Enter value for sql_text:
Enter value for sql_id: 3ggjbbd2varq2
no rows selected
SQL> -- so it's not in the cursor cache
SQL>
SQL> @create_baseline_awr
Enter value for SQL_ID: 48up9g2j8dkct
Enter value for PLAN_HASH_VALUE: 568322376
Enter value for fixed (NO):
Enter value for enabled (YES):
Enter value for plan_name (ID_sqlid_planhashvalue):
Baseline SQLID_48UP9G2J8DKCT_568322376 created.
SQL>
SQL> select sql_handle, plan_name, sql_text
2 from dba_sql_plan_baselines where plan_name like 'SQLID_48UP9G2J8DKCT_568322376';
SQL_HANDLE PLAN_NAME
------------------------ ------------------------------
SYS_SQL_d52c57087080269e SQLID_48UP9G2J8DKCT_568322376
SQL_TEXT
--------------------------------------------------------
select /*+ cursor_sharing_exact */ avg(pk_col)
1 row selected.
Whether you’ve set the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES parameter to TRUE to create Baselines for every statement automatically or you are creating them manually, what happens when Baselines need to be updated? Because Baselines are accepted and put into use, the plan “locked in” by a Baseline may, over time, have different plans generated for it. Those plans may be better or they may be worse, but they are simply added to the statement’s plan history until they can be verified and not used. This is the whole idea behind plan stability; plans won’t change, but sometimes better plans are developed and must be evaluated, verified, and evolved to be used.
Plans that have been generated and stored in a statement’s plan history but not yet accepted for use can be verified using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE function. Each unaccepted plan is executed, and its performance is compared with that of the existing accepted plan. To make it a fair comparison, the conditions in effect at the time the unaccepted plan was added are used (things like instance parameters, bind variables, and so forth). If the new plan’s performance exceeds the current one, it is accepted and added to the Baseline. Listing 17-24 shows an example of an Evolve SQL Plan Baseline Report.
Listing 17-24. An Evolve SQL Plan Baseline Report
SQL> -- Check the status of plans in plan history.
SQL> select plan_name, enabled, accepted from dba_sql_plan_baselines;
PLAN_NAME ENABLED ACCEPTED
----------------------------- ------- --------
SYS_SQL_PLAN_d52c57084a620f25 YES YES
SYS_SQL_PLAN_d52c57087080269e YES NO
SQL> var report clob;
SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure successfully completed.
SQL> print :report
REPORT
--------------------------------------------------------------------
Evolve SQL Plan Baseline Report
--------------------------------------------------------------------
Inputs:
--------------------------------------------------------------------
SQL_HANDLE =
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SYS_SQL_PLAN_d52c57087080269e
--------------------------------------------------------------------
Plan was verified: Time used .1 seconds.
Passed performance criterion: Compound improvement ratio >= 10.13
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
Rows Processed: 960 960
Elapsed Time(ms): 19 15 1.27
CPU Time(ms): 18 15 1.2
Buffer Gets: 1188 116 10.24
Disk Reads: 0 0
Direct Writes: 0 0
Fetches: 0 0
Executions: 1 1
--------------------------------------------------------------------
Report Summary
--------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
SQL> -- Check the status of plans in plan history to verify acceptance.
SQL> select plan_name, enabled, accepted from dba_sql_plan_baselines;
PLAN_NAME ENABLED ACCEPTED
----------------------------- ------- --------
SYS_SQL_PLAN_d52c57084a620f25 YES YES
SYS_SQL_PLAN_d52c57087080269e YES YES
You can also evolve an unaccepted plan from plan history by using STA. If you have access to Enterprise Manager, using STA from that tool is likely the quickest and easiest way to do this. But, if you don’t have access to Enterprise Manager or if you just like having a bit more control over the process, here are the basic steps to do it from the command line (in other words, SQL*Plus):
- Create a tuning task for a single SQL statement or for multiple statements. See create_tuning_task.sql.
- • There are actually three parts to this: creating the tuning task, executing it, and then reporting on it.
- • DBMS_SQLTUNE.CREATE_TUNING_TASK
- • DBMS_SQLTUNE.EXECUTE_TUNING_TASK
- • DBMS_SQLTUNE.REPORT_TUNING_TASK
- • Note the default time limit for the execute part is 30 minutes, so you may want to reduce that (the create_tuning_task.sql script prompts you for a value in seconds). By the way, the reason it can take so long is that the advisor can actually execute parts of the statement to get better estimates of the number of rows returned by a particular step.
- • Primary recommendations are often to accept a SQL Profile, but there may be other recommendations such as creating indexes and so forth.
- • The report output shows the old plan and the proposed new plan.
- Review recommendations.
- • Never, ever, ever (I really mean it) blindly accept a recommendation.
- • Look at the new plan as proposed by REPORT_TUNING_TASK.
- • Specifically, evaluate the proposed plan and, if possible and if time permits, test it further.
- Prepare a script to disable the SQL Profile to be created. See disable_sql_profile.sql.
- • You do have a back-out plan right?
- • It is very easy to disable a profile. However, once a plan is selected, it is not changed. The execution continues to use that plan until it finishes.
- Accept the SQL Profile. See accept_tuning_task.sql (or accept_sql_profile.sql).
- • DBMS_SQLTUNE.ACCEPT_TUNING_TASK
- Confirm the performance improvement. See find_sql.sql, find_sql_stats.sql, dcplan.sql.
- • Existing active cursors continue processing.
- • A new child cursor is created using the Profile, and subsequent executions use it.
- • You can compare the performance of the old plan and the new one.
- Remember, SQL Profiles do not lock the execution plan.
- Figure out why the plan went crazy in the first place.
Plan evolution can be done manually or it can be automated by scheduling it to run during a maintenance window. But, allowing automatic evolution of plans can be a bit risky in that you forgo any oversight on your part. I’m not that trusting, but if you’re comfortable, go for it.
Automatic Plan Evolution in 12c
Beginning in 12c, automatic plan evolution has been enhanced to be done by the SQL Plan Management Evolve Advisor. This advisor is an AutoTask named SYS_AUTO_SPM_EVOLVE_TASK and it operates during the nightly maintenance window to evolve unaccepted plans automatically. Because the maintenance window is time limited by default, the unaccepted plans are ranked from oldest to newest, then the process attempts to evolve as many plans as possible before the end of the maintenance window.
Any unaccepted plans that are evaluated to perform better than any existing accepted plans in their SQL plan Baseline are accepted automatically—without any intervention from you. But, if an unaccepted plan doesn’t measure up to current performance measurements, it remains unaccepted and is held for at least another 30 days before the AutoTask job tries to evolve it again (but only if the LAST_EXECUTED date has been updated to indicate the statement was run within that 30-day period).
You can still evolve unaccepted plans manually or through Enterprise Manager, but using the DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE method as described in Listing 17-24 should be changed to use the evolve advisor, because this procedure has been deprecated. Instead, the steps to evolve plans manually include the following:
- Use DBMS_SPM.CREATE_EVOLVE_TASK to create the evolve task for the plan you want to evolve.
- Use DBMS_SPM.EXECUTE_EVOLVE_TASK with the task name from the create evolve task step.
- Use DBMS_SPM.REPORT_EVOLVE_TASK to report how the evolve task turned out.
- If you agree with the report findings and wish to accept the plan, use DBMS_SPM.ACCEPT_SQL_PLAN_BASELINE to accept it manually.
Furthermore, 12c has changed things such that not only does the SQL Plan Baseline contain the complete set of hints needed to reproduce a specific plan, but also the actual execution plan itself is recorded when the plan is evolved and added to the Baseline. For any plans that had Baselines present from 11g, the actual execution plan is captured and added to the baseline the first time it is executed in 12c. Keep in mind that having the actual execution plan and being able to review it isn’t the same as being able to reproduce the plan, the hints are still needed for that, but it does provide a guide so that if the plan can’t be reproduced, you can see what it “should” be.
So Baselines are obviously the wave of the future, but they still lack some of the flexibility of SQL Profiles when it comes to applying custom controls to statements. They can attempt to lock in plans from the cursor cache or from AWR history, but they cannot import arbitrary hints or apply to many statements at a time like SQL Profiles can via the FORCE_MATCHING attribute. However, they can collect alternate plans for later evaluation and they are designed to store a large set of plans in an attempt to keep any plan from changing without warning.
One final option you have to help exert a bit of influence over SQL execution plans is to use a SQL Patch. Using a SQL Patch, you can inject a hint (or set of hints) into a SQL statement that you otherwise cannot touch. This could be in the case of vendor-supplied code or even for your own SQL when it is embedded in an application that can’t be modified as quickly as you may need. The documented use of a SQL Patch is that it is a SQL manageability object intended to be generated by the SQL Repair Advisor to work around a plan that causes a failure. Generally speaking, the SQL Patch tells the optimizer to adjust the plan in some way or to avoid a particular optimization so that the failure no longer occurs.
But, even though the documentation doesn’t mention it, you can create SQL Patches yourself. Any SQL Patch you create can provide a set of hints that you want the optimizer to use during parse time for a particular query. The end result is that you can inject hints into code that you otherwise couldn’t touch. To create a SQL Patch manually, use DBMS_SQLDIAG_INTERNAL.I_CREATE_PATCH. Using the function, specify the query text you wish to patch as well as a set of hints to apply to that query. Listing 17-25 shows a very simple example of using a SQL Patch to add a GATHER_PLAN_STATISTICS hint to a SQL statement that is performing poorly. We want to inject the hint to be able to get more detailed row-source execution statistics when it executes to help our problem diagnosis efforts.
Listing 17-25. Using a SQL Patch to Add a GATHER_PLAN_STATISTICS Hint
SQL>-- The setting of the statistics_level = TYPICAL.
SQL>-- No rowsource execution statistics will be captured.
SQL>
SQL>show parameter statistics_level
NAME_COL_PLUS_SHOW_PARAM TYPE VALUE_COL_PLUS_SHOW_PARAM
------------------------------ ----------- ------------------------------
statistics_level string TYPICAL
SQL>
SQL>select count(*) from class_sales ;
COUNT(*)
----------
90000000
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID 6fpdvs2gy4vu3, child number 0
-----------------------------------------------------------
select count(*) from class_sales
Plan hash value: 3145879882
-----------------------------------------------------------
| Id | Operation | Name | E-Rows |
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
| 2 | TABLE ACCESS STORAGE FULL| CLASS_SALES | 90M|
-----------------------------------------------------------
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
SQL> -- Create a SQL Patch to inject the hint
SQL> begin
2 dbms_sqldiag_internal.i_create_patch(
3 sql_text => 'select count(*) from class_sales',
4 hint_text => 'gather_plan_statistics',
5 name => 'PATCH_KMTEST1',
6 category => 'DEFAULT'),
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> -- Run the SQL again and try to display the plan statistics.
SQL>select count(*) from class_sales ;
COUNT(*)
----------
90000000
Elapsed: 00:00:04.46
SQL>select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST')) ;
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
SQL_ID 6fpdvs2gy4vu3, child number 1
-----------------------------------------------------------
select count(*) from class_sales
Plan hash value: 3145879882
-----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | A-Rows | Buffers|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 1 | 18K|
| 1 | SORT AGGREGATE | | 1 | 1 | 18K|
| 2 | TABLE ACCESS STORAGE FULL| CLASS_SALES | 90M| 90M| 18K|
-----------------------------------------------------------------------------
Note
-----
- SQL patch "PATCH_KMTEST1" used for this statement
As you can see by the Note and by the fact that the ALLSTATS LAST format parameter didn’t generate an error, the Patch we applied to add the GATHER_PLAN_STATISTICS hint was used. After we get the information we want, we can drop the Patch (using DBMS_SQLDIAG.DROP_SQL_PATCH) or we can disable it (DBMS_SQLDIAG.ALTER_SQL_PATCH).
SQL Patches give you the ability to inject a hint, or set of hints, quickly into a SQL statement with relative ease. They are tied to a specific SQL text, so they are like Baselines in that aspect and not as flexible as SQL Profiles. However, if you need a quick way to hint a SQL statement you can’t otherwise touch, they can be a great option.
Hint-Based Plan Control Mechanisms Wrap-up
Of the options available, I believe that SQL Profiles are the most straightforward and functional. They have the advantage of force matching, which allows a single SQL Profile to apply to multiple SQL statements by ignoring literals (much like cursor_sharing=force; in other words, the “force” matching). They also have a built-in procedure (DBMS_SQLTUNE.IMPORT_SQL_PROFILE) that allows any set of hints to be attached to any SQL statement. You can similarly attach a set of hints using SQL Patches, but because they must be attached to a specific SQL text, they are not as flexible as Profiles. Both Profiles and Patches are extremely powerful tools that basically allow you to apply any hint to any statement, even if you don’t have access to the code. Baselines don’t allow you to apply hints in the same manner and are thus a disadvantage in that way over Profiles or Patches. Baselines do store the original plan_hash_value, which means they can determine whether the hints are still generating the original plan. But, until 12c, they have no way of getting back to the original plan in cases when the hints fail to do their job. Their only option at that point is to throw away the hints all together and try again. In 12c, the actual execution plan is stored in the Baseline, too, so instead of just having a set of hints that “should” get you back to the same plan, you have the actual plan itself to refer to.
Summary
There are several things that contribute to plan instability and several techniques that can be applied to correct and stabilize plan performance. SQL Profiles and SQL Patches provide an extremely valuable tool in situations when the need is urgent and the ability to change the code is nonexistent. They also have the advantage of being very specific in their scope (they can be targeted at a single statement without the possibility of having negative effects on other statements). Baselines can also be very useful if you are using 11g and above. Although they are not as flexible as Profiles or Patches, they do have the advantage of knowing what plan they are trying to recreate. They also have the capability of keeping a list of alternate plans that can be evaluated later. STA Profiles can be useful for identifying better plans and pointing out problem areas, but I am not a fan of implementing them in most cases. In general, I would rather have a mechanism that applies directive hints that lists specific objects and join methods rather than fudge factors. All these types of hint-based control mechanisms, though, should be considered temporary fixes. Although they may work well for an extended period of time while a more permenant solution is contemplated, they really should be considered a temporary fix while appropriate statistics-gathering methodology is implemented or code is changed to make appropriate use of literals or while any other long-term solution is put in place.
From a philosophical standpoint, I strongly believe that consistency is more important than absolute speed. So when a choice must be made, I always favor slightly reduced but consistent performance over anything that doesn’t provide that consistency.