Free Oracle Tips   HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

Determining the Default optimizer_mode Oracle Tips by Burleson

As we have noted, there are some shortcomings in the cost-based and rule-based optimization, and the DBA must make a decision about the appropriate optimizer_mode default for the database. Every database is different, both in the types of queries and the structures of the data. However, there are some general rules that can be applied:

• Pre-Oracle8i queries that join three or more large tables will generally benefit from the rule-based optimizer or the first_rows hint.

• Queries that access bitmapped or function-based indexes will benefit from the cost-based optimizer.

• Queries that use star query hints need the cost-based optimizer.

• Databases at Oracle8i and beyond will benefit from the cost-based optimizer.

The choice also depends on the version of Oracle. Oracle recommends that all Oracle7 databases use rule-based optimization, and by Oracle8i, the cost-based optimizer has improved to the point where it can be considered for a default.

Given that any Oracle environment would benefit from both optimizers, there are several choices. The DBA could make the cost-based optimizer the default and use rule hints when required, or they could make rule-based the default and use cost hints and statistics when desired.

Many DBAs conduct a study where they bounce the init.ora optimizer_mode and then run the application for a day in each mode and collect statistics. From these overall comparisons, shown in Figure 11-3, the proper default optimizer_mode becomes readily apparent.

Figure 11-106: A comparison of response times for cost-based and rule-based optimization in Oracle 8.0.5

In the preceding example, the overall database performance was faster using first_rows, and that was set as the default. After setting the default, individual SQL statements were tuned using the rule hint.

Now let's review some miscellaneous SQL tuning techniques.

Miscellaneous Tuning Techniques

Before we go into detail on the process of tuning, let's look at several important ways to tune individual SQL statements. These topics include:

• Tuning with hints

• Tuning subqueries

• The problem of literal SQL statements

• Tuning with temporary tables

• General rules for writing efficient SQL

Tuning with CBO SQL Hints

There are several hints that can be directly embedded into Oracle SQL. These hints serve the purpose of changing the optimizer path to the data. Remember, hints override all settings for optimizer_mode and optimizer_goal. While there is not room in this book to go into all of the hints, here are the ones most commonly used when tuning SQL:

•  /*+ rule */ This hint is normally used when a table experiences a full table scan, even though an index appears on the table. Often, the RBO will use indexes that the CBO will ignore.

• /*+ full, table=xxx parallel=nn */ This hint forces a full table scan with Parallel Query. This is commonly used when the query is performing a large aggregation such as SUM or AVG across the whole table.

• /*+ table=xxx index=xxx */ This hint forces the use of a specific index. This is commonly used when the RBO chooses a nonselective index to service a query.

• /*+ first_rows */ This hint is commonly used with a CBO default to see if the execution plan will improve with cost-based optimization.

•  /*+ use_hash */ This hint is commonly used with a CBO default to see if the execution plan will improve with a hash join technique.

CAUTION: The cost-based optimizer will not alert you if you have a syntax error in your hint. This is because hints are coded inside comments and comments will be ignored if they contain invalid hint syntax. It is not uncommon to see a DBA add a hint and then not understand why the execution plan has not changed. Hence, be very careful to ensure that you have specified the hint correctly.

Tuning SQL Subqueries

Whenever possible, the use of a subquery within Oracle should be avoided. In most cases, the subquery can be replaced with a standard JOIN operation and thereby avoid the overhead that is associated with subqueries. However, there are circumstances where the use of an Oracle subquery is unavoidable, and this section describes the rules for determining the best way to specify a subquery.

As you may know, Oracle SQL only allows one table to be specified in the FROM clause of a SQL update or delete statement. As a consequence, the only way to specify values from another table is to place the reference to the other table into a subquery. There is a question about the most efficient way to specify the subquery to update or delete from a table when the operation depends on the values of rows inside other tables. For example, the only way to update table1 based upon  corresponding rows in table2 would be to write a subquery to specify the
table2 condition.

