| settings of oracle database [message #62287] |
Mon, 12 July 2004 20:21  |
IKRAM
Messages: 5
Registered: August 2003
|
Junior Member |
|
|
i have questions about the physical structure of Oracle database
for an OLTP and Dataware house system
1 - what will be the size of tablespace?
2 - What will be the size of datafile?
3 - What will be size of redolog files?
4 - number of data files?
5 - number of redolog files?
6 - number of controll files?
7 - different parameters settings for OLTP and dataware house like
db_block_size
db_cache_size
db_block_size
redo_log_buffer
open_cursors
large_pool_size
java_pool_size
shared_pool_size
fast_start_mttr_target
undo_management
undo_tablespace
etc
|
|
|
|
| Re: settings of oracle database [message #62290 is a reply to message #62287] |
Mon, 12 July 2004 21:18  |
Daljit Singh
Messages: 290
Registered: October 2003
Location: Texas
|
Senior Member |
|
|
Hi,
Well the answers of your first 6 questions i.e.
1 - what will be the size of tablespace?
2 - What will be the size of datafile?
3 - What will be size of redolog files?
4 - number of data files?
5 - number of redolog files?
6 - number of controll files?
doesn't depend on weather you are using OLTP or DWH. For some extent we can say that we should pay some attention while creating the redo files for OLTP/DWH that matters. But the rest of the questions totally depends on the size of data and availabe resources. The size of a tablespace cannot be fixed it will keep growing as your business grows. Now to increase the size of tablespace so that more data can be placed there are two options increase the size of existing file or add another file. So it's also very hard to say how much datafiles would be there. You can have a tablespace of 10 GB with only one datafile (if ur OS support) or u can have 10 different datafiles of 1 GB each.
Number of controlfiles is basically just for safeguard, in which more than 1 controlfile is created on separate disk so that in case of disk failure we can have a copy of contorlfile from another disk. No other intention is there.
Number of redo logs is totally different in case of members and groups. If u take members than the story would be same as controlfiles but in case of groups you define number of redo which should be written before overwriting an existing one. It also effect on checkpoints and archivals. And this setting depends upon the amount of transactions.
Finally your last question strongly matters in case of OLTP/DWH. Setting these parameters needs great understanding of oracle and type of applications you are going to introduce. Like block size in case of DWH is greater than OLTP, in DWH large pool is also configured for large memory allocations but in OLTP it depends upon the requirement, in DWS updation happens only in batch jobs whereas in OLTP it happens frequently so undo management should be strong enough to handle that etc.
According to me this is an endless topic and if one has proper understading of oracle as well as type of application than he or she can easily configure the environment for that.
Best Regards
Daljit Singh
|
|
|
|
| Re: settings of oracle database [message #62351 is a reply to message #62290] |
Fri, 16 July 2004 09:29 |
Harish
Messages: 25
Registered: February 2001
|
Junior Member |
|
|
daljit has answered wonderfully.....
:)
But if u look at the init.ora file
u can configure some of the paramerters.
like
db_block_size=value_in_bytes
for oltp it is 2k,4k,8k
for dwh it is 16k,32k
set star_transformation_enabled=true
(availble from 9i onwards for star based)
optimization_mode=choose/first_rows_100
buffer memory is 4*size_of_block
etc.....
these are some of the parameters;
if dwh sometimes has a relational design,then
above parameter setting may vary to address the problem.
for details one has to contact dba for further details and guidence...depending on the business req.
thats all.......
Cheers,
harish
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
