Managing Database Performance

Introduction

The Managing Database Performance module will evaluate the tools that are available in Oracle Database 12c to manage the performance of the database. In this module, we will also learn about optimizer statistics, Automatic Workload Repository, various advisors and Automatic Memory Management. The following topics will be covered in this session:

• Managing Optimizer Statistics
• Gathering Performance Statistics
• Automatic Database Diagnostic Monitoring
• Understanding Automatic Diagnostic Repository
• Automatic Shared Memory Management
• Automatic SQL Execution Memory Management

Exercise 1 - Managing Optimizer Statistics

In this exercise you will learn the following about managing optimizer:

• Establishing a connection to the database
• Collecting Statistics

Optimizer statistics are basically the collection of data which describes the database. To evaluate the best execution plan to display the row of data the query wants to find, the query optimizer makes use of optimizer statistics. The optimizer makes use of statistics to carry out the following:

• How to access data and determine which indexes to use
• How to join tables
• How to find expressions and conditions

These statistics are used by the cost based optimizer to formulate efficient execution plans for the SQL statements that are issued by application users.

Exercise 2 - Gathering Performance Statistics

In this exercise, you will learn the following about gathering performance statistics:

• Using Automatic Workload Repository
• AWR Baselines

Exercise 3 - Automatic Database Diagnostic Monitoring

In this exercise, you will learn the following about automatic database diagnostic monitoring:

• Using Data Dictionary Views to View ADDM Analysis
• Viewing and Configuring Alerts Using SQL

Exercise 4 - Understanding Automatic Diagnostic Repository

In this exercise, you will learn the following about automatic diagnostic repository:

• Using ADRCI to view the Alert Log File

Exercise 5 - Automatic Shared Memory Management

In this exercise, you will learn the following about automatic shared memory management:

• Understanding Automatic Shared Memory Management

Exercise 6 - Automatic SQL Execution Memory Management

In this exercise, you will learn the following about automatic SQL execution memory management:

• Automatic Memory Management