Index
A
Active Session History (ASH) information, 113, 117, 134–139
ashrpt.sql script, 135
awrrpt.sql script, 135
background events, 135
blocking sessions, 137
circular buffer, 139
data dictionary
DBA_HIST_ACTIVE_SESS_HISTORY view, 142, 144
SESSION_STATE column, 143
time frame, 144
V$ACTIVE_SESSION_HISTORY view, 142–145
DBA_HIST_ACTIVE_SESS_HISTORY view, 139
enterprise manager
DBA_HIST_EVENT_NAME view, 141
filter drop-down menu, 141
performance tuning activities, 140
sample report, 140
SQL_ID, 141
time frames, 140
P1/P2/P3 values, 136
real-time/near real-time session information, 134
report section information, 138
SQL command types, 136
SQL statements, 136
user events, 135
ashrpt.sql script, 140
Automated SQL tuning, 367
ADDM, 368
in AWR
creating SQL tuning set object, 388–389
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY funcion, 389–390
determine begin and end AWR snapshot IDs, 389
viewing resource-intensive, 384–386
DBMS_AUTO_TASK_ADMIN.ENABLE/DISABLE procedure, 380–381
DBMS_SQLTUNE.CREATE_SQLSET procedure, 383–384
DBMS_SQLTUNE.CREATE_TUNING_TASK procedure, 398
SQL ID, Cursor Cache, 399
SQL_ID and AWR snapshot IDs, 399–400
text for SQL statement, 399
diagrammatic representation, 369
with enterprise manager, 379
in memory
CAPTURE_CURSOR_CACHE_SQLSET parameter descriptions, 392
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure, 391–392
DBMS_SQLTUNE.SELECT_CURSOR_CACHE funcion, 390–391
viewing resource-intensive, 386–388
maintenance task view descriptions, 371
modifying maintanence window, 382
automatic SQL tuning, 382
segment advice, 382
statistics gathering, 382
SQL Tuning Advisor, 368
from enterprise manager, 403–404
optimizer tuning modes, 402
from SQL Developer, 403
steps to run manually, 401
SQL tuning set, 368
transporting to another database, 396–398
tuning advice, 372
DBMS_AUTO_SQLTUNE package, 372
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK parameter, 377–379
error section, 375
findings section, 375
general information section, 374
REPORT_AUTO_TUNING_TASK function, 376
SCRIPT_TUNING_TASK function, 377
summary section, 374
Automatic Database Diagnostic Monitor (ADDM), 92, 368
DBMS_ADDM Package, 406
performance recommendations, 407
SQL*plus script, 405
Automatic Diagnostic Repository Command Interpreter (ADRCI), 229
ADR base, 230
in batch mode, 230
diagnostic tasks, 231
HELP command, 230
in interactive mode, 229
V$DIAG_INFO view, 231
Automatic memory management, 83
buffer pool, 87
caching client result sets, 103
advantages, 104
CLIENT_RESULT_CACHE_LAG, 103
CLIENT_RESULT_CACHE_SIZE, 103
OCIStmtExecute(), 105
OCIStmtFetch(), 105
optional client-side configuration file, 104
caching PL/SQL function, 105
considerations, 108
restrictions, 108
caching SQL query result, 99
read consistency requirements, 102–103
RESULT_CACHE_MODE initialization parameter, 100
table annotations and query hints, 101–102
configuring server query cache
DBMS_RESULT_CACHE.FLUSH procedure, 96, 97
initialization parameters, 95–96
materialized views, 96
PL/SQL collection, 96
RESULT_CACHE_MAX_RESULT, 96
RESULT_CACHE_MAX_SIZE, 96
RESULT_CACHE_REMOTE_EXPIRATION, 97
DBCA, 85
managing server result cache, 97
DBMS_RESULT_CACHE.STATUS(), 97–99
shared pool percentage, 99
memory resizing operations
V$MEMORY_RESIZE_OPS, 91
V$MEMORY_TARGET_ADVICE, 90
memory structures
MEMORY_MAX_TARGET PARAMETER, 86, 87
MEMORY_TARGET parameter, 84, 85
optimizing memory usage, 91
ADDM reports, 92
Memory Size Advice graph in DB, 91
tuning steps, 91
Oracle Database 11g, 85
Oracle Database Smart Flash Cache, 109–110
DB_FLASH_CACHE_FILE, 109
DB_FLASH_CACHE_SIZE, 109
PGA memory allocation
AWR, 95
PGA_AGGREGATE_TARGET parameter, 93, 94
steps, 93
V$SQL_WORKAREA_HISTOGRAM, 94
V$SYSSTAT and V$SESSTAT, 95
pga_memory_target, 84
redo log buffer tuning, 110–112
SCOPE parameter, 84
setting minimum values, 89
Automatic segment space management (ASSM), 5
Automatic workload repository (AWR), 21, 95, 113
active session information. See Active Session History information
baseline statistics
adaptive metrics, 125
AWR_REPORT_TEXT function, 131
awrextr.sql script, 130
awrload.sql script, 130
awrrpt.sql script, 130
CREATE_BASELINE_TEMPLATE procedure, 131–132
DBA_HIST_BASELINE_TEMPLATE view, 132
DROP_BASELINE procedure, 129
DROP_BASELINE_TEMPLATE procedure, 132
fixed baselines, 123
performance statistics, 123
RENAME_BASELINE procedure, 129
via enterprise manager, 126–128
categories, 115
historical database performance statistics, 113
interval and retention periods, 116–117
interval-based historical statistics, 114
report generation
data dictionary, 119
database instance, 119
report name, 119
report type, 118
snapshot ids, 119
via enterprise manager, 120–121
statistical components, 114–115
STATISTICS_LEVEL parameter, 114
time frame, 113
type of information, 115
UTLBSTAT/UTLESTAT and Statspack, 113
AUTOSTATS_TARGET parameter, 456
B
Bitmap join index, 73
Bottlenecks
iostat command, 198
AWR, 200
column descriptions, 199
examining the output, 198
Statspack, 200
V$ views, 200
network-intensive process, 201–202
Solaris system, 192
prstat utility, 193
vmstat, 190
interpreting the output, 191
output columns, descriptions, 191–192
B-tree indexs, 50
DBMS_SPACE CREATE_INDEX procedure, 50
index blocks
Index fast full scan, 47
Index range scan, 47
Oracle's Autotrace utility, 47
INDEX RANGE SCAN, 49
ROWID, 46
table layout, 46
technical aspects, 45
C
Cartesian join, 261
Center of Expertise (CoE), 342
COALESCE function, 280
Contention, 147
analyzing Oracle wait events, 151–152
DML locks, 168
exclusive locks, 164
long-term strategy, 167
shared locks, 164
short-term strategy, 167
transaction locks, 168
V$LOCK view, 165
V$SESSION view, 166
buffer busy waits, 157
segment header, 157
undo header and undo block, 158
identifying locked object, 168–169
indentifying SQL statements, 150–151
latch contention, 178
cache buffer chains, 179
cache buffers LRU chain, 179
CURSOR_SHARING parameter, 180
shared pool and library latches, 179
log file sync wait events, 158–160
Oracle Enterprise Manager
Oracle wait interface, 147
read by other session wait event, 160–161
recent wait events in database, 174–175
recently locked sessions, database, 171–174
reducing direct path read wait events, 161–162
simultaneous requests, SGA, 147
time spent waiting, locking, 175–178
transaction locks, 147
understanding response time, 147
detailed information, wait event, 148–149
processing time, 148
time model statistics, 149
wait time, 148
understanding wait class events, 152
Application wait class, 152, 153
User I/O wait class, 152
wait classes, 154
concurrency issues, 157
CONTROL_MANAGEMENT_PACK_ACCESS parameter, 115
Correlated subqueries, 267
EXISTS clause, 268
NOT EXISTS, 269
Cost-based optimizer (CBO), 335, 358
CPUspeedNW system statistics, 465
CREATE DATABASE script
creates automatic UNDO tablespace, 3
default tablespace, USERS, 3
default temporary tablespace, TEMP, 3
online redo logs, 4
placing datafiles in directories, 4
sets passwords, DBA-related users, 4
SYSTEM tablespace, 3
Cross join, 261
Cursor leak, 223
cursor_sharing initialization parameter, 473–476
D
Database Configuration Assistant (DBCA), 85
Data loading speeds
db file scattered read wait event, 156
db file sequential read wait event, 156
DBMS_AUTO_TASK_ADMIN package, 448
DBMS_MONITOR package, 327
DBMS_WORKLOAD_REPOSITORY package, 124–126, 129–131
DBMS_WORKLOAD_REPOSITORY PL/SQL package, 116
Degree of parallelism (DOP), 452, 526, 527
E
Estimate_percent parameter, 453, 457
Execution plan, SQL
DBMS_XPLAN.DISPLAY function, 303–306
ALL, 304
BASIC, 304
cost information, 305
format options, 304
SERIAL, 304
TYPICAL, 304
DISPLAY function, 303
identifying resource-consuming SQL statements
DBA_HIST_SQL_PLAN view, 320
DBA_HIST_SQLSTAT view, 319–320
DBA_HIST_SQLTEXT view, 319
monitoring
DBMS_SQLTUNE.REPORT_SQL_MONITOR function, 317–318
V$SQL_PLAN_MONITOR view, 316–318
optimization, 409
initialization parameters, 410, 412
out-of-the-box settings, 410
plan baselines, 412
SQL profiles, 412–417. See also SQL profiles
statistics, 412
stored outlines, 412
reading
AUTOTRACE, 307
factors, 309
Join methods, 310
query processing, steps, 308
resource-consuming SQL statements, 311–312
SQL performance Analyzer
AWR snapshots, 324
considerations, 324
creating analysis task, 321
DBA_ADVISOR, 325
executing analysis task, 322
reporting analysis task function, 322, 325
F
G
GRANULARITY parameter, 455
H
Hybrid columnar compression, 38–39
I
INCREMENTAL preference, 455–456
Index, 43
bitmap, 44
bitmap index, star schema, 72
B-tree cluster, 44
B-tree. See B-tree indexes
compression, 63
advantages, 64
COMPRESS N clause, 63
creating aspects, 43
deciding which columns to index
foreign key columns, 51
index creation and maintenance guidelines, 53
index creation standards, 51
index with NOSEGMENT clause, 53–54
primary key constraint, 51
unique key constraint, 51
domain, 45
freeing up unused space, 78
function-based, 44, 64, 65, 66
global partitioned, 45
Hash cluster, 44
indexed virtual column, 44
index-organized table, 74
DBA/ALL/USER_TABLES, 74
INCLUDING clause, 75
ORGANIZATION INDEX, 74
invisible indexes
advantages, 71
OPTIMIZER_USE_INVISIBLE_INDEXES, 70
uses, 71
key-compressed, 44
local partitioned, 45
maximizing index creation speed, 77
increasing degree of parallelism, 77–78
NOLOGGING, 78
turning off redo generation, 77
monitoring usage, 75
advantages, 76
ALTER INDEX...MONITORING USAGE, 75
V$OBJECT_USAGE, 76
Oracle index types, 44
primary key constraint, 54
ALTER TABLE...AND CONSTRAINT statement, 54
create constraint inline, 55–56
create constraint out of line, 56
create index and constraint, 55
reverse-key, 44, 68. See also B-tree indexes
REBUILD REVERSE, 69
REVERSE clause, 69
unique index, 56
adding constraint, 58
techniques, 57
virtual column, 67
cautions, 68
definition, 68
vs. function-based indexes, 67
improving performance, 67
Index creation standards, 51
Index Fast Full Scan (IndexFFS), 361
Index-organized tables (IOTs), 74–75
Inner join
advantages, 259
filtering criteria, 259
JOIN ... ON clause, 258
JOIN ... USING clause, 258
NATURAL JOIN clause, 258
traditional Oracle SQL, 258, 259
Inner query, 264
Invisible index, 70
IOSEEKTIM system statistics, 465
IOTFRSPEED system statistics, 465
ISO syntax, 253
advantages, 259
methods, 258
J
Join condition
full outer join, 262
inner join, 258, 262. See also Inner join
left outer join, 262
outer join. See Outer join
right outer join, 262
K
L
Low cardinality indexes, 158
M
MMON background process, 139
Multicolumn indexes, 62
Multiple-column subqueries, 266–267
Multiple-row subqueries, 265
ALL operator, 266
ANY and SOME operators, 265
IN operator, 265
N
NO_INVALIDATE parameter, 455
NOLOGGING
advantages, 78
turning off redo generation, 77
O
Operating system performance analysis, 185
bottlenecks
network-intensive processes, 201–202
database network connectivity issues, 202–203
decision-making process, 185
disk space issues, 187
df command, 187
du, sort and head commands, 188
filesp.bsh, 190
find, Is, sort and head commands, 187
mount point, 187
shell script, monitoring, 188–189
usedSpc variable, 189
identifying top server-consuming resources, 194
column descriptions of top Output, 196
commands to change the top Output, 196
19888 process ID, 195
top command, 194
isolate database performance problems, 185
OS Watcher, 192
resource-intensive process
to database process, mapping, 204–206
troubleshooting poor performance, 186, 187
Optimizer, 447
adaptive cursor sharing
bind peeking, 476
BIND_SHAREABLE column, 478
child cursor, 481
INDEX FAST FULL SCAN, 479, 480
IS_BIND_AWARE column, 478
STATUS column, 478
automatic statistics gathering
dbms_auto_task_admin.disable procedure, 449
dbms_auto_task_admin.enable procedure, 449
DBMS_STATS.GATHER_DATABASE_STATS procedure, 450
DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC procedure, 450
enable procedure, 448
GATHER_DATABASE_STATS procedure, 450
SYS and SYSTEM schemas, 450
bind peeking behavior, 447
bulk loaded tables, 457
concurrent statistics collection
DBMS_STATS.GATHER_TABLES_STATS procedure, 488
job_queue_processes parameter, 488
monitoring concurrent stats collection jobs, 489
multi-processor environment, 488
Oracle Database 11g Release 2, 488
parallel execution strategy, 489
SET_GLOBAL_PREFS procedure, 488
locking statistics, 458
non-use of bind variables, 473–476
query optimizer features, 470–471
restoring previous versions, 462–463
statistics on expressions, 482–483
system statistics
I/O and CPU characteristics, 463
interval parameter, 464
IOTFRSPEED, IOSEEKTIM, and CPUSPEEDNW, 465
mbrc and mreadtim statistics, 466
noworkload statistics, 463
Oracle 11g database, 465
workload mode, 465
workload statistics, 464
types of statistics
add_sys, 451
AUTOSTATS_TARGET parameter, 456
CASCADE parameter, 452
DEGREE parameter, 452
ESTIMATE_PERCENT parameter, 452–453
GRANULARITY parameter, 455
INCREMENTAL preference, 455–456
NO_INVALIDATE parameter, 455
pname, 451
PUBLISH parameter, 455
pvalue, 451
SET_DATABASE_PREFS, 451
SET_GLOBAL_PREFS, 451
SET_SCHEMA_PREFS, 451
SET_TABLE_PREFS, 451
STALE_PERCENT preference, 456
volatile tables, 457
Optimizer_dynamic_sampling initialization parameter, 459
Optimizer_features_enable parameter, 470–471
Optimizer_index_cost_adj parameter, 468–470
Optimizer_use_pending_statistics parameter, 468
Oracle's Automatic Storage Management (ASM), 7
Oracle's Autotrace utility, 47, 48
Oracle's basic compression
hybrid columnar compression, 38–39
direct path loading, 34
advantage, 35
ALTER statemment, 35
COMPRESS clause, 35
CREATE TABLE...AS SELECT statement, 34
DBA/ALL/USER_TABLES view, 34
DML statements, 35
MOVE COMPRES clause, 35
DML
ALTER TABLE statement, 37
COMPRESS FOR OLTP clause, 36, 37
I/O performance, 36
Oracle Database 11g R2, 3, 6, 12–14
Oracle Diagnostics Pack, 115
Oracle Enterprise Manager, 331
$ORACLE_HOME/rdbms/admin directory, 117
Oracle locks, 164
Oracle's standard auditing feature, 40
Oracle Trace Analyzer, 327, 344
/trca/install/trcreate.sql script, 341
CoE, 342
individual SQL, 343
installation and running steps, 341
non-default initialization parameters, 343
non-recursive tome and totals, 343
self - time, totals, waits, binds and row source plan, 343
tables and indexes, 343
tacreate.sql script, 341
top SQL, 343
trca_instructions HTML document, 342
ZIP file, 342
oradebug, 206
Outer join
cross join, 261
FULL OUTER JOIN, 261
ISO SQL syntax, 260
ISO syntax, 261
left outer join, 260
Oracle SQL, 261
right outer join, 260
syntax, 260
traditional Oracle SQL, 260
P
Parallelism, 525
creating tables, 536
DDL advantages, 537
deleting rows, 537
drawbacks, 538
reasons, 536
degree of parallelism, 526–528, 543–545
DML operations, 533
ALTER SESSION ENABLE PARALLEL DML, 533
ALTER SESSION FORCE PARALLEL DML, 533
DBMS_PARALLEL_EXECUTE PL/SQL package, 534
DOP, 535
INSERT statement, 533
restrictions, 535
UPDATE, MERGE and DELETE statements, 534
existing object, 532
execution steps, 547
operations, 548
general rule, 525
monitoring operations, 548–550
moving partitions, 541
pitfalls, 525
sessions, detailed information, 552–553
types, 528
understanding factors, 525
understanding system components, 527
Plan baselines
altering
ALTER_SQL_PLAN_BASELINE function, 434, 435
ATTRIBUTE_NAME and ATTRIBUTE_VALUE, 435
DBMS_SPM package, 434
benefits, 410
DBA_SQL_BASELINES, 436
disabling, 442
DISPLAY_SQL_PLAN_BASELINE function, 437–439
EVOLVE_SQL_PLAN_BASELINE function, 439–441
managing tasks, 428
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES, 427
PACK STGTAB BASELINE function, 446
for SQL statements, 428
AWR baseline, 431
DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE function, 429
LOAD_PLANS CURSOR CACHE function, 430
resource-intensive queries, 433
Program global area (PGA), 85
Q
Query coordinator (QC) controls, 345
Query hints, 491
caching query results, 509–513
configuration hierarchy, 512
initialization parameters, 513
changing the access path
changing the join method, 498
necessary factors, 500
querying multiple tables, 500–501
smart merge join, 500
changing the join order, 497
ORDERED hint, 497
changing the optimizer version, 501–502
directing a distributed query, 513
disadvantages, 514
direct-path insert technique, 505–506
fast response and overall optimization, 502–504
GATHER_PLAN_STATISTICS hint, 517–519
in views, 506
complex view, 507
non-mergeable view, 508
simple view, 507
star information/fact hint, 521–523
R
Recovery writer process (RVWR), 162–163
Resource-intensive process
to database process, 204
termination
OS Kill command, 208
using SQL, 207
S
Segment Advisor Advice
display table information
advice and recommendations, 21
ASA_RECOMMENDATIONS, 21
AWR, 21
DBMS_SCHEDULER, 20
enterprise manager, 23
findings, 20
retrieving tools, 21
e-mailing segment advice automatically, 27–28
freeing unused space, 32
ALTER TABLE...SHRINK SPACE statement, 32–33
enable row movement, 32
generating advice manually, 23
DBMS_ADVISOR.CREATE_TASK procedure, object types, 26–27
DBMS_ADVISOR.SET_TASK_PARAMETER procedure, 27
DBMS_SPACE package, 24
rebuild spanned rows, 28
MOVE statement, 29
PCTFREE, 30
UPDATE statement, 30
SELECT statement, 254
FROM clause, 255
SELECT clause, 255
subqueries. See Subqueries
SELECT_BASELINE_DETAILS function, 125
Single-row subqueries, 264–265
Skip-scan feature, 62
avoiding full table scan, 288–290
avoiding NOT clause, 293
comparison operators, 294
drawbacks, 293
NOT IN, 294
NOT LIKE, 294
BETWEEN clause, 274
Oracle optimizer, 276
pitfalls, 275
comparing tables
INTERSECT set operator, 271
controlling transaction sizes, 295–297
execution plan. See Execution plan, SQL
BILLING_INFO view, 292
PRODUCT_INFO view, 292
SERVICE_INFO view, 292
ISO syntax, 253
joining tables
inner join. See Inner join
outer join. See Outer join
null values, 277
partial column values
benefits, 283
considerations, 282
LIKE operator, 281
TO_CHAR function, 281
plan baselines. See Plan baselines
re-using SQL statements, 284
execute immediate statement, bind variables, 287
soft-parsing, 285
TKPROF utility, 286
SAVEPOINT command, 297
SELECT statement. See SELECT statement
WHERE clause, comparison operators, 256
SQL plan management
plan baselines. See Plan baselines
plan history, 409
SQL profiles, 409
vs. database profiles, 417
disabling, 421
manage features, 423
modifiable attributes, 422
SQLTUNE_CATEGORY parameter, 422
parameters
CATEGORY, 416
DESCRIPTION, 416
FORCE_MATCH, 416
NAME, 416
OBJECT_ID, 416
PROFILE_TYPE, 416
REPLACE, 416
TASK_NAME, 416
TASK_OWNER, 416
transporting database, 424
copy the staging table, 425
DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF procedure, 425
DBMS_SQLTUNE.PACK STGTAB SQLPROF procedure, 426
DBMS_SQLTUNE.PACK_STGTAB_SQLPROF procedure, 425
DBMS_SQLTUNE.UNPACK STGTAB SQLPROF procedure, 426
Tuning Advisor. See SQL Tuning Advisor
SQL Test Case Builder (TCB), 242
SQL tracing, 327
<ADR Home>/trace subdirectory, 329
archive logs, Data Guard environment, 365–366
automatic Oracle error traces, 361–362
correct session, 348
$DIAG_INFO view, 329
Diag Trace, 329
diagnostic_dest initialization parameter, 329
event 10046 trace
instance, 356
instance/database, 353
max_dump_file_size parameter, 328, 329
optimizer's execution path
access path analysis for SALES, 360
IndexFFS, 361
Oracle event 10053, 359
types of information, 359
Oracle Database 11g, 329
own session, 332
parallel query
alter system set events command, 345
MyTrace1, 344
Oracle Database 11g, 346
query coordinator, 345
show tracefile -t command, 345
specific SQL statement, 330–332
timed_statistics parameter, 327–328
TKPROF utility, 327
trace dump file, 327
trace files
execution plan, 340
formatting with TKPROF, 336–337
header, 338
Oracle Trace Analyzer. See Oracle Trace Analyzer
row source operations, 339–340
tkprof command, 338
wait events, 340
SQL Tuning Advisor, 368
from ADDM, 404
DBMS_ADDM Package, 406
enterprise manager, 406
SQL*plus script, 405
types of recommendations, 407
create and accept SQL profile, 415
from enterprise manager, 403–404
executing the task, 414
from SQL Developer, 403
optimizer tuning modes, 402
recommendations, 414
steps to run manually, 401
SQL Tuning Set (STS), 368
high-resource consuming statements, in AWR, 388–390
in memory
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET procedure, 391–392
resource-consuming statements, 390–391
transporting to another database
copy the staging table, 397
create staging table, 396
populate staging table, 396–397
unpack the staging table, 397–398
STALE_PERCENT preference, 456
correlated subqueries, 267–269
inline view, 264
multiple-column subqueries, 266–267
multiple-row subqueries, 265–266
single-row subqueries, 264–265
System global area (SGA), 85
T
Table performance, 1
building database
default permanent tablespaces, 2, 4
default temporary tablespaces, 2, 4
locally managed tablespaces, 2
compressing data
creating table
avoiding extent allocation delays, 12–14
performance and sustainability issues, 10–11
scalability and maintainability, 11–12
SEGMENT CREATION DEFERRED, 14
SEGMENT CREATION IMMEDIATE, 14
creating tablespaces
ASSM, 5
autoallocation behavior, 5
AUTOEXTEND ON clause, 7
bigfile tablespace, 7
dictionary-managed tablespaces, 6
locally managed tablespaces, 5, 6
SEGMENT SPACE MANAGEMENT AUTO clause, 6
smallfile tablespace, 7
storage attributes, tables and indexes, 5
maximizing data loading speeds
Oracle database, 1
removing table data
DELETE vs. TRUNCATE statement, 18–19
ROWID pseudo-column, 32
Segment Advisor Advice. See Segment Advisor Advice
table types, 8
clustered, 8
external, 9
nested, 9
object, 9
partitioned, 8
temporary, 8
tablespace, 1
TKPROF utility, 286
TM lock contention wait events, 169–171
tracefile_identifier parameter, 333
Troubleshooting Database, 209
AWR report
compare Periods report, 246–247
DBMS_WORKLOAD_REPOSITORY package, 245
instance efficiency percentages, 249
PGA histogram, 251
session information, 248
Time Model Statistics, 250
Top 5 Foreground Events, 250
hung database
oradebug hanganalyze command, 225, 228
prelim option, 227
resolving steps, 224
systemstate dump, 226
true database hang, 227
invoke ADRCI. See Automatic Diagnostic Repository Command Interpreter (ADRCI)
optimal undo retention parameter, 209
criteria, 211
statistics, 212
ORA-01555 error, 215
guaranteed undo retain feature, 215
snapshot too old error, 216
undo extents, 216
packaging incidents, Oracle Support, 236–238
resolving open cursor errors, 222–224
SQL test case
creation process information, 242
DBMS_SQLDIAG package, 242
EXPORT_SQL_TESTCASE procedure, 243
temporary tablespace
monitoring the usage, 217
unable to extend TEMP segment error, 220–221
Undo usage
V$SESSION, 214
V$TRANSACTION, 214
UNDO_TABLESPACE initialization parameter, 211–212
U
V
Virtual memory statistics (vmstat), 190–192
W, X, Y, Z
Wait events. See also Contention
analyzing Oracle wait events, 151–152
application class, 152
buffer busy waits, 157
segment header, 157
undo header and undo block, 158
Commit class, 152
db file scattered read wait event, 156
db file sequential read wait event, 156
examining session waits, 153, 154
indentifying SQL statements, 150–151
Network class, 152
read by other session, 160–161
reducing direct path read, 161–162
User I/O class, 152
V$ACTIVE_SESSION_HISTORY view, 174–175
WHERE clause, comparison operators, 256