Manage Automatic Partitioning on Autonomous Database

Automatic partitioning analyzes and automates partition creation for tables and indexes of a specified schema to improve performance and manageability in Autonomous Database. Automatic partitioning, when applied, is transparent and does not require any user interaction or maintenance.

Topics

Note:

Automatic partitioning does not interfere with existing partitioning strategies and is complementary to manual partitioning in Autonomous Database. Manually partitioned tables are excluded as candidates for automatic partitioning.

About Automatic Partitioning

Automatic partitioning in Autonomous Database analyzes the application workload and automatically applies partitioning to tables and their indexes to improve performance or to allow better management of large tables.

Finding appropriate partitioning strategies requires deep knowledge of the application workload and the data distribution. When you perform manual partitioning, you must analyze your workload and make choices about how to apply partitioning to tables and indexes to improve the performance of applications. Automatic partitioning enables Autonomous Database users to benefit from partitioning without performing manual schema and workload analysis.

Automatic partitioning uses a single-column partition key combined with single-level partitioning. Automatic partitioning does not support more complex partitioning strategies such as multi-column partitioned tables or composite partitioning.

Automatic partitioning chooses from the following partition methods:

  • AUTOMATIC INTERVAL: This choice is best suited for ranges of partition key values.
  • LIST AUTOMATIC: This partitioning method applies to distinct partition key values.
  • HASH: Applies partitioning on the partition key's hash values.

See Partitioning Concepts for more information.

Automatic partitioning provides the following functionality:

  • Analyzes application workload and finds the optimal partitioning strategy to improve query performance for tables eligible for automatic partitioning.
  • Provides PL/SQL APIs for configuring automatic partitioning in a database, generating reports about partitioning findings, and generating and applying an identified partitioning strategy for a given workload.

Note:

Automatic partitioning requires explicit calls to the DBMS_AUTO_PARTITION PL/SQL APIs to recommend and apply automatic partitioning to an Autonomous Database.

How Automatic Partitioning Works

When invoked, automatic partitioning performs the following operations in Autonomous Database.

  1. Identify candidate tables for automatic partitioning by analyzing the workload for selected candidate tables.

    By default, automatic partitioning uses the workload information collected in an Autonomous Database for analysis. Depending on the size of the workload, a sample of queries might be considered.

  2. Evaluate partition schemes based on workload analysis and quantification and verification of the performance benefits:

    1. Candidate empty partition schemes with synthesized statistics are created internally and analyzed for performance.

    2. The candidate scheme with the highest estimated IO reduction is chosen as the optimal partitioning strategy and is internally implemented to test and verify performance.

    3. If a candidate partition scheme does not improve performance beyond specified performance and regression criteria, automatic partitioning is not recommended.

  3. Implement the optimal partitioning strategy, if configured to do so, for the tables analyzed by the automatic partitioning procedures.

Configure Automatic Partitioning

Use the DBMS_AUTO_PARTITIONING.CONFIGURE procedure to configure automatic partitioning options.

Enable and implement recommendations

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','IMPLEMENT');

Enable recommendations, but do not implement those recommendations

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','REPORT ONLY');

Disable new recommendations and implementation of those new recommendations

EXEC DBMS_AUTO_PARTITION.CONFIGURE('AUTO_PARTITION_MODE','OFF');

Note:

This mode does not disable existing automatically partitioned tables.

Manage schemas and tables for automatic partitioning

Use the AUTO_PARTITION_SCHEMA and AUTO_PARTITION_TABLE settings to specify schemas and tables considered for automatic partitioning.

Note:

When automatic partitioning is invoked, all schemas and tables in user-managed schemas are considered for automatic partitioning if both the inclusion and exclusion lists are empty.
  • Assuming the inclusion list and the exclusion list are empty, add the HR schema and the SH.SALES table to the exclusion list, preventing only those objects from automatic partitioning analysis.

    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
             PARAMETER_NAME  => 'AUTO_PARTITION_SCHEMA', 
             PARAMETER_VALUE => 'HR',
             ALLOW           => FALSE);
    
        DBMS_AUTO_PARTITION.CONFIGURE(
             PARAMETER_NAME  => 'AUTO_PARTITION_TABLE', 
             PARAMETER_VALUE => 'SH.SALES',
             ALLOW           => FALSE);
    END;
    /
  • After the previous example runs, use the following to remove the HR schema from the exclusion list.

    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
             PARAMETER_NAME  => 'AUTO_PARTITION_SCHEMA', 
             PARAMETER_VALUE => 'HR',
             ALLOW           => NULL);
    END;
    /
  • Use the following command to remove all schemas from the exclusion list.

    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
             PARAMETER_NAME  => 'AUTO_PARTITION_SCHEMA', 
             PARAMETER_VALUE => NULL,
             ALLOW           => TRUE);
    END;
    /
  • Assuming the inclusion and exclusion lists are empty, the following example adds the HR schema to the inclusion list. As soon as the inclusion list is no longer empty, only schemas in the inclusion list are considered.

    With this example, only the HR schema is a candidate for automatic partitioning.

    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
             PARAMETER_NAME  => 'AUTO_PARTITION_SCHEMA', 
             PARAMETER_VALUE => 'HR',
             ALLOW           => TRUE);
    END;
    /

Manage Automatic Partitioning Report Retention Period

Set the retention period for automatic partitioning reports to 365 days.

BEGIN
    DBMS_AUTO_PARTITION.CONFIGURE(
         PARAMETER_NAME  => 'AUTO_PARTITION_REPORT_RETENTION', 
         PARAMETER_VALUE => '365');
END;
/
See CONFIGURE Procedure for more information.

Use Automatic Partitioning

Describes the flow and general processes for using and managing automatic partitioning in Autonomous Database.

  1. Choose the database for automatic partitioning.

    In general, Oracle recommends using automatic partitioning in cloned or manually created databases rather than production databases. The analysis and verification of automatic partitioning using RECOMMEND_PARTITION_METHOD is potentially a resource-intensive and long running operation that can add undesirable processing to your database.

    To use a secondary database for automatic partitioning analysis, the database must have information about your workload in an internally managed SQL workload repository.

    1. Use a cloned database for automatic partitioning.

      Autonomous Database automatically collects workload information over time in an internally managed SQL workload repository maintained in the SQL Tuning Set (SYS_AUTO_STS). If you clone your production database after having run the workload for a while, the clone will have the necessary workload information. You can use automatic partitioning with such clones without any additional actions.

      See Clone an Autonomous Database Instance

    2. Use other databases for automatic partitioning.

      You can run your workload manually to collect the necessary workload information. If you manually run your workload prior to using automatic partitioning, any Autonomous Database that contains your desired schemas and data can be used for automatic partitioning after your workload was run, regardless of whether it is cloned or manually created.

  2. Recommend automatic partitioning.

    Use RECOMMEND_PARTITION_METHOD to analyze your database, specific schemas, or specific tables to identify the optimal partitioning strategy, if any. The recommendation analyzes your workload and schemas verifying performance benefits by running your workload against an internally created auxiliary table. This can be a resource-intensive and long running operation, requiring CPU and IO to create the auxiliary table and verify performance. You will also temporarily need additional space, of 1 - 1.5 times, your largest candidate table.

  3. Apply the recommendation.

    Any recommendation can be implemented with the APPLY_RECOMMENDATION procedure in the database where the recommendation analysis occurred. Alternatively, any recommendation can be extracted from the database used for analysis and applied to any database, such as a production system. The script needed for manual modification is stored in column MODIFY_TABLE_DDL in the DBA_AUTO_PARTITION_RECOMMENDATION view.

    Oracle recommends applying automatic partitioning to your database at off-peak time. While your tables will be modified to automatically partitioned tables, the conversion adds additional resource requirements to your system, such as additional CPU and IO. Automatic partitioning requires as much as 1.5 times the size of the table to being modified as additional free space, depending on concurrent ongoing DML operations on those tables.

Generate Automatic Partitioning Reports

Generate automatic partitioning reports using the REPORT_ACTIVITY and REPORT_LAST_ACTIVITY functions of the DBMS_AUTO_PARTITION package.

Generate a report, in plain text format, of automatic partitioning operations for a specific period

This example generates a report containing typical information about the automatic partitioning operations for the last 24 hours. The report is generated in plain text format by default.

DECLARE
  Report clob := NULL
BEGIN
    Report := DBMS_AUTO_PARTITION.REPORT_ACTIVITY();
END;
/

Generate a report, in HTML format, of automatic partitioning operations for MAY 2021

This example generates a report containing basic information about the automatic partitioning operations for the month of MAY 2021. The report is generated in the HTML format, and it includes only a summary of automatic partitioning operations.

