Oracle Tuning-Analyze SQL with SQL Tuning Advisor

SQL TUNING ADVISOR :-

  • The SQL Tuning Advisor takes one or more SQL statements as an input and invokes the Automatic Tuning Optimizer to perform SQL tuning on the statements.
  • The output of the SQL Tuning Advisor is in the form of an recommendations, along with a rationale for each recommendation and its expected benefit.The recommendation relates to collection of statistics on objects, creation of new indexes, restructuring of the SQL statement, or creation of a SQL profile. You can choose to accept the recommendation to complete the tuning of the SQL statements.
  • You can also run the SQL Tuning Advisor selectively on a single or a set of SQL statements that have been identified as problematic.
  • Find the problematic SQL_ID from v$session you would like to analyze. Usually the AWR has the top SQL_IDs column.

In order to access the SQL tuning advisor API, a user must be granted the ADVISOR privilege:

sqlplus / as sysdba
GRANT ADVISOR TO HARI;
CONN HARI/hari;
Steps to tune the problematic SQL_ID using SQL TUNING ADVISOR :-

Create Tuning Task :

DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id => '43x11xxhxy1j7',
scope => 'COMPREHENSIVE',
time_limit => 3600,
task_name => 'my_sql_tuning_task_1',
description => 'Tune query using sqlid');
end;
/

Execute Tuning task :

BEGIN
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task_1');
end;
/

Monitor the task executing using below query:

SELECT TASK_NAME, STATUS FROM DBA_ADVISOR_LOG WHERE TASK_NAME ='my_sql_tuning_task_1';

TASK_NAME                      STATUS
------------------------------ -----------
my_sql_tuning_task_1           COMPLETED

Check the status is completed for the task and we can get recommendations of the advisor.

Report Tuning task :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task_1') from DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1')

-----------------------------------------------------------------------
GENERAL INFORMATION SECTION
-----------------------------------------------------------------------

Tuning Task Name                  : my_sql_tuning_task_1

Tuning Task Owner                 : SYS

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 60

Completion Status                 : COMPLETED

Started at                        : 11/10/2018 19:47:27

Completed at                      : 11/10/2018 19:47:54
--------------------------------------------------------------------
SQL_ID : 43x11xxhxy1j7
SQL_staement : SELECT * FROM HARI.EMP

Number of SQL Profile Findings    : 1
--------------------------------------------------------------------
FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  A potentially better execution plan was found for this statement.

  Recommendation (estimated benefit: 99.94%)

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

  - Consider accepting the recommended SQL profile.

    execute dbms_sqltune.accept_sql_profile(task_name => 'my_sql_tuning_task_1',replace => TRUE);

To get detailed information :

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('my_sql_tuning_task_1','TEXT','ALL','ALL') FROM DUAL;

Drop SQL Tuning task :

BEGIN
DBMS_SQLTUNE.drop_tuning_task (task_name => 'my_sql_tuning_task_1');
END;
/
Another method for adding new task using SQL TUNING ADVISOR :-
Check the PLAN_HASH_VALUE got changed for the specific statement and get SNAP_ID to create a tuning task.
set lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id = nvl('&sql_id')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number 
and executions_delta > 0
order by 1, 2, 3
/

Enter value for sql_id: 483wz173punyb

SNAP_ID NODE BEGIN_INTERVAL_TIME SQL_ID PLAN_HASH_VALUE EXECS AVG_ETIME AVG_LIO
---------- ------ ------------------------------ ------------- --------
15694 1 10-NOV-18 01.00.04.047 AM 483wz173punyb 2391860790 1 4,586.818 33,924,912.0
15695 1 10-NOV-18 02.00.18.928 AM 483wz173punyb 2 1,488.867 0,064,449.0
15696 1 10-NOV-18 03.00.03.192 AM 483wz173punyb 2 1,053.459 8,780,977.0
Create a tuning task for the specific statement from AWR snapshots:-
Create,Execute and Report the task from given AWR snapshot IDs.
Create Task,