L 11-11

One of the shortcomings of the SQL language is that there are numerous ways to write most SQL statements, and each will return identical results but may have radically different access paths and execution times. Also, while the Oracle SQL optimizer will often detect complex queries and decompose them into equivalent JOIN operations, (taking the subquery and converting it into a nested loop JOIN) we cannot always count on the optimal access path to service the query.

In cases where we must use subqueries, there are several options that we need to consider. We have the choice of using a correlated or a noncorrelated subquery, and we also have the choice of using either the IN clause or the EXISTS clause as the comparison condition for the subquery. Hence, there are four possible forms for the subquery:

L 11-12

The Problem of Literal SQL Statements

Many applications are written such that they send SQL statements into the library cache using literal values in the SQL. This is a huge performance issue for Oracle. As you remember from Chapter 9, the purpose of the library cache is to store SQL commands so that they can be reused without reparsing and redeveloping an execution plan every time they are requested.

Next is an actual example from a third-party application that uses Oracle for the database. As you can see, the SQL is identical except for the literal values that exist for page_unique_id. This is an excellent example of the nonreusable SQL problem.

L 11-13

As you can see, each of these statements is identical except for the literal values for page_unique_id. When these statements flood the library cache, they consume unnecessary space because they can never be reused. For some systems, the burden on the library cache greatly degrades the performance of the entire system and the DBA must issue periodic alter system flush shared pool commands to clean out the non- reusable SQL statements. If we could parameterize this SQL, we would only need a single SQL statement in the library cache:

L 11-14

This would greatly improve the efficiency within the library cache and ensure that all SQL can be reused by subsequent queries. In Oracle8i and beyond, you can use the cursor_sharing option to force literal SQL to be reused, and we will cover this later in this chapter.

Now let's look at a query we can use to find these offensive SQL statements.

Whenever possible, it is a good idea to keep the entire database SQL inside stored procedures with variables, and place these stored procedures into packages for pinning in the shared pool. By keeping all of the SQL in packages, the DBA will always be able to locate the SQL for tuning purposes. This approach also has the side benefit of ensuring that all SQL statements are identical.

Identifying Nonreusable SQL

We can attempt to locate literal SQL in the v$sql view by grouping and counting SQL statements that are identical up to a certain point based on the observation that most literal SQL becomes textually distinct toward the end of the statement (e.g., in the WHERE clause).

The following query displays the percentage of SQL executed that did not incur an expensive hard parse. Literal SQL will always be fully parsed, so a low percentage may indicate a literal SQL or other SQL sharing problem.

noparse_ratio.sql

L 11-15

Here is the output from this script. Next we see that the SQL parse-to-execute ratio is well over 90 percent, indicating that the majority of the SQL in the library cache can be reused.

L 11-16

The following query returns SQL statements having more than ten statements that textually match on the leading substring. This script is a great way to locate nonreusable SQL statements in your database.

find_literal_sql.sql

L 11-17

Here is an actual excerpt from this script. Note that the output clearly shows the nonreusable SQL statements.

L 11-18

So, now that we have located literal SQL statements, what can we do? If we are using vendor-supplied code, it is often impossible to get the vendor to make the SQL queries reusable. Oracle has addressed this problem with the cursor_sharing init.ora parameter in Oracle8i.

Using cursor_sharing in Oracle8i

cursor_sharing is a new initialization parameter in Oracle8i (8.1.6) that is designed to help manage the clutter problems with nonsharable SQL. cursor_sharing can take the following values:

•  FORCE The FORCE option forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement. This is achieved by replacing literals with system-generated bind variables and causes increased sharing of literal SQL.

• EXACT (the default value) This causes only identical SQL statements to share a cursor. This is the standard pre-Oracle8i method.

• SIMILAR The SIMILAR values causes Oracle to match SQL whenever the query is identical except for the value of Boolean values in the WHERE clause of the query.

