As you can see, there is quite a bit of complexity to SQL Tuning.
.
The following concepts were covered in this module:
- Oracle provides a standard ANSI SQL for database access, and has provided a wealth of built-in functions to improve SQL productivity.
- SQL tuning is one of the single most important tuning techniques that can be used to tune an Oracle database.
- Oracle provides a set of tools to assist in tuning SQL, including EXPLAIN PLAN, TKPROG and SQL Analyzer.
- Oracle has two optimizer modes. Rule-based optimization is the oldest method and relies on heuristics to determine access paths. The cost-based optimizer is a later development and relies on table and index statistics to formulate access plans to data.
- The EXPLAIN PLAN utility is the primary method for viewing the underlying access path to Oracle tables and indexes.
- Oracle provides hints that can be added to standard SQL to change the access path to data.
Tuning a single SQL query is an enormously important topic. Before going into production virtually every system would expose some statements that require tuning.
Syntactically, SQL query consists of three fundamental parts:
- a list of columns,
- a list of tables, and
- a "where" clause.
The "where" clause is a logical formula that can be further decomposed into predicates connected by Boolean connectives.
For example, the "where" clause of:
select empno, sal from emp e, dept d
where e.deptno = d.deptno and dname = 'ACCOUNTING'
Is a conjunction of dname = 'ACCOUNTING' single table predicate and e.deptno = d.deptno join predicate.
In my opinion, predicate handling is the heart of SQL optimization: predicates could be transitively added, rewritten using Boolean
Algebra laws, and moved around at SQL Execution Plan. In our simplistic example, the single table predicate is
applied either to index or table scan plan nodes, while join predicate could also be applied to the join node. Unfortunately,
despite their significance, Oracle Execution Plan facility didn't show predicates until version 9.2.
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.
