This discussion is archived
1 2 3 4 Previous Next 54 Replies Latest reply: Aug 18, 2010 3:46 PM by 767685 Go to original post RSS
  • 15. Re: Finding an optimal db_block_size
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    John-M wrote:
    Even in my big table (800MB) tables, I delete each n every thing, shutdown/restart the db and restart my computer that there should be no doubt in my mind that data is coming from cache/ram etc during my second block size run.
    That is a great way of understanding the simplest possible case.

    As long as you can describe how this case models real life and real workload and in which situations in your application that will be important.
  • 16. Re: Finding an optimal db_block_size
    jgarry Guru
    Currently Being Moderated
    John-M wrote:
    Well, I do not believe on running test or whatever only once and concluding the rest out of that.

    800MB table size - big in a sense that it is bigger than my RAM allocated at the db startup. I had reduced my RAM size allocated for my db to a minimum level it accepts.
    Even that can make a test useless. For example, Oracle treats what it considers "small tables" differently than normal sized tables for purposes of aging out cache. So it is entirely possible to have a functioning system with some tables considered not small, enlarge the SGA so those tables are suddenly considered small, and wind up with completely different performance characteristics simply by enlarging the SGA. This is the kind of thing completely masked by artificially small buffer caching (and conversely, can lead to unexpected CPU or I/O usage on a large cache, partly dependent on blocksize).

    There are reasons for testing with small cache, but I don't think finding an optimal db block size is one of them. Normally it would be for more specific testing.
  • 17. Re: Finding an optimal db_block_size
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    John-M wrote:

    My question regarding Elapsed time is not answered :
    Thirdly, what things to consider for proper testing like ELAPSED TIME (that the query is faster in particular db_block_size) or anything else?
    Your best option is to run with extended sql tracing enabled (use dbms_monitor to enable tracing with waitstate tracing enabled). Using tkprof to summarise the trace files is a good starting point, if there is no significant difference in elapsed time between tests of the same statement on different block sizes then you need do little more than check the type, number, and time of each wait type was consistent across block sizes. If you find a significant difference then you can check that the execution plan was the same for both tests then, if they were, extract the waits from the trace file to analyse in detail where the difference appeared.

    As far as creating one or many database is concerned, you probably need to create one set of data files that stays unchanged for the duration of all the tests so that your results are not affected by (e.g.) changes in hardware stripe bounaries or different positioning of files on physical drives. Depending on your operating system, this may not be sufficient, of course - for example, if you are on a WAFL system then block writes go to the free space rather than being written back to the same place, so the act of writing to a file means you have moved parts of the file in a way you cannot predict. You may also have to worry about how the configuration of your O/S affects the I/O - I believe that Windows (for example) uses a 4KB allocation unit, so 4KB is the "natural" blocksize and there may be some benefit relating to using that block size on windows for reasons completely outside the scope of how Oracle basically behaves: but since you can change file-system block sizes when you create file systems you may need to consider reformatting your drives for each test.

    For db_file_multiblock_read_count: don't set it, but make sure your cache sizes are large enough compared to you processes count to make Oracle choose the same (maximum, 1MB) muiltiblock read in all cases - and make sure that when you are doing multiblock testing that the extent sizes makes this useful.

    Bear in mind that 11g can do "adaptive direct path serial reads" - it's one of the things you should check in the trace files - if two tests of the same statement show significantly different times has one of them used direct path reads.

    Just a few thoughts as I wait for my plane to be called.

    Regards
    Jonathan Lewis
  • 18. Re: Finding an optimal db_block_size
    767685 Newbie
    Currently Being Moderated
    Hi,

    very much encouraging for me to go on with my work. That's kind of answer I was expecting from experts.

    I'll proceed with my work and as soon as I'll do some reasonable testing, I'll put my results/analysis her for further assistance.

    Bundle of thanks.

    Regards,
  • 19. Re: Finding an optimal db_block_size
    jgarry Guru
    Currently Being Moderated
    Jonathan Lewis wrote:
    John-M wrote:

    My question regarding Elapsed time is not answered :
    Thirdly, what things to consider for proper testing like ELAPSED TIME (that the query is faster in particular db_block_size) or anything else?
    Your best option is to run with extended sql tracing enabled (use dbms_monitor to enable tracing with waitstate tracing enabled). Using tkprof to summarise the trace files is a good starting point, if there is no significant difference in elapsed time between tests of the same statement on different block sizes then you need do little more than check the type, number, and time of each wait type was consistent across block sizes. If you find a significant difference then you can check that the execution plan was the same for both tests then, if they were, extract the waits from the trace file to analyse in detail where the difference appeared.
    As Hans pointed out, this is a simplest possible case as John described his testing. I'm having trouble making the jump from looking at wait states in a simple case to the waits in a realistic concurrent case, which might (ok, shouldn't that be will) be quite different? And I mean different to the point of making the test useless or misleading.

    Remember, we're talking about a basic configuration that will determine performance for the life of the database. If the test doesn't reflect the db use, that's a fairly major fail. Didn't someone famous say not to tune too early? Don't we all know that we need to tune the bottlenecks that actually impact performance? Aren't there simple examples of counterintuitive performance increase by shifting load to slower devices? Aren't the metrics we have incomplete anyways?

    What you are saying makes perfect sense when comparing two similar situations with few variables. What I see here is oversimplification of many variables. And the biggest oversimplification of all - Dan saying just use 8K blocks except for specific situations - may be the right answer for a production system, since it is so expensive to not oversimplify. It's a lot of work to trace a lot of statements in detail.

    All that said, I still would encourage John-M to actually do the testing, please publish details and replicable scripts so we can tear it apart and all learn in the process. The method Jonathan suggested may in itself come up with interesting nuggets of information, as it has so many times in the past. Then we can argue endlessly whether they apply to setting a db blocksize :-)
  • 20. Re: Finding an optimal db_block_size
    jgarry Guru
    Currently Being Moderated
    John-M wrote:
    Hi,


    First of all is it ok to create 4k and 16k tablespaces in my 8k block size 11g (11.1.0.6.0) db or I've to create separate 4k and 16k databases. Does it matter or no difference whatever way is chosen?
    One minor practical difference, nonstandard blocksize tablespaces can't have multiple buffer pools. Whether multiple buffer pools are useful is another whole argument, partly dependent on configuration artistry, partly dependent on the spread of data concurrently accessed versus size of SGA. I've found them useful in the past, 11g mileage on modern systems may vary. Just as an armchair speculation, I would expect a difference by blocksize in a heavily loaded, highly random accessed type of system where the desired data is scattered about many blocks.
  • 21. Re: Finding an optimal db_block_size
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jgarry wrote:

    As Hans pointed out, this is a simplest possible case as John described his testing. I'm having trouble making the jump from looking at wait states in a simple case to the waits in a realistic concurrent case, which might (ok, shouldn't that be will) be quite different? And I mean different to the point of making the test useless or misleading.
    Joel,

    I missed the bit where John-M said he was going to ignore Hans suggestions for testing and only do simple tests ;)

    It doesn't matter whether "a test" is a single user doing a single tablescan, or 50 concurrent sessions running a 3GL loop to insert, update and delete patterns of rows on multiple tables with "refresh on commit" materialized views in place - if you want to decide that a different block size is the sole (or at least most significant) cause for a difference to performance you have to measure where the time went. Obviously it's a lot harder to analyse the detail of 50 tkprof files - let alone synchronising and analysing the underlying trace files - but the principle doesn't change.

    One thought about the concurrency tests, of course, is that each test case needs to be repeated at least three times to see how the variation in tests at the same block size compares to the variation between block sizes; and for tests that involve DML, the duration of each test case should be long enough to ensure that Oracle cycles through all the redo log files at least once.

    Frankly I think John-M is doomed to waste a lot of time if he follows his current approach. I think the point has already been made in this thread that the cost-effective approach is to follow the standard logical design method saying:

    <blockquote>
    Here's a critical task that my system has to handle as efficiently as possible
    Here's the best possible feature set to use from Oracle
    Here's a good model of the requirement - including structures, operations, and sufficiently realistic data
    Does the model behave better with different block sizes.
    </blockquote>



    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"Science is more than a body of knowledge; it is a way of thinking"+
    +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 22. Re: Finding an optimal db_block_size
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jgarry wrote:

    Just as an armchair speculation, I would expect a difference by blocksize in a heavily loaded, highly random accessed type of system where the desired data is scattered about many blocks.
    I think it was Carl Sagan who once said: "I don't think with my guts." In the same vein, I don't think you should speculate with your armchair. But if you do, I'm sure it would be interesting to hear what factors you considered while speculating and where they took you.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"Science is more than a body of knowledge; it is a way of thinking"+
    +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 23. Re: Finding an optimal db_block_size
    jgarry Guru
    Currently Being Moderated
    Jonathan Lewis wrote:
    jgarry wrote:

    Just as an armchair speculation, I would expect a difference by blocksize in a heavily loaded, highly random accessed type of system where the desired data is scattered about many blocks.
    I think it was Carl Sagan who once said: "I don't think with my guts." In the same vein, I don't think you should speculate with your armchair. But if you do, I'm sure it would be interesting to hear what factors you considered while speculating and where they took you.
    Well, one factor would be simply having to get bigger blocks, if there is generally only one desired column, there's all that other undesired stuff that has to get read in. Which ages the buffer more rapidly as many blocks are read in. Don't you think if we can hypothesize a simple test that can lead us to a performance inflection point based solely on blocksize, we should? If the docs say a myth about certain types of systems working better with certain blocksizes, we want to be mythbusters.

    What's wrong with armchair speculation? It leads to hypotheses that can be tested. "We wish to find the truth, no matter where it lies. But to find the truth we need imagination and skepticism both. We will not be afraid to speculate, but we will be careful to distinguish speculation from fact." - Carl Sagan And of course http://en.wikipedia.org/wiki/Drake_equation which means I should say "it *may* lead...," but does illustrate something about Carl Sagan. Positively, in my view (I was a fan, and also studied Drake when I took some astronomy in college - I still have the classic book in my basement), even if such speculation leads to guilt-by-association with UFO cultists.

    A bit of googling gets
    I'm often asked the question, "Do you think there is extraterrestrial intelligence?" I give the standard arguments -- there are a lot of places out there, and use the word billions, and so on. And then I say it would be astonishing to me if there weren't extraterrestrial intelligence, but of course there is as yet no compelling evidence for it. And then I'm asked, "Yeah, but what do you really think?" I say, "I just told you what I really think." "Yeah, but what's your gut feeling?" But I try not to think with my gut. Really, it's okay to reserve judgment until the evidence is in. - Carl Sagan, The Burden Of Skepticism, The Skeptical Inquirer, Vol. 12, Fall 87
  • 24. Re: Finding an optimal db_block_size
    767685 Newbie
    Currently Being Moderated
    Jonathan Lewis wrote:
    I missed the bit where John-M said he was going to ignore Hans suggestions for testing and only do simple tests ;)
    Hans Forbrich wrote:
    To do the tests properly and truly come to conclusions, I see the need for 24 tables (very small, small, medium, large, very large and extremely large row count) x (many rows per block, medium number of rows per block, few rows per block, forced overflow) for each of the table types for each of the block sizes. Then I see the need to use various index combinations. Then I see the need for various concurrency load levels, probably around 10 different session groupings with different combinations of Select and DML. So somewhere around 400 tests per database block size, and another round of 400 for each non-DB block so you can study the impact of the non-DB block against the different DB Data dictionary/undo combinations.
    Hi Jonathan,
    No I'm not going to ignore Hans Forbrich suggestions. Actually I also had planned something like that only except what he said that

    I see the need for various concurrency load levels, probably around 10 different session groupings with different combinations of Select and DML. So somewhere around 400 tests per database block size, and another round of 400 for each non-DB block so you can study the impact of the non-DB block against the different DB Data dictionary/undo combinations.

    I'm not sure about this thing but rest of the suggestion is very useful for me. As it seems lil difficult :)

    Can Hans Forbrich or you please suggest what will be the number in KB or MB when Hans say u need:

    very small , small , medium , large , very large , extremely large row count ???

    and specially

    many rows per block, medium number of rows per block, few rows per block, forced overflow ???

    Thank you

    Regards..
  • 25. Re: Finding an optimal db_block_size
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    John-M wrote:
    +I see the need for various concurrency load levels, probably around 10 different session groupings with different combinations of Select and DML.
    Unless you test concurrency, you are missing the whole point.

    >
    Can Hans Forbrich or you please suggest what will be the number in KB or MB when Hans say u need:

    very small , small , medium , large , very large , extremely large row count ???
    As a rule of thumb, I would have taken

    Very small = 1 block
    Small = less than 1 multiblock read count
    Medium = about 10 multi-block read count
    Large = ~ 50 multi-block read count
    Very large = ~ 100-250 multi-block read count
    Extremely large = 3-5 Luns
    and specially

    many rows per block, medium number of rows per block, few rows per block, forced overflow ???
    Many rows/block would approximate code lookup tables - key + description or about 50 bytes per row ... 80-100 rows for a 4K block

    Medium rows would be typical customer address tables - around 150 bytes per row ... 30 rows / 4K block

    Few rows per block around 750-1K bytes/row ... 3-5 rows / 4K block

    Forced overflow might be 6K/row and other exercises might just add a LOB and test against the LOB


    I just realized that varying percent free and percent used across all these combinations would be interesting as well.


    The objective of the tests is to find those boundary conditions, exercise them, and understand what is happening at that time. As you move along, you might find that my suggestions are too limited and you have to work out new tests.

    Another wrinkle to add ... how about ASM?


    This is starting to be intriguing. I wish I had a couple of years free ...
  • 26. Re: Finding an optimal db_block_size
    jgarry Guru
    Currently Being Moderated
    John-M wrote:
    Jonathan Lewis wrote:
    I missed the bit where John-M said he was going to ignore Hans suggestions for testing and only do simple tests ;)
    Hans Forbrich wrote:
    To do the tests properly and truly come to conclusions, I see the need for 24 tables (very small, small, medium, large, very large and extremely large row count) x (many rows per block, medium number of rows per block, few rows per block, forced overflow) for each of the table types for each of the block sizes. Then I see the need to use various index combinations. Then I see the need for various concurrency load levels, probably around 10 different session groupings with different combinations of Select and DML. So somewhere around 400 tests per database block size, and another round of 400 for each non-DB block so you can study the impact of the non-DB block against the different DB Data dictionary/undo combinations.
    Hi Jonathan,
    No I'm not going to ignore Hans Forbrich suggestions. Actually I also had planned something like that only except what he said that
    Jonathan put a winky eye there to indicate he did not see where you said such a thing, I believe. I was referring mainly to where you said you would ignore timing from cached results. Later Hans mentioned you are missing the point that concurrency is the point. I hope you are not missing that point, but can't really tell now.

    >
    I see the need for various concurrency load levels, probably around 10 different session groupings with different combinations of Select and DML. So somewhere around 400 tests per database block size, and another round of 400 for each non-DB block so you can study the impact of the non-DB block against the different DB Data dictionary/undo combinations.

    I'm not sure about this thing but rest of the suggestion is very useful for me. As it seems lil difficult :)
    Coming up with a generalized answer to determining blocksize is difficult. That is why most people suggest an empirical test that models the application use. Of course, if the database is going to have multiple uses, as consolidation is the big thing and Oracle recommends one db per server (or do they?), then it becomes difficult again.

    In any case, you might investigate Dan's suggestion of Real Application Testing. I wouldn't know about that.

    I hope you understand that most criticism here is intended to help drive people to reasonable tests, and we are all both interested and passionate about such things, even learning from Oracle newbies, though that can be tough for some of us because we see the same misapprehensions repeatedly.
  • 27. Re: Finding an optimal db_block_size
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    jgarry wrote:

    Jonathan put a winky eye there to indicate he did not see where you said such a thing, I believe.
    I should also have put a "winky eye" by my comment about speculating with arm-chairs, although I was trying to make a serious point about speculating in public without stating the assumptions that led you to your hypothesis.
    Just as an armchair speculation, I would expect a difference by blocksize in a heavily loaded, highly random accessed type of system where the desired data is scattered about many blocks.
    Well, one factor would be simply having to get bigger blocks, if there is generally only one desired column, there's all that other undesired stuff that has to get read in. Which ages the buffer more rapidly as many blocks are read in.
    The rest of us can now review the basis of your speculation, allowing someone like John-M to prune their test requirements. For example:

    Very random access to a minimum amount of data from a row, where the rows are so randomly scattered that every row access requires an individual block access and blocks are rarely going to be re-visited when cached, so smaller blocks do less damage to the buffer cache. But if that's the case, then two thoughts spring to mind:
    <ul>
    a) (easy) should you have a recycle pool declared for the objects for which there's no point in caching
    b) (harder) should you have data clones for these critical objects that are copies of just the required data packed in the right way so that you get better caching of the data you need (e.g. through covering, function-based, indexes).
    </ul>

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"Science is more than a body of knowledge; it is a way of thinking"+
    +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
  • 28. Re: Finding an optimal db_block_size
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    Jonathan Lewis wrote:
    The rest of us can now review the basis of your speculation, allowing someone like John-M to prune their test requirements. For example:

    Very random access to a minimum amount of data from a row, where the rows are so randomly scattered that every row access requires an individual block access and blocks are rarely going to be re-visited when cached, so smaller blocks do less damage to the buffer cache. But if that's the case, then two thoughts spring to mind:
    <ul>
    a) (easy) should you have a recycle pool declared for the objects for which there's no point in caching
    b) (harder) should you have data clones for these critical objects that are copies of just the required data packed in the right way so that you get better caching of the data you need (e.g. through covering, function-based, indexes).
    </ul>
    This is getting interesting. I think there might be value on describing where such data storage-and-access pattern might occur in real life.

    Since most environments I've seen (whether design, development, or administration) do not take/have the time to understand their data - no size estimates, no traffic estimates - very few would be able to make any ROT (rule of thumb) adjustments for any boundary conditions such as the above ... unless we started describing where these pattens occur. (And we just have to look at the success in the Java community to see how important it is to implement based on patterns. ;-) ) Separate tablespaces for indexes and data?

    Sounds like a document retrieval system with LOBs to me.

    Edited by: Hans Forbrich on Apr 24, 2010 6:51 AM
  • 29. Re: Finding an optimal db_block_size
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hans Forbrich wrote:

    Since most environments I've seen (whether design, development, or administration) do not take/have the time to understand their data - no size estimates, no traffic estimates - very few would be able to make any ROT (rule of thumb) adjustments for any boundary conditions such as the above
    Absolutely - and this, in part, is why asking the question "which block size is best" in the abstract is a fairly futile exercise. If you want to ask "in these very specific circumstances can I get a worthwhile benefit from choosing a specific blocksize" you could be asking a question that is worth the effort of finding an answer - but even when people have a fully operational system in front of them, I find that they can't always describe accurately what the system is doing in areas where the question might be sensible.

    >
    Sounds like a document retrieval system with LOBs to me.
    LOBs - one of the very few features where it's often easy to argue the case for non-standard block sizes, or non-default caches ... but usually only after the event, and you also need to check what's going on with the cache/nocache option as well, and then check that BFILEs wouldn't be a better idea anyway.


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

    To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
    {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
    fixed format
    .
    
    There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
    
    +"Science is more than a body of knowledge; it is a way of thinking"+
    +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points