Index

@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

@

A

• access paths
  • bounded range on indexed columns
  • cluster join
  • composite index
  • full table scan
  • hash cluster key
  • indexed cluster key
  • listed
  • MAX or MIN of indexed column
  • optimization
  • ORDER BY on indexed column
  • single row by cluster join
  • single row by hash cluster key (with unique key)
  • single row by ROWID
  • single row by unique or primary key
  • single-column index
  • sort-merge join
  • unbounded range on indexed columns
• adding, dispatcher processes
• ALL
• ALL_ROWS hint [2]
• allocating, extents
• allocation, of memory
• ALTER SESSION command
  • examples
  • SET SESSION_CACHED_CURSORS
• ANALYZE command
  • examples
• AND_EQUAL hint [2]
• anonymous PL/SQL blocks
  • application uses
  • performance benefits
• ANY
• applications
  • client/server
  • decision support [2] [3]
  • distributed databases
  • multi-purpose
  • OLTP
  • parallel query option
  • Parallel Server
  • registering with the database
  • scientific or statistical
  • using PL/SQL
• array processing
• assigning, rollback segments to transactions

  B

• BEGIN_DISCRETE_TRANSACTION procedure
• BETWEEN
• bind variables
  • optimization
  • shared SQL areas
• bottlenecks
  • disk I/O
  • identifying
  • memory
• buffer cache
  • memory allocation
  • reducing cache misses
  • tuning
• buffers
  • determining number to add
  • when to reduce number of
• BUSY column, V$DISPATCHER table

  C

• CACHE hint
• cartesian products
• chained rows
• Checkpoint process (CKPT)
  • behavior on checkpoints
  • enabling [2]
• CHECKPOINT_PROCESS parameter, setting
• checkpoints
  • choosing checkpoint frequency
  • effect on recovery time performance
  • effect on runtime performance
  • redo log maintenance
  • signalling DBWR to write
  • tuning
• CHOOSE
• CHOOSE hint
• client/server applications
• CLUSTER hint
• cluster joins
• clusters
  • hash
    • searches [2]
  • how to use
  • index
  • scans
  • tradeoffs
• columns, choosing for indexes
• composite indexes
• consistent gets statistic [2] [3]
  • calculating hit ratio [2]
• consistent mode, number of buffers retrieved
• contention, disk access [2]
  • free lists [2]
  • memory
  • memory access
  • parallel query option
  • redo allocation latch
  • redo copy latches
  • rollback segments
  • tuning
  • V$SESSION_WAIT
• controling transactions
• cost-based optimization [2]
• count column
  • SQL trace facility output
  • X$KCBCBH table
  • X$KCBRBH table
• cpu column, SQL trace facility output
• CREATE CLUSTER command
• CREATE INDEX command
  • examples
  • NOSORT option
• CREATE TABLE command
  • examples
  • parallelism
  • STORAGE clause
  • syntax
  • TABLESPACE clause
• CREATE TABLESPACE command
  • DATAFILE clause
  • examples
• cross joins
• current column, SQL trace facility output
• current mode, number of buffers retrieved
• CURSOR_NUM column, TKPROF_TABLE
• CURSOR_SPACE_FOR_TIME parameter, setting
• cursors, creating

  D

• data definition statements (DDL), processing
• data dictionary cache
  • reducing cache misses
  • tuning
• data dictionary, views used in optimization
• data manipulation statements (DML), processing
• Database Writer process (DBWR), behavior on checkpoints
• databases, distributed, statement optimization
• DATAFILE clause
  • CREATE TABLESPACE command
  • examples
• datafiles
  • parallel query option and
  • placement on disk
• DATE_OF_INSERT column, TKPROF_TABLE
• db block gets statistic [2] [3]
  • calculating hit ratio [2]
• DB_BLOCK_BUFFERS parameter
  • reducing buffer cache misses
  • removing unneeded buffers
• DB_BLOCK_LRU_EXTENDED_STATISTICS parameter, setting
• DB_BLOCK_LRU_STATISTICS parameter, setting
• DB_FILE_MULTIBLOCK_READ_COUNT parameter
  • cost-based optimization
  • optimization and
