Oracle Database 10g: Top Features for DBAs
Release 2 Features Addendum

Oracle ACE Arup Nanda presents his list of the top new Oracle Database 10g Release 2 features for database administrators

Part 3: Performance Features

Memory-attached SGA query (Arup's favorite Release 2 feature) tops this list, but optimizer statistics management, the new "compare periods" report, and other new features are equally compelling.

Covered in This Installment:
· Hung But Not Paralyzed: Memory-Attached SGA Query
· Interruptible SQL Access Advisor
· Check for Tracing Enabled
· Activity Session History
· Optimizer Statistics Management
Compare Periods Report

Hung But Not Paralyzed: Memory-Attached SGA Query

Let's assume that you use Oracle Enterprise Manager to diagnose and solve performance issues. One day, a nasty issue arises: a badly designed application is causing serious library-cache lock issues and the database appears to be hung. You have to quickly identify the culprit sessions and kill them quickly.

You could bring up Oracle Enterprise Manager to diagnose this issue. But, wait! If the entire database is saturated with hung sessions, wouldn't the query from Oracle Enterprise Manager hang as well?

With Oracle Database 10g Release 2, the answer is "no." As I explained in Part 2, in this release, a "Monitor in Memory Access Mode" option allows Enterprise Manager to select the sessions directly from SGA memory, rather than from V$SESSION. Therefore, because the SQL layer is bypassed in this mode, a hung database does not prevent this query from executing. Instead, the query is issued automatically.

Let's see how this works. On the Enterprise Manager screen, choose the Performance tab and scroll down to the bottom of the page, to the section labeled "Additional Monitoring Links," which brings up a screen similar to that below.

Note the hyperlink called "Hang Analysis," shown within a red oval. Clicking on the ink brings up a screen similar to that below.

Here you see a map of the various frozen sessions. In this example, you can see that the session with SID 193, the root session, has blocked the other two sessions, 192 and 214. The color of the sessions in the map indicates how long the sessions may have been blocked. You can click on the SIDs to get more information, which brings you to the Session Details screen.

Remember the ORADEBUG utility? Oracle Enterprise Manager gets the data about system hangs using the same utility. When you enable the SGA direct attach, Oracle uses a single SQL collector per instance. This collector starts automatically along with Enterprise Manager. Data from the following views are retrieved:

Memory-attached SGA query is a very powerful feature that may well save your skin some day. We are all too familiar with applications bringing the database to its knees, and then being asked to explain why. Now, you can provide an answer. This feature gets my vote as the most useful Release 2 one for DBAs.

Interruptible SQL Access Advisor

You may be familiar with the SQL Access Advisor in Oracle Database 10g. Essentially, it offers you an automated approach for tuning SQL workloads by identifying indexes and materialized views that will improve SQL performance.

But consider this situation: You are encountering some performance problems and want to run the SQL Access Advisor on a group of SQL statements. To get a more accurate analysis, you have selected the "Comprehensive mode" option. And then, you wait.

If the SQL workload is large—comprising several hundred statements—and the SQL statements are complex, your wait time may be long. In the meantime, the user is breathing down your neck to get an answer. What can you do?

In Oracle Database 10g Release 2, you can simply interrupt the advisor and view the recommendations or findings produced so far. This capability was available with the SQL Tuning Advisor in Release 1 and now has been extended to the SQL Access Advisor.

Let's see how. From the screen Advisor Central, click on the SQL Access Advisor link.

Choose the "Interrupt" option from the drop-down list on the right-hand side next to the caption "Actions" and press the Go button. This command will interrupt the SQL Access Advisor and let you see the recommendations immediately. Of course, the recommendations will not be a complete set, but they may be sufficient for satisfying the users' need in most cases.

If you are using the command-line version of the SQL Access Advisor, not Oracle Enterprise Manager, can you still see how much is done? Of course, you can—#with the new view V$ADVISOR_PROGRESS.

SQL> desc v$advisor_progress
Name Null? Type
----------------------------------------- -------- -----------
Here the columns TOTALWORK and SOFAR show how much work has been done as well as the total work, similar to what you can see from V$SESSION_LONGOPS view.

Check for Tracing Enabled

If a session is not doing what it is supposed to do, or is doing it slowly, the first step for most DBAs is to check the wait events. To build a profile, you may also want to trace the session over an extended period, which produces a trace file in the user_dump_dest directory.

Now, imagine that you have been using end-to-end tracing on several sessions for some time but now you have no idea which sessions have tracing turned on. How do you find out?

One way is to sift through the myriad trace files to extract the SID and Serial# columns and match them in the database in V$SESSION view. Needless to say, this process is complex, difficult, and error-prone. But in Oracle Database 10g Release 2, a more elegant, much easier approach is available: All you have to do is to check a view you check anyway, V$SESSION.

Three new columns now show the status of tracing:

  • sql_trace—Shows (TRUE/FALSE) if SQL tracing has been enabled in the session
  • sql_trace_waits—If session tracing is enabled, you can have the trace write wait information to the trace file; very useful in diagnosing performance issues.
  • sql_trace_binds—If the session uses bind variables, you can have the trace write the bind variable values to the trace file. This column shows TRUE/FALSE.
When tracing in the session is not turned on, if you select these columns:
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session
where username = 'HR'
The output is:
---------- ---------- -------- ----- -----
Here you can see that tracing is not enabled in the session with SID 196 and Serial# 60946.

Now, you can enable tracing of wait events, but not of bind variables. You can use the package dbms_monitor to enable tracing.

dbms_monitor.session_trace_enable (
session_id => 196,
serial_num => 60960,
waits => true,
binds => false
Now if you want to see the session information:
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session
where username = 'HR'
The output is:
---------- ---------- -------- ----- -----
Note that the view V$SESSION is populated only if the procedure session_trace_enable in the package dbms_monitor is used to enable tracing, not by alter session set sql_trace = true or setting the event 10046. At some point later in time, if you want to find out which sessions have been enabled for tracing, you can do so using the above query.

If you have enabled the trace using the other procedures in the package dbms_monitor—such as SERV_MOD_ACT_TRACE_ENABLE or CLIENT_ID_TRACE_ENABLE—the V$SESSION view will not show that information. Instead, they are recorded in a different view, DBA_ENABLED_TRACES. You can join this view with other relevant stores of information to see the sessions where trace is enabled. For example, with

FROM v$session
WHERE sql_trace = 'ENABLED')
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t
WHERE t.trace_type = 'CLIENT_ID' AND s.client_identifier = t.primary_id)
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'SERVICE'
AND s.service_name = t.primary_id
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'SERVICE_MODULE'
AND s.service_name = t.primary_id
AND s.module = t.qualifier_id1
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
AND s.service_name = t.primary_id
AND s.module = t.qualifier_id1
AND s.action = t.qualifier_id2
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
(SELECT SID, t.trace_type
FROM v$session s, dba_enabled_traces t, v$instance i
WHERE t.trace_type = 'DATABASE'
AND (t.instance_name IS NULL OR t.instance_name = i.instance_name))
The output is:
---------- ---------------------
As you can see, you have enabled trace for the Service Module and Service Module Action for the session 136. DBA_ENABLED_TRACES does not show bind variables or wait events however.

