Thanks for the question, A.

Asked: October 17, 2015 - 7:51 am UTC

Last updated: November 11, 2015 - 7:15 am UTC

Version: 10.2.0.5

Viewed 1000+ times

You Asked

Hi Tom,

Thanks for your time.

we have current table t1 as below.

CREATE TABLE MAN.t1
(
PARAMETERINSTANCEID NUMBER,
PARAMINSTANCE2PARAMSETVERSION NUMBER,
PARAMINSTANCE2PARAMDEFINITION NUMBER,
PARAMINSTANCE2PARENTPARAM NUMBER,
SEQUENCE NUMBER,
X_CTCV_CONV_ID VARCHAR2(50 CHAR),
X_CONV_RUN_NO NUMBER,
SEQ NUMBER
)
PARTITION BY RANGE (PARAMINSTANCE2PARAMSETVERSION)
(
PARTITION PI_P01 VALUES LESS THAN (1000000000)
PARTITION PI_P02 VALUES LESS THAN (2000000000)

..
PARTITION PI_P10 VALUES LESS THAN (10000000000)
PARTITION PI_PMAXVALUE VALUES LESS THAN (MAXVALUE)
)

t1 table has LOCAL indexes on Partition key column PARAMINSTANCE2PARAMDEFINITION

CREATE INDEX MAN.PI_PAD_FK_I ON MAN.t1
(PARAMINSTANCE2PARAMDEFINITION)
LOGGING
LOCAL (
PARTITION PI_P01
PARTITION PI_P02
...
PARTITION PI_PMAXVALUE
)

t1 table has LOCAL indexes on Partition key column PARAMETERINSTANCEID
CREATE INDEX MAN.PI_PK ON MAN.t1
(PARAMETERINSTANCEID)

LOGGING
LOCAL (
PARTITION PI_P01
PARTITION PI_P02
...
PARTITION PI_PMAXVALUE
)

We are re-partitioning the table and new partitioned table will have range of each partition reduced to 100,000,000 from 1,000,000,000 ( current) and all indexes will remain the same (i.e. LOCAL)

CREATE TABLE MAN.t2
(
PARAMETERINSTANCEID NUMBER,
PARAMINSTANCE2PARAMSETVERSION NUMBER,
PARAMINSTANCE2PARAMDEFINITION NUMBER,
PARAMINSTANCE2PARENTPARAM NUMBER,
SEQUENCE NUMBER,
X_CTCV_CONV_ID VARCHAR2(50 CHAR),
X_CONV_RUN_NO NUMBER,
SEQ NUMBER
)
PARTITION BY RANGE (PARAMINSTANCE2PARAMSETVERSION)
(
PARTITION PI_P01 VALUES LESS THAN (100000000)
PARTITION PI_P02 VALUES LESS THAN (200000000)

..
PARTITION PI_PMAXVALUE VALUES LESS THAN (MAXVALUE)
)

Table size is 1000GB and indexes size is 1500GB , table has 25 billion rows.

Summary/ Question
1) what is the best strategy to re-partition this table? considering the downtime, rollback
2) we tried the below approach but keeping the table but it is not efficient
at time t1: create new partitioned table t2 and corresponding indexes
at time t2 : put a trigger on current production table t1
at time t3 : start inserting data into table t2 in batches ( 10 sessions at a time) selecting data from table t1 for various chunk of PARAMETERINSTANCEID column i.e. insert into t2 select * from t1 where PARAMETERINSTANCEID between x and y ;
at time t<n-1> : once all inserts are done... process the delta captured by trigger i.e. insert that data as well.
at time t<n> : Shut-down the application, process the last delta captured by trigger. rename t1 to t1_old & rename t2 to t1

This approach is generating a lot redo (~50 GB/hour with concurrent 20 inserts ) and underlying disk/storage system is unable to cope with it .
3) In order to reduce redo , keeping table into no logging mode is not an option here because we need to use APPEND hint to reduce the redo ( DB is in Archivelog mode) . But using the approach mentioned at#2, we need to run multiple inserts sessions at the same time.. so APPEND hint will serialise these further.
4) Split partition is one option , but we need to have quicker rollback point with almost zero downtime.
5) I am sure you will ask why re-partitioning is being done ... Do you see we really need to do this split partitioning when today table t1 is having 10 partitions but the 90% data is in 1st partition ( PI_P01) and remaining 10% in 2nd partition ( PI_P02) and data is growing on 2nd partition. By splitting each of the partition by into 10 we will have data distributed across the partitions. Application queries never select more than 10 to 20 rows from this table using LOCAL index .. rowiD path . so entire partition is rarely scanned. We see some of the bacth jobs scans massive data and partition prune will help those queries/jobs .