DECLARE
  Report clob := NULL
BEGIN
    Report := DBMS_AUTO_PARTITION.REPORT_ACTIVITY(
                       ACTIVITY_START => TO_TIMESTAMP('2021-05-01', 'YYYY-MM-DD'),
                       ACTIVITY_END   => TO_TIMESTAMP('2021-06-01', 'YYYY-MM-DD'),
                       TYPE           => 'HTML',
                       SECTION        => 'SUMMARY',
                       LEVEL          => 'BASIC' );
END;
/

Generate a report, in plain text format, of the last automatic partitioning operation

This example generates a report containing typical information about the last automatic partitioning operation. The report is generated in the plain text format by default.

DECLARE
  Report clob := NULL
BEGIN
    Report := DBMS_AUTO_PARTITION.REPORT_LAST_ACTIVITY();
END;
/

See REPORT_ACTIVITY Function for more information.

See REPORT_LAST_ACTIVITY Function for more information.

Example Automatic Partitioning Scenarios

Example scenarios for automatic partitioning using the DBMS_AUTO_PARTITIONING API procedures and functions.

Generate a recommendation for a single table and manually apply the recommendation

  1. Set AUTO_PARTITION_MODE parameter to REPORT ONLY to enable an automatic partitioning recommendation to be made and verified. The recommendation is not applied to the table.
    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
            PARAMETER_NAME  => 'AUTO_PARTITION_MODE', 
            PARAMETER_VALUE => 'REPORT ONLY');
    END;
    /
  2. Validate that TPCH.LINEITEM table is a candidate for automatic partitioning. This step is optional and recommended when you are selectively targeting single tables.
    SELECT DBMS_AUTO_PARTITION.VALIDATE_CANDIDATE_TABLE(
               TABLE_OWNER => 'TPCH',
               TABLE_NAME  => 'LINEITEM')
    FROM DUAL;

    If the table is a valid candidate, when you invoke automatic partitioning for a recommendation analysis it returns as VALID. Otherwise, the violation criteria is shown.

    See VALIDATE_CANDIDATE_TABLE Function for a list of criteria for eligible candidate tables.

  3. Invoke the DBMS_AUTO_PARTITION API to generate a recommendation for the TPCH.LINEITEM table.
    -- DEFINE SQLPLUS BIND VARIABLE FOR RECOMMENDATION ID
    VARIABLE RECOMMENDATION_ID VARCHAR2(32);
    BEGIN
      :RECOMMENDATION_ID := DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD(
                                 TABLE_OWNER => 'TPCH',
                                 TABLE_NAME  => 'LINEITEM');
    END;
    /

    The recommendation analysis and verification that you perform with DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD can be a resource-intensive and long running operation and might take considerable time. You should perform this step on a database that is not your primary production system. Oracle recommends giving the verification operation sufficient resources by choosing the HIGH service.

  4. Check the recommendation. The view DBA_AUTO_PARTITION_RECOMMENDATIONS contains the information on the recommendation. In this example, check the recommended partition key and partition method.
    SELECT PARTITION_METHOD, PARTITION_KEY
      FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
      WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID;
    

    Additionally, query the same view to get the performance analysis report generated for the workload after the table was partitioned according to the recommendation.

    SELECT REPORT
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID;
    
  5. After manual validation of the recommendation, apply the recommendation. If you are applying the recommendation in the database where the recommendation analysis has taken place, apply the recommendation by executing the APPLY_RECOMMENDATION procedure.
    BEGIN
        DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION(
             RECOMMENDATION_ID => :RECOMMENDATION_ID);
    END;
    /

    If you want to apply the recommendation to a different database, such as your production environment, extract the modification DDL. Then, run the extracted modification DDL in your target database. The query to extract the modification DDL is as follows:

    SELECT MODIFY_TABLE_DDL
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID;
    

    Example output of modification DDL:

    BEGIN
        -- DBMS_AUTO_PARTITION RECOMMENDATION_ID C3F7A59E085C2F25E05333885A0A55EA
        --   FOR TABLE "TPCH"."LINEITEM"
        --   GENERATED AT 06/04/2021 20:52:29
        DBMS_AUTO_PARTITION.BEGIN_APPLY(EXPECTED_NUMBER_OF_PARTITIONS => 10);
    
        EXECUTE IMMEDIATE
        'ALTER TABLE "TPCH"."LINEITEM"
        MODIFY PARTITION BYLIST(SYS_OP_INTERVAL_HIGH_BOUND
            ("L_SHIPDATE", INTERVAL ''10'' MONTH, TIMESTAMP ''1992-01-01 00:00:00''))
        AUTOMATIC /* SCORE=23533.11;  */
        (PARTITION P_NULL VALUES(NULL))
        AUTO ONLINE PARALLEL';
    
        DBMS_AUTO_PARTITION.END_APPLY;
    EXCEPTION WHEN OTHERS THEN
        DBMS_AUTO_PARTITION.END_APPLY;
        RAISE;
    END;
    
  6. Verify that the table was automatically partitioned, query the catalog views.
    SELECT T.AUTO, T.PARTITIONING_TYPE, C.COLUMN_NAME
        FROM DBA_PART_TABLES T, DBA_PART_KEY_COLUMNS C
        WHERE T.OWNER = 'TPCH' AND T.TABLE_NAME = 'LINEITEM'
            AND T.OWNER = C.OWNER AND T.TABLE_NAME = C.NAME;
    

    Use this query to identify when automatic partitioning was applied to a given table.

    SELECT APPLY_TIMESTAMP_START, APPLY_TIMESTAMP_END
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE TABLE_OWNER = 'TPCH' AND TABLE_NAME = 'LINEITEM';
    

