Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Oracle Database 11gR2 Performance Tuning Cookbook

You're reading from   Oracle Database 11gR2 Performance Tuning Cookbook Shifting your Oracle Database into top gear takes a lot of know-how and fine-tuning ability. The 80+ recipes in this Cookbook will give you those skills along with the ability to troubleshoot if things starts running slowly.

Arrow left icon
Product type Paperback
Published in Jan 2012
Publisher Packt
ISBN-13 9781849682602
Length 542 pages
Edition 1st Edition
Languages
Arrow right icon
Author (1):
Arrow left icon
Ciro Fiorillo Ciro Fiorillo
Author Profile Icon Ciro Fiorillo
Ciro Fiorillo
Arrow right icon
View More author details
Toc

Table of Contents (21) Chapters Close

Oracle Database 11gR2 Performance Tuning Cookbook
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
1. Starting with Performance Tuning FREE CHAPTER 2. Optimizing Application Design 3. Optimizing Storage Structures 4. Optimizing SQL Code 5. Optimizing Sort Operations 6. Optimizing PL/SQL Code 7. Improving the Oracle Optimizer 8. Other Optimizations 9. Tuning Memory 10. Tuning I/O 11. Tuning Contention Dynamic Performance Views A Summary of Oracle Packages Used for Performance Tuning Index

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.

Note

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;

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.

You have been reading a chapter from
Oracle Database 11gR2 Performance Tuning Cookbook
Published in: Jan 2012
Publisher: Packt
ISBN-13: 9781849682602
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image