Huge Table Insert - take long time?

[Woody McKay]

Hi gurus, I'm fishing for creative thoughts here...

12.1.0.2 RAC on Exadata with forced logging for standby

There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)

1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main

The insert in Step 5 took 9.5 hours... Total refactor process time was
about 13 hours.

Is there anything you can think of to make this process any faster?

I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING.
Guessing alter table nologging wouldn't do much either. Can't exchange from
old to new since new mostly likely had columns added.

I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.

I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.

Any Thoughts or ideas to try?

Thanks, as always - Woody

[Brent Day]

Have you looked at DBMS_REDEFINITION? Your could possibly reorg while the
app remains up.

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was
about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Woody McKay]

Thanks Brent - I'll have to check that out...

Post by Brent Day
Have you looked at DBMS_REDEFINITION? Your could possibly reorg while the
app remains up.

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was
about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Karthikeyan Panchanathan]

What is the partition type involved here?

One option you can load data by partition into intermediary table(with new columns) and exchange. This way you can run parallel sessions for each partition and before exchange new table you can build at partition level.

You mentioned other db, have you thought about moving data from other db to prod using data-pump?

Karth

Sent from my IPhone

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING. Guessing alter table nologging wouldn't do much either. Can't exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but the production DBA's are worried about chained-rows, fragmentation, possible explain plan changes, etc. I'm not sure if they are justified in their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have nologging) and use transportable tablespace, but don't think that would be any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Woody McKay]

That's an interesting thought Karth. Most are partitioned by range (date)
and interval (daily or weekly) subpartitions.

Gonna chew on this one - thanks


On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan <

Post by Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary table(with new
columns) and exchange. This way you can run parallel sessions for each
partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db
to prod using data-pump?
Karth
Sent from my IPhone
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Woody McKay]

Ya got me thinking about the "load data by partition" idea...

I wonder if instead of insert dop 16 from select dop 16, what if I have say
50 nonpartitioned tables (one for every partition) and dop4 insert into the
from *_old partition () all at the same time. Then do a partition exchange
from the 50 nonpartitioned tables to the main fully partitioned table? The
exchange is quick since it is just a data dictionary change.

This may be faster because the amount of DOP is higher. Maybe less exchange
waits? I'm assuming that the DOP 16 can't work on all 50 partitions all
together as efficiently as loading all 50 nonpartitioned tables?

Got me thinking...




On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan <

Post by Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary table(with new
columns) and exchange. This way you can run parallel sessions for each
partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db
to prod using data-pump?
Karth
Sent from my IPhone
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Mark W. Farnham]