• DB_FILE_MULTIBLOCK_READ_COUNT parameter
• DBMS_APPLICATION_INFO package [2]
• DBMS_SHARED_POOL package
• DBMS_SYSTEM package
• DBMSPOOL.SQL script
• DBMSUTL.SQL
• decision support [2]
  • tuning
  • with OLTP
• define phase of query processing
• degree of parallelism [2]
  • between operations
  • combining operations
  • default
  • EXPLAIN PLAN command
  • hints
  • setting [2]
• DEPTH column, TKPROF_TABLE
• describe phase of query processing
• designing and tuning
• discrete transactions
  • errors
  • example
  • processing
  • usage notes
  • when to use
• disk column, SQL trace facility output
• disks
  • avoiding contention
  • contention
    • parallel query option
  • distributing I/O
  • monitoring I/O
  • monitoring OS file activity
  • placement of datafiles
  • placement of redo log files
  • striping
    • manually
• dispatcher processes (Dnnn), adding
• distributed databases
  • statement optimization
• distributed processing environment, data manipulation statements
• distributing I/O [2]
  • parallel query option
• DIUTIL package
• dynamic extension
  • avoiding
• dynamic performance tables, enabling statistics

  E

• elapsed column, SQL trace facility output
• enabling
  • Checkpoint process (CKPT)
  • SQL trace facility
• equijoins, defined
• errors, during discrete transactions
• examples
  • ALTER SESSION command
  • ANALYZE command
  • CREATE TABLESPACE command
  • CREATE INDEX command
  • CREATE TABLE command
  • DATAFILE clause
  • discrete transactions
  • execution plan
  • EXPLAIN PLAN output [2] [3] [4]
  • full table scan
  • indexed query
  • NOSORT option
  • SET TRANSACTION command
  • SQL trace facility output
  • STORAGE clause
  • table striping
  • TABLESPACE clause
• execution plans
  • examples [2] [3]
  • execution sequence of
  • overview of
  • TKPROF [2]
  • viewing
• EXPLAIN PLAN command
  • about
  • examples of output [2] [3]
  • exapmles of output
  • invoking with the TKPROF program
  • PLAN_TABLE
• extension, generates recursive calls
• extents, allocating to avoid extension

  F

• fetching rows in a query
• FIRST_ROWS hint [2]
• free lists
  • adding
  • contention [2]
  • reducing contention
• FULL hint [2]
• full table scans [2] [3]
  • example
  • parallel query option

  G

• GETMISSES column, V$ROWCACHE table [2]
• GETS column
  • V$LATCH table
  • V$ROWCACHE table [2]
• goals for tuning

  H

• HASH hint
• HASH parameter, CREATE CLUSTER command
• hashing, how to use
• HASHKEYS parameter, CREATE CLUSTER command
• HIGH_VALUE column, of USER_TAB_COLUMNS
• hints
  • access methods
  • ALL_ROWS
  • AND_EQUAL [2]
  • CACHE
  • CHOOSE
  • CLUSTER
  • degree of parallelism [2]
  • FIRST_ROWS
  • FULL [2]
  • HASH
  • how to use
  • INDEX [2]
  • INDEX_ASC
  • INDEX_DESC
  • join operations
  • join orders
  • NOCACHE
  • NOPARALLEL hint
  • optimization approach and goal
  • ORDERED [2]
  • PARALLEL hint
  • parallel query option
  • PUSH_SUBQ
  • ROWID
  • RULE
  • USE_CONCAT
  • USE_MERGE
  • USE_NL
• HOLD_CURSOR

  I

• I/O, distributing [2]
  • parallel query option
  • tuning
• ID column, PLAN_TABLE table
• IDLE column, V$DISPATCHER table
• IN
• INDEX hint [2]
• INDEX_ASC hint
• INDEX_DESC hint
• indexes
  • avoiding the use of
  • choosing columns for
  • composite
    • searches on
  • creating in parallel
  • ensuring the use of
  • example
  • how to use
  • modifying values of
  • optimization and
  • parallel creation
  • placement on disk
  • range scans
  • scans
  • searches on
    • using MAX or MIN
    • using ORDER BY
  • selectivity of
  • statement conversion and
  • STORAGE clause with parallel query option
  • unique scans