regards

and Connor said...

If you've got the space (and your redo can cope) then dbms_redefinition sounds the easiest option. It wont be the *quickest*, but your downtime will be near zero.

Otherwise, a variant on your approach would be:

1) triggers on T1 to capture deltas

2) create table T2 *with no indexes* - this table can be nologging because its not needed for recovery if you have a database problem, because its simply a copy of t1

3) Use insert /*+ append */ at the partition level, ie,

insert /*+ APPEND */ into T2 partition (p1) select * from T1 where ...

Because they are at the partition level, they can be done concurrently and will generate little redo.

4) Once population complete, *then* create the local indexes on T2

5) Make T2 logging and backup the tablespace(s) its in.

6) Now apply the deltas and do a 'switchover'

Hope this helps.

Rating

(7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Partition..

A Reader, October 19, 2015 - 8:52 pm UTC

Thank you so much Connor !
so further in step 3

....
3) Use insert /*+ append */ at the partition level, ie,
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where ...
....

Actual statement would be as below
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where PARAMINSTANCE2PARAMSETVERSION between 1 and 100000000

since each partition range is 100,000,000 ( PARAMINSTANCE2PARAMSETVERSION - partition key )
we would like to split these inserts further say batch size of 50k each .. because there might be more than 100,000,000 records or less in this insert

nick@ora10g:OracleVM> select 'p1', min(PARAMINSTANCE2PARAMSETVERSION), max( PARAMINSTANCE2PARAMSETVERSION) , count(*)
from man.PARAMETERINSTANCE where PARAMINSTANCE2PARAMSETVERSION between 1 and 100000000; 2

'P MIN(PARAMINSTANCE2PARAMSETVERSION) MAX(PARAMINSTANCE2PARAMSETVERSION) COUNT(*)
-- ---------------------------------- ---------------------------------- ----------
p1 26194 98647574 477419

So we wanted to break these 477419 rows which are candidate for T2 table partition p1 into equal batch size of 50k ( ~10 batches ) ?

using PERCENTILE_DISC function

nick@ora10g:OracleVM>
select min(PARAMINSTANCE2PARAMSETVERSION) -1 c0,
PERCENTILE_DISC(0.10) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c1,
PERCENTILE_DISC(0.20) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c2,
PERCENTILE_DISC(0.35) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c3,
PERCENTILE_DISC(0.45) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c4,
PERCENTILE_DISC(0.50) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c5,
PERCENTILE_DISC(0.60) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c6,
PERCENTILE_DISC(0.70) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c7,
PERCENTILE_DISC(0.80) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c8,
PERCENTILE_DISC(0.95) within group ( order by PARAMINSTANCE2PARAMSETVERSION) c9,
max(PARAMINSTANCE2PARAMSETVERSION) c10
from man.PARAMETERINSTANCE where PARAMINSTANCE2PARAMSETVERSION between 1 and 100000000;

C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
26193 6505683 13436355 20169031 24902675 27387429 45257791 46771934 46774125 89051678 98647574

But these ranges are not equal sized ( as PARAMINSTANCE2PARAMSETVERSION is not equally spaced across each of the group )
columns : PARAMETERINSTANCEID is primary key and composite ( PARAMINSTANCE2PARAMSETVERSION, PARAMINSTANCE2PARAMDEFINITION ) is unique key

Any inputs here would be much appreciated.

October 20, 2015 - 1:18 am UTC

OK, you've lost me a bit here.

If the new table is going to be ranged partitioned (in ranges of 1m), then you dont really get a choice of *how many* rows go into each partition, because that is defined by the hwm of each partition.

So I had envisaged the following (commas in the digits just for readability)

insert /*+ APPEND */ into T2 partition (p1)
select * from T1 where C between 1 and 1,000,000,000-1

insert /*+ APPEND */ into T2 partition (p2)
select * from T1 where C between 1,000,000,000 and 2,000,000,000-1

insert /*+ APPEND */ into T2 partition (p3)
select * from T1 where C between 2,000,000,000 and 3,000,000,000-1

etc etc etc

They can be run in concurrently

Does that make sense ?

Partition..

