Introduction

There are a wide range of issues that could lead to poor performance. Performance of our Oracle database problems could be related to different areas of the system:

• Application design

• Application code

• Memory

• I/O

• Resource contention

• Operating System

• CPU

When we want to tune a database in a proactive way, we can follow the previous list from the top to the bottom.

Issues in the first two areas generally lead the database to very bad performance and to scalability issues. The most common performance problems in an Oracle database related to application design and coding are as follows:

• Incorrect session management

• Poorly designed cursor management

  • Binding variables

  • Cursor sharing

  • Non-set operations

• Inadequate relational design

• Improper use of storage structures

Let's explain each performance problem listed in the previous paragraph. Troubles related to memory, input/output, contention, and operating systems will be explored in the following chapters. A well-tuned application can lead to a significant performance improvement, so it's natural to concentrate the first efforts on performance tuning to application design and coding.

Incorrect session management

Poor session management can lead to scalability problems. For example, if a web page logs on to a database, gets some data, and logs off; the time spent for the log on procedure could be an order of magnitude greater than the time required to execute the queries needed to bring the data which the user has requested.

Poorly designed cursor management

There are different problems related to cursor management.

The first rule in writing applications which connect to an Oracle database is to always use bind variables, which means not to include parameters in SQL statements as literals.

For example, we could code something like the following (using SQL*Plus, connected as user HR):

SQL>SELECT * FROM hr.jobs WHERE job_id = 'SA_MAN';

This is equivalent to the following:

SQL>VARIABLE JOBID VARCHAR2(10)
SQL>EXEC :JOBID := 'SA_MAN'
SQL>SELECT * FROM hr.jobs WHERE job_id = :JOBID;

The big difference between the two examples is in the way the database parses the statements when they are called more than once with different values. Executing the statements the second time, in the first case will require a hard parse, whereas in the second case, Oracle will reuse the execution plan prepared at the time of the first execution, resulting in a huge performance gain.

Note

This behavior is due to the way Oracle checks whether a SQL statement is already in memory or needs to be parsed. A hash value of the SQL string is calculated, and is compared to the hash values already in memory. If we supply a different literal value each time, a new hash value will get generated for a SQL statement and hence Oracle has to parse the statement every time.

Using bind variables will not change the SQL string so Oracle has to parse the statement only once; from there on it will find the hash value in memory—if it doesn't age out—thus reusing the execution plan already existing in memory.

Cursor sharing is another problem related to the parse process. We can set the database parameter CURSOR_SHARING to the values SIMILAR or FORCE, to mitigate the drawbacks related to not using bind variables. In this situation, the database will parse two queries with a different SQL text to a single cursor; for example:

SQL>SELECT * FROM hr.jobs WHERE job_id = 'SA_MAN';
SQL>SELECT * FROM hr.jobs WHERE job_id = 'AC_ACCOUNT';

Both of these statements will be parsed to a single cursor if the parameter CURSOR_SHARING is set to one of the values mentioned.

When a query is dynamically built by the application—for example, to reflect different types of user-defined filters or sorting options—it's important that the statement is built always in the same way—using bind variables, of course—to facilitate the reuse of the cursors, mostly if the CURSOR_SHARING parameter is set to the value EXACT.

Another common problem related to cursor management, is the use of non-set operations. While for the human mind it is simpler to think of an algorithm as an iterative sequence of steps, relational databases are optimized for set operations. Many a times developers code something like the following example code:

CREATE OR REPLACE PROCEDURE example1 (
  JOBID IN hr.jobs.job_id%TYPE) IS
BEGIN
  DECLARE 
  l_empid hr.employees.employee_id%TYPE;
  l_sal hr.employees.salary%TYPE;
  CURSOR jc IS SELECT e.employee_id, e.salary
    FROM hr.employees e 
      INNER JOIN hr.jobs j ON j.job_id = e.job_id
    WHERE e.job_id = JOBID 
    AND e.salary > (j.max_salary - j.min_salary) / 2;
 BEGIN
  OPEN jc;
  LOOP
    FETCH jc INTO l_empid, l_sal;
    EXIT WHEN jc%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_empid) || ' ' ||
     TO_CHAR(l_sal));
    UPDATE hr.employees SET salary = l_sal * 0.9 
      WHERE employee_id = l_empid;
  END LOOP;
  CLOSE jc;
 END;
END;

This example is trivial, but it's good enough to explain the concept. In the procedure, there is a loop on the employees of a certain job, which decreases the salaries that are higher than the average for a particular job. The stored procedure compiles and executes well, but there is a better way to code this example, shown as follows:

CREATE OR REPLACE PROCEDURE example2 (
  JOBID IN hr.jobs.job_id%TYPE) IS
BEGIN
  UPDATE hr.employees e SET
    e.salary = e.salary * 0.9
  WHERE e.job_id = JOBID 
  AND e.salary > (SELECT (j.max_salary - j.min_salary) / 2 FROM hr.jobs j
     WHERE j.job_id = e.job_id);
END;

In the latter version we have only used one statement to achieve the same results. Besides the code length, the important thing here is that we thought in terms of set-operations, rather than in an iterative way. Relational databases perform better when we use this type of operation. We will see how much and why in Chapter 4, Optimizing SQL Code and Chapter 6, Optimizing PL/SQL Code, in the Introducing arrays and bulk operations and Array processing and bulk-collect recipes, respectively.

Inadequate relational design

A big issue could be the relational design of the database. Here we are not discussing academic ways to design a database system, because in the real-world sometimes a relational design could be less-than-perfect in terms of normalization, for example, to provide better performance in the way the data is used.

When we speak about bad relational design, we mean problems like over-normalization, which often leads to an overabundance of table joins to obtain the desired results.

Often, over-normalization is a problem which arises when we try to map an object-oriented model to a relational database: a good volume and operations analysis could help in designing the logical model of the database. For example, introducing a redundant column to a table can lead to better performance because the redundant data, otherwise, have to be calculated by scanning (in most cases) a big table.

Another big issue in relational design is related to the use of incorrect indexes on a table. Based on the data selection approach an application is going to take, correct indexes should be set on the table, and this is one of the design considerations while creating a relational database model.

Improper use of storage structures

The Oracle database logical structure is determined by the tablespace(s) and by the schema objects. Wrong choices about these structures often lead to bad performance.

While designing an Oracle database, we have a rich set of schema objects, and we have to answer questions like "Which is better, a bitmap index or a reverse key index?", looking at both the application and data.

In the latest releases of Oracle database, many operations to alter storage structures can be performed with the database online, with minimal performance decay, and without service shortage.

We will examine in depth the problems we have just been presented with in later chapters, namely, session management and relational design in Chapter 2, cursor management in Chapter 4, and storage structures in Chapter 3.

OK, let's begin!