See CONFIGURE Procedure for information.

See VALIDATE_CANDIDATE_TABLE Function for information.

See RECOMMEND_PARTITION_METHOD Function for information.

See APPLY_RECOMMENDATION Procedure for information.

Generate a recommendation for eligible tables and manually apply the recommendation

  1. Set AUTO_PARTITION_MODE parameter to REPORT ONLY to enable an automatic partitioning recommendation to be made and verified. The recommendation is not applied to existing tables.
    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
            PARAMETER_NAME  => 'AUTO_PARTITION_MODE', 
            PARAMETER_VALUE => 'REPORT ONLY');
    END;
    /
  2. Invoke the DBMS_AUTO_PARTITION API to generate a recommendation table.
    -- DEFINE SQLPLUS BIND VARIABLE FOR RECOMMENDATION ID
    VARIABLE RECOMMENDATION_ID VARCHAR2(32);
    BEGIN
      :RECOMMENDATION_ID := DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD();
    END;
    /

    The recommendation analysis and verification is a resource-intensive and long running operation and might take considerable time. On secondary, non-production databases, Oracle recommends giving the verification sufficient resources by choosing service HIGH.

  3. Query the DBA_AUTO_PARTITION_RECOMMENDATIONS view to see which tables were analyzed.
    SELECT TABLE_OWNER, TABLE_NAME, PARTITION_METHOD, PARTITION_KEY
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID
        ORDER BY RECOMMENDATION_SEQ;
    
  4. Use this query to drill-down in the report for a specific table that was analyzed in the run, the TPCH.LINEITEM table in this example.
    SELECT REPORT
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID
            AND TABLE_OWNER = 'TPCH'
            AND TABLE_NAME  = 'LINEITEM';
    
  5. Apply the recommendation by executing the APPLY_RECOMMENDATION procedure.
    BEGIN
        DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION(
             RECOMMENDATION_ID => :RECOMMENDATION_ID);
    END;
    /

    Alternately, apply recommendations for a specific table that was analyzed, the TPCH.LINEITEM table in this example.

    BEGIN
        DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION(
            RECOMMENDATION_ID => :RECOMMENDATION_ID,
            TABLE_OWNER       => 'TPCH',
            TABLE_NAME        => 'LINEITEM');
    END;
    /

See CONFIGURE Procedure for information.

See RECOMMEND_PARTITION_METHOD Function for information.

See APPLY_RECOMMENDATION Procedure for information.

Note:

Recommendations of automatic partitioning generated by the RECOMMEND_PARTITION_METHOD function have a time limit, specified by the TIME_LIMIT parameter, with a default of 1 day. If you are analyzing a large system with many candidate tables, a single invocation may not generate a recommendation for all tables that can be partitioned. You can safely invoke the recommendation for auto partitioning repeatedly to generate recommendations for additional tables. When the function is invoked and zero rows are in DBA_AUTO_PARTITION_RECOMMENDATIONS for the RECOMMENDATION_ID, then the function did not find any additional candidate tables for automatic partitioning.

