|
Oracle Execution Plan and Optimizers
A. Execution Plan
When user issues a sql statement (read, write or delete) Oracle builds an
execution plan which defines how Oracle will find or write the data. Oracle
provides EXECUTION PLAN command for the user to explore the way Oracle will take
to run the issued sql statement. The general syntax for the EXPLAIN PLAN is:
1. explain plan for your-precious-sql-statement; /*default table name of Oracle
is PLAN_TABLE*/
2. explain plan into table_name for your-precious-sql-statement;
Table A.1: Writing the Execution Plan of a sql statement into a table
Reading an execution plan from the plan table is as below.
explain plan for select /*+ rule */ * from test_for_ep where a = 5; /*Filling
the plan table*/
select substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,30 )
"Operation" object_name "Object"
from plan_table
start with id = 0
connect by prior id=parent_id; /*Reading from the plan table*/
OR
@$ORACLE_HOME/rdbms/admin/utlxpls.sql /*command can be used to read plan table*/
SELECT STATEMENT ()
TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
INDEX (RANGE SCAN) TEST_FOR_EP_IX /*Output of previous select statement*/ |
Table A.2: Reading execution plan of a sql statement from the plan
table
In an explain plan output, the more indented an operation is, the earlier it is
executed. And the result of this operation (or operations, if more than one have
are equally indented AND have the same parent) is then feeded to the parent
operation. In our case, it is obvious that the index (TEST_FOR_EP_IX) is used
first (most indented) then used for a TABLE ACCESS, second most indented, then
the result is returned.
Hint #1
If we only query fields of a table that are already in an index, Oracle doesn't
have to read the data blocks because it can get the relevant data from the
index:
create table test_for_ep (a number, b varchar2(100), constraint uq_tp unique(a));
delete plan_table;
1st case:
explain plan for select /*+ rule */ * from test_for_ep where a = 5;
SELECT STATEMENT ()
TABLE ACCESS (BY INDEX ROWID) TEST_FOR_EP
INDEX (RANGE SCAN) UQ_TP
2nd case:
explain plan for select /*+ rule */ a from test_for_ep where a > 5 and a < 50;
SELECT STATEMENT ()
INDEX (RANGE SCAN) UQ_TP
B. Optimizers
I. Rule Based Optimizer (RBO)
The rule-based optimizer is the oldest and most stable of the optimizers. The
rule-based optimizer is very simple and uses information in the data dictionary
to make decisions about using an index. Also, unlike the cost-based optimizer,
the order of tables in the FROM clause and the order of Booleans in the WHERE
clause affect the execution plan for the query.
In Oracle's rule-based optimizer, the ordering of the table names in the FROM
clause determines the driving table. The driving table is important because it
is retrieved first, and the rows from the second table are then merged into the
result set from the first table. Therefore, it is essential that the second
table return the least amount of rows based on the WHERE clause.
RBO decised which execution plan will be choosed to execute the query according
to the below “RBO Rule Table”. Rules are ordered sequentially according to their
rankings:
1. Single Row by Rowid
2. Single Row by Cluster Join
3. Single Row by Hash Cluster Key with Unique or Primary Key
4. Single Row by Unique or Primary Key
5. Clustered Join
6. Hash Cluster Key
7. Indexed Cluster Key
8. Composite Index
9. Single-Column Indexes
10. Bounded Range Search on Indexed Columns
11. Unbounded Range Search on Indexed Columns
12. Sort Merge Join
13. MAX or MIN of Indexed Column
14. ORDER BY on Indexed Column
15. Full Table Scan
Table B.1: Rule Based Optimizer Rule Table (Access paths and their ranking)
We can make some general observations about the characteristics of the
rule-based optimizer:
- Always use the Index - If an index can be used to access a table, choose the
index. Indexes are always preferred over a full-table scan of a sort merge join
( a sort merge join does not require an index).
- Always starts with the driving table - The last table in the from clause will
be the driving table – For the RBO, this should be the table that chooses the
least amount of rows. The RBO uses this driving table as the first table when
performing nested loop join operations.
- Full-table scans as a last resort - The RBO is not aware of Oracle parallel
query and multi-block reads, and does not consider the size of the table. Hence,
the RBO dislikes full-table scans and will only use them when no index exists.
- Any index will do - The RBO will sometimes choose a less than ideal index to
service a query. This is because the RBO does not have access to statistics that
show the selectivity of indexed columns.
Simple is sometimes better - Prior to Oracle8i, the RBO often provided a
better overall execution plan for some databases.
II. Cost Based
Optimizer (CBO)
Analyze is required to gather information about below subjects:
- Row Number in a table
- Block situation where data is stored
- Index Information
- Row lengths
There are two methods to analyze a table:
1. ANALYZE
Analyze command is used to gather statistics about a table, an index or a
cluster and also user can specify the number of the rows or the percentage of
the table to be analyzed. Example usages of analyze command are:
1. ANALYZE TABLE employees COMPUTE STATISTICS;
2. ANALYZE INDEX employees_pk COMPUTE STATISTICS;
3. ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 100 ROWS;
4. ANALYZE TABLE employees ESTIMATE STATISTICS SAMPLE 15 PERCENT;
Table B.2: ANALYZE command usages
2. DBMS_STATS
Dbms_stats package is an analyzer which has ability of
parallel execution,
copying statistics from one database to another and deleting gathered statistics
from database. Copying statistics from one server to another is a great feature
which gives you the chance to prepare statistics of your database on a copy
database and then carry these information to your live database. Example usages
of dbms_stats command are:
1. EXEC DBMS_STATS.gather_database_stats;
2. EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
3. EXEC DBMS_STATS.gather_schema_stats(’SCOTT’);
4. EXEC DBMS_STATS.gather_schema_stats(’SCOTT’, estimate_percent => 15);
5. EXEC DBMS_STATS.gather_table_stats(’SCOTT’, ‘EMPLOYEES’);
6. EXEC DBMS_STATS.gather_table_stats(’SCOTT’, ‘EMPLOYEES’, estimate_percent =>
15);
7. EXEC DBMS_STATS.gather_index_stats(’SCOTT’, ‘EMPLOYEES_PK’);
8. EXEC DBMS_STATS.gather_index_stats(’SCOTT’, ‘EMPLOYEES_PK’, estimate_percent
=> 15);
9. EXEC DBMS_STATS.delete_database_stats;
10. EXEC DBMS_STATS.delete_schema_stats(’SCOTT’);
11. EXEC DBMS_STATS.delete_table_stats(’SCOTT’, ‘EMPLOYEES’);
12. EXEC DBMS_STATS.delete_index_stats(’SCOTT’, ‘EMPLOYEES_PK’); |
Table B.3: DBMS_STATS command usages
The table order still makes a difference in execution time, even when using the
cost-based optimizer.
The driving table is the table that will initiate the query and should be the
table with the smallest number of rows. Ordering the tables in the FROM clause
can make a huge difference in execution time.
Hint #2
Cost-based optimization – The driving table is first after FROM clause - place
smallest table first after FROM, and list tables from smallest to largest.
Rule-based optimization – The driving table is last in FROM clause - place
smallest table last in FROM clause, and list tables from largest to smallest.
SOURCE
1. Execution Plan, Optimizer ve ēežitleri, http://www.ceturk.com/makaleoku.asp?id=224
2. Oracle's explain plan, http://www.adp-gmbh.ch/ora/explainplan.html
3. EXPLAIN PLAN Usage, http://www.oracle-base.com/articles/8i/ExplainPlanUsage.php
4. Tuning with Rule-Based Optimization, http://www.remote-dba.net/t_tuning_rule_based_optimization.htm
|
