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?

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
                                


 ----------------------------------------- -------- -----------
                                


 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)
                                


                              

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.

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
                                


                              

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

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
                                


                              

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 *
                                


  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
                                


                              

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.

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.