Oracle 18c: New Hidden Parameter: "_PX_PARTITION_SKEW_THRESHOLD"

With Oracle Database 18c, we have a new parameter which can change the execution plan of parallel queries which consist of partitioned tables. There is not too much information about this parameter yet but based on my experiments I have observed its behaviour. Actually this parameter flags if our table is "SKEWED" or "NOT SKEWED"

Let's create a monthly partition table and insert some data;

 CREATE TABLE part_skew_table
   ( prod_id       NUMBER(6)
   , cust_id       NUMBER
   , time_id       DATE
   , channel_id    CHAR(1)
   , promo_id      NUMBER(6)
   , quantity_sold NUMBER(3)
   , amount_sold   NUMBER(10,2)
   )
  PARTITION BY RANGE (time_id)
  INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
  (PARTITION sales_q1_2017 VALUES LESS THAN  (TO_DATE('01-FEB-2017','dd-MON-yyyy')));

 begin
    for rec in 1 .. 25 loop
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+00, 2457755 + 270)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+270, 2457755 + 360)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+180, 2457755 + 270)), 'J'),'S',910,140, 1000);
      
      insert into part_skew_table 
      values (10,110,TO_DATE(TRUNC(DBMS_RANDOM.VALUE(2457755+00, 2457755 + 180)), 'J'),'S',910,140, 1000);
    
    end loop;
 end;

 analyze table part_skew_table compute statistics;

  select table_name, partition_name, num_rows
   from user_tab_partitions
  where table_name = 'PART_SKEW_TABLE'
  order by 3 desc;

 TABLE_NAME         PARTITION_NAME  NUM_ROWS

 ----------             --------------   --------
 PART_SKEW_TABLE SYS_P479  16
 PART_SKEW_TABLE SYS_P482  14
 PART_SKEW_TABLE SYS_P481  12
 PART_SKEW_TABLE SYS_P477  11
 PART_SKEW_TABLE SYS_P478  9
 PART_SKEW_TABLE SALES_Q1_2017  8
 PART_SKEW_TABLE SYS_P484  8
 PART_SKEW_TABLE SYS_P480  5
 PART_SKEW_TABLE SYS_P487  5
 PART_SKEW_TABLE SYS_P485  5
 PART_SKEW_TABLE SYS_P486  4
 PART_SKEW_TABLE SYS_P483  3

 How does it work?

 The mechanism is only relevant when the number of partitions is at least twice the DOP you choose.

 If the DOP (parallelism) is N, then examine the N-1 largest partitions, sum their sizes, and express this as a percentage  of  the size    of the whole table, call it p%

• number of partitions > 2xDOP

• if p% is greater than "_px_partition_skew_threshold" a plan that could be executed as a hash distribution plan

• if p% is less than "_px_partition_skew_threshold" then the optimizer will choose the partition-wise plan.

 Let's check the following query:


  select /*+ parallel(3)*/time_id,count(*) from part_skew_ group by time_id;


 DOP  (N)                => 3 
 N-1 largest partitions  => SYS_P479, SYS_P482
 p%                      => ((16+14)/100)*100 = %30 


 1st Case:          
                    p% =30 > _px_partition_skew_threshold =29


  alter session set "_px_partition_skew_threshold" = 29;
  
  explain plan for
  select /*+ parallel(3)*/time_id,count(*) from part_skew_table group by time_id;
  
  select * from table(dbms_xplan.display('plan_table',null,'basic,predicate'));


   Plan hash value: 1775209382
 
----------------------------------------------------
| Id  | Operation                | Name            |
----------------------------------------------------
|   0 | SELECT STATEMENT         |                 |
|   1 |  PX COORDINATOR          |                 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001        |
|   3 |    HASH GROUP BY         |                 |
|   4 |     PX RECEIVE           |                 |
|   5 |      PX SEND HASH        | :TQ10000   --------------------> PX HASH
|   6 |       HASH GROUP BY      |                 |
|   7 |        PX BLOCK ITERATOR |                 |
|   8 |         TABLE ACCESS FULL| PART_SKEW_TABLE |
----------------------------------------------------

           - Degree of Parallelism is 3 because of hint

2nd Case:          
                    p% =30 < _px_partition_skew_threshold =31


  alter session set "_px_partition_skew_threshold" = 31;
  
  explain plan for
  select /*+ parallel(3)*/time_id,count(*) from part_skew_table group by time_id;
  

  select * from table(dbms_xplan.display('plan_table',null,'basic,predicate'));


Plan hash value: 1410516856
 
----------------------------------------------------
| Id  | Operation                | Name            |
----------------------------------------------------
|   0 | SELECT STATEMENT         |                 |
|   1 |  PX COORDINATOR          |                 |
|   2 |   PX SEND QC (RANDOM)    | :TQ10000        |
|   3 |    PX PARTITION RANGE ALL|--------------------------------> PX PARTITION
|   4 |     HASH GROUP BY        |                 |
|   5 |      TABLE ACCESS FULL   | PART_SKEW_TABLE |

----------------------------------------------------

           - Degree of Parallelism is 3 because of hint

 

Set Environment Variable in the Listener.ora

 Sometimes you need to use a variable (as a parameter) while dealing with RMAN or SQL*Plus.

 For example, you may want to use a parameter while taking your RMAN backup such as:

As you see above, I exported BACKUP_TYPE as an environment variable to determine my backup format and I used it in my rman command.


Now, assume that I'm working on a RAC, I'm still running my backup commands from 1st instance but I want to backup my database from 2nd instance or I need to open channels on both instances. So, I should use TNS  to connect other instance via RMAN:

As you see, my rman backup command failed. The reason is BACKUP_TYPE variable is not passed after connected to 2nd instance.

The solution for this problem is passing the environment variable inside listener.ora like the following;


Step 1:   Configure a second listener in the listener.ora on the 2nd instance's grid home with different port and with my environment variable

Step2: Configure my tns entry (TEST2) to connect the 2nd instance with new listener:

Step3: Connet the 2nd instance by RMAN with new listener and try backup database with our environment variable:

 At this time, when you connect the database by TNS, our environment variable is passed to the new connection.


Step 4: Also verify our environment variable is really set for our new listener:

Partition => 12cR2 New Feature "Automatic LIST Partition"

Automatic list partitioning was introduced in Oracle Database 12c Release 2 (12.2) to handle the new partitions according to the distinct values of partition key.


CREATE TABLE employees ( employee_id NUMBER(4) ,first_name VARCHAR2(30) ,last_name VARCHAR2(30) ,email VARCHAR2(30) ,phone_number VARCHAR2(20) ,hire_date DATE ,salary NUMBER(6,0) ,commission_pct NUMBER(3,2) ,manager_id NUMBER(4) ,department_id NUMBER(4) ) PARTITION BY LIST (department_id) AUTOMATIC ( PARTITION P_10 VALUES (10) );

It prevents getting the following error when a new record (whose partition key values doesnt exist in the list) came.

ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

There is a new column on the (user/dba)_part_tables to check if the table is autmatic list partitioned.

SELECT table_name,
       autolist
FROM   user_part_tables;

TABLE_NAME                     AUTOLIST
------------------------------ --------
EMPLOYEES                         YES

SQL>