Oracle Database 11gR2 Performance Tuning Cookbook

By Ciro Fiorillo
    What do you get with a Packt Subscription?

  • Instant access to this title and 7,500+ eBooks & Videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Free Chapter
    Starting with Performance Tuning
About this book

Oracle's Database offers great performance, scalability, and many features for DBAs and developers. Due to a wide choice of technologies, successful applications are good candidates to run into performance issues and when a problem arises it's very difficult to identify the cause and the right solution to the problem.

The Oracle Database 11g R2 Performance Tuning Cookbook helps DBAs and developers to understand every aspect of Oracle Database that can affect performance. You will be guided through implementing the correct solution in a proactive way before problems arise, and how to diagnose issues on your Oracle database-based solutions.

This fast-paced book offers solutions starting from application design and development, through the implementation of well-performing applications, to the details of deployment and delivering best-performance databases.

With this book you will quickly learn to apply the right methodology to tune the performance of an Oracle Database, and to optimize application design and SQL and PL/SQL code. By following the real-world examples you will see how to store your data in correct structures and access and manipulate them at a lightning speed. You will learn to speed up sort operations, hack the optimizer and the data loading process, and diagnose and tune memory, I/O, and contention issues.

The purpose of this cookbook is to provide concise recipes, which will help you to build and maintain a very high-speed Oracle Database environment.

Publication date:
January 2012


Chapter 1. Starting with Performance Tuning

Performance tuning is a complex process, which requires a deep knowledge of both physical and logical database structures. In this chapter, we will introduce the process and methodology to adopt in performance tuning an Oracle Database, covering the following recipes:

  • Reviewing the performance tuning process

  • Exploring the example database

  • Acquiring data using a data dictionary and dynamic performance views

  • Analyzing data using Statspack report

  • Diagnosing performance issues using the alert log

  • Analyzing data using Automatic Workload Repository (AWR)

  • Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

  • A working example



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):


This is equivalent to the following:


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.


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:


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:

  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 j ON j.job_id = e.job_id
    WHERE e.job_id = JOBID 
    AND e.salary > (j.max_salary - j.min_salary) / 2;
  OPEN jc;
    FETCH jc INTO l_empid, l_sal;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_empid) || ' ' ||
    UPDATE hr.employees SET salary = l_sal * 0.9 
      WHERE employee_id = l_empid;
  CLOSE jc;

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:

  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 j
     WHERE j.job_id = e.job_id);

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!


Reviewing the performance tuning process

Tuning the performance of an Oracle database is a complex task, which requires in-depth knowledge in different areas. There are a lot of forums, documents, and tutorials online responding to many performance tuning issues related to Oracle Database; often, however, the information gathered from these sources may not be enough to solve the peculiar problem we are experiencing, because of different database versions, different server architectures, and a wide number of variables which make it difficult to find the correct recipe to resolve the symptoms we are facing.

Many would-be DBAs approach a performance problem with a bad attitude; that is, they pretend to solve performance issues without investigating the problem, or with little knowledge about what happens under the hood. Often this approach leads to solutions which don't work or—in the worst case—seem to work temporarily, presenting the same problem or another one after a while.

In the following section, we will see the performance tuning process adopted in this book, which can help us in finding the correct way to diagnose, solve, and prevent performance issues on Oracle Databases.

How to do it...

To solve a performance problem on the database, we need to follow these steps:

  1. Elaborate a baseline.

  2. Investigate the problem.

  3. Assume a solution, a test case, and a rollback strategy.

  4. Implement the solution and test for correctness.

  5. Test the solution.

  6. Compare the results.

  7. If the results are not as good as expected, iterate the process.

How it works...

In the first step, we have to elaborate a baseline, because without a comparison element we will not be able to know if the adopted solution really solves the problems we are facing.

The kind of baseline to elaborate depends heavily on the performance issue. There are some performance indicators which should always be checked, while others are more detailed which can be verified only if a previous indicator points to a particular area of the database. After the baseline is decided for the particular problem we are investigating, it is time to automate the process of gathering data, so it is repeatable.

While investigating the problem the process is iterative, so you can return to the previous step to add other elements to the baseline, for final testing of our solution.

When the investigation drives us to assume a particular solution, before we start implementing it on the database we have to list all the changes we are going to do and elaborate a "rollback solution" for these changes. This is especially the case if we don't have the chance to test our solution over a test database similar to the production one which is suffering the problem. If we think, for example, that adding an index IX1 on table T1 could solve our performance problem, we have to prepare a SQL script to create the index, and another SQL script to drop it, in case we want to go back if something goes wrong. In Oracle 11g, we have the opportunity to create an invisible index and check the execution plan of the query, with minimal impact on other sessions.

We might want to prepare a test-case to test the solution we will implement. This task is simpler if we have isolated the problem very well, so we are able to reproduce the issue. If the problem is random, it might be a nightmare to isolate the steps that lead to poor performance. In the latter case, we could evaluate the frequency of the problem, so we could test our solution by measuring the number of occurrences and comparing the results.

After the solution has been implemented, it must be tested with the same process that created the baseline. Check the results of the measure process and decide if the solutionhas solved the issue. If the results are not acceptable, iterate the whole process until there is a satisfactory outcome.

There's more…

The performance tuning process is a never-ending cycle; even when we solve our performance issue there will be another aspect of the system we can tune to in order to obtain better performance, or we need to satisfy more stringent requirements.

Due to these considerations, the iterative process of performance tuning that will be used throughout the book is represented in the following diagram:

To elaborate a baseline, keep track of how the system—and not only the database—is performing. We need unbiased data to compare before and after different solutions are implemented in the systems.


Performance of the system here means performance of the server, I/O, network, database, the application, and other factors.

If there is a generic "slow response-time problem", and new hardware resources (CPU, RAM) are added to the database server, this may lead to a situation where it performs worse than before. With a good baseline, before adding more resources, we could evaluate if the problem we are experiencing is related to the lack of enough hardware power—for example RAM—or something else.

To describe a good baseline we need as much data as possible; most are acquired directly from the database itself, as we will see in the next section. There is information from other sources: Operating System logs, performance counters, application logs, trace files, network statistics, and the like.

In today's multi-layered applications, it's simple to say "the database is slow" when an application is suffering poor performance, but there will be many cases when the database is performing very well but the application responsiveness is very weak.

With a solid baseline, we can isolate the layer in which the problem first occurred and concentrate our efforts on that application layer. After a baseline is established, start investigating the problem.

In the rest of the book, we will learn how to interpret the results of the baseline to correctly identify the problem. Sorry, there isn't a bullet list or a magic wand; this phase is based on knowledge and previous experience. If a simple causal-effect was in place, it would have already been coded with an automatic solution or a specific diagnostic advice, implemented in the database itself. There are several automatic diagnostic tuning features in the latest releases of Oracle database; SQL Tuning Advisor, SQL Access Advisor, Automatic Database Diagnostic Monitor . These database-centric tools help solve common performance problems, which tend to be easily identified. The real tuning process starts when the magic doesn't work, or they don't work as good as we need them to.

We have seen the most common database performance issues in the previous recipe, divided into several categories to help us in the investigation phase. During this stage, we decide what database area is a bottleneck; for instance, the memory, the I/O, and the SQL code.

Once we have identified and delimited the database area involved in the performance problem, we can assume a solution to the issue. As previously stated, both a test case and a rollback strategy are necessary—the former to check the proposed solution, the latter to revert back if the proposed solution wasn't satisfactory.

Once we have the solution, implementing it is often a trivial task, such as writing a small SQL script to alter a database object or a initialization parameter. Be sure that the solution is implemented using reproducible steps, especially when the task is quite complex or we have to test the solution in a staged database before the production.

At the end of the implementation, we have to test the solution to verify its correctness—probably in a test environment—and to know if the expected performance gain has been reached.

To test the solution there are various scenarios, depending on the work done in previous steps and by the development team. A test case will verify the results; if there are application test sets, they can be used to verify the correctness of the solution, especially if the application logic has changed.

After we have assured ourselves about the correctness of the solution implemented, compare the performance of the database (and of the application) to the baseline gathered in the first step of the process.

If the comparison shows that we have not solved the puzzle, well, let's revert back to the applied solution and start again from the first step, investigating the problem better or assuming another solution. Alternately, if the result is satisfactory, very well, let's start again from the first step to solve another problem. Always remember that the tuning process is something which evolves from the application design and lasts throughout the application life cycle.

In describing the performance tuning process, we have stated a baseline. The Oracle database helps us even in this task, with different tools that we can use to monitor the database itself and to take measurements of various performance indicators.

In the following recipes, we will introduce different tools to acquire performance data from the database, illustrating the guidelines to use them. The diagnostic tools presented are:

  • Data Dictionary and Dynamic Performance Views

    • Analyze command

    • Analyze schema and database with DBMS_UTILITY package

  • DBMS_STATS package

  • Statspack report

  • Alert log and trace files

  • Automatic Workload Repository (AWR)

  • Automatic Database Diagnostic Monitor (ADDM)

The tools specific for tuning SQL code will be presented in Chapter 4, Optimizing SQL Code.

Let's spend some time on Oracle Enterprise Manager (OEM). It is a graphical web-based application, and it is the main tool the Oracle DBA uses to configure and monitor the database in non-console mode.

In OEM, there is a performance palette which presents a dashboard with many graphs and indicators, all updated live. At the bottom of the page, there are additional links to the most common tasks related to performance tuning.


OEM itself is not a performance tuning tool, but it's just a front-end to the tools and functions in the previously mentioned list. It's a good idea to familiarize yourself with OEM and its user interface. However, if a DBA knows what happens in the backstage, he/she will be able to do the right thing with any tool, and he/she will not feel lost if his/her favorite tool or GUI isn't up and running (and sometimes this is a real scenario at the customer site).

