Oracle8(TM) Server -- Index

registering with the database, 4-7, 24-2

OLTP, 5-2

parallel query, 5-5

parallel server, 5-9

ARCH process, 18-13, 19-40

architecture

and CPU, 13-10

array interface network tuning, 16-3

assigning RBS to transactions, 15-30

asynchronous I/O, 19-17

asynchronous operation, 19-17

asynchronous readahead, 19-28

audit trail, 4-4

B*-tree index, 10-16, 10-20

backup, 19-26

data warehouse, 6-8

BACKUP_DISK_IO_SLAVES parameter,
19-17

BACKUP_TAPE_IO_SLAVES parameter, 19-17

bandwidth, 19-2

BEGIN_DISCRETE_TRANSACTION
procedure, 11-2, 11-4

benefit of tuning, 2-3

bind variables, shared SQL areas, 14-18

BITMAP CONVERSION row source, 10-20

bitmap index, 6-7, 10-14, 10-19

creating, 10-17

inlist iterator, 21-12

maintenance, 10-16

storage considerations, 10-15

when to use, 10-14

BITMAP keyword, 10-17

BITMAP_MERGE_AREA_SIZE parameter, 10-16, 10-19

block size, 15-15

block contention, 2-12

bottlenecks

disk I/O, 15-21

memory, 14-2

buffer cache, 2-11

memory allocation, 14-32

partitioning, 14-40

reducing cache misses, 14-32

buffer pool

multiple, 14-38, 14-39

recycle cache, 14-39

size, 19-45

syntax, 14-42

BUFFER_POOL clause, 14-42

BUFFER_POOL_name parameter, 14-41

buffers

determining number to add, 14-34

when to reduce number of, 14-34

business rule, 1-8, 2-3

tuning, 2-7

BUSY column

V$DISPATCHER table, 18-6

CACHE hint, 8-29

cardinality, 10-21

CATPARR.SQL script, 14-31

chained rows, 15-32

channel bandwidth, 3-6

checkpoints

choosing checkpoint frequency, 15-41

current write batch size, 15-44

effect on recovery time performance,
15-41

effect on runtime performance, 15-41

redo log maintenance, 15-41

signalling DBWR to write, 15-41

tuning, 15-41

CHOOSE hint, 8-14

CKPT process, 15-43

client/server round trip, 13-5

client/server applications, 5-9

CLUSTER hint, 8-16

clusters, 10-2

columns for indexes, 10-7

COMPATIBLE parameter, 10-17, 19-32

composite indexes, 10-8

COMPUTE option, 19-42

CONNECT BY, 21-14

Connection Manager, 16-5

connection pooling, 18-9

consistency, read, 13-8

consistent gets statistic, 14-29, 18-5, 18-20

calculating hit ratio, 14-33, 14-36

consistent mode, 22-13

constraint

contention

redo allocation latch, 18-17

redo copy latches, 18-17

rollback segments, 18-4

tuning, 18-1

tuning resource, 2-12

context area, 2-11

context switching, 13-5

cost-based optimization, 6-7, 8-2

parallel query, 19-67

using hints with, 19-67

COUNT column

count column

CPU bound operations, 19-28

CPU

checking utilization, 13-4

detecting problems, 13-4

insufficient, 3-5

solving problems, 13-4

system architecture, 13-10

tuning, 13-1

underutilized, 19-2

utilization, 13-2

cpu column

CREATE CLUSTER command, 10-4

CREATE INDEX command, 19-64

examples, 15-38

NOSORT option, 15-38

CREATE TABLE AS SELECT, 6-3, 19-62,
19-71

CREATE TABLE command

examples, 15-25

STORAGE clause, 15-25

TABLESPACE clause, 15-25

CREATE TABLESPACE command

DATAFILE clause, 15-24

examples, 15-24

CREATE_BITMAP_AREA_SIZE parameter, 10-16, 10-19

current column

number of buffers retrieved, 22-13

current mode

CURSOR_NUM column

TKPROF_TABLE, 22-19

CURSOR_SPACE_FOR_TIME parameter, 14-19

data

comparative, 4-5

sources for tuning, 4-2

volume, 4-2

data block size, 15-15

data cache, operating system, 17-2

data design tuning, 2-8

data dictionary cache, 2-11, 4-3

reducing cache misses, 14-22

data warehouse

backup, 6-8

Oracle Parallel Server, 6-5

parallel aware optimizer, 6-6

parallel CREATE TABLE AS SELECT,
6-3

