iSelfSchooling - Optimizing a SQL statement using OEM

Hands-On 08 (Optimizing a SQL statement using OEM)

Description:

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

==Start.
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.

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

For more information about the subject, you are encouraged
to read from a wide selection of available books.

Good Luck!