Optim

ECS 165A Database Systems

1

Oracle Query Optimization Tools – The Very Basics – Cost-Based Query Optimization In order for the query optimizer to generate a query execution plan based on a cost-based query optimization strategy, statistics for the tables involved in the queries of interest need to be determined. These statistics are stored in the data dictionary views USER TABLES, USER TAB COLUMNS, and USER INDEXES. Statistics are computed using the SQL analyze command: analyze [table | index ] compute statistics | estimate statistics [sample rows | percent] | delete statistics; compute estimate

= =

delete

=

exact analysis (all tuples) estimation using 1064 tuples (default) sample number or percentage of rows to use for sampling delete statistics

Examples: • analyze table EMP estimate statistics sample 50 percent; • analyze table EMP compute statistics; • analyze table EMP delete statistics; Statistics for indexes are determined in an analogous fashion: analyze index . . . ; Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

2

Note: Starting with Oracle9i, using the package DBMS STATS is the preferred method to collect/maintain statistics. Aspects of cost-based query optimization

• Determine possible (logically equivalent) execution plans • Cost estimate for each plan is based on the statistics and storage characteristics • Choose execution plan with minimal cost • Role of selectivity of tuples: good selectivity (only a few tuples satisfy search condition) → index defined on search key poor selectivity → full table scan • Cost-based optimization has to take available resources into account, e.g., for storing intermediate results • Goal of best throughput: Nested-loops • Goal of best response time: Merge-Join The explain plan command For select, update, delete, and insert statements, the explain plan command generates and stores information about the execution plan chosen by the query optimizer in a table called PLAN TABLE. This table is created when you run the SQL script $ORACLE HOME/rdbms/admin/utlxplan.sql in SQL*Plus. The PLAN TABLE table then can be queried using a select statement.

Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

3

The syntax of the explain plan command is as follows: explain plan set statement id = ’’ for <SQL statement>; Example: We want to generate the execution plan for the query “List the name, job, salary and department of all employees whose salary is not within the job’s salary range.” explain plan set statement id = ’MYPLAN’ for select ENAME, JOB, SAL, DNAME from EMP, DEPT where EMP.DEPTNO = DEPT.DEPTNO and not exists (select ∗ from SALGRADE where EMP.SAL between LOSAL and HISAL); Note that every execution plan gets a statement id, which is just some string. The rest is just a “normal” select statement. The above explain plan statement causes the query optimizer to insert data about the execution plan for the query into PLAN TABLE. The following query can be used to view to content of the plan table in an indented format, which reflects the tree structure of the plan (the right-most entries correspond to the leafs of the query tree). Note that before creating a new plan with the same id, you have to delete all entries from PLAN TABLE (delete from PLAN TABLE;)

Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

4

select substr(lpad(’ ’,2*(level-1)),1,8)|| substr(operation,1,18) "OPERA substr(options,1,12) "OPTIONS", substr(object_name,1,16) object_name, id, parent_id, cost, cardinality, bytes, filter_predicates from plan_table start with id=0 and statement_id = ’MYPLAN’ connect by prior id = parent_id and statement_id = ’MYPLAN’;

This select statement generates an output that looks like this (some output attributes are omitted): Output: OPERATION --------------------SELECT STATEMENT FILTER MERGE JOIN SORT TABLE ACCESS SORT TABLE ACCESS TABLE ACCESS

OPTIONS OBJECT_NAME ID PARENT_ID ------- ----------- -- --------0 1 0 2 1 JOIN 3 2 FULL DEPT 4 3 JOIN 5 2 FULL EMP 6 5 FULL SALGRADE 7 1

Meaning of the Columns of PLAN TABLE (10 out of 31) OPERATION Name of the operation performed at this step OPTIONS Options used for the operation performed at this step OBJECT NAME The name of the table or index used in operation ID Identification number for this step in the execution plan PARENT ID ID of the next step that operates on the results of this step Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

5

COST Cost of the current operation estimated by the cost-based optimizer (CBO). The value of this column does not have any particular unit of measurement, it is merely a weighted value used to compare costs of execution plans. CARDINALITY Number of rows returned by the current operation (estimated by the CBO) BYTES Number of bytes returned by the current operation Some others that are not shown in the output above but which might be useful in fully describing an execution plan (see also PLAN TABLE in Oracle Reference Manual): ACCESS PREDICATES Predicates used to locate rows in an access structure. For example, start or stop predicates for an index range scan. FILTER PREDICATES Predicates used to filter rows before producing them The top-most operation is typically select statement if the SQL statement was a select query.

Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

6

Meaning of some operation/option value pairs TABLE ACCESS+

FULL BY ROWID HASH

SORT

UNIQUE AGGREGATE

JOIN GROUP BY

NESTED LOOPS+

A retrieval of all rows from a table. A retrieval of a row from a table based on its ROWID. A retrieval of rows from a table based on a value of the key of hash cluster. Operation that sorts a set of rows to eliminate duplicates. Retrieval of a single row that is the result of applying a group function to a group of selected rows. Operation that sorts a set of rows before a merge-join operation Operation sorting a set of rows into groups for a query with a GROUP BY clause. Join operation that accepts two sets of rows, an outer set and an inner set.

See also Chapter 19, Table 19.3 in Performance Tuning Guide; there are more than 60 combinations explained in more detail.

Michael Gertz

Oracle Query Optimizer

ECS 165A Database Systems

7

MERGE JOIN+

Operation that accepts two sets of rows, each sorted by a specific value, combines each row from one set with the matching rows from the other, and returns the result. FILTER Operation that accepts a set of rows, eliminates some of them, and returns the rest (∼ select). UNION, INTERSECTION, MINUS (Set operations) INDEX∗ UNIQUE SCAN A retrieval of a single ROWID from an index. RANGE SCAN A retrieval of one or more ROWIDs from an index. Indexed values are scanned in ascending order. RANGE SCAN A retrieval of one or more ROWIDs from an index. DESCENDING Indexed values are scanned in descending order. FIRST ROW A retrieval on only the first row selected by a query. COUNT Operation counting the number of rows selected from a table. ∗

= ˆ These operations are access methods = ˆ operations are join operations.

+

Michael Gertz

Oracle Query Optimizer