DECLARE
l_sql_tune_task_id VARCHAR2(100);
BEGIN
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
begin_snap => 1868,
end_snap => 1894,
sql_id => '483wz173punyb',
scope => DBMS_SQLTUNE.scope_comprehensive,
time_limit => 300,
task_name => '483wz173punyb_tuning_task',
description => 'Tuning task for statement 483wz173punyb in AWR.');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
/

Execute Task,

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => '483wz173punyb_tuning_task');

Report task,

SET LONG 10000;
SET PAGESIZE 1000
SET LINESIZE 200
SELECT DBMS_SQLTUNE.report_tuning_task('483wz173punyb_tuning_task') AS recommendations FROM dual;
Interrupt Tuning task :
EXEC DBMS_SQLTUNE.interrupt_tuning_task (task_name => '483wz173punyb_tuning_task');
Resume Tuning task :
EXEC DBMS_SQLTUNE.resume_tuning_task (task_name => '483wz173punyb_tuning_task');
Cancel Tuning task :
EXEC DBMS_SQLTUNE.cancel_tuning_task (task_name => '483wz173punyb_tuning_task');
Reset Tuning task :
EXEC DBMS_SQLTUNE.reset_tuning_task (task_name => '483wz173punyb_tuning_task');
Catch Me On:- Hariprasath Rajaram

LinkedIn:   https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:   https://www.facebook.com/HariPrasathdba
Facebook Group: https://www.facebook.com/groups/894402327369506/
Facebook Page:  https://www.facebook.com/dbahariprasath/?
Twitter:        https://twitter.com/hariprasathdba

 

Oracle Partitioning Tips And Tricks

Range Partition :- Range Partitioning maps data to partitions based on ranges of partition key values that you establish for each partition. It is the most common type of partitioning and is often used with dates.

Create a range partition table,

SQL> create table details(order_id number,order_date date) partition by range (order_date) (partition p1 values less than ('01-jan-2018') tablespace t1,partition p2 values less than ('01-mar-2018') tablespace t2,partition p3 values less than ('01-aug-2018') tablespace t3,partition p4 values less than ('01-dec-2018') tablespace t4 );

Table created.

Insert data into partition 1

SQL> insert into details values(1,'10-dec-2017');

1 row created.

SQL> select * from details partition(p1);

ORDER_ID   ORDER_DAT
---------- ---------
1          10-DEC-17

Insert data into partition 2

SQL> insert into details values(2,'10-feb-2018');

1 row created.

SQL> select * from details partition(p2);

ORDER_ID   ORDER_DAT
---------- ---------
2          10-FEB-18

Insert data into partition 3

SQL> insert into details values(3,'13-jul-2018');

1 row created.

SQL> select * from details partition(p3);

ORDER_ID   ORDER_DAT
---------- ---------
3          13-JUL-18

Insert data into partition 4

SQL> insert into details values(4,'1-nov-2018');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from details partition(p4);

ORDER_ID   ORDER_DAT
---------- ---------
4          01-NOV-18

SQL> commit;

Partition table data :

SQL> select * from details;

ORDER_ID   ORDER_DAT
---------- ---------
1          10-DEC-17
2          10-FEB-18
3          13-JUL-18
4          01-NOV-18

 

LIST PARTITION :- List Partitioning is used to list together unrelated data into partitions. It is a technique where you specify a list of discrete values for the partitioning key in the description for each partition.

Create a list partition table

SQL> CREATE TABLE sales_list (salesman_id NUMBER(5),sales_state VARCHAR2(20)) PARTITION BY LIST(sales_state)(PARTITION south VALUES ('py','tn','ap','kl','ka') tablespace t1,PARTITION east VALUES ('cac','or','bi') tablespace t2,PARTITION west VALUES ('pu','go') tablespace t3,PARTITION north VALUES ('dl','ja') tablespace t4);

Table created.

Insert data into partition table

SQL> insert into sales_list values(1,'py');

1 row created.

SQL> insert into sales_list values(2,'cac');

1 row created.

SQL> insert into sales_list values(3,'pu');

1 row created.


SQL> insert into sales_list values(4,'dl');

1 row created.

SQL> commit;

Commit complete.

