代写COMP7104-DASC7104 Practical Assignment 2 - Query optimization代写数据库编程

COMP7104-DASC7104

Practical Assignment 2 - Query optimization (Oracle)

Deadline: Friday April 11th, 23:59.

Make sure you have completed the preliminary steps described in “Practical Assignment 2 - Preliminary instructions (installation, environment, data)”

SECTION 1 – General query execution instructions

Before executing queries, make sure the following system command is executed:

ALTER SYSTEM SET optimizer_features_enable = " 11.2.0.4";

For each SQL query to be executed, here are the instructions that will be used to understand its optimization and execution:

•   RULE: Optimization mode without statistics over the tables ALTER SESSION SET OPTIMIZER_MODE=RULE;

•   CHOOSE: optimization mode with statistics, allows to obtain a more efficient plan ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

(by default, we will use CHOOSE, unless RULE is also requested explicitly)

•   CACHE:  You must flush (empty) the RAM cache before each execution / auto-trace. ALTER SYSTEM FLUSH BUFFER_CACHE;

•   MOVIEi: A query on table MOVIE can be executed on one of the MOVIE copies (1- 4). Each time, which MOVIE copy to use will be indicated in text.

For each query, you will complete a form like the one below, by looking at the top-most row for (a) the value of LAST_CR_BUFFER_GETS (corresponding roughly to the number of pages from the RAM buffer cache – so cache hits leading to no diskI/O operations, a.k.a. “consistent gets” or “logical reads”) and (b) the value of LAST_DISK READS (corresponding roughly to the number of pages read from disk, a.k.a. “physical reads”, so cache misses leading to disk I/Os operations):

Here is one example:

Question: On MOVIE1 and MOVIE4 :

SELECT TITLE

FROM MOVIEi

WHERE IDMOVIE=50273 ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE1	CHOOSE	INDEX RANGE SCAN + ACCESS ROWIDS	To be filled	To be filled
MOVIE4	CHOOSE	TABLE ACCESS FULL	To be filled	To be filled

To fill this table, you would run first:

ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

ALTER SYSTEM FLUSH BUFFER_CACHE;

And then run Autotrace on the following query:

SELECT title, genre

FROM MOVIE1

WHERE IDMOVIE=50273 ;

You would observe the plan and its cost (Buffer Reads, Disk Reads), and fill the table. Note: We already describe in the table the main operators used in each plan.

Then you would also run:

ALTER SESSION SET OPTIMIZER_MODE=CHOOSE;

(even  if  the  optimizer  is  already  in  mode  CHOOSE   since  before,  better  do  it systematically to avoid forgetting it…)

ALTER SYSTEM FLUSH BUFFER_CACHE;

And then run Autotrace on the query:

SELECT title, genre

FROM MOVIE4

WHERE IDMOVIE=50273 ;

And observe the plan and its cost (Buffer Reads, Disk Reads), and fill the table.

YOUR WORK STARTS HERE:

SECTION 2 – SINGLE-TABLE QUERIES

Question 3.1 On MOVIE1 and MOVIE4 :

WHERE IDMOVIE=50273 ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE1	CHOOSE	INDEX UNIQUE SCAN + TABLE ACCESS BY ROWID
MOVIE4	CHOOSE	INDEX UNIQUE SCAN

Write a concise explanation of the SQL query (what it asks for, in plain English):

Write a concise explanation of the two execution plans:

Can the number ofI/Os in the case of Movie1 be explained and, if yes, how ?

Explain the I/O difference in the cost of the two plans:

Question 3.2 On MOVIE2, with RULE, CHOOSE :

SELECT TITLE

FROM MOVIEi M

WHERE YEAR=1999 ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE2	RULE	INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID
MOVIE2	CHOOSE	FULL SCAN

Write a concise explanation of the SQL query (what it asks for, in plain English):

Write a concise explanation of the two execution plans:

How can you explain the number ofI/Os for the CHOOSE plan:

In Oracle, we can force the optimizer to make certain choices, using hints.

Execute the following:

alter session set "_optimizer_ignore_hints"=false;

Then, place the following /* */ comment right after the SELECT in the previous query and re-execute the CHOOSE pipeline (CHOOSE mode, flush, auto-trace on query)

(more details here if you want to learn more on this later:

https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm) /*+ HINT index(M BTREE_MOVIE2_YEAR) */

As in:

SELECT /*+ HINT index(M BTREE_MOVIE2_YEAR) */

