Use of Bind Variables in Dynamic SQLs

One need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL. It, allows you to execute a string containing SQL using the EXECUTE IMMEDIATE command. We need to avoid the hard parse when it is submitted:

"create or replace procedure dsal(p_empno in number)
as
begin
execute immediate 'update emp set sal = sal*2 where empno = '||p_empno; commit;
end;

The way to use bind variables instead is to change the EXECUTE IMMEDIATE command as follows:
create or replace procedure dsal(p_empno in number) as
begin
execute immediate 'update emp set sal = sal*2 where empno = :x' using p_empno;
commit;
end;
/"

Use Bulk Collect/ Object Type for batch Processing

Whenever there is a need to fetch large volumes of data BULK Collect with Limits is one the best ways. The most important thing to remember when you learn about and start to take advantage of features such as BULK COLLECT is that there is no free lunch. There is almost always a trade-off to be made somewhere. The tradeoff with BULK COLLECT, like so many other performance-enhancing features, is "run faster but consume more memory." Fortunately, PL/SQL makes it easy for developers to control the amount of memory used in a BULK COLLECT operation by using the LIMIT clause.

Enable Oracle to perform hash joins

In cases where a very small table is being joined to a large table, the Oracle hash join will often dramatically speed-up the query. Hash joins are far faster than nested loop joins in certain cases, often in cases where your SQL is joining a large table to a small table. However, in a production database with very large tables, it is not always easy to get your database to invoke hash joins without increasing the RAM regions that control hash joins. For large tables, hash joins requires lots of RAM.

Front End Field Validation

Apply Validation on the Front End. Don’t let DB constraint drive front end validation

Avoid Distinct if you can

Avoid Distinct (AVOID Join and instead use EXISTS)  

SELECT DISTINCT dept_no, dept_name FROM dept D, emp E WHERE D.dept_no = E.dept_no;

SELECT dept_no, dept_name FROM dept D WHERE EXISTS ( SELECT 'X' FROM emp E WHERE E.dept_no = D.dept_no);

Avoid queries with condition IS NULL

Avoid queries with condition IS NULL ( Rather create column with NOT NULL Default value). If IS NULL is required, consider replacing it with a NVL call and a function based index, while this may not help with outer joins, it will help with IS NULL type selects. select count(*) from test where object_id is null; ( TABLE ACCESS (FULL)) select count(*) from test where nvl(object_id,-1)=-1; - INDEX (RANGE SCAN) OF 'FBI_TEST' (NON-UNIQUE)"

Use GOTO Statements

The GOTO statement branches unconditionally to a statement label or block label. The label must be unique within its scope and must precede an executable statement or a PL/SQL block. The GOTO statement transfers control to the labelled statement or block.

Example:

"DECLARE

v_Counter BINARY_INTEGER := 1;

BEGIN

LOOP INSERT INTO MyTable VALUES (v_Counter, 'Loop count');

v_Counter := v_Counter + 1;

IF v_Counter > 50 THEN GOTO l_EndOfLoop; END IF;

END LOOP;

<>
INSERT INTO MyTable (char_col) VALUES ('Done!');
END;/"

Use NOCOPY Parameter hint for Stored Procedures

The NOCOPY hint tells the PL/SQL compiler to pass OUT and IN OUT parameters by reference, rather than by value. When parameters are passed by value, the contents of the OUT and IN OUT parameters are copied to temporary variables, which are then used by the subprogram being called. On successful completion of the subprogram the values are copied back to the actual parameters, but unhandled exceptions result in the original parameter values being left unchanged. The process of copying large parameters, such as records, collections, and objects requires both time and memory which affects performance.

Example:
PROCEDURE in_out_nocopy (p_tab IN OUT NOCOPY t_tab) IS BEGIN NULL; END;

Hide Query Complication using Types

Hide and control complication within Stored procedure by using types. Perform following steps..

1) Create Type
2) Write Stored Procedure
3) Query

Create Type:
----------------------------------------------------------
Drop type OT_TEST_ROW;
Drop type OT_TEST;

CREATE OR REPLACE
TYPE "OT_TEST_ROW" AS OBJECT
--===========================================================================
-- Application : HCP - NJTA
--
-- Description : This type is to populate Data for High Variance
--
-- Modification History:-
-- --------------------------------------------------------------------------
-- Date Name Comments
-- -------- ------------------- --------------------------------------------
-- 03/13/10 Parag Wardhane Created.
--===========================================================================
(
COL_DATE DATE,
COL_CHAR VARCHAR2(10),
COL_NUMBER NUMBER(10,2)
)
/


CREATE OR REPLACE
TYPE "OT_TEST" AS TABLE OF
--===========================================================================
-- Application : HCP - NJTA
--
-- Description : This type is to populate Data for High Variance
--
--
-- Modification History:-
-- --------------------------------------------------------------------------
-- Date Name Comments
-- -------- ------------------- ---------------------------------------------
-- 03/13/10 Parag Wardhane Created.
--===========================================================================
OT_TEST_ROW;
/

2) Write Stored procedure

-----------------------------------------
CREATE OR REPLACE FUNCTION HideQueryComplication (arg_in_Testid IN NUMBER)
RETURN OT_TEST
IS

V_AttemptStep VARCHAR2(500) := 'attempting to initialize';
V_error_msg VARCHAR2(400);
v_success VARCHAR2(1);
V_ErrorCode NUMBER DEFAULT 0;
V_ErrorText VARCHAR2(400);
V_CustomErrorText VARCHAR2(400);
V_ErrorReturnCode NUMBER;


V_CountOrgs NUMBER := 1;

l_data OT_TEST := OT_TEST();
l_BlankData OT_TEST := OT_TEST();

V_Counter NUMBER := 1;


BEGIN

For TestRec in ( SELECT sysdate col_date,'TEST' col_char,'1234' col_number
FROM DUAL /* Any Complicated Query */ ) LOOP


l_data.extend;

l_data(V_Counter) := OT_TEST_ROW(
TestRec.col_date, /* DATE */
TestRec.col_char, /*VARCHAR2 */
TestRec.col_number /*NUMBER*/ );

V_Counter := V_Counter + 1;


END LOOP;


RETURN l_data;

EXCEPTION
WHEN OTHERS THEN


V_CustomErrorText := ': ' || 'While ' || V_AttemptStep;
V_ErrorCode := SQLCODE;
V_ErrorText := SUBSTR(SQLERRM, 1, 400);

RETURN l_BlankData ;

END HideQueryComplication;
/
--------------------------------------------------------

2) Query:

SELECT HideQueryComplication(1) FROM DUAL;