Enterprise Management (EM) Performance, Administration and Maintenance

Oracle12x Overview

In the first KB in this series we examined the EM major features and the EM Home tab, we will now review the Performance, Administration, and Maintenance components of the 12x as they are provided in the form of web tabs for pages in a web browser.

EM Performance Tab

On the performance tab, the Oracle 12x provides information to help identify the causes of potential problems with the database. Database access information, such as top sessions and top SQL statements, is provided as a means to run other tools to generate performance and diagnostics reporting, along with links to other monitoring tools. The performance report screen indicates both good and bad performance areas and allows further investigation via the provided drilldown links.

The performance screen provides information on the following categories: Host, Average Active Sessions, Instance Disk I/O, Physical Reads, Physical Writes, Other Reads, Other Writes, and Instance Throughput.

Host – The chart shows potential problems outside the database. The load average is a moving average of the run queue length, which indicates the level of contention for CPU time. If the load average isn’t available on platforms such as Windows, CPU Utilization will be displayed.

If you have selected Memory Access mode, the Host chart initially does not display historical data, unless the screen was recently loaded, and cached historical data is still available. Host data is cached starting from the time the screen was first accessed, and it gradually fills the history on the Host chart.

High values, for example 85% to 100%, may indicate that too many users are waiting in line for CPU time. Compare the values with those of CPU used in the Active Sessions chart. A low sessions value and high run queue length value indicates that something else on the host, other than your database, is consuming the CPU.

Average Active Sessions – The chart shows potential problems inside the database. Categories, called wait classes, show how much of the database is waiting for a resource, such as CPU or disk I/O. The chart displays the load on the instance and identifies bottlenecks in performance.

Click on one of the wait class legends to the right of the chart to go to the Active Sessions Waiting page.

The Session Count is computed by categorizing the time that all active sessions consumed in the last sampling interval into different wait classes, by summing the amount in each wait class, and by dividing it by the sampling interval. For example, If there are 3 active sessions in the last 15-second sampling interval, and each session spent half of the time (7.5 seconds each) on CPU and half of the time idling, then 3 x 7.5 / 15 = 1.5 active sessions on CPU appear for that sampling interval.

Compare the peaks on the Average Active Sessions chart with those on the Instance Throughput charts. If the Average Active Sessions chart displays a large number of sessions waiting, indicating internal contention, but throughput is high, then the situation may be acceptable. The database is probably also performing efficiently if internal contention is low, but throughput is high. However, if internal contention is high but throughput is low, then consider tuning the database.

Top Activity – Reports the statistics for Active Sessions, Top 10 SQL statements, Top 10 Sessions, Services, Modules, Actions, Files, and Top Objects.

Instance Disk I/O – The chart shows the total requests, which represent the rate that the database instance is issuing read/write requests. An increase in other reads and other writes indicates a backup, archiving, or file transfer activity is occurring in the database. Physical reads and writes correspond to the data block reads and writes in the database.

The following information describes the reads and writes from the database instance for the Instance Disk I/O chart:

• Other Writes (KB) – All writes excluding data block writes. This includes writing to the redo log, archive log, backup, control file, and flash back log.
• Physical Writes (KB) – Data block writes issued from the instance. This includes datablock writes issued by the database writer (DBWR) process from buffer cache and writes issued by foregrounds as part of direct load operations.
• Other Reads (KB) – All reads excluding data block reads. This includes reading from the redo log, archive log, backup, control file, and flash back log.
• Physical Reads (KB) – Data block reads issued from the instance. These are data file reads issued by foregrounds to read the blocks into buffer cache or data file reads by foregrounds for direct reads (query operations).
• Instance Throughput – This area contains two charts Per Second and Per transaction.

Both charts indicate how much work the database is performing on behalf of the user. The Per Second view is for databases that handle SQL queries, shown as Physical Reads in the bottom chart. The Per Transaction view is for databases that handle transactions, shown as Transactions in the top chart and Redo Size in the bottom chart. Logons show how many people are logged onto the database per second.

EM Administration Tab

On the administration tab, the Oracle 12x provides information to allow the configuration and tuning aspects of the database in order to adjust different parameter settings for better performance and overall system improvements.

You can find information about the following categories: Storage, Database Configuration, Database Scheduler, Statistics Management, Change Database, Resource Manager, Policies, Database Objects, Programs, XML Database, Users & Privileges, Materialized Views, BI & OLAP, User Defined Types, Change Management, and Enterprise Management Administration.

