Optimizing Data Access Paths 11-9
In this section, you can specify the type of workload for which you want a recommendation. The following categories are available:
– Workload Volatility
Select Consider only queries if the workload primarily contains read-only operations, as in data warehouses. Volatility data is useful for online trans- action processing (OLTP) systems, where the performance of INSERT, UPDATE, and DELETE operations is critical.
– Workload Scope
Select Recommend dropping unused access structures if the workload represents all access structure use cases.
■ Space Restrictions
Indexes and materialized views increase performance at the cost of space. Do one of the following:
– Select No, show me all recommendations (unlimited space) to specify no space limits. When SQL Access Advisor is invoked with no space limits, it makes the best possible performance recommendations.
– Select Yes, limit additional space to and then enter the space limit in megabytes, gigabytes, or terabytes. When SQL Access Advisor is invoked with a space limit, it produces only recommendations with space
requirements that do not exceed the specified limit.
■ Tuning Prioritization
This section enables you to specify how SQL statements will be tuned. Complete the following steps:
– From the Prioritize tuning of SQL statements by list, select a method by which SQL statements are to be tuned and then click Add.
– Optionally, select Allow Advisor to consider creation costs when forming recommendations to weigh the cost of creating access structures against the frequency and potential improvement of SQL statement execution time. Otherwise, creation cost will be ignored. You should select this option if you want specific recommendations generated for SQL statements that are executed frequently.
■ Default Storage Locations
Use this section to override the defaults defined for schema and tablespace locations. By default, indexes are in the schema and tablespace of the table they reference. Materialized views are in the schema and tablespace of the first table referenced in the query. Materialized view logs are in the default
tablespace of the schema of the table that they reference.
4. Click Next.
The SQL Access Advisor: Schedule page appears.
5. Proceed to the next step, as described in "Running SQL Access Advisor: Schedule" on page 11-9.
Running SQL Access Advisor: Schedule
Use the SQL Access Advisor Schedule page to set or modify the schedule parameters for the SQL Access Advisor task.
Running SQL Access Advisor
11-10 Oracle Database 2 Day + Performance Tuning Guide
Figure 11–1 Scheduling a SQL Access Advisor Task
To schedule a SQL Access Advisor task:
1. On the SQL Access Advisor: Schedule page, under Advisor Task Information, enter a name in the Task Name field if you do not want to use the
system-generated task name.
In the example shown in Figure 11–1, SQLACCESS9084523 is entered.
2. In the Task Description field, enter a description of the task.
In the example shown in Figure 11–1, SQL Access Advisor is entered.
3. From the Journaling Level list, select the level of journaling for the task. Tip: Before you can schedule a SQL Access Advisor task, do the following:
■ Select initial options, as described in "Running SQL Access
Advisor: Initial Options" on page 11-2.
■ Select the workload source, as described in "Running SQL Access
Advisor: Workload Source" on page 11-3.
■ Define the filter options, as described in "Running SQL Access
Advisor: Filter Options" on page 11-5.
■ Specify the recommendation options, as described in "Running
Running SQL Access Advisor
Optimizing Data Access Paths 11-11
Journaling level controls the amount of information that is logged to the SQL Access Advisor journal during task execution. This information appears on the Details subpage when viewing task results.
In the example shown in Figure 11–1 on page 11-10, Basic is selected.
4. In the Task Expiration (Days) field, enter the number of days the task will be retained in the database before it is purged.
In the example shown in Figure 11–1 on page 11-10, 30 is entered.
5. In the Total Time Limit (minutes) field, enter the maximum number of minutes that the job is permitted to run.
You must enter a time in this field rather than use the default.
6. 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 time period (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
Running SQL Access Advisor
11-12 Oracle Database 2 Day + Performance Tuning Guide
– 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 the example shown in Figure 11–1 on page 11-10, Standard is selected for schedule type. The task will not repeat and is scheduled to start immediately.
7. Click Next.