Oracle9i Real Application Clusters Deployment and Performance -- Index

Active Users by Instance Chart, 7-13

in Oracle Performance Manager, 7-13

Active Users Chart

in Oracle Performance Manager, 7-13

Active/Active configurations

and Real Application Clusters, 1-3

adding nodes, 2-11

administration

aspects of scaling in Real Application Clusters, 2-11

advanced queuing

and queue table cache transfers, 5-25

and queue table instance affinity, 5-24

in Real Application Clusters, performance, 5-24

affinity

tables and advanced queuing, 5-24

ALL_TABLES view, 4-19

ALLOCATE EXTENT

DATAFILE clause, 4-10

INSTANCE clause, 4-10

ALLOCATE EXTENT clause

DATAFILE clause, 4-10

exclusive mode, 4-9

in exclusive mode, 4-9

INSTANCE clause, 4-10

instance number, 4-11

preallocating extents, 4-11

SIZE clause, 4-9

allocation

automatic, 4-11, 4-12

Cache Fusion resources, A-7

extents, 4-11, 4-12

extents, dynamic, A-15

GCS resources, A-15

of Cache Fusion resources, A-7

ALTER CLUSTER statement

ALLOCATE EXTENT clause, 4-9, 4-11

ALTER DATABASE statement

DATAFILE RESIZE, A-5

ALTER SESSION SET INSTANCE statement, 4-8

ALTER SESSION statement

SET INSTANCE clause, 4-8

ALTER TABLE statement

ALLOCATE EXTENT, 4-9

allocating extents, 4-11, 4-12

DISABLE TABLE LOCK clause, 4-19

ENABLE TABLE LOCK clause, 4-19

MAXEXTENTS clause, 4-12

ALTER TABLESPACE statement

READ ONLY clause, 3-7

analysis

of applications for Real Application Clusters, 3-2

application profile

case study example, B-4

applications

analysis for Real Application Clusters, 3-2

designing, B-2

development for Real Application Clusters, 3-2

diagnosing performance problems of, 5-23

performance problems, 5-22

performance profiles, 5-15

scalability, 6-4

table access patterns, 3-2

transactions, 3-2

tuning queries of, 5-22

AUTOEXTEND clause, A-5

average global cache CR request time, 7-3, 7-7

B2B models

in Real Application Clusters, 2-4

block size

and contention, 5-18

increasing for query performance, 5-22

blocks

contention, 4-11, A-15

dynamic allocation of on resource boundaries, A-10

minimizing contention for, 4-11

branch blocks

minimizing contention for, 4-17

CACHE

clause, for Oracle sequences, 5-21

Cache Fusion

and e-commerce applications, 2-2

and performance issues for, 6-6

benefits, 6-4

performance, 6-1

performance monitoring goals, 6-7

processing, minimizing overhead for, 3-2

resources, associating with free lists, A-15

resources, exclusive, A-6

resources, shared, A-6

resources, specifying, A-5

sources of performance statistics for, 6-8

tuning, 6-1

cache transfers

of queue table data blocks in advanced queuing, 5-25

case study

in Real Application Clusters, B-2

CATCLUST.SQL script, 6-9

using to create views for Real Application Clusters, 6-9

Charts, Statistics, 7-7

cluster file systems

in Real Application Clusters, 1-6

clustered tables

with free lists and free list groups, 4-6

clusters

allocating extents, 4-11

free list groups, 4-9

free lists, 4-6

hash cluster, 4-6

parallel execution tuning, A-16

Clusters Data Block Ping by Instance Chart

in Oracle Performance Manager, 7-13

compatibility

shared and exclusive modes, 4-9

component-based architectures, 2-4

concurrency

inserts and updates, 4-5

consistent read

processing for and tuning issues, 6-2

consistent-read blocks, 6-2

contention

and block size, 5-18

block, 4-11, A-15

for resources, 5-20

minimizing for blocks, 4-11

specific to applications on Real Application Clusters, 5-21

table data, 4-11

context switches

reduced with Cache Fusion, 6-4

control files

data files, 4-10

cost-based optimizer, 2-7

CPU service time required

calculating, 5-12

CPU utilization

reduced with Cache Fusion, 6-4

CR time outs, chart, 7-7

CREATE CLUSTER statement, 4-6

FREELIST GROUPS clause, 4-5

FREELISTS clause, 4-5

CREATE statement

setting FREELISTS and FREELIST GROUPS, 4-5

CREATE TABLE statement

clustered tables, 4-6

examples, 4-11

