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.