• INDX column
  • X$KCBCBH table
  • X$KCBRBH table
• initialization parameters
  • DISCRETE_TRANSACTIONS_ENABLED
  • MAX_DUMP_FILE_SIZE
  • OPTIMIZER_MODE [2] [3]
  • PRE_PAGE_SGA
  • SESSION_CACHED_CURSORS
  • SORT_DIRECT_WRITES [2]
  • SORT_WRITE_BUFFER_SIZE
  • SORT_WRITE_BUFFERS
  • SQL_TRACE
  • TIMED_STATISTICS
  • USER_DUMP_DEST
• instances, limiting for parallel queries
• inter-operator parallelism
• intra-operator parallelism

  J

• joins
  • cartesian products
  • cluster [2]
    • searches on
  • convert to subqueries
  • cross
  • equijoins
  • execution plans and
  • nested loops
    • cost-based optimization
  • nonequijoins
  • optimization of
  • outer
  • parallel query option
  • sort-merge
  • sort-merge searches
  • sort-merge

  K

• KEEP procedure
• keys, searches

  L

• latches
  • redo allocation latch
  • redo copy latches
• library cache
  • memory allocation
  • tuning
• LIKE
• log
• log switches, tuning checkpoints
• Log Writer process (LGWR), tuning
• LOG_BUFFER parameter, setting
• LOG_CHECKPOINT_INTERVAL parameter, guidelines
• LOG_CHECKPOINT_TIMEOUT parameter, guidelines
• LOG_SIMULTANEOUS_COPIES parameter
  • setting
• LOG_SMALL_ENTRY_MAX_SIZE parameter, setting [2]
• LOW_VALUE column, of USER_TAB_COLUMNS

  M

• Managment Information Base (MIB)
• MAX operator
• max session memory statistic
• MAX_DUMP_FILE_SIZE
• MAXOPENCURSORS
• memory allocation
  • buffer cache
  • importance
  • library cache
  • shared SQL areas
  • sort areas
  • tuning [2]
  • users
• migrated rows
• MIN operator
• mirroring, redo log files
• MISSES column, V$LATCH table
• monitoring the system
• MTS_MAX_DISPATCHERS parameter, tuning dispatchers
• MTS_MAX_SERVERS parameter, tuning servers
• multi-block reads
• multi-purpose applications
• multi-threaded server
  • reducing contention
  • shared pool and
  • tuning

  N

• NAMESPACE column, V$LIBRARYCACHE table
• nested loops joins
  • cost-based optimization
• networks, minimizing traffic
• NLS_SORT parameter, ORDER BY access path
• NOCACHE hint
• nonequijoins
• NOPARALLEL hint
• NOSORT option
  • choosing when to use
  • CREATE INDEX command
  • examples
  • performance benefits
• NUM_DISTINCT column, of USER_TAB_COLUMNS
• NUM_ROWS column, of USER_TABLES view

  O

• OBJECT_INSTANCE column, PLAN_TABLE table
• OBJECT_NAME column, PLAN_TABLE table
• OBJECT_NODE column, PLAN_TABLE table
• OBJECT_OWNER column, PLAN_TABLE table
• OBJECT_TYPE column, PLAN_TABLE table
• online redo log, increasing size
• online transaction processing (OLTP)
  • rollback segment extension
  • with decision support
• OPEN_CURSORS parameter
  • allocating more private SQL areas
  • increasing cursors per session
• operating system, monitoring disk I/O [2]
  • tuning [2]
• OPERATION column
  • PLAN_TABLE table
  • values
• operators
  • MAX
  • MIN
• OPTIMAL storage parameter
• optimization
  • choosing an approach and goal for
  • choosing the approach
  • conversion of expressions and predicates
  • cost-based [2]
    • choosing an access path
    • examples of
    • remote databases and
    • when to use
  • hints
  • manual
  • rule-based [2]
    • choosing an access path
    • examples of
    • when to use
  • selectivity of queries and
  • transitivity and