Partition table data :

SQL> select * from sales_list;

SALESMAN_ID   SALES_STATE
-----------   ------------
1               py
2               cac
3               pu
4               dl
SQL> select * from sales_list partition(south);

SALESMAN_ID SALES_STATE
----------- -----------
1           py

SQL> select * from sales_list partition(east);

SALESMAN_ID SALES_STATE
----------- -----------
2           cac

SQL> select * from sales_list partition(west);

SALESMAN_ID SALES_STATE
----------- -----------
3           pu

SQL> select * from sales_list partition(north);

SALESMAN_ID SALES_STATE
----------- -----------
 4          dl

 

HASH PARTITION :

Hash partitioning based on a hash algorithm. Hash partitioning enables partitioning of data that does not lend itself to range or list partitioning. The records in a table, are partitions based on Hash value found in the value of the column, which is used for partitioning. Hash partitioning does not have any logical meaning to the partitions as do the range partitioning.

Syntax:-

CREATE TABLE table-name (...col-list...) ....
PARTITION BY HASH (col-names) PARTITIONS number-of-partitions STORE IN (tbs-name1,tbs-name2,...);

Create a hash partition table

SQL> CREATE TABLE emp (empno NUMBER(4),sal NUMBER) PARTITION BY HASH(empno) PARTITIONS 3 STORE IN (t1,t2,t3);

Table created.

Check the partition under EMP table

SQL> select partition_name from user_tab_partitions where table_name='EMP';
PARTITION_NAME
---------------
SYS_P581
SYS_P582
SYS_P583

Insert data into partition table

SQL> insert into emp values(1,11);

1 row created.

SQL> insert into emp values(2,22);

1 row created.

SQL> insert into emp values(4,44);

1 row created.

SQL> insert into emp values(3,33);

1 row created.

SQL> insert into emp values(5,55);

1 row created.

SQL> insert into emp values(6,66);

1 row created.

SQL> select * from emp;

EMPNO     SAL
-------   ----
6          66
1          11
4          44
3          33
2          22
5          55

6 rows selected.
SQL> select * from emp partition(SYS_P581);

EMPNO      SAL
---------- -----
6          66

SQL> select * from emp partition(SYS_P582);

EMPNO      SAL
---------- ----
1          11
4          44
3          33

SQL> select * from emp partition(SYS_P583);

EMPNO        SAL
-------     ----
2           22
5           55

INTERVAL PARTITION :

Interval partitioning is an enhancement to range partitioning in Oracle 11g and interval partitioning automatically creates time-based partitions as new data is added.The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.

Create a table with range partition and interval clause

create table order_details(order_id number,order_date date) partition by range (order_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) (partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY'))) tablespace t1;

Table created.

Gather the stats

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

Check the partition created and NUM_ROWS under USER_TAB_PARTITIONS

COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';


TABLE_NAME      PARTITION  HIGH_VALUE			NUM_ROWS	
--------------- ---------- ---------------------------  --------
ORDER_DETAILS	P1	 TO_DATE(' 2007-11-01 00:00:00'    0 

Insert data with a ORDER_DATE  less than  ’01-NOV-2007′ the data will be placed in the existing partition p1 and no new partitions will be created.

INSERT INTO order_details VALUES (1, TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));

1 row created.

COMMIT;

Commit complete.

EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

Check the NUM_ROWS count

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='ORDER_DETAILS';

TABLE_NAME      PARTITION  HIGH_VALUE                    NUM_ROWS 
--------------- ---------- ---------------------------   --------
ORDER_DETAILS      P1      TO_DATE(' 2007-11-01 00:00:00'   1

If we add data beyond the range of the existing partition, a new partition is created ie., 01-JAN-2008

If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions ie. 2008-02-01

SQL> INSERT INTO order_details VALUES (5, TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));

1 row created.

SQL> INSERT INTO order_details VALUES (6, TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));

1 row created.
SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'ORDER_DETAILS');

PL/SQL procedure successfully completed.

 

SQL> SELECT table_name,partition_name,high_value,interval from user_tab_partitions where table_name='ORDER_DETAILS';

