|
"Silence is
argument carried out by other means." -
Ernesto"Che"Guevara (1928-1967) |
Read
first then play the video:
OEM008(VIDEO)-Optimizing
a SQL statement
Optimizing a SQL statement
using OEM
Introduction
As a DBA, you are
responsible for optimizing a SQL statement. The following is a summary
of your task:
Covers:
Using the Top Session Tool
Selecting a target database
Obtaining information about
a SQL Statement
Browsing a SQL Statement in
the memory
Running the EXPLAIN PLAN
statement
Obtaining table access
information
Hands-on
In this exercise you will
learn how to use the "Top Sessions" tool to get information
about a SQL statement to determine how the optimizer will execute the
query in question using the Oracle Enterprise Management tool (OEM).
Expand the Database item.
Highlight the SCHOOL database.
Click on the "Diagnostics Pack" icon and then click on the
"Top Sessions" icon.
Double click on the OEM session to display a detail session
information.
On the "Current SQL" section, select the "Explain
Plan" option to determine how the optimizer will execute the
query in the question.
Browse through the SQL statement.
Then, browse through the steps of explain plan operations.
We assume that you have already have a good understanding of the
following steps:
For example you know that if the step name value is: TABLE ACCESS
FULL, it means Oracle will look at every row in the table, and that is
the slowest way.
If the step name is: TABLE ACCESS BY INDEX, that means that Oracle
will use the ROWID method to find a row in the table, and that is very
fast.
If the value is: INDEX UNIQUE SCAN, that means Oracle will use the
primary or unique key, and that is the most efficient way to search an
index.
If it is: NESTED LOOPS or MERGE JOIN that indicates the join statement
is occurring.
If the value is: FILTER that means this is an operation that adds
selectivity to a
TABLE ACCESS FULL operation, based on the contents of the where
clause.
If it is: SORT AGGREGATE, that means Oracle will perform a sort on the
data obtained for the user, etc.
Then, close the Windows.
This way you will know why there is a performance problem and then you
act accordingly.
Maybe you have to have more index columns.
|
