1. Stale optimizer statistics
SQL execution plans are generated by the cost-based optimizer (CBO). For CBO to effectively choose the most efficient plan, it need accurate information on the data volume and distribution of tables and indexes referenced in the queries. Without accurate optimizer statistics, the CBO can be easily mislead and generate suboptimal execution plans.
2. Missing access structure
Absence of access structures, such as indexes, materialized views, and partitions, is a common reason for poor SQL performance. The right set of access structures can improve SQL.
3. Suboptimal Execution Plan selection, and
The CBO can sometimes select a suboptimal execution plan for a SQL statement. This happen for the most part because of incorrect estimates of some attributes of the SQL statement, such as its cost, carnality, or predicate selectivity.
4. Poorly constructed SQL
If the SQL is designed poorly, there is not much that the optimizer can do to improve its performance. A missing join condition leading to a Cartesian product, or the use of more expensive SQL constructs like UNION in place of UNION ALL, are just a couple of examples of inefficient SQL design.
Above are the four main causes of poor SQL optimization can have a drastic impact on performance.
Additional reasons for poor performance might be connected with hardware-related issues, such as memory, I/O, CPUs, and so on.
Example of poorly constructs SQL query:
Query common business question type. Query is to determines how many products have list prices less than 15% above the average cost of the product. This statement has a correlated subquery, which means that the subquery is run for every row found in the outer query.
SELECT COUNT(*) FROM product p
WHERE prod_list_price < 1.15 * (SELECT AVG(unit_cost) FROM cost c
WHERE c.prod_id = p.prod_id);
Following is better written.
SELECT COUNT(*)
FROM product p,
(SELECT prod_id, AVG(unit_cost) auc FROM costs
GROUP BY prod_id) c
WHERE p.prod_id = c.prod_id
AND p.prod_list_price < 1.15 * c.auc;
Use simple equality of having function based indexes
The query, in this example, applies functions to the join columns, restricting the condition where indexes can be used. Use a simple equality, if you can. Otherwise, a function-based index may be necessary.
SELECT * FROM job_history jh, employees e
WHERE SUBSTR(TO_CHAR(e.employee_id),2 = SUBSTR(TO_CHAR(jh.employee_id),2);
In this example, the query ha a condition that forces implicit data type conversion; the ORDER_ID_CHAR column is a character type and the constant is a numeric type. You should make the literal match the column type.
SELECT * FROM orders WHERE order_id_char = 1000;
Example of query uses a data type conversions function in it to make the data types match in the comparison.
The problem here is that the TO_CHAR function is applied to the column values, rather than to the constant. This means that the function is called for ecery row in the table. It would be better to convert the literal once, an not convert the column.
SELECT * FROM employees WHERE TO_CHAR(salary) = :sal
The query is better written using this SQL statement
SELECT * FROM employees WHERE TO_CHARsalary = TO_NUMBER(:sal)
Unnecessary unique sort
In this query, the UNION operator, as opposed to the UNION ALL operator, ensures that there are no duplicate rows in the result set. However, this required an extra step, a unique sort, to eliminate any duplicates. If you know there a no rows in common between the two UNIONed queries, use UNION ALL instead of UNION. This eliminates the unnecessary sort.
SELECT * FROM parts_old
UNION
SELECT * FROM parts_new
The query is better writte using this SQL statement
SELECT * FROM parts_old
UNION ALL
SELECT * FROM parts_new