parallel index creation, 6-3

parallel load, 6-4

parallel query, 6-6

partition, 6-4

partitioned table, 19-27

recovery, 6-8

star schema, 6-7

database, logical structure, 10-6

database administrator (DBA), 1-8

database layout, parallel execution, 19-18

Database Writer process (DBWR)

behavior on checkpoints, 15-41

datafile, placement on disk, 15-21

DATAFILE clause, 15-24

DATE_OF_INSERT column

TKPROF_TABLE, 22-19

db block gets statistic, 14-29, 18-5, 18-20

calculating hit ratio, 14-33, 14-36

DB_BLOCK_BUFFERS parameter, 14-41

and internal write batch size, 15-43

reducing buffer cache misses, 14-32

removing unneeded buffers, 14-34

DB_BLOCK_CHECKPOINT_BATCH
parameter, 15-44

DB_BLOCK_LRU_EXTENDED_
STATISTICS parameter, 14-31-32

DB_BLOCK_LRU_LATCHES parameter,
14-41, 14-46

DB_BLOCK_LRU_STATISTICS parameter, 14-31, 14-35

DB_BLOCK_SIZE parameter, 19-15

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 15-38, 19-15

DBA locking, 19-55

DBA_DATA_FILES view, 19-76

DBA_EXTENTS view, 19-76

DBA_HISTOGRAMS, 8-4

DBA_INDEXES view, 10-17

DBA_OBJECTS view, 14-39

DBA_TAB_COLUMNS, 8-4

DBMS_APPLICATION_INFO package,
24-3, 24-5

DBMS_SHARED_POOL

DBMS_SHARED_POOL package, 12-4,
14-14, 14-25

DBMS_SYSTEM package, 22-6

DBMS_SYSTEM.SET_SQL_TRACE_IN_
SESSION procedure, 22-6

DBMS_UTILITY.ANALYZE_PART_
OBJECT, 19-42

DBMSPOOL.SQL, 12-4, 14-14

DBMSUTL.SQL, 24-3

DBWR tuning, 13-8, 19-40

DBWR_IO_SLAVES parameter, 19-17

decision support, 5-4

processes, 19-46

query characteristics, 19-3

systems (DSS), 1-2

tuning, 19-2

with OLTP, 5-6

decomposition of SQL statements, 9-4

default cache, 14-39

degree of parallelism, 19-74

demand rate, 1-5, 1-6

DEPTH column, TKPROF_TABLE, 22-19

design dictionary, 4-5

designing and tuning, 2-10

device bandwidth, 3-6, 15-16

device latency, 3-6

diagnosing tuning problems, 3-1

dictionary analysis and design, 4-5

dimension table, 6-7

direct-load insert, 19-19, 19-41, 19-86

external fragmentation, 19-55

disabled constraint, 10-12

discrete transactions

disk affinity

and parallel query, 19-58

disabling with MPP, 19-22

with MPP, 19-38

disk column

disk speed characteristics, 15-3

DISK_ASYNC_IO parameter, 19-17

disks

avoiding contention, 15-21

distributing I/O, 15-21

I/O requirements, 15-4

layout options, 15-15

monitoring OS file activity, 15-17

number required, 15-4

placement of datafiles, 15-21

placement of redo log files, 15-21

testing performance, 15-5

dispatcher processes (Dnnn), 18-8

parallel operations, 19-76

distributed query, 9-1, 9-7

distributing I/O, 15-21, 15-24

DIUTIL package, 12-4

DML_LOCKS parameter, 19-12, 19-13

DSS memory, 19-4

dynamic extension, 15-27

dynamic performance views

enabling statistics, 22-4

for tuning, 20-1

elapsed column, SQL trace, 22-13

enabled constraint, 10-12

enabling SQL trace facility, 22-5

enforced constraint, 10-12

ENQUEUE_RESOURCES parameter, 19-12, 19-13

Enterprise Manager, 4-8

equijoin, 7-10

errors

common tuning, 2-14

during discrete transactions, 11-3

ESTIMATE option, 19-42

examples

executable code as data source, 4-4

execution plans, 21-2

examples, 7-8, 22-8

parallel operations, 19-71

TKPROF, 22-8, 22-9

expectations for tuning, 1-10

Expert, Oracle, 4-13

EXPLAIN PLAN command, 19-74, 21-3

examples of output, 7-8, 19-74, 21-13,
22-15

introduction, 4-6

invoking with the TKPROF program,
22-9

parallel query, 19-70

