| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-02 |
|
|
View PDF |
| Oracle® Database 2 Day + Performance Tuning Guide 10g Release 2 (10.2) Part Number B28051-02 |
|
|
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. The SQL Access Advisor enables you to optimize data access paths of SQL queries by recommending the proper set of materialized views, materialized view logs, and indexes for a given workload.
A materialized view provides access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any 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. The SQL Access Advisor recommends how to optimize materialized views so that they can be fast refreshable and take advantage of general query rewrite. For more information about materialized views and materialized view logs, see Oracle Database Concepts.
The 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 the SQL Access Advisor to recommend the proper set of materialized views, materialize view logs, and indexes for a SQL workload involves:
See Also:
Chapter 10, "Tuning SQL Statements" for information about the SQL Tuning Advisor
This section describes how to run the SQL Access Advisor to make recommendations on a SQL workload.
To run the SQL Access Advisor:
Select the initial options, as described in "Running the SQL Access Advisor: Initial Options".
Select the workload source you want to use for the analysis, as described in "Running the SQL Access Advisor: Workload Source".
Define the filters options, as described in "Running the SQL Access Advisor: Filter Options".
Choose the types of recommendations, as described in "Running the SQL Access Advisor: Recommendation Options".
Schedule the SQL Access Advisor task, as described in "Running the SQL Access Advisor: Schedule".
The first step in running the SQL Access Advisor is to select the initial options on the SQL Access Advisor: Initial Options page.
On the Database Home page, under Related Links, click Advisor Central.
The Advisor Central page appears.
Under Advisors, click SQL Access Advisor.
The SQL Access Advisor: Initial Options page appears.
Select the initial options. To use the:
Recommended options defined in the Oracle Enterprise Manager default template, select Use Default Options.
Options defined in an existing SQL Access Advisor task or another template, select Inherit Options from a Task or Template.
The Tasks and Templates section appears. In the View list, select to display Templates Only, Tasks Only, or Tasks and Templates.
Select the task or template you want to use.
In this example, Use Default Options is selected.
Click Continue.
The SQL Access Advisor: Workload Source page appears.
Proceed to the next step, as described in "Running the SQL Access Advisor: Workload Source".
After initial options are specified for the SQL Access Advisor, you need to select the workload source you want to use for the analysis, as described in the following sections:
Tip:
Before you can select the workload source for the SQL Access Advisor, you need to select the initial options, as described in "Running the 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.
Proceed to the next step, as described in "Running the SQL Access Advisor: Filter Options".
You can use an existing SQL tuning set as the workload source. This is useful because SQL tuning sets can be used repeatedly as the workload source for not only the SQL Access Advisor, but also the SQL Tuning Advisor.
To use a SQL tuning set as the workload source:
On the SQL Access Advisor: Workload Source page, select Import Workload from SQL Repository.
To display available SQL tuning sets, click the SQL Tuning Set search icon.
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 is displayed.
Select the SQL tuning set you want to use 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 the SQL Access Advisor: Filter Options".
You can create a workload source by importing SQL statements from a table or view. This is useful if the workload you want to analyze is not currently running on the database or captured in an existing SQL tuning set. There are no restrictions on which schema the workload resides in, the name of the table, or the number of tables that you can define. The only requirement is that the format of the table must match the USER_WORKLOAD table.
See Also:
Oracle Database Performance Tuning Guide for information about creating a user-defined table or view that can be used as the workload source
To use a user-defined workload as the workload source:
On the SQL Access Advisor: Workload Source page, select User-Defined Workload; Import SQL from a Table or View.
To display available tables and views, click the Table search icon.
The Search and Select: User Defined Workload dialog box appears.
In the Schema field, enter the name of the schema containing the table or view you want to use and click Go.
A list of tables and views in the selected schema is displayed.
Select the table or view you want to use for the workload source and click Select.
The Search and Select: User Defined Workload dialog box closes and the selected table or view now appears in the Table field.
Proceed to the next step, as described in "Running the SQL Access Advisor: Filter Options".
You can create a hypothetical workload from dimension tables containing primary or foreign key constraints. This is useful if the workload you want to analyze does not exist. In this case, the SQL Access Advisor will examine the current logical schema design, and provide 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.
To search for tables, click the Table search icon.
The Search and Select: Schema and Table dialog box appears.
In the Schema field, enter the name of the schema you want to search and click Go.
A list of tables in the selected schema is displayed.
Select the tables you want to use in creating the hypothetical workload and click Select.
The Search and Select: Schema and Table dialog box closes and the selected tables now appear in the Table field.
Proceed to the next step, as described in "Running the 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 benefits:
Using filters directs the 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, you need to:Select initial options, as described in "Running the SQL Access Advisor: Initial Options"
Select the workload source, as described in "Running the 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 the SQL Access Advisor: Recommendation Options".
The resource consumption filter restricts the workload to only include a number of high-load SQL statements that you specify.
To define a filter for resource consumption:
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 method by which the SQL statements are to be ordered:
Optimizer cost
Buffer gets
CPU Time
Disk Reads
Elapsed Time
Executions
The users filter restricts the workload to include or exclude SQL statements executed by users that you specify.
To define a filter for users:
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 ICHAN. 
Description of the illustration sql_access_filter_users.gif
The tables filter restricts the workload to include or exclude SQL statements that access a list of tables that you specify.
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 users, 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 contains specific SQL text, enter the SQL text to be excluded in the Exclude all SQL statements containing these SQL text substrings field.
The module ID filter restricts the workload to include or exclude SQL statements that are associated with modules IDs that you specify.
To define a filter for module ID:
To include only SQL statements associated to a specific module ID in the workload, select Include only SQL statements associated with these module IDs.
To exclude all SQL statements associated to a specific module ID from the workload, select Exclude all SQL statements associated with these module IDs.
In the Module IDs field, enter the module IDs 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 to a specific action in the workload, select Include only SQL statements associated with these actions.
To exclude all SQL statements associated to 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, the SQL Access Advisor provides recommendation for indexes, materialized views, or both indexes and materialized views. 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 the SQL Access Advisor, you need to:Select initial options, as described in "Running the SQL Access Advisor: Initial Options"
Select the workload source, as described in "Running the SQL Access Advisor: Workload Source"
Define the filter options, as described in "Running the SQL Access Advisor: Filter Options"
To specify recommendation options:
On the SQL Access Advisor: Recommended Options page, under Recommendation Types, select the type of access structures to be recommended by the SQL Access Advisor:
Indexes
Materialized Views
Indexes and Materialized Views
Evaluation Only
Choose this option to evaluate only existing access structures. New access structures will not be recommended for the workload.
In this example, Indexes and Materialized Views is selected.
Under Advisor Mode, select the mode in which the SQL Access Advisor will run:
Limited Mode
In limited mode, the SQL Access Advisor focuses on SQL statements with the highest cost in the workload. The analysis is quicker, but the recommendations may be limited.
Comprehensive Mode
In comprehensive mode, the SQL Access Advisor analyzes all SQL statements in the workload. The analysis can take much longer, but the recommendations will be exhaustive.
Advanced Options
To use advanced options, click Advanced Options. The Advanced Options section expands. You can specify advanced options for:
Workload Categorization
Under Workload Volatility, you can choose whether or not to allow the SQL Access Advisor to consider the volatility of referenced objects when forming recommendations. Using volatility data is useful for online transaction processing (OLTP) systems, where the performance of INSERT, UPDATE, and DELETE operations is critical. On the other hand, if the workload contains primarily read-only operations, such is the case in database warehouses, volatility data should not be used.
Under Workload Scope, select Partial Workload to exclude volatility data, or Complete Workload to include volatility data.
Space Restrictions
Indexes and materialized views increase performance at the cost of space. When the SQL Access Advisor is invoked with no space limits, it will make the best possible performance recommendations. To specify no space limit, under Space Restrictions, select No, show me all recommendations (unlimited space).
When the SQL Access Advisor is invoked with a space limit, it will produce only recommendations with space requirements that do not exceed the specified limit. To specify a space limit, under Space Restrictions, select Yes, space is limited. In the Space Adjustment field, enter the space limit in Megabytes, Gigabytes, or Terabytes.
Tuning Options
The Tuning Options section enables you to specify how SQL statements will be tuned. From the Prioritize Tuning of SQL Statements by list, select the method by which SQL statements are to be tuned: Optimizer Cost, Buffer Gets, CPU Time, Disk Reads, Elapsed Time, or Executions.
To weigh the cost of creating access structures against the frequency and potential improvement of SQL statement execution time, check the Allow Advisor to consider creation costs when forming recommendations box. Otherwise, creation cost will be ignored. You should check this box if you want specific recommendations generated for SQL statements that are executed frequently.
Default Storage Locations
Use the Default Storage Locations section to override the defaults defined for schema and tablespace locations. By default, indexes are placed in the schema and tablespace of the table they reference. Materialized views are placed in the schema and tablespace of the first table referenced in the query. Materialized view logs are placed in the default tablespace of the schema of the table they reference.
In this example, Limited Mode is selected.
Click Next.
The Schedule page appears.
Proceed to the next step, as described in "Running the SQL Access Advisor: Schedule".
Use the SQL Access Advisor Schedule page, shown in Figure 11-1, 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, you need to:Select initial options, as described in "Running the SQL Access Advisor: Initial Options"
Select the workload source, as described in "Running the SQL Access Advisor: Workload Source"
Define the filter options, as described in "Running the SQL Access Advisor: Filter Options"
Specify the recommendation options, as described in "Running the 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, ICHAN20061023 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. Available journaling levels include:
None
No information is logged.
Basic
Information such as errors, warnings, and roll-ups of activities are logged.
Moderate
Information such as major progress steps, SQL statements skipped, SQL statements processed, and analysis details are logged.
Full
All available information is logged.
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.
Under Scheduling Options, in the Schedule Type list, select a schedule type for the task and a maintenance window in which you want the task to run.
The available schedule types include:
Standard
This schedule type enables you to select a repeating interval and start time for the task.
Under Repeating, select the repeating interval using the Repeat and Interval fields, or Do Not Repeat if you do not want the task to repeat automatically.
Under Start, select Immediately to start the task immediately, or Later to schedule the task to start at a time specified using the Date and Time fields.
User pre-defined schedule
This schedule type enables you to select an existing schedule to use.
In the Schedule field, enter the name of the schedule you want to use for the task. To search for a schedule, click the Schedule search icon. The Search and Select: Schedule dialog box appears. Select the schedule you want to use and click Select. The selected schedule now appears in the Schedule field. To display details about the schedule, click View Details.
Standard using PL/SQL for repeated interval
This schedule types enables you to select a repeating interval and an execution window for the task.
Under Available to Start, select Immediately to make the task available to start immediately, or Later to schedule the task to become available at a time specified using the Date and Time fields.
Under Repeated Interval, enter a PL/SQL schedule expression, such as SYSDATE+1.
Under Not Available After, select No End Date if you do not want to use an end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.
User pre-defined window
In the Window field, select a window to run the task during specific time periods. You can run the task in multiple windows by grouping the windows in a window group.
To search for a window, click the Window search icon. The Search and Select: Window and Window Groups dialog box appears. Select the window or window group you want to use and click Select. The selected window or window group now appears in the Window field.
To stop the task if the available window or window group closes, check the Stop on Window Close box.
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 Review page appears. Under Options, a list of all options for the SQL Access Advisor task is displayed. Options that are modified can be identified by the check marks in the Modified column. The value for each option is displayed in the Value column. To view the SQL text for the task, click Show SQL.
After reviewing the options on the Review page, click Submit to submit the SQL Access Advisor task.
The Advisor Central page appears and a message is displayed to inform you that the task was created successfully.
This section describes how to review the SQL Access Advisor recommendations.
Tip:
Before reviewing the SQL Access Advisor recommendations, you need to run the SQL Access Advisor to make the recommendations, as described in "Running the SQL Access Advisor".To review the SQL Access Advisor recommendations:
On the Advisor Central page, select the SQL Access Advisor task you want to review and click View Result.
If the task is not displayed, 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.
To review recommendations summary:
On the Results for Tasks page, click Summary.
The Summary subpage is displayed.
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 improve from 3183 to 21 by implementing the recommendations.
Use the Query Execution Time Improvement chart to compare the improvement in query execution time.
The Query Execution Time Improvement 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 calculated along the vertical axis (0% to 100%).
In this example, approximately 45 percent of SQL statements in the workload will gain a minimal performance improvement in execution time, but 55 percent will gain a significant improvement of over 10x.
Under Recommendations, the number of recommendations made by the SQL Access Advisor and their space requirements are displayed.
To display the type of recommendations, click Show Recommendation Action Counts.
In this example, creating 3 materialized views and 4 materialized view logs are recommended.
Under SQL Statements, the number of SQL statements analyzed in the workload is displayed.
To display the types of SQL statement, click Show SQL Statement Counts.
In this example, 5 SELECT statements are analyzed.
The Recommendations subpage ranks the SQL Access Advisor recommendations by cost improvement. You can also view details about each recommendation using this page.
To review recommendation details:
On the Results for Tasks page, click Recommendations.
The Recommendations subpage is displayed.
Use the Recommendations by Cost Improvement chart to view recommendations ordered by the cost improvement.
Implementing the top recommendation will have the biggest benefit to the total performance of the workload.
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 statement for each recommendation.
In this example, implementing the recommendation with ID 1 will produce the biggest benefit, with a cost improvement of 72.55 percent, on 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 is displayed.
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 is displayed.
Use the SQL Statements by Cost Improvement chart to view SQL statements in the workload ordered by the cost improvement.
Implementing the recommendation associated with the top SQL statement will have the biggest benefit to the total performance of the workload.
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.
In this example, implementing the recommendation with ID 1 will produce the biggest benefit, a cost improvement of 99.7 percent, for the SQL statement with ID 21.
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 is displayed.
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.
This section describes how to implement the SQL Access Advisor recommendations.
Tip:
Before implementing the SQL Access Advisor recommendations, you need to 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 is displayed.
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:
To stop processing if an error occurs, check the Stop on Error box.
To continue processing even if an error occurs, uncheck the Stop on Error box.
Under Scheduling Options, in the Schedule Type list, select a schedule type for the implementation job and a maintenance window in which you want the job to run.
The available schedule types include:
Standard
This schedule type enables you to select a repeating interval and start time for the job.
Under Repeating, select the repeating interval using the Repeat and Interval fields, or Do Not Repeat if you do not want the job to repeat automatically.
Under Start, select Immediately to start the job immediately, or Later to schedule the job to start at a time specified using the Date and Time fields.
User pre-defined schedule
This schedule type enables you to select an existing schedule to use.
In the Schedule field, enter the name of the schedule you want to use for the job. To search for a schedule, click the Schedule search icon. The Search and Select: Schedule dialog box appears. Select the schedule you want to use and click Select. The selected schedule now appears in the Schedule field. To display details about the schedule, click View Details.
Standard using PL/SQL for repeated interval
This schedule types enables you to select a repeating interval and an execution window for the job.
Under Available to Start, select Immediately to make the job available to start immediately, or Later to schedule the job to become available at a time specified using the Date and Time fields.
Under Repeated Interval, enter a PL/SQL schedule expression, such as SYSDATE+1.
Under Not Available After, select No End Date if you do not want to use an end date for the execution window, or Specified End Date to specify an end date using the Date and Time fields.
User pre-defined window
In the Window field, select a window to run the job during specific time periods. You can run the job in multiple windows by grouping the windows in a window group.
To search for a window, click the Window search icon. The Search and Select: Window and Window Groups dialog box appears. Select the window or window group you want to use and click Select. The selected window or window group now appears in the Window field.
To stop the job if the available window or window group closes, check the Stop on Window Close box.
In this example, Standard is selected for schedule type. The job will not repeat and is scheduled to start immediately.
To view the SQL text for the job, click Show SQL.
To submit the job, click Submit.
If the job is scheduled to start immediately, the Results for Tasks page for the SQL Access Advisor task appears with a confirmation that the job was successfully created.
In the Scheduler Job field, click the link to display the View Job page for the implementation job. Skip the next step and proceed to step 10.
If the job is scheduled to start at a later time, you will need to access the View Job page after the job is completed.
On the Database Administration page, under Database 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, verify that the status of the operation was successful.
If the operation is not successful, you can view details about the operation by selecting the operation and clicking View. This displays the Operation Detail page, which contains information (such as start date and time, run duration, CPU time used, and session ID) that you can use to troubleshoot the failure.
Verify that the access structure recommended by the 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$$_5BCD0000 is created in the SH schema.
