SQL Tuning Steps

3 Main steps to Tuning:
Identify high load sql's or programs
Make sure that these sql's/programs have optimal execution plans
If not, take corrective measures to improve the performance

Purpose/Goals of Tuning:
Reduce the response time taken by sql/program
Reduce the resources consumed

Ways to accomplish these goals:
Reduce/Optimize the highly utilized query
Balance the work load
Parallelize the work load

Reduce/Optimize the highly utilized query:
You can rewrite the query in such a way that functionality does not change - in case the underlying indexes are not getting used or if the query is written badly in first place
See if you can improve the performance by creating an index on any of the columns that results in relative low data fetch as compared to number of rows in the table

Balance the work load:
Look out if any of the programs/sql statements can be moved to batch process.  Since the load on the OLTP  system is considerably more during day time.  So, if any process is not required to execute during day time - it can be safely moved to execute during off peak hours.  This kind of balancing can free up some resources and helps in improving the performance

Parallelize the work load:
See if any of the programs/sql's running in batch process can be parallelized.  This will significantly improved the execution time of the sql.  This is normally advisable for batch processes as compared to OLTP process, since batch jobs are generally scheduled during off peak hours.  And comparatively more resources are available during this time.

Different tools/utilities to identify high load sql statements or programs:
Automatic Database Diagnostic Monitor (ADDM)
SQL Trace - TKPROF
Automatic Workload Repository
V$SQL* Views - V$SQLSTATS, V$SQLTEXT,  V$SQLAREA, V$SQL_PLAN, V$SQL and V$SQL_PLAN_STATISTICS

How to Identify resource consuming SQL:
Categories:
Specific program that is consuming considerable amount of resources
Spread across application - could be multiple statements across application consuming high amount of resources

Speicific program:
First thing to look for is EXPLAIN PLAN of the sql statements within the program and make sure that they are optimal
If EXPLAIN PLAN is not possible because of dynamic sql, than enable SQL_Trace for the program and use TKPROF utility to generate the output.  You can sort this output by elapsed time in descending order and figure out which sql statements are the culprit or poorly written.

Across application:
Use AWR
Use V$SQL* views:
V$SQLSTATS - For SQL related statistics
V$SYSSTAT - For System statistics
V$FILESTAT - For I/O

V$SQLSTATS.BUFFER_GETS - For CPU utilization
V$SQLSTATS.DISK_READS - For I/O statements
V$SQLSTATS.SORTS - For sorts

Once a high load sql statement is identified, prep steps:
Study and understand the structure of the underlying tables
Indexes - unique or non-unique
Latest statistics gathered on the tables being used or not

Tools to tune the queries:
ADDM - It looks at AWR for tuning
SQL Tuning Advisor
SQLAccess Advisor - For materialized views

Explain Plan Checks:
Driving table has the best filter condition - Driving table is determined ideally by the table that results in fetching of the fewest rows of all the tables

Oracle Database Collections

Types of Collections:

1. PL/SQL Tables or Index By Table or Associative Array
2. VARRAY
3. Nested Tables

Associative Array:

This can only exist in PL/SQL and cannot be created in SQL.

TYPE associative_array_type IS TABLE OF element_type INDEX BY key_data_type

key_data_type - PLS_INTEGER or BINARY_INTEGER or VARCHAR2(size)

v_associative_array associative_array_type;

v_associative_array(index) := value;

Key points to keep in mind before and while using Associative Arrays:

• Unbounded - No upper boundary on the number of entries that can be stored
• They are sparse - index values not necessary to be in sequence.  So there can be gaps between index values
• Cannot be persisted
• No initialization required
• Single dimensional

VARRAY:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE varray_type IS VARRAY(size) OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE varray_type AS VARRAY(size) OF element_type;

v_varray varray_type;

--Initialization is required

v_varray := varray_type();

--Once initialized, make room for adding element(s)

v_varray.extend(n);

--Now, assign

v_varray(index) := value;

Key points to keep in mind before and while using VARRAY:

• Bounded - Upper boundary is required - you should know the size of the collection at the time of declaration
• They are dense - index values are in sequence.  So no gaps between index values
• Can be persisted
• Initialization required
• Single dimensional

Nested Table:

This can be created in both SQL and PL/SQL.

--PL/SQL creation command

TYPE nested_table_type IS TABLE OF element_type;

--SQL creation command

