SQL Performance Tuning-Nested Queries

Nested Subqueries
Using nested sub queries instead of joining tables in a single query can lead to dramatic performance gains. Only certain queries will meet the criteria for making this modification. When you find the right one, this trick will take performance improvement to an exponentially better height.  The conditions for changing a query to a nested sub query occur when:
Tables are being joined to return the rows from ONLY one table.
Conditions from each table will lead to a reasonable percentage of the rows to be retrieved (more than 10%)
The original query:
SELECT     A.COL1, A.COL2
FROM     TABLE1 A, TABLE2 B
WHERE     A.COL3 = VAR
AND     A.COL4 = B.COL1
AND     B.COL2 = VAR;

The new query:
SELECT     A.COL1, A.COL2
FROM     TABLE1 A
WHERE     A.COL3 = VAR
AND     EXISTS
(SELECT     ‘X’
FROM     TABLE B
WHERE     A.COL4 = B.COL1
AND     B.COL2 = VAR);

A real life example:
SELECT    ORDER.ORDNO, ORDER.CUSTNO
FROM    ORDER_LINE OL, ORDER
WHERE    ORDER.ORDNO = OL.ORDNO
AND    ORDER.CUSTNO = 5
AND     OL.PRICE = 200;

Execution Time: 240 Minutes

The solution:
SELECT    ORDNO, CUSTNO
FROM    ORDER
WHERE    CUSTNO = 5
AND EXISTS
(SELECT     ‘X’
FROM     ORDER_LINE OL
WHERE     ORDER.ORDNO = OL.ORDNO
AND OL.PRICE = 200);

SQL Tuning Technics-Recursive Procedures

SQL Parsing in Recursive Procedures
Reduce the parse-to-execution ratio in your applications.
Description: Extensive SQL parsing can become a serious problem for a heavily loaded Oracle instance. This is especially true if a SQL statement executed many times is also parsed many times. There are a number of standard techniques and guidelines, which help reduce unnecessary parsing. In some special cases, though, even following all the guidelines does not save you from extensive parsing. Find out below how to apply a workaround in a specific situation to further reduce the parse-to-execute ratio of a statement.
The effect can be observed when a SQL cursor is used in a recursively-structured procedure. In such procedures, very often a cursor is opened and for each fetched row the same procedure is called, which opens the same cursor, etc. In most of the cases the recursive call is executed before the cursor is closed. As a result of this, for each new "cursor open" a parse call is executed resulting in as many parses as executions.
Take a look at a simplified recursive procedure using the SCOTT schema:

PROCEDURE recurs (p_mgr IN emp.mgr%TYPE)
IS
    CURSOR emp_mgr IS
    SELECT empno
    FROM emp
    WHERE mgr = p_mgr;

BEGIN
    FOR c IN emp_mgr
    LOOP
        recurs(c.empno);
    END LOOP;
END recurs;
As you can see the recursive call is executed before the (implicit) cursor is closed. The main idea for reducing the parse calls is to first collect the results of the cursor (for example in a PL/SQL table), then close the cursor and finally cycle through the results and perform the recursive procedure calls.
See below and example of such procedure (In this procedure I have used a bulk bind select, but a normal cursor loop can be used too):

PROCEDURE recurs_close (p_mgr IN emp.mgr%TYPE)
IS
    CURSOR emp_mgr IS
    SELECT empno
    FROM emp
    WHERE mgr = p_mgr;
    TYPE t_empno IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
    p_empno t_empno;
    i PLS_INTEGER := 0;
BEGIN

    OPEN emp_mgr;
    FETCH emp_mgr BULK COLLECT INTO p_empno;
    i := emp_mgr%ROWCOUNT;
    CLOSE emp_mgr;
    FOR j IN 1..i
    LOOP
        recurs_close(p_empno(j));
    END LOOP;
END recurs_close;
In the excerpts of the trace files generated during the procedure execution can be seen that the first procedure has as many parses as executions (14), while the second has 1 parse only.


exec cursor_parse.recurs(7839);

SELECT empno
    FROM emp
    WHERE mgr = :b1


call     count    cpu    elapsed    disk      query    current rows
------- ------  ----- ---------- ------- ---------- ---------- ----------
Parse       14   0.02       0.15       0          0          0 0
Execute     14   0.00       0.00       0          0          0 0
Fetch       27   0.00       0.05       1         26         28 13
------- ------  ----- ---------- ------- ---------- ---------- ----------
total       55   0.02       0.20       1         26         28 13


exec cursor_parse.recurs_close(7839);

SELECT empno
    FROM emp
    WHERE mgr = :b1


call    count    cpu     elapsed  disk  query  current rows
------- -----  ---- -------  ----  -----  ------- --------
Parse       1     0.00    0.00     0      0        0 0
Execute    14   0.00    0.00     0      0        0 0
Fetch      14     0.00    0.00     0     14       28 13
------- -----  ---- ------- ----- ------ -------- --------
total      29  0.00    0.00     0     14       28 13

Most of the important statistics are better for the execution of the recurs_close than the recurs procedure.

Statistic name                          recurs  recurs_close
opened cursors cumulative           26      12
recursive calls                                               89      50
session logical reads                       84      72
consistent gets                            41      29
no work - consistent read gets          32      20
cursor authentications                        2       1
parse count (total)                        26      12

oracle tuning tips

