| dbfile_multiblock_read_count [message #60534] |
Thu, 12 February 2004 22:37  |
Patrick Tahiri
Messages: 119
Registered: January 2004
|
Senior Member |
|
|
Hi,
I want to create some new tablespaces with extent management of type locally managed tablespace.
To choose an UNIFORM SIZE X, I have to take a value (for X) that will be a multiple of dbfile_multiblock_read_count!
When I'm checking which value I have on my server for this init. parameter, I can see that no value is set for it!? Can somebody help me to set a value for dbfile_multiblock_read_count? What is the rules to estimate/calculate a value for it?
Thank you very much in advance for your answers.
Regards,
Patrick Tahiri.
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60544 is a reply to message #60534] |
Fri, 13 February 2004 05:13  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Patrick,
did you have a chance to look at my follow up post on the Uniform extent size thread. I missed the block_size , sorry about that.
typically db_file_multiblock_read_count should be set such that size of the single I/O( db_file_multiblock_read_count*db_block_size) matches the I/O size of the OS( typically 64K ; 128K on some platforms).
For eg,if you block_size is say 8K, then you would set this multiblock_read_count to 8 , so that you do 64K reads.
On Raw devices & Direct I/O filesystems, you can go higher to reap the full benefits , but keep in mind that high values (like 32 or 64) could very well affect the optimizer's decision in choosing between Full table scan and index scan ,especially in the absence of the new optimizer_index settings(introduced in 9i)
Extent sizes ,however, can be way bigger than this , just a nice multiple of the I/O size(eg 256k, 1M , 8M etc)
-Thiru
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60550 is a reply to message #60544] |
Fri, 13 February 2004 08:59 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru,
IS there any method to find out what is the maximum IO permitted on unix(SUN and HP) and Linux and NT platforms. optimizer_index_caching(<100) which favours Nested loops and Optmizer_index_cost_adj(<100) which favours Index scans.Suppose If set db_file_multiblock_read_count=32 still I am seeing the good performance.that is still it is using Index scans. My next question is db_file_multiblock_read_count=32 help in fetching index blocks with less IO.
My third question is Which Parameters I have to look in statspack to find the diffrence for setting of these parameters(optimizer_index_caching and Optmizer_index_cost_adj). and also please advice me what more parameters I have set to use to drive oracle for better performance.Since I have only control from database not much from application.Most of the things driven from java/corba stuff.
1)optimizer_max_permutations=1000
2)optimizer_mode= first rows or all rows( advice me here)
3) pga_aggregate_target=200m
4) workarea_size_policy=auto.
What more parameters I have to set in the initalization paramerts.
and 4th Question is in Donlad Burleson articles says that setting log_buffer > 4M is of not much use. But I may predict the redolog space requests and redo allocation retries will increase if I am reducing redolog buffer u'r advice on this.
Finally in rollback segments we were assigning bigger rollback segment for any large jobs how the same thing is taken care in undo_tablespace. If I am setting too much undo_retention this will be applicable for all the undo segments which I feel waste of space.I am quite curious to know is anyway we can provide large chunk of space for paritcular undo segment.
I know I have loaded you with lot of questions I strongly feel that you have better exposure to all these problems and solutions for each one of these.
Thanks and Regards
Prasad
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60575 is a reply to message #60550] |
Tue, 17 February 2004 05:57 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Prasad,
answers inline:
IS there any method to find out what is the maximum IO permitted on unix(SUN and HP) and Linux and NT platforms.
On Solaris(sun4u architecture),it used to be 64K . It now defaults to 128K and can be defined in /etc/system ( maxphys parameter). If defined, you can do sysdef|grep maxphys
On HP , it used to be 256K, but it defaults to 1M now.
Not too sure on Linux and NT.
The information can be obtained from the OS manual.
optimizer_index_caching(<100) which favours Nested loops and Optmizer_index_cost_adj(<100) which favours Index scans.
See here --> Optimizer_Index
Suppose If set db_file_multiblock_read_count=32 still I am seeing the good performance.that is still it is using Index scans.
setting db_file_multiblock_read_count=32 is OK,as long as it doesnt cause inappropriate full table scans. Actually, in case of buffered filesystems, the OS will perform I/O in terms of its filesystem block size which is typically 8K. So not much benefit there. Even if you set DBFMRC to 32 on a 8K block size(DB), the OS will split the I/O accordingly.
This is the same case with Raw devices and Direct I/O,but the limits are much higher, limited by maxphys value and so the benefits of higher DBFMRC are pronounced.
For eg,lets say your db_block_size=4K and your DBFMRC=32 and so in a single Oracle I/O, its supposed to fetch 4k*32=128K and on Solaris, maxphys defaults to 128K and so we are fine. OS can complete this multiblock read request in a single physical I/O , if they are contigous.
But say your db_block_size=8k and your DBFMRC=32,then the logical I/O of 256K will be split by OS into two 128K reads.
Note also that RAID controller/LVM may impose its own limitations on the maximum physical I/O.
In general higher values of DBFMRC is good for Raw devices/Direct I/O/Vertias filesystems , but pretty much ineffective for buffered filesystems.
My next question is db_file_multiblock_read_count=32 help in fetching index blocks with less IO.
See previous answer. A high DBFMRC is good as long as it doesnt cause inadvertant full table scans becos of the lower cost ,the optimizer assigns for full table scans. It sure reduces the physical I/O and is especially a great help when it comes to DSS environments where you need to deal with frequent FTS opposed to OLTP databases where the size of the I/O is pretty small.
In 9i,you can control this behaviour better with optimizer_index statistics and hence we may get away with using higher DBFMRC than 8i .
My third question is Which Parameters I have to look in statspack to find the diffrence for setting of these parameters(optimizer_index_caching and Optmizer_index_cost_adj). and also please advice me what more parameters I have set to use to drive oracle for better performance.Since I have only control from database not much from application.Most of the things driven from java/corba stuff.
1)optimizer_max_permutations=1000
2)optimizer_mode= first rows or all rows( advice me here)
3) pga_aggregate_target=200m
4) workarea_size_policy=auto.
What more parameters I have to set in the initalization paramerts.
Sorry,its not possible to list all possible parameters that can influence performance ,as the list is pretty long and is dependent on the environment(for eg parallel query for DSS etc). You could post new threads for details about specific parameters.
and 4th Question is in Donlad Burleson articles says that setting log_buffer > 4M is of not much use. But I may predict the redolog space requests and redo allocation retries will increase if I am reducing redolog buffer u'r advice on this.
He is probably right. The LGWR flushes redolog buffers ,whenever any of the following happens :
- when a commit occurs
- when the log_buffer becomes 1/3rd full
- when the log_buffer becoes 1MB full
- when LGWR times out (3 secs)
- when DBWR triggers LGWR to flush the redo entries
Also , in most of the cases, the cause for 'redo log space requests' is usually slow log file switch completion and not really the log_buffer space at all.
A huge log_buffer may infact cause slowdowns ( by increasing log_file sync times) ,espcially during commits.
Finally in rollback segments we were assigning bigger rollback segment for any large jobs how the same thing is taken care in undo_tablespace. If I am setting too much undo_retention this will be applicable for all the undo segments which I feel waste of space.I am quite curious to know is anyway we can provide large chunk of space for paritcular undo segment.
You would size undo_retention and the undo tablespace to cater to your biggest transaction. Disk space is becoming cheaper :-) Also in 9i,there is something called 'extent stealing or dynamic extent transfer' where one undo segment could use an expired extent from a different segment ,if required.
-Thiru
[Updated on: Fri, 18 February 2005 23:32] Report message to a moderator |
|
|
|
| Re: dbfile_multiblock_read_count [message #60584 is a reply to message #60575] |
Tue, 17 February 2004 22:10 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
Thanks a lot for your reply
I have few more Lined up for today.
1) Already I have set Session_cached_cursos to 100 now If set hold_cursor=y So there is no need to open once again new cursor but Will it impact others. and how about release_cursor parameter setting.
2) I am looking at using Global Partioned Indexes which reduces I/O but with added maintance operations. Is it an good approach Instead of Local Paritioned iNdexes.
3) Disk_asynch_IO Mine is HP System. how to check whether asycn IO driver Installed.
lsdev -Casio => Not working what else to check or anything wrong with the usage. And also Since I am using db_writer_process=2, I think simulataneous enabling of both these parameters not required.
Which is the best option db_writer_process or dbwr_io_slaves. I feel Dbwr_io_slaves divides main job into sub systems and thus total execution time is increased.dbwr_writer__process is better.
4)My application is almost using Bind Variables, I am I go for Cursor_sharing to Similar what is the Impact.presently it is Exact
5) Since this application is Migrated from 8i to 9i. I wud like to go far Fast_start_mttr_target instead of both have set on my old system log_checkpoint_timeout(Its not needed since Idle time also checkpoint happening) and log_checkpoint_interval
6) Like to Explore lock_sga (To allocate to Entire Shared memory in one segment) and pre_page_sga parameters. Which one is better for Which platform.
7) How to Check whether Direct I/O is supported on HP Unix platforms. on SUN I am seeing forcedirectio in /etc/system.
8) CURSOR_SPACE_FOR_TIME=TURE will fragment any shared sql area or is it benefical since it not aged out of shared sql.
9) Finally Log_buffer I am still skeptical to change from 125M Since with this value itself I am seeing almost 10 for each for Redolog space requests and redolog allocation retries. if I reduce it redo are written at aggressive can I tackle it with multiple
lgwr.
I am seeing Contradicting statements two diffrent Oracle authors
Steve Adams has this Opinion
http://www.ixora.com.au/tips/tuning/log_buffer_size.htm
Thanks and Regards
Prasad
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60588 is a reply to message #60584] |
Wed, 18 February 2004 02:03 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Wow! lots of questions.. Prasad, could you split these questions into separate posts,so that its easier to answer ? Finding time to answer all at once is tough.
-Thiru
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60592 is a reply to message #60588] |
Wed, 18 February 2004 06:01 |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Thiru
First I am looking for answers to these questions.
1) Already I have set Session_cached_cursos to 100 now If set hold_cursor=y So there is no need to open once again new cursor but Will it impact others. and how about release_cursor parameter setting.
3) Disk_asynch_IO Mine is HP System. how to check whether asycn IO driver Installed.
lsdev -Casio => Not working what else to check or anything wrong with the usage. And also Since I am using db_writer_process=2, I think simulataneous enabling of both these parameters not required.
Which is the best option db_writer_process or dbwr_io_slaves. I feel Dbwr_io_slaves divides main job into sub systems and thus total execution time is increased.dbwr_writer__process is better.
5) Since this application is Migrated from 8i to 9i. I wud like to go far Fast_start_mttr_target instead of both have set on my old system log_checkpoint_timeout(Its not needed since Idle time also checkpoint happening) and log_checkpoint_interval
6) Like to Explore lock_sga (To allocate to Entire Shared memory in one segment) and pre_page_sga parameters. Which one is better for Which platform.
Thanks and Regards
Prasad
|
|
|
|
| Re: dbfile_multiblock_read_count [message #60603 is a reply to message #60592] |
Wed, 18 February 2004 14:45 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Ok Prasad, I'll take two questions now ..
5) Yes, you can disable both time based and interval based checkpoints and just use FAST_START_MTTR_TARGET to control incremental checkpoints. You'll still have the full checkpoints during the log switches.
6) Lock_sga is to lock the entire SGA in the physical memory. This is not available in Solaris,but can be achieved by using intimate shared memory(ism). Oracle 9i makes use of DISM ( dynamic intimate shared memory) on Solaris. HP supports locking SGA in memory and you should be fine.
Pre_page_sga basically loads the entire SGA into memory during instance startup and hence the instance startup is likely to be slower ,especially with large SGAs. But I have heard of connection slowdowns when this parameter is set to true . Also this does not prevent the SGA from paging out , which is ensured by LOCK_SGA.I dont use pre_page_sga at all.
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