CREATE [OR REPLACE] TYPE nested_table_type AS TABLE OF element_type;

v_nested_table nested_table_type;

--Initialization is required

v_nested_table := nested_table_type();

--Once initialized, make room for adding element(s)

v_nested_table.extend(n);

--Now, assign

v_nested_table(index) := value;

Key points to keep in mind before and while using Nested Table:

• Unbounded
• They are dense to begin with, but can become sparse with deletion of elements
• Can be persisted
• Initialization required
• Single dimensional
• Elements are not stored in any particular order

PLS_INTEGER and BINARY_INTEGER

There is no difference between these 2 types in and after Oracle Database Version 10g.

But prior to 10g these types were not exactly the same:

PLS_INTEGER uses machine arithmetic, unlike the library arithmetic usage of NUMBER or BINARY_INTEGER data types.

Prior to 10g though only BINARY_INTEGER was allowed in Index-By (Associative array) tables.

Machine arithmetic is always faster than the Library Arithmetic:
Reason being that for any mathematical operation machine arithmetic will go to the hardware to get an answer while Library arithmetic will go to a software library to get the results.  Software library is generally slower since it emulates the functionality of the hardware.

For this reason, PLS_INTEGER was the preferred option in versions prior to 10g.

Some additional information on NUMBER, INTEGER, PLS_INTEGER and BINARY_INTEGER:

INTEGER is defined in STANDARD package as a subtype of NUMBER data type.
BINARY_INTEGER is subtype of INTEGER.
PLS_INTEGER is subtype of BINARY_INTEGER.

INTEGER and NUMBER are platform independent.

One point to keep in mind while using the PLS_INTEGER is that in case of overflow PL/SQL engine will throw an exception while in case of BINARY_INTEGER it will not throw any exception if the result is being assigned to a NUMBER data type.

Oracle PL SQL Exception Handling

Exception Types:

• Internal or System Exceptions
• User Defined Exceptions

Declaring & Raising Exceptions:

DECLARE
   invalid_format EXCEPTION;
BEGIN
   ......................
   ..................
   IF.....THEN
      RAISE invalid_format;
   END IF;
   .....................
EXCEPTION
WHEN invalid_format THEN
   ...............
END;

Pragma EXCEPTION_INIT:
This is for assigning exception names to Oracle error/exception codes that do not have predefined names. Normally we see these kind of exceptions being handled using OTHERS block, but assigning names using Pragma is more cleaner approach. Pragma is evaluated during compile time.

This assignment of name to error code is done in the declaration section:

DECLARE
   PRAGMA EXCEPTION_INIT(network_error, -12541);
BEGIN
   ......................................
EXCEPTION
WHEN network_error THEN
   .............
END;

RAISE_APPLICATION_ERROR:
This lets you raise user defined ORA- exceptions. This helps in handling business logic exceptions in a more cleaner way than returning unhandled exceptions to the caller.

RAISE_APPLICATION_ERROR(
<error number from -20000 to -20999>,
<error message>,
<TRUE/FALSE - TRUE to keep the maintain the entire error stack, default is FALSE - this will only have the latest error>]);

DECLARE
BEGIN
   IF....THEN
      RAISE_APPLICATION_ERROR(-20001, "Logical Error");
   END IF;
EXCEPTION
END;

To capture the name of the unit/procedure/function from where the error is being raised - you can use $$PLSQL_UNIT (from 10g onwards).  And to capture the line where you are logging the error you can use $$PLQSL_LINE (also from 10g onwards).

Some other useful Oracle constructs that can be used to handle and capture the exceptions effectively:

DBMS_UTILITY.FORMAT_ERROR_STACK
   This returns the formatted error stack

DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
   This displays the call stack where the exceptions were raised along with the line numbers

DBMS_UTILITY.FORMAT_CALL_STACK
   This displays the formatted call stack

Oracle PL SQL Pre-Defined Exceptions

I am just listing down some of the frequently faced Oracle PL SQL Internal/System Defined Exceptions:

Exception	Raised when ...
COLLECTION_IS_NULL	Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	Your program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
INVALID_CURSOR	Your program attempts an illegal cursor operation such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
SUBSCRIPT_BEYOND_COUNT	Your program references a nested table or varray element using an index number larger than the number of elements in the collection.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)

References:

http://docs.oracle.com/cd/A97630_01/appdev.920/a96624/07_errs.htm