• optimizer
• OPTIMIZER column, PLAN_TABLE
• OPTIMIZER_GOAL option
  • ALTER SESSION command
  • of ALTER SESSION command
• OPTIMIZER_MODE [2] [3] [4]
  • hints affecting
• optimizer
  • parallel queries
  • when used
• OPTIONS column, PLAN_TABLE table
• Oracle Call Interface (OCI)
  • bind variables
  • control of parsing and private SQL areas
• Oracle Forms
  • control of parsing and private SQL areas
  • PL/SQL
  • PL/SQL engine
• Oracle Parallel Server
• Oracle Precompilers
  • bind variables
  • control of parsing and private SQL areas
  • PL/SQL
• Oracle Server Manager
• Oracle Server
  • client/server configuration
  • configurations
  • PL/SQL engine
  • SQL processing
  • transactions
• ORDERED hint [2]
• OTHER column, PLAN_TABLE table
• outer joins
• overloaded disks

  P

• packages, DBMS_APPLICATION_INFO [2]
  • DBMS_SHARED_POOL
  • DBMS_TRANSACTION
  • DIUTIL
  • performance benefits
  • registering with the database [2]
  • STANDARD
• paging
  • library cache
  • reducing
  • SGA
• PARALLEL hint
• parallel query option [2]
  • combining operations
  • degree of parallelism
    • setting
  • EXPLAIN PLAN command
  • full table scans
  • hints
  • index creation
  • inter-operator parallelism
  • intra-operator parallelism
  • multi-threaded server
  • number of server processes
  • operations in execution plan
  • optimizer
  • Parallel Server and [2]
  • partitioning rows
  • query coordinator
  • query servers [2]
  • SORT_DIRECT_WRITES parameter
  • summary or rollup tables
  • tuning
  • tuning query servers [2]
• Parallel Server
  • parallel query option [2]
• PARALLEL_MAX_SERVERS parameter
• PARALLEL_MIN_SERVERS parameter
• PARALLEL_SERVER_IDLE_TIME parameter
• PARAMETER column, V$ROWCACHE tabe
• PARENT_ID column, PLAN_TABLE table
• parsing
  • Oracle Call Interface (OCI)
  • Oracle Forms
  • Oracle Precompilers
  • reducing unnecessary calls
  • SQL statements
• performance
  • bottlenecks
  • client/server applications
  • decision support applications
  • different types of applications
  • distributed databases
  • monitoring registered applications
  • multi-purpose applications
  • OLTP applications
  • packages benefits
  • Parallel Server
  • PL/SQL benefits
  • scientific applications
  • stored procedures benefits
  • viewing execution plans
• PHYRDS column, V$FILESTAT table
• physical reads statistic
  • calculating hit ratio [2]
• PHYWRTS column, V$FILESTAT table
• PINS column, V$LIBRARYCACHE table
• PL/SQL
  • anonymous blocks
  • application uses
  • location of PL/SQL engine
  • Oracle Forms
  • Oracle Precompilers
  • performance benefits
  • SQL*Plus
  • stored procedures
  • tuning PL/SQL areas
• PLAN_TABLE table
  • ID column
  • OBJECT_INSTANCE column
  • OBJECT_NAME column
  • OBJECT_NODE column
  • OBJECT_OWNER column
  • OBJECT_TYPE column
  • OPERATION column
  • OPTIMIZER column
  • OPTIONS column
  • OTHER column
  • PARENT_ID column
  • POSITION column
  • REMARKS column
  • SEARCH_COLUMNS column
  • STATEMENT_ID column
  • structure
  • TIMESTAMP column
• POSITION column, PLAN_TABLE table
• PRE_PAGE_SGA parameter
• primary keys
  • optimization
  • searches
• private SQL areas, reuse by multiple SQL statements
• procedures
• process priority
• PROCESSES parameter, increasing for CKPT
• processing, distributed processing environment [2]
  • queries
• production systems, tuning
• PUSH_SUBQ hint

  Q

• queries
  • ad hoc
  • avoiding the use of indexes
  • compound
    • optimization of
    • ORs converted to
  • define phase
  • describe phase
  • ensuring the use of indexes
  • fetching rows
  • parallel processing
  • processing
  • selectivity
