Oracle Cloud Infrastructure Documentation

DBMS_AUTO_PARTITION Package

The DBMS_AUTO_PARTITION package provides administrative routines for managing automatic partitioning of schemas and tables.

  • CONFIGURE Procedure
    This procedure configures settings for automatic partitioning in Autonomous Database.
  • VALIDATE_CANDIDATE_TABLE Function
    This function checks if the given table is a valid candidate for automatic partitioning in Autonomous Database.
  • RECOMMEND_PARTITION_METHOD Function
    This function returns a recommendation ID that can be used with APPLY_RECOMMENDATION procedure to apply the recommendation, or can be used with DBA_AUTO_PARTITION_RECOMMENDATIONS view to retrieve details of the recommendations for automatic partitioning in Autonomous Database.
  • APPLY_RECOMMENDATION Procedure
    This procedure applies the given recommendation in an Autonomous Database.
  • REPORT_ACTIVITY Function
    This function returns a report of the automatic partitioning operations executed during a specific period in an Autonomous Database.
  • REPORT_LAST_ACTIVITY Function
    This function returns a report of the most recent automatic partitioning operation executed in an Autonomous Database.

Parent topic: Autonomous Database Supplied Package Reference

CONFIGURE Procedure

This procedure configures settings for automatic partitioning in Autonomous Database.

Syntax

DBMS_AUTO_PARTITION.CONFIGURE (
     PARAMETER_NAME     IN VARCHAR2,
     PARAMETER_VALUE    IN VARCHAR2,
     ALLOW              IN BOOLEAN    DEFAULT TRUE);

Parameters

Parameter Description

PARAMETER_NAME

Name of the automatic partitioning configuration parameter to update. It can have one of the following values:

  • AUTO_PARTITION_MODE
  • AUTO_PARTITION_SCHEMA
  • AUTO_PARTITION_TABLE
  • AUTO_PARTITION_REPORT_RETENTION
AUTO_PARTITION_MODE sets the mode of automatic partitioning operation, and has one of the following values:
  • IMPLEMENT: In this mode, automatic partitioning generates a report and modifies the existing table using the recommended partition method.
  • REPORT ONLY: In this mode, automatic partitioning generates a report but existing tables are not modified. This is the default value.
  • OFF: In this mode, automatic partitioning is prevented from generating, considering, or applying recommendations. It does not disable existing automatic partitioned tables.
AUTO_PARTITION_SCHEMA sets schemas to include or exclude from using automatic partitioning. Its behavior is controlled by the allow parameter. The automatic partitioning process manages two schema lists.
  1. Inclusion list is the list of schemas, case-sensitive, that can use automatic partitioning.
  2. Exclusion list is the list of schemas, case-sensitive, that cannot use automatic partitioning.

Initially, both lists are empty, and all schemas in the database can use automatic partitioning. If the inclusion list contains one or more schemas, then only the schemas listed in the inclusion list can use automatic partitioning. If the inclusion list is empty and the exclusion list contains one or more schemas, then all schemas use automatic partitioning except the schemas listed in the exclusion list. If both lists contain one or more schemas, then all schemas use automatic partitioning except the schemas listed in the exclusion list. AUTO_PARTITION_TABLE sets tables to include or exclude from using auto partitioning. The parameter value is <schema_name>.<table_name>. The automatic partitioning process manages two table lists.

  1. Inclusion list is the list of tables, case-sensitive, that can use automatic partitioning.
  2. Exclusion list is the list of tables, case-sensitive, that cannot use automatic partitioning.

Initially, both lists are empty, and all tables in the database can use automatic partitioning. If the inclusion list contains one or more tables, then only the tables listed in the inclusion list can use automatic partitioning. If the inclusion list is empty and the exclusion list contains one or more tables, then all tables use automatic partitioning except the tables listed in the exclusion list. If both lists contain one or more tables, then all tables use automatic partitioning except the tables listed in the exclusion list. If a table is not on either list, the schema inclusion and exclusion lists decide if a table is a candidate table for automatic partitioning. If there is a conflict between the schema level lists and the table level lists, the table level lists take precedence.

To remove all tables from inclusion and exclusion lists run:
DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_TABLE', NULL);

AUTO_PARTITION_REPORT_RETENTION sets the number of days for which automatic partitioning logs are retained in the database before they are deleted. An automatic partitioning report cannot be generated for a period beyond the value specified for this value. Default value is 90 days.

PARAMETER_VALUE

Value for the configuration setting specified in parameter_name. When set to NULL, the configuration setting is assigned its default value.