FREELISTS clause, 4-5

initial storage, 4-11

Current Block Request Chart, Global Cache, 7-11

current image

for consistent read processing and tuning of, 6-2

data blocks

cache transfers in advanced queuing, 5-25

contention for, causes of, 5-18

types accessed by transactions, 3-3

data dictionary

querying views, 6-9

data dictionary cache

contention for, 5-20

data locality

in Real Application Clusters, 2-7

data warehousing

deploying applications for in Real Application Clusters, 2-6

separating from e-commerce, 3-8

database

design techniques for Real Application Clusters, 4-2

designing, B-2

database design

case study example, B-3

data-dependent routing, 3-10

DATAFILE clause

table, 4-11

datafiles

allocating extents, 4-10

multiple files for each table, 4-11, A-15

DB_FILE_MULTIBLOCK_READ_COUNT

increasing for full table scans, 5-22

DBA_QUEUE_TABLES

analyzing table and instance affinity in advanced queuing, 5-24

DBA_TABLES table, 4-19

dedicated server

and connection load balancing, 1-4

degree of parallelism (DOP), 2-6

DELETE

block access during, 3-6

departmental partitioning method, 3-8

deployment

of Real Application Clusters, 1-7

strategies for Real Application Clusters, 2-3

techniques for application development, 3-2

designing

databases for Real Application Clusters, 4-2

diagnosing

performance problems, 5-23

DISABLE TABLE LOCK clause, 4-19

disk affinities

and parallel query, 2-9

disk affinity, 2-9

dlm_requests, chart, 7-11

DML_LOCKS parameter

and performance, 4-19

dynamic performance view

creating, 6-9

e-commerce

applications in Real Application Clusters, 2-2

separating from data warehousing, 3-8

ENABLE TABLE LOCK clause, 4-19

error messages

storage options, 4-5

event 29700

enabling for GES resource statistics collection, 6-19

exclusive mode

free lists, 4-5, 4-9

specifying instance number, 4-11

startup, 4-11

execution history

tracing of with TRACE_ENABLED parameter, 5-3

extents

allocating GCS resources, A-15

allocating to instance, 4-8, 4-11

initial allocation, 4-11

not allocated to instance, 4-10

preallocating, 4-9

preallocating to free list groups, 4-10

size, 4-9

specifying a file, 4-10

failover

and Real Application Clusters, 1-2

false forced disk writes, A-20

false forced writes, A-19

false pings, A-3

features

new, xxx

taking advantage of, 1-2

File I/O Rate by Instance Default Chart

in Oracle Performance Manager, 7-4, 7-12

File I/O Rate by Object Default Chart

in Oracle Performance Manager, 7-4, 7-12

File I/O Rate Default Chart

in Oracle Performance Manager, 7-4, 7-12

File Ping by Instance Chart

in Oracle Performance Manager, 7-3, 7-9

files

allocating extents, 4-10

flow-controlled messaging

and the GES, 6-21

forced disk writes, A-20

false, A-19, A-20

identifying by block class, 6-24

forced disk writes, chart, 7-7

free list groups

assigning to session, 4-8

for concurrent inserts, 4-3

setting !blocks, A-4

free lists

cluster, 4-6

creating for clustered tables, 4-6

creating for indexes, 4-7

examples, 4-6

GCS resources, A-15

hash cluster, 4-6

in exclusive mode, 4-5, 4-9

number of lists, 4-5

FREELIST GROUPS

determining reorganization needs, 4-4

parameter, use, 4-5

parameter, use with indexes, 4-7

FREELIST GROUPS clause, 4-5, 4-12

FREELISTS

creating for clustered tables, 4-6

creating for indexes, 4-7

examples of use, 4-6

parameter, use, 4-5

parameter, use with indexes, 4-7

STORAGE clause, 4-5

FREELISTS clause, 4-5

maximum value, 4-5

function shipping, 2-6

functional partitioning, 3-7

GC_FILES_TO_LOCKS parameter, A-6, A-16

associating GCS resources with extents, A-15

examples, A-5

reducing false pings, A-20

setting, A-4

syntax, A-4

GCS

resource acquisition, 5-25

resource statistics, analyzing, 6-19

GCS LMS process utilization, 7-7

GCS resources, A-16

contention, A-15

mapping blocks to, A-15

geographic

partitioning method, 3-8

GES

message statistics, analyzing, 6-21

resources, analyzing, 6-19

statistics, analyzing, 6-18

statistics, for monitoring contention, 5-24

global cache

