OEM

As a DBA, you are responsible for optimizing a SQL statement. The following is a summary of your task:

Covers:

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