Manage Automatic Partitioning on Autonomous Database
Automatic partitioning, when applied, is transparent and does not require any user interaction or maintenance.
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. - How Automatic Partitioning Works
When invoked, automatic partitioning identifies candidate tables for automatic partitioning, evaluates partition schemes, and implements a partitioning strategy. - Configure Automatic Partitioning
Use theDBMS_AUTO_PARTITION.CONFIGURE
procedure to configure automatic partitioning options. - Use Automatic Partitioning
Describes the flow and general processes for using and managing automatic partitioning in Autonomous Database. - Generate Automatic Partitioning Reports
Generate automatic partitioning reports using theREPORT_ACTIVITY
andREPORT_LAST_ACTIVITY
functions of theDBMS_AUTO_PARTITION
package. - Example Automatic Partitioning Scenarios
Example scenarios for automatic partitioning using theDBMS_AUTO_PARTITION
API procedures and functions. - 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.
Parent topic: Performance Monitor and Management
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.
Automatic partitioning requires explicit calls to the
DBMS_AUTO_PARTITION
PL/SQL APIs to recommend and
apply automatic partitioning to an Autonomous Database.
Parent topic: Manage Automatic Partitioning on Autonomous Database
How Automatic Partitioning Works
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.
When you invoke automatic partitioning it performs the following tasks:
-
Identifies 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.
-
Evaluates partition schemes based on workload analysis, quantification, and verification of the performance benefits:
-
Candidate empty partition schemes with synthesized statistics are created internally and analyzed for performance.
-
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.
-
If a candidate partition scheme does not improve performance beyond specified performance and regression criteria, automatic partitioning is not recommended.
-
-
Implements the optimal partitioning strategy, if configured to do so, for the tables analyzed by the automatic partitioning procedures.
Parent topic: Manage Automatic Partitioning on Autonomous Database
Configure Automatic Partitioning
Use the DBMS_AUTO_PARTITION.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');
This mode does not disable existing automatically partitioned tables.
Manage schemas and tables for automatic partitioning
AUTO_PARTITION_SCHEMA
and
AUTO_PARTITION_TABLE
settings to specify schemas and tables
considered for automatic partitioning.
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 theSH.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.
Parent topic: Manage Automatic Partitioning on Autonomous Database
Use Automatic Partitioning
Describes the flow and general processes for using and managing automatic partitioning in Autonomous Database.
- 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.
- 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. - 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.
- Use a cloned database for automatic partitioning.
- 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. - 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 columnMODIFY_TABLE_DDL
in theDBA_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.
Parent topic: Manage Automatic Partitioning on Autonomous Database
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.
Parent topic: Manage Automatic Partitioning on Autonomous Database
Example Automatic Partitioning Scenarios
Example scenarios for automatic partitioning using the DBMS_AUTO_PARTITION
API procedures and functions.
Generate a recommendation for a single table and manually apply the recommendation
- Set
AUTO_PARTITION_MODE
parameter toREPORT 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; /
- 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.
- Invoke the
DBMS_AUTO_PARTITION
API to generate a recommendation for theTPCH.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. - 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;
- 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;
- 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
- Set
AUTO_PARTITION_MODE
parameter toREPORT 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; /
- 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.
- 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;
- 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';
- 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.
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
- Set
AUTO_PARTITION_MODE
parameter toREPORT 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; /
- 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.
- 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;
- 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.
Parent topic: Manage Automatic Partitioning on Autonomous Database
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. - DBMS_AUTO_PARTITION DBA_AUTO_PARTITION_RECOMMENDATIONS View
When you runCONFIGURE
orRECOMMEND_PARTITION
, the results from those procedures is stored in this view. TheRECOMMENDATION_ID
is used in several procedures and functions. - DBMS_AUTO_PARTITION Updates to Existing Views
Discusses the changes to existing views as a result of the implementation of automatic partitioning.
Parent topic: Manage Automatic Partitioning on Autonomous 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 |
Parent topic: Data Dictionary Views for Automatic Partitioning
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. |
Parent topic: Data Dictionary Views for Automatic Partitioning
DBMS_AUTO_PARTITION Updates to Existing Views
Discusses the changes to existing views as a result of the implementation of automatic partitioning.
*_PART_TABLES
, the AUTO
column (VARCHAR2(3)
) was added. Its values are as follows:
YES
- If the table was partitioned byDBMS_AUTO_PARTITION
.NO
- If the table was not partitioned byDBMS_AUTO_PARTITION
.
Parent topic: Data Dictionary Views for Automatic Partitioning