Index

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

A

  • accepted plans 1
  • access paths
    • execution plans 1
    • full table scan 1
    • full table scans 1 , 2
  • adaptive plans 1
  • adaptive query optimization
    • adaptive plans 1
    • adaptive query plans 1 , 2 , 3
    • controlling 1
    • dynamic statistics 1
  • adaptive query plans
    • about 1
    • bitmap index pruning 1
    • cardinality misestimates 1
    • enabling 1
    • join methods 1
    • optimizer statistics collector 1
    • parallel distribution methods 1
    • subplans 1
  • adaptive sampling 1
  • adaptive statistics
    • automatic reoptimization 1
    • dynamic statistics 1
    • SQL plan directives 1 , 2
    • when enabled 1
  • ADDM 1
  • advisors
    • Optimizer Statistics Advisor 1 , 2 , 3
  • ALTER SESSION statement
    • examples 1
  • antijoins 1
  • applications
    • implementing 1
  • approximate query processing 1
  • automatic plan capture
    • creating automatic capture filters 1
    • enabling 1
  • automatic reoptimization
    • cardinality misestimates 1
    • performance feedback 1
    • statistics feedback 1
  • automatic statistics collection 1
  • Automatic Tuning Optimizer
    • SQL profiles 1
  • Automatic Workload Repository (AWR) 1

B

  • band joins 1
  • big bang rollout strategy 1
  • bind-aware cursors 1
  • bind-sensitive cursors 1
  • bind variable peeking 1
  • bind variables
    • bind peeking 1
  • bitmap indexes
    • inlist iterator 1
    • on joins 1
    • pruning 1
    • when to use 1
  • BOOLEAN data type 1
  • bulk loads
    • creating histograms 1
    • gathering index statistics 1
    • restrictions for online statistics gathering 1
  • BYTES column
    • PLAN_TABLE table 1 , 2

C

  • cardinality 1 , 2 , 3 , 4 , 5 , 6 , 7 , 8 , 9 , 10 , 12
  • CARDINALITY column
    • PLAN_TABLE table 1 , 2
  • cartesian joins 1
  • child cursors 1
  • clusters
    • sorted hash 1
  • clusters, table 1
  • column groups
    • optimizer statistics 1
  • column group statistics
    • automatic and manual 1
  • columns
    • cardinality 1
    • to index 1
  • compilation, SQL 1 , 2 , 3
  • composite indexes 1
  • composite partitioning
    • examples of 1
  • concurrent statistics gathering 1 , 2
  • CONFIGURE procedure 1
  • consistent mode
    • TKPROF 1
  • constraints 1
  • COST column
    • PLAN_TABLE table 1 , 2
  • current mode
    • TKPROF 1
  • CURSOR_NUM column
    • TKPROF_TABLE table 1
  • CURSOR_SHARING initialization parameters 1
  • cursor-duration temporary tables 1
  • cursor merging 1
  • cursors
    • about 1
    • bind-aware 1
    • bind-sensitive 1
    • bind variable peeking 1
    • child 1
    • invalidating 1
    • marked DEFERRED INVALIDATION 1
    • marked rolling invalid 1
    • parent 1 , 2
    • parsing and 1
    • sharing 1 , 2 , 3
  • cursor sharing
    • forcing 1

