Managing Expressly

Manage Oracle Database with Oracle Enterprise Manager Database Express, a built-in browser-based graphical tool.

By Arup Nanda

July/August 2014

How do you administer Oracle Database? Command-line tools such as SQL*Plus may be all you need in many situations, but you may also find a graphical tool useful in some cases, such as for displaying performance statistics.

Oracle Database 12c includes a built-in graphical management tool called Oracle Enterprise Manager Database Express. It’s a web-based tool you can launch from any web browser, and no operating-system-level installation is required. Oracle Enterprise Manager Database Express is extremely lightweight and easily configurable, and in this article, you will learn how to configure and use it to perform your daily DBA chores.

The first screen of Oracle Enterprise Manager Database Express asks you for authentication. Enter a database username and password. Remember, you need a database username. This is different from the standalone version of Oracle Enterprise Manager Cloud Control (formerly known as Oracle Enterprise Manager Grid Control), where you enter a username from the Oracle Enterprise Manager repository—not Oracle Database.

Oracle Enterprise Manager Database Express comes with two predefined roles—EM_EXPRESS_ALL and EM_EXPRESS_BASIC. The Oracle Database DBA role is already granted these two Oracle Enterprise Manager Database Express roles, so any user with a DBA role will not need to be granted these Oracle Enterprise Manager Database Express roles explicitly.

Enter a valid database username and password to see the database home page, as shown in Figure 1. The main menu—right below the Oracle Enterprise Manager Database Express 12c banner—displays five items. The first is the database name and the version, which is ANN1 and 12.1.0.1.0 in this case. From any other screen, click this item to return to the database home page.

Figure 1: Oracle Enterprise Manager Database Express home page

The next menu item—Configuration—displays four submenus:

• Initialization Parameters. Click this item to display and edit initialization parameters and their descriptions.
• Memory. Click this item to see memory allocations, including system global area (SGA) and program global area (PGA).
• Database Features Usage. Click this item to see database features, their descriptions, and when they were used. (High-water-mark analysis is also available through this item.)
• Current Database Properties. Click this item to see properties such as the character set and the time zone of the database.

The next menu item—Storage—displays five submenus:

• Tablespace. Click this item to see the list of tablespaces, datafiles, their sizes, and the free space inside them. (You can also create and alter existing tablespaces here.)
• Undo Management. Click this item to see how the undo mechanism is working in your database. (Two of the very useful graphically represented undo-related metrics here are the rate of generation and the space used.)
• Redo Log Groups. Click this item to see the online redo log files for the database. (It also enables you to add, drop, and alter redo log groups.)
• Archive Logs. Click this item to see the list of archived logs, when they were created, the system change number (SCN) of the changes contained in them, and more.
• Control Files. Click this item to see the control files for the database, the size and number of records in different sections of the control file, and more.

The next menu item—Security—enables you to perform database-security-related activities, such as managing users and roles. It displays three submenus:

• Users. Click this item to see the list of users and details such as status (active, locked, or password expired), default tablespace, when they were created, and expiration dates for passwords. (This item also enables you to alter users and create new ones.)
• Roles. Click this item to create, alter, and display database roles.
• Profiles. Click this item to create, alter, and display database profiles. (Profiles enable you to control how long the user session is allowed to remain active, control how many invalid login attempts are permitted before the account is locked, define the function to enforce the complexity of the password, and so on.)

The last menu item is Performance. (Note that you need to license the extra-cost Oracle Diagnostics Pack to use this menu item.) I will devote more attention to it here, because this is an item you will likely spend a lot of time on.

The Performance menu item includes two submenus. Let’s take the first one—Performance Hub—which displays a screen similar to the one shown in Figure 2. The topmost graph, called the time picker, shows the average active sessions across various points in time.

Figure 2: Performance Hub

You can drag the shadow box across the graph to focus on the activities in a particular time period. The information below the graph will change to reflect the activities in that period. The various color bands represent different activities—blue for user I/O, green for CPU, and so on. Place your cursor above a color band. A pop-up text box displays the metric that this bar represents and its value. A camera icon on the graph (at around 07:00 p.m. in Figure 2) indicates that an Automatic Database Diagnostic Monitor task was run at that time automatically (Automatic Database Diagnostic Monitor is a feature of Oracle Database). If you place your cursor above that icon, you will see the task ID, which enables you to check the details of that Automatic Database Diagnostic Monitor task later. You can check the details on that task in the ADDM tab on the screen shown in Figure 2.

The four boxes below the time picker are self-explanatory: the load average of the database host, memory, I/O, and the activities of the active sessions. These are all “active” displays—that is, if you move your cursor over a part of the diagram, you will see the name and the value of the metric it represents and the time it was collected. You can go back as far into the past as data is available in the Automatic Workload Repository feature of Oracle Database.

It is easier to see the power of these charts and graphs by examining them in the context of a real problem. Suppose you are investigating the cause of a database performance problem. From the time picker, you can see that there is a spike at 7:10:00 p.m., as shown in Figure 3. From the main menu, choose Performance and then from the tabs below the time picker, click Activity, which shows total activity and the associated wait classes. The section at the bottom left shows the SQL statements with the highest activity. The colored bar next to each SQL ID shows the breakdown of the wait classes for that activity. Place your cursor over the colored bar, and a pop-up box will show the name and the value of the metric.

Figure 3: Activity, as shown in Performance Hub

