Performacne Tuning Overview

Download Report

Transcript Performacne Tuning Overview

Performance Tuning: Overview

2-2

Objectives

After completing this lesson, you should be able to do the following:

•

Write appropriate tuning goals

• • • •

Apply the tuning methodology Balance performance and safety trade-offs Identify common tuning problems Log a performance Service Request with Oracle Support

2-3

General Tuning Session

Tuning sessions have the same procedure: 1. Define the problem and state the goal.

2. Collect current statistics.

3. Consider some common performance errors.

4. Build a trial solution.

5. Implement and measure the change.

Decide: “Did the solution meet the goal?”

– –

No? Then go to step 3 and repeat.

Yes? Then create a new baseline.

Defining the Problem

To discover and define the problem:

• • •

Listen to user feedback Check the alert log and trace files for errors Check the parameter file for any diagnostic or inappropriate parameter setting

•

Check memory, I/O, and CPU usage. Identify processes with resource usage anomalies

•

Identify and tune SQL statements that are heavy consumers of CPU or I/O

•

Collect instance and operating system (OS) statistics

Setting the Priority

Choose the problem that has the greatest impact :

•

Analyze system performance in terms of work done ( CPU or service time ) versus time spent waiting for work ( wait time ).DB

타임 감소가 목표 •

Determine which component consumes the greatest amount of time .

•

Drill down to tune that component, if appropriate.

2-7

Tuning Methodology: Setting the Priority Example

Time Model System Stats DB/Inst: ORCL Snaps: 1-11 -> Ordered by % of DB time desc, Statistic name Statistic Time (s) hard parse elapsed time % of DB time --------------------------- --------- -------- sql execute elapsed time 467.0 77.1

DB CPU 414.2 68.4

parse time elapsed 200.5 33.1

109.0 18.0

… DB time 605.8

Common Tuning Problems

2-8

The most common tuning problems:

• • • • • • • • •

SQL statements Session management Shared pool Buffer cache Data Redo block sizing and contention sizing and contention contention log and redo buffer tuning Undo tuning I/O issues Locking issues

ADDM Tuning Session

2-9

ADDM tuning session has the same procedure as the general tuning session, but combines steps: 1. View the ADDM report.

A. Collect current statistics; compare with previous set.

B. Compare to performance issues knowledge base.

C. Define the problem and make recommendations.

2. Review recommendations.

D. Build a trial solution.

3. Implement the recommendation.

E. Implement and measure the change.

4. Review the next ADDM report.

Decide: “Did the solution meet the goal?”

Effective Tuning Goals

Effective tuning goals are:

• • •

Specific Measurable Achievable

Tuning Objectives

The objectives of tuning are:

• • • •

Minimizing response time Increasing throughput Increasing load capabilities Decreasing recovery time

2-13

DB Time

DB Time = DB Wait Time + DB CPU Time

2-14

CPU and Wait Time Tuning Dimensions

DB time = DB CPU time + DB wait time CPU time Possibly needs SQL tuning Scalable application Needs instance/RAC tuning Wait time No gain achieved by adding CPUs/nodes

Tuning Life Cycle Phases

Application life cycle can be divided into different phases:

•

Application design and development

• •

Testing: Database configuration Deployment: Adding a new application to an existing database

•

Production: Troubleshooting and tuning

Tuning Steps During Life Cycle

1. Tune the design.

2. Tune the application.

3. Tune memory.

4. Tune I/O.

5. Tune contention.

6. Tune the operating system.

2-17

Application Design and Development

The application can be tuned even in the design and development phases by building and tuning test cases.

•

Check normalization against major functions.

• • • •

Check data structures against access times.

Look at points where processes are serialized.

Tune the major reports. Tune the high-volume processes.

Testing: Database Configuration

The testing phase allows tuning at a deeper level:

• •

Check physical layout.

Monitor for resource contention.

– – –

Memory utilization Locks Disk hot spots

•

Test for resource exhaustion.

2-19

Deployment

Deployment of:

•

New application and database

– –

Take baseline . Monitor growth and performance .

•

New application in existing database

– – –

Take baseline before deployment.

Take baseline after deployment. Compare baselines.

2-20

Production

Tuning is reactive. You need to know:

What has changed?

Where is the baseline?

Collecting a Baseline Set of Statistics

A baseline set of statistics is used to:

•

Provide a set of statistics that are collected when the system is operating within the bounds set

•

Compare the baseline statistics with statistics current

•

Create a hypothesis about what has changed on the system

2-22

Performance Versus Safety Trade-Offs (

반비례

)

Factors that affect performance:

• • • • • • •

Multiple control files Multiple redo log members in a group Frequent checkpointing Backing up data files Performing archiving Block check sums Number of concurrent users and transactions

Filing a Performance Service Request

File a Performance Service Request:

• • •

Is the problem instancewide or query specific?

Identify the root cause.

Provide Statspack or AWR reports, and OS statistics.

• •

Provide Remote Diagnostics Agent (RDA) reports.

Provide SQL_TRACE reports.

RDA Report

2-25

Monitoring and Tuning Tool: Overview

Services Alert log Trace files Performance views Optimizer statistics SQL statistics Base statistics Histograms Metrics Service statistics ASH tkprof trcsess System statistics Session statistics Wait model Time model Alerts ASH reports

2-26

Monitoring and Tuning Tool: Overview

Services AWR EM performance pages Metric baseline EM policies ADDM Advisors Direct SGA monitor Hang analyzer AWR baselines Compare periods Statspack Baselines

Practice Overview: Write Tuning Goals

This practice covers the following topics:

• •

Transform a problem definition into a goal Make a goal measurable

2-28

Summary

• • •

In this lesson, you should have learned how to:

• •

Write appropriate tuning goals Identify the proper methodology for tuning in different development phases Balance performance and safety trade-offs Identify common tuning problems Log a performance Service Request with Oracle Support

Performacne Tuning Overview

Transcript Performacne Tuning Overview

Performance Tuning: Overview

Objectives

General Tuning Session

Defining the Problem

Setting the Priority

Tuning Methodology: Setting the Priority Example

Common Tuning Problems

ADDM Tuning Session

Effective Tuning Goals

Tuning Objectives

DB Time

CPU and Wait Time Tuning Dimensions

Tuning Life Cycle Phases

Tuning Steps During Life Cycle

Application Design and Development

Testing: Database Configuration

Deployment

Production

What has changed?

Where is the baseline?

Collecting a Baseline Set of Statistics

Performance Versus Safety Trade-Offs (

)

Filing a Performance Service Request

RDA Report

Monitoring and Tuning Tool: Overview

Monitoring and Tuning Tool: Overview

Practice Overview: Write Tuning Goals

Summary

Directory