coherence, measuring, 5-13

Global Cache Convert Timeouts By Instance Chart, 7-8

Global Cache CR Request By Instance Chart, 7-9

Global Cache CR Request by Instance Chart

in Oracle Performance Manager, 7-3

Global Cache CR Request Chart, 7-7

in Oracle Performance Manager, 7-3

global cache CR request time, average, 7-3, 7-7

Global cache CR timeouts, 7-7

Global Cache CR Timeouts By Instance Chart, 7-8

Global Cache CR Timeouts by Instance Chart

in Oracle Performance Manager, 7-3

Global Cache Current Block Instance Activity Chart, 7-12

in Oracle Performance Manager, 7-4

Global Cache Current Block Request By Instance Chart, 7-12

Global Cache Current Block Request by Instance Chart

in Oracle Performance Manager, 7-4

Global Cache Current Block Request Chart, 7-11

in Oracle Performance Manager, 7-4

Global Cache Freelist Waits By Instance Chart, 7-9

Global Cache Freelist Waits by Instance Chart

in Oracle Performance Manager, 7-3

Global Cache Lock Convert By Instance Chart, 7-9

Global Cache Lock Convert by Instance Chart

in Oracle Performance Manager, 7-3

Global Cache Lock Convert Chart, 7-8

in Oracle Performance Manager, 7-3

global cache statistics

analyzing, 6-11

global cache synchronization costs

calculating, 5-18

Global Cache Timeouts Chart, 7-7

in Oracle Performance Manager, 7-3

global enqueue statistics

analyzing, 6-16

GLOBAL hint, 6-10

global V$ view tables, 7-2

global work ratios

measuring, 5-16

GV$CACHE view, 6-9

GV$CACHE_TRANSFER view, 6-9

GV$CLASS_CACHE_TRANSFER view, 6-9

GV$FILE_CACHE_TRANSFER view, 6-9

GV$LIBRARYCACHE view, 6-9

GV$ROWCACHE view, 6-9

hash clusters, 4-6

high availability

and Real Application Clusters, 1-2

high water mark, A-11

moving, A-11

hot blocks

identifying, 6-24

identifiers

for resources, A-21

incremental growth, 4-11

indexes

issues for inter-instance contention, 4-16

partitioning, case study example, B-17

reverse-key, for minimizing contention, 4-17

using with free lists and free list groups, 4-7

INITIAL storage parameter

minimum value, 4-11

INSERTS

concurrent, 4-5

free space unavailable, 4-9

processing within Oracle, 3-3

INST_ID column, 6-10

INSTANCE clause

allocating, 4-11

SET INSTANCE statement, 4-8

Instance Ping Chart

in Oracle Performance Manager, 7-3

INSTANCE_NUMBER parameter, 4-8

setting, 4-11

INSTANCE_ROLE

use of in secondary instance connections, 1-5

instances

adding, 3-16

adding instances, 4-11

associated with data file, 4-11

associated with extent, 4-8

associating with free list groups, 4-7

free list, 4-9

instance number, 4-11

number, 4-8

scalability, 3-16

interconnect

and performance, 6-6

protocols for Real Application Clusters, 6-6

intra-node parallelism, 2-7

IPCs

and Cache Fusion, 6-5

latches

analyzing statistics for, 6-25

leaf blocks

minimizing contention for, 4-17

library cache

contention for, 5-21

Library Cache Lock By Instance Chart, 7-11

Library Cache Lock by Instance Chart

in Oracle Performance Manager, 7-4

Library Cache Lock Chart, 7-10

in Oracle Performance Manager, 7-4

LMS

and flow-controlled messaging, 6-21

load balancing, 2-8

local work ratios

measuring, 5-16

Lock Activity Chart, 7-12

Lock Activity Default Chart

in Oracle Performance Manager, 7-4

lock activity rate, chart, 7-12

locks

deciding whether to use by setting GC_FILES_TO_LOCKS, A-2

setting pre-9.0.1 release locks with GC_FILES_TO_LOCKS, A-2

when to use pre-9.0.1 release locks, A-3

mapping blocks to Cache Fusion resources, A-6

MAXEXTENTS storage parameter

automatic allocations, 4-11

preallocating extents, 4-12

Maximum Ping By Block Chart, 7-10

Maximum Ping by Block Chart

in Oracle Performance Manager, 7-4

memory-mapped IPCs

and Cache Fusion, 6-5

message statistics

analyzing, GES, 6-22

messages

as processed by the GES, 6-21

migration

identifying critical tables beforehand, 4-4

returning to exclusive mode, 4-9

MINEXTENTS storage parameter

automatic allocations, 4-11, 4-12

default, 4-11

monitoring

goals of, 6-7

procedures for, 6-7

statistics for Real Application Clusters, 5-2

new features, xxx

nodes

adding, 2-11, 4-11

n-tier architectures

benefits of, 2-5

in Real Application Clusters, 2-3

monitoring and tuning performance of, 2-5

Object Ping by Instance Chart

in Oracle Performance Manager, 7-4

objects

creation of and effect on performance, 4-20

identifying contention, 5-11

using free list groups to create, 4-3

online transaction processing

in Real Application Clusters, 2-2

operating system

striping for performance, 5-22

Oracle

compatibility, 4-9

Oracle Enterprise Manager

starting

Oracle Performance Manager, 7-5

Oracle Net

in Real Application Clusters, 1-3

Oracle Performance Manager, 7-2

displaying charts, 7-5

overview, 7-2

starting, 7-5

overlaps

of tables in applications, 3-13

packaged applications

scalability for, 6-4

parallel execution

and load balancing, 2-8

clusters, A-16

parallel instance groups, 2-8

parallelism

in Real Application Clusters, 2-6

parallel-aware query optimization, 2-7

parameters

storage, 4-5, 4-9

partitioning

and scalability, 3-16

by transaction, 3-9

case study example, B-14

data, in data files, 4-11

departmental, 3-8

functional, 3-7

physical table, 3-9

user, 3-8

users, 3-8

partitioning data

free lists, A-15

GCS resources, A-15

table data, A-15

PCTFREE

and contention, 5-18

PCTINCREASE parameter

table extents, 4-9

performance

expectations and Cache Fusion, 6-6

maintaining history of, 6-8

measuring workloads, 5-9

primary components affecting, 6-6

problems in applications in Real Application Clusters, 5-22

problems, diagnosing, 5-23

problems, identifying, 6-27

tuning and inter-instance performance, 6-1

Ping By Block Chart, 7-10

Ping By Block Class Chart

in Oracle Performance Manager, 7-10

Ping by Block Class Chart

in Oracle Performance Manager, 7-3

Ping By File Chart

in Oracle Performance Manager, 7-9

Ping by File Chart

in Oracle Performance Manager, 7-3

Ping By Object Chart

in Oracle Performance Manager, 7-10

Ping by Object Chart

in Oracle Performance Manager, 7-3

Ping By Object Drilldown Chart

in Oracle Performance Manager, 7-10

PL/SQL

in Real Application Clusters, 1-5

preallocating

extents, 4-9

extents to free list groups, 4-10

Primary/Secondary configurations

and Real Application Clusters, 1-3

profiles

of application performance, 5-15

protocols

interconnect, 6-5

reader/writer conflicts

and Cache Fusion, 6-1

Real Application Clusters

deployment phases, 1-7

disk affinities, 2-9

parallel execution, A-16

recording statistics

for tuning, 5-3

resource acquisition

and the GCS, 5-25

resources

block mode conversions, analyzing by type, 6-23

contention for, 5-20

convert timeouts, analyzing, 6-15

GCS resource, A-15

identifier, A-21

name format, A-21

response times

degradation, causes of, 5-18

reverse-key indexes

for minimizing contention, 4-17

RMAN

in Real Application Clusters, 1-6

routing, data-dependent, 3-10

row cache

contention for, 5-20

Row Cache Lock By Instance Chart, 7-11

Row Cache Lock by Instance Chart

in Oracle Performance Manager, 7-4

Row Cache Lock Chart, 7-11

in Oracle Performance Manager, 7-4

scalability

and partitioning, 3-16

assessing by measuring workloads, 5-9

with Cache Fusion, 6-4

scaling applications, 3-1

segment header

processing during inserts, 3-3

segment headers

and new applications, 5-19

contention for, 5-19

segments

header, A-22

SELECT

block access during, 3-6

sequence number multipliers, 5-21

sequence numbers

global conflict detection for, 4-13

using, 4-13

sequences

contention when not using the CACHE option, 5-20

uncached and contention, 5-20

server coordination events, 6-29

Sessions Chart

in Oracle Performance Manager, 7-12

Sessions Default Chart

in Oracle Performance Manager, 7-4

setting pre-release 9.0.1 locks, A-2

shared resource system, 4-11

shared server

and connection load balancing, 1-4

in Real Application Clusters, 1-4

SIZE clause

allocating extents, 4-11

space