You are probably correct. An insert dop of 1 might even be best so there is a single insert point for each table and thus no contention for above the high water mark blocks (which is presumably the beginning since you’re using a new table for each insert target.



Unlike online move, in this model your original data source still exists, so you may persuade your administrators to give you new time period based tablespaces that are initially NOT force logged and back them and ship them well before the source goes away. Remember, since exchange doesn’t move the data, these new tablespaces are where the data will remain. If you’re appending to only one table per tablespace at a time, then dbwr is going to have contiguous blocks or compression units to write. I don’t know how much that might speed things up on an Exadata. On older disk tech it can be a lot.



If there is a known best order for both compression and query block clustering (or even one or the other), this is the time to do it. With the way HCC works this can save a lot of space if commonly duplicated column values gain a high fan-in per compression unit. With zone maps this can dramatically reduce the compression units needed to be unpacked and delivered at query time. You get that forever for any partitions that have become quiescent with age.



Good luck!



mwf



From: oracle-l-***@freelists.org [mailto:oracle-l-***@freelists.org] On Behalf Of Woody McKay
Sent: Thursday, July 26, 2018 7:58 PM
To: ***@gmail.com
Cc: ORACLE-L
Subject: Re: Huge Table Insert - take long time?



Ya got me thinking about the "load data by partition" idea...



I wonder if instead of insert dop 16 from select dop 16, what if I have say 50 nonpartitioned tables (one for every partition) and dop4 insert into the from *_old partition () all at the same time. Then do a partition exchange from the 50 nonpartitioned tables to the main fully partitioned table? The exchange is quick since it is just a data dictionary change.



This may be faster because the amount of DOP is higher. Maybe less exchange waits? I'm assuming that the DOP 16 can't work on all 50 partitions all together as efficiently as loading all 50 nonpartitioned tables?



Got me thinking...









On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan <***@gmail.com> wrote:

What is the partition type involved here?



One option you can load data by partition into intermediary table(with new columns) and exchange. This way you can run parallel sessions for each partition and before exchange new table you can build at partition level.



You mentioned other db, have you thought about moving data from other db to prod using data-pump?



Karth

Sent from my IPhone


On Jul 23, 2018, at 7:52 PM, Woody McKay <***@gmail.com> wrote:

Hi gurus, I'm fishing for creative thoughts here...



12.1.0.2 RAC on Exadata with forced logging for standby

There's a refactoring process: (subpartitioned tables with millions to a few billions of rows)

1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main

The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.

Is there anything you can think of to make this process any faster?

I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING. Guessing alter table nologging wouldn't do much either. Can't exchange from old to new since new mostly likely had columns added.

I've mentioned using alter table if we are only adding a few columns, but the production DBA's are worried about chained-rows, fragmentation, possible explain plan changes, etc. I'm not sure if they are justified in their thoughts.



I've thought of doing the refactor in a diff DB (where I can truly have nologging) and use transportable tablespace, but don't think that would be any better.

Any Thoughts or ideas to try?



Thanks, as always - Woody

[Woody McKay]

Thanks you Mark! I appreciate your thoughts and insight. Going to
incorporate this as much as possible to see what it buys me.

Post by Mark W. Farnham
You are probably correct. An insert dop of 1 might even be best so there
is a single insert point for each table and thus no contention for above
the high water mark blocks (which is presumably the beginning since you’re
using a new table for each insert target.
Unlike online move, in this model your original data source still exists,
so you may persuade your administrators to give you new time period based
tablespaces that are initially NOT force logged and back them and ship them
well before the source goes away. Remember, since exchange doesn’t move the
data, these new tablespaces are where the data will remain. If you’re
appending to only one table per tablespace at a time, then dbwr is going to
have contiguous blocks or compression units to write. I don’t know how much
that might speed things up on an Exadata. On older disk tech it can be a
lot.
If there is a known best order for both compression and query block
clustering (or even one or the other), this is the time to do it. With the
way HCC works this can save a lot of space if commonly duplicated column
values gain a high fan-in per compression unit. With zone maps this can
dramatically reduce the compression units needed to be unpacked and
delivered at query time. You get that forever for any partitions that have
become quiescent with age.
Good luck!
mwf
*Sent:* Thursday, July 26, 2018 7:58 PM
*Cc:* ORACLE-L
*Subject:* Re: Huge Table Insert - take long time?
Ya got me thinking about the "load data by partition" idea...
I wonder if instead of insert dop 16 from select dop 16, what if I have
say 50 nonpartitioned tables (one for every partition) and dop4 insert into
the from *_old partition () all at the same time. Then do a partition
exchange from the 50 nonpartitioned tables to the main fully partitioned
table? The exchange is quick since it is just a data dictionary change.
This may be faster because the amount of DOP is higher. Maybe less
exchange waits? I'm assuming that the DOP 16 can't work on all 50
partitions all together as efficiently as loading all 50 nonpartitioned
tables?
Got me thinking...
On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan <
What is the partition type involved here?
One option you can load data by partition into intermediary table(with new
columns) and exchange. This way you can run parallel sessions for each
partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db
to prod using data-pump?
Karth
Sent from my IPhone
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody
--
Sincerely,
WoodyMcKay

[Insights]

Stop guessing and get the awr, ash, and sql explain plan at the very least.

Sent from my iPhone

Post by Woody McKay
Ya got me thinking about the "load data by partition" idea...
I wonder if instead of insert dop 16 from select dop 16, what if I have say 50 nonpartitioned tables (one for every partition) and dop4 insert into the from *_old partition () all at the same time. Then do a partition exchange from the 50 nonpartitioned tables to the main fully partitioned table? The exchange is quick since it is just a data dictionary change.
This may be faster because the amount of DOP is higher. Maybe less exchange waits? I'm assuming that the DOP 16 can't work on all 50 partitions all together as efficiently as loading all 50 nonpartitioned tables?
Got me thinking...

Post by Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary table(with new columns) and exchange. This way you can run parallel sessions for each partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db to prod using data-pump?
Karth
Sent from my IPhone

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING. Guessing alter table nologging wouldn't do much either. Can't exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but the production DBA's are worried about chained-rows, fragmentation, possible explain plan changes, etc. I'm not sure if they are justified in their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have nologging) and use transportable tablespace, but don't think that would be any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

--
Sincerely,
WoodyMcKay

[Mladen Gogala]

Why? Guessing is fun. It's like fantasy football, only with other
people's databases.

Post by Insights
Stop guessing and get the awr, ash, and sql explain plan at the very least.
Sent from my iPhone

Post by Woody McKay
Ya got me thinking about the "load data by partition" idea...
I wonder if instead of insert dop 16 from select dop 16, what if I
have say 50 nonpartitioned tables (one for every partition) and dop4
insert into the from *_old partition () all at the same time. Then do
a partition exchange from the 50 nonpartitioned tables to the main
fully partitioned table?  The exchange is quick since it is just a
data dictionary change.
This may be faster because the amount of DOP is higher. Maybe less
exchange waits?  I'm assuming that the DOP 16 can't work on all 50
partitions all together as efficiently as loading all 50
nonpartitionedtables?
Got me thinking...
On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary
table(with new columns) and exchange. This way you can run
parallel sessions for each partition and before exchange new
table you can build at partition level.
You mentioned other db, have you thought about moving data from
other db to prod using data-pump?
Karth
Sent from my IPhone

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with
millions to a few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16
(note: most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process
time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has
FORCE LOGGING. Guessing alter table nologging wouldn't do much
either. Can't exchange from old to new since new mostly likely
had columns added.
I've mentioned using alter table if we are only adding a few
columns, but the production DBA's are worried about
chained-rows, fragmentation, possible explain plan changes, etc.
I'm not sure if they are justified in their thoughts.
I've thought of doing the refactor in a diff DB (where I can
truly have nologging) and use transportable tablespace, but
don't think that would be any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

--
Sincerely,
WoodyMcKay

[Woody McKay]

Lol... Guessing? I don't know if lower level details like exchange waits
show up in explain plans, ash or awr (maybe awr and trace). I saw the
exchange waits in OEM as I drilled into the session parallelism page.

The guessing comes from trying to prioritizing methods of approach and an
close approaching August release date. For August, I'm taking this 12
hours insert from old to hist table out of the foreground timed release and
put it in the background clean-up task (un-timed). That way I can release
the main tables back to live operational status quicker.

I will setup some tests in the performance lab for these approaches and see
how they benchmark. I'd like to find the best solution because I'm not sure
yet if this DB will never be purging data and inefficient and un-scalable
solutions will only get worse as the DB grows.

I do greatly appreciate all the great thoughts and creative approaches
given.

Thanks!

Why? Guessing is fun. It's like fantasy football, only with other people's
databases.
Stop guessing and get the awr, ash, and sql explain plan at the very least.
Sent from my iPhone
Ya got me thinking about the "load data by partition" idea...
I wonder if instead of insert dop 16 from select dop 16, what if I have
say 50 nonpartitioned tables (one for every partition) and dop4 insert into
the from *_old partition () all at the same time. Then do a partition
exchange from the 50 nonpartitioned tables to the main fully partitioned
table? The exchange is quick since it is just a data dictionary change.
This may be faster because the amount of DOP is higher. Maybe less
exchange waits? I'm assuming that the DOP 16 can't work on all 50
partitions all together as efficiently as loading all 50 nonpartitioned
tables?
Got me thinking...
On Mon, Jul 23, 2018 at 8:15 PM Karthikeyan Panchanathan <

Post by Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary table(with
new columns) and exchange. This way you can run parallel sessions for each
partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db
to prod using data-pump?
Karth
Sent from my IPhone
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

--
Sincerely,
WoodyMcKay
--
Mladen Gogala
Database Consultant
Tel: [phone]

[Karthikeyan Panchanathan]

Since you are moving process to background as clean-up task, you can approach this way also.

Deploy now to Production only New table(tablename_new), Exchanging table and code to copy data/swap partition.

You run daily job to copy/swap your stale(history) data partitions and exchange them with new table.

This way on Release date you need to copy/swap only latest (dml activity present) partitions.

Validate record count between New and existing table. On success drop old table( or rename as _OLD)

Finally rename tablename_new to tablename.

Thanks
Karth

Sent from my IPhone

Lol... Guessing? I don't know if lower level details like exchange waits show up in explain plans, ash or awr (maybe awr and trace). I saw the exchange waits in OEM as I drilled into the session parallelism page.
The guessing comes from trying to prioritizing methods of approach and an close approaching August release date. For August, I'm taking this 12 hours insert from old to hist table out of the foreground timed release and put it in the background clean-up task (un-timed). That way I can release the main tables back to live operational status quicker.
I will setup some tests in the performance lab for these approaches and see how they benchmark. I'd like to find the best solution because I'm not sure yet if this DB will never be purging data and inefficient and un-scalable solutions will only get worse as the DB grows.
I do greatly appreciate all the great thoughts and creative approaches given.
Thanks!

Why? Guessing is fun. It's like fantasy football, only with other people's databases.

Post by Insights
Stop guessing and get the awr, ash, and sql explain plan at the very least.
Sent from my iPhone

Post by Woody McKay
Ya got me thinking about the "load data by partition" idea...
I wonder if instead of insert dop 16 from select dop 16, what if I have say 50 nonpartitioned tables (one for every partition) and dop4 insert into the from *_old partition () all at the same time. Then do a partition exchange from the 50 nonpartitioned tables to the main fully partitioned table? The exchange is quick since it is just a data dictionary change.
This may be faster because the amount of DOP is higher. Maybe less exchange waits? I'm assuming that the DOP 16 can't work on all 50 partitions all together as efficiently as loading all 50 nonpartitioned tables?
Got me thinking...

Post by Karthikeyan Panchanathan
What is the partition type involved here?
One option you can load data by partition into intermediary table(with new columns) and exchange. This way you can run parallel sessions for each partition and before exchange new table you can build at partition level.
You mentioned other db, have you thought about moving data from other db to prod using data-pump?
Karth
Sent from my IPhone

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE LOGGING. Guessing alter table nologging wouldn't do much either. Can't exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but the production DBA's are worried about chained-rows, fragmentation, possible explain plan changes, etc. I'm not sure if they are justified in their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have nologging) and use transportable tablespace, but don't think that would be any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

--
Sincerely,
WoodyMcKay

--
Mladen Gogala
Database Consultant
Tel: [phone]

--
Sincerely,
WoodyMcKay

[GG]

Hi,

    due to the datacenter relocation we have to set new IPs for all
Exadata networks including new VLANs, new IP for ILOM and so on.
As far as I can tell it is possible to keep the data intact as well as
'internal' 192.168/24 addressing.
Is there any document which can help organize this activity (OEDA ? )?
Regards
Grzegorz



--
http://www.freelists.org/webpage/oracle-l

[Rajesh Aialavajjala]

Grzegorz,
Please take a look at MOS Document - Changing IP addresses on Exadata
Database Machine (Doc ID 1317159.1) - this discusses changing IP addresses
for Exadata...

Thanks,


--Rajesh

Post by GG
Hi,
due to the datacenter relocation we have to set new IPs for all
Exadata networks including new VLANs, new IP for ILOM and so on.
As far as I can tell it is possible to keep the data intact as well as
'internal' 192.168/24 addressing.
Is there any document which can help organize this activity (OEDA ? )?
Regards
Grzegorz
--
http://www.freelists.org/webpage/oracle-l

[Hemant K Chitale]

You mention "additional columns".

Are you using transforms, functions, joins to generate the values for the
new columns ?
Maybe these operations are taking time ?

APPEND avoids undo generation, so it does reduce redo generation even if
you have FORCE LOGGING.
NOLOGGING doesn't help in the presence of FORCE LOGGING.

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
5. insert into new_main from main_old using APPEND PARALLEL 16 (note: most
tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was
about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Woody McKay]

Thanks Hemant. All new columns are NOT NULL and get data with updated app
code. Older recs may not have data for any new columns.

Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

Post by Hemant K Chitale
You mention "additional columns".
Are you using transforms, functions, joins to generate the values for the
new columns ?
Maybe these operations are taking time ?
APPEND avoids undo generation, so it does reduce redo generation even if
you have FORCE LOGGING.
NOLOGGING doesn't help in the presence of FORCE LOGGING.

Post by Woody McKay
Hi gurus, I'm fishing for creative thoughts here...
12.1.0.2 RAC on Exadata with forced logging for standby
There's a refactoring process: (subpartitioned tables with millions to a
few billions of rows)
1. rename the main table to main_old
2. create the new_main table (no indexes or constraints at this point)
3. set table prefs for concurrent incremental stats
4. enable parallel dml
most tables are compressed)
6. create a PK or an index (as needed) on new_main
7. gather table stats on new_main
The insert in Step 5 took 9.5 hours... Total refactor process time was
about 13 hours.
Is there anything you can think of to make this process any faster?
I assume the APPEND hint isn't doing much because the DB has FORCE
LOGGING. Guessing alter table nologging wouldn't do much either. Can't
exchange from old to new since new mostly likely had columns added.
I've mentioned using alter table if we are only adding a few columns, but
the production DBA's are worried about chained-rows, fragmentation,
possible explain plan changes, etc. I'm not sure if they are justified in
their thoughts.
I've thought of doing the refactor in a diff DB (where I can truly have
nologging) and use transportable tablespace, but don't think that would be
any better.
Any Thoughts or ideas to try?
Thanks, as always - Woody

[Mladen Gogala]

Hi Woody,

Yes it does avoid UNDO, because it extends the table above the
high-water mark and inserts the data into empty blocks. Empty block do
not contain any information that would need to be saved in the UNDO
segments. That is the principal benefit of doing /*+ APPEND */. You may
want to try using DBMS_PARALLEL_EXECUTE instead of the /*+ PARALLEL */
hint. My experiences with DBMS_PARALLEL_EXECUTE are quite good.

Regards

Post by Woody McKay
Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

[Mark J. Bobak]

Also, just a thought:

For the duration of this load, disable FORCE LOGGING, then re-enable it
when load completes, and catch up the standby with an RMAN Incremental
backup?

-Mark

Post by Mladen Gogala
Hi Woody,
Yes it does avoid UNDO, because it extends the table above the high-water
mark and inserts the data into empty blocks. Empty block do not contain any
information that would need to be saved in the UNDO segments. That is the
principal benefit of doing /*+ APPEND */. You may want to try using
DBMS_PARALLEL_EXECUTE instead of the /*+ PARALLEL */ hint. My experiences
with DBMS_PARALLEL_EXECUTE are quite good.
Regards

Post by Woody McKay
Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

--
Mladen Gogala
Database Consultant
Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l

[Woody McKay]

Thanks Mark! But, I don't think our exadata DBA team will go for that in
production. Can you say very decentralized?

Thanks though!

Post by Mark J. Bobak
For the duration of this load, disable FORCE LOGGING, then re-enable it
when load completes, and catch up the standby with an RMAN Incremental
backup?
-Mark

Post by Mladen Gogala
Hi Woody,
Yes it does avoid UNDO, because it extends the table above the high-water
mark and inserts the data into empty blocks. Empty block do not contain any
information that would need to be saved in the UNDO segments. That is the
principal benefit of doing /*+ APPEND */. You may want to try using
DBMS_PARALLEL_EXECUTE instead of the /*+ PARALLEL */ hint. My experiences
with DBMS_PARALLEL_EXECUTE are quite good.
Regards

Post by Woody McKay
Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

--
Mladen Gogala
Database Consultant
Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l

[Woody McKay]

Thanks Mladen! I'll for sure check out DBMS_PARALLEL_EXECUTE!
Thanks again :)

Post by Mladen Gogala
Hi Woody,
Yes it does avoid UNDO, because it extends the table above the
high-water mark and inserts the data into empty blocks. Empty block do
not contain any information that would need to be saved in the UNDO
segments. That is the principal benefit of doing /*+ APPEND */. You may
want to try using DBMS_PARALLEL_EXECUTE instead of the /*+ PARALLEL */
hint. My experiences with DBMS_PARALLEL_EXECUTE are quite good.
Regards

Post by Woody McKay
Didn't realize that about APPEND avoiding undo even if FORCE LOGGING. Thanks

--
Mladen Gogala
Database Consultant
Tel: [phone]
--
http://www.freelists.org/webpage/oracle-l