Watch Non-Indexed WHERE Conditions
Oracle evaluates Non-Indexed conditions linked by AND bottom up
Bad: select * from address where
                         areacode = 500003 and
                         type_nr = (select seq_nr from code_table where type = ‘HOME’)
Good: select * from address where
                         type_nr = (select seq_nr from code_table where type = ‘HOME’) and
                         areacode = 500003
Oracle evaluates Non-Indexed conditions linked by OR top down
Bad: select * from address where
                         type_nr = (select seq_nr from code_table where type = ‘HOME’) or
                         areacode = 500003
Good: select * from address where
                         areacode = 500003 or
                         type_nr = (select seq_nr from code_table where type = ‘HOME’)
Oracle Tuning Tips UNION/OR
Consider IN or UNION in place of OR  
i
 columns are not indexed, stick with OR
if columns are indexed, use IN or UNION in place of OR
IN example
Bad: select * from address where
                         state = 'AP‘ or
                         state = 'KL‘ or
                         state = 'KL‘
Good: select * from address where
                         state in ('AP','KL','KL')
UNION example
Bad: select * from address where
                         state = ‘KL’ or
                         areacode = 500003
Good: select * from address where
                         state = ‘KL’
               union
               select * from address where
                         areacode = 500003

Bulk Collect In Oracle

BULK COLLECT

Ø  This is used for array fetches

Ø  With this you can retrieve multiple rows of data with a single roundtrip.

Ø  This reduces the number of context switches between the pl/sql and sql engines.

Ø  Reduces the overhead of retrieving data.

Ø  You can use bulk collect in both dynamic and static sql.

Ø  You can use bulk collect in select, fetch into and returning into clauses.

Ø  SQL engine automatically initializes and extends the collections you reference in the bulk collect clause.

Ø  Bulk collect operation empties the collection referenced in the into clause before executing the query.

Ø  You can use the limit clause of bulk collect to restrict the no of rows retrieved.

Ø  You can fetch into multible collections with one column each.

Ø  Using the returning clause we can return data to the another collection.

BULK COLLECT IN FETCH

Ex:

DECLARE

     Type t is table of dept%rowtype;

     nt t;

     Cursor c is select *from dept;

BEGIN

     Open c;

     Fetch c bulk collect into nt;

     Close c;

     For i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

BULK COLLECT IN SELECT

Ex:

DECLARE

     Type t is table of dept%rowtype;

     Nt t;

BEGIN

     Select * bulk collect into nt from dept;

     for i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Dname = SALES Loc = CHICAGO

Dname = OPERATIONS Loc = BOSTON

LIMIT IN BULK COLLECT

Ex:

DECLARE

     Type t is table of dept%rowtype;

     nt t;

     Cursor c is select *from dept;

BEGIN

     Open c;

     Fetch c bulk collect into nt limit 2;

     Close c;

     For i in nt.first..nt.last loop

           dbms_output.put_line('Dname = ' || nt(i).dname || ' Loc = ' || nt(i).loc);

     end loop;

END;

Output:

Dname = ACCOUNTING Loc = NEW YORK

Dname = RESEARCH Loc = DALLAS

Oracle Parametrized Cursors

Oracle Parametrized Cursors

•   This was used when you are going to use the cursor in more than one place with different values for the same where clause.
•   Cursor parameters must be in mode.
•   Cursor parameters may have default values.
•   The scope of cursor parameter is within the select statement.

Ex:

     DECLARE

         cursor c(dno in number) is select * from dept where deptno = dno;

         v_dept dept%rowtype;

      BEGIN

         open c(20);

         loop

             fetch c into v_dept;

             exit when c%notfound;

            dbms_output.put_line('Dname = ' || v_dept.dname || ' Loc = ' || v_dept.loc);

         end loop;

         close c;

     END;

Output:

     Dname = RESEARCH Loc = DALLAS

PACKAGED CURSORS WITH HEADER IN SPEC AND BODY IN PACKAGE BODY

•   cursors declared in packages will not close automatically.In packaged cursors you can modify the select statement without making any changes to the cursor header in the package specification.
• Packaged cursors with must be defined in the package body itself, and then use it as global for the package.You can not define the packaged cursor in any subprograms.
•   Cursor declaration in package with out body needs the return clause.
• Ex:

CREATE OR REPLACE PACKAGE PKG IS

                         cursor c return dept%rowtype is select * from dept;

                procedure proc is

END PKG;

CREATE OR REPLACE PAKCAGE BODY PKG IS

      cursor c return dept%rowtype is select * from dept;

PROCEDURE PROC IS

BEGIN

      for v in c loop

           dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' || v.dname || '   

                                                  Loc = ' || v.loc);

      end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

        Deptno = 10 Dname = ACCOUNTING Loc = NEW YORK

        Deptno = 20 Dname = RESEARCH Loc = DALLAS

        Deptno = 30 Dname = SALES Loc = CHICAGO

                  Deptno = 40 Dname = OPERATIONS Loc = BOSTON

CREATE OR REPLACE PAKCAGE BODY PKG IS

      cursor c return dept%rowtype is select * from dept where deptno > 20;

PROCEDURE PROC IS

BEGIN

      for v in c loop

           dbms_output.put_line('Deptno = ' || v.deptno || ' Dname = ' || v.dname || '   

                                                  Loc = ' || v.loc);

      end loop;

END PROC;

END PKG;

Output:

SQL> exec pkg.proc

               Deptno = 30 Dname = SALES Loc = CHICAGO

                  Deptno = 40 Dname = OPERATIONS Loc = BOSTON