ALLOW
Applicable only for the AUTO_PARTITION_SCHEMA or AUTO_PARTITION_TABLE configuration settings with one of the following values:
  • TRUE adds specified schema or table to the inclusion list.
  • FALSE removes specified schema or table from the exclusion list.
  • NULL removes specified schema or table from the list to which currently assigned.
Refer to the description of the AUTO_PARTITION_SCHEMA and AUTO_PARTITION_TABLE configuration settings for more information about inclusion lists and exclusion lists.

Usage Notes

  • You can check the current setting for automatic partitioning configuration using the following SQL:

    SELECT * FROM DBA_AUTO_PARTITION_CONFIG;

  • Unlike automatic indexing, automatic partitioning does not run periodically as a background task. Automatic partitioning only runs when you invoke it using the DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD function.

VALIDATE_CANDIDATE_TABLE Function

This function checks if the given table is a valid candidate for automatic partitioning in Autonomous Database.

Valid Candidate

To be a valid candidate, the following tests must pass:
  • Table passes inclusion and exclusion tests specified by AUTO_PARTITION_SCHEMA and AUTO_PARTITION_TABLE configuration parameters.
  • Table exists and has up-to-date statistics.
  • Table is at least 64 GB.
  • Table has 5 or more queries in the SQL tuning set that scanned the table.
  • Table does not contain a LONG data type column.
  • Table is not manually partitioned.
  • Table is not an external table, an internal/external hybrid table, a temporary table, an index-organized table, or a clustered table.
  • Table does not have a domain index or bitmap join index.
  • Table is not an advance queuing, materialized view, or flashback archive storage table.
  • Table does not have nested tables, or certain other object features.
Returns:
  • VALID if the table is a valid candidate for autonomous partitioning
  • INVALID: <reason> if the table is not a valid candidate for autonomous partitioning, and <reason> is a string describing why the table is not a valid candidate.

Syntax

DBMS_AUTO_PARTITION.VALIDATE_CANDIDATE_TABLE
    ( SQLSET_OWNER  IN VARCHAR2   DEFAULT 'SYS',
      SQLSET_NAME   IN VARCHAR2   DEFAULT 'SYS_AUTO_STS',
      TABLE_OWNER   IN VARCHAR2,
      TABLE_NAME    IN VARCHAR2)
RETURN VARCHAR2;

Parameters

Parameter Description

SQLSET_OWNER, SQLSET_NAME

Name of SQL tuning set representing the workload to be evaluated.

TABLE_OWNER, TABLE_NAME

Name of a table to validate as a candidate for automatic partitioning.

Usage Notes

  • As an example, you can check the validity of a sample table, LINEORDER in schema TEST, with the following SQL:
    SELECT DBMS_AUTO_PARTITION.VALIDATE_CANDIDATE_TABLE
    ( TABLE_OWNER => 'TEST',
      TABLE_NAME  => 'LINEORDER')
FROM DUAL;

RECOMMEND_PARTITION_METHOD Function

This function returns a recommendation ID that can be used with APPLY_RECOMMENDATION procedure to apply the recommendation, or can be used with DBA_AUTO_PARTITION_RECOMMENDATIONS view to retrieve details of the recommendations for automatic partitioning in Autonomous Database.

Syntax

DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_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,
      TIME_LIMIT       IN INTERVAL DAY TO SECOND DEFAULT INTERVAL '1' DAY,
      REPORT_TYPE      IN VARCHAR2   DEFAULT 'TEXT',   
      REPORT_SECTION   IN VARCHAR2   DEFAULT 'SUMMARY',
      REPORT_LEVEL     IN VARCHAR2   DEFAULT 'TYPICAL')
  RETURN RAW;

Parameters

Parameter Description

SQLSET_OWNER, SQLSET_NAME

Name of SQL tuning set representing the workload to be evaluated.

TABLE_OWNER, TABLE_NAME

Name of a table to validate as a candidate for automatic partitioning.

TIME_LIMIT

When the function chooses the tables for which to generate recommendations, TABLE_OWNER and TABLE_NAME are NULL), parameter limits how long the function runs before it stops looking for new candidate tables to partition. Once started processing a table, process will not terminate. It is expected that the function may run longer than this parameter. If this parameter is NULL there is no time limit. The default is 1 day.

REPORT_TYPE

Used to generate report for recommended partition method. See REPORT_ACTIVITY Function for details.

REPORT_SECTION

Used to generate persistent report for recommended partition method. See REPORT_ACTIVITY Function for details.

REPORT_LEVEL

Used to generate report for recommended partition method. See REPORT_ACTIVITY Function for details.

