Database Reference
In-Depth Information
9.
To reduce the number of SQL statements in the result set, choose to filter by
Schema name
= 'SCOTT'
. The Advisor shows us the SQL statements that are potential opportunities for
tuning, as shown in Figure 9.27. From the result set, choose a SQL statement to review.
10.
We selected the first query that looked suspicious to us, a multi-table join that did not
have a
WHERE
clause (select * from
dept,emp,bonus,salgrade
), and on the details page,
shown in Figure 9.28; the Advisor indicates that we have a Merge Join Cartesian oper-
ation in the execution plan, which could present a potential tuning opportunity.
FiGURe 9.27
SQL Tuning Advisor recommendations for a SQL statement
SQL Tuning Advisor Supplied Package and Views
Oracle 12
c
includes the
DBMS_SQLTUNE
package to manually execute the SQL Tuning
Advisor. Instead of pointing and clicking from Oracle EM, you can manually configure the
steps from SQL*Plus or another SQL front end.
Oracle also provides SQL tuning informational views, if you prefer to use them instead
of EM:
■
DBA_ADVISOR_*
■
DBA_SQLTUNE_*
■
DBA_SQLSET
_*
DBA_SQL_PROFILES
■
Search WWH ::
Custom Search