See also

  • Acquiring data using Data dictionary and dynamic performance views recipes in this chapter

  • Appendix B, Tools and Packages


Exploring the example database

In this recipe, we will prepare a database to use for our examples.

Getting ready

We need an Oracle Database 11gR2 system up and running to create our database. The host system could be a UNIX/Linux or Windows physical or virtual machine. If you want to use a virtual machine, be sure to follow the minimum CPU and memory requirements for the Oracle installation.

If you have installed the database software along with the Create Database option, then you have already set up a database with the necessary schema installed.

How to do it...

We will use the default demo database installed by the default OLTP template of Oracle Database Configuration Assistant (DBCA) for all our examples.


You can find the official Oracle Database Installation Guide 11gR2 for Linux at

  1. Log on to the Operating System as a member of the administrative group, authorized to install Oracle software and to create and run database instances.

  2. Launch DBCA (for Windows users: Start | Programs | Oracle – home_name | Configuration and Migration Tools | Database Configuration Assistant) for *nix systems enter the following command at system prompt:

    $ dbca

    Please note that the dbca executable is by default in the $ORACLE_HOME/bin directory.

  3. A welcome screen is shown. Click Next.

  4. You are presented with some options. Select the first, namely Create a database, and click Next.

  5. You are presented a list of database templates. Choose the first, namely General purpose / OLTP, and click Next.

  6. You are asked for the global database name and SID; enter TESTDB in the global database name (the SID should be set accordingly) and click Next.

  7. In the next screen—shown in the following screenshot—leave the default options selected (OEM configuration). If you wish, you can enable e-mail notifications, checking the corresponding flag and entering the SMTP server to use (something like or and the e-mail address where the alerts will be delivered. Click Next to go to the next screen.

  8. Choose to use the same password for all administrative accounts, enter the password you want to use twice, and click Next. If you are advised that the password you entered is weak (not responding to the minimum complexity requirements) you can ignore the message and go on. Please note that for a production database these are very bad choices, but we are installing a demo database for testing purposes only and don't want to bother with security issues.

  9. In the next screen, leave the default option for the files position (Use Database File Locations from Template) and click Next.

  10. Leave the default options for the flash recovery area and click Next.

  11. In the next screen, check the Sample Schemas flag and click Finish.

  12. You are presented with the operations summary. Click OK and wait until the database creation process is finished.

  13. At the end of the creation process, we have to unlock the accounts created. In the summary form, there is a Password Manager button; click on it, and you will be presented with the list of accounts created.

  14. Find the following accounts: BI, HR, IX, OC, OE, PM, SH and uncheck the second column (unlocking them). Insert the password for the accounts in the last two columns, setting them the same as the account name.


    You can click on the username column to sort accordingly.

    Don't use sample schemas or passwords the same as the username in production databases!

Now our TESTDB database is ready for experimenting.

How it works...

Oracle DBCA lets us create a database using predefined templates. For our examples, we will use the default example schemas provided by Oracle (which are installed in the EXAMPLE tablespace).

The sample schemas are HR (Human Resources), OE (Order Entry), OC (Order Catalog), PM (Product Media), IX (Information eXchange), SH (Sales History), and BI (Business Intelligence). We will use mostly HR and SH schemas.

There's more...

If we want to reset the sample schemas to the initial state, we can use the script mksample.sql located in the $ORACLE_HOME/demo/schema/ directory. This script requires eleven parameters, with the following syntax:

SQL>@?/demo/schema/mksample systempwd syspwd hrpwd oepwd pmpwd ixpwd shpwd bipwd default_tablespace temp_tablespace log_file_directory/


Please note that the log_file_directory is an already existing folder and also the path must be terminated by a slash.

Our database—assuming test as the system and system password—will be reset with the following statement:

SQL>@?/demo/schema/mksample test test hr oe pm ix sh bi EXAMPLE TEMP testlog/


Please note that in the default installation of Oracle Database 11gR2 Enterprise Edition the mksample.sql script is not present.

You can find it in the Companion CD.


Acquiring data using a data dictionary and dynamic performance views

In the Oracle database, there are many views which can be queried to acquire data about the database state. They are divided into data dictionary views, with a name similar to DBA_*, and dynamic performance views, named something similar to V$_*.

Getting ready

When we use a standard template in Oracle DBCA to create a database, both data dictionary views and dynamic performance views are in place after database creation. If we prefer to use our own scripts to create the database, we need to launch at least the catalog.sql and catproc.sql scripts to populate the data dictionary with the views we need. These scripts are located in the rdbms/admin subdirectory of the Oracle Home directory.

To collect timing information in the dynamic performance views, we have to set the parameter TIMED_STATISTICS=TRUE in the init.ora file of our database instance. We can also accomplish this requirement with the following SQL statement:



Please note that the default value for the TIMED_STATISTICS parameter is already TRUE and that there isn't any perceptible performance gain in changing this default value to FALSE.

How to do it...

We can query the data dictionary views and the dynamic performance views like any other view in the database, using SQL statements.

We can also query DBA_VIEWS, which is a data dictionary view showing other views in the database:

select view_name from dba_views
  where view_name like 'DBA%' order by 1

We can query the V$FIXED_TABLE view to get a list of all the V$ dynamic performance views and X$ tables:

select name from V$FIXED_TABLE order by 1;


You can find the definition of each view we will use in the book in Appendix A, Dynamic Performance Views

How it works...

Data dictionary views are owned by the user SYS and there is a public synonym for each of them. They expose data about database objects, for example, tables and indexes.

In Oracle Database 11gR2 Enterprise Edition, the database installed from the DBCA template will have more than 800 data dictionary views available. We will present the data dictionary views that we need in our recipes when we have to query them.

Even dynamic performance views are owned by the user SYS; they are synonyms to V_$* views. Those views are based on X$ tables, which are undocumented structures populated at instance start-up. The data dictionary view contains two kinds of data, namely, fields that store information on the characteristics of the object, and other fields that collect information dynamically from object usage.

For example, in the DBA_TABLES there are fields about the physical structure of the table (such as TABLESPACE_NAME, PCT_FREE, INITIAL_EXTENT) and other fields which expose statistics on the table contents (such as NUM_ROWS, AVG_SPACE, AVG_ROW_LEN).

To collect these statistical data we have to perform the ANALYZE statement. For a table, we will execute the following statement:


To speed up and automate the analysis of many objects, we can use DBMS_UTILITY.analyze_schema or DBMS_UTILITY.analyze_database to analyze all the objects in a schema in the first case, or in the database in the latter. To analyze the objects of the HR schema, we will execute the following statement:

EXEC DBMS_UTILITY.analyze_schema('HR','COMPUTE');


For both the ANALYZE command and the DBMS_UTILITY functions, we have two choices, which are either to compute the statistics or to estimate these values based on the analysis of a restricted set of data. When ESTIMATE is chosen, we have to specify the number of rows to use for the sample or a percentage.

Oracle advises us to use another method to compute statistics, namely, the DBMS_STATS package, which allows deleting statistics, exporting, importing, and gathering statistics in parallel. The following statement analyses the schema HR:

EXEC DBMS_STATS.gather_schema_stats('HR');


ANALYZE and the use of DBMS_UTILITY illustrated earlier are supported for backward compatibility only; use the package DBMS_STATS to collect statistics.

Similarly, we can gather statistics on tables, indexes, or database. Even with DBMS_STATS we can use the ESTIMATE method, as in the first of the following examples:

EXEC DBMS_STATS.gather_database_stats(estimate_percent => 20);
EXEC DBMS_STATS.gather_table_stats('HR', 'EMPLOYEES');
EXEC DBMS_STATS.gather_index_stats('HR', 'EMP_JOB_IX');

Using the DBMS_STATS package we can also delete statistics, as shown:

EXEC DBMS_STATS.delete_table_stats('HR', 'EMPLOYEES');

To transfer statistics between different databases, we have to use a statistics table, as shown in the following steps:

  1. Create the statistics table on the source database.

  2. Export the statistics from the data dictionary to the statistics table.

  3. Move the statistics table (Export/Import, Datapump, Copy) to the target database.

  4. Import the statistics from the statistics table to the data dictionary.

  5. Drop the statistics table.

The corresponding statements to execute on the source database are as follows:


With these statements we have created the statistics table MY_STAT_TABLE in the DBA_SCHEMA and populated it with data from the APP_SCHEMA (for example, HR).

Then we transfer the MY_STAT_TABLE to the target database; using the export/import command line utilities we export the table from source database and then import the table into the target database, in which we execute the following statements:


In the example, we have transferred statistics about the entire schema APP_SCHEMA. We can choose to transfer statistics for the entire database, a table, an index, or a column, using the corresponding import_* and export_* procedures of the DBMS_STATS package.

There's more...

The COMPUTE STATISTICS and ESTIMATE STATISTICS parameters of the ANALYZE command are supported only for backward compatibility by Oracle. However, there are other functionalities of the command that allow validating the structure of a table, index, cluster, materialized views, or to list the chained or migrated rows:


The first statement validates the structure of the EMPLOYEES table, while the second command lists the chained rows of the same table into the CHAINED_ROWS table (created with the script utlchain.sql or utlchn1.sql.)

See also

  • Avoiding row chaining in Chapter 3, Optimizing Storage Structures


Analyzing data using Statspack reports

Statspack was first introduced in Oracle Database 8i R8.1.6. We shall now look at how to use this tool.

Getting ready

To use Statspack, we have to set up a tablespace to store its structures; if we don't, in the installation process we have to choose an already existing tablespace—SYSAUX is the tablespace proposed by default. To create the tablespace, we will use the following command (with the necessary change in the datafile parameter, according to the platform used and the database location):


To collect timing information in the dynamic performance views, we have to set the parameter TIMED_STATISTICS=TRUE, as shown in the recipe about the dynamic performance view.

How to do it...

Follow these steps to make use of the Statspack tool:

  1. Connect to the database with a user with the SYSDBA privilege and run the spcreate.sql script from the $ORACLE_HOME/rdbms/adminr directory. This script will ask for a password to assign to the PERFSTAT user.

  2. We will then be asked for the tablespace to use. Select the previously created tablespace by entering its name (STATSPACK). When the script asks for the temporary tablespace just press Enter to use the default temporary tablespace defined in the system.

  3. The script will create the user PERFSTAT, identified by the password provided, and all the objects needed to run the tool.

    After the tool is created, we can collect statistics by executing the following procedure:


    With this simple command, we have created a snapshot in the Statspack table.

  4. When we have at least two snapshots, we can create a report using a pair of them. To do so, we will execute the spreport.sql script.

    The script will show us the completed snapshots and we will be asked for the ID of the two which we want to compare.

  5. The script will ask for the name to give to the report—the default will be sp_id1_id2, where id1 and id2 are the beginning and ending snapshots chosen in the previous step.

At the end of the process, we will find our Statspack report.

How it works...

The spcreate.sql script internally launches the spcusr.sql, spctab.sql, and spcpkg.sql scripts. For every script, after the execution, we will find a corresponding file with the extension changed to .lis with the spool of the actions performed. In case anything goes wrong, we can launch the spdrop.sql script to rollback the actions performed by spcreate.sql.

A snapshot of Statspack contains information from the dynamic performance views. As these views are emptied at database start-up, it makes no sense to elaborate Statspack performance reports with the use of snapshots taken before and after a database shutdown.

The tables used to collect the data have names which start with STATS$, and are based on the corresponding V$ dynamic performance views. For example, the table STAT$DB_CACHE_ADVICE has the same columns of the view V$DB_CACHE_ADVICE, with three columns added in front of them, SNAP_ID, DBID, INSTANCE_NUMBER, which are used to identify the snapshot, the database, and the instance respectively.


If you want to use Statspack in an Oracle Real Application Cluster (RAC) environment, you have to launch STATSPACK.snap connecting to every instance you want to gather data from.

The report is divided into several sections:

  • General information about the database instance and the snapshots used

  • Cache sizes (buffer cache, shared pool, and log buffer)

  • Load profile (instance events per second and per transaction)

  • Instance efficiency indicators (buffer cache and shared pool statistics)

  • Top five timed events, showing the first five events sorted by total wait time in seconds

  • Host CPU and Instance CPU, showing the load on the CPU

  • Virtual Memory Paging and Memory Statistics

  • Wait events, foreground, background, and both foreground and background grouped together

  • SQL ordered by different criteria, by CPU, by elapsed time for DB, by gets, by executions, by parse calls, by sharable memory, by version count

  • Instance activity statistics

  • Tablespace and file I/O

  • Memory, buffer pool, and PGA statistics

  • Latch activity

  • Dictionary cache statistics

  • Library cache activity

  • SGA activity

  • init.ora parameters

There's more...

We can configure Statspack to collect different amounts of data and to produce a report on specific SQL; we wish to automate snapshot collection, too.

Collecting different amounts of data

We can configure Statspack to collect more or less data. The LEVEL parameter can be used to instruct the tool about the kind of information we want to store in the snapshot. The following table summarizes the available levels (the default level is 5):




General performance statistics


Additional data: High resource usage SQL statements


Additional data: SQL Plans and SQL Plan usage information for high resource usage SQL statements


Additional data: Segment level statistics including logical and physical reads, row locks, and so on


Additional statistics: Parent and Child latches

We can use a different level parameter for a single snapshot, passing the corresponding level to the STATSPACK.snap procedure:

EXEC STATSPACK.snap(i_snap_level=>10);

If we want our selection made permanent for subsequent snapshots, we add another parameter to the procedure:

EXEC STATSPACK.snap(i_snap_level=>6, i_modify_parameter=>'true');

If we want to change the level of the snapshots without taking one, we will use the following statement:

EXECUTE STATSPACK.modify_statspack_parameter(i_snap_level=>6);

Producing a report on a specific SQL

Statspack provides another script, sprepsql.sql, which allows us to elaborate a more detailed report on a specific SQL statement.

If we find a statement in the Statspack report that we want to investigate deeper, we can launch this script, indicating the beginning and ending snapshots, and the "Old Hash Value" (a pre-10g memory) of the SQL statement on which we want to elaborate the report.

If in our Statspack report (elaborated between the snapshots identified by 2 and 3) we have a row in the SQL ordered by CPU section that is similar to the one shown in the following screenshot:

And we want to investigate the related statement, we can launch the sprepsql.sql script and indicate ID 2 as begin, ID 3 as end, and 3787177051 as Old Hash Value.

The script will ask for the filename and will then produce a detailed report for the statement analyzed.

Automating snapshot generation

We can automate snapshot generation in various ways. Besides using a Unix cron job or a Windows Scheduled Task, we can instruct the database to capture the snapshots with a simple job. There is the spauto.sql script in the $ORACLE_HOME/rdbms/admin directory to set up an hourly snapshot. The script uses DBMS_JOB to schedule the snapshots.

Statspack maintenance

We can purge the no longer needed snapshots with the use of the spurge.sql script, indicating the ID of the first and the last snapshot to delete. Before deleting the data, we may want to export the PERFSTAT schema.

The sptrunc.sql script, instead, deletes all the data collected. All the scripts are in the $ORACLE_HOME/rdbms/admin directory.

To completely uninstall Statspack, there is the already mentioned spdrop.sql script, which has to be executed with SYSDBA privileges.


Diagnosing performance issues using the alert log

To diagnose certain performance issues, even the alert log can be used successfully.

Getting ready

There are some parameters to look at in the init.ora file of our database instance.

The parameter BACKGROUND_DUMP_DEST indicates the directory in which the alert log is located. If the parameter LOG_CHECKPOINTS_TO_ALERT is set to TRUE, we will find even checkpoint information in the alert log. By default this parameter is set to FALSE.

Before starting, we can issue the following command:


This writes checkpoint information to the alert log and shows the directory in which we will find the alert log file (named alert_<instance_name>.log).

How to do it...

The following steps will demonstrate how to use the alert log:

  1. In the alert log, we can find information like the following:

    Sun Sep 19 12:25:26 2010
    Thread 1 advanced to log sequence 5 (LGWR switch)
    Current log# 2 seq# 5 mem# 0: D:\APP\ORACLE\ORADATA\TESTDB\REDO02.LOG

    This informs us of a log-switch.

  2. We can then verify the time between log switches.

    If we have set the parameter LOG_CHECKPOINTS_TO_ALERT to TRUE, we will also see lines like these in the alert log:

    Sat Sep 25 20:18:01 2010
    Beginning global checkpoint up to RBA [0x16.fd.10], SCN: 1296271
    Completed checkpoint up to RBA [0x16.fd.10], SCN: 1296271

    Then we can calculate checkpoint performance.

How it works...

The database writes information on the alert log about log switches and checkpoints. We can inspect the alert log to diagnose a possible problem with log files.

There's more...

We can force a log switch by using the following command:


A checkpoint can be forced by using the following statement:


See also

  • We will see the use of SQL_TRACE and TKPROF to generate trace files and the corresponding report over SQL activity of a particular session in Tracing SQL activity with SQL Trace and TKPROF in Chapter 4, Optimizing SQL Code


Analyzing data using Automatic Workload Repository (AWR)

With Oracle Database 10g, Automatic Workload Repository (AWR) was introduced. It is a tool that extends the key concepts of Statspack.

In this recipe, we will create a manual snapshot, a baseline, and some reports.

Getting ready

To use AWR, the STATISTICS_LEVEL parameter of the init.ora file must be set to the value TYPICAL or ALL.


With the default setting TYPICAL, all the statistics needed for self-management functionalities are collected, providing best overall performance. Using the parameter ALL the database will collect all the statistics included in the TYPICAL settings, as well as timed operating system statistics and row source execution statistics.

We can change the parameter online with the following statement without shutting down the database:


How to do it...

The following steps demonstrate use of AWR:

  1. To make a manual snapshot using AWR, we use the following stored procedure:

    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();

    With the default settings in place, AWR creates a snapshot every hour, and the data collected are stored for seven days.

  2. To modify the interval or the grace period of the snapshots, we can use the modify_snapshot_settings procedure, as shown:

    EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 30);
    EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 21600);
  3. In AWR, we can also create a baseline to compare performances. A baseline is a set of snapshots which will be held to compare with the same kind of data in the future.

    We could have, for example, a baseline for the daily transactional work and a baseline for a batch job or a peak (quarter end). We can define a baseline indicating the start and end snapshots to be used, and we can name it:

    EXEC DBMS_WORKLOAD_REPOSITORY.create_baseline(Start_snap_id => 1, end_snap_id => 11, baseline_name => 'Friday off-peak');
  4. To generate a report, we will use the awrrpt.sql script, located in the $ORACLE_HOME/rdbms/admin folder. The script will ask to choose the output format (text or HTML) and the number of days to use to filter the snapshots.

  5. Then they will be presented the list of the snapshots, according to the parameter chosen in the previous step, and we are asked for the first and the last snapshot to be used. The last question is about the name of the file to generate the output to. The report generated is very similar to the Statspack report.

