Oracle SQL Tuning (Glossary)

Oracle SQL Tuning - Glossary

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

Tuning individual Oracle SQL statements

The acronym SQL stands for Structured Query Language. SQL is an industry standard database query language that was adopted in the mid-1980s. It should not be confused with commercial products such as Microsoft SQL Server or open source products such as MySQL, both of which use the acronym as part of the title of their products.
Steps to complete before starting individual SQL statement tuning
This broad-brush approach can save thousands of hours of tedious SQL tuning because you can hundreds of queries at once.
Remember, this must be done first, else later changes to the optimizer parameters or statistics may un-tune your SQL.

all_rows hint

The default cost-based optimizer method, all_rows maximizes overall execution time.

ANSI standard

The American National Standards Institute

BIF

Extensions to standard Oracle SQL

Built-in functions

Extensions to standard Oracle SQL

Choose optimizer

This is a default optimizer_mode in the init.ora file. If statistics exist for any table in a query, CHOOSR will invoke the cost-based optimizer.

Cost-based optimizer

This is the latest SQL optimizer that uses object statistics to make intelligent table access decisions.

Driving table

This is the table used by the SQL optimizer in the initial step of execution.

Execution plan

This is the compiled execution path to Oracle data.

EXPLAIN PLAN

This is an Oracle utility to show the access path.

explicit cursor

A cursor that is declared with the DECLARE command.

first_rows hint

This is a cost-based SQL optimizer hint that delivers rows as quickly as possible.

Full-table scan

This is an execution plan that accesses a table without an index, reading each block of the table.

Gather Statistics

In Oracle SQL tuning, gathering statistics refers to the process of collecting data about the distribution of values in table columns, indexes, and other database objects. The Oracle database optimizer uses these statistics to generate efficient execution plans for SQL statements. Accurate statistics are crucial for optimal query performance.

Hash join

This is an execution plan that creates a hash table in SGA memory and uses this memory structure to join the tables.

Heuristic

This is a rule or set of rules used to describe a process.

Hint

This is an SQL compiler directive that tells Oracle to change an execution plan.

Histogram

This is a range of values within an index.

host variables

This is a variable that is used inside PL/SQL.

implicit cursor

A cursor that is allocated on behalf of the executing program.

Index

This is a data structure used to facilitate fast access to table rows in a specified sequence.

Index range scan

An index range scan is a common access method used by Oracle to retrieve data from a table. It utilizes an index to efficiently locate rows within a specific range of values, rather than scanning the entire table. This method is often employed when a query includes a WHERE clause with comparison operators (e.g., >, <, BETWEEN) on an indexed column.

Join Elimination

Join elimination is a SQL tuning technique used by the Oracle optimizer to remove unnecessary join operations from a query. This occurs when the optimizer can determine that a join is redundant, meaning it does not affect the final result set.

SELECT e.employee_id, e.employee_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE e.employee_id = 123;

Nested loop

This is a join method that queries the driving table and then probes the other tables via an index.

Non-correlated subquery

This is an SQL subquery that does not have a WHERE condition that references values in the outer query.

object ID (OID)

A unique identifier for a table row that is defined as an object type.

Optimizer

This is an Oracle tool used to determine Oracle SQL execution plans.

Optimizer mode

This is the default optimizer as set in the init.ora file.

Outer join

This is a join whereby rows that do not have a match in both tables are also displayed.

Parallel query

This is a method for using multiple processes to read a table via a full-table scan.

PL/SQL

Procedural Language/Structured Query Language - The application language of Oracle.

PLAN TABLE

This is an internal table that contains the display of the execution plan.

pointer

A reference where an OID is embedded into a data column.

Ranking scheme

This is a method for determining the relative costs among candidate execution plans.

Rule-based optimizer

This is the first Oracle SQL optimizer; it uses general rules to formulate execution plans.

TKPROF utility

The Oracle utility that is used to trace SQL statements.

UNION operator

This is an SQL operator that joins the result sets of multiple SELECT statements.