allocating extents, 4-11

not allocated to instance, 4-10

unavailable in exclusive mode, 4-9

space parameters

and contention, 5-20

SQL statements

execution of in Real Application Clusters, 3-3

starting

Oracle Performance Manager, 7-5

starting up

exclusive mode, 4-11

statistics

analyzing, GES, 6-22

and their classes in V$SYSSTAT, 5-5

contents of, 5-2

for high contention, 5-14

from V$SYSTEM_EVENT, 5-7

GES, for monitoring contention, 5-24

global cache, analyzing, 6-11

list of most important for Real Application Clusters, 5-4

recording for tuning, 5-3

setting TIMED_STATISTICS for collection, 6-9

views containing, 5-8

where maintained, 5-2

where Oracle collects from, 6-8

Statistics Charts, 7-7

Statspack

using to monitor for contention, 5-24

storage options

clustered tables, 4-5

extent size, 4-9, 4-11

table, 4-5

striping

and disk affinity, 2-9

synchronization

calculating costs of, 5-18

determining the costs of, 5-12

table

affinity and advanced queuing, 5-24

table access analysis

case study example, B-5

TABLE_LOCK column, 4-19

tables

allocating extents, 4-11

cluster, 4-6

contention, 4-11

free space unavailable, 4-9

GCS resource, A-15

initial storage, 4-11

locks, disabling, 4-19

multiple files, 4-11

overlapping, 3-13

read-only, 3-6

tablespaces

design, for access distribution, 4-14

three-tier architectures, 2-3

throughput

with Cache Fusion, 6-4

TIMED_STATISTICS

setting for statistics collection, 6-9

Total Ping Chart, 7-7

in Oracle Performance Manager, 7-3, 7-7

trace files

locations of, 5-4

TRACE_ENABLED parameter

tracing execution history, 5-3

tracing

of execution history with TRACE_ENABLED, 5-3

transaction processing monitor, 3-10

transaction volume

case study example, B-10

transactions

types of DML involved, 3-3

transparent application failover

in Real Application Clusters, 1-4

tuning

general recommendations, 5-10

overview of for Real Application Clusters, 5-2

queries, 5-22

two-tier architectures, 2-3

UPDATE

block access during, 3-5

user

moving among instances, 3-16

partitioning method, 3-8

user processes

associating with free list groups, 4-8

user sessions

associating with free list groups, 4-7

USER_QUEUE_TABLES

analyzing table and instance affinity in advanced queuing, 5-24

USER_TABLES table, 4-19

user-mode IPCs

and Cache Fusion, 6-4, 6-5

Users Default Chart, 7-13

in Oracle Performance Manager, 7-4

Users Logged On Chart, 7-13

Users Per Instance Default Chart

in Oracle Performance Manager, 7-4, 7-13

UTLBSTAT

for recording statistics, 5-3

using to monitor for contention, 5-24

UTLESTAT

for recording statistics, 5-3

using to monitor for contention, 5-24

V$ fixed views, 7-2

V$BH

identifying contended objects with, 5-11, 5-19

using to identify forced writes, 5-24

V$BH view, 3-3

V$CACHE

identifying contended objects with, 5-11, 5-19

V$CACHE_LOCK view, 6-10

V$CACHE_TRANSFER

identifying contended objects with, 5-11, 5-19

using for monitoring contention, 5-24

V$CLASS_CACHE_TRANSFER

for statistics gathering, 5-10

V$FILE_CACHE_TRANSFER

statistics gathering, 5-10

V$LOCK_ACTIVITY

monitoring block mode conversion rates, 5-24

V$LOCK_ACTIVITY view, 6-10

V$LOCKS_WITH_COLLISIONS view, 6-10

V$ROLLNAME view, 6-10

V$ROWCACHE

and contention, 5-20

V$SYSSTAT

for statistics, 5-5

V$SYSTEM_EVENT

and contention for resources, 5-20

events specific to Real Application Clusters, 6-28

wait events and relevant statistics, 5-7

V$SYSTEM_EVENT view, 6-27

versions, Oracle

compatibility, 4-9

VIA

interconnect protocol, 6-5

views

creating for Real Application Clusters, 6-9

most important for Real Application Clusters performance, 5-4

work ratios

measuring, 5-16

workloads

application performance, 5-15

characterization of in Real Application Clusters, 3-11

concepts of distribution, 3-7

distinquishing e-commerce and data warehousing, 3-8

general tuning recommendations, 5-10

measuring performance of, 5-9

object contention, 5-11