Generate and automatically apply recommendations for eligible tables

  1. Set AUTO_PARTITION_MODE parameter to REPORT ONLY to enable an automatic partitioning recommendation to be made and verified. The recommendation is not applied to existing tables.
    BEGIN
        DBMS_AUTO_PARTITION.CONFIGURE(
            PARAMETER_NAME  => 'AUTO_PARTITION_MODE', 
            PARAMETER_VALUE => 'IMPLEMENT');
    END;
    /
  2. Invoke the DBMS_AUTO_PARTITION API to generate a recommendation table.
    -- DEFINE SQLPLUS BIND VARIABLE FOR RECOMMENDATION ID
    VARIABLE RECOMMENDATION_ID VARCHAR2(32);
    BEGIN
      :RECOMMENDATION_ID := DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD();
    END;
    /

    The recommendation analysis and verification is a resource-intensive and long running operation and might take considerable time. On secondary, non-production databases, Oracle recommends giving the verification sufficient resources by choosing service HIGH.

  3. Query the DBA_AUTO_PARTITION_RECOMMENDATIONS view to see which tables were analyzed.
    SELECT TABLE_OWNER, TABLE_NAME, PARTITION_METHOD, PARTITION_KEY
        FROM DBA_AUTO_PARTITION_RECOMMENDATIONS
        WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID
        ORDER BY RECOMMENDATION_SEQ;
    
  4. Use the REPORT_LAST_ACTIVITY function to retrieve the report on the actions taken during the last run.
    SELECT DBMS_AUTO_PARTITION.REPORT_LAST_ACTIVITY() FROM DUAL;
    

See CONFIGURE Procedure for information.

See RECOMMEND_PARTITION_METHOD Function for information.

See REPORT_LAST_ACTIVITY Function for information.

Data Dictionary Views for Automatic Partitioning

There are two new views and one updated view in the data dictionary for information about the automatic partitioning configuration and recommendations in your database.

DBMS_AUTO_PARTITION DBA_AUTO_PARTITION_CONFIG View

Displays the current configuration parameter settings for automatic partitioning.

Column Description
PARAMETER_NAME Name of the configuration parameter
PARAMETER_VALUE Value of the configuration parameter
LAST_MODIFIED Time, in UTC, at which the parameter value was last modified.
MODIFIED_BY User who last modified the parameter value

DBMS_AUTO_PARTITION DBA_AUTO_PARTITION_RECOMMENDATIONS View

When you run CONFIGURE or RECOMMEND_PARTITION, the results from those procedures is stored in this view. The RECOMMENDATION_ID is used in several procedures and functions.

Column Description
TABLE_OWNER Owner of the table
TABLE_NAME Owner of the table
PARTITION_METHOD Recommended partition method. See CONFIGURE Procedure
PARTITION_KEY Recommended partition key. NULL means that analysis complete and recommendation is not to partition the table.
GENERATE_TIMESTAMP Time, in UTC, when this recommendation was generated.
RECOMMENDATION_ID ID used with DBMS_AUTO_PARTITION APIs to get additional information about this recommendation.
RECOMMENDATION_SEQ When a recommendation ID has recommendations for multiple tables, provides the order in which the recommendations were generated. Performance reports are generated assuming that earlier recommendations have been applied. For example, the report for RECOMMENDATION_SEQ = 2 assumes recommendations have been applied for both RECOMMENDATION_SEQ = 1 and RECOMMENDATION_SEQ = 2.
MODIFY_TABLE_DDL DDL that would be, or was, used to apply the recommendation.
APPLY_TIMESTAMP_START Time, in UTC, when application of this recommendation was started. NULL if recommendation was not applied.
APPLY_TIMESTAMP_END Time, in UTC, when application of this recommendation was finished. NULL if recommendation was not applied or if application has not finished.
REPORT SQL Performance Analyzer report from SQL execution on database after recommendation is applied.

DBMS_AUTO_PARTITION Updates to Existing Views

Discusses the changes to existing views as a result of the implementation of automatic partitioning.

In *_PART_TABLES, the AUTO column (VARCHAR2(3)) was added. Its values are as follows:
  • YES - If the table was partitioned by DBMS_AUTO_PARTITION.
  • NO - If the table was not partitioned by DBMS_AUTO_PARTITION.