34 DBMS_AUTO_CLUSTERING

The DBMS_AUTO_CLUSTERING package contains a collection of subprograms that recommend clustering and zone map schemes for improved query performance.

This chapter contains the following topics:

34.1 DBMS_AUTO_CLUSTERING Overview

The DBMS_AUTO_CLUSTERING package generates clustering recommendations, such as tables to cluster and clustering schemes. It also verifies the impact of the clustering recommendation and implements the clustering recommendation.

34.2 Summary of DBMS_AUTO_CLUSTERING Subprograms

This table lists the DBMS_AUTO_CLUSTERING subprograms and their descriptions.

Table 34-1 DBMS_AUTO_CLUSTERING Package Subprograms

Subprogram Description
CONFIGURE Procedure

Configures settings related to automatic clustering.
RECOMMEND_CLUSTERING_METHOD Function

Generates a recommendation. No recommendation will be returned if the recommendation criteria are not met. The generated recommendation is stored in the DBA_AUTO_CLUSTERING_RECOMMENDATION view. The function will return the recommendation ID when a recommendation is generated and NULL when no recommendation is generated.

VERIFY_RECOMMENDATION Procedure

Verifies the performance of the clustering recommendation. It retrieves the information used for generating the recommendation based on input arguments. This procedure finds the original table and the SQL tuning set used for the recommendation task. It creates a table and applies the clustering recommendation to the table. It runs a workload against the clustered table and the original table and returns a detailed report on performance benefits. The verification result can be ACCEPTED or REJECTED.

APPLY_RECOMMENDATION Procedure Clusters an input table using the recommendation (identified by recommendation_id). The value of the incremental column specifies whether to use full clustering or incremental clustering.
GET_RECOMMENDATION Function

Returns the latest recommendation ID for a given input.
REPORT_ACTIVITY Function

Returns a report of the auto-clustering operations executed during a specific period in a database.
REPORT_LAST_ACTIVITY Function Returns a report of the last automatic indexing operation executed in a database.

34.2.1 CONFIGURE Procedure

This procedure configures settings related to automatic clustering and zone map.

Syntax 

DBMS_AUTO_CLUSTERING.CONFIGURE (
     parameter_name      IN VARCHAR2, 
     parameter_value     IN VARCHAR2,
     allow               IN BOOLEAN DEFAULT TRUE);

Parameters

Table 34-2 CONFIGURE Procedure Parameters

Parameter Description

parameter_name

Automatic clustering configuration setting. It can have one of the following values:

  • AUTO_CLUSTERING_SCHEMA: Schemas to include or exclude from the automatic clustering recommendation. Its behavior is controlled by allow parameter.

    The automatic clustering process manages two schema lists – the inclusion list and the exclusion list. The inclusion list contains the schemas that can use cluster tables. The exclusion list contains the schemas that cannot use cluster tables. When automatic clustering is enabled for a database, both lists are initially empty, and all schemas in the database can use auto-clustering in all modules in the database.

    If the inclusion list contains at least one module, then only the modules listed in the inclusion list can use auto-clustering.

    If the inclusion list is empty and the exclusion list contains at least one module, then all the modules can use auto-clustering, except the module listed in the exclusion list.

    If both the lists (the inclusion list and the exclusion list) contain at least one module, then all the modules can use auto-clustering, except the modules listed in the exclusion list.

  • AUTO_CLUSTERING_REPORT_RETENTION: Number of days for which automatic clustering logs are retained in the database before they are deleted. As automatic clustering report is generated based on these logs, it cannot be generated for a period beyond the value specified for AUTO_CLUSTERING_REPORT_RETENTION. Default value is 31 daysFoot 1.

  • AUTO_CLUSTERING_WORKLOAD_WINDOW: Maximum number of hours for which auto-clustering will investigate queries from the latest snapshots from AutoSTS to make recommendations. For example, if the parameter is 24, then auto-clustering will look at statements in latest 24 hours from the AutoSTS.

    Valid value is an integer value between 1 and 8760 (24*36). Default value is 24.

  • AUTO_CLUSTERING_WORKLOAD_MIN_TIME: Minimum database time, in seconds, for a query to be considered for auto-clustering recommendation. Queries below this threshold will not be considered for recommendations.

    Valid value is an integer value between 0 and 3600 (1 hour). Default value is 120 (2 minutes).

