Database Reference
In-Depth Information
database environment, we will use the SQL Tuning Advisor, the SQL Access Advisor, and
Database Replay.
Each of these tools can be used by the DBA to analyze and improve database performance.
The Advisors operate directly on the database you wish to tune, while the Database Replay
feature allows you to test a production workload on a test system to determine ways to improve
performance without directly impacting the production system.
Chapter 10, “Managing Database Resources,” is dedicated to the topics of
managing memory and disk space resources.
Using the SQL Tuning Advisor
The SQL Tuning Advisor is a tool that you can use to analyze the performance of one
or more SQL statements. To improve SQL performance, the Advisor may suggest new
or modified indexes, new SQL profiles, restructuring your SQL statements, or gathering
statistics. The SQL Tuning Advisor runs in one of two modes, automatic or manual. The
Automatic Tuning Advisor is scheduled to run during the maintenance window, finds ways
to improve high-load SQL statements, and automatically takes action.
In the multitenant Oracle Database 12 c environment, Automatic SQL Tuning Advisor
data is stored in the CDB root and has these characteristics:
Data may include SQL statements executed in a PDB that were analyzed by the Tuning
Advisor. These results are not included if the PDB is unplugged.
Results are visible only to a common user whose current container is the root, not
when the current container is a PDB.
Use the SQL Tuning Advisor in manual mode to analyze collections of SQL statements
or individual SQL statements. In manual mode, the SQL Tuning Advisor is used to analyze
a collection of SQL statements called a SQL Tuning Set.
You can run the SQL Tuning Advisor in manual mode in the multitenant environment to
tune SQL statements in one or more PDBs.
Multiple PDBs To tune an SQL statement that runs in multiple PDBs, run the SQL Tuning
Advisor manually as a common user whose current container is the root. The results are
stored in the root.
The Current PDB To tune an SQL statement only in the current PDB, run the SQL
Tuning Advisor manually as a user whose current container is the PDB. The results are
stored in the PDB and are included if the PDB is unplugged. The same SQL statement
running in other PDBs will not be tuned.
A common user must have the SET CONTAINER privilege to run SQL Tuning Advisor for
SQL statements from a PDB, as well as the privileges to run the SQL statements in the PDB.
Search WWH ::




Custom Search