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');

Manage schemas and tables for automatic partitioning

• 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;
/

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.

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.

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.