• query column, SQL trace facility output
• query coordinator
• query plan
• query server process
  • about [2]
  • tuning [2]

  R

• recovery from instance failure, effect of checkpoints
• recursive calls
  • detected by the SQL trace facility
  • dynamic extension
  • statistic
• recursive SQL, shared SQL
• redo allocation latch
  • contention
• redo copy latches
  • choosing how many [2]
  • contention
  • creating more
• redo log files
  • mirroring
  • placement on disk
  • tuning checkpoints
• redo log space requests statistic
• reducing
  • buffer cache misses
  • contention
    • OS processes
    • query servers
    • redo log buffer latches
    • shared servers
  • data dictionary cache misses
  • disk contention
  • library cache misses
  • network traffic
  • number of database buffers
  • paging and swapping
  • query execution time
    • parallel query option
  • rollback segment contention
  • unnecessary parse calls
• registering applications with database
• RELEASE_CURSOR
• RELOADS column, V$LIBRARYCACHE table
• REMARKS column, PLAN_TABLE table
• response time
  • cost-based approach
  • optimizing [2]
• rollback segments
  • assigning to transactions
  • choosing how many
  • contention
  • creating
  • detecting dynamic extension
  • dynamic extension
  • OLTP applications
• row sources
• ROWID hint
• ROWIDs, table access by
• rows column, SQL trace facility output
• rows
  • fetched
  • row sources
  • ROWIDs used to locate [2]
• RULE hint
  • OPTIMIZER_MODE and
• rule-based optimization [2]

  S

• scans
  • bounded range on indexed columns
  • cluster [2]
  • full table [2]
    • parallel query option
  • hash [2]
  • index [2] [3]
  • range
  • unbounded range on indexed columns
  • unique
• scientific applications
• SEARCH_COLUMN column, PLAN_TABLE table
• segments
• selectivity
  • indexes
  • queries
• Server Manager
  • SHOW SGA command
• session memory statistic
• SESSION_CACHED_CURSORS parameter
• SET TRANSACTION command
  • assigning transactions to rollback segments
  • examples
• shared pool
  • keeping objects pinned in
  • tuning
• shared SQL areas
  • finding large areas
  • identical SQL statements
  • keeping in the shared pool
  • memory allocation
  • statements considered
• SHARED_POOL_SIZE parameter
  • allocating library cache
  • reducing dictioanry cache misses
  • tuning the shared pool
• SHOW SGA command
• Simple Network Management Protocol (SNMP)
  • Management Information Base (MIB)
• SIZES procedure
• SLEEPS column, V$LATCH table
• SNMP
• SOME
• sort areas
  • memory allocation
  • memory required
• sort-merge joins
  • cost-based optimization
• SORT_AREA_RETAINED_SIZE parameter, tuning sorts
• SORT_AREA_SIZE parameter
  • cost-based optimization and
  • tuning sorts
• SORT_DIRECT_WRITES parameter
  • parallel query option
• SORT_WRITE_BUFFERS
• sorts (disk) statistic
• sorts (memory) statistic
• sorts
  • avoiding on index creation
  • parallel query option
  • tuning
• SQL areas, tuning
• SQL statements
  • array processing
  • avoiding the use of indexes
  • binding variables
  • complex
  • converting
  • creating cursors
  • distributed
    • optimization
  • ensuring the use of indexes
  • execution [2]
  • execution plans
  • modifying indexed data
  • optimization of
  • optimization of complex
  • parallel query option
  • parallelizing
  • parsing
  • recursive
  • simple
  • transactions
  • tuning
• SQL trace facility [2]
  • enabling [2]
  • example of output
  • output
  • parse calls
  • steps to follow
  • trace files [2]
• SQL*Plus, PL/SQL
• SQL_STATEMENT column, TKPROF_TABLE
• SQL_TRACE parameter
• STANDARD package
• STATEMENT_ID column, PLAN_TABLE table
• statistical systems
• statistics, consistent gets [2] [3]
  • db block gets [2]
  • dispatcher processes
  • enabling collection
  • max session memory
  • optimizer use of
  • physical reads
  • query servers
  • recursive calls
  • redo log space requests
  • session memory
  • shared server processes
  • sorts (disk)
  • sorts (memory)
  • undo block
  • undo header