D

  • data
    • modeling 1
  • database operations
    • attributes 1
    • composite 1 , 2
    • concepts 1
    • definition 1 , 2
    • monitoring 1
    • simple 1 , 2
  • database operations, monitoring
    • composite 1
    • composite operations 1 , 2
    • creating database operations 1
    • enabling with hints 1
    • enabling with initialization parameters 1
    • Enterprise Manager interface 1
    • PL/SQL interface 1
    • purpose 1
    • real-time SQL 1
    • simple operations 1
    • using Cloud Control 1
  • data blocks 1
  • data dictionary cache 1
  • Data Pump
    • Export utility
      • statistics on system-generated columns names 1
    • Import utility
      • copying statistics 1
  • data skew 1
  • data types
    • BOOLEAN 1
  • DATE_OF_INSERT column
    • TKPROF_TABLE table 1
  • DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter 1
  • DBA_ADVISOR_EXECUTIONS view 1
  • DBA_IND_STATISTICS view 1
  • DBA_OPTSTAT_OPERATION_TASKS view 1
  • DBA_OPTSTAT_OPERATIONS view 1
  • DBA_SQL_MANAGEMENT_CONFIG view 1 , 2
  • DBA_TAB_MODIFICATIONS view 1
  • DBA_TAB_STATISTICS view 1
  • DBMS_ADVISOR package 1
  • DBMS_MONITOR package
    • end-to-end application tracing 1
  • DBMS_SPM package 1
  • DBMS_SQLTUNE package
    • SQL Tuning Advisor 1
  • DBMS_XPLAN 1
  • DDL (data definition language)
    • processing of 1
  • deadlocks 1
  • debugging designs 1
  • dedicated server 1
  • deferred invalidation 1
  • density, histogram 1
  • DEPTH column
    • TKPROF_TABLE table 1
  • designs
    • debugging 1
    • testing 1
    • validating 1
  • development environments 1
  • DIAGNOSTIC_DEST initialization parameter 1
  • disabled constraints 1
  • DISTRIBUTION column
    • PLAN_TABLE table 1 , 2
  • domain indexes
    • and EXPLAIN PLAN 1
    • using 1
  • DROP_SQL_PLAN_DIRECTIVE procedure 1
  • dynamic statistics
    • controlling 1
    • process 1
    • sampling levels 1
    • setting statistics level 1
    • when to use 1

E

  • enabled constraints 1
  • endpoint repeat counts, in histograms 1
  • end-to-end application tracing
    • action and module names 1
    • creating a service 1
    • DBMS_APPLICATION_INFO package 1
    • DBMS_MONITOR package 1
    • multitenant environment 1
    • overview 1
    • purpose 1
  • enforced constraints 1
  • ESS
    • See: Expression Statistics Store
  • examples
    • ALTER SESSION statement 1
    • EXPLAIN PLAN output 1
  • EXECUTE_TASK procedure 1
  • execution plans
    • about 1
    • adaptive 1 , 2 , 4
    • examples 1
    • overview of 1
    • TKPROF 1 , 2
    • V$ views 1
    • viewing with the utlxpls.sql script 1
  • execution trees 1
  • EXPLAIN PLAN statement
    • access paths 1 , 2
    • and full partition-wise joins 1
    • and partial partition-wise joins 1
    • and partitioned objects 1
    • basic steps 1
    • examples of output 1
    • invoking with the TKPROF program 1
    • PLAN_TABLE table 1
    • restrictions 1
    • viewing the output 1
  • expression statistics
    • creating 1
  • Expression Statistics Store (ESS) 1
  • extended statistics 1
  • extensions 1
  • external tables
    • guideline for gathering optimizer statistics 1

F

  • fixed objects
    • gathering statistics for 1 , 2
  • FLUSH_DATABASE_MONITORING_INFO procedure 1
  • FLUSH_SQL_PLAN_DIRECTIVE procedure 1
  • frequency histograms 1 , 2 , 3
  • full outer joins 1
  • full partition-wise joins 1
  • full table scans 1 , 2 , 3
  • function-based indexes 1

G

  • global temporary tables 1