PLAN_TABLE, 21-3

query parallelization, 19-74

SQL decomposition, 9-3

extension, recursive calls, 15-27

extents

size, 19-32

file storage, designing, 15-5

FIRST_ROWS hint, 8-13, 19-5

fragmentation, external, 19-55

free lists

adding, 18-20

contention, 18-19

reducing contention, 18-20

FREELISTS, 19-40

FULL hint, 8-15, 10-10

full table scans

example, 7-9

GC_FILES_TO_LOCKS parameter, 19-55

GC_ROLLBACK_LOCKS parameter, 19-56

GC_ROLLBACK_SEGMENTS parameter, 19-56

GETMISSES column

V$ROWCACHE table, 14-21, 14-22

GETS column

V$LATCH table, 18-15

V$ROWCACHE table, 14-21, 14-22

global dynamic performance view, 19-76

global index, 19-39

goals for tuning, 1-9, 2-13

GROUP BY

decreasing demand for, 19-49

example, 19-75

GROUP BY NOSORT, 15-39

GV$FILESTAT view, 19-76

hash area, 2-11, 19-46

HASH hint, 8-16

hash join, 19-4, 19-46

HASH parameter

CREATE CLUSTER command, 10-4

HASH_AJ hint, 8-16, 19-14

HASH_AREA_SIZE parameter, 19-4

and parallel query, 19-4

example, 19-49

relationship to memory, 19-48

HASH_MULTIBLOCK_IO_COUNT
parameter, 19-16

hashing, how to use, 10-3

HASHKEYS parameter

CREATE CLUSTER command, 10-4

hints, 8-10

histogram

creating, 8-3

number of buckets, 8-4

viewing, 8-4

HOLD_CURSOR, 14-11

I/O

analyzing needs, 15-2, 15-3

asynchronous, 19-17

balancing, 15-23

distributing, 15-21, 15-24

insufficient, 3-6

multiple buffer pools, 14-39

parallel execution, 19-2

striping to avoid bottleneck, 19-21

testing disk performance, 15-5

tuning, 2-12, 15-2

ID column

IDLE column

V$DISPATCHER table, 18-6

INDEX hint, 8-17, 10-10, 10-18

index join, 19-49

INDEX_ASC hint, 8-18

INDEX_COMBINE hint, 10-18

INDEX_DESC hint, 8-19

INDEX_FFS hint, 8-19

indexes

INDX column

SESSION_CACHED_CURSORS, 14-20

INITIAL extent size, 19-32, 19-55

initialization parameters

DISCRETE_TRANSACTIONS_
ENABLED, 11-3

for parallel query, 19-3

MAX_DUMP_FILE_SIZE, 22-4

OPTIMIZER_MODE, 8-9, 8-12

PRE_PAGE_SGA, 14-6

SORT_DIRECT_WRITES, 15-39

SORT_WRITE_BUFFER_SIZE, 15-39

SORT_WRITE_BUFFERS, 15-39

SQL_TRACE, 22-6

TIMED_STATISTICS, 22-4

USER_DUMP_DEST, 22-4

inlists, 8-18, 8-20

INSERT functionality, 19-82

INSERT, append, 8-27

integrity constraint, 10-13

internal write batch size, 15-43

ISOLATION LEVEL, 11-6

keep cache, 14-39

KEEP procedure, 12-6

latches

contention, 2-12, 13-9

redo allocation latch, 18-14

redo copy latches, 18-14

least recently used list (LRU), 13-8

LGWR, tuning I/O, 15-43

LGWR_IO_SLAVES parameter, 19-17

library cache, 2-11

memory allocation, 14-17

tuning, 14-15

listening queue, 16-4

load, parallel, 6-4, 19-35

load balancing, 6-5, 15-23

local index, 19-39

local striping, 19-23

lock contention, 2-12

log, 18-13

log buffer, 2-11, 14-8

log switches, 15-42

Log Writer process (LGWR)

tuning, 15-21

LOG_BUFFER parameter, 14-8, 15-43

and parallel execution, 19-11

setting, 18-13

LOG_CHECKPOINT_INTERVAL
parameter, 15-42

LOG_CHECKPOINT_TIMEOUT
parameter, 15-42

LOG_SIMULTANEOUS_COPIES
parameter, 18-14, 18-17

LOG_SMALL_ENTRY_MAX_SIZE
parameter, 18-14, 18-17

LOGGING option, 19-41, 19-83

logical structure of database, 2-9, 10-6

LRU

aging policy, 14-38

