12.2 move tables online

One of the really cool new features introduced in Oracle 12c release 2 is the capability to move tables ONLINE, while maintain all the associated index structures. This was possible for table partitions in Oracle 12c release 1, but only for index organized tables before then.

I will start by creating a table and specifically populate a CODE column with 100 distinct values that are distributed throughout the entire table structure.

demo@ORA12C> create table t as

  2  select rownum as id ,

  3     mod(rownum,100) as code,

  4     sysdate - mod(rownum,1000) as created,

  5     object_name

  6  from all_objects ,

  7      all_users

  8  where rownum <= 2000000;

 

Table created.

 

demo@ORA12C>

 

If we create an index on the CODE column, we will see the index has a terrible clustering factor, as the values basically appear throughout the entire table structure in each of the table blocks.

 

demo@ORA12C> create index t_idx on t(code) nologging;

 

Index created.

 

demo@ORA12C> select i.index_name, i.clustering_factor,i.status,

  2         t.num_rows, t.blocks

  3  from user_tables t ,

  4    user_indexes i

  5  where t.table_name = i.table_name

  6  and t.table_name ='T';

 

INDEX_NAME    CLUSTERING_FACTOR STATUS     NUM_ROWS     BLOCKS

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

T_IDX                   1583789 VALID       2000000      16663

 

 

If we now run a query that returns rows for just one CODE values (i.e. just 1% of the table).

 

demo@ORA12C> set autotrace traceonly explain statistics

demo@ORA12C> select * from t where code = 42;

 

20000 rows selected.

 

 

Execution Plan

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

Plan hash value: 1601196873

 

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 20000 |  1054K|  4542   (1)| 00:00:01 |

|*  1 |  TABLE ACCESS FULL| T    | 20000 |  1054K|  4542   (1)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   1 - filter("CODE"=42)

 

 

Statistics

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

          0  recursive calls

          4  db block gets

      16636  consistent gets

      16492  physical reads

          0  redo size

    1153360  bytes sent via SQL*Net to client

       2071  bytes received via SQL*Net from client

        135  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      20000  rows processed

 

demo@ORA12C> set autotrace off

 

We can see the CBO ignores the index and opts for a full table scan, with such a terrible clustering factor and with each block in the table having to be accessed; the CBO is making the correct decision here. The index is effectively useless, even though we are only interested in just 1% of the table.

 

If this query was important to us and executed frequently, we might want to look at improving the clustering factor and hence the efficiency of this index. So reorganize the table such that data in the table is sorted / clustered in CODE order.

 

Oracle 12c release 1 (12.1.0.2) introduced the concept of table attribute clustering, by which table after a reorg or bulk load will store the data based on this clustering attribute.

 

demo@ORA12C> alter table t add clustering by linear order(code);

 

Table altered.

 

demo@ORA12C>

 

This now ensures that data within the table will be in CODE order after a table re-org, but we need to reorg this table for this to take effect. However prior to 12.2 it wasn’t possible to subsequently MOVE the table ONLINE.

 

Online Table move is not possible prior to 12.2 (moving a table online in 11g (11.2.0.4) database, produces this error)

 

demo@ORA11G> alter table t move ONLINE;

alter table t move ONLINE

*

ERROR at line 1:

ORA-01735: invalid ALTER TABLE option

 

Moving a table previously caused locking issues and resulted in all associated index being unusable and having to rebuilt, further adding to the effective unavailability of the table.

 

demo@ORA11G> alter table t move ;

alter table t move

            *

ERROR at line 1:

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 

 

demo@ORA11G> alter table t move ;

 

Table altered.

 

demo@ORA11G> select i.index_name, i.clustering_factor,i.status,

  2         t.num_rows, t.blocks

  3  from user_tables t ,

  4    user_indexes i

  5  where t.table_name = i.table_name

  6  and t.table_name ='T';

 

INDEX_NAME    CLUSTERING_FACTOR STATUS     NUM_ROWS     BLOCKS

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

T_IDX                   1301875 UNUSABLE    2000000      13174

 

demo@ORA11G>

 

This now got changes in 12c release 2, where we can now move tables online while fully maintaining all associated indexes.

 

demo@ORA12C> alter table t move ONLINE;

 

Table altered.

 

demo@ORA12C> select i.index_name, i.clustering_factor,i.status,

  2         t.num_rows, t.blocks

  3  from user_tables t ,

  4    user_indexes i

  5  where t.table_name = i.table_name

  6  and t.table_name ='T';

 

INDEX_NAME     CLUSTERING_FACTOR STATUS     NUM_ROWS     BLOCKS

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

T_IDX                      16491 VALID       2000000      16663

 

 

After the table re-org, we note that not only is the index still a valid state, but because the table re-org effectively sorted the data in CODE order due to the attribute clustering, it has resulted in much more reduced clustering factor (just 16,491 reduced down from 1,583,789)

 

If we re-run our original query:

 

demo@ORA12C> select * from t where code = 42;

 

