In the context of SQL plan management, a plan that is in a SQL plan baseline for a SQL statement and thus available for use by the optimizer. An accepted plan contains a set of hints, a plan hash value, and other plan-related information.
The means by which the database retrieves data from a database. For example, a query using an index and a query using a full table scan use different access paths.
A feature that enables a single statement that contains bind variables to use multiple execution plans. Cursor sharing is "adaptive" because the cursor adapts its behavior so that the database does not always use the same plan for each execution or bind variable value.
A feature of the optimizer that enables it to adapt plans based on run-time statistics.
An execution plan that changes after optimization because run-time conditions indicate that optimizer estimates are inaccurate. An adaptive plan has different built-in plan options. During the first execution, before a specific subplan becomes active, the optimizer makes a a final decision about which option to use. The optimizer bases its choice on observations made during the execution up to this point. Thus, an adaptive plan enables the final plan for a statement to differ from the default plan.
A set of capabilities that enables the adaptive optimizer to make run-time adjustments to execution plans and discover additional information that can lead to better statistics. Adaptive optimization is helpful when existing statistics are not sufficient to generate an optimal plan.
A join that returns rows that fail to match the subquery on the right side. For example, an antijoin can list departments with no employees. Antijoins use the NOT EXISTS or NOT IN constructs.
Automatic Database Diagnostic Monitor (ADDM)
ADDM is self-diagnostic software built into Oracle Database. ADDM examines and analyzes data captured in Automatic Workload Repository (AWR) to determine possible database performance problems.
automatic optimizer statistics collection
The automatic running of the DBMS_STATS package to collect optimizer statistics for all schema objects for which statistics are missing or stale.
automatic initial plan capture
The mechanism by which the database automatically creates a SQL plan baseline for any repeatable SQL statement executed on the database. Enable automatic initial plan capture by setting the OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES initialization parameter to true (the default is false).
The ability of the optimizer to automatically change a plan on subsequent executions of a SQL statement. Automatic reoptimization can fix any suboptimal plan chosen due to incorrect optimizer estimates, from a suboptimal distribution method to an incorrect choice of degree of parallelism.
The work performed by Automatic SQL Tuning Advisor it runs as an automated task within system maintenance windows.
SQL Tuning Advisor when run as an automated maintenance task. Automatic SQL Tuning runs during system maintenance windows as an automated maintenance task, searching for ways to improve the execution plans of high-load SQL statements.
See SQL Tuning Advisor.
The optimizer when invoked by SQL Tuning Advisor. In SQL tuning mode, the optimizer performs additional analysis to check whether it can further improve the plan produced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan.
Automatic Workload Repository (AWR)
The infrastructure that provides services to Oracle Database components to collect, maintain, and use statistics for problem detection and self-tuning.
A set of data for a specific time that is used for performance comparisons. The delta values captured by the snapshot represent the changes for each statistic over the time period. Statistics gathered by are queried from memory. You can display the gathered data in both reports and views.
In the context of AWR, the interval between two AWR snapshots that represent the database operating at an optimal level.
A bind-sensitive cursor that is eligible to use different plans for different bind values. After a cursor has been made bind-aware, the optimizer chooses plans for future executions based on the bind value and its cardinality estimate.
A cursor whose optimal plan may depend on the value of a bind variable. The database monitors the behavior of a bind-sensitive cursor that uses different bind values to determine whether a different plan is beneficial.
A placeholder in a SQL statement that must be replaced with a valid value or value address for the statement to execute successfully. By using bind variables, you can write a SQL statement that accepts inputs or parameters at run time. The following query uses v_empid as a bind variable:
SELECT * FROM employees WHERE employee_id = :v_empid;
The ability of the optimizer to look at the value in a bind variable during a hard parse. By peeking at bind values, the optimizer can determine the selectivity of a WHERE clause condition as if literals had been used, thereby improving the plan.
A subcomponent of a single bitmap index entry. Each indexed column value may have one or more bitmap pieces. The database uses bitmap pieces to break up an index entry that is large in relation to the size of a block.
An index organized like an upside-down tree. A B-tree index has two types of blocks: branch blocks for searching and leaf blocks that store values. The leaf blocks contain every indexed data value and a corresponding rowid used to locate the actual row. The "B" stands for "balanced" because all leaf blocks automatically stay at the same depth.
The number of rows that is expected to be or actually is returned by an operation in an execution plan. Data has low cardinality when the number of distinct values in a column is low in relation to the total number of rows.
A join in which one or more of the tables does not have any join conditions to any other tables in the statement. The optimizer joins every row from one data source with every row from the other data source, creating the Cartesian product of the two sets.
The cursor containing the plan, compilation environment, and other information for a statement whose text is stored in a parent cursor. The parent cursor is number 0, the first child is number 1, and so on. Child cursors reference the same SQL text as the parent cursor, but are different. For example, two queries with the text SELECT * FROM t use different cursors when they reference two different tables named t.
An access path for a table cluster. In an indexed table cluster, Oracle Database first obtains the rowid of one of the selected rows by scanning the cluster index. Oracle Database then locates the rows based on this rowid.
Statistics about columns that the optimizer uses to determine optimal execution plans. Column statistics include the number of distinct column values, low value, high value, and number of nulls.
In a database operation, the activity between two points in time in a database session, with each session defining its own beginning and end points. A session can participate in at most one composite database operation at a time.
Simultaneous access of the same data by many users. A multiuser database management system must provide adequate concurrency controls so that data cannot be updated or changed improperly, compromising data integrity.
A combination of one or more expressions and logical operators that returns a value of TRUE, FALSE, or UNKNOWN.
A numeric internal measure that represents the estimated resource usage for an execution plan. The lower the cost, the more efficient the plan.
The legacy name for the optimizer. In earlier releases, the cost-based optimizer was an alternative to the rule-based optimizer (RBO).
The internal optimizer model that accounts for the cost of the I/O, CPU, and network resources that a query is predicted to use.
A count such as the number of block reads. Oracle Database generates many types of cumulative statistics for the system, sessions, and individual SQL statements.
A handle or name for a private SQL area in the PGA. Because cursors are closely associated with private SQL areas, the terms are sometimes used interchangeably.
See shared SQL area.
Combining cursors to save space in the shared SQL area. If the optimizer creates a plan for a bind-aware cursor, and if this plan is the same as an existing cursor, then the optimizer can merge the cursors.
A set of database tasks defined by end users or application code, for example, a batch job or ETL processing.
For an adaptive plan, the execution plan initially chosen by the optimizer using the statistics from the data dictionary. The default plan can differ from the final plan.
A plan that a database administrator has manually marked as ineligible for use by the optimizer.
The number of parallel execution servers associated with a single operation. Parallel execution is designed to effectively use multiple CPUs. Oracle Database parallel execution framework enables you to either explicitly choose a specific degree of parallelism or to rely on Oracle Database to automatically control it.
A key that represents all grouping keys whose grouping columns come from a particular fact table or dimension.
A key that represents all join keys whose join columns come from a particular fact table or dimension.
A decimal number between 0 and 1 that measures the selectivity of a column. Values close to 1 indicate that the column is unselective, whereas values close to 0 indicate that this column is more selective.
The table to which other tables are joined. An analogy from programming is a for loop that contains another for loop. The outer for loop is the analog of a driving table, which is also called an outer table.
A view created on dynamic performance tables, which are virtual tables that record current database activity. The dynamic performance views are called fixed views because they cannot be altered or removed by the database administrator. They are also called V$ views because they begin with the string V$ (GV$ in Oracle RAC).
An optimization technique in which the database executes a recursive SQL statement to scan a small random sample of a table's blocks to estimate predicate selectivities.
The level that controls both when the database gathers dynamic statistics, and the size of the sample that the optimizer uses to gather the statistics. Set the dynamic statistics level using either the OPTIMIZER_DYNAMIC_SAMPLING initialization parameter or a statement hint.
In SQL plan management, a plan that is eligible for use by the optimizer.
A number that uniquely identifies a bucket in a histogram. In frequency and hybrid histograms, the endpoint number is the cumulative frequency of endpoints. In height-balanced histograms, the endpoint number is the bucket number.
In a hybrid histogram, the number of times the endpoint value is repeated, for each endpoint (bucket) in the histogram. By using the repeat count, the optimizer can obtain accurate estimates for almost popular values.
An endpoint value is the highest value in the range of values in a histogram bucket.
The component of the optimizer that determines the overall cost of a given execution plan.
The combination of steps used by the database to execute a SQL statement. Each step either retrieves rows of data physically from the database or prepares them for the user issuing the statement. You can override execution plans by using hints.
A tree diagram that shows the flow of row sources from one step to another in an execution plan.
A combination of one or more values, operators, and SQL functions that evaluates to a value. For example, the expression 2*2 evaluates to 4. In general, expressions assume the data type of their components.
A type of extended statistics that improves optimizer estimates when a WHERE clause has predicates that use expressions.
A type of optimizer statistics that improves estimates for cardinality when multiple predicates exist or when predicates contain expressions.
An optimizer capability that enables authors of user-defined functions and indexes to create statistics collection, selectivity, and cost functions that the optimizer uses when choosing an execution plan. The optimizer cost model is extended to integrate information supplied by the user to assess CPU and I/O cost.
A column group or an expression. The statistics collected for column groups and expressions are called extended statistics.
A read-only table whose metadata is stored in the database but whose data in stored in files outside the database. The database uses the metadata describing external tables to expose their data as if they were relational tables.
A WHERE clause component that eliminates rows from a single object referenced in a SQL statement.
In an adaptive plan, the plan that executes to completion. The default plan can differ from the final plan.
A dynamic performance table or its index. The fixed objects are owned by SYS. Fixed object tables have names beginning with X$ and are the base tables for the V$ views.
An accepted plan that is marked as preferred, so that the optimizer considers only the fixed plans in the SQL plan baseline. You can use fixed plans to influence the plan selection process of the optimizer.
A type of histogram in which each distinct column value corresponds to a single bucket. An analogy is sorting coins: all pennies go in bucket 1, all nickels go in bucket 2, and so on.
A combination of a left and right outer join. In addition to the inner join, the database uses nulls to preserve rows from both tables that have not been returned in the result of the inner join. In other words, full outer joins join tables together, yet show rows with no corresponding rows in the joined tables.
A scan of table data in which the database sequentially reads all rows from a table and filters out those that do not meet the selection criteria. All data blocks under the high water mark are scanned.
A special temporary table that stores intermediate session-private data for a specific duration.
The steps performed by the database to build a new executable version of application code. The database must perform a hard parse instead of a soft parse if the parsed representation of a submitted statement does not exist in the shared SQL area.
A type of table cluster that is similar to an indexed cluster, except the index key is replaced with a hash function. No separate cluster index exists. In a hash cluster, the data is the index.
A function that operates on an arbitrary-length input value and returns a fixed-length hash value.
A method for joining large data sets. The database uses the smaller of two data sets to build a hash table on the join key in memory. It then scans the larger data set, probing the hash table to find the joined rows.
An access path for a table cluster. The database uses a hash scan to locate rows in a hash cluster based on a hash value. In a hash cluster, all rows with the same hash value are stored in the same data block. To perform a hash scan, Oracle Database first obtains the hash value by applying a hash function to a cluster key value specified by the statement, and then scans the data blocks containing rows with that hash value.
An in-memory data structure that associates join keys with rows in a hash join. For example, in a join of the employees and departments tables, the join key might be the department ID. A hash function uses the join key to generate a hash value. This hash value is an index in an array, which is the hash table.
In a hash cluster, a unique numeric ID that identifies a bucket. Oracle Database uses a hash function that accepts an infinite number of hash key values as input and sorts them into a finite number of buckets. Each hash value maps to the database block address for the block that stores the rows corresponding to the hash key value (department 10, 20, 30, and so on).
A mathematical technique in which an infinite set of input values is mapped to a finite set of output values, called hash values. Hashing is useful for rapid lookups of data in a hash table.
A table in which the data rows are stored in no particular order on disk. By default, CREATE TABLE creates a heap-organized table.
A histogram in which column values are divided into buckets so that each bucket contains approximately the same number of rows.
An instruction passed to the optimizer through comments in a SQL statement. The optimizer uses hints to choose an execution plan for the statement.
A special type of column statistic that provides more detailed information about the data distribution in a table column.
hybrid hash distribution technique
An adaptive parallel data distribution that does not decide the final data distribution method until execution time.
An enhanced height-based histogram that stores the exact frequency of each endpoint in the sample, and ensures that a value is never stored in multiple buckets.
A component of a DML statement that retrieves data without a subquery. An UPDATE, DELETE, or MERGE statement that does not explicitly include a SELECT statement uses an implicit query to retrieve the rows to be modified.
incremental statistics maintenance
The ability of the database to generate global statistics for a partitioned table by aggregating partition-level statistics.
Optional schema object associated with a nonclustered table, table partition, or table cluster. In some cases indexes speed data access.
An table cluster that uses an index to locate data. The cluster index is a B-tree index on the cluster key.
A measure of row order in relation to an indexed value such as employee last name. The more scattered the rows among the data blocks, the lower the clustering factor.
A scan of the index blocks in unsorted order, as they exist on disk. This scan reads the index instead of the table.
A table whose storage organization is a variant of a primary B-tree index. Unlike a heap-organized table, data is stored in primary key order.
An index range scan is an ordered scan of an index that has the following characteristics:
One or more leading columns of an index are specified in conditions.
0, 1, or more values are possible for an index key.
An index range scan in which the database returns rows in descending order.
An index scan occurs in which the initial column of a composite index is "skipped" or not specified in the query. For example, if the composite index key is (cust_gender,cust_email), then the query predicate does not reference the cust_gender column.
Statistics about indexes that the optimizer uses to determine whether to perform a full table scan or an index scan. Index statistics include B-tree levels, leaf block counts, the index clustering factor, distinct keys, and number of rows in the index.
A join of two or more tables that returns only those rows that satisfy the join condition.
In a nested loops join, the table that is not the outer table (driving table). For every row in the outer table, the database accesses all rows in the inner table. The outer loop is for every row in the outer table and the inner loop is for every row in the inner table.
A statement that retrieves data from multiple tables specified in the FROM clause of a SQL statement. Join types include inner joins, outer joins, and Cartesian joins.
A condition that compares two row sources using an expression. The database combines pairs of rows, each containing one row from each row source, for which the join condition evaluates to true.
The removal of redundant tables from a query. A table is redundant when its columns are only referenced in join predicates, and those joins are guaranteed to neither filter nor expand the resulting rows.
A cost-based transformation that can factorize common computations from branches of a UNION ALL query. Without join factorization, the optimizer evaluates each branch of a UNION ALL query independently, which leads to repetitive processing, including data access and joins. Avoiding an extra scan of a large base table can lead to a huge performance improvement.
A method of joining a pair of row sources. The possible join methods are nested loop, sort merge, and hash joins. A Cartesian join requires one of the preceding join methods
The order in which multiple tables are joined together. For example, for each row in the employees table, the database can read each row in the departments table. In an alternative join order, for each row in the departments table, the database reads each row in the employees table.
To execute a statement that joins more than two tables, Oracle Database joins two of the tables and then joins the resulting row source to the next table. This process continues until all tables are joined into the result.
A predicate in a WHERE or JOIN clause that combines the columns of two tables in a join.
A low-level serialization control mechanism used to protect shared data structures in the SGA from simultaneous access.
In an outer join, the table specified on the left side of the OUTER JOIN keywords (in ANSI SQL syntax).
An area of memory in the shared pool. This cache includes the shared SQL areas, private SQL areas (in a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.
During SQL processing, the act of searching for a usable plan in the library cache and not finding it.
A contiguous time interval during which automated maintenance tasks run. The maintenance windows are Oracle Scheduler windows that belong to the window group named MAINTENANCE_WINDOW_GROUP.
The user-initiated bulk load of existing plans into a SQL plan baseline.
A schema object that stores a query result. All materialized views are either read-only or updatable.
An I/O call that reads multiple database blocks. Multiblock reads can significantly speed up full table scans.
A type of join method. A nested loops join determines the outer table that drives the join, and for every row in the outer table, probes each row in the inner table. The outer loop is for each row in the outer table and the inner loop is for each row in the inner table. An analogy from programming is a for loop inside of another for loop.
In a histogram, any value that does not span two or more endpoints. Any value that is not nonpopular is a popular value.
The manual invocation of SQL Tuning Advisor. Any invocation of SQL Tuning Advisor that is not the result of an Automatic SQL Tuning task is on-demand tuning.
Built-in database software that determines the most efficient way to execute a SQL statement by considering factors related to the objects referenced and the conditions specified in the statement.
The model that the optimizer uses to select an execution plan. The optimizer selects the execution plan with the lowest cost, where cost represents the estimated resource usage for that plan. The optimizer cost model accounts for the I/O, CPU, and network resources that the query will use.
The totality of session settings that can affect execution plan generation, such as the work area size or optimizer settings (for example, the optimizer mode).
The prioritization of resource usage by the optimizer. Using the OPTIMIZER_MODE initialization parameter, you can set the optimizer goal best throughput or best response time.
Details about the database its object used by the optimizer to select the best execution plan for each SQL statement. Categories include table statistics such as numbers of rows, index statistics such as B-tree levels, system statistics such as CPU and I/O performance, and column statistics such as number of nulls.
optimizer statistics collection
The gathering of optimizer statistics for database objects. The database can collect these statistics automatically, or you can collect them manually by using the system-supplied DBMS_STATS package.
optimizer statistics collector
A row source inserted into an execution plan at key points to collect run-time statistics for use in adaptive plans.
optimizer statistics preferences
The default values of the parameters used by automatic statistics collection and the DBMS_STATS statistics gathering procedures.
A join condition using the outer join operator (+) with one or more columns of one of the tables. The database returns all rows that meet the join condition. The database also returns all rows from the table without the outer join operator for which there are no matching rows in the table with the outer join operator.
See driving table
The application of multiple CPU and I/O resources to the execution of a single database operation.
A query in which multiple processes work together simultaneously to run a single SQL query. By dividing the work among multiple processes, Oracle Database can run the statement more quickly. For example, four processes retrieve rows for four different quarters in a year instead of one process handling all four quarters by itself.
The cursor that stores the SQL text and other minimal information for a SQL statement. The child cursor contains the plan, compilation environment, and other information. When a statement first executes, the database creates both a parent and child cursor in the shared pool.
A call to Oracle to prepare a SQL statement for execution. The call includes syntactically checking the SQL statement, optimizing it, and then building or locating an executable form of that statement.
The stage of SQL processing that involves separating the pieces of a SQL statement into a data structure that can be processed by other routines.
A hard parse occurs when the SQL statement to be executed is either not in the shared pool, or it is in the shared pool but it cannot be shared. A soft parse occurs when a session attempts to execute a SQL statement, and the statement is already in the shared pool, and it can be used.
partition maintenance operation
A partition-related operation such as adding, exchanging, merging, or splitting table partitions.
A join optimization that divides a large join of two tables, one of which must be partitioned on the join key, into several smaller joins.
Unpublished optimizer statistics. By default, the optimizer uses published statistics but does not use pending statistics.
This form of automatic reoptimization helps improve the degree of parallelism automatically chosen for repeated SQL statements when PARALLEL_DEGREE_POLICY is set to ADAPTIVE.
A PL/SQL function that accepts a collection of rows as input. You invoke the table function as the operand of the table operator in the FROM list of a SELECT statement.
The manual change of an unaccepted plan in the SQL plan history into an accepted plan in the SQL plan baseline.
The part of the optimizer that tries different access paths, join methods, and join orders for a given query block to find the plan with the lowest cost.
The optimizer's attempt to find a matching plan in the SQL plan baseline for a statement after performing a hard parse.
Comparing the performance of an unaccepted plan to a plan in a SQL plan baseline and ensuring that it performs better.
In a histogram, any value that spans two or more endpoints. Any value that is not popular is an nonpopular value.
A transformation technique in which the optimizer "pushes" the relevant predicates from the containing query block into the view query block. For views that are not merged, this technique improves the subplan of the unmerged view because the database can use the pushed-in predicates to access indexes or to use as filters.
An area in memory that holds a parsed statement and other information for processing. The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas.
Using SQL tuning tools to identify SQL statements that are candidates for tuning before users have complained about a performance problem.
An optimizer-generated view that appear in queries in which a DISTINCT view has been merged, or a GROUP BY view is merged into an outer query block that also contains GROUP BY, HAVING, or aggregates.
An operation that retrieves data from tables or views. For example, SELECT * FROM employees is a query.
A top-level SELECT statement, subquery, or unmerged view
See optimizer.
Diagnosing and fixing SQL-related performance problems after users have complained about them.
Additional SQL statements that the database must issue to execute a SQL statement issued by a user. The generation of recursive SQL is known as a recursive call. For example, the database generates recursive calls when data dictionary information is not available in memory and so must be retrieved from disk.
A statement that the database parses or executes after recognizing that it is tracked in the SQL statement log.
The time required to complete a unit of work.
See throughput.
A join tree in which the right input of every join is the result of a previous join.
In an outer join, the table specified on the right side of the OUTER JOIN keywords (in ANSI SQL syntax).
A set of rows returned by a step in an execution plan.
An iterative control structure that processes a set of rows in an iterated manner and produces a row set.
Software that receives the optimal plan from the optimizer and outputs the execution plan for the SQL statement.
A collection of row sources produced by the row source generator. The row source tree for a SQL statement shows information such as table order, access methods, join methods, and data operations such as filters and sorts.
A scan that retrieves a random sample of data from a simple table or a complex SELECT statement, such as a statement involving joins and views.
A value indicating the proportion of a row set retrieved by a predicate or combination of predicates, for example, WHERE last_name = 'Smith'. A selectivity of 0 means that no rows pass the predicate test, whereas a value of 1 means that all rows pass the test.
The adjective selective means roughly "choosy." Thus, a highly selective query returns a low proportion of rows (selectivity close to 0), whereas an unselective query returns a high proportion of rows (selectivity close to 1).
A join that returns rows from the first table when at least one match exists in the second table. For example, you list departments with at least one employee. The difference between a semijoin and a conventional join is that rows in the first table are returned at most once. Semijoins use the EXISTS or IN constructs.
An area in the shared pool that contains the parse tree and execution plan for a SQL statement. Only one shared SQL area exists for a unique statement. The shared SQL area is sometimes referred to as the cursor cache.
A database operation consisting of a single SQL statement or PL/SQL procedure or function.
The merging of select-project-join views. For example, a query joins the employees table to a subquery that joins the departments and locations tables.
Any parse that is not a hard parse. If a submitted SQL statement is the same as a reusable SQL statement in the shared pool, then Oracle Database reuses the existing code. This reuse of code is also called a library cache hit.
A type of join method. The join consists of a sort join, in which both inputs are sorted on the join key, followed by a merge join, in which the sorted lists are merged.
SQL Access Advisor is internal diagnostic software that recommends which materialized views, indexes, and materialized view logs to create, drop, or retain.
In the context of Oracle SQL processing, this term refers collectively to the phases of parsing, optimization, and plan generation.
Additional information and instructions that the optimizer can use to generate a more optimal plan. For example, a SQL plan directive might instruct the optimizer to collect missing statistics or gather dynamic statistics.
A string value derived from the numeric SQL signature. Like the signature, the handle uniquely identifies a SQL statement. It serves as a SQL search key in user APIs.
For a specific SQL statement, the unique identifier of the parent cursor in the library cache. A hash function applied to the text of the SQL statement generates the SQL ID. The V$SQL.SQL_ID column displays the SQL ID.
In the fault diagnosability infrastructure of Oracle Database, a single occurrence of a SQL-related problem. When a problem (critical error) occurs multiple times, the database creates an incident for each occurrence. Incidents are timestamped and tracked in the Automatic Diagnostic Repository (ADR).
A logical repository that stores statement logs, plan histories, SQL plan baselines, and SQL profiles. The SMB is part of the data dictionary and resides in the SYSAUX tablespace.
A set of one or more accepted plans for a repeatable SQL statement. Each accepted plan contains a set of hints, a plan hash value, and other plan-related information. SQL plan management uses SQL plan baselines to record and evaluate the execution plans of SQL statements over time.
Techniques for capturing and storing relevant information about plans in the SQL management base (SMB) for a set of SQL statements. Capturing a plan means making SQL plan management aware of this plan.
Additional information that the optimizer uses to generate a more optimal plan. The optimizer collects SQL plan directives on query expressions rather than at the statement level. In this way, the directives are usable for multiple SQL statements.
The set of plans generated for a repeatable SQL statement over time. The history contains both SQL plan baselines and unaccepted plans.
SQL plan management is a preventative mechanism that records and evaluates the execution plans of SQL statements over time. SQL plan management can prevent SQL plan regressions caused by environmental changes such as a new optimizer version, changes to optimizer statistics, system settings, and so on.
The stages of parsing, optimization, row source generation, and execution of a SQL statement.
A set of auxiliary information built during automatic tuning of a SQL statement. A SQL profile is to a SQL statement what statistics are to a table. The optimizer can use SQL profiles to improve cardinality and selectivity estimates, which in turn leads the optimizer to select better plans.
A numeric hash value computed using a SQL statement text that has been normalized for case insensitivity and white space. It uniquely identifies a SQL statement. The database uses this signature as a key to maintain SQL management objects such as SQL profiles, SQL plan baselines, and SQL patches.
When automatic SQL plan capture is enabled, a log that contains the SQL ID of SQL statements that the optimizer has evaluated over time. A statement is tracked when it exists in the log.
A problematic SQL statement and related information needed to reproduce the execution plan in a different environment. A SQL test case is stored in an Oracle Data Pump file.
A database feature that gathers information related to a SQL statement and packages it so that a user can reproduce the problem on a different database. The DBMS_SQLDIAG package is the interface for SQL test case builder.
A server-generated file that provides performance information on individual SQL statements. For example, the trace file contains parse, execute, and fetch counts, CPU and elapsed times, physical reads and logical reads, and misses in the library cache.
Built-in database diagnostic software that optimizes high-load SQL statements.
A database object that includes one or more SQL statements along with their execution statistics and execution context.
A relational schema whose design represents a dimensional data model. The star schema consists of one or more fact tables and one or more dimension tables that are related through foreign keys.
A form of automatic reoptimization that automatically improves plans for repeated queries that have cardinality misestimates. The optimizer may estimate cardinalities incorrectly for many reasons, such as missing statistics, inaccurate statistics, or complex predicates.
A set of hints for a SQL statement. The hints in stored outlines direct the optimizer to choose a specific plan for the statement.
A portion of an adaptive plan that the optimizer can switch to as an alternative at run time. A subplan can consist of multiple operations in the plan. For example, the optimizer can treat a join method and the corresponding access path as one unit when determining whether to change the plan at run time.
A query nested within another SQL statement. Unlike implicit queries, subqueries use a SELECT statement to retrieve data.
A transformation technique in which the optimizer transforms a nested query into an equivalent join statement, and then optimizes the join.
A set of auxiliary statistics gathered on a partitioned table when the INCREMENTAL value is set to true.
Statistics that enable the optimizer to use CPU and I/O characteristics. Index statistics include B-tree levels, leaf block counts, clustering factor, distinct keys, and number of rows in the index.
A schema object that contains data from one or more tables, all of which have one or more columns in common. In table clusters, the database stores together all the rows from all tables that share the same cluster key.
A transformation technique that enables the optimizer to generate a plan that uses indexes on the read-mostly portion of a partitioned table, but not on the active portion of the table.
Statistics about tables that the optimizer uses to determine table access cost, join cardinality, join order, and so on. Table statistics include row counts, block counts, empty blocks, average free space per block, number of chained rows, average row length, and staleness of the statistics on the table.
The amount of work completed in a unit of time.
See response time.
A variation of a frequency histogram that ignores nonpopular values that are statistically insignificant, thus producing a better histogram for highly popular values.
One of the two optimizer modes. When running in tuning mode, the optimizer is known as the Automatic Tuning Optimizer. In tuning mode, the optimizer determines whether it can further improve the plan produced in normal mode. The optimizer output is not an execution plan, but a series of actions, along with their rationale and expected benefit for producing a significantly better plan.
A plan for a statement that is in the SQL plan history but has not been added to the SQL plan management.
A relatively large fraction of rows from a row set. A query becomes more unselective as the selectivity approaches 1. For example, a query that returns 999,999 rows from a table with one million rows is unselective. A query of the same table that returns one row is selective.
The time between when a user submits a command and receives a response.
See throughput.
A type of I/O in which the database obtains a set of rowids, sends them batched in an array to the operating system, which performs the read.
The merging of a query block representing a view into the query block that contains it. View merging can improve plans by enabling the optimizer to consider additional join orders, access methods, and other transformations.