How it works...

As with Statspack, even AWR collects data and statistics from the database and stores them in tables. With AWR the concept of baseline is introduced.

The baselines can be fixed, moving window, or templates. The baseline we have defined in the previous example is fixed, because it corresponds to a specific time period in the past. The moving windows baseline corresponds to the AWR data within the entire retention period, and it's useful when used with adaptive thresholds. The baseline templates, instead, are created for a future time period, and can be single or repeating.

In the first statement of step 2, we have set the interval between snapshots to 30 minutes; in the second statement the retention period of the snapshots collected is set to 21600 minutes, which corresponds to 15 days.

The adaptive thresholds just mentioned consent to adapt the thresholds of a performance metric according to the workload of the system, eliminating false alerts. From Oracle 11g, adaptive thresholds are adjusted based on different workload patterns (for example, a system used for OLTP in daytime and for batch jobs at night) automatically recognized by the database.

We have created a report in the previous example by using the awrrpt.sql script. There are other reports available, generated by a corresponding script in the same folder; for example, awrrpti.sql is the same as awrrpt.sql, but for a specific database instance. awrsqrpt.sql generates a report for a particular SQL statement, like the script sprepsql.sql for Statspack. The corresponding script awrsqrpti.sql prepares the same report for a specific database instance.

There are also compare period reports, which allow us to compare not two snapshots but two AWR reports. If we have a database which performs well in a certain period, and we experiment a lack of performance in another period, we can elaborate two reports for the first and the latter period, and then compare the reports among them, to point out the differences and try to identify the issue.

