As you can see, there is quite a bit of complexity to
SQL tuning.
This module has served as a high level introduction to the advanced SQL tuning topics that will follow in the later modules.
Topics in this module include:
- Oracle extensions to ANSI standard SQL
- Basic SQL tuning tools
- Using the EXPLAIN PLAN utility
- The SQL optimizer modes
- The rule-based SQL optimizer
- The cost-based SQL optimizer
- Tuning SQL with hints
Here are the terms from this module that may have been new to you:
- ANSI standard: The American National Standards Institute
- BIF: Extensions to standard Oracle SQL
- 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.
- 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.
- Index: This is a data structure used to facilitate fast access to table rows in a specified sequence.
- Optimizer: This is an Oracle tool used to determine Oracle SQL execution plans.
- 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.
Now that we know the basics, we will move on to look at advanced Oracle locking topics.
In the next module, we will explore the implementation of the ANSI standard.