|Oracle Discoverer Administration Edition Administration Guide
Release 4.1 for Windows
This chapter describes how to automatically create and maintain summary folders using Discoverer's Automated Summary Management (ASM) feature. To understand summary folders and learn how Discoverer allows you to specify and maintain them yourself see Chapter 15, "Summaries".
This chapter consists of the following sections:
Discoverer is able to create summaries that improve query performance in Discoverer Plus (formerly known as Discoverer User Edition). Summaries represent combinations of pre-aggregated data held in summary tables or Materialized Views (see Chapter 184.108.40.206, "Summary tables or Materialized Views?"). Queries in Discoverer Plus can be redirected to the summary data when that data satisfies the conditions of the query. This means that summary data is quickly accessed without having to perform a new query on the database.
By using summaries Discoverer greatly reduces the time taken to display data in Discoverer Plus and provides a significant improvement in query performance.
In this chapter the term summary refers to pre-aggregated data held in a summary table or Materialized View (8.1.6+ databases) and is represented in Discoverer as a summary folder.
Automated Summary Management (ASM) delivers fast performing queries while greatly reducing the amount of time spent on administration.
ASM simplifies the process of summary creation and maintenance by offering you a fully automated solution to summary management. When run periodically ASM can automatically refine the set of summaries it creates using query statistics gathered by Discoverer from user queries. ASM also provides guidance should you wish to control the specification of default values during summary creation/maintenance.
Discoverer provides two ways to create summaries:
ASM analyzes your tables, uses query statistics (when available) and default values (the summary policy) to determine how summaries are created for you. ASM is able to automatically create and maintain the best set of summaries based on this combination.
ASM converts your summary policy into a list of recommended summary folder definitions. You can either implement these recommendations without further intervention (the actual set of summary tables dynamically adjusts with system usage) or execution can be stalled until you have specifically sanctioned one or more of them.
ASM will require some table space to create your summaries but you can adjust how much space is used via the Summary Wizard (see Section 16.2.4, "Allocate space for summaries - Step 3").
If you want Discoverer to automatically create and maintain your summaries you should select the ASM option: Have Discoverer recommend and create the best summaries from the Summary Wizard.
If you want to control the composition of your summaries then you should select the manual option: I want to specify the summaries myself from the Summary Wizard. See Chapter 15, "Summaries" for further details.
ASM should be run periodically as it is an iterative process, though you shouldn't need to alter your ASM policy (see Figure 16.1.6, "The ASM Policy (User Defined Constraints and Options)").
Running ASM periodically will ensure that ASM dynamically changes the set of summaries it creates or deletes to best fit your query usage patterns.
ASM can be run in three ways:
The advantage of using the command line is that Discoverer does not need to be running at the time. This reduces any intervention required by you to a minimum (see Appendix D.9.15, "Running Automated Summary Management (ASM)" for further details about how to run ASM via the command line).
You can run ASM through the batch file/scheduler facility provided by your operating system. This way you specify when you want ASM to run including the scheduled intervals when you want to repeat the process.
By using the command line within a batch file the process can be run automatically overnight or on a weekends. This allows the system to maintain itself.
Before you can run ASM from a batch file you first need to create a file suitable to run in batch mode (e.g. asmsched.bat). You then need to enter the command line details as text into the batch file.
To learn how to schedule a batch file refer to your operating system documentation or help. For details of ASM command line syntax see Appendix D.9.15, "Running Automated Summary Management (ASM)".
You need to connect to Discoverer as the EUL owner (i.e. the schema owner of the EUL tables) before using the Summary Wizard and ASM option.
ASM generates its own list of recommendations, which you can preview. You then accept or decline either all or a subset of these recommendations (see Section 16.2, "Running ASM using the Summary Wizard" for information on using the wizard).
NOTE: ASM works on the EUL as a whole, not just currently selected business areas which means it can use the folders from all business areas in the current EUL.
NOTE: In order for ASM to be able to analyze folders, permission must be granted to the EUL owner (the ASM user) to analyze either specific folders or `all' folders (see your database administrator for details).
During the Bulk Load process the Load Wizard gives you the option (a check box) to create summaries (by running ASM) based on the Folders created during Bulk Load (see Chapter 220.127.116.11, "Load Wizard: Step 4, Automatic Attributes"). If you select this option suitable summaries will be created after Bulk Load.
When ASM is run after a Bulk Load the summaries created are derived from table analysis and summary policy (see Section 16.1.6, "The ASM Policy (User Defined Constraints and Options)"). Query statistics are not available in this instance.
NOTE: Where Discoverer has been in use for a while, query statistics (gathered by Discoverer) will be available. ASM is usually run through using the Summary Wizard or the Command Line and will use the available query statistics to create more suitable summaries.
You do not need to make any changes to summary policy settings for a Bulk Load ASM process; default settings are used if no changes are made to the ASM policy. Once changes are made, these settings then become the defaults. The minimum required information for an ASM policy is a tablespace name and an allocated amount of disc space. The table space is the user's given tablespace, and a standard/default amount of disc space is used.
You can control how ASM behaves and what summaries it produces through a range of user defined constraints and options known as the ASM policy.
The ASM policy is divided into space options and advanced settings. In many cases you will only need to set the space options.
You may never need to customize a policy at all as the default settings are designed to provide a balanced range of values to ensure that suitable summaries are created and maintained without your intervention.
These determine the amount of system resources set aside for ASM. (see Section 16.2.4, "Allocate space for summaries - Step 3")
This defines the maximum estimated space that can be used for the automatic generation of summary data and is specified in terms of a Tablespace and then further qualified as an allotted quota within the Tablespace.
Performance benefit is displayed, for the allocated space selected, in the form of a chart showing space verses performance gain.
The preset default values found within Advanced Settings enable ASM to create and maintain the best set of summaries for your needs. If you want you can further refine the default setting to alter how ASM creates and maintains your summaries.
The Advanced Settings define which objects in the database to consider for inclusion in the ASM process and influence what ASM produces (see Section 16.2.6, "Change default settings dialog").
The Advanced Settings consist of the following areas:
Enables you to modify some of the settings related to table analysis, which is a requirement for ASM (see Section 18.104.22.168, "Analyze tab").
Enables the following EUL objects to be filtered for inclusion in the ASM process.
Selections can be based on:
The default selects all folders and all users for the current EUL to be available for the ASM process.
This enables filtering of sets of previously run queries which can help determine previous system performance. These are based on characteristics of the workload statistics:
Options available (see Section 22.214.171.124, "Query Usage tab"):
This enables Summary Folders to be deleted based on specified criteria.
During folder analysis (see Section 16.2.3, "Analyze folders - Step 2") this control influences whether the constructed summary data favors predetermined (performance) or ad hoc (coverage) queries.
For a performance based approach ASM would for example, recommend a list of exact match summaries, corresponding one-to-one with previously run queries (where ASM determines there is a benefit). For example, this could result in a list of say, five summaries.
For a coverage based approach ASM might combine the five exact-match summaries to form fewer but more general summaries. These would provide benefit not only for the five previously run queries but also for a greater number of other potential queries (i.e. there would be a gain in overall coverage).
If the setting is predetermined, the summaries maintained by ASM will be optimized to reflect historical queries, that is, the summaries will give great performance improvements where queries stay much the same over time.
On the other hand if the setting is ad hoc then combining summaries, as described above, will increase the coverage of a summary. This will give a potential performance gain for queries that have never been run before, but are closely related to previous system usage. However, the cost of this is that the individual performance gain for the previously run queries may not be as high.
The setting of this constraint should therefore be left to your own discretion.
The ASM wizard (see Section 126.96.36.199, "Query Usage tab") represents the above control as a sliding bar, with a choice between Predetermined and Ad hoc query usage. Moving the slider to the Ad hoc end of the bar means that most of your users perform principally ad hoc queries (as with coverage above), and moving the slider to the Predetermined end means that most of your users perform principally predetermined queries (as with performance above).
The default value used for analysis will balance performance with coverage.
This section describes how you use ASM to create Summary Folders. The summaries created by the ASM process will be exactly the same as other summaries.
To create summary folders in Discoverer you need certain privileges (see Chapter 15.3.1, "Prerequisites" for the privileges required to create summary folders).
See Section 188.8.131.52, "When some folders cannot be analyzed" for the reasons why some folders cannot be analyzed).
NOTE: For more information about Oracle Set Operators, refer to refer to the section Set Operators in the Oracle 8i SQL Reference, Release 2 (8.1.6), Part Number A76989-01.
There are three ways to do this:
The following dialog is displayed:
The above dialog gives you two choices:
Have Discoverer recommend and create the best summaries for you
This displays the ASM wizard (see Figure 16-2, "Summary Wizard Step 2 - Analyze Folders")
I want to specify the summaries myself
This displays the Summary Wizard step 2 for building new summary folders, where three ways of creating summaries are displayed (see Chapter 15.3, "How to create Summary Folders").
In order for Discoverer to work out the best summaries to create, it needs to analyze every folder that is to be involved in the summary process. Discoverer uses available query statistics (gathered by Discoverer), table structure information and summary policy details to create suitable summaries. Depending on the number and size of folders to be analyzed this could take a little time to complete. Discoverer allows you to start and stop this process at your own pace.
Your progress will be displayed during the analyze process.
Folder analysis comprises four steps:
If at the end of the "Setting up advice" step some folders cannot be analyzed then a dialog box appears (Figure 16-7, "Not Analyzed") asking if you would like the offending folders to be removed from the policy.
Reasons why folders cannot be analyzed are listed below:
In order to ANALYZE a table, you need one of the following:
Where one of the underlying tables that make up a folder does not fit the above rules, that folder will not be analyzed. This means that if one or more tables within a folder cannot be analyzed, the whole folder cannot be analyzed.
How you fix this depends on why a folder is marked as invalid.
To display the error message associated with an invalid folder go to View | Validate Folders.
If a folder refers to a table that resides on a database accessed over a DB-Link then ANALYZE will fail. This operation is not supported in the Oracle Server.
A Discoverer folder can contain more than one underlying database table and/or view. Getting a full set of the underlying tables may be impossible. For example:
This only applies to the server dynamic tables (e.g. the V$ tables, and many DBA_tables). Not all these views and tables resolve to physical tables; some of them are stored in memory, thus they cannot be analyzed.
Although you may run queries on this kind of table/view, in practice it makes more sense not to have summaries built on them as the summarized data would soon be out of date.
The next step of the wizard requires you to allocate space for summaries by specifying:
The graph plots the expected performance gain from allocating a certain amount of space for summaries.
The information is calculated and displayed here as a result of the folder analysis carried out in the previous step. Changes you make in the Advanced Settings dialog might cause Discoverer to recalculate the graph.
We recommend you place summaries in a separate tablespace specifically intended for summary data. If such a tablespace does not exist, we strongly recommend you do not use the SYSTEM or TEMP tablespaces.
NOTE: If there is insufficient available space displayed below the Tablespace to use for summaries field, this does not matter if you have set your tablespace to autoextend. With your tablespace set to autoextend the extra space needed will be added automatically to the database.
This dialog lists the summaries which Discoverer recommends will be created for optimum performance gain given the space currently allocated.
Should you not wish to display this page but instead click the Finish button in one of the previous dialogs, the selected summaries will be created/removed as determined by the current ASM policy. The Recommended summaries dialog gives you a chance to view which summaries Discoverer intends to create/delete and change the included list by selecting some and deselecting others. Discoverer may mark existing summaries for deletion when they fall outside the threshold values of the current ASM policy.
Summaries recommended by Discoverer are displayed in Figure 16-10, "Recommended summaries". When you first display this dialog, the summaries that Discoverer recommends for the allocated space are ticked. This means that should you click the Finish button in the main wizard the summaries which are ticked will be either be created or deleted depending on whether Create or Delete is displayed in the Action column. You can change which summaries are created/deleted by selecting or clearing check boxes from the list of recommended summaries.
The right hand pane lists the currently selected summary's components. In Figure 16-10, "Recommended summaries" the first summary in the list is based on a number of folders; details of these folders are displayed in the right hand pane.
The total space required for the selected summaries is displayed underneath.
The method used to derive the figure for the Total space required (in Figure 16-10, "Recommended summaries") from the list of recommended summaries is as follows:
Where there are many hundreds of folders to analyze, the analysis process can take some time. This tab gives you the ability to optimize the analysis.
How do you wish to analyze the selected folders?
The default for this is Only analyze folders that have not already been analyzed. If you choose Analyze all folders regardless then every time you click Start/Continue in the Analyze dialog, all folders will be analyzed.
When would you like a folders analysis to expire?
The default for this is 30 days. If you set this field to 30 days then any folder which was analyzed over 30 days ago will be treated as if it had not been analyzed.
How would you like the analysis to be optimized?
The default for this is Max Accuracy giving 100% analysis and enabling the broadest range of suitable summaries to be created by ASM. If you are using a very large data warehouse then this setting may cause analysis to take longer. Reducing the Accuracy/Speed measure above in no way affects the accuracy of a summary, it just reduces the range of summaries created by ASM.
If set to Max Speed then around 10% analysis will occur (this reduces the range of summaries created by ASM) but speed of analysis will improve.
In this tab you can select which folders to include in the ASM process. By default all folders are included, so if your system has thousands of folders then it may take some time for them all to be analyzed. If so you can use this dialog to choose just the most important folders (for instance the fact tables) which will improve the analysis time (due to having less folders to analyze).
The available folders are listed in the left hand list box, the included folders are in the right hand list box. To include a folder you can drag and drop between list boxes or you can use the buttons.
Folders that have already been analyzed are shown with a green tick in the folder icon.
This tab enables you to select one or more users, for the current EUL, to be available for the ASM process. If a single user is selected then the queries made by that user can be included with ASM only using the Query Performance Prediction statistics (QPP) generated for that user. This means that ASM can be set up to create summaries from the queries of a restricted list of users cutting down the amount of QPP that feeds into the ASM process. The default is for all users to be selected. To exclude a user drag the user from the Included column into the Available column, or highlight one or more users and use the buttons.
This tab enables you to select which queries are made available to be considered for summary recommendation.
Move the slider to the position that best reflects the nature of your users' queries
This tab enables you to purge summary data that is not being used. This is a significant requirement for a system that automatically manages summary data. At some point the summaries will need to be cleaned up and this dialog allows you to determine what is removed.
Removing unused summaries means that the space they occupied can be re-used by alternative (more appropriate) summaries.
Delete specific summary folders if:
These settings define the circumstances where summaries will be deleted.
The default setting is to allow all summaries to be deleted except summaries created via existing non-ASM methods. Such summaries are automatically placed on to the exclusion list (the Selected column) (see Section 184.108.40.206.1, "External summary deletion message").
Summaries in the Available column will be deleted subject to the settings made above
This means that those summaries you want to create yourself, without the recommendations of ASM, will be placed on the exclusion list and not deleted by ASM, unless explicitly removed from that list.
Always retain the following summaries:
If you select this check box summaries in the Selected column will be retained.
If you clear this check box summaries may be deleted subject to entries made in the Delete specific summary folders if: section (see Figure 16-16, "Change default settings - Delete summaries" above).
Discoverer does not delete external summary data as it does not own it, also deleting the meta data associated with external summaries would not yield any great space gain which is why it is not done.
If you try to move an external summary from the Selected to the Available column a warning message appears and the summary remains in the Selected column.
ASM summaries are the same as any other summaries and need to be refreshed at regular intervals (see Section 15.6, "Refreshing Summary Folders"). This is also achieved when ASM ia run via the command line (see Section 220.127.116.11, "Using the command line" for further details).
If you are using an Oracle 8.1.6 database you can employ the dba facilities to carry out a refresh.