latch, 14-41, 14-46

latch contention, 14-46, 18-18

Managment Information Base (MIB), 4-6

massively parallel system, 19-2

max session memory statistic, 14-23

MAX_DUMP_FILE_SIZE, 22-4

MAXEXTENTS keyword, 19-32, 19-55

MAXOPENCURSORS, 14-11

media recovery, 19-37

memory

configure at 2 levels, 19-3

insufficient, 3-5

process classification, 19-46

reducing usage, 14-48

memory allocation

shared SQL areas, 14-17

sort areas, 15-35

tuning, 14-2, 14-47

users, 14-7

memory/user/server relationship, 19-44

MERGE_AJ hint, 8-20, 19-14

message rate, 3-7

method

applying, 2-13

tuning steps, 2-1, 2-4

MIB, 4-6

migrated rows, 15-32

MINEXTENT, 19-55

mirroring, 19-26

redo log files, 15-22

MISSES column

V$LATCH table, 18-15

monitoring the system, 4-6

MPP

disk affinity, 19-22

MTS_DISPATCHERS parameter, 18-8

POOL attribute, 18-9

MTS_MAX_DISPATCHERS parameter

tuning dispatchers, 18-8

MTS_MAX_SERVERS parameter

tuning servers, 18-11

multi-block reads, 15-28

MULTIBLOCK_READ_COUNT parameter, 19-32

multiple archiver processes, 19-40

multiple buffer pools, 14-38, 14-39, 14-42

multi-purpose applications, 5-6

multi-threaded server, 19-46

context area size, 2-11

reducing contention, 18-6

shared pool and, 14-23

tuning, 18-6

multi-tier systems, 13-11

NAMESPACE column

V$LIBRARYCACHE table, 14-15

nested loop join, 19-28, 19-46

nested query, 19-61

network

array interface, 16-3

bandwidth, 3-7

constraints, 3-7

detecting performance problems, 16-2

prestarting processes, 16-3

problem solving, 16-3

Session Data Unit, 16-4

tuning, 16-1

NEXT extent, 19-55, 19-83

NO_MERGE hint, 8-24

NOAPPEND hint, 8-28, 19-82

NOARCHIVELOG mode, 19-41

NOCACHE hint, 8-29

NOLOGGING option, 19-38, 19-41, 19-62, 19-64, 19-82

NOPARALLEL attribute, 19-60

NOPARALLEL hint, 8-27

NOSORT option, 15-38

choosing when to use, 15-38

CREATE INDEX command, 15-38

examples, 15-38

GROUP BY, 15-39

performance benefits, 15-38

NOT IN operator, 19-13

NT performance, 17-6

OBJECT_INSTANCE column

OBJECT_NAME column

OBJECT_NODE column, 19-75

OBJECT_OWNER column

OBJECT_TYPE column

online redo log size, 15-42

online transaction processing (OLTP),
1-2, 5-2

processes, 19-46

with decision support, 5-6

OPEN_CURSORS parameter

allocating more private SQL areas, 14-11

increasing cursors per session, 14-17

operating system

data cache, 17-2

monitoring disk I/O, 15-17

monitoring tools, 4-3

striping, 19-21, 19-22

tuning, 2-12, 3-7, 14-5

OPERATION column

OPTIMAL storage parameter, 15-30

values, 21-7

optimization

choosing an approach and goal for, 8-2

OPTIMIZER column

OPTIMIZER_GOAL option

ALTER SESSION command, 8-6

OPTIMIZER_MODE, 6-7, 8-3, 8-9, 8-12,
19-67

OPTIMIZER_PERCENT_PARALLEL
parameter, 6-6, 19-70

parallel execution, 19-5

OPTIONS column

parsing and private SQL areas, 14-12

Oracle striping, 19-22

Oracle Call Interface (OCI)

Oracle Expert, 2-1, 4-13

Oracle Forms, 22-5

parsing and private SQL areas, 14-12

Oracle Network Manager, 16-4

Oracle Parallel Server, 5-9, 6-5

CPU, 13-12

disk affinity, 19-58

parallel load, 19-35

parallel query, 19-10, 19-55

ST enqueue, 19-54

synchronization points, 2-8

Oracle Performance Manager, 4-9

Oracle Precompilers

parsing and private SQL areas, 14-11

Oracle Server

client/server configuration, 5-9

configurations, 5-7

Oracle Tablespace Manager, 4-12

Oracle TopSessions, 4-10

Oracle Trace, 4-11, 14-45, 23-1

