Workshop Overview

After completing this lesson, you should be able to do the following: Use the Oracle tuning methodology to diagnose and resolve performance problems Use Oracle tools to diagnose performance problems Understand the goals of the workshop

trinh02

Workshop Overview

20

Objectives

After completing this lesson, you should be able to do the following: • Use the Oracle tuning methodology to diagnose

and resolve performance problems

• Use Oracle tools to diagnose performance

problems

20-2

• Understand the goals of the workshop

Approach to Workshop

• Group-oriented and interactive •

Intensive hands-on diagnosis and problem resolution

20-3

• Proactive participant involvement

Physical Workshop Configuration

Each client account is set up in the following manner: • All workshop files are located in E:\Labs\Wkshop • All the data files are located at

•

20-5

E:\ORANT\ORA92\ORADATA\ORCL The E:\ORANT\ORA92\ADMIN\ORCL\BDUM directory is used for instance-specific trace files.

Workshop Database Configuration

• The database consists of a sample schema. • Users log in as oltp or dss depending on the

nature of their work.

20-6

• • • End users have access to sample schema objects. • There are seven tablespaces: system, undotbs, temp, users, indx, sample, tools. The DBA account is system/oracle. The sys account is sys/oracle.

Workshop Procedure

Implement the changes.

1. Choose a scenario. 2. Create a Statspack report. 3. Run the workload generator. 4. Create a Statspack report. 5. Determine what changes should take place. 6. 7. Return to the second step to check that the

changes have made an improvement.

8. When the changes have improved performance,

20-7

choose another scenario.

Choosing a Scenario

20-8

• Open the WORKSHOP group. • Select the icon for the chosen scenario. • Script then makes the relevant changes. • The database is left up and running.

Workshop Scenarios

Indexes

20-9

Choose from the following scenarios: • Shared pool • Buffer cache • Redo log buffer • • Rollback segments / undo tablespace • Sort area size • Assorted

Collecting Information

The v$ dynamic performance views

Table statistics

20-10

To perform a physical investigation of your workshop database environment, collect statistics by using: • • Data dictionary views • • Statspack

Generating a Workshop Load

20-11

To run the workshop load: • Open the WORKSHOP group • Select the desired workload: – Four users (current) – Twenty Users (future)

Results

20-12

• Present your conclusions and findings. • Demonstrate the effectiveness of the tuning strategy and what effect the changes to the instance and database parameters had on overall performance. – What was done and why? – What were the results? – Are there still any issues pending? – What would you do differently?

Summary

In this lesson, you should have learned how to: • Follow the Oracle tuning methodology: – Collect and review statistics. – List the objectives for enhanced performance