Usage Notes

  • The AUTO_PARTITION_MODE controls the actions taken by this function:
    • IMPLEMENT: In this mode, automatic partitioning generates a report and modifies the existing table using the recommended partition method.
    • REPORT ONLY: In this mode, automatic partitioning generates a report generated but existing tables are not modified. This is the default value.
    • OFF: In this mode, automatic partitioning prevented from producing, considering, or applying new recommendations. It does not disable existing automatic partitioned tables.

  • Unlike automatic indexing, automatic partitioning does not run periodically as a background task. Automatic partitioning only runs when you invoke it using the DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD function.

Return Values

This function returns a recommendation ID that can be used as follows: DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION to apply the recommendation,

  • Use with DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION to apply the recommendation.
  • Use with DBA_AUTO_PARTITION_RECOMMENDATIONS view to retrieve details of the recommendations. For example:
    SELECT PARTITION_METHOD, PARTITION_KEY
     FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
     WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID;

APPLY_RECOMMENDATION Procedure

This procedure applies the given recommendation in an Autonomous Database.

Syntax

DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION
    ( RECOMMENDATION_ID  IN RAW,
      TABLE_OWNER        IN VARCHAR2   DEFAULT NULL,
      TABLE_NAME         IN VARCHAR2   DEFAULT NULL);

Parameters

Parameter Description

RECOMMENDATION_ID

Recommendation ID returned from RECOMMEND_PARTITION_METHOD function or queried from DBA_AUTO_PARTITION_RECOMMENDATIONS view.

TABLE_OWNER, TABLE_NAME
When a single recommendation ID has recommendations for multiple tables, this optional parameter allows you to control which tables are partitioned.
  • If parameters are NULL, partition all tables recommended in the given recommendation ID.
  • If a table name is given, partition only the named table.
  • If either TABLE_OWNER or TABLE_NAME is NOT NULL, they must both be NOT NULL.

Usage Note:

Regardless of AUTO_PARTITION_MODE, this procedure raises an ORA-20000: recommendation_id was not found if either there are no accepted recommendations associated with the RECOMMENDATION_ID, or all accepted recommendations associated with the RECOMMENDATION_ID have already been applied. The first case applies if RECOMMENDATION_ID was generated with AUTO_PARTITION_MODE = OFF. The second case applies if RECOMMENDATION_ID was generated with AUTO_PARTITION_MODE = IMPLEMENT.

REPORT_ACTIVITY Function

This function returns a report of the automatic partitioning operations executed during a specific period in an Autonomous Database.

Syntax

DBMS_AUTO_PARTITION.REPORT_ACTIVITY
    ( ACTIVITY_START     IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
      ACTIVITY_END       IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
      TYPE               IN VARCHAR2                 DEFAULT 'TEXT',
      SECTION            IN VARCHAR2                 DEFAULT 'ALL',
      LEVEL              IN VARCHAR2                 DEFAULT 'TYPICAL')
RETURN CLOB;

Parameters

Parameter Description

ACTIVITY_START

Starting time automatic partitioning operations use for the report. If no value is specified, or NULL is specified, the report is generated for the last automatic partitioning operation that was executed.

ACTIVITY_END

Ending time automatic partitioning operations use for the report. If no value is specified, or NULL is specified, then the report is generated for the last automatic partitioning operation that was executed.

TYPE
Format of the report that has one of the following values:
  • TEXT (default)
  • HTML
  • XML

SECTION
Sections to include in the report that has one of the following values:
  • SUMMARY - Include only the workload summary in the report
  • ALL = Include all the sections in the report. (default)

level
Level of information to include in the report that has one of the following values:
  • TYPICAL - Include typical automatic partitioning information in the report (default).
  • CHANGED - Include only SQL with changed performance in the report.
  • IMPROVED - Include only SQL with improved performance in the report.
  • REGRESSED - Include only SQL with regressed performance in the report.
  • UNCHANGED - Include only SQL with unchanged performance in the report.
  • ALL - Include all automatic partitioning information in the report.

Usage Notes

Returns: A performance analysis report for workload executed on database after recommendation is applied. This report is not stored persistently with the recommendation.

REPORT_LAST_ACTIVITY Function

This function returns a report of the most recent automatic partitioning operation executed in an Autonomous Database.

Syntax

DBMS_AUTO_PARTITION.REPORT_LAST_ACTIVITY
    ( TYPE    IN VARCHAR2 DEFAULT 'TEXT',
      SECTION IN VARCHAR2 DEFAULT 'ALL',
      LEVEL   IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;

Parameters

Parameter Description

TYPE

The output format of the report, see REPORT_ACTIVITY Function for information.

SECTION

The sections included in the report, see REPORT_ACTIVITY Function for information.

LEVEL

The level of information included in the report, see REPORT_ACTIVITY Function for information.

Usage Notes

Returns: A performance analysis report for workload executed on database after latest recommendation is applied. This report is not stored persistently with the recommendation.