command line interface, 23-7

detail report, 23-9

formatting data, 23-10

parameters, 23-4

Oracle Trace Manager, 23-4

ORACLE_TRACE_COLLECTION_NAME parameter, 23-5

ORACLE_TRACE_ENABLE parameter, 23-4

ORACLE_TRACE_FACILITY_NAME
parameter, 23-5

ORDER BY, 21-14

decreasing demand for, 19-49

order, preserving, 21-14

ORDERED hint, 8-21

OTHER column

oversubscribing resources, 19-47, 19-51

OTHER_TAG column, 19-74

overhead, process, 19-46

overloaded disks, 15-21

packages

DBMS_APPLICATION_INFO, 24-3,
24-5

DBMS_SHARED_POOL, 12-4

DBMS_TRANSACTION, 11-5

DIUTIL, 12-4

registering with the database, 4-7, 24-2

STANDARD, 12-4

page table, 13-4

paging, 3-5, 13-5, 19-47, 19-71, 19-81

paging subsystem, 19-48

parallel aware optimizer, 6-6

PARALLEL clause, 19-83

and PDML, 19-82

PARALLEL CREATE INDEX statement,
19-11

PARALLEL CREATE TABLE AS SELECT

data warehousing, 6-3

external fragmentation, 19-55

resources required, 19-11

parallel Data Manipulation Lanugage

examples, 19-86

parallel execution

introduction, 19-2

resource parameters, 19-3

tuning parallel servers, 19-77

tuning physical database layout, 19-18

PARALLEL hint, 8-26, 19-60, 19-70, 19-83

and PDML, 19-82

parallel index, 19-64

creation, 6-3

parallel load, 6-4

example, 19-35

Oracle Parallel Server, 19-35

using, 19-29

parallel query, 5-5, 6-6

parallel server, 5-9

disk affinity, 19-58

parallel query tuning, 19-55

PARALLEL_MAX_SERVERS parameter,
19-6, 19-8, 19-9, 19-48

and parallel query, 19-6

and SHARED_POOL_SIZE, 19-9

PARALLEL_MIN_PERCENT parameter,
19-6

PARALLEL_MIN_SERVERS parameter,
19-8

PARALLEL_TRANSACTION_RESOURCE_TIMEOUT parameter, 19-59

parallelism

degree on parallel server, 19-10

degree, overriding, 19-60

degree, with parallel query, 19-28

PARALLEL-TO-PARALLEL keyword, 19-75

PARAMETER column

V$ROWCACHE tabe, 14-21

parameter file, 4-4

PARENT_ID column

Oracle Call Interface (OCI), 14-12

parsing, 13-7

Oracle Forms, 14-12

Oracle Precompilers, 14-11

reducing unnecessary calls, 14-11

partition view, 6-4

partitioned table, 6-4

data warehouse, 19-27

example, 19-33

parallel grouping, 19-75

PCM lock, 19-55

PCTFREE, 2-12, 15-33

PCTINCREASE parameter, 15-37

and SQL.BSQ file, 15-34

PCTUSED, 2-12, 15-33

performance

client/server applications, 5-9

decision support applications, 5-4

different types of applications, 5-2

Performance Monitor, NT, 13-4

evaluating, 1-10

key factors, 3-3

mainframe, 17-6

monitoring registered applications, 4-7, 24-2

NT, 17-6

OLTP applications, 5-2

Parallel Server, 5-9

UNIX-based systems, 17-5

Performance Manager, 4-9

PHYRDS column

V$FILESTAT table, 15-19

physical database layout

parallel execution, 19-18

physical reads statistic, 14-29

calculating hit ratio, 14-33, 14-36

PHYWRTS column

V$FILESTAT table, 15-19

ping UNIX command, 4-3

pinging, 2-12

PINS column

V$LIBRARYCACHE table, 14-16

PL/SQL

package, 4-7

tuning PL/SQL areas, 14-9

PLAN_TABLE table, 21-3- 21-5

POOL attribute, 18-9

POSITION column

PRE_PAGE_SGA parameter, 14-6

PRIMARY KEY constraint, 10-12, 10-13,
19-64

private SQL areas, 14-11

proactive tuning, 2-2

process

classes of parallel query, 19-20, 19-22,
19-38, 19-47

dispatcher process configuration, 18-8

DSS, 19-46

maximum number, 19-44

maximum number for parallel query,
19-44

maximum number of, 3-7

OLTP, 19-46

process priority, 17-3