For example, in step 4, we have created a baseline based on the snapshots with IDs from 1 to 11, and we name it "Friday off-peak".

The timespan of the two reports we are comparing isn't important, because AWR normalizes the data according to the different timeframe.

Compare period reports can be launched from Oracle Enterprise Manager or using the script awrddrpt.sql (the script awrddrpti.sql to concentrate the result on a single instance).

There's more...

We can specify the adaptive thresholds as a percentage of the maximum value observed in the moving window baseline, or as a statistical percentile, ranging from 0.95 to 0.9999—from five observations expected to exceed the value in 100 to 1 observation in 10,000.


Analyzing data using Automatic Database Diagnostic Monitor (ADDM)

In this recipe, we present the Automatic Database Diagnostic Monitor, a tool which analyzes the data collected by AWR to diagnose the cause of a performance problem, providing advice on how to solve the issue.

Getting ready

ADDM is enabled by default in Oracle Database 11g; it depends upon two configuration parameters of the init.ora file, STATISTICS_LEVEL and CONTROL_MANAGEMENT_PACK_ACCESS. The value for these parameters should be TYPICAL or ALL for the former and DIAGNOSTIC or DIAGNOSTIC+TUNING for the latter. To show the current parameter values, we can use the following statement:


While to set the parameters we can use the following commands:


We are now ready to diagnose a problem using ADDM.

How to do it...

The following steps will demonstrate how to use ADDM:

  1. To run the ADDM in Database mode (all instances of the database will be analyzed), we will use the following statement where the parameters 3 and 5 in these steps are the numbers identifying the beginning and ending snapshots to be used:

    VAR task_name VARCHAR2(30);
      :task_name := 'Report for 3 to 5';
      DBMS_ADDM.ANALYZE_DB (:task_name, 3, 5);
  2. To run the ADDM in Instance mode (a single instance of the database will be analyzed), we will use the following statement:

    VAR task_name VARCHAR2(30);
      :task_name := 'Report for 3 to 5 inst. 1';
      DBMS_ADDM.ANALYZE_INST (:task_name, 3, 5, 1);
  3. To run the ADDM in Partial mode (a subset of all database instances will be analyzed), we will use the following statement:

    VAR task_name VARCHAR2(30);
      :task_name := 'Custom for 3 to 5 inst. 1,2';
      DBMS_ADDM.ANALYZE_INST (:task_name, '1,2', 3, 5);
  4. To view the results we will query the DBMS_ADDM.GET_REPORT function, passing the name of the task used in generating the reports:

    SELECT DBMS_ADDM.get_report('Report for 3 to 5') FROM DUAL;
    SELECT DBMS_ADDM.get_report('Report for 3 to 5 inst. 1') FROM DUAL;
    SELECT DBMS_ADDM.get_report('Custom for 3 to 5 inst. 1,2') FROM DUAL;

    Each line in the previous code will display the corresponding ADDM report.

