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?

V$SESSION
V$SESSION_WAIT
V$SYSTEM_EVENT
V$SYSSTAT

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?

SQL> desc v$advisor_progress
 Name                                      Null?    Type
 ----------------------------------------- -------- -----------
 SID                                                NUMBER
 SERIAL#                                            NUMBER
 USERNAME                                           VARCHAR2(30)
 OPNAME                                             VARCHAR2(64)
 ADVISOR_NAME                                       VARCHAR2(64)
 TASK_ID                                            NUMBER
 TARGET_DESC                                        VARCHAR2(32)
 SOFAR                                              NUMBER
 TOTALWORK                                          NUMBER
 UNITS                                              VARCHAR2(32)
 BENEFIT_SOFAR                                      NUMBER
 BENEFIT_MAX                                        NUMBER
 FINDINGS                                           NUMBER
 RECOMMENDATIONS                                    NUMBER
 TIME_REMAINING                                     NUMBER
 START_TIME                                         DATE
 LAST_UPDATE_TIME                                   DATE
 ELAPSED_SECONDS                                    NUMBER
 ADVISOR_METRIC1                                    NUMBER
 METRIC1_DESC                                       VARCHAR2(64)

• 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.

select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session 
where username = 'HR'

       SID    SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
       196      60946 DISABLED FALSE FALSE

begin
   dbms_monitor.session_trace_enable (
      session_id   => 196,
      serial_num   => 60960,
      waits        => true,
      binds        => false
   );
end;
/

select sid, serial#, sql_trace, sql_trace_waits, sql_trace_binds
from v$session 
where username = 'HR'

       SID    SERIAL# SQL_TRAC SQL_T SQL_T
---------- ---------- -------- ----- -----
       196      60960 ENABLED  TRUE  FALSE

SELECT *
  FROM (SELECT SID, 'SESSION_TRACE' trace_type
          FROM v$session
         WHERE sql_trace = 'ENABLED')
UNION
(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)
UNION
(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))
UNION
(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))
UNION
(SELECT SID, t.trace_type
   FROM v$session s, dba_enabled_traces t, v$instance i
  WHERE t.trace_type = 'SERVICE_MODULE_ACTION'
    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))
UNION
(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))

       SID TRACE_TYPE
---------- ---------------------
       136 SERVICE_MODULE
       136 SERVICE_MODULE_ACTION

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.