| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-01 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 11g Release 1 (11.1) Part Number B28275-01 |
|
|
View PDF |
To achieve optimum performance for data-intensive queries, materialized views and indexes are essential when tuning SQL statements. Implementing these objects, however, does not come without a cost. Creation and maintenance of these objects can be time-consuming, and space requirements can be significant. SQL Access Advisor enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views and view logs, indexes, SQL profiles, and partitions for a given workload.
A materialized view provides access to table data by storing query results in a separate schema object. Unlike an ordinary view, which does not take up storage space or contain data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view log is a schema object that records changes to a master table's data, so that a materialized view defined on the master table can be refreshed incrementally. SQL Access Advisor recommends how to optimize materialized views so that they can be rapidly refreshed and take advantage of the general query rewrite feature. For more information about materialized views and view logs, see Oracle Database Concepts.
SQL Access Advisor also recommends bitmap, function-based, and B-tree indexes. A bitmap index provides a reduced response time for many types of ad hoc queries and reduced storage requirements compared to other indexing techniques. A function-based index derives the indexed value from the table data. For example, to find character data in mixed cases, a function-based index can be used to look for the values as if they were all in uppercase characters. B-tree indexes are most commonly used to index unique or near-unique keys.
Using SQL Access Advisor involves the following tasks:
See Also:
Chapter 10, "Tuning SQL Statements" for information about SQL Tuning Advisor
This section describes how to run SQL Access Advisor to make recommendations on a SQL workload.
To run SQL Access Advisor:
Select the initial options, as described in "Running SQL Access Advisor: Initial Options".
Select the workload source you want to use for the analysis, as described in "Running SQL Access Advisor: Workload Source".
Define the filters options, as described in "Running SQL Access Advisor: Filter Options".
Choose the types of recommendations, as described in "Running SQL Access Advisor: Recommendation Options".
Schedule the SQL Access Advisor task, as described in "Running SQL Access Advisor: Schedule".
The first step in running SQL Access Advisor is to select the initial options on the SQL Access Advisor: Initial Options page.
To select initial options:
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Advisors.
The SQL Advisors page appears.
Click SQL Access Advisor.
The SQL Access Advisor: Initial Options page appears.
Select the initial options. Do one of the following:
Select Verify use of access structures (indexes, materialized views, partitioning, and so on) only to verify existing structures.
Select Recommend new access structures to use the recommended options defined in the Oracle Enterprise Manager default template.
If you select this option, then you can optionally complete the following additional steps:
Select Inherit Options from a previously saved Task or Template to use the options defined in an existing SQL Access Advisor task or another template.
In Tasks and Templates, select the task or template that you want to use.
In this example, Recommend new access structures is selected.
Click Continue.
The SQL Access Advisor: Workload Source page appears.
Proceed to the next step, as described in "Running SQL Access Advisor: Workload Source".
After initial options are specified for SQL Access Advisor, select the workload source that you want to use for the analysis, as described in the following sections:
Tip:
Before you can select the workload source for SQL Access Advisor, select the initial options, as described in "Running SQL Access Advisor: Initial Options".You can use SQL statements from the cache as the workload source. However, because only current and recent SQL statements are stored in the SQL cache, this workload source may not be representative of the entire workload on your database.
To use SQL statements from the cache as the workload source:
On the SQL Access Advisor: Workload Source page, select Current and Recent SQL Activity.
In this example, Use Default Options is selected.
Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".
You can use an existing SQL Tuning Set as the workload source. This option is useful because SQL Tuning Sets can be used repeatedly as the workload source for not only SQL Access Advisor, but also SQL Tuning Advisor.
To use a SQL Tuning Set as the workload source:
On the SQL Access Advisor: Workload Source page, select Use an existing SQL Tuning Set.
Click the SQL Tuning Set search icon to use an existing SQL Tuning Set.
The Search and Select: SQL Tuning Set dialog box appears.
In the Schema field, enter the name of the schema containing the SQL Tuning Set you want to use and click Go.
A list of SQL Tuning Sets contained in the selected schema appears.
Select the SQL Tuning Set to be used for the workload source and click Select.
The Search and Select: SQL Tuning Set dialog box closes and the selected SQL Tuning Set now appears in the SQL Tuning Set field.
Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".
See Also:
A dimension table stores all or part of the values for a logical dimension in a star or snowflake schema. You can create a hypothetical workload from dimension tables containing primary or foreign key constraints. This option is useful if the workload to be analyzed does not exist. In this case, SQL Access Advisor examines the current logical schema design, and provides recommendations based on the defined relationships between tables.
To use a hypothetical workload as the workload source:
On the SQL Access Advisor: Workload Source page, select Create a Hypothetical Workload from the Following Schemas and Tables.
Leave Schemas and Tables empty and click Add to search for tables.
The Workload Source: Search and Select Schemas and Tables page appears.
In the Tables section, enter a schema name in the Schema field and click Search.
A list of tables in the selected schema is displayed.
Select the tables to be used in creating the hypothetical workload and click Add Tables.
The selected tables now appear in the Schemas and Tables field.
Click OK.
The SQL Access Advisor: Workload Source page appears with the selected tables now added.
Proceed to the next step, as described in "Running SQL Access Advisor: Filter Options".
After the workload source is selected, you can apply filters to reduce the scope of the SQL statements found in the workload. While using filters is optional, it can be very beneficial due to the following:
Using filters directs SQL Access Advisor to make recommendations based on a specific subset of SQL statements from the workload, which may lead to better recommendations.
Using filters removes extraneous SQL statements from the workload, which may greatly reduce processing time.
Tip:
Before you can select the filter options for the workload, do the following:Select initial options, as described in "Running SQL Access Advisor: Initial Options".
Select the workload source, as described in "Running SQL Access Advisor: Workload Source".
To apply filters to the workload source:
On the SQL Access Advisor: Workload Source page, click Filter Options.
The Filter Options section expands.
Select Filter Workload Based on these Options.
The Filter Options section is enabled.
Define the filters you want to apply, as described in the following sections:
Click Next.
The Recommendation Options page appears.
Proceed to the next step, as described in "Running SQL Access Advisor: Recommendation Options".
The resource consumption filter restricts the workload to include only the number of high-load SQL statements that you specify.
To define a filter for resource consumption:
On the SQL Access Advisor: Workload Source page, under User Resource Consumption, enter the number of high-load SQL statements in the Number of Statements field.
From the Order by list, select one of the methods by which the SQL statements are to be ordered.
The users filter restricts the workload to include or exclude SQL statements executed by users that you specify.
To define a filter for users:
On the SQL Access Advisor: Workload Source page, under Users, select Include only SQL statements executed by these users or Exclude all SQL statements executed by these users.
To search for available users, click the Users search icon.
The Search and Select: Users dialog box appears.
Select the users for which you want to include or exclude SQL statements and click Select.
The Search and Select: Users dialog box closes and the selected tables now appear in the Users field.
In this example, a filter is defined to include only SQL statements executed by the user SH.
The tables filter restricts the workload to include or exclude SQL statements that access a list of tables that you specify. Table filters are not permitted if you selected the Create a Hypothetical Workload from the Following Schemas and Tables option, as described in "Using a Hypothetical Workload".
To define a filter for tables:
To include only SQL statements that access a specific list of tables, enter the table names in the Include only SQL statements that access any of these tables field.
To exclude all SQL statements that access a specific list of tables, enter the table names in the Exclude all SQL statements that access any of these tables field.
To search for available tables, click the Tables search icon.
The Search and Select: Schema and Table dialog box appears.
Select the tables for which you want to include or exclude SQL statements and click Select.
The Search and Select: Schema and Table dialog box closes and the selected tables now appear in the corresponding Tables field.
The SQL text filter restricts the workload to include or exclude SQL statements that contains SQL text substrings that you specify.
To define a filter for SQL text:
To include only SQL statements that contains specific SQL text, enter the SQL text to be included in the Include only SQL statements containing these SQL text substrings field.
To exclude all SQL statements that contain specific SQL text, enter the SQL text to be excluded in the Exclude all SQL statements containing these SQL text substrings field.
The module filter restricts the workload to include or exclude SQL statements that are associated with modules that you specify.
To define a filter for module ID:
To include only SQL statements associated with a specific module ID in the workload, select Include only SQL statements associated with these modules.
To exclude all SQL statements associated to a specific module ID from the workload, select Exclude all SQL statements associated with these modules.
In the Modules field, enter the names of the modules for which associated SQL statements will be included or excluded.
The actions filter restricts the workload to include or exclude SQL statements that are associated with actions that you specify.
To define a filter for actions:
To include only SQL statements associated with a specific action in the workload, select Include only SQL statements associated with these actions.
To exclude all SQL statements associated with a specific action from the workload, select Exclude all SQL statements associated with these actions.
In the Actions field, enter the actions for which associated SQL statements will be included or excluded.
To improve the underlying data access methods chosen by the optimizer for the workload, SQL Access Advisor provides recommendation for indexes, materialized views, and partitioning. Using these access structures can significantly improve the performance of the workload by reducing the time required to read data from the database. However, you must balance the benefits of using these access structures against the cost to maintain them.
Tip:
Before you can select the recommendation options for SQL Access Advisor, do the following:Select initial options, as described in "Running SQL Access Advisor: Initial Options".
Select the workload source, as described in "Running SQL Access Advisor: Workload Source".
Define the filter options, as described in "Running SQL Access Advisor: Filter Options".
To specify recommendation options:
On the SQL Access Advisor: Recommendation Options page, under Access Structures to Recommend, select the type of access structures to be recommended by SQL Access Advisor:
Indexes
Materialized Views
Partitioning
In this example, all of the preceding access types are selected.
Under Scope, select the mode in which SQL Access Advisor will run. Do one of the following:
Select Limited Mode.
In limited mode, SQL Access Advisor focuses on SQL statements with the highest cost in the workload. The analysis is quicker, but the recommendations may be limited.
Select Comprehensive Mode.
In comprehensive mode, SQL Access Advisor analyzes all SQL statements in the workload. The analysis can take much longer, but the recommendations will be exhaustive.
In this example, Limited Mode is selected.
Optionally, click Advanced Options.
The Advanced Options section expands. This section contains the following subsections:
Workload Categorization
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 contains primarily read-only operations, as in data warehouses. Volatility data is useful for online transaction 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.
Click Next.
The SQL Access Advisor: Schedule page appears.
Proceed to the next step, as described in "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.
Figure 11-1 Scheduling a SQL Access Advisor 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".
Select the workload source, as described in "Running SQL Access Advisor: Workload Source".
Define the filter options, as described in "Running SQL Access Advisor: Filter Options".
Specify the recommendation options, as described in "Running SQL Access Advisor: Recommendation Options".
To schedule a SQL Access Advisor task:
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.
In the Task Description field, enter a description of the task.
In the example shown in Figure 11-1, SQL Access Advisor is entered.
From the Journaling Level list, select the level of journaling for the task.
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, Basic is selected.
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, 30 is entered.
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 of UNLIMITED. In the example shown in Figure 11-1, 10 is entered.
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 types 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 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 the example shown in Figure 11-1, Standard is selected for schedule type. The task will not repeat and is scheduled to start immediately.
Click Next.
The SQL Access Advisor: Review page appears.
Under Options, a list of modified options for the SQL Access Advisor task is shown. To display both modified and unmodified options, click Show All Options. To view the SQL text for the task, click Show SQL.
Click Submit.
The Advisor Central page appears. A message informs you that the task was created successfully.
SQL Access Advisor graphically displays the recommendations and provides hyperlinks so that you can quickly see which SQL statements benefit from a recommendation. Each recommendation produced by the SQL Access Advisor is linked to the SQL statement it benefits.
Tip:
Before reviewing the SQL Access Advisor recommendations, run SQL Access Advisor to make the recommendations, as described in "Running SQL Access Advisor".To review the SQL Access Advisor recommendations:
On the Advisor Central page, select the SQL Access Advisor task for review and click View Result.
In this example, Limited Mode is selected.
If the task is not displayed, then you may need to refresh the screen. The Results for Task page appears.
Review the Summary subpage, which provides an overview of the SQL Access Advisor analysis, as described in "Reviewing the SQL Access Advisor Recommendations: Summary".
Review the Recommendations subpage, which enables you to view the recommendations ranked by cost improvement, as described in "Reviewing the SQL Access Advisor Recommendations: Recommendations".
Review the SQL statements analyzed in the workload, as described in "Reviewing the SQL Access Advisor Recommendations: SQL Statements".
Review the details of the workload, task options, and the SQL Access Advisor task, as described in "Reviewing the SQL Access Advisor Recommendations: Details".
The Summary subpage displays an overview of the SQL Access Advisor analysis.
In this example, Limited Mode is selected.
To review the recommendations summary:
On the Results for Tasks page, click Summary.
The Summary subpage appears.
In this example, Limited Mode is selected.
Under Overall Workload Performance, assess the potential for improvement in implementing the recommendations.
Use the Workload I/O Cost chart to compare the original workload I/O cost (in red) with the new cost (in blue).
In this example, the workload I/O cost will decrease from 877 to 867 by implementing the recommendations.
Use the Query Execution Time Improvement chart to compare the improvement in query execution time.
This chart shows the percentage of SQL statements in the workload whose execution time will improve by accepting the recommendations. The SQL statements are grouped by the projected improvement factor along the horizontal axis on the chart (1x to >10x). The percentage of SQL statements that will improve by the projected improvement factor are along the vertical axis (0% to 100%).
In this example, approximately 75 percent of SQL statements in the workload will gain no performance improvement in execution time, but about 25 percent will have the potential for improvement of over 4x or more.
Under Recommendations, click Show Recommendation Action Counts.
In this example, creating 1 index, 4 materialized views, and 6 materialized view logs is recommended.
In this example, Limited Mode is selected.
Under SQL Statements, click Show Statement Counts to display the type of SQL statement.
In this example, 19 SELECT statements are analyzed.
In this example, Limited Mode is selected.
The Recommendations subpage ranks the SQL Access Advisor recommendations by cost improvement. You can also view details about each recommendation.
To review recommendation details:
On the Results for Tasks page, click Recommendations.
The Recommendations subpage appears.
Use the Recommendations by Cost Improvement chart to view recommendations ordered by the cost improvement.
Under Select Recommendations for Implementation, each recommendation is listed with its implementation status, recommendation ID, cost improvement, space consumption, and the number of affected SQL statements for each recommendation. Implementing the top recommendation will have the biggest benefit to the total performance of the workload.
To view details for a particular recommendation, select the recommendation and click Recommendation Details.
The Recommendation Details page appears.
The Recommendation Details page displays all actions for the specified recommendation.
Under Actions, you can choose to modify the schema name, tablespace name, and storage clause for each action. To view the SQL text of an action, click the link in the Action column for the specified action.
Under SQL Affected by Recommendation, the SQL text of the SQL statement and cost improvement information are displayed.
Click OK.
The Recommendations subpage appears.
To view the SQL text of a recommendation, select the recommendation and click Show SQL.
The Show SQL page for the selected recommendation appears.
The SQL Statements subpage ranks SQL statements in the workload by cost improvement. You can use this page to view details about the SQL statements analyzed in the workload.
On the Results for Tasks page, click SQL Statements.
The SQL Statements subpage appears.
Use the SQL Statements by Cost Improvement chart to view SQL statements in the workload ordered by the cost improvement.
Under Select SQL Statements to be Improved, each SQL statement is listed with its statement ID, SQL text, associated recommendation, cost improvement, and execution count.
Implementing the recommendation associated with the top SQL statement will have the biggest benefit to the total performance of the workload. In this example, implementing the recommendation with ID 1 will produce the biggest benefit, a cost improvement of 57.14 percent, for the SQL statement with ID 2421.
To view the SQL text of a recommendation, select the recommendation and click Show SQL.
The Show SQL page for the selected recommendation appears.
The Details subpage displays a list of all the workload and task options used in the analysis. You can also use this subpage to view a list of journal entries for the task, based on the journaling level used when the task was created.
To review workload and task details:
On the Results for Tasks page, click Details.
The Details subpage appears.
Under Workload and Task Options, a list of options that were selected when the advisor task was created is displayed.
Under Journal Entries, a list of messages that were logged to the SQL Access Advisor journal while the task was executing is displayed.
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.
Tip:
Before implementing the SQL Access Advisor recommendations, review them for cost benefits to determine which ones, if any, should be implemented. For more information, see "Reviewing the SQL Access Advisor Recommendations".To implement the SQL Access Advisor recommendations:
On the Results for Tasks page, click Recommendations.
The Recommendations subpage appears.
Under Select Recommendations for Implementation, select the recommendation you want to implement and click Schedule Implementation.
In this example, the recommendation with ID value 1 is selected.
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 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.
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.
In this example, Standard is selected for schedule type. The job will not repeat and is scheduled to start immediately.
Optionally, click Show SQL to view the SQL text for the job.
To submit the job, click Submit.
If the job is scheduled to start immediately, then the Results for Tasks page for the SQL Access Advisor task appears with a confirmation that the job was successfully created.
Do one of the following, depending on whether the job is scheduled to start immediately or later:
Complete the following steps:
On the Server page, under Oracle Scheduler, click Jobs.
The Scheduler Jobs page appears.
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.
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 to troubleshoot the failure.
On the Schema subpage, 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.
In this example, a materialized view named MV$$_00690000 is created in the SH schema.