A Reader, October 20, 2015 - 2:59 am UTC

Agree with you that I can run concurrent inserts on each of the partitions..

[ Just little data correction as I will be using the same below . new table T2 is going to be ranged partitioned (in ranges of 100m), and therefore I have changed the INSERT statements accordingly in this example.]

insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 1 and 100,000,000-1
insert /*+ APPEND */ into T2 partition (p2) select * from T1 where C between 100,000,000 and 200,000,000-1
....
....

The point which I was trying to highlight was that , I cannot run the 'single insert' statement for each of the partition at one go.

e.g.( for a single partition p1 )

insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 1 and 100,000,000-1
///
we are avoiding this 'single' insert statement because had insert statement to break due to some reason, we will loose all day(s) work done here.
so doing in smaller chunks and cementing the work, is the approach we would like to follow.. in production system these numbers are big so we expect it would take day(s) to insert one partition data.
///

So instead, what we are trying to do is
run the inserts into each partition in a smaller chunks of say 1m and not for the complete range 1 to 100,000,000-1 at one go
so for ex in partition (p1), we will have following statements ( of course all will be run sequentially )

insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 1 and 10,000,000-1
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 10,000,000 and 2,000,000-1
....
....
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between (100,000,000-1)-10,000,000 and 100,000,000

So in summary here, I have splitted the entire range of 100m (which are the candidate rows for partition p1) into batches of 10m (so total 100,000,000 /10,000,000 = 10 batches)

But each batch doesnt have the equal number of rows inserted. As we can see in below example there are ~477k candidate rows. with 10 batches I was expecting each of the batch to be equi sized ~47k
but as we can see some of the ranges are oversized ( range 9) and some undersized ( range 10). we know reason for it is partition key data is not uniqueue in each of the partition.

SELECT MIN(PARAMINSTANCE2PARAMSETVERSION) -1 c0,
PERCENTILE_DISC(0.10) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c1,
PERCENTILE_DISC(0.20) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c2,
PERCENTILE_DISC(0.35) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c3,
PERCENTILE_DISC(0.45) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c4,
PERCENTILE_DISC(0.50) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c5,
PERCENTILE_DISC(0.60) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c6,
PERCENTILE_DISC(0.70) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c7,
PERCENTILE_DISC(0.80) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c8,
PERCENTILE_DISC(0.95) within GROUP (
ORDER BY PARAMINSTANCE2PARAMSETVERSION) c9,
MAX(PARAMINSTANCE2PARAMSETVERSION) c10
FROM man.PARAMETERINSTANCE
WHERE PARAMINSTANCE2PARAMSETVERSION BETWEEN 1 AND 100,000,000;

C0 C1 C2 C3 C4 C5 C6 C7 C8 C9 C10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
26193 6505683 13436355 20169031 24902675 27387429 45257791 46771934 46774125 89051678 98647574

So my insert statements would get changed to
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 1 and 26,193-1
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 26,193 and 6,505,683-1
....
....
insert /*+ APPEND */ into T2 partition (p1) select * from T1 where C between 89,051,678 and 98,647,574

SELECT SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 26193
AND PARAMINSTANCE2PARAMSETVERSION <=6505683
THEN 1
ELSE 0
END ) range1,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 6505683
AND PARAMINSTANCE2PARAMSETVERSION <= 13436355
THEN 1
ELSE 0
END ) range2,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 13436355
AND PARAMINSTANCE2PARAMSETVERSION <= 20169031
THEN 1
ELSE 0
END ) range3,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 20169031
AND PARAMINSTANCE2PARAMSETVERSION <= 24902675
THEN 1
ELSE 0
END ) range4,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 24902675
AND PARAMINSTANCE2PARAMSETVERSION <= 27387429
THEN 1
ELSE 0
END ) range5,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 27387429
AND PARAMINSTANCE2PARAMSETVERSION <= 45257791
THEN 1
ELSE 0
END ) range6,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 45257791
AND PARAMINSTANCE2PARAMSETVERSION <= 46771934
THEN 1
ELSE 0
END ) range7,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 46771934
AND PARAMINSTANCE2PARAMSETVERSION <= 46774125
THEN 1
ELSE 0
END ) range8,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 46774125
AND PARAMINSTANCE2PARAMSETVERSION <= 89051678
THEN 1
ELSE 0
END ) range9,
SUM (
CASE
WHEN PARAMINSTANCE2PARAMSETVERSION > 89051678
AND PARAMINSTANCE2PARAMSETVERSION <= 98647574
THEN 1
ELSE 0
END ) range10,
COUNT(*)
FROM man.PARAMETERINSTANCE
WHERE PARAMINSTANCE2PARAMSETVERSION BETWEEN 1 AND 100000000;

