|
|
|
|
| Re: Disable RedoLog for a single user? [message #586545 is a reply to message #586483] |
Fri, 07 June 2013 05:53   |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
Quote:Smarter? It'd be dumber!
Personally I don't care, but the client thinks Oracle is dumb with redo logs management.
Quote:Explain your real problem and need, maybe we can help you to find a solution.
Production DB has ~30 users that hold tables dedicated to the transactional system.
Production DB has one user that holds tables dedicated to the data warehouse, which is refreshed every night.
Warehouse scripts are doing full refresh (drop all/insert all) and take about 5 hours.
The scripts, transforming the data from transactional schemas into the warehouse, are spamming redo logs with lots of trash data (40GB).
The client sees no point in having the redo archive grow that big every time.
The client says this has no advantages, only causes a slower database restoration time in case the redo log has to be put in action, also performance of warehouse scripts is slowed down.
The client wants to restore transactional data as fast as possible and warehouse data to be refreshed at night.
As far as I know disabling Redo Log for a single user is not possible within a single database, and it has been tried to move the warehouse into another DB, but the performance of data transformation scripts gets much slower then.
|
|
|
|
|
|
| Re: Disable RedoLog for a single user? [message #586568 is a reply to message #586545] |
Fri, 07 June 2013 08:41 |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
Can you not set the DW tables to NOLOGGING, and populate them with a direct load? That will avoid redo and undo generation.
Or if for some reason you cannot do a direct load into the DW tables, then do your intermediate processing on global temporary tables (which will generate undo (in the current release) but not redo) and when the data is prepared, direct load from them into the DW tables?
|
|
|
|
| Re: Disable RedoLog for a single user? [message #586575 is a reply to message #586545] |
Fri, 07 June 2013 09:32 |
 |
LKBrwn_DBA
Messages: 487
Registered: July 2003
Location: WPB, FL
|
Senior Member |
|
|
Buchas wrote on Fri, 07 June 2013 06:53. . . E t c . . . to the data warehouse, which is refreshed every night.
Warehouse scripts are doing full refresh (drop all/insert all) and take about 5 hours.
. . .
This means the DW refresh is designed extremely inefficient.
Listen to Littlefoot and Watson and re-design the process.
And perhaps the DW itself needs to be re-designed?
[Updated on: Fri, 07 June 2013 10:28] by Moderator Report message to a moderator |
|
|
|
| Re: Disable RedoLog for a single user? [message #586581 is a reply to message #586545] |
Fri, 07 June 2013 10:31 |
 |
Michel Cadot
Messages: 68732
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
... In addition, to all the above, why transactional and DWH are in the same database?
DWH should be in NOARCHIVEDLOG mode (as there should be no transaction), all tables in NOLOGGING mode and... (see John's post).
Your architecture is wrong.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
