1)Verify Optimizer Statistics:
--------------------------------------------
The query optimizer uses statistics gathered on tables and indexes when determining the optimal execution plan. If these statistics have not been gathered, or if the statistics are no longer representative of the data stored within the database, then the optimizer does not have sufficient information to generate the best plan.
So, gather statistics of all tables that are involved in SQL statements. You can check whether your statistics is up to date or not by querying
SELECT COUNT(*) FROM table_name;
and,
select NUM_ROWS from dba_tables where table_name='TABLE_NAME';
If they are almost same then you have correct optimizer statistics. If they don't match then gather new statistics.
2)Review the Execution Plan:
---------------------------------------
When tuning (or writing) a SQL statement, the goal is to drive from the table that has the most selective filter. This means that there are fewer rows passed to the next step. If the next step is a join, then this means that fewer rows are joined. Check to see whether the access paths are optimal.
We can check it by examine the optimizer execution plan following,
•The plan is such that the driving table has the best filter.
•The join order in each step means that the fewest number of rows are being returned to the next step (that is, the join order should reflect, where possible, going to the best not-yet-used filters).
•The join method is appropriate for the number of rows being returned. For example, nested loop joins through indexes may not be optimal when many rows are being returned.
•Views are used efficiently. Look at the SELECT list to see whether access to the view is necessary.
•There are any unintentional Cartesian products (even with small tables).
•Each table is being accessed efficiently:
-Consider the predicates in the SQL statement and the number of rows in the table. Look for suspicious activity, such as a full table scans on tables with large number of rows, which have predicates in the where clause. Determine why an index is not used for such a selective predicate.
-A full table scan does not mean inefficiency. It might be more efficient to perform a full table scan on a small table, or to perform a full table scan to leverage a better join method (for example, hash_join) for the number of rows returned.
If any of these conditions are not optimal, then consider restructuring the SQL statement or the indexes available on the tables.
3)Restructuring the SQL Statements
----------------------------------------------
Often, rewriting an inefficient SQL statement is easier than modifying it. If you understand the purpose of a given statement, then you might be able to quickly and easily write a new statement that meets the requirement.
While restructuring the SQL statements keep in mind of the following issues.
•Use equijoins whenever possible.
That is compose predicate using AND and =.
•Avoid Transformed Columns in the WHERE Clause.
That is use
a=b instead of to_number(a)=to_number(b)
When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement; if you have index on varcol
TO_CHAR(numcol) = varcol
rather than
varcol = TO_CHAR(numcol)
•Write Separate SQL Statements for Specific Tasks.
SQL is not a procedural language. Using one piece of SQL to do many different things usually results in a less-than-optimal result for each task. If you want SQL to accomplish different things, then write various statements, rather than writing one statement to do different things depending on the parameters you give it.
It is always better to write separate SQL statements for different tasks, but if you must use one SQL statement, then you can make a very complex statement slightly less complex by using the UNION ALL operator.
•Use of EXISTS versus IN for Subqueries.
In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.
4)Controlling the Access Path and Join Order with Hints
--------------------------------------------------------------------------------------
You can use hints in SQL statements to instruct the optimizer about how the statement should be executed. Hints, such as /*+FULL */ control access paths.
Join order can have a significant effect on performance. The main objective of SQL tuning is to avoid performing unnecessary work to access rows that do not affect the result. This leads to three general rules:
•Avoid a full-table scan if it is more efficient to get the required rows through an index.
•Avoid using an index that fetches 10,000 rows from the driving table if you could instead use another index that fetches 100 rows.
•Choose the join order so as to join fewer rows to tables later in the join order.
•Be careful when joining views, when performing outer joins to views, and when reusing an existing view for a new purpose.
•Joins to complex views are not recommended, particularly joins from one complex view to another. Often this results in the entire view being instantiated, and then the query is run against the view data.
•Beware of writing a view for one purpose and then using it for other purposes to which it might be ill-suited. Querying from a view requires all tables from the view to be accessed for the data to be returned. Before reusing a view, determine whether all tables in the view need to be accessed to return the data. If not, then do not use the view. Instead, use the base table(s), or if necessary, define a new view. The goal is to refer to the minimum number of tables and views necessary to return the required data.
•An outer join within a view is problematic because the performance implications of the outer join are not visible.
•Consider using materialized views.
5)Restructuring the Indexes
-----------------------------------------------
Often, there is a beneficial impact on performance by restructuring indexes. This can involve the following:
Remove nonselective indexes to speed the DML.
Index performance-critical access paths.
Consider reordering columns in existing concatenated indexes.
Add columns to the index to improve selectivity.
6)Modifying or Disabling Triggers and Constraints
-----------------------------------------------------------
Using triggers consumes system resources. If you use too many triggers, then you can find that performance is adversely affected and you might need to modify or disable them.
7)Restructuring the Data
-----------------------------------------
After restructuring the indexes and the statement, you can consider restructuring the data.
Introduce derived values. Avoid GROUP BY in response-critical code.
Review your data design. Change the design of your system if it can improve performance.
Consider partitioning, if appropriate.
8)Combine Multiples Scans with CASE Statements
-----------------------------------------------------------
Often, it is necessary to calculate different aggregates on various sets of tables. Usually, this is done with multiple scans on the table, but it is easy to calculate all the aggregates with one single scan. Eliminating n-1 scans can greatly improve performance.
9)Maintaining Execution Plans Over Time
-------------------------------------------------------
You can maintain the existing execution plan of SQL statements over time either using stored statistics or stored SQL execution plans. Storing optimizer statistics for tables will apply to all SQL statements that refer to those tables. Storing an execution plan (that is, plan stability) maintains the plan for a single SQL statement. If both statistics and a stored plan are available for a SQL statement, then the optimizer uses the stored plan.
No comments:
Post a Comment