Control Files – Provides information about database control files (Valid, File Name, and File Directory) and makes recommendations such as the need for multiple control files on different disk spindles.

Tablespaces – Provides information about the Name, Size (MB), Used (MB), Used (%), Free (MB), Status, Datafiles, Type, Extent Management, and Segment Management for every one of the Tablespaces.

Temporary Tablespace Groups – Provides a search mechanism by Object Name in order to report by Name the number of temporary tablespaces, Total Size (MB), and Default.

Datafiles – Provides a report of all Datafiles and includes File Name, Tablespace, Status, Size (MB), Used (MB), and Used (%).

Rollback Segments – Provides a report that includes the Name, Status, Tablespace, number of Extents, Size (MB), number of Wraps, number of Shrinks, High Water Mark (MB), and the number of Active Transactions for every Rollback Segment associated with the database.

Redo Log Groups – Provides a report that includes the Group, Status, number of Members, if it is Archived, Size (KB), current Sequence, and the First Changed number for all Redo Log Groups.

Archive Logs – Provides a report that includes the Name, Sequence, Thread, First Change #, First Time, and Archival Time for the database Archive Logs.

Memory parameters – Reports of the allocation history, current allocation, and maximum size for the SGA[1] and the PGA[2]

Undo Management – Report on the Undo Retention Settings, current Undo tablespace, Recommendations, System activity, and tablespace usage.

Initialization Parameters – Report of the Name, Revision, Value, Comments, Type, Basic, Modified, Dynamic, and Category for both the current Oracle parameters and the same in the SPFILE.

Database Feature Usage – Report by Name, Currently Used, Detected Usage, Total Samples, First and Last Usage, along with the Database Version of how often every database feature is used.

Scheduler Jobs – Report of the Name, Owner, Scheduled Date, Last Run Date, Status of all scheduler Jobs. Chains – Report of the status of all scheduler chains. Schedules – Report of the status of all scheduler schedules. Programs – Report of programs that define what are to be executed in the jobs. Job Classes – Report of the Name, Logging Level, Log Retention Period (Days), Resource Consumer Group, Service Name, and Description of all the Scheduler Job Classes[3]. Windows – report of all system windows that specify database resource usage limits based on time intervals. Window Groups – Name, Enabled, and number of Members for all Scheduler Windows Groups. For example, the MAINTENANCE Window Group could have the two members: WEEKNIGHT and WEEKEND, each specifying the day, hour, minute, and second of the start and duration of the maintenance period. Global Attributes – Includes the Name, Value, and Description of all Schedule Global Attributes, A few examples are DEFAULT_TIMEZONE, LOG_HISTORY, and MAX_JOB_SLAVE_PROCESSES.

Statistics Management: Automatic Workload Repository (AWR) – Report of the database statistics stored in the AWR. Also, provides the ability to Edit the settings for AWR in terms of retention period (Days), snapshot collection interval, and collection level (BASIC, TYPICAL, or ALL). Manage Optimizer Statistics – Optimizer Statistics are used by the query optimizer to choose the best execution plan for each SQL statement. Up-to-date optimizer statistics can greatly improve the performance of SQL statements. This allows the configuration of the SYS.GATHER_STATS_JOB, which updates the optimizer statistics.

Change Database:  Migrate to ASM – Allows the creation of an ASM instance and the management of the same by the EM. You can choose to migrate database files, recovery-related files, and the persistent initialization parameter file (spfile). The Migrate Database To ASM job uses RMAN to convert your database to ASM. Make Tablespace Locally Managed – Allows the selection of all dictionary managed tablespaces to be converted to locally managed.

Resource Manager: Monitors – Ability to provide configuration setting for resource monitoring reports. Also, a graphical report is provided with the amount of CPU time consumed, along with total CPU Wait time. Consumer Groups – report of the various consumer groups and their associated Consumer Group Mappings along with resources for the various Plans.

Policies: Policy Library – Report by Priority, Police Rule, Category, Target Type, and Description of all Policies in the library. Example policies are Default passwords, Unlimited login attempts, and Remote OS authentication. Policy Violations – Reports existing policy violations.

The Schema section of the Administration tab contains information about the administration and configuration of the Database Objects, Programs, XML Database, Users & Privileges, Materialized Views, BI & OLAP, and the User Defined Types

