Oracle Performance Tuning:

  1. Use a WHERE clause whenever possible.
  2. Use Joins rather than multiple table queries.
  3. Fully qualify a column reference with its table name so that the optimizer doesn't have to do that.
  4. Use CASE statements rather than multiple queries. 
  5. Create Indexes.
  6. Use a WHERE clause rather than a HAVING clause.
  7. Use UNION ALL instead of UNION when  you don't need to filter duplicates.
  8. Use EXIST rather than IN.  EXIST will stop after the first match, IN will continue until all values have been checked.
  9. Use EXIST rather than DISTINCT: 
  10. Use Bind variables:
  11. Review optimizer plans:

3.  Fully Qualify Column Reference:

select lname, order_date
from customer c join orders o on c.customer_number = o.customer_number

 select c.lname, o.order_date
from customer c join orders o on c.customer_number = o.customer_number

Use CASE Expressions Rather than Multiple Queries

Use CASE expressions rather than multiple queries when you need to perform many calculations on the same rows in a table. The following example uses multiple queries to count the number of products within various price ranges (bad):

-- BAD (three separate queries when one CASE statement would work)
SELECT COUNT(*)
FROM products
WHERE price < 13;

  COUNT(*)
----------
         2

SELECT COUNT(*)
FROM products
WHERE price BETWEEN 13 AND 15;

  COUNT(*)
----------
         5

SELECT COUNT(*)
FROM products
WHERE price > 15;

  COUNT(*)
----------
         5

Rather than using the three queries just shown, you should write one query that uses CASE expressions. For example:

-- GOOD (one query with a CASE expression rather than three queries)

SELECT
 COUNT(CASE WHEN unit_price < 13 THEN 1 ELSE null END) low,
 COUNT(CASE WHEN unit_price BETWEEN 13 AND 15 THEN 1 ELSE null END) med,
 COUNT(CASE WHEN unit_price > 15 THEN 1 ELSE null END) high
FROM part;



       LOW        MED       HIGH
---------- ---------- ----------
         2          5          5

Notice the counts of the products with prices below $13 are labeled as low, products between $13 and $15 are labeled med, and products greater than $15 are labeled high.

  Note 

You can, of course, use overlapping ranges and different functions in your CASE expressions.

Use EXISTS rather than IN:

SELECT customer.lname FROM customer
WHERE customer_number IN (SELECT customer_number FROM orders);

versus:

SELECT customer.lname FROM customer
WHERE EXISTS (SELECT 1 FROM orders WHERE orders.customer_number = customer.customer_number);

 

Use EXISTS Rather than DISTINCT

You can suppress the display of duplicate rows using DISTINCT; you use EXISTS to check for the existence of rows returned by a sub query. Whenever possible, you should use EXISTS rather than DISTINCT because DISTINCT sorts the retrieved rows before suppressing the duplicate rows.

The following query uses DISTINCT (bad since EXISTS would work) to retrieve products that have been purchased:

-- BAD (uses DISTINCT when EXISTS would work)
SELECT DISTINCT pr.product_id, pr.name
FROM products pr, purchases pu
WHERE pr.product_id = pu.product_id;

PRODUCT_ID NAME
---------- -----------------------------
         1 Modern Science
         2 Chemistry
         3 Supernova

The next query rewrites the previous example to use EXISTS rather than DISTINCT:

-- GOOD (uses EXISTS rather than DISTINCT)
SELECT product_id, name
FROM products outer
WHERE EXISTS
  (SELECT 1
   FROM purchases inner
   WHERE inner.product_id = outer.product_id);

PRODUCT_ID NAME
---------- -----------------------------
         1 Modern Science
         2 Chemistry
         3 Supernova

 

Fixing the SQL:

First make it readable -- prettying the sql.

You can't fix what you can't read.  One of the problems with code in general, not just SQL, is that lazy or inexperienced programmers don't bother to make the stuff readable, many programmers have the attitude that if I can squeeze it all onto one or two lines that is more efficient.  Part of this attitude comes from the old-days when disk space was expensive.  The few lines used less disk space.  Today this is not an issue and the number of lines used has no effect on the compilers ability to efficiently execute the code.  Making code readable does help programmers to debug the problem, especially if the code was written by someone else. 

"Riding the unique indexes"  You should join your tables from child to parent NOT from parent to child.

 

Now try a make the following statement readable.

select student.lname, student.fname, student.student_id, classes.name, sessions.room, sessions.time, sessions.days, sessions.length, teacher.lname, teacher.fname from student, enrollment, classes, sessions, teachers where session_id = 1234 and student.student_id = enrollment.student_id and enrollment.session_id = sessions.session_id and sessions.class_id = classes.class_id and sessions.teacher_id = teacher.teacher_id  order by student.lname, student.fname

SELECT 	student.lname, 
	student.fname, 
	student.student_id, 
	classes.name, 
	sessions.room, 
	sessions.time, 
	sessions.days, 
	sessions.length, 
	teacher.lname, 
	teacher.fname 
FROM 	enrollment, student, classes, sessions, teachers 
WHERE 	session_id = 1234 
AND 	enrollment.student_id 	= student.student_id 
AND	enrollment.session_id 	= sessions.session_id 
AND	sessions.class_id 	= classes.class_id 
AND	sessions.teacher_id 	= teacher.teacher_id  
ORDER BY student.lname, student.fname

Bind Variables: The technique of using place holders in the text of SQL statement and then substituting that place holder with a value stored in a variable is strongly encouraged for batch programs, forms, reports, and analysis tools.  In Java this is done using Prepared Statements instead of Dynamic SQL.

// Dynamic Example:

String sql = "SELECT * FROM customer WHERE customer_id = 1";

// Prepared (Bind) Example:

String sql = "SELECT * FROM customer WHERE customer_id = ?";

 

Resolving Excessive Resource Consumption "On MySQL systems, excessive resource consumption is nearly always due to inefficient SQL statements.  Fixing an inefficient SQL statement could be as simple as adding an appropriate index, or it could be as complex as brain surgery."

Remember the 80/20 rule:  80% of the problem can usually be fixed in 20% of the time.  If the performance is now "Good Enough"  don't waste time with the remaining 20%.  Know when to quit and to recognize a "Point of diminishing returns".