In the chart in Figure 4, you can clearly see that the first SQL ID—4sjngb1tsayy9—is responsible for most of the activity in the database. Your next diagnostic step is to find out why that SQL statement is so active. Click the 4sjngb1tsayy9 SQL ID to get the details on that SQL statement. That brings up the SQL Details screen shown in Figure 4. Click the Execution Statistics tab.

Figure 4: SQL Details screen

The SQL Details screen includes two types of output. At the top, in the Plans area, you can see the impact of the SQL statement represented through various metrics, including the database time, the number of I/O requests, the rows returned, the number of times the statement was parsed, and so on. If the SQL statement has had different execution plans, Oracle Enterprise Manager Database Express will grab the plans from all the sources—the cursor cache, the Automatic Workload Repository, and so on—and will display the statistics for all execution plans, one per line. The icon in the first column—labeled Source—shows the source of the information on the execution plan. Displaying details on all plans is very useful if you have seen different performance characteristics for the same SQL statement.

A very important metric—Buffer Gets, which shows the number of buffers fetched—is shown in the last column. This number in my example is 16,808—a significantly large number, which probably explains why this SQL statement had so much impact on performance. To understand why this SQL statement had so many buffer gets, look at the bottom part of the screen, in the Plan Details area, where the execution plan of the SQL statement is shown. Each step in the plan includes statistics such as the estimated number of rows and bytes, shown both as values and graphically. Note the HASH JOIN step—the Estimated Bytes value shows 18,447 petabytes! This is just an estimate; it may not be accurate, but it does give you an idea of the amount of data this SQL statement will fetch. Because the data is stored in buffers, the SQL statement fetches a large number of buffers—explaining the large buffer gets metric for this SQL.

Do you want to see if this SQL can be tuned to improve its performance? Oracle Enterprise Manager Database Express can help you do that. Click the Tune SQL button at the top of the SQL Details screen, just below the menu bar, to bring up the SQL Tuning Advisor feature of Oracle Database. (You can also invoke it from the main menu bar from the Performance tab by choosing the SQL Tuning Advisor submenu.) Clicking Tune SQL creates a task that may take some time to complete. When it completes, you will be presented with a screen that displays the results of the tuning effort, as shown in Figure 5.

Figure 5: SQL Tuning Advisor

The findings are shown in boxes toward the right of the screen. The first box, SQL Examined Count, shows a bar chart of the number of SQL statements examined for tuning. The blue area in the vertical bar shows the count of SQL statements where SQL Tuning Advisor has no findings, meaning that there are no tuning recommendations. If the green area in the vertical bar shows the count of SQL statements where SQL Tuning Advisor reported findings, it means that there are tuning recommendations. Recommendations might include implementing a SQL profile or restructuring (rewriting) the SQL. The last box shows the possible savings in database time if you implement the SQL profile suggested by SQL Tuning Advisor. The magnitude of the potential savings will help you decide whether the recommendations are worth exploring.

While you mull over the displayed potential savings resulting from implementation of the SQL profile suggested by SQL Tuning Advisor, you will naturally be curious to learn how SQL Tuning Advisor computed the savings. The SQL profile does not change the SQL; it changes only the execution plan. If you want to learn how the plan will be different after the SQL profile is applied, click the value for that SQL statement in the bottom box, Top SQL Statements, in the Id column. The details of the plan changes will appear, as shown in Figure 6. The tabs show Original Plan for the SQL statement and Plan Using SQL Profile—the modified plan. But the screen displays more than the modified plan. It also shows—graphically and as values—the estimated operation cost and the rows and bytes of data at each step of the execution plan. The display helps you visualize the step with the biggest performance impact and how that changes from the original to the modified plan.

Figure 6: Specific details of the recommendations

Happy with the modified plan? Just click the Implement button at the top of the screen. It will implement the SQL profile for that SQL statement, and the modified plan will be in effect. Don’t like the plan? Well, no harm done. The SQL profile is only a suggestion by SQL Tuning Advisor; it’s not implemented—unless you click Implement—and can be ignored. Repeat this SQL Tuning Advisor review for all of your high-impact SQL statements.

With Oracle Enterprise Manager Database Express and SQL Tuning Advisor, you can not only identify the high-impact SQL statements; you can also get recommendations on improving their performance quickly and compare the effectiveness of the suggestions with the original—all with just a few clicks.

Oracle Enterprise Manager Database Express also works with Oracle Real Application Clusters (Oracle RAC) databases. The Database home page, similar to the screen shown in Figure 7, shows all the database instances and details of each, such as the host the instance runs on, how long the instance has been up, and so on. A very useful display is the graphical representation of the CPU, memory, and active sessions on each of the instances. A visual examination of the display can quickly reveal whether the instances are equally loaded or skewed.

Figure 7: Oracle RAC database home page

Oracle Enterprise Manager Database Express also works on Oracle Multitenant databases. The database home page shows the details of the multitenant container database (CDB). However, the Performance panel at the right has a tab labeled PDBs that shows the pluggable databases (PDBs) running on the CDB, as shown in Figure 8. The Performance bar chart shows the division of various PDBs, differentiated by multiple colors.

Figure 8: Multitenant database home page

Oracle Enterprise Manager Database Express provides Oracle Database 12c DBAs an out-of-the-box graphical interface for common database management tasks. This article explored Oracle Enterprise Manager Database Express features, described basic tasks, and demonstrated how to troubleshoot common performance issues and use SQL Tuning Advisor to improve database performance.