Monitoring and Tuning the Database
Monitoring the performance of a database and ensuring that it performs optimally is an
important task for a database administrator. This chapter discusses the features and
functions included in Oracle Database that make it easy to monitor database health,
identify performance problems, and implement any corrective actions.
Proactive Database Monitoring
Oracle Database makes it easy to monitor the health and performance of your database.
It monitors the vital signs (or metrics) related to database health and performance,
analyzes the workload running against the database, and automatically identifies any
issues that need your attention as an administrator. Any incidents (critical errors in the
database) are reported on the Database Home page in EM Express.
Monitoring General Database State and Workload
The Database Home page enables you to monitor the state and workload of your
database. It provides a central place for general database state information and is
updated periodically.
To monitor the general database state and workload:
1. Go to the Database Home page.
2. (Optional) Click the Refresh icon to the right of the selected refresh interval for the Auto
Refresh list to up date the information displayed.
The time that the Database Home page was last collected from the database appears near the
top right corner of the page.
By default, the Database Home page automatically refreshes every 60 seconds. You can prevent
automatic refresh by selecting Off in the Auto Refresh list at the top right-hand corner of the
page.
You must then click the Refresh icon to View the latest information.
3. Get a quick overview of the database state in the Status section, which includes the
following information:
. Up Time
Information about how long the database has been up
- Type
The database type. The type can be a single instance database (CDB or non—CDB) or an Oracle
RAC database (or cluster database).
If the database type is a CDB, the next line will identify the database as a CDB and specify the
number of PDBs in the CDB. The CDB (n PDBs) line is a link to the Containers page, which shows
a list of containers in the CDB (not including PDB$SEED), as well as status, performance, and
resource information about the containers.
. Version
The database version number
. Database name
The database name
. Instance name
The name of the database instance
. Platform name
The platform on which the database is running
- Host name
The name of the host system on which the database is running
. Thread
The redo log threads for the database
. Archiver
The status of the archiver process
4. View active session information in the Performance section. The Performance section shows
trend information for the past hour.
The Activity Class chart shows the average number of database sessions active for the past
hour. The chart shows the type of activity for each session (on CPU, waiting for I/O, or waiting
for another resource).
The Services chart shows the average number of database sessions active for the past hour for
database services.
For Oracle RAC, the Activity Class chart shows activity aggregated across all instances in the
cluster. Also, an Instances chart appears for Oracle RAC that shows Average Active Sessions per
instance.
5 . View resource utilization for the latest data point (the last minute) in the Resources section.
The Resources section includes the following information:
. Host CPU chart
This chart shows the percentage of CPU time used by the database instance and other
processes during the last minute. Place your cursor over the instance data to see the
percentage of CPU used by foreground and background instance processes.
If other processes are taking up most of your CPU time, then this indicates that some other
application running on the database host computer could be causing performance problems.
. Active Sessions chart
This chart shows the average number of active sessions during the last minute, broken out by
wait, user I/O, and CPU.
- Waits
This is the value for all wait classes combined, excluding user I/ O and idle wait events. Wait
classes are groupings of wait events based on the type of wait.
Go to the Performance Hub and click the Activity tab to view more information about waits.
. User I/O
This is the average number of active sessions waiting for user I/O. User I/ O means that the
workload originating from the user causes the database to read data from disk or write data to
disk.
Go to the Performance Hub and click the Summary tab to view more information about I/O.
- CPU
This is the average active sessions using CPU. Go to the Performance Hub and click the
Summary tab to view more information about CPU usage.
. Memory (GB)
This chart shows the current memory utilization (as of the latest refresh time) broken out by
the database shared pool, java pool, buffer cache, PGA, and other SGA components.
. Data Storage (GB)
This chart shows the current space usage (as of the latest refresh time) broken out by user
data, database log files, undo tablespaces, and temporary, SYSAUX, and SYSTEM tablespaces.
6. View SQL activity in the SQL Monitor section:
The table in this section displays information about monitored SQL statement executions. If
there is a green spinning icon in the Status column, then the monitored statement is still
running. If there is a check mark in the Status column, then the statement has completed its
execution.
SQL statements are monitored only if they have consumed at least 5 seconds of CPU or I/O
time.
For each SQL statement, the table provides information in the Status, Duration, SQL ID, Session
ID, Parallel, Database Time, and SQL Text columns.
Click a SQL ID to display the SQL Details page with more information about that SQL statement.
7. The Incidents — Last 24 Hours section displays a table that provides information about
database incidents that have occurred in the past 24 hours. The table has the Instance, Time,
Incident, Problem, and Error columns.
An incident is an occurrence of a critical error
in the database. Each incident in the Incidents —
Last 24 Hours table is recorded in the Automatic
Diagnostic Repository (ADR), a directory structure
located outside the database, which is available
for problem diagnosis even when the database is
down. You can investigate critical errors using the
ADR Command Interpreter (ADRCI) utility.
8. The Running jobs section displays a table
that shows database jobs that are currently
running. The table has the Instance, Owner,
Name, Elapsed, and Started columns.
Monitoring Performance Using the Performance Hub
The Performance Hub allows you to view all the perfor-
mance data available for a specified time period. Once a
time period is selected, the performance information is
collected and presented based on performance subject
areas.
When real-time data is selected, more granular data
is presented (because data points are available every
minute).
When historical data is selected, more detailed data
(broken down by different metrics) is presented, but
the data points are averaged out to the Automatic
Workload Repository (AWR) interval (usually an hour).
Different tabs are available in the Performance Hub,
depending on whether real-time or historical data is se-
lected for the time period.
The following table describes the Performance Hub
tabs, and indicates whether the tab is available when
real—time data is selected or historical data is selected,
or both.
Perfor-
mance Hub Available
Tab Name Description When
Summary The Summary tab pro- Real-time
vides an overall view of data or his—
the performance of the torical data
system for the specified is selected
time period. in the Select
When real-time data Time Period
for the last hour is Held for ª
displayed in the Perfor— non-CDB,
mance Hub page, this CDB' or PDB
tab shows a summary
of running processes,
memory allocation, databaseact1v1tyby
category, and I/O data
during the last hour.
When historical data is
displayed in the Perfor-
mance Hub page, this
tab shows a summary
of average active ses-
sion waits by category,
load profile per second,
active session activity,
host CPU usage by the
database instance and
other processes, I/O
read and write requests
per second, and mem—oryusagedurmgthe
selected time period.
RAC The RAC tab appears Real-time
only when EM Express data or his-
is being used with an torical data
Oracle RAC database is selected
(or cluster database). in the Select
When real-time data Time Period
is selected, this tab Hºld
shows global cache
activity information
and a breakdown of
activity (average active
sessions) and resource
usage (CPU, I/O, mem-
ory) per instance. When historical data
is selected, this tab
shows global cache
activity information
and a breakdown of
activity (average active
sessions) and resource
usage (CPU, I/O, mem-
ory) per instance dur—
ing the selected time
period.
Activity The Activity tab shows Real-time
Active Session History data or his-
(ASH) analytics. It al- torical data
lows detailed drilldown is selected
into average active ses— in the Select
SionsforASHoverthe___-_T-iiri-e-Pe-r-ib-dnm.
selected time period. field for a
This tab enables you to non-CDB,
select an average active CDB! or PDB
sessions dimension
and view the top activ-
ity for that dimension
for a selected time
period. For example,
you can View the SQL
statements and user
sessions that had the
top average active ses—
sions activity for the
selected time period.
Workload The workload proíile Real-time
chartsshowthe521?""""'ââããíiiíêlww
tern of user calls, parse torical data
calls, Redo Size and is selected
SQLªªNet over the last in the Select
60 minutes in real—time Time Period
mode. The Sessions íield for a
chart show the logon non—CDB,
rate, current logons CDB, or PDB
and open cursors.
Clicking a SQL_ID dis—
plays the SQL Details
page with more infor-
mation about that SQL
statement.
Monitored This tab enables you Real—time
SQL to view information data or his—"""""""'"""""""Zíáãiít"33535221"Ea?""íál'í'c'ã'i'ã'ããã"""'
statements that were is selected
executing or that com- in the Select
pleted during the se- Time Period
lected time period. field for a
The table displays in— non-CDB,
formation about mon- CDB: or PDB
itored SQL statement
executions. If there is
a green spinning icon
in the Status column,
then the monitored
statement did not
complete during the
selected time period.
A red cross indicates
that the SQL did not
completeeitherdue
to an error or due to
the session getting
terminated. If there is a
check mark in the Sta—
tus column, then the
statement completed
its execution during
the selected time
period.
SQL statements are
monitored only if they
have consumed at least
5 seconds of CPU or I/O
time.
You can view informa—
tion such as the status
of a statement, its dura-
tion, its type (SQL, PL/
SQL, or DBOP), its SQL
ID, its SQL plan hash,
the user who issued it,
whether it executed
as a serial or parallel
statement, the time
the database spent per—
forming CPU activity, I/
0, or other activity for
the statement, the read
and write requests and
bytes associated with
the statement, and the
start and end time for
the statement.
Click a SQL ID to
display the SQL Details
page with more infor—
mation about that SQL
statement.
ADDM The ADDM tab enables Real—time
you to view perfor— data or his—
mance findings and torical data
recommendations that is selected
have been found by in the Select
Automatic Database Time Period
Diagnostics Monitor field for a
(ADDM) for tasks per— non—CDB or
formed in the database CDB.
during the selected This tab is
time periºd. available for a PDB only
when a CDB
administra—Time enables you to view data is se-
wait events by category lected in the
for various metrics, Select Time
and to View time statis- Period field
tics for various metrics for a non-
for the selected time CDB, CDB, or
period. PDB.
Resources The Resources tab Historical
enables you to view data is se—
operating system re— lected in the
source usage statistics, Select Time
I/O resource usage Period field
statistics, and memory for a non-
usage statistics for the CDB, CDB, or
selected time period. PDB.
tor logs into System The System Statistics Historical
Statistics tab enables you to view data is se—
database statistics by lected in the
value, per transaction, Select Time
or per second for the se- Period field
lected time period. for a non-
CDB, CDB, or
PDB.
Containers The Containers tab Real-time
enables you to view data or his—
open time, active ses— torical data
sions, memory used, is selected
I/O requests, and I/O in the Select
throughput informa- Time Period
a CDB and
tion for the PDBs in the field for a
CDB. CDB.
The following figure shows the Performance Hub when
Real Time - Last Hour data is selected.
navigates
(drills down)
to the PDB.
The tab is
not available
when a PDB
administra-
tor is logged
directly into
the PDB.
Database The Database Time tab Historical
The following figure shows the Performance Hub when
historical data is selected.
To view Performance Hub data:
1. At the top of the Database Home page, from
the Performance menu, select Performance
Hub.
The Performance Hub page appears, with the
Summary tab displayed. By default, real time data
for the last hour appears in the Performance Hub.
You can select a different time period in the Select
Time Period field if you would like to view histor—
ical data in the Performance Hub instead of real-
time data.
In the figure above, Historical - All is selected in
the Select Time Period field.
2. The time picker appears below the Select
Time Period fleld.
The Shaded block area in the time picker identifles
the period of time for which performance statistics
are currently being displayed in the Performance
Hub. This is a subset of the period of time you se-
lected in the Select Time Period field.
When historical data is displayed in the Perfor-
mance Hub, you can increase or decrease the size of
the Shaded block area by clicking and dragging the
user control on either end of the Shaded block area.
The Shaded block area is the time period for which
statistics are displayed on all of the Performance
Hub tabs, not just on the currently selected tab.
3. Click any of the tabs that appear in the Perfor-
mance Hub to view the performance data on
the tab.
4. Click the Peerub Report button to generate
a Performance Hub active report, which will
include the contents of the Performance Hub
tabs in an HTML file. After you click Peerub
Report, you are prompted to choose one of
these report types for the Performance Hub
active report:
. Basic: The basic information for all the
tabs is saved in the report.
- Typical: All the information for the
basic report is saved. Also, the SQL
Monitor information for the top SQL
in the Monitored SQL tab is saved, and
ADDM reports are saved.
- All: All the information for the basic
report is saved. Also, the SQL Monitor
information for all of the SQL in the
Monitored SQL tab is saved (not just
for the top SQL), and all the detailed
reports in all the tabs are saved.
You are then prompted for a location and file name
for the active report, and the report is generated
in that file and location. You use a web browser to
view the report and navigate the Performance Hub
tabs in the report.
5. When historical data is selected in the
Performance Hub, you can click the AWR Re-
port button to generate an AWR report for the
selected time period.
You are prompted for a location and file name for
the AWR report, and the report is generated in that
file and location. You use a web browser to view the
report.
Specifying the Time Period for Which
to Display Statistics
In the Real-Time: Last Hour mode, the data in the Per—
formance Hub is sourced from Active Session History
(ASH). The ASH data is written to disk when the ASH
buffer is filled up or after 1 hour, and is stored as part of
the AWR framework.
By default, AWR has a retention period of 8 days. When
you view historical data in the Performance Hub, you
are viewing statistics collected as part of the hourly
snapshots in AWR.
You use the Select Time Period field in the Performance
Hub to determine the time periods for which statistics
are available for viewing. Because Oracle Database
statistics are stored in memory for one hour, the Real
Time — Last Hour option always appears in the Select
Time Period list.
The historical data options that are available in the
Select Time Period list change, depending on the time
period for which data is available in AWR, as shown in
the following table:
Time Period for
Which AWR Data is Historical Options in the
Available Select Time Period List
Less than 24 hours Historical — All
More than 24 hours, Historical — Day
but less than 7 days Historical _ All
Historical — Custom
7 days Historical — Day
Historical — Week
Historical – Custom
8 days or more Historical - Day
Historical — Week
Historical — Custom
After you choose a historical option from the Select
Time Period field, you use the time picker to specify the
time period for which data is displayed in the Perfor-
mance Hub tabs.
The following table describes the data displayed and
available for selection in the time picker when different
values are selected in the Select Time Period field for
the Performance Hub:
