argument carried out by other means." -
first then play the video:
a SQL statement
Optimizing a SQL statement
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
Running the EXPLAIN PLAN
Obtaining table access
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
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
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
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
If it is: NESTED LOOPS or MERGE JOIN that indicates the join statement
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
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
Maybe you have to have more index columns.