When cursor_sharing is set to FORCE, Oracle adds an extra layer of parsing that identifies statements as equivalent if they differ only in the values of literals, hashing them to identical library cache objects. We will see that under the right circumstances this setting can help solve the performance problems of literal SQL.

CAUTION: Oracle technical support states that cursor_sharing should be set to FORCE only when the risk of suboptimal plans is outweighed by the improvements in cursor sharing. Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications
and in applications using stored outlines.

Setting cursor_sharing=FORCE may be worth considering if your system has these characteristics:

• Are there a large number of statements in the shared pool that differ only in the values of their literals?

• Is the response time low due to a very high number of library cache misses (i.e., hard parses and library cache latch contention)?

In cases where the DBA has added cursor_sharing=FORCE, this directive has indeed made the SQL reusable, dramatically reducing the strain on the shared pool. The downside is Oracle's warnings that some SQL can get suboptimal execution plans.

Tuning SQL with Temporary Tables

The prudent use of temporary tables can dramatically improve Oracle performance. Consider the following example: We want to identify all users who exist within Oracle but have not been granted a role. We could formulate the following query:

L 11-19

This query runs in 18 seconds. Now, we rewrite the same query to utilize temporary tables:

L 11-20

This query runs in less than three seconds.

Tuning SQL by Adding Indexes

One of the most common techniques for removing an unwanted full table scan is to add a new index to a table. This can often remove an unwanted full table scan or force the query to use a more selective index to service the query. Of course, we must be cautious when adding indexes because a new index may change the execution plan for other SQL statements. It is always a good idea to make all SQL tuning changes by using optimizer plan stability or by adding hints to the SQL.

When tuning by adding indexes, there are two special cases of indexes that are especially useful:

• Function-based indexes Whenever a SQL query must use a function (i.e., WHERE upper(last_name)), a function-based index can remove a full table scan.

• Bitmapped indexes It was a common misconception that bitmapped indexes were only appropriate for columns with a very small number of distinct values—say, less than 50. Current research in Oracle8i has shown that bitmapped indexes can substantially improve the speed of queries using columns with up to 1,000 distinct values, because retrieval from a bitmapped index is done in RAM and is almost always faster than using a traditional B-tree index. Most experienced DBAs will look for columns with less than 1,000 distinct values, build a bitmapped index on this column, and then see if the query is faster.

Next let's look at general guidelines for writing efficient SQL.

General Rules for Writing Efficient SQL

There are some general rules available for writing efficient SQL in Oracle regardless of the optimizer that is chosen. These rules may seem simplistic, but they can greatly improve the performance of SQL:

·        Never do a calculation or add a built-in function (BIF) to an indexed column. This causes an unnecessary full table scan. In cases where you must use a BIF, the use of function-based indexes can remove the full table scan.

·        Avoid the use of NOT IN or HAVING in the WHERE clause. Instead, use the NOT EXISTS clause.

·        Never specify numeric values in character form, and character values in numeric form. This invalidates the index and causes full table scans.

·        Avoid specifying NULL in an indexed column.

·        Avoid using the LIKE parameter if = will suffice. Using any Oracle function will invalidate the index, causing a full table scan. In the following example, an index could be created on substr(license_plate,1,3) and an = could be used.

·        Avoid leading wildcard with the LIKE parameter.  For example, a query LIKE '4YE%' will use an index, while a trailing wildcard such as LIKE  '%4YE' will not be able to use an index.

·        Avoid using subqueries when a JOIN will do the job.

·        Always determine the number of row accesses required to service your query. Consider whether the table rows are ordered or unordered, the setting for db_file_multiblock_read_count, and the degree of parallelism on the table. In general, if your query will return more than 40 percent of the rows in a row-ordered table, use a full table scan rather than an index scan. If a query returns more than 7 percent of the rows in an unordered table, a full table scan may be faster.

Next, let's move on and look at the overall process of tuning SQL statements.

This is an excerpt from "Oracle High Performance tuning with STATSPACK" by Oracle Press.

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.