TITLE

FROM MOVIE2 M

WHERE YEAR=1999 ;

What do you observe ?

Question 3.3 On MOVIE2, MOVIE4 :

SELECT COUNT(*)

FROM MOVIEi

WHERE YEAR=1999 ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE2	CHOOSE	INDEX RANGESCAN + SORT AGGREGATE
MOVIE4	CHOOSE	INDEX FAST FULL SCAN+SORT AGGREGATE

Write a concise explanation of the SQL query (what it asks for, in plain English):

What do the two plans have in common?

What do the two plans have as their main difference?

How many index leaf pages are accessed in Movie2?

How can you explain the number of index leaf pages that are accessed in Movie2?

How can you explain the much larger I/O cost in Movie4?

SECTION 4 – MULTI-TABLE (JOIN) QUERIES

Question 4.1 Run the following statistics queries and observe their results:

SELECT COUNT(DISTINCT IDMES) FROM MOVIE2 M

WHERE CODECOUNTRY='aaej';

SELECT COUNT(*) FROM MOVIE2 M

WHERE CODECOUNTRY='aaej';

Then, on MOVIE2, with RULE, CHOOSE :

SELECT TITLE, LASTNAME FROM MOVIEi M, ARTIST A

WHERE CODECOUNTRY='aaej' AND M.IDMES=A.IDARTIST ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE2	CHOOSE	Hash join, Table access full (Artist), Table access (by index rowID), Index Range Scan (on Movie2_code)
MOVIE2	RULE	Nested loops  Index range scan on Movie2_code, Index unique scan on Artist, Table access (by index rowID)

Write a concise explanation of the SQL query (what it asks for, in plain English):

Write a concise explanation of the execution plan in CHOOSE mode:

Write a concise explanation of the execution plan in RULE mode:

How many heap pages of the Artist table are retrieved in the RULE mode plan?

What could have been in the worst-case the number of heap pages retrieved from the Artist table in the RULE mode plan?

How many leaf nodes of the index on Movie2 are accessed, in either plan?

In Oracle, we can force the optimizer to make certain choices, using hints.

Execute the following:

alter session set "_optimizer_ignore_hints"=false;

Then, place the following /* */ comment right after the SELECT in the previous query and re-execute the CHOOSE pipeline (CHOOSE mode, flush, auto-trace on query)

(more details here if you want to learn more on this later:

https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm)

/*+USE_MERGE(M A)*/

Which changes the join method to SORT MERGE, using the hint.

As in:

SELECT /*+USE_MERGE(M A)*/

TITLE, LASTNAME

FROM MOVIE2 M, ARTIST A

WHERE CODECOUNTRY='aaej' AND M.IDMES=A.IDARTIST ;

What do you observe ?

Question 4.2 Run the following statistics queries and observe their results:

SELECT COUNT(DISTINCT IDMES) FROM MOVIE2 M

WHERE CODECOUNTRY='aaej';

SELET COUNT(*) FROM MOVIE2 M

WHERE CODECOUNTRY='aaej';

Then, on MOVIE2, with RULE, CHOOSE :

SELECT TITLE, IDMES

FROM MOVIEi M, ARTIST A

WHERE CODECOUNTRY='aaej' AND M.IDMES=A.IDARTIST ;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE2	CHOOSE	INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID
MOVIE2	RULE	INDEX RANGE SCAN + TABLE ACCESS BY INDEX ROWID

Write a concise explanation of the SQL query (what it asks for, in plain English):

Write a concise explanation of the execution plan in CHOOSE mode:

Write a concise explanation of the execution plan in RULE mode:

How many leaf nodes of the index on Movie2 are accessed, in either plan?

Question 4.3 On MOVIE2 :

SELECT lastname, title

FROM MOVIEi M, MOVIERATER MR, RATING R

WHERE  MR.email= R.email AND M.IDMOVIE=R.IDMOVIE AND R.IDMOVIE= 367856;

TABLE	MODE	OPERATORS	BUFFER READS	DISK READS
MOVIE2	CHOOSE	Nested Loops, Index Unique Scan on MovieRater, IndexUnique Scan on Movie2,  Index range scan on Rating

Write a concise explanation of the SQL query (what it asks for, in plain English):

Write a concise explanation of the execution plan:

Why the Rating table is not accessed?

Explain the number ofI/Os from accessing the PK index on MovieRater ?

How many heap pages are read in total ?