In the Database Objects section,the Tables provide reports on the Schema, Table Name, Tablespace, Partitioned, number of Rows, and Last Analyzed for all Tables in the database.

Indexes – Reports on the Table Owner, Table name, Indexed Columns, Index Owner, Index Name, Table Type, tablespace, Partitioned, and Last Analyzed for all Indexes in the database.

Views – Reports on the Schema, View Name, and Status of all database Views.

Synonyms – Reports on the Schema, Synonym, Object Schema, Ref. Object Name and Link for all Synonyms in the database.

Sequences – Reports on the Schema, Sequence Name, Minimum Value, Maximum Value, Interval, Cycle, Order, and Cache of all Sequences.

Database Links – Reports on the Schema, Database Links, User Name, Service Name, and date Created of all Database Links in the database.

Directory Objects – Lists the Name, and Path of all Directory Objects in the database.

Reorganize Objects – Ability to reorganize [4]individual objects, an entire schema or an entire tablespace.

The Enterprise Manager Administration section of the Administration tab contains pointers regarding the creating, editing, views administrators, and their management tasks such as Blackouts[5] along with their notification schedules.

EM Maintenance Tab

The maintenance tab of Oracle 12x provides information allowing the execution of tasks related to importing and exporting data in the database, and collecting and deleting statistics related to the performance of SQL queries.

The maintenance screen provides information about the following categories: Backup/Recovery, Backup/Recovery Settings, Move Row Data, Move Database Files, Streams, Install Database Software, and Database Software Patching.

Schedule Backup – Provides the ability to backup the contents of the database to disk, tape, or both.

Perform Recovery – Provides the ability to restore or recover a database, tablespaces, datafiles, archived logs or to flashback tables or undrop objects.

Manage Current Backups – Search for and display a list of backup sets or backup copies, and perform management operations such as crosschecks and deletions on selected copies, sets, or files.

Manage Restore Points – Ability to create or delete defined restoration points to which you can revert to the database at a future time.

Backup Reports – Display the backup jobs that are known to the database.

Backup Settings – Ability to configure the settings for the Backup utility.

Recovery Settings – Ability to configure the settings for the Recovery utility.

Recovery Catalog Settings – Ability to register the current database with a recovery catalog.

Export to Export File – Means to export the contents of a database, objects within user’s schemas, and tables.

Import from Export File – Means to import the contents of objects and tables.

Import from Database – Means to import the contents of a database.

Load Data from User Files – Means to load data from a non-Oracle database into an Oracle database.

Monitor Import and Export Jobs – Monitor the status of current and completed Import and Export jobs.

Transport Tablespaces – Ability to move a subset of an Oracle database from one Oracle database to another, including across different platforms.

Clone Database – Means to duplicate the current database by performing a backup and then transferring the current database to a different destination.

Streams Setup – Configure the setup options for Streams.

Streams Management – Manage Streams, which provides the capabilities required to build and operate distributed enterprises and applications, data warehouses, and high availability solutions.

Collection Status – View the last collected configuration for your database. You can compare the configuration to another database instance or to multiple instances at once (in a submitted job).

Apply Patch – Means to simplify the patching of software on any version of the Oracle database running in your enterprise.

View Patch Cache – View a list of patches that have been downloaded from Oracle MetaLink to the Enterprise Manager repository. The patch cache allows a patch to be staged to multiple destinations, but only downloaded once.

[1] The System Global Area (SGA) is a portion of the main memory on a database server system that is allocated by an Oracle instance and is shared among all the Oracle processes. It consists of the memory structures for the Shared Pool, Database Buffer Cache, Redo Log Buffer, Large Pool, Java Pool, Streams Pool and of other structures required for lock and latch management. The SGA is allocated in memory when an Oracle database instance is started.

[2] The Program or Process Global Area (PGA) is a memory structure that contains data and control information for the server processes. A PGA is created by Oracle when a server process is started.

[3] A job class defines the resource consumer group in which a job will run. Using a resource plan in a window, a DBA can allocate resources among different resource groups and between different job classes.

[4] Reorganization is necessary for rebuilding indexes that are fragmented, rebuilding tables that are fragmented, and relocating objects to another tablespace, and recreating objects with optimal storage attributes.

[5] Blackouts allow the suspension of the database monitoring activities so you can perform database maintenance operations.