Monitor your sequences

Never let your sequence exceed your column precision. Here is simple way to generate a check..

select ds.sequence_name,
dtc.table_name,
dtc.column_name,
ds.last_number,
dtc.data_precision,
round(ds.last_number/power(10,dtc.data_precision),2)*100||'%' percent_used
from all_sequences ds,
all_tab_columns dtc
where ds.sequence_name like 'SEQ_'||trim(upper(dtc.column_name))
order by percent_used desc, sequence_name;

Avoid Use of Sequence in the CASE, DECODE, NVL

While using the NEXTVAL function in a select query (CASE) , inside a false condition, it increases the current value of the sequence.

Create Sequence TEST_SEQ Start with 1 increment by 1;

SELECT TEST_SEQ.CURRVAL FROM DUAL
------------
2

SELECT CASE WHEN 1>2 THEN TEST_SEQ.NEXTVAL ELSE TEST_SEQ.CURRVAL END SEQ_NO FROM DUAL;
------------
3
In second select statement, NEXTVAL is used inside a FALSE condition

The process of evaluating a sequence reference in an SQL statement is somewhat unique. It's almost like Oracle treats them as substitution variables on a per-row basis, prior to any other expression/condition evaluations, evaluating .NEXTVAL first (if referenced), then .CURRVAL, then the other conditions/expressions.

This is in part due to the need to return the same sequence.nextval value within a row, regardless of the number of sequence.nextval references. To do this, it probably goes ahead and evaluates .NEXTVAL even if it is not "needed" (due to a false condition in a CASE, DECODE, etc. statement). Interestingly that's how sequences work, and its documented that way. The SQL REFERENCE manual (Chapter 3 "Pseudo columns", section for "sequences") states "Within a single SQL statement containing a reference to NEXTVAL, Oracle increments the sequence only once: For each row returned by the outer query block of a SELECT statement."
so, if the query block references NEXTVAL and returns a row, the sequence is incremented. doesn't matter if you use it in a case, decode, nvl or anything else.

Suggested to use the function which will not be direct reference to the sequence and therefore it will not increment for the FALSE condition. GettestSequence custom function will get the next value from the SEQUENCE.

SELECT CASE WHEN 1>2 THEN GettestSequence('INSTRUMENT') ELSE c14_ads.TEST_SEQ.CURRVAL END SEQ_NO FROM DUAL;

How to get Age in Years and Months? (Using MONTHS_BETWEEN)

Simple query to get age in Years and Months..

select trunc(months_between(<(today)>,<(birth Date)>)/12) years,
FLOOR(months_between(<(today)>,<(birth Date)>)-12*trunc(months_between(<(today)>,<(birth Date)>)/12)) months
from dual;

So for e.g. If somebody was born on 01/01/2009 and you want to know the age then run the following

select trunc(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))/12) years,
FLOOR(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))-12*trunc(months_between(SYSDATE,to_date('01/01/2009','mm/dd/yyyy'))/12)) months
from dual;

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;

Use ROWNUM for Pagination!!!

Use of ROWNUM in the queries can be very useful to improve query performance for pagination.


select * from

( select rownum rnum, a.* from (your_query) a where rownum <= :M )

where rnum >= :N;

select * from (

select /*+ first_rows(25) */ your_columns, row_number() over (order by something unique) rn from your_tables )

where rn between :n and :m

order by rn;

Avoid accidental table scans

Table scans that occur unintentionally are a major source of poorly performing SQL. Causes include:

a. Missing Index

b. Using “!=“, “<>” or NOT

i. Use inclusive range conditions or IN lists

c. Looking for values that are NULL

i. Use NOT NULL values with a default value

d. Using functions on indexed columns – Function Based Indexes.

Use “functional” indexes in Oracle8i and above

One of the most fundamental SQL tuning problems is the “accidental” table scan. Accidental table scans usually occur when the SQL programmer tries to perform a search on an indexed column that can’t be supported by an index. This can occur when:

Using != (not equal to). Even if the not equals condition satisfies only a small number of rows, Oracle will not use an index to satisfy such a condition. Often, you can re-code these queries using > or IN conditions, which can be supported by index lookups.

Searching for NULLS. Oracle won’t use an index to find null values, since null values are not usually stored in an index (the exception is a concatenated index entry where only some of the values are NULL). If you’re planning to search for values which are logically missing, consider changing the column to NOT NULL with a DEFAULT clause. For instance, you could set a default value of “UNKNOWN” and use the index to find these values.

Using functions on indexed columns. Any function or operation on an indexed column will prevent Oracle from using an index on that column. For instance, Oracle can’t use an index to find SUBSTR(SURNAME,1,4)=’PWAR’. Instead of manipulating the column, try to manipulate the search condition. In the previous example, a better formulation would be SURNAME LIKE ‘PWAR%’. In Oracle8i you can create functional indexes, which are indexes created on functions, providing that the function always returns the same result when provided with the same inputs. This allows you, for instance, to create an index on UPPER(surname).

Common Complaints

Every application big or small at some point in time will have performance issues in its life spam. Some of the common complaints are

1) Application is very slow
2) Reports taking hours to generate
3) Schedule jobs taking way too long to complete
4) Tired of throwing money @ Oracle consulting.

By taking some pro-active approach and setting the right expectation some of the issues can be handled. There is no single Oracle init parameter which will fix all the problems.

Always design and develop with performance in mind

All too often, SQL tuning is performed on an application designed and developed with virtually no consideration given to performance requirements. SQL tuning is often the best option for tuning such applications, but it is both more efficient and effective to design an application with performance in mind.

Producing good performance by design requires that the following activities occur within the development life-cycle:

1) Identify performance targets
2) Measure performance as early as possible.
3) Focus on key areas of the application
4) Prototype critical portions of the application
5) Consider de-normalization where ever applicable.

Establish a tuning and development environment

1) A significant portion of SQL that performs poorly in production was originally crafted against empty or nearly empty tables.

2) Make sure you establish a reasonable sub-set of production data that is used during development and tuning of SQL

3) Make sure your developers understand EXPLAIN PLAN and tkprof, or equip them with commercial tuning tools.

Keep it Simple.

1) Balance/Reduce the workload (If Possible).

2) Pre – Generate reports overnight when there is less load on the system.

3) Parallelize the queries – Use the full potential of the system.

Execute Smart Queries

1) Index wisely - Use of Indexes

2) Eliminate Full table scans.

3) Use concatenated indexes where appropriate

4) Consider advanced indexing options

a. Hash Clusters

b. Bit mapped indexes

c. Index only tables