cs342 → performance → lab9

In these lab exercises, we will be using imdbLarge, a fuller version of the movies database based on a subset of the Internet Movies Database (IMDB). Because of its size, this database must be loaded using Oracle Datapump files. See the following readme file for details on how to do this.

C:\projects\cs342\cs342-resources\web\resources\databases\imdbLarge\readme.txt

The schema is similar, but not identical to the imdb database we’ve used before; see the schema.sql for details.

When tuning SQL code, use the following tools (see the class code in cs342/code/09programming for examples of how to use them).

The following settings turn on the execution plan output.
```
SET AUTOTRACE ON; 
SET SERVEROUTPUT ON;
SET TIMING ON;
```
Set AUTOTRACE to TRACEONLY for queries with voluminous output.
To list the indexes currently maintained by Oracle, run getIndexes.sql. You can deactivate/activate the indexes using the following command.
```
ALTER INDEX indexName [INVISIBLE, VISIBLE];
```

You can create/drop indexes using the following commands.

[CREATE, DROP] INDEX IndexName ON Tablename(fieldList);

To select random, individual records in the Actor or Movie tables, it’s useful to have a continuously numbered index value so that you can randomly generate an index using dbms_random.value(). To support this, run buildOrdinalTables.sql. With this done, you can run random selection queries such as the one used in class exercise 9.1.b.
You can execute code multiple times using the following anonymous code block.
```
DECLARE
    dummy AppropriateType;
BEGIN
	FOR i IN 1..1000 LOOP
		YourSelectCommand (with INTO dummy clause);
	END LOOP;
END;
```
Note that the imdbLarge is sufficiently large that you may not need to loop this many times to get reasonable results.

With this rather large database in place and these helpful tools, do the following exercises.

Exercise 9.1

Build sample queries to test that:

there is a benefit to using either COUNT(1) , COUNT(*) or SUM(1) for simple counting queries.
the order of the tables listed in the FROM clause affects the way Oracle executes a join query.
the use of arithmetic expressions in join conditions (e.g., FROM Table1 JOIN Table2 ON Table1.id+0=Table2.id+0 ) affects a query’s efficiency.
running the same query more than once affects its performance.
adding a concatenated index on a join table improves performance (see the create index command described above).

Store your test queries along with your conclusions and explanations in lab09_1.sql . Be sure to experiment with different variations of each exercise and pay particular attention to the EXPLAIN-PLAN operations listed in the reading guide.

Don Burleson offers some useful Oracle tuning tips at Oracle SQL tuning, though his heuristics don’t always work out on the IMDBlarge.

Checking in

We will grade your work according to the following criteria:

10% — Guide questions
90% — Exercise 9.1