H

  • hard parses 1
  • hard parsing 1 , 2
  • hash clusters
    • sorted 1
  • hashing 1
  • hash joins
    • cost-based optimization 1
  • hash partitions
    • examples of 1
  • height-balanced histograms 1 , 2
  • high-load SQL
    • tuning 1 , 2
  • hints, optimizer
    • FULL 1
    • GATHER_OPTIMIZER_STATISTICS 1
    • NO_GATHER_OPTIMIZER_STATISTICS 1
    • NO_INDEX 1
    • NO_MONITOR 1
    • OPTIMIZER_FEATURES_ENABLE 1
  • histograms
    • cardinality algorithms 1
    • creation during bulk loads 1
    • criteria for creation 1
    • data skew 1
    • definition 1
    • density 1
    • endpoint numbers 1
    • endpoint repeat counts 1
    • endpoint values 1
    • frequency 1 , 2 , 3
    • height-balanced 1 , 2
    • hybrid 1 , 2
    • NDV 1
    • nonpopular values 1
    • popular values 1
    • purpose 1
    • top frequency 1 , 2
  • hybrid histograms 1 , 2
  • HyperLogLog algorithm 1

I

  • I/O
    • reducing 1
  • ID column
    • PLAN_TABLE table 1 , 2
  • incremental statistics
    • maintenance 1 , 2
    • synopses 1
  • index clustering factor 1
  • indexes
    • avoiding the use of 1
    • bitmap 1 , 2
    • choosing columns for 1
    • composite 1
    • domain 1
    • dropping 1
    • enforcing uniqueness 1
    • ensuring the use of 1
    • function-based 1
    • improving selectivity 1
    • low selectivity 1
    • modifying values of 1
    • non-unique 1
    • re-creating 1
    • scans 1
    • selectivity of 1
  • index statistics
    • index clustering factor 1
  • initialization parameters
    • DIAGNOSTIC_DEST 1
  • In-Memory Expressions (IMEs)
    • Expression Statistics Store (ESS) 1
  • in-memory table scans
    • controls 1
    • example 1
    • when chosen 1
  • inner loop, in nested loops joins 1
  • invalidation, deferred 1

J

  • join methods
    • nested loops 1 , 2
  • joins
    • antijoins 1
    • band 1
    • cartesian 1
    • full outer 1
    • hash 1
    • nested loops 1 , 2 , 3
    • nested loops and cost-based optimization 1
    • order 1
    • outer 1
    • partition-wise
      • examples of full 1
      • examples of partial 1
      • full 1
    • semijoins 1
    • sort merge 1
    • sort-merge and cost-based optimization 1

L

  • latches
    • parsing and 1
  • library cache 1
  • library cache miss 1
  • locks
    • deadlocks 1

M

  • manual plan capture 1
  • materialized views
    • query rewrite 1
  • modeling
    • data 1
  • multiversion read consistency 1

N

  • NDV 1
  • nested loop joins
    • how they work 1
  • nested loops
    • driving table 1
  • nested loops joins
    • cost-based optimization 1
    • USE_NL hint 1
  • NO_INDEX hint 1
  • nonpopular values, in histograms 1
  • NOT IN subquery 1