RANGE1 RANGE2 RANGE3 RANGE4 RANGE5 RANGE6 RANGE7 RANGE8 RANGE9 RANGE10 COUNT(*)
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
47751 47756 71616 47717 23906 47758 47708 47727 71619 23861 477419

So what would be the best way to go here ..Objective is to inserts data of equal row sized batches one after the other in a given partition?
Also any other method using rowid of the candidate rows ... WHERE clause as ( rowid between xx and yy ) etc ..?
Hope I was able to explain the problem statement here and many thanks for your time and support once again !

October 20, 2015 - 3:50 am UTC

Hmmm...unless your rows are massively wide, I cannot think of why it would take day(s) to load 100 million rows into an *unindexed* table that is set to nologging. That's a problem right there in itself.

eg lets say 100m rows at 1KB wide (which is pretty wide) over a terrible 1 Gigabit interface (ie, what an old PC would have)

SQL> select 100 * 1000 * 1000  -- rows
  2    *    1000               -- width
  3    *    8                  -- bits per byte
  4    /    1024 / 1024 / 1024 -- gigabit interface
  5    /    0.8 secs           -- 20% network overhead
  6  from dual;

      SECS
----------
931.322575

So 15 minutes perhaps...

Partition..

A Reader, October 21, 2015 - 3:59 am UTC

Thanks Conor !
yeah we are doing some tests to workarout the timings with these inserts...

So, once data copy,index creations, backups, delta copy is done , we will rename the tables . T2(stage) table renamed to T1(application table).
Next moving on another bit - Table/Partitions/Indexes Statistics and SQL Plan stability
I have tried to explain the same as follows.

A) Current statistics:
=====================
Historically all the stats are locked and as we know current application table T1 has data in only 1st Partition PI_P01 completely filled and 2nd partition PI_P02 partially filled.
But all the partitions ( table/ index) stats have been set to the max and sealed. there is one exception to this - Index PI_PAD_FK_I is a forgein key column where no stats are set...
but the good part is that this index is not used by application at all and it is kept only for the sake oif avoiding locks when parent key column is updated etc.
Primarily priority is plan stability here. and that might be the reason for keepng the stats locked and fixed.
( old theory - big table/parition will be always big etc..)
All the indexes are LOCAL index.

SQL> select table_name, partition_name,object_type,num_rows, blocks,GLOBAL_STATS,LAST_ANALYZED, STATTYPE_LOCKED, stale_stats from dba_tab_statistics where
table_name in ('T1');

TABLE_NAME                     PARTITION_NAME                 OBJECT_TYPE          NUM_ROWS           BLOCKS GLOBAL_STATS    LAST_ANAL STATTYPE_LOCKED      STALE_STATS
------------------------------ ------------------------------ ------------ ---------------- ---------------- --------------- --------- -------------------- --------------------
T1                                                            TABLE           3,222,108,620       12,305,272 YES             11/MAY/10 ALL                  YES
T1                 PI_P01                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  YES
T1                 PI_P02                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  YES
T1                 PI_P03                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P04                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P05                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P06                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P07                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P08                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                 PI_P09                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                     PI_P10                         PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO
T1                   PI_PMAXVALUE                   PARTITION       1,032,041,760        3,833,381 YES             20/MAY/10 ALL                  NO

12 rows selected.

SQL> select INDEX_NAME, PARTITION_NAME, object_type, blevel, LEAF_BLOCKS, DISTINCT_KEYS, CLUSTERING_FACTOR, num_rows,SAMPLE_SIZE, LAST_ANALYZED, GLOBAL_STATS,
2 USER_STATS, STATTYPE_LOCKED, STALE_STATS
3 from dba_ind_statistics where
4 table_name in ('T1')
5 order by index_name, PARTITION_NAME;

