Home » SQL & PL/SQL » SQL & PL/SQL » Backup and Purge Data (11.2.0.3.0)
Backup and Purge Data [message #576555] Wed, 06 February 2013 04:17 Go to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Hi Experts,

I have 100 history tables in my database,When some condition is met, set of data from these tables need to be backed up(in form of .dmp or flat files) and purged.

Current Implementation is like there is a stage table which will have data by which we can uniquely identify these data, backup and purge. This is done serially, one table at a time

Q1- Instead of doing this serially if it is done in parallel, will this approach enhance the performance? Should i put trigger on these tables or should i make use of oracle parallel feature in a job scheduled to backup and purge, which one would be better?
Q2- Any idea how to optimize this whole process?

Thanks in advance


Re: Backup and Purge Data [message #576561 is a reply to message #576555] Wed, 06 February 2013 04:36 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Q1. It depends on your hardware and concurrent workload, so you have to make benchmarks yourself.
Q2. Partition the tables

Regards
Michel
Re: Backup and Purge Data [message #576631 is a reply to message #576561] Wed, 06 February 2013 14:14 Go to previous messageGo to next message
alan.kendall@nfl.com
Messages: 163
Registered: June 2012
Location: Culver City, California
Senior Member
In the following example, I truncate the partition that is almost a year old. Truncate is faster than delete.
ECSCDAS1S > @cr8_RANGE_PARTITION_on_12_MONTHS.sql

Table dropped.
Table created.
Index created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.

       AGE      MONTH
---------- ----------
        45          1
        44          2
         5          3
         4          4
         5          5
        11          6
        22          7
        33          8
        44          9
        47         10
        56         11
        66         12

12 rows selected.

alter table alan2 truncate partition Mar;

Table truncated.

       AGE      MONTH
---------- ----------
        45          1
        44          2
         4          4
         5          5
        11          6
        22          7
        33          8
        44          9
        47         10
        56         11
        66         12

11 rows selected.

The full sql follows.
set pages 50
drop table alan2;
create table alan2 (age number, month number default to_number(to_char(sysdate,'MM')))
PARTITION BY RANGE (month) 
( PARTITION Jan VALUES LESS THAN (2) ,  
  PARTITION Feb VALUES LESS THAN (3) ,  
  PARTITION Mar VALUES LESS THAN (4) ,  
  PARTITION Apr VALUES LESS THAN (5) ,  
  PARTITION may VALUES LESS THAN (6) ,  
  PARTITION jun VALUES LESS THAN (7) ,  
  PARTITION Jul VALUES LESS THAN (8) ,  
  PARTITION aug VALUES LESS THAN (9) ,  
  PARTITION sep VALUES LESS THAN (10) ,  
  PARTITION oct VALUES LESS THAN (11) ,  
  PARTITION nov VALUES LESS THAN (12) ,  
  PARTITION dec VALUES LESS THAN (13) )
;

CREATE INDEX alan2_age ON alan2(age) 
TABLESPACE users NOLOGGING
LOCAL (  
  PARTITION jan TABLESPACE users,
  PARTITION feb TABLESPACE users,
  PARTITION mar TABLESPACE users,
  PARTITION apr TABLESPACE users,
  PARTITION may TABLESPACE users,
  PARTITION jun TABLESPACE users,
  PARTITION jul TABLESPACE users,
  PARTITION aug TABLESPACE users,
  PARTITION sep TABLESPACE users,
  PARTITION oct TABLESPACE users,
  PARTITION nov TABLESPACE users,
  PARTITION dec TABLESPACE users
)
NOPARALLEL;

insert into alan2 (age,month) values (45,1);
insert into alan2 (age,month) values (44,2);
insert into alan2 (age,month) values (5,3);
insert into alan2 (age,month) values (4,4);
insert into alan2 (age,month) values (5,5);
insert into alan2 (age,month) values (11,6);
insert into alan2 (age,month) values (22,7);
insert into alan2 (age,month) values (33,8);
insert into alan2 (age,month) values (44,9);
insert into alan2 (age,month) values (47,10);
insert into alan2 (age,month) values (56,11);
insert into alan2 (age,month) values (66,12);
select * from alan2;
set pages 0
set feedback off
spool truncate.lst
select 'alter table alan2 truncate partition '||
substr(to_char(sysdate-330,'Month'),1,3)||';' from dual;
spool off
set pages 50
set feedback on
@truncate.lst
select * from alan2;
Re: Backup and Purge Data [message #576636 is a reply to message #576631] Wed, 06 February 2013 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And "exchange partition" is even faster when you have to "backup and delete".

Regards
Michel
Re: Backup and Purge Data [message #576644 is a reply to message #576636] Wed, 06 February 2013 22:56 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks Alan & Michel, appreciate your help

I understood partition is the best solution for such scenario but Quote:
Partitioning is only available with Oracle Enterprise Edition as a cost option (you need to buy licenses before you can use it).


What could be the second best solution?
Re: Backup and Purge Data [message #576645 is a reply to message #576644] Wed, 06 February 2013 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
if/when tables reside on same volume, then disk becomes BOTTLENECK for any parallel operation & disk is already slowest system component.
Re: Backup and Purge Data [message #576646 is a reply to message #576645] Wed, 06 February 2013 23:28 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Thanks BlackSwan!!

I didn't understood your below statement, can u please elaborate on this
Quote:
if/when tables reside on same volume


My intent was to hit (let's say)10 tables simultaneously (backup and purge) rather than doing it sequentially one by one, This could save time and enhance performance
Re: Backup and Purge Data [message #576647 is a reply to message #576646] Wed, 06 February 2013 23:38 Go to previous messageGo to next message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
>This could save time and enhance performance
only the cleanup tasks should be accessing these tables so what is benefit to "save time and enhance performance"?
It is zero sum game.
A fixed amount or resources will be consumed by the cleanup task.
Why does it matter if it completes in 5 minutes or 50 minutes?
Re: Backup and Purge Data [message #576649 is a reply to message #576647] Thu, 07 February 2013 00:04 Go to previous messageGo to next message
rishwinger
Messages: 132
Registered: November 2011
Senior Member
Dear BlackSwan,

Quote:
Why does it matter if it completes in 5 minutes or 50 minutes?


With these cleanup jobs there will other jobs running simultaneously, some low resource intensive and some high resource intensive, If cleanup job takes 50 mins and let's say (A fixed amount or resources will be consumed by the cleanup task.) is true and high resource intensive job is running simultaneously then my overall performance will reduce

You might say that similar scenario could happen with 5 mins time span but there is a lesser probability for that to happen.

Quote:
A fixed amount or resources will be consumed by the cleanup task.

Is is true for both parallel and serial operation? I think parallel will be more resource intensive and less time consuming

In normal stress load our CPU runs at 70-80% , that's why i am looking for such alternative which could reduce resource intake
Re: Backup and Purge Data [message #576651 is a reply to message #576649] Thu, 07 February 2013 00:17 Go to previous messageGo to next message
Michel Cadot
Messages: 58643
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As BlackSwan suggested, what is your disk(s) load during?
Will your disk(s) support parallelism or will them just queue each parallel process?
How much more time will the concurrent tasks support? (Don't expect to have the same time for them if you add a new and palletized high consuming task.)
...

Regards
Michel
Re: Backup and Purge Data [message #576710 is a reply to message #576651] Thu, 07 February 2013 07:38 Go to previous message
BlackSwan
Messages: 22558
Registered: January 2009
Senior Member
>In normal stress load our CPU runs at 70-80% , that's why i am looking for such alternative which could reduce resource intake
Then at most only one additional session should exist to consume the remaining CPU cycles & it would run at less than half speed.
Previous Topic: Trouble with reqexp_substr and connect by
Next Topic: Data Purging
Goto Forum:
  


Current Time: Fri Aug 01 19:45:25 CDT 2014

Total time taken to generate the page: 0.08919 seconds