Index
A
Access structures, 296
bind-awareness monitoring, 61–73
and SPM. See SPM (SQL plan management), adaptive cursor sharing.
ACS (adaptive cursor sharing), bind sensitiveness with
equality predicate histograms, 55–56
ACS (adaptive cursor sharing), bind-aware cursors
performance issue, example, 76–81
Active Data Guard, 279
Active Session History (ASH). See ASH (Active Session History).
Actuator arms, 388
Adams, Steve, 370
ADDM (Automatic Database Diagnostic Monitor), finding buffer busy wait event information, 41
Administrative tasks, utilities and commands, 283
ADO (Automatic Data Optimization), 160–162, 326, 410
ADR (Automatic Diagnostic Repository), 276–277
Advanced Index Compression, 162
Advanced Row Compression, 160
Alert logs, 277
ALTER DATABASE FLASHBACK OFF command, 179
ALTER DATABASE FLASHBACK ON command, 179
ALTER INDEX <index name> VISIBLE command, 441
ALTER SYSTEM FLUSH SHARED_POOL command, 378
Alter table operation, 142–144, 147–152
ALTER TABLESPACE <tablespace_name> FLASHBACK OFF command, 179
AMM (Automatic Memory Management), 281
Antivirus software, 281
buffer busy wait event information, 43–44
finding latch contention, 373–375
ASM (Automatic Storage Management), disk groups on Exadata, 416–418
ATTACH parameter, 319
Automatic Data Optimization (ADO), 160–162, 326, 410
Automatic Database Diagnostic Monitor (ADDM), 41, 277
Automatic Diagnostic Repository (ADR), 276–277
Automatic Maintenance Tasks (AUTOTASK) framework, 290–291
Automatic Memory Management (AMM), 281
Automatic Storage Management (ASM), disk groups on Exadata, 416–418
AUTOTASK (Automatic Maintenance Tasks) framework, 290–291
Autotuning retention values, disabling, 21
AWR (Automatic Workload Repository). See also Performance tuning.
basic reports, 198
buffer pool statistics, 237–240
buffer waits statistics, 247–248
dynamic memory components, 260–262
excessive disk spills, 246
finding buffer busy wait event information, 41–43
initialization parameter changes, verifying, 266
instance recovery statistics, 239
Java pool advisory, 245–246, 247
library cache activity, 257–260
OOS (out-of-space) errors, 251
overloaded buffer cache, 239
resource limits, 266
shared pool statistics, 244–245
STO (snapshot too old), 251
streams pool advisory, 245–246
tablespace I/O statistics, 235–237
time model statistics, 211–212
timing, 266
undo segment statistics, 250–251
what to look for, 199
AWR (Automatic Workload Repository), header section
buffer hit percentage, 202
buffer nowait percentage, 202
instance CPU, 207
instance efficiencies, 202–203
latch hit percentage, 203
library hit percentage, 202
log file stress, 206
memory sort percentage, 202–203
non-parse CPU percentage, 203
redo nowait percentage, 202
shared pool memory statistics, 203
soft parse percentage, 203
AWR (Automatic Workload Repository), instance activity statistics
consistent gets, 224
dirty blocks, 224
enqueue, 224
execution count, 225
free buffer, 225
GC (global cache), 225
index fetch by key, 226
index scan, 226
index scans kdiixs1, 226
leaf nodes, 226
open cursors, 226
parses, 226
physical reads and writes, 226
recursive calls, 229
redo related, 229
sorts, 230
summed dirty queue length, 230
thread activity, 233
transaction rollback, 231
user I/O wait time, 232
work area, 232
AWR (Automatic Workload Repository), latch statistics
latch activity, 253
parent and child latches, 255
Pct Get Misses, 253
Pct NoWait Misses, 253
sleep summary, 255
spin count, 254
AWR (Automatic Workload Repository), OS statistics
background wait events, 214–215
foreground wait events, 213–214
service related statistics, 216–217
wait event histograms, 215–216
AWR (Automatic Workload Repository), PGA statistics
aggregate target histogram, 242–243
aggregate target statistics, 242
cache hit percentages, 241–242
OOBs (out-of-band-sorts), 243
AWR (Automatic Workload Repository), process memory
process memory summary, 264
SGA breakdown difference, 264
SGA memory summary, 264
AWR (Automatic Workload Repository), RAC-specific pages
cluster interconnects, 210
global cache and enqueue services, 209–210, 268–273
global cache load statistics, 209
global cache transfer statistics, 271–272
global CR served statistics, 271
global current served statistics, 271
global enqueue statistics, 271
hot blocks, 271
RAC statistics (CPU), 208
AWR (Automatic Workload Repository), SQL sections
CPU time, total, 218
disk reads, total, 219
executions, total, 219
recursive calls, 218
recursive CPU usage, 218
unsafe bind variables, 220
version count, 220
awrddrpi.sql script, 198
awrddrpt.sql script, 198
awrgdrpt.sql script, 198
awrgrpt.sql script, 198
awrrpti.sql script, 198
awrrpt.sql script, 198
awrsqrpti.sql script, 198
awrsqrpt.sql script, 198
B
Background wait event statistics, 214–215
Backup and recovery. See also RMAN (Recovery Manager).
backup optimization and tuning, 187–188
BCT (block change tracking), 170, 178
Data Guard configuration, 172
DRA (Data Recovery Advisor), 193–194
Exadata solutions, 171
excluding tablespaces, 179
Flashback Database features enabling/disabling, 179
FRA (fast recovery area), 179
guaranteed restore points, 179
most recently detected failures, 193
recovery factors, 174
rewinding in Oracle Flashback technology, 178–179
RPO (recovery point objective), 174
RTO (recovery time objective), 174
TSPITR (tablespace point-in-time recovery), 179
Backup and recovery, backup strategies
cumulative incremental backups, 177
decompression, 177
differential incremental backups, 177
full backups, 176
IUIC (incrementally updatable image copies), 180–186
null block compression, 176
RFF (recover forward forever), 180–186
unused block compression, 176
BACKUP DURATION clause, 188
BACKUP OPTIMIZATION setting, 188, 192
_backup_disk_bufcnt parameter, 189
_backup_disk_bufsz parameter, 189
_backup_file_bufcnt parameter, 189
_backup_file_bufsz parameter, 189
Balanced tree (B-tree) indexes, 422–425, 432
BASICFILE LOBs. See also LOB (large object) data type.
issues, 8
BASICFILE LOBs, migrating to SECUREFILE LOBs
poor INSERT performance, 17
BCT (block change tracking)
definition, 170
enabling, 178
BFILE data type, managing free space, 16
Binary large object (BLOB), managing free space, 16
Bind variable technique, 376–378
Bind variables
identifying, 451
SPM (SQL plan management), 86
Bind-awareness monitoring, 61–73
Bitmap join indexes, 431
BLOB (binary large object), managing free space, 16
Block-checking parameters, configuring, 279
Blocks, SSD, 392
blocksize designation parameter, 237–238
Books and publications
“ASMM vs. AMM and LINUX HugePages Support,” 281
“HugePages and Oracle 11g Automatic Memory Management (AMM) on Linux,” 281
Oracle Database 12c Reference Guide, 36
Oracle Database SecureFiles and Large Objects Developer’s Guide, 1
Oracle Exadata Expert’s Handbook, 414
Oracle Tuning Guide and Concepts Manual, 199
Oracle8i Internal Services, 370–371
Bottlenecks. See Performance bottlenecks.
Branch pages, 422
B-tree (balanced tree) indexes, 422–425, 432
BUCKET_ID, COUNT relationship, 62–66
buffer busy, 36
finding waiting sessions, 45–46
gc buffer busy, 36
gc buffer busy acquire, 36
gc buffer busy release, 36
key tools. See ADDM (Automatic Database Diagnostic Monitor); ASH (Active Session History); AWR (Automatic Workload Repository); ORAchk utility.
performance bottlenecks, isolating, 47–49
read by other session, 36
types of, 36
Buffer busy wait events, finding event information with
ADDM, 41
buffer busy waits, 239
Buffer gets, analyzing, 218–219
Buffer hit percentage, analyzing, 202
Buffer nowait percentage, analyzing, 202
Buffer pool
C
Cache buffers lru chain latch, 382
CACHE directive, 9
Cache hit percentages, 241–242
Caching, 9
CBC (cache buffer chain) latches, 379–381
C_DDL column
in a column group extension, 140–142
default value changes, 142–144
Child latches, 255
CHM (Cluster Health Monitor), 278
CHUNK parameter, 9
Chunk size, specifying, 9
CLOB (character large object), managing free space, 16
Cloning databases, 330
Cluster interconnects analyzing, 210
Cluster wait time, analyzing, 220–221
Clustering factors, indexes, 435–436
Clusterware componentry status, checking, 283–284
Columns, with default values, adding to tables. See DDL (data definition language) optimization.
COMPARE method, 451
Composite indexes, 430
Compressed indexes, 431
managing LOB data types, 8
SSDs (solid-state drives), 410
table, 160
Compression, VLDBs and XLDBs
Advanced Index Compression, 162
Advanced Row Compression, 160
HCC (Hybrid Columnar Compressions), 160
Oracle Advanced Compression, 160
table compression, 160
Concatenated indexes, 430
CONCURRENT global preference, 169
Configuration information, displaying, 287–288
Consistent gets, instance activity statistics, 224
CONTENT parameter, 306
Contention
interinstance, 441
latches and mutexes. See Latches and mutexes, contention.
sequences and indexes, 442
CONTINUE_CLIENT command, 319
CONTROL_FILE_RECORD_KEEP_TIME parameter, 191
Copying. See also Data Pump; Migration.
cloning databases, 330
duplicate schema objects, 305
entire databases, 305
objects between databases, 304–305
from Oracle Database 9i or older, 305
schemas between databases, 304–305
table metadata only, 306
tables between databases, 305
tablespaces, 306
CPT (cross-platform transport), 331, 344–345
CPU management, 281
CPU time, analyzing, 218
CPU_COUNT parameter, 281
Cross-platform transportable tablespaces (XTTS), 331, 340–343
crsctl check commands, 283
crsctl query commands, 284
crsctl status commands, 284–286
CRSCTL (Oracle Clusterware Control) utility, 283
Cumulative incremental backups, 177
CURSOR_SHARING parameter, 377
D
Data, excluding from export, 321
Data block corruption, protecting against, 279
Data block size, optimal, 155–156
Data compression, 159–160. See also Compression.
Data definition language (DDL) optimization. See DDL (data definition language) optimization.
Data dictionary object block corruption, 30–31
Data Guard, physical standby database, 333
Data Guard backup and recovery configuration, 172
Data Pump. See also Copying; Exporting; Importing; Migration.
changing object properties, 313–317
database directory location, specifying, 306
database links, saving and restoring, 307
database links and synonyms, exporting, 307–308
default storage parameters, 314
excluding BLOB data, 321
exiting, 319
Export/Import utilities, 345
FTE (full transportable export/import), 346, 347–350
importing partitioned tables as nonpartitioned, 313
importing table partitions as individual tables, 313
improving performance, 320–321
invoking, 303
job name, getting, 319
job status, displaying, 319
log file, specifying, 306
masking database, 314
monitoring and altering resources, 319
public and private objects, 306–309
renaming tables, 314
return to logging mode, 319
scrambling sensitive data, 314
with SQL*Plus (PL/SQL API), 317–319
Data Pump, dump files
adding, 319
resizing, 319
scrambling sensitive data, 314
specifying, 306
Data Pump, tablespaces
resizing, 314
Data Recovery Advisor (DRA), 193–194
Data warehouse templates, 154–155
Database Configuration Assistant (DBCA), 155
Database directory location, specifying, 306
Database Flash Cache (DBFC). See DBFC (Database Flash Cache).
Database links
exporting, 307
saving and restoring, 307
and synonyms, exporting, 307–308
Database writer (DBWR) process, 398
Databases
cloning, 330
hanging. See Hung databases.
masking sensitive data, 314
very large. See VLDBs (very large databases); XLDBs (extremely large databases).
Databases, transferring data
to another database. See Cloning databases; Data Pump.
from a file. See Importing.
to a file. See Exporting.
Datafiles
I/O stress, 237
limiting the number of, 156–157
DATA_PUMP_DIR parameter, 306
DBA_DATAPUMP_JOBS view, 319
DBA_DATAPUMP_SESSIONS view, 319
DBA_DB_LINKS view, 308
DBA_SYNONYMS view, 308
DB_BLOCK_CHECKING parameter, 279
DB_BLOCK_CHECKSUM parameter, 279
DBCA (Database Configuration Assistant), 155
DB_CACHE_ADVICE parameter, 382
db_cache_size parameter, 261
creating, 399
DBWR process, 398
deferred writing of changed blocks, 396
lazy writes, 396
overview, 396
performance statistics, 400–402
writing to the flash cache, 398
DB_FLASHBACK_RETENTION_TARGET parameter, 179
DB_FLASH_CACHE_FILE parameter, 398–399
DB_FLASH_CACHE_SIZE parameter, 398–399
DB_LOST_WRITE_PROTECT parameter, 279
DBMS DEFINITION package, 12–14
DBMS_FILE_TRANSFER utility, 339, 344, 349
DBMS_SHARED_POOL parameter, 379
DBMS_SQLDIAG package, 300
DBMS_STATS.GATHER_TABLE_PREF package, 169
DBMS_UNDO_ADV package, 21
DBMS_UNDO_ADVISOR procedure, 21
DBMS_WORKLOAD_REPOSITORY package, 197–198
dbms_xplan package, 88
DB_RECOVERY_FILE_DEST parameter, 179
DB_RECOVERY_FILE_DEST_SIZE parameter, 179
DBWR (database writer) process, 398
DBWR_IO_SLAVE parameter, 188
DDL (data definition language) optimization
alter table operation, 142–144, 147–152
C_DDL column and indexes, 145–147
C_DDL column in a column group extension, 140–142
C_DDL column in a virtual column, 139–140
C_DDL default value changes, 142–144
inaccurate cardinality estimates, resolving, 139–140
table cardinality estimation, 137–138
DEBUG parameter, 188
Decompression, 177
Deduplication, 8
Depth statistics, indexes, 435
DETACH program, 317
diagcollection.pl script, 278
Differential incremental backups, 177
DIRECTORY parameter, 306
Dirty blocks, instance activity statistics, 224
Disk reads, analyzing, 219
Disk spills, analyzing, 246, 264–266
DISPATCHERS parameter, 243
Distinct key statistics, indexes, 435
Distributed transactions. See XA (X/Open XA).
DRA (Data Recovery Advisor), 193–194
DTP (distributed transaction processing), hanging databases, 22–24
Dump files
adding, 319
resizing, 319
scrambling sensitive data, 314
specifying, 306
DUMPFILE parameter, 306
DUPLICATE DATABASE method, 330, 332–333
Dynamic memory components, 260–262
E
Elapsed time, analyzing, 217–218
Encryption, 8
Enqueue
definition, 2
instance activity statistics, 224
Equality predicate histograms, bind sensitiveness with ACS, 55–56
Error messages. See specific messages.
ESTIMATE_PERCENT value, getting, 170
EtherChannel, 279
Exachk utility, 280
Exadata
backup and recovery solutions, 171
SSDs (solid-state drives), 414–418
EXCLUDE parameter
exporting public database links and synonyms, 307–308
saving and restoring database links, 307
specifying objects for import/export, 306
Execution count, instance activity statistics, 225
Executions, analyzing, 219
EXIT_CLIENT command, 319
Expdp, common parameters, 306
Exp/imp tools, 305
EXPLAIN PLAN FOR command, 451
Explain plans, comparing original and new, 294
Exporting. See also Copying; Data Pump; Importing.
database links and synonyms, 307
file size, predicting, 305
help for, 306
from a higher version to a lower one, 322
legacy exp/imp tools, 305
from Oracle Database 9i or older, 305
subsets of table data, 310–313
EXtended Architecture (XA), hanging databases, 22–24
Extended cursor sharing. See ACS (adaptive cursor sharing).
Extents. adding to LOBs, 7
EXTRACT processes, 329
Extremely large databases (XLDBs). See XLDBs (extremely large databases).
F
Failover, configuring, 280
Fast recovery area (FRA), 179
FILESIZE command, 319
FILESPERSET setting, 192
Filtering data, during migration, 329
Flash technology. See SSDs (solid-state drives).
Flashback Database features enabling/disabling, 179
Flashback options, 280
Flashback technology, rewinding databases, 178–179
FLASHBACK_SCAN parameter, 347
Forced-plan sharing issues, 86
Foreground wait event statistics, 213–214
Forensics. See AWR (Automatic Workload Repository).
FRA (fast recovery area), 179
Free buffer, instance activity statistics, 225
free buffer waits statistics, 238
Free global transaction table entry wait event, 460–462
Free lists, 393
Free space, minimum percentage, setting, 14–17
Full backups, 176
Full table scans, SSDs, 404–406
G
Garbage collection, SSDs, 393–394
GATHER_DICTIONARY_STATS parameter, 320
enqueue services, analyzing, 268–273
instance activity statistics, 225
load statistics, analyzing, 209
times (immediate), analyzing, 272
transfer (immediate), analyzing, 272
transfer statistics, analyzing, 271–272
transfer times, analyzing, 272
GC buffer busy acquire events, 36
GC buffer busy events, 36
GC buffer busy release events, 36
Global cache (gc). See GC (global cache).
Global Cache Fusion, 275
Global CR served statistics, analyzing, 271
Global current served statistics, analyzing, 271
Global enqueue statistics, analyzing, 271
Global index hash partitioning, 441
Global partitioned indexes, 427–428
Global transactions. See XA (X/Open XA).
GTTs (global temporary tablespace groups)
automatic statistics gathering, 358–359
description, 356
separate temporary tablespaces, 356
H
Hash operations, SSDs, 406–408
Hash-partitioned indexes, 432
HCC (Hybrid Columnar Compressions), 160
Health Check script, 447
Help
exporting, 306
MOS (My Oracle Support) resources, 278–279
HELP parameter, 306
High-watermark (HW) enqueue events, 2, 4–7
_HIGHTHRESHOLD_UNDORETENTION parameter, 21
Hints, forcing an index, 437
Histograms, wait events, 215–216
Hot blocks, analyzing, 271
HugePages and Oracle 11g Automatic Memory Management (AMM) on Linux, 281
gathering information about, 27–28
Hung databases, caused by
DTP (distributed transaction processing), 22–24
rollback segments, 24
XA (eXtended Architecture), 22–24
HW (high-watermark) enqueue events, 2, 4–7
Hybrid Columnar Compressions (HCC), 160
I
IDA (In-Database Archiving), 326
ILM (Information Lifecycle Management), 326
Impdp, common parameters, 306
Importing. See also Copying; Data Pump; Exporting; Migration.
default storage parameters, 314
legacy exp/imp tools, 305
from Oracle Database 9i or older, 305
partitioned tables as nonpartitioned, 313
resizing tablespaces, 314
table partitions as individual tables, 313
IMU (in-memory undo) latch, 383
INCLUDE parameter
exporting public database links and synonyms, 307–308
saving and restoring database links, 307
specifying objects for import/export, 306
Incremental statistics synopsis, 166–168
Incrementally updatable image copies (IUIC), 180–186
In-Database Archiving (IDA), 326
Index fetch by key, 226
Index partitioning, local vs. global, 159
Index scan, 226
Index scans kdiixs1, 226
Indexed reads, SSDs, 403
Indexes
bitmap join, 431
branch pages, 422
B-tree (balanced tree), 422–425, 432
composite, 430
compressed, 431
concatenated, 430
creating, 298
hash partitioned, 432
IOTs (index-organized tables), 430
leaf pages, 422
making invisible, 439–441, 443
multiple on identical columns, 431–432
nonunique, 432
parallel operation, 320
parallelism, 425
partial, 429
partition pruning, 427
range partitioned, 432
referencing multiple rows simultaneously, 426
reverse key, 430
root pages, 422
skip-scan operations, 430
unique, 432
average data blocks per key, 435
average leaf blocks per key, 435
choosing the wrong index type, 437
clustering factor statistics, 435
deleting index entries in a block, 438–439
depth statistics, 435
distinct key statistics, 435
ever-increasing values, 441
forcing an index via a hint, 437
global index hash partitioning, 441
hiding unselective indexes, 439–441
index overuse, 439
interinstance contention, 441
leaf block statistics, 435
low clustering factors, 435–436
monotonically increasing indexes, 441
nonselective indexes, 441
OLTP and read-mostly workload contention, 442
operational considerations, 436–439
Oracle sequences and index contention, 442
outmoding initialization parameter settings, 437–438
reverse key, 441
Index-organized tables (IOTs), 430
Information Lifecycle Management (ILM), 326
Initialization parameter changes, verifying, 266
init.ora parameter, 277
In-memory undo (IMU) latch, 383
_in_memory_undo parameter, 383
Input/output. See I/O.
INSERT performance, after migrating BASICFILE LOBs to SECUREFILE LOBs, 17
Instance activity statistics. See AWR (Automatic Workload Repository), instance activity statistics.
Instance CPU, analyzing, 207
Instance efficiencies, analyzing, 202–203
Instance recovery statistics, 239
Interconnect devices, analyzing, 273
Interconnect ping latency, analyzing, 272
Interconnect throughput by client, analyzing, 273
Interinstance contention, 441
Interobject parallelism, 168
deferred writing of changed blocks, 396
physical read/write statistics, 227–228
timing for writes, 236
writing to the flash cache, 398
I/O stress, tablespaces or datafiles, 237
IOTs (index-organized tables), 430
IUIC (incrementally updatable image copies), 180–186
J
Java pool advisory, 245–246, 247
java_pool_size parameter, 261
K
_kdli_sio_fileopen parameter, 17
KILL_JOB command, 319
Kks stats latch, 383
L
Large object (LOB) data type. See LOB (large object) data type.
large_pool_size parameter, 261
LARGE_POOL_SIZE parameter, 188, 193
Latch gets, 369
Latch hit percentage, analyzing, 203
Latch misses, 369
Latch sleeps, 369
Latch statistics
activity, 253
parent and child latches, 255
Pct Get Misses, 253
Pct NoWait Misses, 253
sleep summary, 255
spin count, 254
Latch wait list, 369
Latch wait posting algorithm, 372
Latches
cache buffer chains latches, 369
redo allocation latches, 369
soft parsing, 375
Latches and mutexes, architecture
cache buffer chains latches, 369
latch gets, 369
latch wait list, 369
mutexes, definition, 370
redo allocation latches, 369
spin locks, 369
spinning, 370
test and set instruction, 369
Latches and mutexes, contention
drilling into segments and SQLs, 373–375
fine tuning latch algorithms, 383–385
identifying individual latches, 372–373
intractable latch contention, 383–385
latch wait posting algorithm, 372
most common cause, 376
Latches and mutexes, contention scenarios
bind variable technique, 376–378
cache buffers lru chain latch, 382
CBC (cache buffer chain) latches, 379–381
IMU (in-memory undo) latch, 383
kks stats latch, 383
library cache mutex waits, 375–378
library cache pin wait, 378
process allocation latch, 382
RC (result cache) latches, 383
redo allocation latch, 382
session allocation latch, 382
simulator lru latch, 382
Lazy writes, 396
Leaf block statistics, indexes, 435
Leaf nodes, instance activity statistics, 226
Leaf pages, 422
Lewis, Jonathan, 118
Library cache activity, analyzing, 257–260
Library cache mutex waits, 375–378
library cache pin wait, 378
Library hit percentage, analyzing, 202
LMS (Lock Management Server), 275
LMTTs (locally managed temporary tablespaces), 354
Load average, analyzing, 206–207
LOB (large object) data type. See also BASICFILE LOBs.
caching, 9
chunk size, specifying, 9
compression, 8
deduplication, 8
encryption, 8
enqueue, definition, 2
HW (high-watermark) enqueue events, 2
logging, enabling, 9
minimum percentage of free space, setting, 14–17
storage parameters vs. performance, 9
LOB (large object) data type, example problems
adding extents, 7
increasing throughput, 8
Local partitioned indexes, 427–428
Locally managed temporary tablespaces (LMTTs), 354
Lock Management Server (LMS), 275
Locks. See Latches and mutexes.
Log file, specifying, 306
Log file stress, analyzing, 206
LOGFILE parameter, 306
Logging, enabling, 9
LOGGING option, 9
Logical corruption, protecting against, 280
Logical standby database, 328
M
MAA (Maximum Availability Architecture) guidelines, 279–280
Masking sensitive data, 314
Materialized views, creating, 298
MAXOPENFILES setting, 192
MAXPIECESIZE setting, 192
Memory
dynamic memory components, 260–262
managing, 281
process. See Process memory.
resources, optimizing, 157–158
SGA, summary, 264
sort percentage, analyzing, 202–203
statistics, analyzing, 207–208
memory_max_target parameter, 260–261
Memory-related parameters, RMAN (Recovery Manager), 189
memory_target parameter, 260–261
METADATA_FILTER program, 317
Migration. See also Copying; Data Pump; Exporting; Importing.
ADO (Automatic Data Optimization), 326
IDA (In-Database Archiving), 326
ILM (Information Lifecycle Management), 326
legacy exp/imp tools, 305
from Oracle Database 9i or older, 305
overview, 324
purpose of, 324
selecting data for, 326
Migration methods
EXTRACT processes, 329
filtering data, 329
logical standby database, 328
modifying data on the fly, 329
OGG (Oracle Golden Gate), 329
Oracle Streams, 329
physical standby database, 328
REPLICATE processes, 329
transactional capture, 327–329
Migration methods, nontransactional migration
CPT (cross-platform transport), 331, 344–345
database cloning, 330
DUPLICATE DATABASE method, 330, 332–333
overview, 330
physical standby database, 330, 333
TDB (transportable database), 330, 333–336
transferring just what’s needed, 336–340
TTS (transportable tablespaces), 331, 336–340
verifying database transportability, 336
XTTS (cross-platform transportable tablespaces), 331, 340–343
Migration methods, piecemeal migration
Data Pump Export/Import utilities, 345
Data Pump FTE (full transportable export/import), 346, 347–350
partition exchange, 346, 350–351
programmed methods, 346
resynchronizing tables, 347
Migration strategies
read-only tolerance, 325
real-time vs. near real-time, 325
window of inopportunity, 325
MLC (multi-level cell) SSDs, 391–392
Monitoring
databases in real-time, 278
distributed transactions, 462–464
processes, 278
third-party monitoring tools and utilities, 281
Moore, Gordon, 388
Moore’s law, 388
MOS (My Oracle Support) resources, 278–279
Mutexes. See Latches and mutexes.
_mutex_spin_count variable, 384
_mutex_wait_scheme variable, 384
_mutex_wait_time variable, 384
N
NAND flash, 389
NCLOB (national character large object), managing free space, 16
NETWORK_LINK parameter, 347
NOLOGGING clause, 425
Non-parse CPU percentage, analyzing, 203
Nonselective indexes, 441
Nontransactional migration. See Migration methods, nontransactional migration.
Nonunique indexes, 432
Null block compression, 176
NULL columns, DDL optimization, 147–152
O
Object properties, changing, 313–317
Objects, copying between databases, 304–305
OGG (Oracle Golden Gate), 329
OLTP (online transaction processing)
compression. See Advanced Row Compression.
and read-mostly workload contention, 442
read/write workload, SSDs, 403–404
OOBs (out-of-band-sorts), 243
OOS (out-of-space) errors, 251
Open cursors, instance activity statistics, 226
OPEN program, 317
Optimizer. See Oracle Optimizer.
Optimizer statistics, gathering for VLDBs and XLDBs
gathering statistics concurrently, 168–169
getting ESTIMATE_PERCENT value, 170
incremental statistics synopsis, 166–168
interobject parallelism, 168
optimizer_capture_sql_plan_baselines parameter, 87
optimizer_use_sql_plan_baselines parameter, 87
ORA-00439 message, 399
ORA-01652 message, 356
ORA-4031 message, 378
ORAchk utility. See also Buffer busy wait events.
description, 276
verifying customization, 38
Oracle Advanced Compression, 160
Oracle Clusterware Control (CRSCTL) utility, 283
Oracle Database 12c Reference Guide, 36
Oracle Database SecureFiles and Large Objects Developer’s Guide, 1
Oracle Exadata Expert’s Handbook, 414
Oracle Golden Gate (OGG), 329
Oracle Optimizer, interaction with SPM
CBO plan does not match SQL plan baseline, 99–104
CBO plan matches SQL plan baseline, 96–99
optimizer mode, selecting, 117–122
overview, 96
SQL plan is not reproducible, 104–108
Oracle products. See specific products.
Oracle Streams, 329
Oracle Tuning Guide and Concepts Manual, 199
ORATOP utility, 278
OS statistics. See AWR (Automatic Workload Repository), OS statistics.
OSWBB (OS Watcher Black Box), 278
Out-of-band-sorts (OOBs), 243
Out-of-space (OOS) errors, 251
Overloaded buffer cache, 239
Overprovisioning, 393
P
Pages, SSDs, 392
PARALLEL command, 319
Parallelism, indexes, 425
Parallelization, 282
Parent latches, 255
Parse calls, analyzing, 219–220
Parses, instance activity statistics, 226
Partial indexes, 429
Partition exchange during migration, 346, 350–351
Partition keys, bind sensitiveness with ACS, 56–57
Partition pruning, indexes, 427
Partition tables, 298
Partitioning
RAC environment, 282
SSDs (solid-state drives), 410
tiering data with SSD partitions, 410–414
PCIe (Peripheral Component Interconnect Express), 395
Pct Get Misses, 253
Pct NoWait Misses, 253
PCTSPACE parameter, 314
Performance
effects of storage parameters, 9
full table scan, SSDs, 404–405
increasing throughput, example, 8
operating system performance metrics, capturing, 278
RAC databases. See Troubleshooting RAC databases.
solving with SSDs. See SSDs (solid-state drives).
Performance issues
indexes. See Indexes, performance issues.
SPM, 86
Performance statistics
DBFC (Database Flash Cache), 400–402
Performance tuning. See also AWR (Automatic Workload Repository).
disk-based backup performance, 189
large databases. See VLDBs (very large databases), performance tuning; XLDBs (extremely large databases), performance tuning.
queries. See SQLT utility.
Peripheral Component Interconnect Express (PCIe), 395
PGA (program global area), memory resources, 157–158
PGA (program global area), statistics
aggregate target histogram, 242–243
aggregate target statistics, 242
cache hit percentages, 241–242
OOBs (out-of-band-sorts), 243
pga_aggregate_target parameter, 262–263
PGA_AGGREGATE_TARGET parameter, 240–244, 359
_pga_max_size parameter, 243, 262–263
PGA_TARGET parameter tuning, 158
Physical corruption, 25
Physical standby database, 328, 330, 333
Piecemeal migration. See Migration methods, piecemeal migration.
Platters, 388
PL/SQL API (SQL*Plus), with Data Pump, 317
Private cluster interconnect, checking, 277
Process allocation latch, 382
SGA breakdown difference, 264
SGA memory summary, 264
summary, 264
Processes, monitoring, 278
ProcWatcher script, 278
Program global area (PGA), 157–158
Programmed migration methods, 346
Q
Query response time, stabilizing. See SPM (SQL plan management).
R
RAC (real application clusters), definition, 275
RAC Configuration Audit Tool, 278
RAC databases
RMAN (Recovery Manager), 189–191
troubleshooting. See Troubleshooting RAC databases.
tuning. See Troubleshooting RAC databases.
RAC databases, analyzing with AWR
cluster interconnects, 210
global cache and enqueue services, 209–210, 268–273
global cache load statistics, 209
global cache times (immediate), 272
global cache transfer (immediate), 272
global cache transfer statistics, 271–272
global cache transfer times, 272
global CR served statistics, 271
global current served statistics, 271
global enqueue statistics, 271
hot blocks, 271
interconnect devices, 273
interconnect ping latency, 272
interconnect throughput by client, 273
RAC statistics (CPU), 208
RACcheck tool. See ORAchk utility.
RAM, SSDs, 389
Range predicate, bind sensitiveness with ACS, 52–55
Range-partitioned indexes, 432
RC (result cache) latches, 383
Reading. See I/O.
Read-only tolerance, migration strategy, 325
Real application clusters (RAC), definition, 275
Real-time vs. near real-time migration strategy, 325
Recover forward forever (RFF), 180–186
Recovery. See Backup and recovery.
Recovery catalogs, retaining data in, 191
Recovery Manager (RMAN). See RMAN (Recovery Manager).
Recovery point objective (RPO), 174
Recovery time objective (RTO), 174
Recursive calls, analyzing, 218
Recursive calls, instance activity statistics, 229
Recursive CPU usage, analyzing, 218
Redo allocation latch, 382
Redo nowait percentage, analyzing, 202
Redo-related instance activity statistics, 229
REMAP_DATA parameter, 314
Renaming tables, 314
REPLICAT processes, 329
Reports, SQLT utility, 447–451
Reproducing a SQL plan baseline. See SQL plan baseline, reproducing.
Resizing, dump files, 319
RESMGR:CPU Quantum wait events, 281
Resource limits, analyzing, 266
Resource management, 280–281, 283, 285–287
Restore points, guaranteed, 179
Restructure SQL statement, 293
Result cache (RC) latches, 383
Retention time, specifying, 20–21
Retention values, disabling autotuning of, 21
Reversibility, migration strategy, 325–326
RFF (recover forward forever), 180–186
RMAN (Recovery Manager). See also Backup and recovery.
memory-related parameters, 189
retaining data in a recovery catalog, 191
RMAN BACKUP FOR TRANSPORT command, 344
Rollback segments, hanging databases, 24
Root pages, 422
Rotational latency, 388
RPO (recovery point objective), 174
RTO (recovery time objective), 174
S
SATA (serial advanced technology attachment), 395
SATA vs. PCIe SSD, 395
Schemas, copying between databases, 304–305
Scrambling sensitive data, 314
SECTION SIZE parameter, 188
SECUREFILE LOBs, migrating from BASICFILE LOBs
poor INSERT performance, 17
SECUREFILE LOBs vs. BASICFILE, 8–11
Seek time, 388
Segment access statistics, 255–257
Sensitive data, scrambling, 314
Sequences and index contention, 442
Serial advanced technology attachment (SATA), 395
servctl config commands, 287–288
Server Control (SRVCTL) utility, 283
Service related statistics, 216–217
Session allocation latch, 382
Session cursor, instance activity statistics, 229–230
SET_GLOBAL_PREFS procedure, 358–359
definition, 368
memory summary, 264
SGA_TARGET parameter tuning, 157–158
Shared pool memory statistics, analyzing, 203
Shared pool statistics, 244–245
SHARED_POOL_RESERVED_SIZE parameter, 379
shared_pool_size parameter, 260–261
SHARED_SERVERS parameter, 243
Short stroking, 389
Simulator lru latch, 382
Single-level cell (SLC) SSDs, 391–392
Skip-scan operations, 430
SLC (single-level cell) SSDs, 391–392
Sleep, definition, 253
Sleep summary, 255
Snapshot too old (STO), 251
Soft parse percentage, analyzing, 203
Soft parsing, 375
Solid-state drives (SSDs). See SSDs (solid-state drives).
SORT_AREA_SIZE parameter, 243
Sorts, instance activity statistics, 230
Space, minimum percentage of free space, setting, 14–17
Spin locks, 369
_spin_count parameter, 254
_spin_count variable, 384
SPM (SQL plan management). See also SQL plan baseline.
bind variables, 86
forced-plan sharing issues, 86
interaction with Oracle Optimizer. See Oracle Optimizer, interaction with SPM.
performance issues, 86
SPM (SQL plan management), adaptive cursor sharing
Oracle 11g Release 11.2.0.3.0, 123–127
Oracle 12c Release 12.1.0.1.0, 128–130
sqcreate.sql script, 446
sqdefparams.sql script, 446–447
SQL (structured query language)
analyzing. See AWR (Automatic Workload Repository), SQL sections.
executing queries on a physical standby database, 451
optimizing. See SQL Tuning Advisor.
performance improvement. See SQL Access Advisor; SQL Performance Advisor.
profiles, 293
query response time, stabilizing. See SPM (SQL plan management).
repairing. See SQL Repair Advisor.
source of, identifying, 218
uppercase vs. lowercase, 218
workload analysis. See SQL Access Advisor.
indexes, creating, 298
materialized views, creating, 298
overview, 295
partition tables, 298
recommending new access structures, 296
from SQL Tuning Sets page, 296
structures runtime options, 297
verifying access structures, 296
workload source runtime options, 296
SQL Advisors Home, 290
SQL Performance Advisor, 301
SQL plan baseline, creating, 293. See also SPM (SQL plan management).
capturing plans automatically, 87–90
loading plans from the cursor cache, 90–92
SQL plan baseline, faking, 92–96
SQL plan baseline, reproducing
adding trailing columns to the index, 112–113
changing the index type, 111–112
optimizer mode, selecting, 117–122
SQL plan is not reproducible, 104–108, 117–122
SQL plan management (SPM). See SPM (SQL plan management).
SQL Repair Advisor, 300
on an individual SQL page, 292
invoking, 290
licensing, 291
on a set of SQL statements, 292
SQL Tuning Advisor, recommendations for
comparing original and new explain plans, 294
creating a SQL plan baseline, 293
a restructure SQL statement, 293
SQL profiles, 293
stale statistics, 293
sqlhc.sql utility, 447
SQL*Plus (PL/SQL API), with Data Pump, 317
comparing query execution times, 451
creator of, 445
executing queries on a physical standby database, 451
identifying bind variables, 451
identifying the worst executing query, 452
overview, 445
SQLT utility, methods
COMPARE, 451
TRCANLZR, 451
TRCASPLIT, 452
TRCAXTR, 452
XPLAIN, 451
XTRSBY, 451
XTRSET, 452
XTRXEC, 451
SRVCTL (Server Control) utility, 283
SSDs (solid-state drives). See also DBFC (Database Flash Cache).
ADO (Automatic Data Optimization), 410
ASM disk groups on Exadata, 416–418
compression, 410
partitioning, 410
redo log optimization, 409–410
tiering data with partitions, 410–414
SSDs (solid-state drives), options
full table scan performance, 404–405
indexed reads, 403
OLTP read/write workload, 403–404
redo log optimization, 409–410
SSDs (solid-state drives), vs. HDDs
actuator arms, 388
blocks, 392
free lists, 393
MLC (multi-level cell), 391–392
NAND flash, 389
overprovisioning, 393
pages, 392
platters, 388
RAM, 389
rotational latency, 388
SATA vs. PCIe SSD, 395
seek time, 388
short stroking, 389
SLC (single-level cell), 391–392
SSDs in Oracle databases, 395
stripe magnetic disks, 389
TLC (triple-level cell), 391–392
transfer time, 388
Stale statistics, 293
Star configuration, 205
START_JOB program, 317
Statistics. See also AWR (Automatic Workload Repository); Optimizer statistics.
GC instance activity, 225
GC load, 209
GTTS, automatic gathering, 358–359
instance recovery, 239
PGA aggregate target, 242
shared pool memory, 203
significantly relevant samples, 259
stale, 293
undo segment statistics, 250–251
VLDB and XLDB optimization, gathering, 168–169
Statistics, indexes
clustering factor, 435
depth, 435
distinct key, 435
leaf block, 435
Statistics, RAC databases
global cache load, 209
global cache transfer, 271–272
global CR served, 271
global current served, 271
global enqueue, 271
RAC statistics (CPU), 208
STATUS command, 319
STO (snapshot too old), 251
STOP_JOB command, 319
Storage tiering, SSDs (solid-state drives), 410–414
Stream pool size, analyzing, 264–266
Streams pool advisory, 245–246
streams_pool_size parameter, 261
STREAMS_POOL_SIZE parameter, 245–246
Stripe magnetic disks, 389
Structured query language (SQL). See SQL (structured query language).
Structures runtime options, 297
Subsets of table data, exporting, 310–313
Summed dirty queue length, instance activity statistics, 230
Synonyms, exporting, 307
System global area (SGA). See SGA (system global area).
System statistics. See AWR (Automatic Workload Repository).
T
Table fetch, instance activity statistics, 230–231
Table metadata, copying, 306
TABLE_EXISTS_ACTION=APPEND option, 320–321
Tables
cardinality estimation, 137–138
compression, 160. See also Compression.
copying between databases, 305
importing partitioned as nonpartitioned, 313
importing partitions as individual tables, 313
renaming, 314
resynchronizing after migration, 347
Tablespace point-in-time recovery (TSPITR), 179
Tablespaces
copying, 306
corruption, undoing. See Undo tablespace corruption.
excluding from recovery, 179
I/O stress, 237
temporary. See Temporary tablespaces.
Tablespaces, importing/exporting
resizing, 314
TABLESPACES parameter, 306
TDB (transportable database), 330, 333–336
TEMPFILE I/O waits, correcting. See also Temporary tablespaces.
inappropriate extent sizing, 364
inappropriate use of GTTs, 364
Temporary tablespace groups (TTGs). See TTGs (temporary tablespace groups).
Temporary tablespaces. See also TEMPFILE I/O waits, correcting.
global. See GTTs (global temporary tablespace groups).
LMTTs (locally managed temporary tablespaces), 354, 355
read-only databases, 354
Test and set instruction, 369
TFA (Transparent File Analyzer), 276
Thread activity, instance activity statistics, 233
Three A’s of troubleshooting, 277
Tiering data with partitions, SSDs (solid-state drives), 410–414
Time model statistics, 211–212
Timing, analyzing, 266
TLC (triple-level cell) SSDs, 391–392
Trace analysis, SQLT utility, 451–452
TRACE parameter, 188
Transaction rollback, instance activity statistics, 231
Transactional capture migration, 327–329
transactions_per_rollback_segment parameter, 248, 250–251
Transfer time, SSDs, 388
Transparent File Analyzer (TFA), 276
Transportable database (TDB), 330, 333–336
Transportable tablespaces (TTS), 331, 336–340
TRANSPORT_TABLESPACES parameter, 306
TRCANLZR method, 451
TRCASPLIT method, 452
TRCAXTR method, 452
TRIM command, 393
Triple-level cell (TLC) SSDs, 391–392
Troubleshooting. See also AWR (Automatic Workload Repository).
backup and recovery, 188
DEBUG parameter, 188
TRACE parameter, 188
Troubleshooting RAC databases. See also ASH (Active Session History); AWR (Automatic Workload Repository), RAC-specific pages.
ADDM (Automatic Database Diagnostic Monitor), 277
ADR (Automatic Diagnostic Repository), 276–277
alert logs, 277
CHM (Cluster Health Monitor), 278
monitoring processes, 278
MOS (My Oracle Support) resources, 278–279
operating system performance metrics, capturing, 278
ORAchk health-check tool, 276
ORATOP utility, 278
OSWBB (OS Watcher Black Box), 278
private cluster interconnect, 277
ProcWatcher script, 278
RAC Configuration Audit Tool, 278
real-time monitoring, 278
TFA (Transparent File Analyzer), 276
three A’s, 277
Troubleshooting RAC databases, best practices
Active Data Guard, 279
AMM (Automatic Memory Management), 281
antivirus software, 281
backup and recovery strategies. See Backup and recovery, backup strategies.
configuring block-checking parameters, 279
configuring failover, 280
CPU management, 281
EtherChannel, 279
Exachk utility, 280
Flashback options, 280
MAA (Maximum Availability Architecture) guidelines, 279–280
maintaining current versions, 280
memory management, 281
parallelization, 282
partitioning, 282
periodic health checks, 280
protecting against data block corruption, 279
protecting against logical corruption, 280
third-party monitoring tools and utilities, 281
tuning RAC parameters, 281–282
undo retention, setting, 280
Troubleshooting RAC databases, utilities and commands
administrative tasks, 283
checking Clusterware componentry status, 283–284
configuration information, displaying, 287–288
crsctl check commands, 283
crsctl query commands, 284
crsctl status commands, 284–286
CRSCTL (Oracle Clusterware Control) utility, 283
resource management, 283, 285–287
servctl config commands, 287–288
SRVCTL (Server Control) utility, 283
TSPITR (tablespace point-in-time recovery), 179
TTGs (temporary tablespace groups), 158
adding an existing LMTT, 355
creating, 355
multiple, 355
TTS (transportable tablespaces), 331, 336–340
_TUNED_UNDORETENTION parameter, 21
U
Undo Advisor, 21
Undo change vector, instance activity statistics, 231–232
Undo retention, setting, 280
Undo segment statistics, 250–251
autotuning retention values, disabling, 21
data dictionary object block corruption, 30–31
_HIGHTHRESHOLD_UNDORETENTION parameter, 21
physical corruption, 25
retention time, specifying, 20–21
_TUNED_UNDORETENTION parameter, 21
Undo Advisor, 21
_UNDO_AUTOTUNE parameter, 21
UNDO_RETENTION parameter, 20–21
UNDO_TABLESPACE parameter, 19
_UNDO_AUTOTUNE parameter, 21
UNDO_RETENTION parameter, 20–21
UNDO_TABLESPACE parameter, 19
Unique indexes, 432
Unsafe bind variables, 220
Unselective indexes, hiding, 439–441
Unused block compression, 176
User I/O wait time, instance activity statistics, 232
USER_ADVISOR_ACTIONS view, 298–299
USER_ADVISOR_SQLA_WK_STMTS view, 298–299
USER_ADVISOR_TASKS view, 294
V
V$ADVISOR_PROGRESS view, 294
VALIDATE BACKUPSET command, 187
V$BACKUP_ASYNC_IO view, 188
V$BACKUP_SYNC_IO view, 188
Verifying database transportability, 336
Version control
exporting from a higher version to a lower one, 322
maintaining current versions, 280
Version count, 220
V$LATCH view, 372
ADO (Automatic Data Optimization), 160–162
Advanced Index Compression, 162
Advanced Row Compression, 160
data warehouse templates, 154–155
HCC (Hybrid Columnar Compressions), 160
index partitioning, local vs. global, 159
limiting the number of datafiles, 156–157
optimal data block size, 155–156
Oracle Advanced Compression, 160
PGA (program global area), 157–158
SGA (system global area), 157–158
table compression, 160
temporary tablespace groups, 158
VLDBs (very large databases), gathering optimizer statistics
gathering statistics concurrently, 168–169
getting ESTIMATE_PERCENT value, 170
incremental statistics synopsis, 166–168
interobject parallelism, 168
VLDBs (very large databases), performance tuning
common issues, 162
indexes and data loading, example, 164–165
maximizing resource utilization, 165–166
suboptimal application coding, example, 162–163
V$MEMORY_TARGET_ADVICE view, 157–158
V$PGA_TARGET_ADVICE view, 157–158
V$SGA_TARGET_ADVICE view, 157–158
W
Wait event histograms, 215–216
Wait events, analyzing, 203–206
Wait for other processes, 239
WAIT_FOR_JOB program, 317
Window of inopportunity, 325
Work area instance activity statistics, 232
Workload source runtime options, 296
Writing. See I/O.
X
XA (eXtended Architecture), hanging databases, 22–24
XA (X/Open XA), distributed transaction issues
free global transaction table entry wait event, 460–462
information exists, transaction missing, 457–458
monitoring distributed transactions, 462–464
ORA-1591 has no corresponding information, 458–460
transaction hangs after COMMIT or ROLLBACK, 460–462
XLDBs (extremely large databases)
ADO (Automatic Data Optimization), 160–162
Advanced Index Compression, 162
Advanced Row Compression, 160
data warehouse templates, 154–155
HCC (Hybrid Columnar Compressions), 160
index partitioning, local vs. global, 159
limiting the number of datafiles, 156–157
optimal data block size, 155–156
Oracle Advanced Compression, 160
PGA (program global area), 157–158
SGA (system global area), 157–158
table compression, 160
temporary tablespace groups, 158
XLDBs (extremely large databases), gathering optimizer statistics
gathering statistics concurrently, 168–169
getting ESTIMATE_PERCENT value, 170
incremental statistics synopsis, 166–168
interobject parallelism, 168
XLDBs (extremely large databases), performance tuning
common issues, 162
indexes and data loading, example, 164–165
maximizing resource utilization, 165–166
suboptimal application coding, example, 162–163
XPLAIN method, 451
XTRSBY method, 451
XTRSET method, 452
XTRXEC method, 451
XTTS (cross-platform transportable tablespaces), 331, 340–343