Oracle SQL Tuning

Oracle SQL Tuning - Glossary

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.
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: This is a scan of a table using an index.
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.