process scheduler, 17-3

processing, distributed, 5-9

production systems, tuning, 2-4

PRVTPOOL.PLB, 12-4

queries

avoiding the use of indexes, 10-10

distributed, 9-1

ensuring the use of indexes, 10-9

query column

read/write operations, 15-5

query plan, 21-2

query server process

tuning, 18-11, 19-77

RAID, 15-26, 19-26, 19-37

read consistency, 13-8

REBUILD, 10-11

record keeping, 2-14

recovery

data warehouse, 6-8

effect of checkpoints, 15-41

media, with striping, 19-26

recursive calls, 15-27

detected by the SQL trace facility, 22-14

dynamic extension, 15-27

statistic, 15-27

recursive SQL, 12-2

recycle cache, 14-39

redo allocation latch, 18-14

contention, 18-17

redo buffer allocation retries statistic, 18-13

redo copy latches, 18-14

choosing how many, 18-14, 18-17

contention, 18-17

creating more, 18-17

redo log archiver processes, 19-40

redo log buffer

tuning, 14-8

redo log files

mirroring, 15-22

placement on disk, 15-21

tuning checkpoints, 15-42

reducing

buffer cache misses, 14-32

contention

dispatchers, 18-6

OS processes, 17-3

query servers, 18-12

redo log buffer latches, 18-13

shared servers, 18-10

data dictionary cache misses, 14-22

disk contention, 15-21

library cache misses, 14-16

number of database buffers, 14-34

paging and swapping, 14-5

rollback segment contention, 18-5

unnecessary parse calls, 14-11

registering applications with database, 4-7, 24-2

regression, 19-70, 19-71

RELEASE_CURSOR, 14-11

RELOADS column

V$LIBRARYCACHE table, 14-16

REMARKS column

remote-mapped statement, 9-2

reparsing, 13-7

resource

adding, 1-5

oversubscribing, 19-47

oversubscription, 19-51

parallel query usage, 19-3

tuning contention, 2-12

response time, 1-2, 1-4

optimizing, 8-6, 8-13

roles in tuning, 1-8

rollback segments, 13-8, 19-11

assigning to transactions, 15-30

choosing how many, 18-5

contention, 18-4

creating, 18-5

detecting dynamic extension, 15-27

dynamic extension, 15-30

ROLLBACK_SEGMENTS parameter, 19-11

round trip, client/server, 13-5

ROWID hint, 8-16

rows column

rule-based optimization, 8-9

RULE hint, 8-14, 19-67

sar UNIX command, 13-4, 19-81

scalability, 6-5, 13-9, 19-73

scheduling processes, 13-5

SEARCH_COLUMN column

serializable transactions, 11-6

segments, 15-27

selectivity

indexes, 10-7

sequence cache, 2-11

sequential reads, 15-5

sequential writes, 15-5

Server Manager

SHOW SGA command, 14-7

server/memory/user relationship, 19-44

service time, 1-3, 1-4

Session Data Unit (SDU), 16-4

session memory statistic, 14-23

SESSION_CACHED_CURSORS parameter, 13-7, 14-20

SET TRANSACTION command

assigning transactions to RBS, 15-30

SGA size, 14-8, 19-4

keeping objects pinned in, 12-4

tuning, 14-13, 14-25

shared SQL areas

finding large areas, 12-5

identical SQL statements, 12-2

keeping in the shared pool, 12-4

memory allocation, 14-17

statements considered, 12-2

SHARED_POOL_RESERVED_MIN_ALLOC parameter, 14-25, 14-27

SHARED_POOL_RESERVED_SIZE
parameter, 14-25, 14-26

SHARED_POOL_SIZE parameter, 14-27

allocating library cache, 14-17

and parallel query, 19-9

on parallel server, 19-10

reducing dictioanry cache misses, 14-22

tuning the shared pool, 14-23

SHOW SGA command, 14-7

Simple Network Management Protocol
(SNMP), 4-6

single tier, 13-10

size, buffer pool, 19-45

SIZES procedure, 12-5

skew, workload, 19-72

SLEEPS column

V$LATCH table, 18-15

SNMP, 4-6

sort areas

memory allocation, 15-35

process local area, 2-11

sort merge join, 19-46

SORT_AREA_RETAINED_SIZE parameter, 14-47, 15-37

SORT_AREA_SIZE parameter, 10-16, 14-47

and parallel execution, 19-10

tuning sorts, 15-36

SORT_DIRECT_WRITES parameter, 15-39

