everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

Performance

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15
<< Previous

Chapter # 12

Next >>


 

Optimizing a SQL statement

 

Introduction

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%

EXPLAIN PLAN SET STATEMENT_ID=

 

Hands-on

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 ORACLE user.

SQL> CONNECT oracle/learning

 

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

SQL> DESC plan_table

Create PLAN_TABLE

In order to optimize a SQL statement, you execute the EXPLAIN PLAN statement to populate a list plan of execution in PLAN_TABLE. Then you write a SQL statement against the table to query a plan of execution list generated by EXPLANIN PLAN.

If PLAN_TABLE does not exist, run the utlxplan.sql script provided in the rdbmsfolder to create the PLAN_TABLE table.

SQL> START %ORACLE_HOME%

Now, the PLAN_TABLE table was created.

 

Check the number of records in the table.

SQL> SELECT count(1)

FROM plan_table

/

There should be no records in the table.

 

Evaluate a SQL statement

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. You should have one single unique statement_id for each specific SQL statement that you want to optimize.

 

SQL> 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;

 

Check the number of records in the table again.

SQL> SELECT count(1)

FROM plan_table

/

Now, there should be at least 13 records in the table.

 

Display the result of the SQL statement evaluation

Now, recall the execution plan from the PLAN_TABLE table.

SQL> 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'

/

 

How to read PLAN_TABLE

Now, assuming the following is an output of the above query, let's learn how to read the output report.

 

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

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

1 0 SORT GROUP BY

 

FILTER

"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

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

3 2 NESTED LOOPS

 

MERGE JOIN

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

4 3 MERGE JOIN

 

SORT JOIN

"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

"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

"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

"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

 

Read your output and see what the problem of the query is

 

 

Questions:

Q: How do you optimize a SQL statement?

Q: How do you identify that a SQL statement is not optimized?

Q: Describe the EXPLAIN PLAN statement?

Q: How do you create the PLAN_TABLE table?

Q: Describe the use of the SET STATEMENT_ID clause?

Q: Describe the following operation in PLAN_TABLE?

TABLE ACCESS FULL

TABLE ACCESS BY INDEX

INDEX UNIQUE SCAN

NESTED LOOPS

MERGE JOIN

FILTER

SORT AGGREGATE

Q: What does the following SQL statement?

SQL> 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

/

Q: What does the following SQL statement?

SQL> 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'

/

Q: How do you read the following PLAN_TABLE output?

ID PARENT_ID Query_Plan

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

0 SELECT STATEMENT Cost =

1 0 SORT GROUP BY

2 1 FILTER

3 2 NESTED LOOPS

4 3 MERGE JOIN

5 4 SORT JOIN

6 5 TABLE ACCESS FULL STOCKS

7 4 SORT JOIN

8 7 TABLE ACCESS FULL PORTFOLIO

9 3 TABLE ACCESS BY INDEX ROWID CUSTOMERS

10 9 INDEX UNIQUE SCAN SYS_C003126

11 2 SORT AGGREGATE

12 11 TABLE ACCESS FULL STOCKS

 

 

     Reviews and Templates for FrontPage
     

Copyright © everythingOracle.bizhat.com 2006 All Rights Reserved.