iSelfSchooling - Optimizing a SQL statement

As a DBA, you are responsible for optimizing a SQL statement using the EXPLAIN PLAN statement in case of performance problems. Your job’s responsibilities dictate that you should at least be informed of the following basic fundamental subjects:

Using the EXPLAIN PLAN statement

Creating the PLAN_TABLE table

Submitting a SQL statement using the EXPLAIN PLAN statement

Using the SET STATEMENT_ID clause

Recalling the EXECUTION plan from the PLAN_TABLE table

Understanding of the following operations:

TABLE ACCESS FULL

TABLE ACCESS BY INDEX

INDEX UNIQUE SCAN

NESTED LOOPS

MERGE JOIN

FILTER

SORT AGGREGATE

Commands:

START %ORACLE_HOME%\rdbms\admin\utlxplan

EXPLAIN

EXPLAIN PLAN SET STATEMENT_ID=

Manuscript

-- Hands-On 12 (Optimizing a SQL statement)
-- Optimizing a SQL Statement
-- Preparation
set echo on
connect system/manager as sysdba
SET linesize 1000 pagesize 55
COL name FORMAT a40
CONNECT iself/schooling
drop table plan_table;
col parameter format a40
col username format a10
col Query_Plan format a60
col owner format a10
col type format a4
pause

--Start

CLEAR SCR

-- In this exercise you will learn how to use the explain plan

-- statement to determine how the optimizer will execute the

-- query in question.

-- First, let's connect to SQLPlus as the ISELF user.

pause

CONNECT iself/schooling

pause

CLEAR SCR

-- Check to see if the PLAN_TABLE exists in the user's schema.

pause

DESC plan_table

-- It looks like the table does not exist.

pause

CLEAR SCR

-- Run the utlxplan.sql script provided in the rdbms\admin folder

-- to create the PLAN_TABLE table.

pause

-- START %ORACLE_HOME%\rdbms\admin\utlxplan

pause

START %ORACLE_HOME%\rdbms\admin\utlxplan

-- Now, the PLAN_TABLE table was created.

pause

CLEAR SCR

-- Check the number of records in the table.

pause

SELECT count(1)

FROM plan_table

-- There are no records in the table.

pause

CLEAR SCR

-- Submit a query to the database using the EXPLAIN PLAN statement,

-- so that the database will list the plan of execution.

-- Use the SET STATEMENT_ID clause to identify the plan for

-- later review.

pause

EXPLAIN PLAN

SET STATEMENT_ID='MY_FIRST_TEST'

INTO plan_table FOR

SELECT last_name, trade_date,

sum(shares_owned*current_price) portfolio_value

FROM customers, portfolio, stocks s

WHERE id = customer_id and stock_symbol = symbol

AND trade_date = (SELECT max(trade_date) FROM stocks

WHERE symbol = s.symbol)

GROUP BY last_name, trade_date;

pause

CLEAR SCR

-- Check the number of records in the table again.

pause

SELECT count(1)

FROM plan_table

-- Now, there are 13 records in the table.

pause

CLEAR SCR

-- Now, recall the execution plan from the PLAN_TABLE table.

pause

SELECT id, parent_id,

lpad(' ', 2*(level-1)) || operation || ' ' ||

options || ' ' || object_name || ' ' ||

decode (id, 0, 'Cost = ' || position) "Query_Plan"

FROM plan_table

START WITH id = 0 and STATEMENT_ID = 'MY_FIRST_TEST'

CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = 'MY_FIRST_TEST'

-- Now, let's learn how to read the output report.

pause

CLEAR SCR

-- The previous output report will be read this way. Notice that the PARENT_ID and ID

-- columns show a child and parent relationship.

ID PARENT_ID Query_Plan

--- ---------- ----------------------------------------------

0 SELECT STATEMENT Cost =

-- "SORT GROUP BY" means Oracle will perform a sort on the data obtained for the user.

1 0 SORT GROUP BY

-- "FILTER" means that this is an operation that adds selectivity to a TABLE ACCESS FULL

-- operation, based on the contents of the where clause.

2 1 FILTER

-- "NESTED LOOPS" indicates that the join statement is occurring.

3 2 NESTED LOOPS

-- "MERGE JOIN" indicates that the join statement is occurring.

4 3 MERGE JOIN

pause

CLEAR SCR

-- "SORT JOIN" indicates that the join statement is sorting.

-- "TABLE ACCESS FULL" means that Oracle will look at every row in the table (slowest way).

5 4 SORT JOIN

6 5 TABLE ACCESS FULL STOCKS

7 4 SORT JOIN

8 7 TABLE ACCESS FULL PORTFOLIO

-- "TABLE ACCESS BY INDEX" means that Oracle will use the ROWID method to find a row in

-- the table. It is very fast.

9 3 TABLE ACCESS BY INDEX ROWID CUSTOMERS

-- "INDEX UNIQUE SCAN" means Oracle will use the primary or unique key.

-- This is the most efficient way to search an index.

10 9 INDEX UNIQUE SCAN SYS_C003126

-- "SORT AGGREGATE" means Oracle will perform a sort on the data obtained for the user.

11 2 SORT AGGREGATE

12 11 TABLE ACCESS FULL STOCKS

pause

CLEAR SCR

-- Now, you should practice this Hands-On exercise.

-- For more information about the subject, you are encouraged

-- to read from a wide selection of available books.

-- Good luck.

pause