|
"Perfection is
achieved, not when there is nothing more to add, but when there
is nothing left to take away." - Antoine de Saint Exupery |
Read
first then play the video:
PRF-VIDEO -Optimizing
a SQL Statement
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 ISELF user.
SQL> CONNECT iself/schooling
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
|
"It is much more
comfortable to be mad and know it, than to be sane and have
one's doubts." - G. B. Burgin |
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: 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
|