A
Using Advanced Event Tests Effectively

Oracle Advanced Event Tests are integrated into the Oracle Enterprise Manager Console's Event Management System. They are installed as part of the Diagnostics pack and provide DBAs and database users with additional resources to monitor event conditions in their network environment.

This appendix demonstrates how the Oracle Event Management System and Advanced Event Tests can help DBAs simplify their day to day administrative tasks, enabling them to perform tasks that can provide even greater benefit to the company.

In our examples, the DBA uses events in three different scenarios to proactively monitor potential problems. Proactive management allows the DBA to take action to resolve the issue before the problem occurs, minimize unscheduled downtime, and prevent slow response time.

Each of the scenarios provides a description of the problem, a step-by-step preventative action plan, and a resolution summary. The following management tasks are addressed in the scenarios:

• Operational Management
  • The DBA wants to automate his routine monitoring tasks so he can spend his time addressing performance tuning and participating in system design.
• Space Management
  • The DBA wants to ensure a night time batch load does not fail due to insufficient tablespace free space.
• Performance Tuning
  • The DBA wants to ensure there is no performance degradation once a new SQL intensive application is brought online.

    Note: For step-by-step instructions on using the Oracle Enterprise Manager Console and Event Management System, please see the Oracle Enterprise Manager Administrator's Guide. This guide also provides information on setting up job system and event requirements.

Operational Management

Problem

The DBA realizes that most of his time is spent performing routine, time-consuming tasks which focus on the operational aspect of keeping various databases up and running. To reduce the labor of system management across numerous managed databases, he uses the Oracle Event Management System. The Event System will allow him to proactively monitor the systems for which he is responsible.

He decides to divide his tasks into three different groups (depending on the frequency at which these tasks should be performed):

• Items that should be monitored on a continuous basis
• Items that should be monitored on a daily basis
• Items that should be monitored on a weekly basis

Action

1. Create an event which will monitor the items that should be monitored continuously by following these steps:
   1. Enter the name of the new event, Continuous, in the General tab of the Create Event page. Choose Database as the Target Type. Add one or more targets where the monitoring should take place for the event.
   2. Select the following event tests from the Available Tests column in the Tests tab of the Create Event page:

      Fault:

      Alert-- new errors in Alert file

      Database UpDown-- database is down

      Probe -- database connection cannot be made

      User Blocks -- database user is blocking others

      Performance - Instance Activity:

      Buffer Cache Hit %-- buffer cache hit ratio is low

      Free Buffer Waits -- free buffer waits is increasing

      In Memory Sort % -- in memory sort ratio is low

      Redo Log Allocation Hit % -- redo log allocation hit ratio is low

      Rollback Contention -- rollback segment contention is high

      Space:

      Archive Full -- archive device is full

      Archive Full (%) -- archive device is full shown as a percentage

   3. In the Parameters tab of the Create Event page, accept the Critical and Warning threshold settings.
2. Create an event which will monitor the items that should be monitored on a daily basis by following these steps:
   1. Enter the name of the new event, Daily, in the General tab of the Create Event page. Choose Database as the Target Type. Add one or more targets where the monitoring should take place for the event.
   2. Select the following event tests from the Available Tests column in the Tests tab of the Create Event page:

      Space:

      Chunk Small-- contiguous space is insufficient

      Dump Full -- dump destination device is full

      Dump Full (%)-- dump destination device is full shown as a percentage

      Fast Segment Growth-- segment growth rate is high

      Maximum Extents -- segment's maxextent limit approached

      Tablespace Full -- tablespace is full

   3. In the Parameters tab of the Create Event page, accept all default settings.
   4. In the Schedule tab of the Create Event page, select the On Interval option and set the interval to be 24 hours.
3. Create an event which will monitor the items that should be monitored on a weekly basis by following these steps:
   1. Enter the name of the new event, Weekly, in the General tab of the Create Event page. Choose Database as the Target Type. Add one or more targets where the monitoring should take place for the event.
   2. Select the following event tests from the Available Tests column in the Tests tab of the Create Event page.

      Resource:

      Datafile Limit -- maximum datafile approached

      Space:

      Alert File Large -- alert file is large

      Index Rebuild-- index which may benefit from a rebuild

   3. In the Parameters tab of the Create Event page, accept all default settings.
   4. In the Schedule tab of the Create Event page, select the On Day of Week option and set the date and time.

Resolution

The appropriate resolution depends on the specific event condition. Each event description provides a possible user action to correct your condition.

In addition, the DBA can use other Oracle Enterprise Manager tools to further pinpoint database inefficiencies and tune performance. Please refer to the Oracle Enterprise Manager documentation library for more information on the individual tools.

Table 7-3 Summary of Event Tests Used in Operational Management

Fault	Performance - Instance Activity	Space	Resource
Alert Database UpDown Probe User Blocks	Buffer Cache Hit % Free Buffer Waits In Memory Sort % Redo Log Allocation Hit % Rollback Contention	Alert File Large Archive Full Archive Full (%) Chunk Small Dump Full Dump Full (%) Fast Segment Growth Index Rebuild Maximum Extents Tablespace Full	Datafile Limit

