33 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:
33.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.
33.2 Summary of DBMS_AUTO_CLUSTERING Subprograms
This table lists the DBMS_AUTO_CLUSTERING
subprograms and their descriptions.
Table 33-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 |
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 |
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. |
33.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 33-2 CONFIGURE Procedure Parameters
Parameter | Description |
---|---|
|
Automatic clustering configuration setting. It can have one of the following values:
|
|
Value for the configuration setting specified in the When it is set to |
|
This parameter is applicable only for the
AUTO_CLUSTERING_SCHEMA and it can have one of the following values:
|
Footnote 1 Default value for auto partitioning is 90 days and 373 days for auto indexing.
33.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 33-3 RECOMMEND_CLUSTERING_METHOD Procedure Parameters
Parameter | Description |
---|---|
|
Owner of the SQL Tuning set representing the workload to be considered. Default is |
|
Name of the SQL Tuning set representing the workload to be considered. Default is |
|
Name of a table for generating recommendation. |
|
Type parameter for generating report for the recommendation activity. Default is |
|
Section parameter used to generate persistent report for the recommended clustering method. Default is |
|
Level parameter used to generate report for the recommended clustering method. Default is |
|
Verification parameter indicates whether the generated recommendation is verified as part of |
33.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 33-4 VERIFY_RECOMMENDATION Procedure Parameters
Parameter | Description |
---|---|
|
Recommendation ID returned from |
|
Name of a owner to validate as a candidate for automatic clustering within the given recommendation id. |
|
Name of a table to validate as a candidate for automatic clustering within the given recommendation id. |
33.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,
clustering_ddl IN VARCHAR2 DEFAULT NULL,
zonemap_ddl IN VARCHAR2 DEFAULT NULL,
apply_mode IN VARCHAR2 DEFAULT 'FULL',
zonemap_creation IN BOOLEAN DEFAULT TRUE);
Parameters
Table 33-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. |
clustering_ddl |
Clustering DDL to add to the target table. table_owner and table_name are NULL if they are extracted from the specified DDL.
|
zonemap_ddl |
Zonemap DDL to use in the target table. table_owner and table_name are NULL if they are extracted from the specified DDL.
|
apply_mode |
Mode to specify the clustering to be performed:
|
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 .
|
33.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 33-6 GET_RECOMMENDATION Function Parameters
Parameter | Description |
---|---|
|
Owner of the table to cluster |
|
Name of the table to cluster |
33.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 33-7 REPORT ACTIVITY Function Parameters
Parameter | Description |
---|---|
|
Time from when automatic clustering operations are considered for the report. If no value is specified or |
|
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.
|
|
Format of the report. It can have one of the following values:
TEXT |
section |
Sections to include in the report. It can have a combination of the following values:
|
level |
Level of automatic clustering information to include in the report. It can have one of the following values:
|
33.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 33-8 REPORT_LAST_ACTIVITY Function Parameters
Parameter | Description |
---|---|
|
Format of the report. It can have one of the following values:
The default value is |
|
Sections to include in the report. It can have a combination of the following values:
|
|
Level of automatic clustering information to include in the report. It can have one of the following values:
|