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