How it works...

Automatic Database Diagnostic Monitor runs automatically every time a new snapshot is taken by AWR (by default every hour), and the corresponding report is built comparing the last two snapshots available, so we have an ADDM report every hour.

With the statement presented, we can run a report between snapshots to identify possible problems. The reports can be built, for a Real Application Cluster configuration, with three analysis models: database, instance, and partial. In non-RAC databases, only instance analysis is possible because there is only one instance of the database.

We can see the reports with SQL*Plus using the DBMS_ADDM.GET_REPORT function, which returns a CLOB containing the report (80-columns formatted), or we can use Oracle Enterprise Manager to view the reports generated both in automatic or manual mode. In OEM, we can view ADDM findings in the homepage in the Diagnostic Summary information. We can choose Advisor Central on the bottom of the page to see a list of the ADDM reports available, as shown in the following screenshot:

Clicking on the name link in the previous list we can view the corresponding report; in the following screenshot, we can see an example of an ADDM report viewed through OEM:

There's more...

The parameter DBIO_EXPECTED influences the ADDM analysis of I/O performance, because it describes the expected I/O subsystem performance, measuring the average time needed to read a single database block. The default value of the parameter is 10 milliseconds, corresponding to the average time of common hard disks. Please note that this measure includes the seek time.

If our I/O subsystem is significantly slower or faster, we may end up with possible false alerts or no alerts at all. We can adjust the parameter issuing the following statement:


The numeric value is the time expressed in microseconds.

See also

  • Analyzing data using Automatic Workload Repository (AWR) in this chapter


A working example

In this recipe we will present a simple example of a performance tuning session, applying the recipes seen earlier.

Getting ready

The example is based on the SH schema. Be sure Statspack is installed, as presented in an earlier recipe.

How to do it...

The following steps demonstrate a simple example using the SH schema:

  1. We assume the user PERFSTAT with the password PERFSTAT and the user SH with the password SH. The TESTDB database is the default instance.

  2. Launch SQL*Plus and connect to the SH schema:

    $ sqlplus SH/SH
  3. Create the package Chapter1:

      PROCEDURE Workload;
      PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE); 
      PROCEDURE Workload IS
       FOR i in 1 .. 50000
       END LOOP;
      END Workload;
      PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
        l_stmt VARCHAR2(2000);
        l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' || TO_CHAR(CUSTID);
        EXECUTE IMMEDIATE l_stmt;
      END Foo;
  4. Now we create the initial snapshot:

    EXEC statspack.snap;
  5. Execute the test workload:

    EXEC Chapter1.Workload;
  6. Now we can elaborate the end snapshot:

    EXEC statspack.snap;
  7. Finally we can launch the report creation:

  8. When asked, select the last two snapshots created to produce the Chapter1.lst report (naming the report accordingly).

How it works...

In this simple example, the stored procedure Foo inside the package Chapter1 is executed 50,000 times to query the SALES table. We have not used bind variables, and the Statspack report reflects this performance issue:

In the highlighted section of the Statspack report, we can see that only 2.92 percent of parses have been "soft", because the cursor_sharing parameter is set to EXACT and we are not using bind variables.

There's more...

To solve this issue, we can:

  • Change the CURSOR_SHARING parameter to SIMILAR

  • Recode the Foo procedure, introducing bind variables

In the first case, we have to execute the following statement:


Now we can recreate the snapshots:

EXEC statspack.snap;
EXEC Chapter1.Workload;
EXEC statspack.snap;

And finally, we launch the report creation:


The newly created report presents a significant change:

Now the Soft Parse is 97.84 percent.

We can recode the procedure as well; let's rollback the change in CURSOR_SHARING:


And let's alter the Foo procedure:

   FOR i in 1 .. 50000
  END Workload;
  PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS
    l_stmt VARCHAR2(2000);
    l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id';
  END Foo;

Let's launch the snapshots and the report:

EXEC statspack.snap;
EXEC Chapter1.Workload;
EXEC statspack.snap;

The newly created report presents a result similar to the previous execution:

There is now a Soft Parse of 99.20 percent.

In this simple example, we have seen how to diagnose a simple problem using Statspack; as an exercise, try to use the other tools presented using the same test case.


To use AWR and ADDM take a manual snapshot before and after running the Workload procedure.

See also

  • Using bind variables in Chapter 4, Optimizing SQL Code

  • Minimizing latches using bind variables and Tuning resources to minimize latch contention in Chapter 11, Tuning Contention

About the Author
  • Ciro Fiorillo

    Ciro Fiorillo is an IT professional and consultant with experience of more than a decade in different roles (developer, analyst, DBA, project manager, data and software architect) among software industries. He has worked on different technologies and architectures, such as Oracle, SQL Server, Delphi, C# and .NET Framework, C/C++, Java, PHP, COBOL, Fortran, and Tibco. Ciro is currently employed as Lead Software and Data Architect with FinWin Srl, a software house specializing in banking and loans applications. As a freelancer he writes articles for websites and printed magazines about software and computing, participates in workshops, and teaches C++ and Fortran parallel programming with Intel Software tools. Ciro can be reached at

    Browse publications by this author
Latest Reviews (1 reviews total)
Oracle Database 11gR2 Performance Tuning Cookbook
Unlock this book and the full library FREE for 7 days
Start now