and parallel query, 19-16

SORT_READ_FAC parameter, 15-38

and parallel query, 19-16

SORT_WRITE_BUFFERS, 15-39

sorts

avoiding on index creation, 15-38

tuning, 15-35

sorts (disk) statistic, 15-35

sorts (memory) statistic, 15-35

source, of data for tuning, 4-2

space management, 19-37

parallel query, 19-54

reducing transactions, 19-54

speed, disk, 15-3

spin count, 13-9

SPINCOUNT parameter, 13-9, 18-2

SQL areas, tuning, 14-9

SQL Loader, 19-29

SQL statements

avoiding the use of indexes, 10-10

decomposition, 9-4

ensuring the use of indexes, 10-9

inefficient, 13-7

modifying indexed data, 10-8

recursive, 12-2

reparsing, 13-7

tuning, 2-10

SQL trace facility, 4-6, 14-9, 14-45, 22-2, 22-7

enabling, 22-5

example of output, 22-15

output, 22-13

parse calls, 14-9

steps to follow, 22-3

SQL_STATEMENT column

SQL_TRACE parameter, 22-6

SQLUTLCHAIN.SQL, 4-7

ST enqueue, 19-54

STANDARD package, 12-4

STAR hint, 8-21

star query, 6-7

star schema, 6-7

star transformation, 6-8, 8-22

STAR_TRANSFORMATION hint, 6-8, 8-22

STAR_TRANSFORMATION_ENABLED
parameter, 6-8, 8-22

STATEMENT_ID column

CREATE TABLE command, 15-25

statistics, 20-2

STORAGE clause

examples, 15-25

modifying parameters, 15-34

modifying SQL.BSQ, 15-34

OPTIMAL, 15-30

parallel query, 19-64

storage, file, 15-5

stored procedures

BEGIN_DISCRETE_TRANSACTION, 11-3

KEEP, 12-6

READ_MODULE, 24-8

registering with the database, 4-7, 24-2

SET_ACTION, 24-5

SET_CLIENT_INFO, 24-6

SET_MODULE, 24-4

SIZES, 12-5

UNKEEP, 12-6

striping, 15-23, 19-21

and disk affinity, 19-58

example, 19-29

examples, 15-24

local, 19-23

manual, 15-24, 19-21

media recovery, 19-26

operating system, 19-22

operating system software, 15-26

Oracle, 19-22

temporary tablespace, 19-37

subquery, correlated, 19-61

swapping, 3-5, 13-4, 13-5

library cache, 14-17

reducing, 14-5

SGA, 14-47

switching processes, 13-5

symmetric multiprocessor

parallel query benefits, 19-2

System Global Area (SGA)

tuning, 14-6

system-specific Oracle documentation

software constraints, 3-7

SPIN_COUNT parameter, 13-9

USE_ASYNC_IO, 19-17

table queue, 19-75, 19-78

tables

placement on disk, 15-22

striping examples, 15-24

tablespace

creating, example, 19-31

dedicated temporary, 19-37

temporary, 15-37

TABLESPACE clause

CREATE TABLE command, 15-25

examples, 15-25

Tablespace Manager, 4-12

TAPE_ASYNC_IO parameter, 19-17

TCP.NODELAY option, 16-4

temporary extent, 19-37

TEMPORARY keyword, 15-37

temporary tablespace, 19-37

optimizing sort, 15-37

throughput, 1-4, 8-6, 8-12

tier

single, 13-10

two-tier, 13-10

TIMED_STATISTICS parameter, 19-78, 22-4

TIMESTAMP column

command line parameters, 22-9

TKPROF program, 14-45, 22-3, 22-7

editing the output SQL script, 22-17

example of output, 22-15

generating the output SQL script, 22-17

introduction, 4-6

syntax, 22-8

using EXPLAIN PLAN, 22-9

TKPROF_TABLE, 22-18, 22-19

tool, in-house performance, 4-14

TopSessions, 4-10

TOTALQ column

V$QUEUE table, 18-7, 18-10

Trace, Oracle, 4-11, 23-1

transaction processing monitor, 13-11, 13-12

transaction rate, 19-54

transactions

assigning rollback segments, 15-30

discrete, 11-2

serializable, 11-6

TRANSACTIONS parameter, 19-11

transmission time, 3-7

Transparent Gateway, 9-7

tuning

access path, 2-10

and design, 2-10

application design, 2-9

business rule, 2-7

checkpoints, 15-41

client/server applications, 5-9

contention, 18-1