INDEX_NAME      PARTITION_NAME  OBJECT_TYPE  BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR      NUM_ROWS SAMPLE_SIZE LAST_ANAL GLOBAL_STATS    USER_STATS      STATTYPE_LOCKED         STALE_STATS
--------------- --------------- ------------ ------ ----------- ------------- ----------------- ---------------- ----------- --------- --------------- --------------- -------------------- --------------------
PI_PAD_FK_I     PI_P01          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P02          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P03          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P04          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P05          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P06          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P07          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P08          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P09          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_P10          PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I     PI_PMAXVALUE    PARTITION                                                                                      YES             YES             ALL
PI_PAD_FK_I                     INDEX             3     7448020           125         563441880    3,236,559,040   161827952 11/MAY/10 YES             NO              ALL                  YES
PI_PI_FK_I      PI_P01          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  YES
PI_PI_FK_I      PI_P02          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  YES
PI_PI_FK_I      PI_P03          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P04          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P05          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P06          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P07          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P08          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P09          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_P10          PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I      PI_PMAXVALUE    PARTITION         0           0             0                 0             0   2500 20/MAY/10 YES             YES             ALL                  NO
PI_PI_FK_I                      INDEX             0           0             0                 0             0      0 11/MAY/10 YES             NO              ALL                  YES
PI_PK           PI_P01          PARTITION         3     2573060    1032041760         518289160    1,032,240,640    51612032 20/MAY/10 YES             YES             ALL                  YES
PI_PK           PI_P02          PARTITION         3     2573060    1032041760         518289160    1,032,240,640    39695313 20/MAY/10 YES             YES             ALL                  YES
PI_PK           PI_P03          PARTITION         3     2573060    1032041760         518289160    1,032,240,640    31971072 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P04          PARTITION         3     2573060    1032041760         518289160    1,032,240,640        2500 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P05          PARTITION         3     2573060    1032041760         518289160    1,032,240,640        2500 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P06          PARTITION         3     2573060    1032041760         518289160    1,032,240,640        2500 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P07          PARTITION         3     2573060    1032041760         518289160    1,032,240,640        2500 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P08          PARTITION         3     2573060    1032041760         518289160    1,032,240,640        2500 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P09          PARTITION         3     2573060    1032041760         518289160    1,032,240,640    11301513 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_P10          PARTITION         3     2573060    1032041760         518289160    1,032,240,640    17630552 20/MAY/10 YES             YES             ALL                  NO
PI_PK           PI_PMAXVALUE    PARTITION         3     2573060    1032041760         518289160    1,032,240,640     8146607 20/MAY/10 YES             YES             ALL                  NO
PI_PK                           INDEX             3     8398280     393701412        1841488260    3,198,743,340   159937167 11/MAY/10 YES             NO              ALL                  YES
PI_UK           PI_P01          PARTITION         3     3042620    1039253300         565922680    1,039,253,300    51962665 20/MAY/10 YES             YES             ALL                  YES
PI_UK           PI_P02          PARTITION         3     3042620    1039253300         565922680    1,039,253,300    39993712 20/MAY/10 YES             YES             ALL                  YES
PI_UK           PI_P03          PARTITION         3     3042620    1039253300         565922680    1,039,253,300    31346655 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P04          PARTITION         3     3042620    1039253300         565922680    1,039,253,300        2500 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P05          PARTITION         3     3042620    1039253300         565922680    1,039,253,300        2500 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P06          PARTITION         3     3042620    1039253300         565922680    1,039,253,300        2500 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P07          PARTITION         3     3042620    1039253300         565922680    1,039,253,300        2500 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P08          PARTITION         3     3042620    1039253300         565922680    1,039,253,300        2500 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P09          PARTITION         3     3042620    1039253300         565922680    1,039,253,300    11260216 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_P10          PARTITION         3     3042620    1039253300         565922680    1,039,253,300    17540534 20/MAY/10 YES             YES             ALL                  NO
PI_UK           PI_PMAXVALUE    PARTITION         3     3042620    1039253300         565922680    1,039,253,300     8564924 20/MAY/10 YES             YES             ALL                  NO
PI_UK                           INDEX             3     9995980    3234342560        1685810760    3,234,342,560   161717128 11/MAY/10 YES             NO              ALL                  YES

48 rows selected.

T1 table has 10 range partitioned (each 1,000,000,000 = 1b )
T2 table (new ) we have created 50 partitions and each ranged partitioned ( each 100,000,000 = 100m)

We have below 03 options when it comes to the stats management for the new table T2
a) copy / import stats from existing table/ index partitions to the new table/index partitions [we have more partitions in new table T2 so , stats are same in each of the
source T1 table paritions, so from either of the source partition stats can be copied to new partitions ] and lock these. As we have reduced the range of each partition in T2 (new) by 1/10, so in theory the existing stats 'should' work?
b) gather stats on each of the table/ index partitions and copy the same to remaining empty index and table partitions and lock these.
c) gather stats on each of the table/ index partitions at regular intervals (donot lock )

