Run the Summary Recommendations Tool¶
This section explains in detail the third of the necessary steps to configure the automatic summary recommendations described in section Process Setup Overview.
Open the Administration Tool and navigate to Tools -> Query optimizations -> Summary Recommendations. This option is only enabled for global Admin users and it must be allowed by the server license.
Before clicking on ‘Generate recommendations’ there are several options that you can tune:
Recommendations time limit (minutes): The maximum time in minutes the process is allowed to run.
Minimum query duration (ms): Consider only those queries from the logs that run for more than this minimum duration. This is useful to avoid analyzing queries that already have good performance and focus on the ones you really want to optimize.
Initial date to consider: Consider only those queries of the workload that started after this date. You can specify a date literal like ‘2021-05-01’ or a more complex expression like addweek(current_date(), -1)
Last date to consider: Consider only those queries of the workload that started before this date (inclusive).
Maximum size for summary candidates (number of rows): The recommender will consider summaries which estimated size is under this limit.
Maximum number of summary candidates: Maximum number of summaries you want the process to recommend.
Database for recommended summaries: Database in Virtual DataPort where the process will place the recommended summaries. This summaries will only exist in the Virtual DataPort server until the user explicitly decides to materialize them.
Folder recommended summaries: Folder in the selected database where the process will place the recommended summaries.
Analyze queries from these databases: Consider only those queries executed from these databases (any if empty). This is useful if you are trying to optimize queries from a particular client that always connects using a specific database.
Views to consider: If some views are selected, all summary candidates considered will include at least one of them. This is useful as a hint to narrow the process around interesting views like facts tables.
Preferred target data sources: You can select those data sources where you would prefer to store the recommended summaries. If empty, the process will consider the data source configured in the server cache configuration and the data source containing the biggest views on each query.
Excluded target data source: Select those data sources where the process should not consider to store any summary.
Things to take into account:
A very wide date range will consider more queries, which will potentially generate better recommendations, but it will also need more time to complete. In general, it is better to adjust the other parameters first to consider just valuable queries, and then choose the date range that best fits your scenario. As a good practice, consider instead of running the process once for all queries on the server, focus each time on a specific use case and adjust the parameters for that one.
Example: If you want to optimize queries from users using a BI tool that accesses using the database ‘bi_tool’ you can:
Configure ‘Analyze queries from these databases’ to select the database bi_tool.
Configure ‘Views to consider’ to select the facts tables
Select your preferred data source
Set the minimum query duration according to that use case. For instance, maybe it is reasonable to select something higher that 100ms, like 10000.
Finally, to see more details about the progress during the recommendations process you can open the Query Monitor utility under Tools. Select the request starting with “CREATE SUMMARY RECOMMENDATION” and review the execution plan.
If the property ‘Read log information time’ is too high you can consider caching the views accessing the logs implementing interfaces i_queryblock_notification and i_request_notification.
Process the Results¶
The process will select the best summaries for the workload and for each one:
It creates the summary metadata in the database and folder you have specified in Database for recommended summaries and Folder recommended summaries. Notice that the table does not exist yet in the remote data source and no data has been loaded yet.
It returns useful information about that summary:
Name: The name of the summary. The name starts with ‘sum’ followed by the possition of that summary in the ranking and the biggest views included. You can click on it to open the summary and review its information. Remember at this point the summary does not exist in the remote data source yet and it will not be considered by the query optimizer until is loaded. That is the reason why property ‘Valid for query acceleration’ has the value ‘Needs refresh’ and you get an error if you execute the summary directly.
Data source: The data source that is configured to store the data for that summary in case you decide to load it.
Views: Views used in the query definition of the summary.
Estimated size: Estimated size of the summary. A Medium estimated size would be in the order of hundred thousands while Large is in the order of millions.
Usage (Num.queries): The number of queries from the selected workload that benefit from the summary.
Absolute gain factor: This represents the number of times the estimated cost of the workload gets reduced by using this summary only compared to the original workload without summaries.
Relative gain factor: This represents the number of times the estimated cost of the workload gets reduced by using this summary and the previous ones in the ranking compared to the estimated cost using just the previous summaries.
The result shows the summaries in order in terms of cost benefit and size, which means the first one in the result is estimated as the best option. Based on the results, you can review the recommended summaries and decide which ones you want to persist. For the ones that you want to persist, you should first edit each one to review the selected data source, catalog and schema are the most suitable ones and modify if necessary. We encourage you to also review the recommendations on section Recommendations When Using Summaries. Then, you can either click Load to create the summaries on that same database or click ‘Export’ to save the summary VQL. Once you load a summary, it is available for the query optimizer to accelerate queries on that server. If you prefer to create a summary on a different environment you can include your selected summaries in the revision of your next promotion. Visit the Promotions section of the Solution Manager Guide for more details.