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:
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.
V$SESSIONMemory-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.
SQL> desc v$advisor_progressHere 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:
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_bindsThe output is:
SID SERIAL# SQL_TRAC SQL_T SQL_THere 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.
beginNow if you want to see the session information:
select sid, serial#, sql_trace, sql_trace_waits, sql_trace_bindsThe output is:
SID SERIAL# SQL_TRAC SQL_T SQL_TNote 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
SELECT *The output is:
SID TRACE_TYPEAs 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.
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.
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.
In Part 4, I'll cover data warehousing and integration features.
Back to Series Index