• STORAGE clause
  • CREATE TABLE command
  • examples
  • OPTIMAL
  • parallel query option [2]
• stored procedures, application uses [2]
  • BEGIN_DISCRETE_TRANSACTION
  • KEEP
  • performance benefits [2]
  • READ_MODULE
  • registering with the database
  • SET_ACTION
  • SET_CLIENT_INFO
  • SET_MODULE
  • SIZES
  • UNKEEP
• striping tables
  • examples
• subqueries, converting to joins
• swapping
  • library cache
  • reducing
  • SGA
• System Global Area (SGA), tuning

  T

• TABLESPACE clause
  • CREATE TABLE command
  • examples
• tables
  • parallel creation
  • placement on disk
  • STORAGE clause with parallel query option
  • striping examples
  • summary or rollup
• throughput
  • cost-based approach
  • optimizing [2]
• TIMED_STATISTICS
• TIMESTAMP column, PLAN_TABLE table
• TKPROF program [2]
  • command line parameters
  • editing the output SQL script
  • example of output
  • generating the output SQL script
  • syntax
  • using the EXPLAIN PLAN command
• TKPROF_TABLE
  • columns of
  • querying
• TOTALQ column, V$QUEUE table [2]
• trace facility
• transactions
  • assigning rollback segments
  • discrete
• Trusted Oracle Server, application registration
• tuning
  • and design
  • buffer cache
  • checkpoints
  • client/server applications
  • contention
  • data dictionary cache
  • decision support systems
  • distributed databases
  • goals
  • I/O
  • identifying bottlenecks
  • library cache
  • memory allocation [2]
  • monitoring registered applications
  • multi-purpose applications
  • multi-threaded server
  • OLTP applications
  • operating system [2]
  • parallel query option [2] [3]
  • Parallel Server
  • production systems
  • query servers [2]
  • scientific or statistical systems
  • shared pool [2] [3]
  • sorts
  • SQL and PL/SQL areas
  • SQL statements
  • System Global Area (SGA)

  U

• undo header statistic
• undo block statistic
• unique keys
  • optimization
  • searches
• UNKEEP procedure
• UNRECOVERABLE option
• USE_CONCAT hint
• USE_MERGE hint
• USE_NL hint
• USER_DUMP_DEST
• USER_ID column, TKPROF_TABLE
• USER_TAB_COLUMNS view [2]
• USER_TABLES view
• users, memory allocation
• UTLCHAIN.SQL
• UTLXPLAN.SQL

  V

• V$ dynamic performance views
• V$DATAFILE view, disk I/O
• V$DISPATCHER view, using
• V$FILESTAT view
  • disk I/O
  • PHYRDS column
  • PHYWRTS column
• V$LATCH view
  • GETS column
  • MISSES column
  • SLEEPS column
  • using
• V$LIBRARYCACHE view
  • NAMESPACE column
  • PINS column
  • RELOADS column
  • using
• V$QUEUE view
  • examining wait times
  • identifying contention
• V$ROWCACHE view
  • data dictionary cache performance statistics
  • GETMISSES column [2]
  • GETS column [2]
  • PARAMETER column
  • using
• V$SESSION_WAIT view
• V$SESSION, application registration
• V$SESSTAT view, using
• V$SQLAREA, application registration [2]
• V$SYSSTAT view
  • detecting dynamic extension
  • examining recursive calls
  • redo buffer space
  • tuning sorts
  • using
• V$WAITSTAT view
  • reducing free list contention
  • rollback segment contention
• views, optimization
• virtual tables
  • X$KCBCBH table
  • X$KCBRBH table

  W

• WAIT column, V$QUEUE table [2]
• writing SQL statements

  X

• X$KCBCBH table
  • buffer cache performance statistics
  • COUNT column
  • enabling use
  • INDX column
• X$KCBRBH table
  • buffer cache performance statistics
  • COUNT column
  • enabling use
  • INDX column

  Y

  Z