Oracle Wait Interface: A Practical Guide to Performance Diagnostics & Tuning (Osborne ORACLE Press Series)

S

sampling

with PL/SQL. See PL/SQL code

with SGA Direct Access. See SGA Direct Access

scans , serial, 127

SCN (System Change Number), 199

SCUR state, buffer cache, 219

SECONDS_IN_WAIT, V$SESSION_WAIT view, 27 ‚ 29

security

direct access SGA and, 318

trace file, 304

segment header contention

buffer busy waits and, 189 ‚ 191

undo, 191 ‚ 192

SEQ# column

log file sync events and, 64

overview of, 27

V$SESSION_WAIT_HISTORY view, 37 ‚ 38

Sequence (SQ) enqueue, 235

serial scans, 127

_SERIAL_DIRECT_READ parameter, 127

service time, 9

session events, 285

session level

diagnosis of db file scattered read events, 113 ‚ 116

diagnosis of direct path read events, 121 ‚ 126

granularity requirement, 80

setting tracing event 10046 at, 30 ‚ 31

SESSION_CACHED_CURSORS parameter, 156

set ID parameter, 206

set_ev procedure parameter, 280 ‚ 281

SGA Direct Access, 317 ‚ 331

attaching to C program, 327 ‚ 331

concurrency and, 319

hidden information, 319

overhead and, 318

references, 337

sampling for performance data, 101

security and, 318

SGA base address, 323 ‚ 324

SGA ID, 322 ‚ 323

speed and, 318 ‚ 319

X$ views, 319 ‚ 322

X$KSUSECST starting address, 324

X$KSUSECST view columns offset, 325 ‚ 327

X$KSUSECST, number of records, 325

X$KSUSECST, record size , 324 ‚ 325

shared memory ID (shmid), 323

SHARED mode, 70

shared pool latches

defining, 58

oversize shared pools and, 156 ‚ 158

overview of, 151 ‚ 153

parsing, 153 ‚ 156

shared server state dumps, 314

SHARED_POOL_SIZE, 169

shmat system call, 327 ‚ 330

shmflg value, 327

shmid (shared memory ID), 323

short-wait latches, 146

SID column

V$SESSION_EVENT view, 24 ‚ 26

V$SESSION_WAIT view, 27

V$SESSION_WAIT_CLASS view, 40

SIGN function, 157

SINGLEBLKRDS, 112

SINGLEBLKRDTIM, 112

SLEEPS statistic

interpreting latch free wait events, 148 ‚ 149

latch contentions and, 151

overview of, 145

short- and long-wait latches, 146

SNAP_IDs

creating/dropping baselines, 251

creating/dropping snapshots, 249

defined, 243

snapshot too old (ORA-01555) error, 201

snapshots

AWR (Automatic Workload Repository), 243 ‚ 244

baselines, 244

creating/dropping, 249 ‚ 250

creating/dropping baselines, 250 ‚ 251

modifying settings, 247 ‚ 248

Solaris operating system

cache buffers chains latch contentions, 163

default hash buckets in, 162

improving DBWR average write times, 133

solid state disks, 202

solitary latches, 144, 145

speed

direct access SGA and, 318 ‚ 319

I/O operations and, 104

spelling, event names , 19 ‚ 20

SPID values, 281

_SPIN_COUNT parameter

latch classification and, 146 ‚ 148

latch free wait events, 148

overview of, 145

spreading hot blocks, 166

SQ (Sequence) enqueue, 235

SQL statements

ADDM analysis of, 269

cache buffers chains latch contentions caused by, 162 ‚ 165

db file scattered reads, diagnosing, 114 ‚ 116

db file sequential reads, diagnosing, 107

direct path writes , diagnosing, 129

free buffer waits events caused by, 207

library cache latch contention, 158 ‚ 159

repository, 91 ‚ 92

response time for, 12 ‚ 13

sampling without. See SGA Direct Access

shared pool/library cache latch contention, 155 ‚ 156

TX enqueue wait in mode 6 and, 179

SQL statistics, 241

SQL trace, 4. See also trace event 10046

SQL*Net message from client events, 65

SQL*Net message to client events, 65 ‚ 66

SQL_ID hash value, 241

sstiomax, 119

ST enqueue wait, 182 ‚ 183

start_snap_id parameter, 250 ‚ 251

STATE column, 27 ‚ 28, 29

STATE=WAITING, in events, 29

statistics

buffer busy waits, 192 ‚ 194

CPU, 9

db file scattered reads, 115 ‚ 116

enqueue wait events, 177

free buffer waits events, 206 ‚ 207

global cache, 227 ‚ 228

hard parse, 154

latches in no-wait mode, 145

latches in willing-to-wait mode, 145

Oracle Database 10 g , 238 ‚ 241

tracking, 72 ‚ 74

wait event, 7

Statspack

comparing AWR to, 243, 251

showing delta information with, 23 ‚ 24

as unsuitable data collector, 81

storage

database logoff triggers and, 85 ‚ 86

Real Application Clusters, 216

subpools, shared pool latch, 151 ‚ 153, 156 ‚ 158

Sun operating system, 225

SV sequence enqueue, 235

sync writes, 198 ‚ 199

synchronous I/O operations, 104, 133

syntax

buffer dumps, 307

buffers dumps, 306

control file dumps, 310

data block dumps, 304 ‚ 305

event specification, 282 ‚ 284

file headers dumps, 308

heap dumps, 311

library cache dumps, 312

processstate dumps, 313

redo log dumps, 316

shared server state dumps, 314

systemstate dumps, 315

SYS user , 318, 322

SYSAUX tablespace, AWR tables, 243, 248

System Change Number (SCN), 199

System I/O wait class, 39

system-level diagnosis

buffer busy waits, 192 ‚ 194

db file scattered read events, 116

db file sequential read events, 110 ‚ 112

systemstate dumps, 315