TABLE_NAME    PARTI HIGH_VALUE                               INT
----------    ----- ---------------------------------------- ---
ORDER_DETAILS P1    TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M  NO
M-DD HH24:MI:SS',   'NLS_CALENDAR=GREGORIA


ORDER_DETAILS SYS_P TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M YES
              625   M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA

Restrictions in Interval partitioning :

  • Interval partitioning is restricted to a single partition key that must be a numerical or date range.
  • At least one partition must be defined when the table is created.
  • Interval partitioning is not supported for index-organized tables.
  • You cannot create a domain index on an interval partitioned table.
  • Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can’t be used at the subpartition level.
  • A MAXVALUE partition cannot be defined for an interval partitioned table.
  • NULL values are not allowed in the partition column.

SYSTEM PARTITIONING  :

There are scenarios where a database developer or database designer is not able to make a logical way to partition a huge table. Oracle 11g has provided us a way to define partitions in an intelligent manner by System Partitioning, where application needs to control destination partition for a specific record. The DBA just needs to define the partitions.

Table Creation

SQL> create table system_order_details(order_id number,order_date date) partition by SYSTEM (PARTITION p1,PARTITION p2) tablespace t1;

Table created.

The partition must be explicitly defined in all insert statements or an error is produced.

SQL> INSERT INTO system_order_details VALUES (1, SYSDATE);
INSERT INTO system_order_details VALUES (1, SYSDATE)
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method

The PARTITION clause is used to define which partition the row should be placed in.

SQL> INSERT INTO system_order_details PARTITION (p1) VALUES (1, SYSDATE);

1 row created.

SQL> INSERT INTO system_order_details PARTITION (p2) VALUES (2, SYSDATE);

1 row created.

SQL> commit;

Commit complete.

SQL> EXEC DBMS_STATS.gather_table_stats('HARI', 'system_order_details');

PL/SQL procedure successfully completed.

Check the created system table partitions

SQL> COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT table_name, partition_name, high_value, num_rows FROM user_tab_partitions where table_name='SYSTEM_ORDER_DETAILS';

TABLE_NAME                PARTITION_NAME   HIGH_VALUE  NUM_ROWS
------------------------- ---------------  ----------  --------
SYSTEM_ORDER_DETAILS      P1                                 1
SYSTEM_ORDER_DETAILS      P2                                 1

Notice that the HIGH_VALUE for the partitions is blank.

The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION  clause is used, you must be sure to perform the operation against the correct partition.

For Delete and Update statements,

SQL> DELETE FROM SYSTEM_ORDER_DETAILS PARTITION (p2) WHERE order_id = 1;

0 rows deleted.

SQL> UPDATE SYSTEM_ORDER_DETAILS PARTITION (p1) SET order_id =3 WHERE order_id=2;

0 rows updated.

The PARTITION  clause can also be used to check specific partitions.

SQL> SELECT * FROM SYSTEM_ORDER_DETAILS PARTITION (p1);

ORDER_ID   ORDER_DAT
---------- ---------
1          24-SEP-18

When selecting, updating, or deleting data from a system partitioned object, the best practice is to always specify the partition to avoid scanning all partitions.  The use of system partitioning should be viewed with scrutiny by a DBA since it transfers responsibility of optimal use of partitions away from the DBA and to the developers and applications. 

REFERENCE PARTITIONING :-

Reference partitioning is for a child table is inherited from the parent table through a primary key – foreign key relationship. The partitioning keys are not stored in actual columns in the child table.

The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns.Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.

Parent table creation

CREATE TABLE parent_tab (
  id           NUMBER NOT NULL,
  code         VARCHAR2(10) NOT NULL,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
   PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
   PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);

Child table creation 

CREATE TABLE child_tab (
  id             NUMBER NOT NULL,
  parent_tab_id  NUMBER NOT NULL,
  code           VARCHAR2(10),
  description    VARCHAR2(50),
  created_date   DATE,
  CONSTRAINT child_tab_pk PRIMARY KEY (id),
  CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
                               REFERENCES parent_tab (id))
