Zicklin School of Business - Baruch College
City Unversity of New York
Database Management Systems II
Query Optimization Example
Query Optimization Example
The following notes cover some basic examples
of query optimization and hints in Oracle. These
examples assuem you have an Oracle account at Baruch College.
If you do not, the following instructions may be much different
for your local installation.
Database Schema
The following example of an OLTP system resides in the
the WAREHOUSE schema of the Oracle database. The
data model consists of a normalized schema
including tables for BRANCH, CLIENT, POWER, RENTAL, STAFF and
VIEWING.
For a list of the CREATE TABLE and other DDL statements
used to create the schema
Check here.
You will also see a list of the indexes as well.
Note that you do not have to create this schema - it has already
been done in the Oracle database at Baruch College.
Step 1: Create the PLAN_TABLE table in your schema
Copy and paste the following CREATE TABLE statement
into your Oracle schema using SQL*Plus. You need only do this ONE TIME.
|
<![CDATA[
CREATE TABLE PLAN_TABLE (
STATEMENT_ID VARCHAR2(30), TIMESTAMP DATE,
REMARKS VARCHAR2(80), OPERATION VARCHAR2(30),
OPTIONS VARCHAR2(30), OBJECT_NODE VARCHAR2(128),
OBJECT_OWNER VARCHAR2(30), OBJECT_NAME VARCHAR2(30),
OBJECT_INSTANCE NUMERIC, OBJECT_TYPE VARCHAR2(30),
OPTIMIZER VARCHAR2(255), SEARCH_COLUMNS NUMBER,
ID NUMERIC, PARENT_ID NUMERIC,
POSITION NUMERIC, COST NUMERIC,
CARDINALITY NUMERIC, BYTES NUMERIC,
OTHER_TAG VARCHAR2(255), PARTITION_START VARCHAR2(255),
PARTITION_STOP VARCHAR2(255), PARTITION_ID NUMERIC,
OTHER LONG, DISTRIBUTION VARCHAR2(30));
]]>
|
The PLAN_TABLE is used to store data about your query plan.
Step 2: Format some columns so that query results print nicely
Each time you log into SQL*Plus and you want to try these exercises,
paste in these COLUMN ... FORMAT commands so that the output is
formatted nicely. Note that these commands do not affect the
underlying data types or data. They only affect how the query
results are formatted in SQL*Plus.
|
<![CDATA[
COLUMN propertyno FORMAT A10
COLUMN city FORMAT A10
COLUMN staffno FORMAT A6
COLUMN lname FORMAT A10
COLUMN branchno FORMAT A6
]]>
|
Step 3: Turn on the AUTOTRACE facility in SQL*Plus
- The AUTOTRACE facility in SQL*Plus can be used to automatically
gather information about a query and then display the execution plan.
- The general command is: SET AUTOTRACE ON
In this mode, the query will be executed and the query output will be
displayed. Following the normal query output, the execution plan
will be shown along with the query statistics.
- Note that in certain version of SQL*Plus (Such as 3.x) you may have
to enter a SQL*Plus command: SET ARRAYSIZE 2 in order to
display the Autotrace output.
- Another useful variation of the Autotrace command is:
SET AUTOTRACE TRACEONLY
In this mode, the normal output of the query is surpressed and
only the query statistics and plan are shown.
- To see if the AUTOTRACE facility is on or off, use the SQL*PLus SHOW AUTOTRACE
commad.
- To shut off the AUTOTRACE facility use: SET AUTOTRACE OFF
Step 4: Run a query
The following query runs against tables in the WAREHOUSE
schema. In the FROM clause,
the WAREHOUSE schema has been specified. This is where the
tables are stored.
|
<![CDATA[
SELECT rental.propertyNo, rental.city, rental.staffNo,
staff.lName, staff.branchNo, branch.city
FROM warehouse.rental, warehouse.staff, warehouse.branch
WHERE rental.staffNo = staff.staffNo
AND staff.branchNo = branch.branchNo
AND branch.city = rental.city
AND rental.rooms > 3
AND (staff.salary > 10000
OR rental.rtype = 'House')
]]>
|
Here is the output from running this command with the AUTOTRACE
facility on:
|
<![CDATA[
SQL> COLUMN propertyno FORMAT A10
SQL> COLUMN city FORMAT A10
SQL> COLUMN staffno FORMAT A6
SQL> COLUMN lname FORMAT A10
SQL> COLUMN branchno FORMAT A6
SQL> SET AUTOTRACE ON
SQL> SELECT rental.propertyNo, rental.city, rental.staffNo,
2 staff.lName, staff.branchNo, branch.city
3 FROM warehouse.rental, warehouse.staff, warehouse.branch
4 WHERE rental.staffNo = staff.staffNo
5 AND staff.branchNo = branch.branchNo
6 AND branch.city = rental.city
7 AND rental.rooms > 3
8 AND (staff.salary > 10000
9 OR rental.rtype = 'House') ;
PROPERTYNO CITY STAFFN LNAME BRANCH CITY
---------- ---------- ------ ---------- ------ ----------
PG16 Glasgow SG14 Ford B003 Glasgow
PG21 Glasgow SG37 Beech B003 Glasgow
PA14 Aberdeen SA9 Howe B007 Aberdeen
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'BRANCH'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'RENTAL'
5 4 INDEX (RANGE SCAN) OF 'IDX_RENTAL_CITY' (NON-UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'STAFF'
7 6 INDEX (UNIQUE SCAN) OF 'PK_STAFF' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
23 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
]]>
|
- Of interest to us in terms of query optimization are the actual plan
plus the following query statistics:
- db block gets - The number of blocks the DBMS had to retrieve
- physical reads - The number of physical disk reads
(the rest were found in the cache)
- consistent gets - Number of requests for a db block
- Of interest to us in terms of networking performance are:
- bytes sent via SQL*Net to client - The number of bytes
sent from the server to the client.
- bytes received via SQL*Net from client - The number of
bytes actually received by the server from the clientdisk reads
- SQL*Net roundtrips to/from client - Number of network round trips
between client and server.
- An query that optimizes DBMS resources will minimize the db block gets,
and consistent gets.
- Physical gets may vary depending on how many other users are on the
database. If there are only a few users, then more of the table
records will remain in the cache and this will result in fewer
physical reads.
Step 5: Adding Query Hints
- Query hints are a way to override the default
optimization behaviour of the DBMS.
- By specifiying different query hints, we can influence
the execution plan and hence the performance of the query.
- Different DBMS apply hints in slightly different ways.
- For example, in Oracle, a query hint is placed in a special
comment just after the SELECT keyword. Like this:
SELECT /*+ some hints here */
- In Oracle, some hints include:
| Hint | Explanation
|
|---|
| CHOOSE | If statistics are available (see DBMS_STATS package)
then use Cost Based otherwise use Rule Based.
|
| RULE | Use the Rule based Optimizer (even if table
statistics are available).
|
| ALL_ROWS | Use the Cost Based optimizer to maximize
throughput (minimize system resources)
|
| FIRST_ROWS | Use the Cost Based optimizer to minimize
response time
|
| ORDERED | Join tables in the order in which they appear in
the FROM clause of the query. Try changing around the order
to see if performance improves.
|
| FULL (table_name) | Perform a full table scan on the
named table even if an index is available.
|
| INDEX (table_name index_name) | Forces the use of the named index
on the named table (rather than a full table scan).
|
As an exercise, try different query hints such as
RULE, CHOOSE, ORDERED, FIRST_ROWS, ALL_ROWS, FULL, INDEX
and so on for the above query. Then use the AUTOTRACE feature
to see which would be the lowest cost way (in terms of
db block gets, consistent gets and physical reads) to run the
query (be sure you have created the PLAN_TABLE table
and you have SET AUTOTRACE ON).
|
<![CDATA[
SELECT /*+ALL_ROWS */
rental.propertyNo, rental.city, rental.staffNo,
staff.lName, staff.branchNo, branch.city
FROM warehouse.rental, warehouse.staff, warehouse.branch
WHERE rental.staffNo = staff.staffNo
AND staff.branchNo = branch.branchNo
AND branch.city = rental.city
AND rental.rooms > 3
AND (staff.salary > 10000
OR rental.rtype = 'House');
PROPERTYNO CITY STAFFN LNAME BRANCH CITY
---------- ---------- ------ ---------- ------ ----------
PG16 Glasgow SG14 Ford B003 Glasgow
PG21 Glasgow SG37 Beech B003 Glasgow
PA14 Aberdeen SA9 Howe B007 Aberdeen
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: ALL_ROWS (Cost=4 Card=1 Bytes=269)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=269)
2 1 HASH JOIN (Cost=3 Card=1 Bytes=215)
3 2 TABLE ACCESS (FULL) OF 'RENTAL' (Cost=1 Card=5 Bytes=605)
4 2 TABLE ACCESS (FULL) OF 'STAFF' (Cost=1 Card=82 Bytes=7708)
5 1 TABLE ACCESS (BY INDEX ROWID) OF 'BRANCH' (Cost=1 Card=82 Bytes=4428)
6 5 INDEX (UNIQUE SCAN) OF 'PK_BRANCH' (UNIQUE)
Statistics
----------------------------------------------------------
222 recursive calls
8 db block gets
20 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
9 sorts (memory)
0 sorts (disk)
3 rows processed
]]>
|
Below is another example using the RULE based optimizer.
|
<![CDATA[
SELECT /*+RULE */
rental.propertyNo, rental.city, rental.staffNo,
staff.lName, staff.branchNo, branch.city
FROM warehouse.rental, warehouse.staff, warehouse.branch
WHERE rental.staffNo = staff.staffNo
AND staff.branchNo = branch.branchNo
AND branch.city = rental.city
AND rental.rooms > 3
AND (staff.salary > 10000
OR rental.rtype = 'House');
PROPERTYNO CITY STAFFN LNAME BRANCH CITY
---------- ---------- ------ ---------- ------ ----------
PG16 Glasgow SG14 Ford B003 Glasgow
PG21 Glasgow SG37 Beech B003 Glasgow
PA14 Aberdeen SA9 Howe B007 Aberdeen
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 TABLE ACCESS (FULL) OF 'BRANCH'
4 2 TABLE ACCESS (BY INDEX ROWID) OF 'RENTAL'
5 4 INDEX (RANGE SCAN) OF 'IDX_RENTAL_CITY' (NON-UNIQUE)
6 1 TABLE ACCESS (BY INDEX ROWID) OF 'STAFF'
7 6 INDEX (UNIQUE SCAN) OF 'PK_STAFF' (UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
4 db block gets
23 consistent gets
0 physical reads
0 redo size
646 bytes sent via SQL*Net to client
359 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed
]]>
|
More about Oracle's Query Optimizers
- The Oracle8i
Designing and Tuning for Performance is a great place to find more info
on optimizing queries.
File: optimizationex.html Date: Wed Oct 24 12:40:31 EDT 2001
All materials Copyright, 1997-2001 Richard Holowczak