O

  • OBJECT_INSTANCE column
    • PLAN_TABLE table 1 , 2
  • OBJECT_NAME column
    • PLAN_TABLE table 1 , 2
  • OBJECT_NODE column
    • PLAN_TABLE table 1 , 2
  • OBJECT_OWNER column
    • PLAN_TABLE table 1 , 2
  • OBJECT_TYPE column
    • PLAN_TABLE table 1 , 2
  • OPERATION column
    • PLAN_TABLE table 1 , 2
  • optimization
    • adaptive query 1
  • optimization, SQL 1
  • optimizer
    • adaptive 1
    • definition 1
    • environment 1
    • estimator 1
    • execution 1
    • Expression Statistics Store (ESS) 1
    • goals 1
    • purpose of 1
    • row sources 1
    • statistics 1
    • throughput 1
  • OPTIMIZER_FEATURES_ENABLE initialization parameter 1
  • OPTIMIZER column
    • PLAN_TABLE 1 , 2
  • optimizer environment 1
  • optimizer hints
    • FULL 1
    • MONITOR 1
    • NO_INDEX 1
    • OPTIMIZER_FEATURES_ENABLE 1
  • optimizer statistics
    • adaptive statistics 1
    • automatic collection 1 , 2
    • bulk loads 1
    • cardinality 1
    • collection 1
    • column group 1
    • column groups 1 , 2 , 3
    • determining staleness 1
    • dynamic 1 , 2 , 3 , 4 , 5
    • extended 1
    • gathering 1
    • gathering concurrently 1 , 2 , 3
    • gathering during bulk loads 1
    • getting 1
    • guideline for gathering external table 1
    • histograms 1
    • incremental 1 , 2 , 3 , 4
    • index 1
    • manual collection 1
    • monitoring collection 1
    • Optimizer Statistics Advisor 1 , 2 , 3
    • pluggable databases and 1
    • preferences 1 , 2 , 3 , 4
    • setting 1 , 2 , 3
    • SQL plan directives 1 , 2
    • system 1
    • temporary 1
  • Optimizer Statistics Advisor
    • about 1
    • actions 1
    • basic tasks 1
    • benefits 1
    • creating an object filter 1
    • creating a rule filter 1 , 2
    • creating a task 1 , 2
    • DBA_ADVISOR_EXECUTIONS view 1
    • executing a task 1
    • filters 1
    • findings 1
    • generating a script 1
    • implementing actions automatically 1
    • listing advisor tasks 1
    • modes of operation 1
    • problems with script-based approach 1
    • purpose 1
    • recommendations 1
    • rules 1
  • Optimizer Statistics Advisor PL/SQL package
    • DBMS_STATS 1
  • optimizer statistics collection 1
  • optimizer statistics collectors 1
  • OPTIONS column
    • PLAN_TABLE table 1 , 2
  • OR expansion 1
  • OTHER_TAG column
    • PLAN_TABLE table 1 , 2
  • OTHER column
    • PLAN_TABLE table 1 , 2
  • outer joins 1
  • outer loops, in nested loops join 1

P

  • packages
    • DBMS_ADVISOR 1
  • PARENT_ID column
    • PLAN_TABLE table 1 , 2
  • parent cursors 1 , 2
  • parse calls 1
  • parsing
    • hard 1
  • parsing, SQL
    • hard 1
    • hard parse 1
    • parse trees 1
    • soft 1
    • soft parse 1
  • PARTITION_ID column
    • PLAN_TABLE table 1 , 2
  • PARTITION_START column
    • PLAN_TABLE table 1 , 2
  • PARTITION_STOP column
    • PLAN_TABLE table 1 , 2
  • partitioned objects
    • and EXPLAIN PLAN statement 1
    • gathering incremental statistics 1 , 2
  • partitioning
    • examples of 1
    • examples of composite 1
    • hash 1
    • range 1
    • start and stop columns 1
  • partition maintenance operations 1
  • partition-wise joins
    • full 1
    • full, and EXPLAIN PLAN output 1
    • partial, and EXPLAIN PLAN output 1
  • performance
    • viewing execution plans 1
  • PLAN_TABLE table
    • BYTES column 1 , 2
    • CARDINALITY column 1 , 2
    • COST column 1 , 2
    • creating 1
    • displaying 1
    • DISTRIBUTION column 1 , 2
    • ID column 1 , 2
    • OBJECT_INSTANCE column 1 , 2
    • OBJECT_NAME column 1 , 2
    • OBJECT_NODE column 1 , 2
    • OBJECT_OWNER column 1 , 2
    • OBJECT_TYPE column 1 , 2
    • OPERATION column 1 , 2
    • OPTIMIZER column 1 , 2
    • OPTIONS column 1 , 2
    • OTHER_TAG column 1 , 2
    • OTHER column 1 , 2
    • PARENT_ID column 1 , 2
    • PARTITION_ID column 1 , 2
    • PARTITION_START column 1 , 2
    • PARTITION_STOP column 1 , 2
    • POSITION column 1 , 2
    • REMARKS column 1 , 2
    • SEARCH_COLUMNS column 1 , 2
    • STATEMENT_ID column 1 , 2
    • TIMESTAMP column 1 , 2
  • plan evolution
    • purpose 1
  • pluggable databases
    • automatic optimizer statistics collection 1
    • manageability features 1
    • SQL management base 1
    • SQL Tuning Advisor 1 , 2
    • SQL tuning sets 1
  • popular values, in histograms 1
  • POSITION column
    • PLAN_TABLE table 1 , 2
  • preferences, optimizer statistics 1 , 2
  • private SQL areas
    • parsing and 1
  • processes
    • dedicated server 1
  • programming languages 1

