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:
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
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.
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.
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
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.
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
to the values
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
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.
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.
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!
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.
To solve a performance problem on the database, we need to follow these steps:
Elaborate a baseline.
Investigate the problem.
Assume a solution, a test case, and a rollback strategy.
Implement the solution and test for correctness.
Test the solution.
Compare the results.
If the results are not as good as expected, iterate the process.
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.
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 schema and database with
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).
Acquiring data using Data dictionary and dynamic performance views recipes in this chapter
Appendix B, Tools and Packages
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.
You can find the official Oracle Database Installation Guide 11gR2 for Linux at http://download.oracle.com/docs/cd/E11882_01/install.112/e16763/toc.htm.
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.
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:
A welcome screen is shown. Click Next.
You are presented with some options. Select the first, namely Create a database, and click Next.
You are presented a list of database templates. Choose the first, namely General purpose / OLTP, and click Next.
You are asked for the global database name and SID; enter
TESTDBin the global database name (the SID should be set accordingly) and click Next.
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
smtp.yourISP.com) and the e-mail address where the alerts will be delivered. Click Next to go to the next screen.
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.
In the next screen, leave the default option for the files position (Use Database File Locations from Template) and click Next.
Leave the default options for the flash recovery area and click Next.
In the next screen, check the Sample Schemas flag and click Finish.
You are presented with the operations summary. Click OK and wait until the database creation process is finished.
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.
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.
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
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
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.
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/
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
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
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:
ALTER SYSTEM SET TIMED_STATISTICS = TRUE SCOPE = BOTH;
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
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
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
INITIAL_EXTENT) and other fields which expose statistics on the table contents (such as
To collect these statistical data we have to perform the
ANALYZE statement. For a table, we will execute the following statement:
ANALYZE TABLE hr.employees COMPUTE STATISTICS;
To speed up and automate the analysis of many objects, we can use
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:
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
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');
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:
Create the statistics table on the source database.
Export the statistics from the data dictionary to the statistics table.
Move the statistics table (Export/Import, Datapump, Copy) to the target database.
Import the statistics from the statistics table to the data dictionary.
Drop the statistics table.
The corresponding statements to execute on the source database are as follows:
EXEC DBMS_STATS.create_stat_table('DBA_SCHEMA', 'MY_STAT_TABLE'); EXEC DBMS_STATS.export_schema_stats('DBA_SCHEMA', 'MY_STAT_TABLE', NULL, 'APP_SCHEMA');
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,
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:
EXEC DBMS_STATS.import_schema_stats('APP_SCHEMA', 'MY_STAT_TABLE', NULL, 'DBA_SCHEMA'); EXEC DBMS_STATS.drop_stat_table('DBA_SCHEMA', 'MY_STAT_TABLE');
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
export_* procedures of 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:
ANALYZE TABLE employees VALIDATE STRUCTURE; ANALYZE TABLE employees LIST CHAINED ROWS INTO CHAINED_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
Avoiding row chaining in Chapter 3, Optimizing Storage Structures
Statspack was first introduced in Oracle Database 8i R8.1.6. We shall now look at how to use this tool.
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):
CREATE TABLESPACE statspack DATAFILE '/u01/oracle/db/STATSPACK.DBF' SIZE 200 M REUSE EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K SEGMENT SPACE MANAGEMENT AUTO PERMANENT ONLINE;
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.
Follow these steps to make use of the Statspack tool:
Connect to the database with a user with the
SYSDBAprivilege and run the
spcreate.sqlscript from the
$ORACLE_HOME/rdbms/adminrdirectory. This script will ask for a password to assign to the
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.
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.
When we have at least two snapshots, we can create a report using a pair of them. To do so, we will execute the
The script will show us the completed snapshots and we will be asked for the ID of the two which we want to compare.
The script will ask for the name to give to the report—the default will be
id2are the beginning and ending snapshots chosen in the previous step.
At the end of the process, we will find our Statspack report.
spcreate.sql script internally launches the
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
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,
INSTANCE_NUMBER, which are used to identify the snapshot, the database, and the instance respectively.
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
Dictionary cache statistics
Library cache activity
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.
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
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:
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
Old Hash Value.
The script will ask for the filename and will then produce a detailed report for the statement analyzed.
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.
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.
sptrunc.sql script, instead, deletes all the data collected. All the scripts are in the
To completely uninstall Statspack, there is the already mentioned
spdrop.sql script, which has to be executed with
To diagnose certain performance issues, even the alert log can be used successfully.
There are some parameters to look at in the
init.ora file of our database instance.
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
Before starting, we can issue the following command:
ALTER SYSTEM SET LOG_CHECKPOINTS_TO_ALERT = TRUE; SHOW PARAMETER BACKGROUND_DUMP_DEST
This writes checkpoint information to the alert log and shows the directory in which we will find the alert log file (named
The following steps will demonstrate how to use the alert log:
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.
We can then verify the time between log switches.
If we have set the parameter
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.
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.
We can force a log switch by using the following command:
ALTER SYSTEM SWITCH LOGFILE;
A checkpoint can be forced by using the following statement:
ALTER SYSTEM CHECKPOINT;
We will see the use of
TKPROFto 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
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.
To use AWR, the
STATISTICS_LEVEL parameter of the
init.ora file must be set to the value
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:
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;
The following steps demonstrate use of AWR:
To make a manual snapshot using AWR, we use the following stored procedure:
With the default settings in place, AWR creates a snapshot every hour, and the data collected are stored for seven days.
To modify the interval or the grace period of the snapshots, we can use the
modify_snapshot_settingsprocedure, as shown:
EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 30); EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 21600);
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');
To generate a report, we will use the
awrrpt.sqlscript, located in the
$ORACLE_HOME/rdbms/adminfolder. The script will ask to choose the output format (text or HTML) and the number of days to use to filter the snapshots.
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.
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).
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.
ADDM is enabled by default in Oracle Database 11g; it depends upon two configuration parameters of the
CONTROL_MANAGEMENT_PACK_ACCESS. The value for these parameters should be
ALL for the former and
DIAGNOSTIC+TUNING for the latter. To show the current parameter values, we can use the following statement:
SHOW PARAMETER STATISTICS_LEVEL SHOW PARAMETER CONTROL_MANAGEMENT_PACK_ACCESS
While to set the parameters we can use the following commands:
ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL; ALTER SYSTEM SET CONTROL_MANAGEMENT_PACK_ACCESS = 'DIAGNOSTIC+TUNING';
We are now ready to diagnose a problem using ADDM.
The following steps will demonstrate how to use ADDM:
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); BEGIN :task_name := 'Report for 3 to 5'; DBMS_ADDM.ANALYZE_DB (:task_name, 3, 5); END;
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); BEGIN :task_name := 'Report for 3 to 5 inst. 1'; DBMS_ADDM.ANALYZE_INST (:task_name, 3, 5, 1); END;
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); BEGIN :task_name := 'Custom for 3 to 5 inst. 1,2'; DBMS_ADDM.ANALYZE_INST (:task_name, '1,2', 3, 5); END;
To view the results we will query the
DBMS_ADDM.GET_REPORTfunction, 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.
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:
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:
EXEC DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER('ADDM', 'DBIO_EXPECTED', 12000);
The numeric value is the time expressed in microseconds.
In this recipe we will present a simple example of a performance tuning session, applying the recipes seen earlier.
We assume the user
PERFSTATwith the password
PERFSTATand the user
SHwith the password
TESTDBdatabase is the default instance.
Launch SQL*Plus and connect to the
$ sqlplus SH/SH
Create the package
CREATE OR REPLACE PACKAGE Chapter1 AS PROCEDURE Workload; PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE); END; / CREATE OR REPLACE PACKAGE BODY Chapter1 AS PROCEDURE Workload IS BEGIN FOR i in 1 .. 50000 LOOP Foo(i); END LOOP; END Workload; PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS BEGIN DECLARE l_stmt VARCHAR2(2000); BEGIN l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = ' || TO_CHAR(CUSTID); EXECUTE IMMEDIATE l_stmt; END; END Foo; END; /
Now we create the initial snapshot:
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap;
Execute the test workload:
CONNECT SH/SH EXEC Chapter1.Workload;
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap;
Finally we can launch the report creation:
When asked, select the last two snapshots created to produce the
Chapter1.lstreport (naming the report accordingly).
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.
To solve this issue, we can:
Fooprocedure, introducing bind variables
In the first case, we have to execute the following statement:
ALTER SYSTEM SET CURSOR_SHARING = SIMILAR SCOPE=MEMORY;
Now we can recreate the snapshots:
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap; CONNECT SH/SH EXEC Chapter1.Workload; CONNECT PERFSTAT/PERFSTAT 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
ALTER SYSTEM SET CURSOR_SHARING=EXACT SCOPE = MEMORY;
And let's alter the
CREATE OR REPLACE PACKAGE BODY Chapter1 AS PROCEDURE Workload IS BEGIN FOR i in 1 .. 50000 LOOP Foo(i); END LOOP; END Workload; PROCEDURE Foo(CUSTID IN sh.sales.cust_id%TYPE) IS BEGIN DECLARE l_stmt VARCHAR2(2000); BEGIN l_stmt := 'SELECT * FROM sh.sales s WHERE s.cust_id = :p_cust_id'; EXECUTE IMMEDIATE l_stmt USING CUSTID; END; END Foo; END; /
Let's launch the snapshots and the report:
CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap; CONNECT SH/SH EXEC Chapter1.Workload; CONNECT PERFSTAT/PERFSTAT EXEC statspack.snap; SQL>@?/RDBMS/ADMIN/SPREPORT.SQL
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.