Document

Download Report

Transcript Document

Performance Tuning: Summary

Objectives

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

•

List best practices identified throughout the course

•

Summarize the performance tuning methodology

15-3

Necessary Initialization Parameters with Little Performance Impact

Parameter DB_NAME DB_DOMAIN OPEN_CURSORS CONTROL_FILES DB_FILES STATISTICS_LEVEL Description Name of the database. This should match the ORACLE_SID environment variable. Location of the database in Internet dot notation Limit on the maximum number of cursors for each session. The setting is application dependent; 500 is recommended. Set to contain at least two files on different disk drives to prevent failures from control file loss Set to the maximum number of files that can be assigned to the database Set to TYPICAL

15-4

Important Initialization Parameters with Performance Impact

Parameter COMPATIBLE DB_BLOCK_SIZE SGA_TARGET PGA_AGGREGATE_TARGET PROCESSES SESSIONS UNDO_MANAGEMENT UNDO_TABLESPACE Description To take advantage of the latest improvements of a new release 8192 for OLTP and higher for DSS Automatically sized SGA components Automatic PGA management Maximum number of processes that can be started by that instance To be used essentially with shared server AUTO mode recommended Undo tablespace to be used by instance

Sizing Memory Initially

15-6

As an initial guess for memory allocation:

•

Leave 20% of available memory to other applications.

• •

Leave 80% of memory to the Oracle instance.

For OLTP: SGA_TARGET=(total_mem*80%)*80% PGA_AGGREGATE_TARGET=(total_mem*80%)*20%

•

For DSS: SGA_TARGET=(total_mem*80%)*50% PGA_AGGREGATE_TARGET=(total_mem*80%)*50%

Database High Availability: Best Practices

• • • • • • • • • • • •

Use SPFILE .

Multiplex redo logs.

Use resumable space allocation.

Create at least two control files.

Enable Flashback Database.

• • • •

Enable block checking.

Use auto-tune checkpointing.

Log checkpoints to the alert log. Use database resource manager. Use Automatic Undo Management.

Use Automatic Segment Space Management.

Use locally managed tablespaces. Use locally managed temporary tablespaces. Enable ARCHIVELOG mode and use a flash recovery area.

Set time long enough for CONTROL_FILE_RECORD_KEEP_TIME .

Designate a default permanent tablespace other than SYSTEM SYSAUX .

and

15-8

Undo Tablespace: Best Practices

• • •

Use Automatic Undo Management.

UNDO_RETENTION :

–

Oracle Database 10g Release 1: Set it to your flashback requirement.

–

Oracle Database 10g Release 2: Do not set it.

Undo tablespace size:

–

Initial size: Small with AUTOEXTEND enabled

–

Steady state: Fix size using the Undo Advisor and add a 20% safe margin.

Temporary Tablespace: Best Practices

15-10

Locally managed temporary tablespaces use a uniform extent. Extent size should be:

•

1 MB to 10 MB extent size:

– –

For DSS, OLAP applications involving huge work areas Large temporary LOBs are predominant.

•

64 KB or multiple less than 1 MB:

– –

Small global temporary tables are predominant.

OLTP Temporary tablespace group increases addressability from TB to PB.

General Tablespace: Best Practices

15-12 • • • • • •

Use locally managed tablespaces with auto allocate extents policy.

Use Automatic Segment Space Management (ASSM).

Use online segment shrink to eliminate internal fragmentation.

Periodically review the results of the Automatic Segment Advisor.

Monitor tablespace space usage using server generated alerts.

Size of extents matter more than the number of extents in the segments.

Internal Fragmentation Considerations

15-13 • •

Watch for:

–

Bad choices of PCTFREE and PCTUSED for heap segments

–

Bad choices of PCTVERSION and RETENTION for LOB segments

– – –

Low density of data in segment Direct loads followed by deletes (no inserts) Indexes on tables with random updates and deletes with no further inserts Remedy:

– – –

Online segment shrink Online redefinition MOVE operations

Block Size: Advantages and Disadvantages

Block Size Smaller Larger Advantages Disadvantages Small rows with lots of random access Reduce block contention (OLTP) For large rows (chaining) Lower overhead: more room for data Good for sequential access Relatively high overhead (block header) Waste cache space with random access of small rows Index leaf block contention (OLTP)

15-16

Sizing Redo Log Files

• • • • •

Size of redo log files can influence performance.

Larger redo log files provide better performance.

Generally, redo log files should range between 100 MB and a few gigabytes.

Switch redo log file at most once every twenty minutes.

Use the Redo Logfile Size Advisor to correctly size your redo logs.

Automatic Statistics Gathering

15-17 • • •

STATISTICS_LEVEL = TYPICAL | ALL Statistics are gathered by the predefined GATHER_STATS_JOB job.

This job implicitly determines the following:

– –

Database objects with missing or stale statistics Appropriate sampling percentage necessary to gather good statistics on those objects

–

Appropriate columns that require histograms and the size of those histograms

– –

Degree of parallelism for statistics gathering Prioritization of objects on which to collect statistics

15-18

Automatic Statistics Collection: Considerations

• •

You should still manually gather statistics in the following cases:

– – – –

After bulk operations When using external tables To collect system statistics To collect statistics on fixed objects Prevent automatic gathering for volatile tables:

– –

Lock with statistics for representative values Lock without statistics implies dynamic sampling.

Commonly Observed Wait Events

Wait Event Area Possible cause Examine buffer busy waits free buffer waits db file scattered read, db file sequential read Enqueue waits (enq:) Library cache waits log buffer space Log file sync Buffer cache, DBWR Buffer cache, DBWR, I/O I/O, SQL Tuning Depends on buffer type. PK index and seq.

V$SESSION (block) while issue is occurring Slow DBWR Write time using OS stats. Buffer cache stats Poorly tuned SQL, Slow I/O system V$SQLAREA V$FILESTAT disk reads. read time Locks Latches Log buffer I/O Over-commit, I/O Depends on enq type SQL parsing/sharing Small buffer, slow I/O Slow I/O, un-batched commits V$ENQUEUE_STAT V$SQLAREA V$SYSSTAT parse calls, child cursors redo buffer allocation retries, disk commits + rollbacks from V$SYSSTAT , Disks.

Statistic name

Additional Statistics

Description Recommended action Redo Log Space Requests Consistent changes Consistent gets Table Fetch by Continued Row How many times a server process had to wait for space in the online redo log How many rollbacks done for consistent read purposes Checkpoints, DBWR, or archiver activity should be tuned, larger log files Use automatic undo, tune the workload How many blocks are read in Consistent Read mode Migrated or chained rows Use automatic undo, tune the workload Reorganize

Top 10 Mistakes Found in Oracle Systems

15-21

1. Bad connection management 2. Bad use of cursors and shared pool 3. Bad SQL 4. Use of nonstandard initialization parameters 5. Getting database I/O wrong 6. Redo log setup problems 7. Serialization of data blocks in the buffer cache 8. Long full-table scans 9. High amount of recursive SQL 10. Deployment and migration errors

Summary

In this lesson, you should have learned how to:

•

Create your initial database following the best practices identified throughout the course

•