Oracle Enterprise DBA Part 2: Performance and Tuning

Table of Oracle Performance Tuning Goals

Areas Affected

Manual Pages (old

Oracle 8 Manual)

Statistic

Tool

Goal

 Remedy

SQL Statement Tuning

5-25

Parse, Execute, Fetch

Query+Current

TKPROF output

bring down Query + Current

Operating System

6-6

Max CPU busy rate

<= 90%

Operating System

6-6

Max OS/user processing rate

40/60

Library Cache

7-13

V$LIBRARYCACHE

and gethitratio, SQL Area

Table/Procedure Body

V$LIBRARYCACHE and

report.txt

all should be in high 90%

Library Cache

7-16 to 7-18

sum(reloads)/sum(pins)

V$LIBRARYCACHE

< .01 (1%)

Library Cache

7-16 to 7-18

pinhitratio = library cache hit %

V$LIBRARYCACHE

>= .99 (99%)

Data Dictionary Cache

7-35

sum(getmisses)/sum(gets)

V$ROWCACHE

< .15 (15%)

Data Dictionary Cache

7-36

dcobject, dcsynonyms ->

get-miss/get-reqs

report.txt

< .15 (15%)

Database Buffer Cache

8-9

cache hit ratio = # blocks

found in memory/# blocks

accessed

V$SYSTAT, report.txt

>= 90%

Database Buffer Cache

8-14

hit ratio = 1 - (physical reads/

/(db block gets+ consistent

gets))

V$SYSTAT, report.txt

>= 90%

Redo Log Buffer

7-9 (new 8i manuals)

select sid, event,  stats

seconds_in_wait

from v$session_wait

where event = 'log

bufferspace';

V$SESSION_WAIT

= 0

Redo Log Buffer

7-9 (new 8i manuals)

select name, value

from v$sysstat

where name in ('redo buffer

allocation retries', 'redo

retries');

V$SYSSTAT

retries near 0

retries/redo entries < 1%

Database Configuration

and I/O Issues

8-16 (new 8i manuals)

I/O statistics section of

report.txt

report.txt

see that I/O load is

distributed across

devices, minimize full

table scans

 distribute I/O across devices, build indexes when appropriate

Database Configuration

and I/O Issues - checkpointing

8-26 (new 8i manuals)

Background Checkpoints

Started - Background

Checkpoints Completed

report.txt

<= 1

otherwise LGWR has to wait since checkpoints are not finishing fast enough to keep up with log file switches

 increase size of log files

Using Oracle Blocks Efficiently

?

?

report.txt ?

reduce or eliminate chaining and migration - don't have wasted unused space

 chaining - choose appropriate block size to allocate large data elements

migration - export data, truncate table, insert data

Using Oracle Blocks Efficiently

9-31

select (del_lf_rows_len/lf_rows_len)*100

from v$index_stats

V$INDEX_STATS ??

< 20%

 rebuild index

Optimizing Sort Operations

10-20

select disk.value "disk", mem.value "mem", (disk.value/mem.value) *100 "ratio"

from v$sysstat mem, v$sysstat disk

where mem.name = 'sorts(memory)' and disk.name = 'sorts(disk)';

report.txt

 

OEM Performance Manager

< 5%

 increase sort_area_size in init.ora (parameter) file

Tuning Rollback Segments

11-13 (new 8i manuals)

select sum(waits)*100/sum(gets) "ratio", sum(waits) "waits", sum(gets) "gets" from v$rollstat

*or*

undo segment in report.txt:

trans_tbl_gets

trans_tbl_waits

V$ROLLSTAT

 

*or*

 

report.txt

< 5%

 create more rollback segments

Tuning Rollback Segments

13-7 (old Oracle 8 manuals)

number of waits for any class =

select class, count

from v$waitstat

 

or

 

select class/sum(value)

from v$sysstat

where name = 'consistent gets';

V$WAITSTAT

 

*or*

 

V$SYSSTAT

< 1%

 create more rollback segments

Tuning Rollback Segments

13-19 (old Oracle 8 manuals)

RULE OF FOUR

one rbs per 4 transactions (round up to 4 or 8, then next higher integer after 8)

Lock Contention

14-25, 26 and 14-27, 28 of old Oracle 8 manuals

lock contention

V$LOCK, V$LOCKED_OBJECT, DBA_OBJECTS, OEM Lock Manager

want to eliminate waiting for locked objects

 killing sessions

Lock Contention

14-33 of old Oracle 8 manuals

deadlock is normally handled by Oracle without user overrides....

problem: users override default locking in Oracle

user_dump_dest

look at transactions - change their overrides of defaults

Latch Contention: Redo Copy Latch

15-6 of old Oracle 8 manuals

gets, misses, sleeps, % misses/gets

V$LATCH

< 1%

 increase the number of log_simultaneous_copies, the max number is 2* number of CPUs

Latch Contention: Redo Allocation Latch

15-11 of old Oracle 8 manuals

% immediate misses/(immed. misses + immed. gets)

V$LATCHNAME

< 1%

 decrease log_small_entry_max_size

Latch Contention: LRU Latch

15-4 of old Oracle 8 manuals

select l.name, sleeps/gets "LRU hit %"

from v$latch l, v$latchname ln

where ln.name in

('cache buffers lru chain') and ln.latch# = l.latch#;

V$LATCHNAME, V$SYSTEM_EVENT,

V$SESSION_EVENT,

V$BUFFER_POOL_STATISTICS

< 1%

 increase number of LRU latches with db_block_lru_latches so that the max is the smaller of {# CPUs * 6, number of buffers/50}

Free List  Contention

15-18,19 of old Oracle 8 manuals

select class, count, time

from v$waitstat

where class = 'segment headers';

OR

select event, total_waits from v$system_event

V$WAITSTAT, DBA_EXTENTS,

V$SYSTEM_EVENT,

OEM Performance Manager

Free List Hit % ?????

 ???