Implementing the SQL Access Advisor Recommendations

A SQL Access Advisor recommendation can range from a simple suggestion to a complex solution that requires partitioning a set of existing base tables and implementing a set of database objects such as indexes, materialized views, and materialized view logs. You can select the recommendations for implementation and schedule when the job should be executed.

To implement the SQL Access Advisor recommendations:

Review the SQL Access Advisor recommendations for cost benefits to determine which ones, if any, should be implemented.

See "Reviewing the SQL Access Advisor Recommendations" for more information.
Access the Results for Task page, as described in "Reviewing the SQL Access Advisor Recommendations".
Click Recommendations.

The Recommendations subpage appears.
Under Select Recommendations for Implementation, select the recommendation you want to implement and then click Schedule Implementation.

In the following example, the recommendation with ID value 1 is selected.

Description of the illustration sql_access_schedule_imp.gif

The Schedule Implementation page appears.
In the Job Name field, enter a name for the job if you do not want to use the system-generated job name.
Determine whether the implementation job should stop if an error is encountered. Do one of the following:
- To stop processing if an error occurs, select Stop on Error.
- To continue processing even if an error occurs, deselect Stop on Error.
Under Scheduling Options, in the Schedule Type list, select a schedule type for the task and a maintenance window in which the task should run. Do one of the following:
- Click Standard.
  
  This schedule type enables you to select a repeating interval and start time for the task. Complete the following steps:
  - Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.
  - In the Repeat list, select Do Not Repeat to perform the task only once, or select a unit of time and enter the number of units in the Interval field.
  - Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
- Click Use predefined schedule.
  
  This schedule type enables you to select an existing schedule. Do one of the following:
  - In the Schedule field, enter the name of the schedule to be used for the task.
  - To search for a schedule, click the search icon.
    
    The Search and Select: Schedule dialog box appears.
    
    Select the desired schedule and click Select. The selected schedule now appears in the Schedule field.
- Click Standard using PL/SQL for repeated interval.
  
  This schedule type enables you to select a repeating interval and an execution window for the task. Complete the following steps:
  - Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.
  - Under Available to Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
  - In the Repeated Interval field, enter a PL/SQL schedule expression, such as SYSDATE+1.
  - Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.
- Click Use predefined window.
  
  This schedule type enables you to select an existing window. Select Stop on Window Close to stop the job when the window closes. Do one of the following:
  - In the Window field, enter the name of the window to be used for the task.
  - To search for a window, click the search icon.
    
    The Search and Select: Window and Window Groups dialog box appears.
    
    Select the desired window and click Select. The selected window now appears in the Schedule field.
- Click Event.
  
  Complete the following steps:
  - Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.
  - Under Event Parameters, enter values in the Queue Name and Condition fields.
  - Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
  - Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.
- Click Calendar.
  
  Complete the following steps:
  - Enter your time zone code in the Time Zone field or click the search icon to locate the code for your area.
  - Under Calendar Expression, enter a calendar expression.
  - Under Start, select Immediately to start the task now, or Later to schedule the task to start at a time specified using the Date and Time fields.
  - Under Not Available After, select No End Date to indicate that there is no end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.
In this example, Standard is selected for schedule type. The job does not repeat and is scheduled to start immediately.

Description of the illustration sql_access_schedule_options.gif
Optionally, click Show SQL to view the SQL text for the job.
Click Submit to submit the job.
Do one of the following, depending on whether the job is scheduled to start immediately or later:
- If you submitted the job immediately, and if the Results for Task page appears, then click the link in the Scheduler Job field to display the View Job page. Go to Step 12.
- If the job is scheduled to start at a later time, then proceed to Step 11.
Complete the following steps:
1. From the Administration menu, select Oracle Scheduler, then Jobs.
  
  The Scheduler Jobs page appears.
2. Select the implementation job and click View Job Definition.
  
  The View Job page for the selected job appears.
On the View Job page, under Operation Detail, check the status of the operation.

Description of the illustration sql_access_operation_det.gif
Optionally, select the operation and click View.

The Operation Detail page appears.

This page contains information (such as start date and time, run duration, CPU time used, and session ID) that you can use when troubleshooting.
Optionally, from the Database Home page, select Schema, then the page of the object that was created.

Depending on the type of access structure that is created, you can display the access structure using the Indexes page, Materialized Views page, or the Materialized View Logs page.