Oracle Performance Tuning tips

Goals for SQL Tuning

1/ Reduce the Workload
2/ Balance the Workload
3/Parallelize the Workload

Reduce the Workload
If a commonly executed query needs to access a small percentage of data in the table, then it can be

executed more efficiently by using an index. By creating such an index, you reduce the amount of resources used.  

Balance the Workload

Systems often tend to have peak usage in the daytime when real users are connected to the system, and low usage in the nighttime.

Avoiding CPU Overhead in PL/SQL Code

1. Make SQL Statements as Efficient as Possible

•     Make sure you have appropriate indexes.

•     Analyze the execution plans and performance of the SQL statements, using:

        EXPLAIN PLAN statement

•         Rewrite the SQL statements if necessary. For example, query hints can avoid problems such as unnecessary full-table scans.

•     If you are running SQL statements inside a PL/SQL loop, look at the FORALL statement as a way to replace loops of INSERT, UPDATE, and DELETE statements.

•     If you are looping through the result set of a query, look at the BULK COLLECT clause of the SELECT INTO statement as a way to bring the entire result set into memory in a single operation.

   2.  Make Function Calls as Efficient as Possible

• Avoid unnecessary calls to subprograms, and optimize their code:

    If a function is called within a SQL query, you can cache the function value for each row by creating a function-based index on the table in the query. The CREATE INDEX statement might take a while, but queries can be much faster.

    If a column is passed to a function within an SQL query, the query cannot use regular indexes on that
column, and the function might be called for every row in a (potentially very large) table. Consider nesting the query so that the inner query filters the results to a small number of rows, and the outer query calls the function only a few times as shown in Example :

BEGIN
-- Inefficient, calls function for every row
   FOR item IN (SELECT DISTINCT(SQRT(department_id)) col_alias FROM employees)
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
-- Efficient, only calls function once for each distinct value.
   FOR item IN
   ( SELECT SQRT(department_id) col_alias FROM
     ( SELECT DISTINCT department_id FROM employees)
   )
   LOOP
      DBMS_OUTPUT.PUT_LINE(item.col_alias);
   END LOOP;
END;
/

• If you use OUT or IN OUT parameters, PL/SQL adds some performance overhead to ensure correct action in case of exceptions (assigning a value to the OUT parameter, then exiting the subprogram because of an manhandled exception, so that the OUT parameter keeps its original value).

• If your program does not depend on OUT parameters keeping their values in such situations, you can add the NOCOPY keyword to the parameter declarations, so the parameters are declared OUT NOCOPY or IN OUT NOCOPY.

  3.   Make Loops as Efficient as Possible

Because PL/SQL applications are often built around loops, it is important to optimize both the loop itself and  the code inside the loop:

•     To issue a series of DML statements, replace loop constructs with FORALL statements.

•     To loop through a result set and store the values, use the BULK COLLECT clause on the query to bring the query results into memory in one operation.

• Explore include UNION, INTERSECT, MINUS, and CONNECT BY.

•     You can also nest one query inside another (known as a subselect) to do the filtering and sorting in multiple stages. For example, instead of calling a PL/SQL function in the inner WHERE clause (which might call the function once for each row of the table), you can filter the result set to a small set of rows in the inner query, and call the function in the outer query.

   4.  Use Built-In String Functions

• PL/SQL provides many highly optimized string functions such as REPLACE, TRANSLATE, SUBSTR, INSTR, RPAD, and LTRIM. The built-in functions use low-level code that is more efficient than regular PL/SQL.

   5. Put Least Expensive Conditional Tests First

PL/SQL stops evaluating a logical expression as soon as the result can be determined. This functionality is known as short-circuit evaluation.

    6. Minimize Data Type Conversions
At run time, PL/SQL converts between different data types automatically. For example, assigning a PLS_INTEGER variable to a NUMBER variable results in a conversion because their internal representations are different.

• Use PLS_INTEGER or SIMPLE_INTEGER for Integer Arithmetic

• Use BINARY_FLOAT, BINARY_DOUBLE, SIMPLE_FLOAT, and SIMPLE_DOUBLE for Floating-Point Arithmetic

Avoiding Memory Overhead in PL/SQL Code

    Declare VARCHAR2 Variables of 4000 or More Characters

    Group Related Subprograms into Packages

    Pin Packages in the Shared Memory Pool

    Apply Advice of Compiler Warnings

1. Declare VARCHAR2 Variables of 4000 or More Characters

You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be.

You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

2. Group Related Subprograms into Packages

When you call a packaged subprogram for the first time, the whole package is loaded into the shared memory pool. Subsequent calls to related subprograms in the package require no disk I/O, and your code executes faster. If the package ages out of memory, and you reference it again, it is reloaded.

You can improve performance by sizing the shared memory pool correctly. Make it large enough to hold all frequently used packages, but not so large that memory is wasted.

3. Pin Packages in the Shared Memory Pool

You can pin frequently accessed packages in the shared memory pool, using the supplied package DBMS_SHARED_POOL. When a package is pinned, it does not age out; it remains in memory no matter how full the pool gets or how frequently you access the package.

Summary of New PL/SQL Language Features

Oracle Database 10g introduces support for these new language features:

•     the binary_float and binary_double datatypes (the IEEE datatypes).

•     the regexp_like, regexp_instr, regexp_substr and regexp_replace builtins to support regular expression  manipulation with standard POSIX syntax.

•     multiset operations on nested table instances supporting operations like equals, union, intersect, except,member, and so on.

•     the user-defined quote character.

•     indices of and values of syntax for forall.

•     the distinction between binary_integer and pls_integer vanishes.