Activity Session History

By now you must understand how important and useful the Automatic Workload Repository (AWR) is. (Please read up on AWR if you need to.) As a recap, AWR captures workload-related performance data at the user and system levels, including performance statistics by different dimensions, metrics, OS statistics, and ASH data at regular predetermined intervals.

Activity Session History (ASH) represents the history of the activities of all recent active sessions captured efficiently through a circular buffer in memory and efficiently written to AWR to incur minimal overhead. The ASH data can be rolled up by different dimensions: TOP SQL, object, file, session, module, action, and so on.

However, most DBAs are commonly asked to diagnose transient performance problems. To diagnose such problems, Oracle Database 10g Release 2 introduces the ASH report. The ASH report can be used to target the entire database or a particular session, SQL_ID, module, action, or a combination of these dimensions.

One way to access the ASH report is from the Database page. Choosing the Performance tab will generate a screen similar to the following.

Note the button (inside the red oval) labeled "Run ASH Report." Clicking on it brings up the Active Session History report:

This screen allows you to put the date and time of the start and finish times of the period in which you're interested. Enter the date and time as needed and press the "Generate Report" button on the upper right. By default the date and time shows a 5-minute interval.

After you click the button, you will see the ASH report on the screen for that period. If you look carefully, you will see that the report resembles the STASPACK report; but since it comes from AWR data, the metrics in them are much more useful. A small portion of the screen is shown below:

You can save the report to a file for later viewing by pressing the button "Save to File."

Note the links in the section "ASH Report." Here you can see the different types of available performance-related statistics and metrics in one glance. For instance, you can see Top Events during the period only by clicking on that link. If performance issues come up within the period, this information will help substantially. You can generally identify bottlenecks that caused the transient spikes by looking at skews along the various dimensions listed in the ASH report.

Remember, this report is pulled from data collected by AWR or from in-memory buffers as appropriate; hence, if you want to diagnose a performance issue that occurred earlier, you could simply fire up the ASH report for that period and see any issues that might have surfaced then.