Space Management

Problem

The DBA has been warned that a new table will be created in the APP_DATA tablespace and during the night a batch load will take place. The DBA decides to monitor the free space in this tablespace. Should free space become a problem, he wants to be notified of the condition, but also wants a datafile automatically added to avoid having the batch load fail.

Action

1. Create an event that will monitor the total used space in the APP_DATA tablespace. The event will generate a warning or critical alert if the tablespace becomes too full. In addition, create a fixit job (AppDataAddFile) and associate it with the event. Then, if the tablespace becomes too full, the job will run and automatically correct the problem. The steps to create the event are:
   1. Enter the name of the new event, AppDataFull, in the General tab of the Create Event page. Choose Database as the Target Type. Add one or more targets where the monitoring should take place for the event.
   2. Select the following event test from the Available Tests column in the Tests tab of the Create Event page:

      Space:

      Tablespace Full -- tablespace is full

2. In the Parameters tab of the Create Event page, accept the default Warning and Critical threshold settings. Also, specify the following filter for the Tablespace Name Filter: ='APP_DATA'
3. Create a fixit job (a job used to correct problems detected by an event) by accessing the Fixit Jobs tab on the Create Event page. Check the 'If ANY test triggers, run a fixit job' option then click the Create button. This activates the Oracle Enterprise Manager Console Job System. Follow these steps to create the fixit job:
   1. Enter the name of the new job, AppDataAddFile, in the General tab of the Create Job page. Choose Database as the Target Type. Select the database target where the event will be monitored.
   2. Select Run SQL*Plus Script from the Available Tasks list in the Tasks tab of the Create Job page.
   3. Enter the following Script Text in the Parameters tab of the Create Job page:

      ALTER TABLESPACE APP_DATA

      ADD DATAFILE

      'c:\orant\database\app02.ora' SIZE 5M;

4. Submit the fixit job. (You must resubmit the job for each datafile or the job will fail.)

Resolution

In this example using the Oracle Event Management System, the DBA was able to proactively monitor for the potential problem and automatically prevent any impact to end users. No further action would be required.

Performance Tuning

Problem

The DBA knows a new SQL intensive application is being brought on-line. He worries that his shared pool might be sized too small to handle the new application. If the shared pool is too small, users will consume additional resources in order to complete a database operation. For library cache access, the overhead is primarily the additional CPU resources required to reparse the SQL statement. For dictionary cache access, the overhead is primarily the additional I/O since the dictionary cache references that have been displaced from the cache will need to be refetched from disk.

To prevent any performance degradation that would be noticed by users, he decides to proactively monitor the performance of the shared pool.

Action

Create an event through the Oracle Enterprise Manager Event Management System that will monitor the performance of the shared pool. Since the shared pool contains the library cache and the data dictionary cache, the efficiency of both of these caches should be monitored to determine whether or not the shared pool requires tuning. If either of these caches are performing poorly, the DBA wants to be notified. The steps to create the event set are:

1. Enter the name of the new event, SharedPool, in the General tab of the Create Event page. Choose Database as the Target Type. Add one or more targets where the monitoring should take place for the event.
2. Select the following event tests from the Available Tests column in the Tests tab of the Create Event page:

   Performance - Instance Activity:

   Library Cache Hit % -- monitors library cache efficiency as measured by the miss ratio. It records the percentage of times the fully parsed or compiled representation of PL/SQL blocks and SQL statements are not already in memory.

   Data Dictionary Hit % -- monitors dictionary cache efficiency as measured by the miss ratio. It records the percentage of times the dictionary data was not already in memory.

3. Accept the default number of occurrences and threshold settings in the Parameters tab of the Create Event page for each of the selected events.

Resolution

If the library cache miss ratio or the data dictionary miss ratio is high then the DBA has several options:

• Use Oracle Expert to tune the shared pool by creating a focused tuning session for the database. The tuning scope of the tuning session should focus on SGA instance tuning. If the Library Cache Event test was triggered, the DBA may also want to include the SQL reuse tuning focus. Once Oracle Expert has automatically collected and analyzed the appropriate data, a set of recommendations for achieving the best performance is provided.
• Consider increasing the SHARED_POOL_SIZE to:
  • Decrease the frequency in which SQL requests are being flushed from the shared pool in order to make room for new request
  • Decrease the frequency in which dictionary data is being flushed from the shared pool in order to make room for new data
• To take advantage of the additional memory available for shared SQL areas, the number of cursors permitted for a session may need to be increased. This limit can be increased by increasing the value of the initialization parameter OPEN_CURSORS.
• If the library cache was found to be inefficient, examine SQL statements which can be modified to optimize shared SQL pool memory utilization and avoid unnecessary statement reparsing. This type of problem is commonly caused when similar SQL statements are written which differ in space, case or some combination of the two. Consider using bind variables rather than explicitly specified constants in your statements whenever possible.