Q

  • queries
    • avoiding the use of indexes 1
    • ensuring the use of indexes 1
  • query transformations
    • OR expansion 1
    • purpose of table expansions 1
    • query rewrite with materialized views 1
    • star transformations 1
    • table expansion 1 , 2 , 3

R

  • range
    • examples of partitions 1
    • partitions 1
  • Real-Time Database Operations 1
  • Real-Time SQL Monitoring 1 , 2
  • Real-World Performance
    • cursor sharing 1
  • recursive calls 1
  • recursive SQL 1 , 2 , 3
  • REMARKS column
    • PLAN_TABLE table 1 , 2
  • reoptimization, automatic
    • cardinality misestimates 1
    • performance feedback 1
    • statistics feedback 1
  • result sets, SQL 1 , 2
  • rolling invalid cursors 1
  • rollout strategies
    • big bang approach 1
    • trickle approach 1
  • rowids
    • table access by 1
  • rows
    • rowids used to locate 1
    • row set 1
    • row source 1
  • row source generation 1
  • row sources 1

S

  • SAMPLE BLOCK clause 1
  • SAMPLE clause 1
  • sample table scans 1
  • scans
    • in-memory 1
    • sample table 1
  • SEARCH_COLUMNS column
    • PLAN_TABLE table 1 , 2
  • selectivity
    • creating indexes 1
    • improving for an index 1
    • indexes 1
  • SELECT statement
    • SAMPLE clause 1
  • semijoins 1
  • SET_EVOLVE_TASK_PARAMETER procedure 1
  • shared cursors
    • life cycle 1
  • shared pool
    • parsing check 1
  • shared SQL areas 1 , 2
  • soft parsing 1 , 2
  • sort merge joins
    • cost-based optimization 1
    • how they work 1
  • SPM Evolve Advisor task
    • about 1
    • managing 1
    • scheduling 1
  • SQL
    • execution 1
    • optimization 1
    • parsing 1
    • recursive 1
    • result sets 1 , 2
    • stages of processing 1 , 2
  • SQL_STATEMENT column
    • TKPROF_TABLE 1
  • SQL, recursive 1 , 2
  • SQL Access Advisor
    • constants 1
    • EXECUTE_TASK procedure 1
  • SQL compilation 1 , 2 , 3
  • SQL management base
    • managing 1 , 2
    • pluggable databases and 1
  • SQL operators 1
  • SQL parsing
    • parse calls 1
  • SQL Performance Analyzer 1
  • SQL plan baselines
    • automatic plan capture 1
    • configuring initial capture 1
    • displaying 1
    • dropping 1
    • loading 1
    • loading from AWR 1
    • loading from shared SQL area 1
    • loading using DBMS_SPM 1
    • plan matching 1
  • SQL plan capture
    • configuring filters of automatic plan capture 1
    • enabling automatic 1
  • SQL plan directives
    • cardinality misestimates 1
    • creation 1
    • managing 1
  • SQL plan evolution
    • managing an evolve task 1
  • SQL plan history 1
  • SQL plan management
    • accepted plans 1
    • automatic plan capture 1 , 2
    • basic tasks 1
    • benefits 1
    • DBMS_SPM 1
    • filters for automatic plan capture 1
    • introduction 1
    • loading plans from a staging table 1
    • loading plans from SQL tuning sets 1
    • manual plan capture 1
    • plan capture 1
    • plan evolution 1 , 2 , 3
    • plan selection 1 , 2
    • plan verification 1
    • purpose 1
    • SPM Evolve Advisor 1
    • SQL plan baselines 1 , 2 , 3
    • SQL plan capture 1
  • SQL processing
    • semantic check 1
    • shared pool check 1
    • stages 1
    • syntax check 1
  • SQL profiles
    • and SQL plan baselines 1
  • SQL statement log 1
  • SQL statements
    • avoiding the use of indexes 1
    • ensuring the use of indexes 1
    • execution plans of 1
    • modifying indexed data 1
  • SQL Test Case Builder 1
  • SQL test cases 1
  • SQL trace facility
    • output 1
    • statement truncation 1
    • trace files 1
  • SQL trace files 1
  • SQL tuning
    • definition 1
    • introduction 1
    • proactive 1
    • purpose 1
    • reactive 1
    • tools overview 1
  • SQL Tuning Advisor
    • about 1
    • administering with APIs 1
    • analyses 1
    • architecture 1
    • configuring a SQL tuning task 1
    • controls 1
    • input sources 1 , 2
    • pluggable databases and 1 , 2
    • using 1 , 2
    • using remotely 1
  • SQL tuning sets
    • loading in SQL plan baselines 1
    • pluggable databases and 1
  • SQL Tuning Sets
    • managing with APIs 1
  • start columns
    • in partitioning and EXPLAIN PLAN statement 1
  • star transformations 1
  • STATEMENT_ID column
    • PLAN_TABLE table 1 , 2
  • statistics, optimizer
    • adaptive statistics 1
    • automatic collection 1
    • bulk loads 1
    • cardinality 1
    • collection 1
    • column group 1
    • column groups 1 , 2
    • determining staleness 1
    • dynamic 1 , 2 , 3 , 4 , 5
    • dynamic statistics 1
    • exporting and importing 1
    • extended 1
    • gathering concurrently 1
    • guideline for external tables 1
    • incremental 1 , 2 , 3 , 4
    • index 1
    • limitations on restoring previous versions 1
    • manual collection 1
    • monitoring collection 1
    • Optimizer Statistics Advisor 1 , 2 , 3
    • preferences 1
    • system 1 , 2 , 3
    • user-defined 1
  • stop columns
    • in partitioning and EXPLAIN PLAN statement 1
  • stored outlines
    • categories 1
    • purpose of migrating 1
  • subqueries
    • NOT IN 1
  • synopses 1
  • system statistics 1