The ASH report can also be run through command line, by running the Oracle supplied SQL script located in $OH/rdbms/admin/ashrpt.sql.

Optimizer Statistics Management

Oracle Database 10g offers several very useful features for managing optimizer statistics, such as one for locking down statistics to prevent subsequent overwriting. These features make the task of collecting and managing optimizer statistics a breeze. And in Oracle Database 10g Release 2, you can do that using Oracle Enterprise Manager.

From the Database home page click on the Administration tab. Go down to the section titled "Statistics Management," where you see the Manage Optimizer Statistics link as shown below.

Clicking on the hyperlink brings you to the next screen: the Manage Optimizer Statistics page.

From this screen you can use the hyperlinks on the right-hand side to perform various stats-related tasks. For example, using the Configure button, you can easily configure a different time for a job run by choosing a new Window.

One particularly useful feature is the Statistics Options link under "Related Links." Clicking on it will bring this screen up:

from which you can do many useful tasks such as changing the default value of parallelism and estimating percentages.

Compare Periods Report

Imagine this scenario. You have just been called to an emergency meeting with the business and applications teams. The reason is all too obvious: the database is slow. (Is there ever any other reason?) Anyway, here's the scoop, says the development tech lead: the batch program run last night between 1 a.m. and 3 a.m. was very slow. It always runs at that time for about 30 minutes, but last night it took two hours. And, as if on cue, the business team lead declares: "The company suffered a potential revenue loss."

"Were there any recent changes?", you ask. "Nope, not a thing," is the oh-so-swift reply from the development tech lead. ("Yeah, right," you think.)

Sound familiar? If you have been on the production support hot seat for even a tenth as long as I have, you're nodding in agreement right now. What do you do?

Fortunately, you have Oracle Database 10g Release 2 and fire up the Snapshot or Time Periods comparison in Oracle Enterprise Manager. Using this feature, you can see the metric changes between two time intervals, not just two points in time. For instance, in this case, you could ask to see the snapshot changes between 1 a.m. and 3 a.m. last night and the same for the previous day. If the batch process was running fine the previous day, and not last night, the snapshot changes will give you a big clue.

Here's how it would work: Pull up Oracle Enterprise Manager and go to the Performance tab. Toward the bottom of the page, you will see the section "Additional Monitoring Links." In that group of links, look for "Snapshots." Clicking on that will bring up a screen similar to that shown below.

Note the drop-down box inside the red oval. Choose "Compare Periods" and press the "Go" button. This will bring up a screen to choose the end of the first snapshot period as shown below:

As shown in the box within the red oval, choose the approximate time and date of the period you want to examine. You are interested in the period between 1 a.m. and 3 a.m., so choose 3 a.m. Press the Next button to go the next screen.

Choose the Select Beginning Snapshot option as shown in the picture within a red oval. It will display the list of snapshots available. Choose the date and time to show 1 a.m. From the resultant display, choose the radio button that corresponds to 1 a.m, which happens to be 248 in this example. Click on "Next."

Repeat the same steps for the second period, 1-3 a.m. on April 22. Finally, press the "Finish" button. The side-by-side analysis of the metrics captured and calculated in both periods comes up. The next figure shows the first half of the analysis screen:

Note how you can see the start and end times of each period. The first column shows different metrics either collected or computed from others; the next columns show how these metrics look in each period. These metrics give you the clues you were looking for to find the discrepancies in performance.

You can make it easier by clicking on the Second Period Rate Per Second column, which sorts its output. Start with the highest to lowest sorting, which shows the worst values at the top. In your example, suppose it says that the physical reads were much higher in the second period. There you go: Now you know why the elapsed time was high. To know what caused the physical reads to go up you cam pull up a report of all the activities and all the metrics, similar to the AWR report—but as a comparative one, not for a specific period. Click on the Report tab and you will see the report similar to the following screen:

Scroll down or search for the SQL Statistics section, which shows a menu like the following:

Here the SQL statements that caused the physical reads to go up can be explored. Click on the link "Top 10 SQL Comparison by Physical Reads" and it will show you a grid of all the SQL IDs and the corresponding physical reads. You can drill down further by clicking on the SQL ID.

Of course, this is merely an example case. Regardless of the issue, you can easily drill down to find the exact reason performance differed in two periods by doing a comparative analysis. The report also lists all the changes in the database configuration settings for the two time periods being compared. Also, as all the metrics and statistics are appropriately normalized by time for the two time periods being compared, they need not be of the same duration.

In Part 4, I'll cover data warehousing and integration features.

Back to Series Index