Oracle Database 11g: Performance Tuning Exam Study Guide

The Oracle Database 11g: Performance Tuning Exam Study Guide is designed to provide students with the information that can help them learn more to pass the Oracle Database 11g Performance Tuning (1Z0-054) Exam. The exam audience is represented by database administrators, support engineers, and technical consultants who can demonstrate their knowledge and skills using Oracle Database 11g automatic tuning features, including SQL Tuning Advisor, SQL Access Advisor, Automatic Workload Repository and Automatic Database Diagnostic Monitor. For each exam topic there have been identified alternative training options that are available at Oracle. Please note that some of the training recommended can cover multiple exam topics. The exam covers 19 topics. Topic 1: Basic Tuning Tools Objectives Diagnose performance issues using top wait events and the time model Use dynamic performance views to view statistics and wait events Use Enterprise Manager Monitoring Identify the key tuning components of the alert logs Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 2: Using Automatic Workload Repository Objectives Create and manage AWR snapshots Generate and read AWR reports, analyze Compare Periods reports Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 3: Defining Problems Objectives Identify performance issues & set tuning priorities

Interpret tuning diagnostics Tune for life cycle phase Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 4: Using Metrics and Alerts Objectives Interpret metric views and alerts Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 5: Baseline Objectives Create and manage AWR baselines Create AWR baselines for future time periods Monitor performance and enable adaptive thresholds Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 6: Using AWR-Based Tools Objectives Tune automatic maintenance tasks Analyze ADDM reports Analyze Active Session History (ASH) reports Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 7: Monitoring an Application Objectives Configure and manage services Use services with client applications, Database Resource Manager and Scheduler

Configure services aggregation, tracing, and set performance-metric thresholds Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 8: Identifying Problem SQL Statements Objectives Describe SQL statement processing View the SQL statement statistics to identify the SQL statements that perform poorly Describe and use methods for Viewing Execution Plans Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 9: Influencing the Optimizer Objectives Describe the optimizer's behavior Adjust parameters to influence the optimizer Influence the optimizer (access methods, join techniques and adjust data structures) Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 10: SQL Performance Management Objectives Manage optimizer statistics Configure automatic SQL tuning and SQL profiling Set up and use SQL plan baseline Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 11: Using SQL Performance Analyzer

Objectives Describe SQL Performance Analyzer process and benefits Use SQL Performance Analyzer Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 12: Using Database Replay Objectives Identify when and how to use Database Replay Use Enterprise Manager to record and replay workloads Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 13: Tuning the Shared Pool Objectives Describe Latches and Mutexes Diagnose and change cursor sharing Diagnose and resolve shared pool fragmentation Manage the results cache and tune the large pool Tune the data dictionary cache Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 14: Tuning the Buffer Cache Objectives Describe the buffer cache architecture Explain the symptoms that indicate a buffer cache problem Tune the buffer cache for performance issues Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 15: Tuning PGA and Temporary Space

Objectives Diagnose PGA memory issues and size the PGA Diagnose temporary space issues Specify temporary tablespace parameters for efficient operation Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 16: Automatic Memory Management Objectives Implement Automatic Shared Memory Management (ASMM) Implement Automatic Memory Management (AMM) Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 17: Tuning Segment Space Usage Objectives Tune segment space management Use Automatic Segment Space Management Diagnose row migration and chaining Use segment shrinking and table compression Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 18: Tuning I/O Objectives Diagnose database I/O issues Describe the Stripe and Mirror Everything (SAME) concept Set filesystemio_options and choose appropriate I/O solutions Tune Automatic Storage Management (ASM) Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Topic 19: Appendix B Using Statspack Objectives Using Statspack Training Options o Instructor Led Training (delivered by Oracle University) Oracle Database 11g: Performance Tuning

Copyright 2011, Oracle and/or its affiliates. All rights reserved. This document is provided for information purposes only and the contents hereof are subject to change without notice. This document is not warranted to be error-free, nor subject to any other warranties or conditions, whether expressed orally or implied in law, including implied warranties and conditions of merchantability or fitness for a particular purpose. We specifically disclaim any liability with respect to this document and no contractual obligations are formed either directly or indirectly by this document. This document may not be reproduced or transmitted in any form or by any means, electronic or mechanical, for any purpose, without our prior written permission.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. 0109