20000 rows selected.

 

 

Execution Plan

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

Plan hash value: 767293772

 

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

| Id  | Operation                           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT                    |       | 20000 |  1054K|   206   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T     | 20000 |  1054K|   206   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN                  | T_IDX | 20000 |       |    41   (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("CODE"=42)

 

 

Statistics

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

          0  recursive calls

          0  db block gets

        476  consistent gets

          0  physical reads

          0  redo size

    1214141  bytes sent via SQL*Net to client

       2071  bytes received via SQL*Net from client

        135  SQL*Net roundtrips to/from client

          0  sorts (memory)

          0  sorts (disk)

      20000  rows processed

 

demo@ORA12C>

 

We noticed that index is now used and has resulted in a significant reduction of consistent gets (just 476 down from 16636), the query is much more efficient than it was previously.

Intelligent partition pruning

Partition pruning is only possible in presence of partition key columns in predicates, but this scenario recently appeared in an application that breaks the rule.

demo@ORA11G> create table t

  2  partition by range(end_date)

  3  ( partition p2010 values less than (to_date('01-Jan-2011','dd-mon-yyyy')) ,

  4    partition p2011 values less than (to_date('01-Jan-2012','dd-mon-yyyy')) ,

  5    partition p2012 values less than (to_date('01-Jan-2013','dd-mon-yyyy')) ,

  6    partition p2013 values less than (to_date('01-Jan-2014','dd-mon-yyyy')) ,

  7    partition p2014 values less than (to_date('01-Jan-2015','dd-mon-yyyy')) ,

  8    partition p2015 values less than (to_date('01-Jan-2016','dd-mon-yyyy')) ,

  9    partition p2016 values less than (to_date('01-Jan-2017','dd-mon-yyyy')) ,

 10    partition p2017 values less than (to_date('01-Jan-2018','dd-mon-yyyy')) )

 11  as

 12  select a.* , created-1 as begin_date,

 13               created+1 as end_date

 14  from all_objects a;

 

Table created.

 

demo@ORA11G> exec dbms_stats.gather_table_stats(user,'T');

 

PL/SQL procedure successfully completed.

 

demo@ORA11G> set autotrace traceonly explain

demo@ORA11G> select *

  2  from t

  3  where begin_date between

  4     to_date('01-apr-2016','dd-mon-yyyy') and

  5     to_date('15-apr-2016','dd-mon-yyyy') ;

 

Execution Plan

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

Plan hash value: 3557914527

 

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

| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT    |      |   989 |   110K|   360   (1)| 00:00:05 |       |       |

|   1 |  PARTITION RANGE ALL|      |   989 |   110K|   360   (1)| 00:00:05 |     1 |     8 |

|*  2 |   TABLE ACCESS FULL | T    |   989 |   110K|   360   (1)| 00:00:05 |     1 |     8 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("BEGIN_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss') AND "BEGIN_DATE"<=TO_DATE(' 2016-04-15 00:00:00', 'syyyy-mm-dd

              hh24:mi:ss'))

 

 

The table is partitioned by end_date and the above query doesn’t have any filters/predicates on the end_date column so we scanned all the partitions to answer this query.

 

But if we could provide some information about the data representation, then the optimizer can make use of them during optimization to yield better plans.

 

demo@ORA11G> alter table t add constraint t_check

  2  check(begin_date <= end_date);

 

Table altered.

 

demo@ORA11G> alter table t modify begin_date not null;

 

Table altered.

 

demo@ORA11G> alter table t modify end_date not null;

 

Table altered.

 

 

So I have provided the information via the check constraint saying that begin_date and end_date are mandatory columns and begin_date should always be less than or equal to end_date column.

 

With these metadata in place, executing the above query would produce the plan like this.

 

demo@ORA11G> select *

  2  from t

  3  where begin_date between

  4     to_date('01-apr-2016','dd-mon-yyyy') and

  5     to_date('15-apr-2016','dd-mon-yyyy') ;

 

Execution Plan

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

Plan hash value: 1571388083

 

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

| Id  | Operation                | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |

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

|   0 | SELECT STATEMENT         |      |   989 |   110K|     7   (0)| 00:00:01 |       |       |

|   1 |  PARTITION RANGE ITERATOR|      |   989 |   110K|     7   (0)| 00:00:01 |     7 |     8 |

|*  2 |   TABLE ACCESS FULL      | T    |   989 |   110K|     7   (0)| 00:00:01 |     7 |     8 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - filter("BEGIN_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')

              AND "BEGIN_DATE"<=TO_DATE(' 2016-04-15 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND

              "END_DATE">=TO_DATE(' 2016-04-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 

 

 

With the presence of check and not null constraint, the optimizer effectively added the predicate on end_date column and resulted in scanning only a subset of partitions.

How to get an execution plan

One of the things I do fairly regularly when struggling with a SQL statement that it is not behaving well goes like this.

• Get the copy of the statement into a script file
• Add gather_plan_statistics hint or set statistics_level =all
• Bind all those inputs
• Run it
• Run xplan on it.

drop table t1 purge;

drop table t2 purge;

create table t1 as select * from all_objects;

create table t2 as select * from all_objects;

create index t1_idx on t1(owner);

Lets say this sql is not behaving well from an application.

select t1.owner, max(t2.object_id), count(*)

from t1, t2

where t1.object_id = t2.object_id

and t2.owner = :x

group by t1.owner

order by t1.owner;

Then we could follow either of these options to generate an execution plans.

Option#1 – set the parameter statistics_level=all

Script1.sql 

variable x varchar2(30)

exec :x := 'SCOTT';

set serveroutput off linesize 300 pagesize 9999

alter session set statistics_level=all;

set termout off

select t1.owner,max(t1.object_id),count(*)

from t1 , t2

where t1.object_id = t2.object_id

and t2.owner = :x

group by t1.owner;

set termout on

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

Executing the above script from SQL*Plus produces the output like this.

demo@ORA12C> @d:\script1.sql

PL/SQL procedure successfully completed.

Session altered.

PLAN_TABLE_OUTPUT

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

SQL_ID  4h1b567g4uh7h, child number 0

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

select t1.owner,max(t1.object_id),count(*) from t1 , t2 where

t1.object_id = t2.object_id and t2.owner = :x group by t1.owner

Plan hash value: 51733071

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

| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.59 |    2634 |   2629 |       |       |          |

|   1 |  HASH GROUP BY      |      |      1 |     20 |      1 |00:00:00.59 |    2634 |   2629 |  1079K|  1079K|  487K (0)|

|*  2 |   HASH JOIN         |      |      1 |   3872 |      4 |00:00:00.59 |    2634 |   2629 |  2168K|  2168K|  921K (0)|

|*  3 |    TABLE ACCESS FULL| T2   |      1 |   3872 |      4 |00:00:00.36 |    1317 |   1315 |       |       |          |

|   4 |    TABLE ACCESS FULL| T1   |      1 |  77430 |  77430 |00:00:00.22 |    1317 |   1314 |       |       |          |

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

Predicate Information (identified by operation id):

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

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   3 - filter("T2"."OWNER"=:X)

23 rows selected.

demo@ORA12C>

Else, you could introduce gather_plan_statistics hint to the sql like this.

Option#2 – add the hint gather_plan_statistics to the SQL

Script2.sql

variable x varchar2(30)

exec :x := 'SCOTT';

set serveroutput off linesize 300 pagesize 9999

set termout off

select /*+ gather_plan_statistics */ t1.owner,max(t1.object_id),count(*)

from t1 , t2

where t1.object_id = t2.object_id

and t2.owner = :x

group by t1.owner;

set termout on

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

Executing the above script from SQL*Plus produces the output like this.

demo@ORA12C> conn demo/demo@ora12c

Connected.

demo@ORA12C> @d:\script2.sql

PL/SQL procedure successfully completed.

PLAN_TABLE_OUTPUT

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

SQL_ID  7v8mwngxy82zx, child number 0

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

select /*+ gather_plan_statistics */

t1.owner,max(t1.object_id),count(*) from t1 , t2 where t1.object_id =

t2.object_id and t2.owner = :x group by t1.owner

Plan hash value: 51733071

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

| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT    |      |      1 |        |      1 |00:00:00.43 |    2634 |   2629 |       |       |          |

|   1 |  HASH GROUP BY      |      |      1 |     20 |      1 |00:00:00.43 |    2634 |   2629 |  1079K|  1079K|  453K (0)|

|*  2 |   HASH JOIN         |      |      1 |   3872 |      4 |00:00:00.42 |    2634 |   2629 |  2168K|  2168K|  999K (0)|

|*  3 |    TABLE ACCESS FULL| T2   |      1 |   3872 |      4 |00:00:00.15 |    1317 |   1315 |       |       |          |

|   4 |    TABLE ACCESS FULL| T1   |      1 |  77430 |  77430 |00:00:00.02 |    1317 |   1314 |       |       |          |

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

Predicate Information (identified by operation id):

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

   2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

   3 - filter("T2"."OWNER"=:X)

24 rows selected.

demo@ORA12C>

Using this information it is very easy to see how the optimizer estimates line up with reality, and we can see “where” the optimizer is having the problem by comparing A-ROWS and E-ROWS column.

This information should be sufficient to start focusing the performance problem, but if you are interested to understand what those other columns from the execution plan represents, have a look at them here

Addendum: added on 10/31/2018

To get an execution plan, we need READ privilege (starting with 12c READ privilege and SELECT privilege on prior to 12c) on the below v$ tables

grant read on v_$sql                       to YOUR_USER_ACCOUNT;

grant read on v_$sql_plan                  to YOUR_USER_ACCOUNT;

grant read on v_$sql_plan_statistics_all   to YOUR_USER_ACCOUNT;

grant read on v_$session                   to YOUR_USER_ACCOUNT;