T

  • table clusters 1
  • table expansion
    • about 1
    • how it works 1
    • purpose 1
    • scenario 1
  • table statistics 1
  • temporary tables
    • cursor-duration 1
  • temporary tables, global 1
  • testing designs 1
  • throughput
    • optimizer goal 1
  • TIMESTAMP column
    • PLAN_TABLE table 1 , 2
  • TKPROF_TABLE 1
  • TKPROF program
    • editing the output SQL script 1
    • example of output 1
    • generating the output SQL script 1
    • row source operations 1
    • using the EXPLAIN PLAN statement 1
    • wait event information 1
  • top frequency histograms 1 , 2
  • tracing
    • consolidating with trcsess 1
    • identifying files 1
  • transformations, query
    • purpose of table expansions 1
    • table expansion 1 , 2 , 3
  • trcsess utility 1
  • trickle rollout strategy 1
  • tuning
    • logical structure 1

U

  • uniqueness 1
  • USER_ID column, TKPROF_TABLE 1

V

  • V$SQL_PLAN_STATISTICS_ALL view
    • using to display execution plan information 1
  • V$SQL_PLAN_STATISTICS view
    • using to display execution plan statistics 1
  • V$SQL_PLAN view
    • using to display execution plan 1
  • V$SQLAREA view 1
  • validating designs 1

W

  • workloads 1