Mar 12, 2013 3:05:00 PM by Quest Software
|Isolating and Tuning Problem SQL|
Spotlight on Oracle's Top SQL drilldown allows you to identify the SQL that is consuming the most resources on your system. Tuning this SQL can be one of the most effective ways of tuning your database. This section contains guidelines on SQL tuning.
Indexes exist primarily to improve the performance of SQL statements. In many cases, establishing good indexes is the best path to optimal performance.
|Use concatenated indexes|
Try not to use two indexes when one would do. If searching for SURNAME and FIRSTNAME, do not unnecessarily create separate indexes for each column. Instead, create a concatenated index on both SURNAME and FIRSTNAME. You can use the leading portion of a concatenated index on its own. If you sometimes query on the SURNAME column without supplying the FIRSTNAME, then SURNAME should come first in the index.
Spotlight on Oracle - Cost-based Optimizer (SOO26)
|Taking advantage of the cost-based optimizer|
The component of the Oracle software that determines the execution plan for a SQL statement is known as the optimizer. Oracle supports two approaches to query optimization. They are:
Early experiences with the cost-based optimizer in Oracle 7.0 and 7.1 were often disappointing and gave the cost-based optimizer a bad reputation. However, the cost-based optimizer has been improving in each release. The rule-based optimizer is virtually unchanged since Oracle 7.0. Advanced SQL access methods (such as star and hash joins) are only available when you use the cost-based optimizer.
The cost-based optimizer is the best choice for almost all new projects. Converting from rule to cost-based optimization is worthwhile for many existing projects.
Consider the following guidelines for getting the most from the cost-based optimizer:
SELECT /*+ INDEX(CUSTOMERS CUST_I2) */ * FROM CUSTOMERS WHERE NAME=:CUST_NAME
|Avoid accidental table scans|
One of the most fundamental SQL tuning problems is the accidental table scan. Accidental table scans usually occur when the SQL programmer tries to perform a search on an indexed column that can’t be supported by an index. This can occur when:
|Optimize necessary table scans|
In many cases, avoiding a full table scan by using the best of all possible indexes is your aim. Often though, a full table scan cannot be avoided. In these situations, consider some of the following techniques to improve table scan performance:
If the number of rows you want to retrieve from a table is greater than an index lookup could effectively retrieve, but still only a fraction of the table itself (say between 10 and 40% of total), you could consider partitioning the table.
For instance, suppose that a SALES table contains all sales records for the past 4 years and you frequently need to scan all sales records for the current financial year in order to calculate year-to-date totals. The proportion or rows scanned is far greater than an index lookup would comfortably support, but is still only a fraction of the total table.
If you partition the table by financial year, you can restrict processing to only those records that match the appropriate financial year. This could potentially reduce scan time by 75% or more.
In Oracle 7.3, you can create separate tables for each financial year, and then create a partition view. A partition view is a UNION ALL view with CHECK constraints on each table. These enforce the partitioning. Scans on the view that specify a particular financial year clause only need to scan the appropriate table.
In Oracle 8, true partitioned tables can be created. In an Oracle 8 table, partitioned by financial year, rows for the appropriate financial year would be stored in distinct partitions, and the optimizer would restrict queries against a particular financial year to the appropriate partition.
|Use array processing|
Array processing refers to Oracle’s ability to insert or select more than one row in a single operation. For SQL, which deals with multiple rows of data, array processing usually results in reductions of 50% or more in execution time (more if you’re working across the network). In some application environments, array processing is implemented automatically and you won’t have to do anything to enable this feature. In other environments, array processing must be totally implemented by the programmer.
Many programmers implement huge arrays. This can be excessive and may even reduce performance by increasing memory requirements for the program. Most of the gains of array processing are gained by increasing the array size from 1 to about 20. Further increases result in diminishing gains. You do not normally see much improvement when increasing the array size over 100.
|Utilizing SQL tuning features|
Each release of Oracle introduces new and improved SQL performance features. In particular the cost-based optimizer contains improvements in each release—many of which are undocumented. Some of the Oracle features that can help your SQL performance are:
Optimizer hints appear as a comment following the first word of the SQL statement (for example, SELECT, INSERT, DELETE, or UPDATE). Hints are differentiated from other comments by the presence of the plus sign (+) following the opening comment delimiter (/*). For instance, the FULL hint in the following example tells the optimizer to perform a full table scan when resolving the query:
SELECT /*+ FULL(E) */ * FROM EMPLOYEE E WHERE SALARY > 1000000
The following list shows the hints that can be used:
|ALL_ROWS||Use the cost-based optimizer and optimize for the retrieval of all rows.|
|AND_EQUAL (table_name index_name index_name ....)||Retrieve rows from the specified table using each of the specified indexes and merge the results.|
|APPEND||Invokes a direct load insert. Only valid for insert … select from statements.|
|BITMAP (table_name index_name)||Retrieve rows from the specified table using the specified bitmap index.|
|CACHE (table_name)||Encourages rows retrieved by a full table scan to remain in the buffer cache of the SGA.|
|CHOOSE||If statistics have been collected for any table involved in the SQL statement, use cost-based or all-rows optimization, otherwise use rule-based optimization.|
|CLUSTER (table_name)||Uses a cluster scan to retrieve table rows.|
|DRIVING_SITE (table_name)||For a distributed SQL statement, this causes the site at which the specified table resides to be the driving site.|
|FIRST_ROWS||Specifies that the cost-based optimizer should optimize the statement to reduce the cost of retrieving the first row only.|
|FULL (table_name)||Use a full table scan to retrieve rows from the specified table.|
|HASH (table_name)||Use a hash scan to retrieve rows from the specified table. The table must be stored in a hash cluster.|
|HASH_AJ||Perform an anti-join using hash join methodology. This hint must appear after the select statement, not in sub-query.|
|HASH_SJ||Appears within an EXISTS sub-query. Invokes a hash semi-join.|
|INDEX (table_name [index_name])||Uses the specified index to retrieve rows from the table or, if no index is specified, uses any index.|
|INDEX_ASC (table_name [index_name])||Specifies an ascending index range scan using the specified index or, if no index is specified, any suitable index.|
|INDEX_COMBINE (table_name [index_name…])||Instructs the optimizer to combine the specified bitmap indexes. If no bitmap indexes are specified, the optimizer chooses suitable bitmap indexes.|
|INDEX_DESC (table_name [index_name])||Specifies a descending index range scan using the specified index or, if no index is specified, any suitable index.|
|INDEX_FFS (table_name [index_name])||Invokes a fast full index scan using the specified index or, if no index is specified, any suitable index. A fast full scan reads the entire index in block order, using multi-block reads and possibly parallel query.|
|MERGE||Instructs the optimizer to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause.|
|NO_MERGE||Instructs the optimizer not to perform complex view merging when resolving a query based on a view, or one that includes a sub-query in the WHERE clause.|
|MERGE_AJ||Performs an anti-join using sort-merge join method. This hint must appear after the SELECT statement, not in a sub-query.|
|MERGE_SJ||Appears within an EXISTS sub-query. Invokes a sort-merge semi-join.|
|NO_EXPAND (table_name)||Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by a union operation. This hint instructs the optimizer not to do this, even if it calculates that such a transformation would be beneficial.|
|NO_INDEX (table_name [index_name] )||No index suppresses the use of the named indexes or, if no indexes are specified, all indexes on the named table.|
|NO_PUSH_PRED||Instructs not to push join conditions from the WHERE clause into a view or sub-query.|
|NOAPPEND||Suppresses direct load insert in an INSERT… SELECT FROM... statement.|
|NOCACHE (table_name)||Discourages Oracle from keeping rows retrieved by a full table scan in the buffer cache of the SGA. Overrides the cache setting on the CREATE or ALTER TABLE statement.|
|NOPARALLEL (table_name)||Do not use parallel processing for the SQL statement. Overrides the parallel setting on the CREATE or ALTER TABLE statement.|
|NOPARALLEL_INDEX (table_name index_name)||Suppresses parallelism in fast full index scans or in partitioned index access.|
|NOREWRITE (Oracle 8i)||Prevents the SQL statement from being rewritten to take advantage of materialized views. It overrides the server parameter query_rewrite_enabled.|
|ORDERED||Instructs the optimizer to join the tables in exactly the left to right order specified in the FROM clause.|
|ORDERED_PREDICATES (Oracle 8i)||Causes predicates in the WHERE clause to be evaluated in the order in which they appear in the WHERE clause.|
|PARALLEL (table_name , degree_of_parallelism)||Instructs the optimizer to perform parallel scans on the named table. If no degree of parallelism is specified, the default is used.|
|PARALLEL_INDEX (table_name [index_name])||Parallelizes a fast full index scan, or an index scan against a partitioned index.|
|PQ_DISTRIBUTE (table_name outer_distribution inner_distribution)||This query determines how a parallel join using table_name is executed. Valid options for outer_distribution and inner_distribution are (not all combinations are valid) hash, broadcast, none, partition.|
|PUSH_JOIN_PRED/PUSH_PRED||Push join conditions from the WHERE clause into a view or sub-query.|
|PUSH_SUBQ||Causes sub-queries to be processed earlier in the execution plan. Normally, sub-queries are processed last, unless the SQL statement is transformed into join.|
|REWRITE (view_name [view_name…]) (Oracle 8i)||Restricts query rewrite to only those materialized views specified in the hint.|
|ROWID (table_name)||Performs a ROWID access.|
|RULE||Uses rule-based optimization.|
|STAR||Considers the STAR join methodology in preference to other methods.|
|STAR_TRANSFORMATION (Oracle 8.0+)||Requests that the star transformation optimization be performed. This transforms a star query into an alternate form that can take advantage of bitmap indexes.|
|USE_CONCAT||Oracle sometimes expands statements with OR conditions into multiple SQL statements combined by union all. This hint instructs the optimizer to do this, even if it calculates that such a transformation would not be beneficial.|
|USE_HASH (table_name)||When joining to this table, use the hash join method.|
|USE_MERGE (table_name)||When joining to this table, use the sort-merge join method.|
|USE_NL (table_name)||When joining to this table, use the nested-loops join method.|
Written by Quest Software