parameter_value

Value for the configuration setting specified in the parameter_name.

When it is set to NULL, the configuration setting is assigned the default value.

allow
This parameter is applicable only for the AUTO_CLUSTERING_SCHEMA and it can have one of the following values:
  • TRUE: Add the specified modules to the inclusion list.
  • FALSE: Add the specified modules to the exclusion list.
  • NULL: Remove the specified modules from the list to which it is currently added.

Footnote 1 Default value for auto partitioning is 90 days and 373 days for auto indexing.

34.2.2 RECOMMEND_CLUSTERING_METHOD Function

This function generates a recommendation. No recommendation will be returned if the recommendation criteria are not met. The generated recommendation can be retrieved from the DBA_AUTO_CLUSTERING_RECOMMENDATION view. The function will return the recommendation ID when a recommendation is generated and NULL when no recommendation is generated.

Syntax

DBMS_AUTO_CLUSTERING.RECOMMEND_CLUSTERING_METHOD (
     sqlset_owner      IN  VARCHAR2 DEFAULT ‘SYS’,    
     sqlset_name       IN  VARCHAR2 DEFAULT ‘SYS_AUTO_STS’, 
     table_owner       IN  VARCHAR2 DEFAULT  NULL,    
     table_name        IN  VARCHAR2 DEFAULT  NULL, 
     report_type       IN  VARCHAR2 DEFAULT ‘TEXT’,
     report_section    IN  VARCHAR2 DEFAULT ‘SUMMARY’,
     report_level      IN  VARCHAR2 DEFAULT ‘TYPICAL’,
     verification      IN  BOOLEAN  DEFAULT  TRUE)
RETURN RAW;

Parameters

Table 34-3 RECOMMEND_CLUSTERING_METHOD Procedure Parameters

Parameter Description

sqlset_owner

Owner of the SQL Tuning set representing the workload to be considered. Default is SYS.

sqlset_name

Name of the SQL Tuning set representing the workload to be considered. Default is SYS_AUTO_STS.

table_owner, table_name

 Name of a table for generating recommendation.

report_type

Type parameter for generating report for the recommendation activity. Default is TEXT.

report_section

Section parameter used to generate persistent report for the recommended clustering method. Default is SUMMARY.

report_level

Level parameter used to generate report for the recommended clustering method. Default is TYPICAL.

verification

Verification parameter indicates whether the generated recommendation is verified as part of RECOMMEND_CLUSTERING_METHOD. Default is TRUE, which means, the generated recommendation is verified to see how it performs based on the workload. If it is set to FALSE, only recommendations are generated, and their performance against workload and schema is not verified.

34.2.3 VERIFY_RECOMMENDATION Procedure

This procedure verifies the performance of the clustering recommendation. It retrieves the information used for generating the recommendation based on the input arguments. This procedure finds the original table and the SQL tuning set used for the recommendation task identified by the recommendation ID. It creates a table and applies the clustering recommendation to the table. It runs a workload against the clustered table and the original table and returns a detailed report on performance benefits. The verification result can be ACCEPTED or REJECTED.

Syntax

DBMS_AUTO_CLUSTERING.VERIFY_RECOMMENDATION (
     recommendation_id      IN  RAW,    
     table_owner       	    IN  VARCHAR2 DEFAULT NULL,    
     table_name             IN  VARCHAR2 DEFAULT NULL)
RETURN CLOB;

Parameters

Table 34-4 VERIFY_RECOMMENDATION Procedure Parameters

Parameter Description

recommendation_id

Recommendation ID returned from RECOMMEND_CLUSTERING_METHOD function that generates recommendation for several tables in a given workload.

table_owner

Name of a owner to validate as a candidate for automatic clustering within the given recommendation id.

table_name

Name of a table to validate as a candidate for automatic clustering within the given recommendation id.

34.2.4 APPLY_RECOMMENDATION Procedure

This procedure clusters an input table using the recommendation (identified by recommendation_id). The value of the incremental column specifies whether to use full clustering or incremental clustering. Full clustering clusters an input table after applying the recommended clustering clause and performs an alter table move online to cluster data in the table. Incremental clustering applies the recommended clustering clause, and Automatic Data Compression (ADO) background will cluster data in the background.

