No More Guesswork: The Oracle Advisor for Optimal Database Partitioning

Intelligent analysis and execution

Oracle SQL Access Advisor (part of the Oracle Tuning Pack) originally released in Oracle Database 10g, has been extended to include detailed advice on partitioning existing tables, materialized views and indexes. This feature analyses the state of the underlying structures (table / indexes), their sizes as well as the SQL statements accessing these tables. It will then provide certain recommendations on how best to use the partitioning functionality to enhance the execution of the affected SQL statements. It also considers the cost of creation and maintaining access to the new structures.

Oracle SQL Access Advisor may recommend partitioning a normal single large table to improve the performance of a series of SQL statements. The partitioning recommendation could also include new indexes and materialized views. To migrate an existing large and heavily used production table which has also indexes, triggers, views and constraints is a very complex operation. It has to be cautiously planned and executed to avoid any outages to the applications. The advisor will also generate a series of SQL statements to assist with this activity.

Forecast performance improvements

There are four major activities involved in such an operation; create a task, defined a workload, run the workload & generate recommendations, and finally review & deploy these recommendations. The recommendations generated by the Oracle SQL Access Advisor will show the expected performance improvements, should the listed recommendations to be implemented against the production database.

The above activities can all be managed and controlled through the Oracle Enterprise Manager 13c SQL Access Advisor wizard. These activities can also be controlled through various internal DBMS packages manually. These are all fully explained in Oracle’s product documentation.

Illustrations

In the simple example below, there is an ORDER_HISTORY table that is made up of company order details, including order dates. This is a single large table that is constantly been extended with new orders.

SQL> desc order_history2
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORDER_ID                                  NOT NULL NUMBER(20)
 COMPANY_NAME                                       VARCHAR2(20)
 COMPANY_ID                                         VARCHAR2(25)
 EMAIL                                              VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 ORDER_DATE                                         DATE
 ITEM_ID                                            VARCHAR2(10)
 VALUE                                              NUMBER(20,2)
 PART_NAME                                          VARCHAR2(25)

SQL> select count(*) from order_history2;
  COUNT(*)
106530449

The underlying tablespace containing the above table is USER_TBS1. This tablespace is created using a large datafile as shown below;

The end user application may executes the following SQL queries against the underlying ORDER_HISTORY table;

SQL> select COMPANY_NAME,COMPANY_ID from order_history  where ORDER_DATE  between to_date('01-DEC-2019','dd-mon-yyyy') and to_date('31-DEC-2019','DD-MON-YYYY');

SQL> select COMPANY_NAME,COMPANY_ID from order_history  where ORDER_DATE  between to_date('01-JAN-2019','dd-mon-yyyy') and to_date('31-JAN-2019','DD-MON-YYYY');

SQL> select count(*), order_date  from order_history group by order_date;

The SQL statements typically look for records within certain date ranges.

The OEM13c SQL Access Advisor is invoked using the following steps;

In the screen below, we have assumed that the target SQL statements are currently available in the SGA. These statements can be filtered to restrict tables / SQL statements collected. We can use existing SQL Tuning Sets to focus on individual statements.

The third option allows the DBA to add new schemas / tables that will be used to create a synthetic hypothetical workload.

Choose the areas to produce the recommendations;

Now execute the newly created job;

After you submit the job, the Oracle SQL Access Advisor examines the underlying tables and the target SQL statements. It then makes a series recommendations in the chosen areas.

You can navigate further to see a breakdown of the actual cost improvements

The above diagram shows the massive improvements to the workload I/O cost. You can navigate to detail recommendations and the improved SQL statements.

The recommendations also provides the SQL statements to convert the existing table structure to the new format, along with any associated indexes. Please note, the advice and the generated SQL statements need to be examined and tested very carefully before being deployed. Your DBA can then either run the SQL statements interactively or schedule them to run at a later time.

The screen shot below shows the SQL statements that are affected by the recommendations made by the advisor and the potential improvements in terms of overall cost. The following are the recommendations made by the SQL Access advisor;

Conclusion

This is a very powerful feature of the Oracle Tuning Pack that can help you get the maximum out of the Oracle Database most effectively, particularly when dealing with real life databases and production workloads.

OEM13c can fully manage this activity from end to end, although it can also be implemented through various DBMS internal packages manually, if required.

What next?

Once you have optimised your Oracle Database partitions, you may be interested in my previous blog, which discusses the use of Oracle Advanced Compression in conjunction with table partitions.

Explorer specialise in the management and optimisation of Oracle Database workloads. If you would like to explore this with us further, please don’t hesitate to contact us.

Author: Hamid Ansari

Job Title: Technical Consultant

Bio: Hamid is a Technical consultant at DSP-Explorer, he has a BA(Hons) in Data Processing / Business computing from the University of Sunderland and is a specialist in Oracle Database and associated technologies. He has over 25 years of working with Oracle RDMS starting with version 5.1. His main focus areas are Oracle GoldenGate, RAC, DataGuard, Engineered Systems and Oracle Cloud. He has worked across various companies in the Middle East, Europe, UK and US.