CPU, 13-1

data design, 2-8

data sources, 4-2

database logical structure, 2-9

decision support systems, 5-4

diagnosing problems, 3-1

logical structure of database, 10-6

expectations, 1-10

factors, 3-2

goals, 1-9, 2-13

I/O, 2-12, 15-2

library cache, 14-15

memory allocation, 2-11, 14-2, 14-47

method, 2-1

monitoring registered applications, 4-7, 24-2

multi-threaded server, 18-6

OLTP applications, 5-2

operating system, 2-12, 3-7, 14-5

production systems, 2-4

query servers, 18-11, 19-77

reactive, 2-3

shared pool, 14-13, 14-23

sorts, 15-35

SQL, 2-10

SQL and PL/SQL areas, 14-9

System Global Area (SGA), 14-6

two-phase commit, 19-11

two-tier, 13-10

undo block statistic, 18-4

UNIQUE constraint, 10-12, 10-13, 19-64

UNIQUE index, 10-17

UNIX-based system

performance, 17-5

UNKEEP procedure, 12-6

unlimited extents, 15-29

USE_CONCAT hint, 8-20

USE_MERGE hint, 8-24

USE_NL hint, 8-23

user memory allocation, 14-9

user/server/memory relationship, 19-44

USER_DUMP_DEST, 22-4

USER_HISTOGRAMS, 8-4

USER_ID column

TKPROF_TABLE, 22-19

USER_INDEXES view, 10-17

USER_TAB_COLUMNS, 8-4

V$ dynamic performance views, 4-5

V$BH view, 14-31

V$BUFFER_POOL view, 14-46

V$DATAFILE view, 15-19

V$DISPATCHER view, 18-6

V$FILESTAT view

and parallel query, 19-76

disk I/O, 15-19

PHYRDS column, 15-19

PHYWRTS column, 15-19

V$FIXED_TABLE, 20-2

V$INSTANCE, 20-2

V$LATCH view, 18-2, 20-2

V$LATCH_CHILDREN view, 14-46

V$LATCH_MISSES, 13-9

V$LIBRARYCACHE view, 20-2

NAMESPACE column, 14-15

PINS column, 14-16

RELOADS column, 14-16

using, 14-15

V$LOCK, 20-3

V$MYSTAT, 20-3

V$PARAMETER view, 19-76

V$PQ_SESSTAT view, 19-71, 19-77

V$PQ_SLAVE view, 19-77

V$PQ_SYSSTAT view, 19-71, 19-77

V$PQ_TQSTAT view, 19-72, 19-78

V$PROCESS, 20-3

V$QUEUE view

examining wait times, 18-7

identifying contention, 18-10

V$RESOURCE_LIMIT view, 18-2

V$ROLLSTAT, 20-2

V$ROWCACHE view, 20-2

GETMISSES column, 14-21, 14-22

GETS column, 14-21, 14-22

PARAMETER column, 14-21

performance statistics, 14-21

V$SESSION, 20-3

application registration, 4-7, 24-2

V$SESSION_EVENT view, 20-3

network information, 16-2

V$SESSION_WAIT view, 14-46, 18-2, 20-3

network information, 16-2

V$SESSTAT view, 13-6, 19-81, 20-3

network information, 16-2

using, 14-23

V$SGA, 20-2

V$SGASTAT, 20-2

V$SHARED_POOL_RESERVED view, 14-27

V$SORT_SEGMENT view, 19-54

V$SORT_USAGE view, 7-5

V$SQLAREA, 13-7, 20-2

application registration, 4-7, 24-2, 24-7

resource-intensive statements, 7-5

V$SQLTEXT, 20-2

V$SYSSTAT view, 13-6, 13-7, 19-40, 20-2

detecting dynamic extension, 15-27

examining recursive calls, 15-27

redo buffer allocation, 18-13

redo buffer allocation retries, 19-11

tuning sorts, 15-35

using, 14-28

V$SYSTEM_EVENT view, 13-9, 18-2, 20-2

V$WAITSTAT view, 18-2, 20-2

reducing free list contention, 18-19

rollback segment contention, 18-4

views

instance level, 20-2

tuning, 20-1

virtual memory, 19-4

virtual tables

write batch size, internal, 15-43

vmstat UNIX command, 13-4, 19-81

WAIT column

V$QUEUE table, 18-7, 18-10

wait time, 1-3, 1-4, 19-47

workload, 1-7, 13-2

adjusting, 19-50

exceeding, 19-47

skew, 19-72