Your view on above please? most of the query retunrs small chunk (few tens) of rows from this table T1 table. No entire partition scan( pruning) is performed by the querries.

B) SQL Plan stability
=====================
We would like the SQL plan shouldnt get adversely affected here by this change . So we are capturing the SQL plan for the existing sqls and will enforce , just in case we see change in plan.
Your view on this please.

( and my apologies for the not been able to format the fixed -width data nicely,I used code tag but in preview one single line is split into two, i I think total width is the issue )

October 21, 2015 - 4:09 am UTC

Yes, we have an existing bug on format width - hopefully we'll fix that in the next release of the asktom app.

Some thoughts

With (a), you can use dbms_stats.copy_table_stats, which does some nice auto-adjustment for partition key values. In either case, you would probably want to making some manual adjustments afterwards anyway for blocks/rows/distinct keys etc...which leads me to

(b) would be my preferred, in that since you are doing this large migration, it seems an opportune time to get good quality accurate stats on all of the historical data (and then lock it)

With (c), for large tables, I'm not convinced that gathering stats "all the time" really is necessary. But I do think its important that people "investigate" their stats regularly. For example, you might want to adjust some low/high water marks etc...

SQL Plan stability/management is a very good idea in my opinion.

Hope this helps.

Partition..

A Reader, October 31, 2015 - 10:36 pm UTC

Thank you Connor !

Further regarding SQL Plan stability/management ( sorry if the topic is diverting little from Original the post)

a) We are at 10.2.0.5
b) SPM and Plan baselines is not available ( as we know it is 11g onwards)
c) So looks SQL Profiles is the option here for switching back to Original SQL plan in case the new plan with post switch is suboptimal ?
d) Next how do we do this using SQL profiles?

Say before switchover, if sql plan is x for the sql_id
and post switchover the plan changes to y for the given sql_id which is suboptimal.

then , how to force the old plan x for the given sql_id ?

regards

November 02, 2015 - 1:06 am UTC

Here's a good blog post from a friend

http://oracle-randolf.blogspot.com.au/2009/03/plan-stability-in-10g-using-existing.html

and he links to other nice bits of information as well

Hope this helps

Partition..

A Reader, November 10, 2015 - 7:55 am UTC

Thanks Connor !

Next - LOCAL indexes vs GLOBAL indexes and how bad INDEXING scheme / bad design has aggregated problem further by having more partitions .
But yes we have not yet switched the tables. During validations of new table table T2 we found LOCAL indexing scheme is hurting a lot ! ( as expected)

I have tried to summarize it as below and have asked the questions in-line.
( I have renamed the column name etc in order to mask few things here )

T2 - new ranged partitioned table ( range 100m) -- data copied from T1 table
T1 - original partitioned table ( range - 1 bn)
T2_I1 - index on T2 (c1 column)
T1_I1 - index on T1 (c1 column )
other_app_table_t - another application table , which is joined with T1 table
Idx_2 - Index on other_app_table_t ( c5 column)

Using new table T2 ( with 100 million partitioned range and 39 partitions) the query plan is as follows.

select /*+ gather_plan_statistics */ a.* from T2 a, other_app_table_t b where a.c1=b.c3
and upper(b.c5)='12345'

--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID   | T2                    |      1 |      8 |   168 |   122   (0)|       |       |     74 |00:00:00.01 |    4313 |
|   2 |   NESTED LOOPS                       |                        |      1 |  26899 |  1024K|   402K  (1)|       |       |    149 |00:00:00.10 |    4240 |
|   3 |    PARTITION RANGE ALL               |                        |      1 |   3287 | 59166 |   838   (1)|     1 |    11 |     74 |00:00:00.01 |      91 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| other_app_table_t      |     11 |   3287 | 59166 |   838   (1)|     1 |    11 |     74 |00:00:00.01 |      91 |
|*  5 |      INDEX RANGE SCAN                | Idx_2                  |     11 |   2579 |       |    42   (0)|     1 |    11 |     74 |00:00:00.01 |      27 |
|   6 |    PARTITION RANGE ALL               |                        |     74 |      8 |       |   117   (0)|     1 |    39 |     74 |00:00:00.02 |    4149 |
|*  7 |     INDEX RANGE SCAN                 | T2_I1        |   2886 |      8 |       |   117   (0)|     1 |    39 |     74 |00:00:00.01 |    4149 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------

- there is problem with other_app_table_t which is suffering the same syndrome as our T2 ( new table ) is .and stats are way off . but parking that at the moment.

- the index ranged scan step

|* 7 | INDEX RANGE SCAN | T2_I1 | 2886 | 8 | | 117 (0)| 1 | 39 | 74 |00:00:00.01 | 4149 |
there are 4149 buffer consistent gets. I could justify it close to I was able to get the buffers = 4144= (each BLEVEL+ 1)*74

INDEX T2_I1 BLEVEL is mentioned below in few lines ..( I did gathered stats on T2_I1 )

#Question1 - where I am missing those 5 (=4149-4144) buffers ?

#Question2 - Does base index ( null partition_name ) ever scanned in a LOCAL index ? in below output it has BELEVEL of 2 -- 1st line in the output

for index T2_I1 -- below is the BLEVEL including partition

  PARTITION_NAME                     BLEVEL
 ------------------------------ ----------
                                         2
 T2_P01                                  1
 PI_P02                                  1
 PI_P03                                  1
 PI_P04                                  1
 PI_P05                                  1
 PI_P06                                  1
 PI_P07                                  1
 PI_P08                                  1
 PI_P09                                  1
 PI_P10                                  1
 PI_P11                                  1
 PI_P12                                  1
 PI_P13                                  2
 PI_P14                                  1
 PI_P15                                  2
 PI_P16                                  0
 PI_P17                                  0
 PI_P18                                  0
 PI_P19                                  0
 PI_P20                                  0
 PI_P21                                  0
 PI_P22                                  0
 PI_P23                                  0
 PI_P24                                  0
 PI_P25                                  0
 PI_P26                                  0
 PI_P27                                  0
 PI_P28                                  0
 PI_P29                                  0
 PI_P30                                  0
 PI_P31                                  0
 PI_P32                                  0
 PI_P33                                  0
 PI_P34                                  0
 PI_P35                                  0
 PI_P36                                  0
 PI_P37                                  0
 PI_P38                                  0
 PI_PMAXVALUE                            0

Using original table T1 ( with 1 billion partitioned range - total 11 partitions ) the query plan is as follows.

select /*+ gather_plan_statistics */ a.* from T1 a, other_app_table_t b where a.c1=b.c3
and upper(b.c5)='12345'

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                   | Starts | E-Rows |E-Bytes| Cost (%CPU)| Pstart| Pstop | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   1 |  TABLE ACCESS BY LOCAL INDEX ROWID   | T1                     |      1 |      8 |   168 |    24   (0)|       |       |     74 |00:00:00.01 |    1123 |    170 |
|   2 |   NESTED LOOPS                       |                        |      1 |  44481 |  1607K|   132K  (1)|       |       |    149 |00:00:00.04 |    1049 |    110 |
|   3 |    PARTITION RANGE ALL               |                        |      1 |   5435 | 86960 |  1879   (1)|     1 |    11 |     74 |00:00:00.01 |      86 |     78 |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID| other_app_table_t      |     11 |   5435 | 86960 |  1879   (1)|     1 |    11 |     74 |00:00:00.01 |      86 |     78 |
|*  5 |      INDEX RANGE SCAN                | idx_2                  |     11 |   4017 |       |    36   (0)|     1 |    11 |     74 |00:00:00.01 |      23 |     15 |
|   6 |    PARTITION RANGE ALL               |                        |     74 |      8 |       |    22   (0)|     1 |    11 |     74 |00:00:00.01 |     963 |     32 |
|*  7 |     INDEX RANGE SCAN                 | T1_I1                  |    814 |      8 |       |    22   (0)|     1 |    11 |     74 |00:00:00.01 |     963 |     32 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

- Here also problem exists but not to the extent compared to new table T2 where we have 39 partitions and hence 39 LOCAL Index scans.. ( buffer are 963)

#Question3# - we see changing T2_I1 to GLOBAL INDEX on new table T2 is the solution here. ? do you agree? Having global index would add additional overhead ? I think at
most the height of index might increase by 1 , but still it would do lesser buffer reads compared to LOCAL index

#Question4# - Earlier Application designer said they opted LOCAL index thinking partition maintenance etc
but in reality , we do ZERO partition maintenance, Index rebuild ( we shouldn't do that anyways .. ) , there is no daily partition load.. etc ..
data grows continuously .. Just to summarize it in short : we are OLTP so I see the simpler solution of the problem is get rid of partitioning here ,
do you agree?

#Question5# c1 column on table T1 and new table T2 is generated from Oracle sequence, but having LOCAL index on T1 (c1) or new table T2( c1), primary key
cannot be enabled.. so application was left to live without primary key and application designer said they enforce the uniqueness via another columns in the table
, so your comment please about this?

#Question6# I have just asked the above 5 questions but you can kindly add further your comments/suggestions

November 11, 2015 - 12:57 am UTC

q1) could be anything - an extent map block, or a segment header block. I wouldnt dwell heavily on the maths to that degree

q2) not to my knowledge, since there is no segment

q3) indexes (partitioned or otherwise) are all the same - choose so that the benefit of having it outweighs the cost of having it. In this case, global *might* give a benefit because only one probe, rather than one per partition. The cost is that now you've got a big structure to look after (eg if you are archiving old data etc etc). [Better in 12c]

q4) see q3, but also remember that no partition maintenance schema in place "today" is not always the same as no no partition maintenance planned forever.

q5) I dont know if the 'c1' column you are referring to is the partition key or otherwise. Assuming its not, then yes, unless you add that partition key in there, you dont get to use it as a PK. It's not uncommon for people to add the partition key to get a local index at the expense of "perturbing" the true natural key.

Partition..

A Reader, November 11, 2015 - 2:58 am UTC

Hi Connor,

regarding

..........q5) I dont know if the 'c1' column you are referring to is the partition key or otherwise. Assuming its not, then yes, unless you add that partition key in there, you dont get to use it as a PK. It's not uncommon for people to add the partition key to get a local index at the expense of "perturbing" the true natural key.

yes 'c1' column I was referring is not the partition key column ( sorry for not making it explit during the bit of masking which I did earlier...). yes adding partition key can be one option.

so in summary - I got the clear message from you that indexes (partitioned or otherwise) are all the same. I agree with that.
Partition maintenance is the key thing which makes these different otherwise (administrative and availability ). In our application at least in this schema/product version there will not be any need to 'drop' inactive data partition at least in the current schema/application version wherein all inactive/active data is sitting horizontally across all the partitions. No opportunity in future as well wherein we will be dropping the partition. And suppose, in case there is an option in future, we will REBUILD the GLOBAL index during an outage. So cost of having an outage will be very less compared to the amount of 'mad' scans which are being forced today with having LOCAL index on c1 column.

Next,

q6) About stats gathering on new 'type' GLOBAL index T2_I1 ? As mentioned earlier in this post. We will go with importing exiting stats from T1 table into T2( newly partitioned table) . this is all valid except for T2_I1 index now.. which will be GLOBAL instead of LOCAL. So we can't (and shouldn't try also) import existing stats here as the index structure is changed. So only the option left is
(a) gather stats on GLOBAL index T2_I1 and lock it implicitly by locking the stats on table T2 ( we will be locking stats of table T2 ). Having one index latest scan shouldn't make the optimizer to behave differently? but we will see how things goes post gathering stats on GLOBAL index alone.
(b) gather stats on GLOBAL index T2_I1 and leave it and let stats gather job gather the stats as per the schedule .. but as we will be locking the stats on T2 table so it gets cascaded to Index level as well. so looks this options doesn't exist for us.
your comments on stats gathering on GLOBAL index T2_I1 please ?

November 11, 2015 - 7:15 am UTC

When you create the index, stats will automatically be created. Then you may choose to use those figures as representative ones for either locking, or manually adjusting as time goes on (rather than having to re-gather from scratch)

Partition..

A Reader, November 11, 2015 - 8:02 am UTC

Thank you :-)

Ahh I forgot it ( in-fact I learned it recently that 10g onward index stats are gathered as it gets created). I just did a quick test to confirm if index stats gets gathered(captured) in scenario where we create LOCAL indexes partition by partition ( using setting UNUSABLE and rebuild) and it does .

thanks once again !

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Partition..

Partition..

Partition..

Partition..

Partition..

Partition..

Partition..

More to Explore

VLDB

VLDB

Quickest way to split partition a table

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Partition..

Partition..

Partition..

Partition..

Partition..

Partition..

Partition..

More to Explore

VLDB

VLDB