Syntax

DBMS_AUTO_CLUSTERING.APPLY_RECOMMENDATION(
    recommendation_id IN RAW DEFAULT NULL,
    table_name        IN VARCHAR2 DEFAULT NULL,
    table_owner       IN VARCHAR2 DEFAULT NULL,
    apply_mode        IN VARCHAR2 DEFAULT 'FULL',
    zonemap_creation  IN BOOLEAN DEFAULT TRUE);

Parameters

Table 34-5 APPLY_RECOMMENDATION Procedure Parameters

Parameter Description
recommendation_id

The task ID that recommends clustering for the given table. If not specified, the clustering recommendation made in the latest task will be used.

table_name Name of the input table.
table_owner Owner of the input table.
apply_mode

Mode to specify the clustering to be performed:

  • FULL: full clustering using online redefinition
  • INCREMENTAL: background incremental clustering
zonemap_creation If this value is set to FALSE, it will not create a zonemap. It will only apply the clustering clause. Default is TRUE.

34.2.5 GET_RECOMMENDATION Function

This function returns the latest recommendation ID for a given input.

Syntax

DBMS_AUTO_CLUSTERING.GET_RECOMMENDATION (
     table_owner        IN  VARCHAR2,    
     table_name         IN  VARCHAR2, 
RETURN RAW;

Parameters

Table 34-6 GET_RECOMMENDATION Function Parameters

Parameter Description

table_owner

Owner of the table to cluster

table_name

Name of the table to cluster

34.2.6 REPORT ACTIVITY Function

This function returns a report of the auto-clustering operations executed during a specific period in a database.

Syntax 

DBMS_AUTO_CLUSTERING.REPORT_ACTIVITY (
     activity_start IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP-1,       
     activity_end   IN  TIMESTAMP WITH TIME ZONE DEFAULT SYSTIMESTAMP,    
     type           IN  VARCHAR2 DEFAULT 'TEXT',    
     section        IN  VARCHAR2 DEFAULT 'ALL',    
     level          IN  VARCHAR2 DEFAULT 'TYPICAL') 
RETURN CLOB;

Parameters

Table 34-7 REPORT ACTIVITY Function Parameters

Parameter Description

activity_start

Time from when automatic clustering operations are considered for the report. If no value is specified or NULL is specified, then the report is generated for the last automatic clustering operation that was executed.

activity_end

 Time until the automatic clustering operations are considered for the report. If no value is specified or NULL is specified, then the report is generated for the last automatic clustering operation that was executed.

type
Format of the report. It can have one of the following values:
  • TEXT
  • HTML
  • XML
The default value is TEXT
section Sections to include in the report. It can have a combination of the following values:
  • SUMMARY: Include only the summary details section in the report.
  • ALL: Include all the sections in the report. This is the default value.
level Level of automatic clustering information to include in the report. It can have one of the following values:
  • BASIC: Include basic automatic clustering information in the report.
  • TYPICAL: Include typical automatic clustering information in the report. This is the default value.
  • ALL: Include all the automatic clustering information in the report.

34.2.7 REPORT_LAST_ACTIVITY Function

This function returns a report of the last automatic clustering operation executed in a database.

Syntax

DBMS_AUTO_CLUSTERING.REPORT_LAST_ACTIVITY (
     type             IN  VARCHAR2 DEFAULT 'TEXT',    
     section          IN  VARCHAR2 DEFAULT 'ALL',    
     level            IN  VARCHAR2 DEFAULT 'TYPICAL') 
RETURN CLOB;

Parameters

Table 34-8 REPORT_LAST_ACTIVITY Function Parameters

Parameter Description

type

Format of the report. It can have one of the following values:

  • TEXT

  • HTML

  • XML

The default value is TEXT.

section

Sections to include in the report. It can have a combination of the following values:

  • SUMMARY: Include only the summary details section in the report.

  • ALL: Include all the sections in the report. This is the default value.

level

Level of automatic clustering information to include in the report. It can have one of the following values:

  • BASIC: Include basic automatic clustering information in the report.

  • TYPICAL: Include typical automatic clustering information in the report. This is the default value.

  • ALL: Include all the automatic clustering information in the report.