PARTITION BY REFERENCE (child_parent_tab_fk);

Insert data in parent table

INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE',ADD_MONTHS(SYSDATE,12));

Insert data in child table

INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
COMMIT;

Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table.So we insert two rows into the first partition

Gather stats for both tables 

SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>PARENT_TAB’);

Procédure PL/SQL successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname =>user,tabname=>CHILD_TAB’); 

Procédure PL/SQL successfully completed.

We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.

SELECT table_name, partition_name, high_value, num_rows
FROM   user_tab_partitions
ORDER BY table_name, partition_name;

TABLE_NAME   PARTITION_NAME HIGH_VALUE                        NUM_ROWS         
------------ -------------- -------------------------------- ----------
CHILD_TAB   PART_2007                                              1                1
CHILD_TAB   PART_2008                                              2               2
PARENT_TAB  PART_2007     TO_DATE(' 2008-01-01 00:00:00',          2
                          'SYYYY-MM-DD HH24:MI:SS',                   
                           'NLS_CALENDAR=GREGORIA

PARENT_TAB  PART_2008     TO_DATE(' 2009-01-01 00:00:00',          1          
                          'SYYYY-MM-DD HH24:MI:SS',                      
                           'NLS_CALENDAR=GREGORIA

4 rows selected.

 

COMPOSITE PARTITIONING :-

Composite partitioning is a combination of the basic partitioning techniques of Range, List, Hash, and Interval Partitionings.

Composite Partitioning:

RANGE-HASH PARTITION:

This is basically a combination of range and hash partitions. The data is divided using the range partition and then each range partitioned data is further subdivided into a hash partition using hash key values. All sub partitions, together, represent a logical subset of the data.

SQL> CREATE TABLE rng_hash (cust_id NUMBER(10),time_id DATE) PARTITION BY RANGE(time_id) SUBPARTITION BY HASH(cust_id) SUBPARTITION TEMPLATE(SUBPARTITION sp1 TABLESPACE t1,SUBPARTITION sp2 TABLESPACE t2,SUBPARTITION sp3 TABLESPACE t3)(PARTITION R1 VALUES LESS THAN ('01-apr-2010'),PARTITION R2 VALUES LESS THAN ('01-aug-2010'),PARTITION R3 VALUES LESS THAN ('01-dec-2010'),PARTITION R4 VALUES LESS THAN(MAXVALUE));

Table created.

 

RANGE-LIST PARTITION:

This is a combination of Range and List partitions, first the data is divided using the Range partition and then each Range partitioned data is further subdivided into List partitions using list key values. Each subpartition individually represents logical subset of the data not like composite Range-Hash Partition.

SQL> CREATE TABLE rng_list (cust_state VARCHAR2(2),time_id DATE)PARTITION BY RANGE(time_id)SUBPARTITION BY LIST (cust_state)SUBPARTITION TEMPLATE(SUBPARTITION south VALUES ('tn','py') TABLESPACE t1,SUBPARTITION east VALUES ('wb','bi') TABLESPACE t2,SUBPARTITION north VALUES ('jm','dl') TABLESPACE t3)(PARTITION a1 VALUES LESS THAN ('01-apr-2010'),PARTITION a2 VALUES LESS THAN ('01-aug-2010'),PARTITION a3 VALUES LESS THAN ('01-dec-2010'),PARTITION a4 VALUES LESS THAN(MAXVALUE));

Table created.

COMPRESS :-

Create a COMPRESS table,

SQL> CREATE TABLE sales (region VARCHAR2(10)) COMPRESS PARTITION BY LIST (region) (PARTITION southwest VALUES ('SOUTHWEST'),PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,PARTITION western VALUES ('WESTERN'));

Table created.

Check the COMPRESS and NOCOMPRESS partition

SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions where table_name='SALES';

TABLE_NAME   PARTITION_NAME       COMPRESS     COMPRESS_FOR
----------   -------------------- --------     ------------
SALES        NORTHEAST            DISABLED
SALES        SOUTHWEST            ENABLED      BASIC
SALES        WESTERN              ENABLED      BASIC

 

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba