Chapter 3. Ease the Chaos with Performance Management

There are normally numerous applications running in a data center. These applications often impact the database in different ways at different times, and no application or database ever stays static; there is continuous change.

The data in the database changes as time goes by with more volumes of data being added or with continuous updates, deletions, and inserts. Application users also change in terms of more and more users and the application code itself changes with periodic application upgrades that add more functionality to the applications.

Consequently, with all these changes, the database performance is bound to deteriorate sooner or later and the DBA will always be forced to look into performance issues at the database layer.

As per the IOUG Survey on Database Manageability in 2011, 90 percent of DBAs mentioned that they had experienced unplanned downtime due to database changes not having been properly tested, and as a result over...

Laying the foundation

The foundation of the diagnostics capabilities is the built-in repository that is installed with every Oracle database (from version 10g onwards). This is known as the Automatic Workload Repository (AWR), and is stored in the SYSAUX Tablespace. Once every hour, by default, the database takes a snapshot of its workload and statistical information, and stores it in the AWR repository. This data is saved for 8 days by default.

Different classes of statistical data are stored: base statistics (such as physical reads), SQL statistics (such as reads per SQL statement), or different metrics (such as physical reads per second). Thus, historical information about the performance of the database is captured automatically, and the DBA does not have to set up any home-grown scripts or tables to do the capture, as in the past. The AWR is also self-managing and does not have to be micromanaged by the DBA. The space requirements are automatically managed; based on the retention interval...

Top activity

At any point in time, it is possible to go to Performance | Top Activity and display a view of the top SQL statements as well as top sessions. You can drag the highlighted bar to select the particular time required.

This screen looks similar to the drilldown from the Average Active Sessions section on the performance home page. However, the difference is that all the wait events can be seen against the time in this graph, on the Top Activity page. This data is fully sourced from the ASH information in the database. The text in the following screenshot may not be fully readable, but it has been included for the purpose of illustration:

Another difference is the presence of the Run ASH Report button on this page. This allows you to generate an ASH report for a period as small as one minute. The beauty of ASH is that it allows you to examine database activity that happened in any small period of time you select.

Enterprise Manager Cloud Control 12c also allows you to examine the...

Testing infrastructure changes

Real Application Testing (RAT) is an Oracle Database Enterprise Edition option that allows you to test out the effects of major or minor infrastructure changes on your database.

This may include any changes at the database layer or below, such as database upgrades from 9.2, 10.1, or 10.2 to 11g, moving from a single-instance database to a RAC database, migrating to a different OS, using a different storage subsystem, moving to Exadata, and so on. It may also include database patches, initialization parameter changes, optimizer setting changes, and so on.

RAT includes Database Replay as well as SQL Performance Analyzer (SPA). Both of these components of RAT can be fully executed via Enterprise Manager Cloud Control 12c.

Database Replay can be accessed by going to Performance | Database Replay on the database menu in Enterprise Manager. The screen appears as follows:

Database Replay is a unique feature available for Oracle databases. You can capture an entire...

SQL Monitoring

For managing and helping to tune long-running SQL statements, Enterprise Manager Cloud Control 12c provides a very useful utility called SQL Monitoring, which is enabled out of the box with no performance impact.

This will automatically monitor any long-running SQL statement that has gone beyond 5 seconds of either CPU or I/O time, as well as any parallel statements. However, in a very busy system with lots of such queries, SQL monitoring may not take place for all the queries. You can use the /*+MONITOR*/ hint in your SQL statements to explicitly ask for SQL monitoring in this case.

You can access the SQL monitoring page by going to Performance | SQL Monitoring. Any SQL statements that are long-running or parallel will appear on this page. For example, the SQL executed by user PORUSHH has taken 36 seconds to execute so far, as shown in the following screenshot:

The completed SQL statements are shown with a tick mark. There is a rotating wheel shown against the statements that...

Doctor in the database

What is also important is that the database home page shows the latest ADDM findings. This is a proactive diagnostics engine that runs every hour by default on the database, and generates a list of recommendations.

Clicking on the ADDM Findings button takes you to the last ADDM Performance Analysis page, which is displayed in the following screenshot:

Informational findings are also shown. ADDM lets you know if the database is CPU-bound or I/O-bound, it lets you know of hard/soft parse issues, top SQL statements impacting the database, any locking issues, and so on.

ADDM is also RAC aware and informs you if there are any networking/interconnect or database configuration issues affecting the performance of the RAC database. For example, improper database sequence usage can have a severe performance impact on such active/active databases.

You can run ADDM at any time by going to Performance | Advisors Home on the menu and then selecting ADDM from the list of advisors available...

Real-Time ADDM

ADDM is further enhanced in the new Enterprise Manager Cloud Control 12c version with a real-time feature. This is known as Real-Time ADDM (supported for any Oracle database Target of version 10.2.0.4 and higher) and is very useful for situations when the database has become very slow, or has hung and normal ADDM cannot work since it may not be possible to even log in to the database. Even if it is possible to log in to the database, taking an AWR snapshot or running an ADDM task may worsen the problem.

Sometimes it may be due to just one blocking session that the entire database arrives at this unresponsive state.

Often, not being able to find out the root cause of the problem, DBAs may resort to bouncing the database to fix the situation. But this is a very drastic step as all uncommitted transactions will be aborted and rolled back, and mid-tier connections and states will also be lost. The actual cause of the problem will not be found as all diagnostic information will...

Compare Period ADDM

Compare Period ADDM (supported for any Oracle database Target of version 10.2.0.4 and higher) is another new feature related to ADDM in this new version. This helps in comparative performance analysis. For example, you may have noticed that the performance of your database is worse on some days and better on other days—and you want to know the reason for this. Are some other batch programs running on some days?

Or, you have a RAC database and some instances are faster and you want to know if the workload is unevenly distributed. If any changes have been made across those periods, that is also important to understand.

You access this feature by going to Performance | AWR | Compare Period ADDM.

This will take two AWR snapshot periods and will perform a full ADDM analysis across those periods. SQL commonality is also measured and displayed, so that you can get an idea if the periods that are being compared are roughly similar or not in terms of their SQL workload. This is...

Active Session History (ASH) analytics

ASH analytics is accessed by going to Performance | ASH Analytics. This feature is new in Enterprise Manager Cloud Control 12c and allows you to analyze the database performance across multiple dimensions. It is supported for any Oracle database Target of Version 10.2.0.4 or higher.

The information on this page comes entirely from the in-memory ASH samples that are captured per second, or from the disk data, which is one sample in every ten seconds. These ASH samples are active session information, and the corresponding session attributes are also captured; these become the performance dimensions on this page.

Each second of DB time is stored as one row in each sample. Different dimensions can be selected on this page in a sort of user-defined hierarchy, for example, Instance | Module | SQL ID, and as such you are able to perform analysis on this performance data with the help of slicing and dicing.

The topmost graph on the ASH Analytics page can be seen...

Summary

Ensuring the performance of data-center applications is foremost in the minds of the IT manager. The database is a crucial part of the IT center, and if it is not properly managed, performance issues can easily get out of control—especially with the increasing data and users of today's applications. DBAs do not want to fight fires every day.

Oracle's Enterprise Manager Cloud Control 12c offers very strong performance-management capability for Oracle databases. Using the Enterprise Manager Diagnostics and Tuning packs, it is possible to easily diagnose performance issues by capturing workload statistics in the AWR and the per second session information captured by ASH for transient issues. There is the proactive diagnosis engine in the database—ADDM, which runs every hour out of the box and displays any outstanding issues.

You can then tune those issues seamlessly with the advanced tuning capabilities in the Tuning pack, such as the SQL profile, which offers auxiliary information to...