SQL Tuning - Reducing Database Accesses

SQL Tuning - Reducing Database Accesses -

Every time a SQL statement is executed, Oracle needs to perform many internal processing steps; the statement needs to be parsed, indexes evaluated, variables bound, and data blocks read. The Oracle database tries to reuse as much work as possible by caching information in the Shared Pool and accessing previously used blocks in the Buffer Cache. However, the more the number of database accesses are reduced, the more overheads can be saved. Reducing the physical number of trips to the database is particularly beneficial in client-server configurations in which the database may need to be accessed over a network.

The following examples show four distinct ways of retrieving data about employees who have employee numbers 0342 or 0291. Method 1 is the least efficient, method 2 is more efficient, method 3 is even more efficient, and method 4 is the most efficient of all.

Method 1 shows two separate database accesses:

SELECT emp_name, salary, grade
FROM    emp
WHERE  emp_no = 0342;
SELECT emp_name, salary, grade
FROM    emp
WHERE  emp_no = 0291;

Method 2 shows the use of one cursor and two fetches:

DECLARE
       CURSOR C1 (E_no  number)  IS
       SELECT emp_name, salary, grade
       FROM   emp
       WHERE  emp_no = E_no;
BEGIN
        OPEN   C1 (342);
        FETCH  C1 INTO ..., ..., ...;
          .
          .
          .
        OPEN   C1 (291);
      FETCH  C1 INTO ..., ..., ...;
        CLOSE  C1;
END;

Method 3 shows a SQL table join:

SELECT   A.emp_name, A.salary, A.grade,
                  B.emp_name, B,salary, B.grade
FROM     emp A,
                 emp B
WHERE    A.emp_no = 0342
AND      B.emp_no = 0291 ;

In this example, the same table is identified by two aliases, A and B, that are joined by a single statement. In this way, Oracle uses only one cursor and performs only one fetch.

Method 4 shows how one query can be used to return the two rows:

SELECT   emp_name, salary, grade,
FROM     emp A
WHERE    emp_no IN (0342,0291) ;

The lesson here is that using one SQL statement instead of multiple statements can significantly improve database performance. If possible, have the application retrieve all the information that it needs in one operation rather than making multiple trips to the database.

Note:  One simple way to increase the number of rows of data that can be fetched with one database access, and thus reduce the number of physical calls needed, is to reset the ARRAYSIZE parameter in SQL*Plus, SQL*Forms, and Pro*C. A setting of 200 is suggested.