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: 

1. On the Results for Tasks page, click Recommendations.

   The Recommendations subpage appears.

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

   The Schedule Implementation page appears.

3. In the Job Name field, enter a name for the job if you do not want to use the system-generated job name.

4. Determine whether or not 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.

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

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

6. Optionally, click Show SQL to view the SQL text for the job.

7. To submit the job, click Submit.

8. 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 is shown, then click the link in the Scheduler Job field to display the View Job page. Go to Step 10.

   • If the job is scheduled to start at a later time, then proceed to Step 9.

9. Complete the following steps:

   1. On the Server page, under Oracle Scheduler, click 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.

10. On the View Job page, under Operation Detail, check the status of the operation.

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

12. Optionally, from the Database Home page, click Schema.

    The Schema subpage appears.On this page you can verify that the access structure recommended by SQL Access Advisor is 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.