There are several methods to monitor database performance, but EM is probably the most frequent used today. Oracle Enterprise Manager Cloud Control is designed to manage the entire Oracle environment: database, server, middleware, and more. OEM Cloud control requires additional licensing, however EM Express is provide free with Enterprise editions.
EM Express can be used to manage a single Oracle 12c database and it is actually built into the database. EM Express is a light weight management console and provides basic administrative tasks with minimal impact to database operations. EM Express has no background processes and it gathers information from already existing database stores. The Performance Hub of Express provides the following abilities:
- ASH Analytics.
- SQL Monitor
- Workload metrics.
- Resource usage
- See real-time and historic data.
- In historic mode AWR (Automatic Workload Repository) data is used.
- Performance Hub tabs are
- Summary – real-time mode
- Activity – ASH analytics both real-time and historic.
- Workload – info about Top SQL
- RAC – RAC specific metrics.
- Monitor SQL – current and historic SQL executions.
- ADDM – ADDM reports both real-time and historic
- Current ADDM findings – performance analysis of last 5 minutes.
Oracle 12c Automatic Diagnostic Repository (ADR) provides files to store diagnostic information about the health of the database. This release has added two new log files to add in administration of the RDBMS suite: DDL Log File and Debug Log File.
NEW DDL Log file – The new DDL log file has the same format and behavior as the alert log. When the ENABLE_DDL_LOGGING parameter is set to TRUE, DDL statements are written out to the DDL log file. Two log files are maintained: XML and plain text. Log files are stored in the /log/ddl directory of the ADR home and are included in IPS incident packages.
Debug Log – the debug log records unusual incidents that do not impact normal operations. They are occurrences which do not warrant an entry in alert log or incident report. The debug log has the same format as alert log.
Conventional ADDM uses AWR which are based on reports taken during intervals. Some changes will not appear on this conventional ADDM because changes where not great enough to trigger new AWR analysis. This is also not a real-time comparison of performance. ASH Active Session History is real-time and had the following characteristics:
- Active Session History is a collection of real-time data stored in the SGA based on active session
- Can be viewed via the V$ACTIVE_SESSION_HISTORY view.
- EM Cloud has the ability to run ASH reports from:
- HOME PAGE à Performance Menu/Home à Logon with admin privilege à Under Average Active Sessions click ASH Reports à Enter Start and Stop time of report à click Generate.
- Reports are placed under Run ASH Report Page.
- ASH Reports are divided into the sections
- Top Events – top wait events
- Load Profile – Describes load during report period.
- Top SQL – order by amount of resource consumed by SQL statements.
- Top Sessions – order by sessions waiting for wait events.
- Top DB Objects/Files/Latches – order by top objects being used/accessed.
- Activity Over Time – Shows picks and values in performance over reporting period.
- EM Cloud also includes new ASH Analytics page, providing graphical view of ASH data.