Oracle Query Optimization
Before you start
If you are using SQL*Plus...
Before you can see the output of PL/SQL programs in SQL*Plus,
you need to set the size of the output buffer. Do this by
typing the following command into SQL*Plus.
Notice that there is no semi-colon at the end of the command.
SET SERVEROUTPUT ON SIZE 4096
If you are using Oracle SQL Developer...
Enter your PL/SQL programs into the window where you normally
enter your SQL statements.
To enable output, click on the "DBMS Output" tab and then click
the "Enable DBMS Output" button (to the left of the pencil eraser
button).
Run the PL/SQL program by clicking
the "Run Script" button or pressing F5.
After the script is run,
in the lower part of the screen, the "Script Output" window will
show "anonymous block completed" to indicate that the script
has finished running.
To see the output, click on the "DBMS Output" tab.
Generating a large test data set using PL/SQL
First, you need to get some code to generate pseudo-random
numbers. The random package that we will use for example
purposes was written by Jeff Hunter and can be found at:
http://www.idevelopment.info/data/Oracle/DBA_tips/PL_SQL/PLSQL_9.shtml
Copy the "Method 1" code exactly as shown and run it.
After loading the random number package, run the following commands
to create the tables that we will use.
drop table trucks cascade constraints;
drop table demp cascade constraints;
drop table drives cascade constraints;
create table trucks (tid INTEGER PRIMARY KEY, color varchar(20));
create table demp (eid INTEGER PRIMARY KEY, years varchar(20));
create table drives (did INTEGER PRIMARY KEY, tid INTEGER, eid INTEGER,
FOREIGN KEY (eid) REFERENCES demp,
FOREIGN KEY (tid) REFERENCES trucks);
After the tables are created, the following PL/SQL code will
create a random set of 100 trucks (0-99) with random colors,
100 demp (driver employees, 0-99),
and a set of 1000 drives records that record when a demp drove a
particular truck.
DECLARE
a NUMBER;
r NUMBER;
t NUMBER;
c VARCHAR(20);
BEGIN
-- Create the trucks
a:=0;
LOOP
r := random.RNDINT(4);
IF r = 0 THEN c:='red';
ELSIF r = 1 THEN c:='blue';
ELSIF r = 2 THEN c:='green';
ELSE c:='yellow';
END IF;
DBMS_OUTPUT.PUT_LINE('a = ' || a || ', c = ' || c);
insert into trucks values (a, c);
a := a + 1;
EXIT WHEN a>=100;
END LOOP;
-- Create the demp
a:=0;
LOOP
r := random.RNDINT(30);
insert into demp values (a, r);
a := a + 1;
EXIT WHEN a>=100;
END LOOP;
-- Create the drives
a:=0;
LOOP
r := random.RNDINT(100);
t := random.RNDINT(100);
insert into drives values (a, r, t);
a := a + 1;
EXIT WHEN a>=1000;
END LOOP;
END;
You can then verify that the data was created using select statements.
select count(*) from drives;
select * from demp where eid > 90;
select * from trucks where tid > 90;
select demp.eid, trucks.tid, trucks.color
from demp, trucks, drives
where demp.years > 10 AND trucks.color='yellow' AND trucks.tid = drives.tid AND demp.eid = drives.eid;
Oracle TRACE utility
You can use the Oracle TRACE utility to get timing information
about your queries. However, you have to have permission to use
this Oracle feature from your DBA, or it has to be enabled for all
(which may have security issues).
More information about the TRACE utility can be found at:
http://batboy.fms.indiana.edu/oracle/oracle_trace_info.html
Oracle EXPLAIN PLAN
Note: If you are using SQL*Plus, you will need to use the
syntax shown on this page:
http://www.ils.unc.edu/courses/2006_fall/inls723_001/oct30.php
If you are using the Oracle SQL Developer, you can highlight the query
and then click the "Execute Explain Plan" button or press F6.
EXPLAIN PLAN examples using Trucks, Demp, and Drives
Start with this example to see the query plan:
select d.eid
from drives d
where d.tid = 5;
Notice: ALL_ROWS, TABLE ACCESS(FULL)
Look at:
Oracle Database Performance Tuning Guide, Chapter 13
Index to speed up access
If you create an index, Oracle can use it to speed up access.
create index tid_index on drives(tid);
select d.eid
from drives d
where d.tid = 5;
Optimizer recognizes large data access
The Oracle cost-based optimizer (CB0) will recognize
if you are doing a query that will return a large amount
of data from the table. If so, it may be faster to do a full
table scan rather than using the index.
select d.eid
from drives d
where d.tid > 1;
Unique scans
If Oracle determines that you are doing a query
that accesses a UNIQUE or PRIMARY KEY field
such that only one row will be accessed,
then it may use a Index Unique scan.
select t.color
from trucks t
where t.tid = 5;
Oracle Query Optimizer
The Oracle query optimizer works to optimize queries in terms of speed,
memory usage, and other factors.
Look at:
Oracle Database Performance Tuning Guide, Chapter 13
- Optimizer operation
- Optimizer goal
- Understanding the optimizer (Fig. 13-1)
- Access Paths
- Full Table Scans
- Rowid Scans
- Index Scans
- Hash Access
- Joins
- Nested Loop Joins
- Hash Joins
- Sort Merge Joins
Join examples
select t.color
from trucks t, drives d
where t.tid = d.tid AND d.did < 5;
select e.eid, t.color
from demp e, trucks t, drives d
where e.eid = d.eid AND t.tid = d.tid AND d.did < 5;
Oracle Hints
Hints can be used to change the way the Oracle query optimizer
organizes a query.
Look at:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/hintsref.htm#sthref1602
select /*+ FULL(s) */ sid, sname
from sailors s
where sname = 'leiferikson';
select s.sname, b.color
from sailors s, reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;
select /*+ USE_NL(s r) */ s.sname, b.color
from sailors s, reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;
select /*+ NO_USE_NL(s r) */ s.sname, b.color
from sailors s, reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;
select s.sname, b.color
from sailors s, reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;
select /*+ LEADING(b) */ s.sname, b.color
from sailors s, reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;
References and Links
|