UNC
School of Information and Library Science 		INLS723 – Database Systems II, Fall 2007
INLS 723

Main Page

Announcements

Calendar/Notes

Assignments

Links

Course Policies

Grades

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;
drop table demp;
drop table drives;
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 Sailors, Boats, and Reserves

Start with this example to see the query plan: 
select sid, sname
from sailors s
where sname = 'leiferikson';
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 sname_index on sailors(sname);

select sid, sname
from sailors s
where sname = 'leiferikson';

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
    • Throughput
    • Response time
  • 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

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 r) */ s.sname, b.color
from sailors s,  reserves r, boats b
where s.sid=r.sid AND b.bid=r.bid;

References and Links