Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out


. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |


Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.



Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 |


Lesson 08

"Silence is argument carried out by other means." - Ernesto"Che"Guevara (1928-1967)


Read first then play the video:

   OEM-VIDEO -Optimizing a SQL statement


Optimizing a SQL statement using OEM



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


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



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.