This discussion is archived
1 2 3 Previous Next 38 Replies Latest reply: Nov 11, 2007 3:58 PM by 181444 Go to original post RSS
  • 15. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    One day, it might get to see the light of day, maybe ?
    Well, I know a publisher, and I'm sure it's probably worthy of being in the bookstores . . . .

    http://www.rampant-books.com/mgt_proposal_submissions.htm
  • 16. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    why precisely should we consider large block sizes for Fast Full Index Scans ?
    Is this the new 10gr2 “automatic multiblock tuning” feature? I just had this in my DBA forum on the topic:

    http://dba.ipbhost.com/index.php?showtopic=8783

    If you can show me that it contradicts this evidence, I will revise my notes accordingly!

    *****************************************

    Multiple block size advantages.

    SQL> r
    1 select count(MYFIELD) from table_8K where ttime >to_date('27/09/2006','dd/mm/y
    2* and ttime <to_date('06/10/2006','dd/mm/yyyy')

    COUNT(MYFIELD)
    -------------------
    164864

    Elapsed: 00:00:01.40
    ...
    (This command is executed several times - the execution time was approximately the same ~
    00:00:01.40)

    And now the test with the same table, but created together with the index in 16k tablespace:

    SQL> r
    1 select count(MYFIELD) from table_16K where ttime >to_date('27/09/2006','dd/mm/
    2* and ttime <to_date('06/10/2006','dd/mm/yyyy')

    COUNT(MYFIELD)
    -------------------
    164864

    Elapsed: 00:00:00.36
    http://www.dba-oracle.com/art_so_blocksize.htm

    This conclusion is by Robin Schumacher, now a senior VP at MySQL:

    “As you can see, the amount of logical reads has been reduced in half simply by using the new 16K tablespace and accompanying 16K data cache. Clearly, the benefits of properly using the new data caches and multi-block tablespace feature of Oracle9i and above are worth your investigation and trials in your own database. “

    ************************************

    Continuing to peel-back the layers, what do you think about the issue of “more inodes per block”?

    Regards,

    Donald K. Burleson
    "Root Hog or Die"
  • 17. Re: Index blocksize
    153119 Pro
    Currently Being Moderated
    Here we see the typical Don Burleson reasoning at work.
    First of all the authority argument : 'Because Robin Schumacher says so'
    When you follow up the complete forest of links, you notice the following
    1 I/O seems to have been reduced. however during an index fast full scan.
    My remark : How often do you have an index fast full scan?
    Does it work on simple lookups also? 'Authority' Robin Schumacher doesn't tell us, at least not in the quote by Don Burleson.
    2 The book by Robin Schumacher which Don Burleson is quoting, is published by, not surprisingly, Rampant Press.
    Rampant Press is as far as I know owned by Don Burleson.

    So what we do have here:
    In order to 'speed up' a very specific categories of queries, Don Burleson recommends to use multiple blocksizes. This is trying to resolve an application problem by 'tuning' the database. Whether this approach helps for the other 90 percent of ordinary queries fired on this database, isn't disclosed, but it is very unlikely, because as soon as you select a primary key, you fetch 2 times as much records, you don't need at all, compared to the old 8k blocksize.
    The test, resulting in a recommendation to use multiple blocksizes, is incomplete.
    The proof shown here on it's own is insufficient to decide for using multiple blocksizes.

    At the same time, Don Burleson promotes a publication of his own publishing company, by referring to it.
    This is what I call 'The Burleson Marketing Machine', Please note other authors do not promote their publications here as aggressively as Don Burleson, or they do not promote their publications at all.
    As Don Burleson is promoting his publications and other materials so aggressively, people might even think, other publications do not exist.
    Quite frankly, this is bad!

    --
    Sybrand Bakker
    Senior Oracle DBA


    --
  • 18. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    http://www.lifeaftercoffee.com/2007/11/01/you-find-it-in-the-strangest-places/

    Sorry, Sybrand Bakker, I don't talk to racists.
  • 19. Re: Index blocksize
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Hi Richard,

    Is this the new 10gr2 “automatic multiblock tuning”
    feature? I just had this in my DBA forum on the
    topic:

    http://dba.ipbhost.com/index.php?showtopic=8783

    If you can show me that it contradicts this evidence,
    I will revise my notes accordingly!

    *****************************************

    Multiple block size advantages.

    SQL> r
    1 select count(MYFIELD) from table_8K where ttime >to_date('27/09/2006','dd/mm/y
    2* and ttime <to_date('06/10/2006','dd/mm/yyyy')

    COUNT(MYFIELD)
    -------------------
    164864

    etc. etc. etc.
    A) The fact that the db_file_multiblock_read_count defines the size of a multiblock read in terms of the standard block size (as described in Richard's post) bears no relationship whatsoever to the fact that 10g tries to use a sensible multiblock read size if you don't specify one.

    B) If you're going to quote other posts, it's a good idea to check the links before you do so - they can be very informative. The link you've posted points (ultimately) to Tom Kyte's website, but the fails to identify the specific item quoted. However it is very easy to find the item, which is at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:44779427427730#474704400346599870.

    If you read it, you will find that Tom's response is as follows:

    I want to see a tkprof, without that - we would only be GUESSING.

    it would have the row source operation, showing the plans.
    it would have the amount of work performed
    it would show physical as well as logical IO's
    it would have useful information

    I won't comment unless and until you have it.


    It's been more than two weeks since Tom asked for the relevant supporting information - but it hasn't yet been supplied.

    Since you didn't understand Richard's explanation of the way the db_file_multiblock_read_count is used when non-standard block sizes are used, you are probably not aware of the fact that a change in block size can result in a change in execution plan. That's why it's important to have information about execution plans and actual work done when creating and running test cases.

    I note, in passing, that in the past you have been keen to insist that 'small test cases prove nothing about Oracle performance' - yet you seem to be perfectly happy to suggest that this incomplete sample of SQL and timings does prove something.

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

    Message was edited by: Jonathan Lewis
    To point out that the link to Tom Kyte's site was indirect, and not the immediate target of the quoted URL.
  • 20. Re: Index blocksize
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    You're missing the point !!

    And so was Robin with the consistent gets example which suggests neither of you understand what the consistent gets value is actually representing and why it's a meaningless comparison when discussing different block sizes.

    As I tried to explain, the actual reads being performed by Oracle in the background during a Index Fast Full Scan is effectively the same, regardless of the block size. Robin's example shows the difference in how Oracle calculates the consistent reads, it doesn't consider the simple fact that the consistent reads don't represent the same thing in each example.

    Don, it's the trap that people who don't sufficiently study what numbers actually represent fall into again and again.

    Don, perform this very simple demo to highlight exactly what I'm trying to explain. This will fundamentally apply in all supported versions of Oracle in all environments.

    First create a simple table/index such as the following:

    SQL> CREATE TABLE bowie AS SELECT rownum id, sysdate-10000+mod(rownum,10000) date_field FROM dual CONNECT BY LEVEL <=1000000;

    Table created.

    SQL> CREATE INDEX bowie_i ON bowie(date_field);

    Index created.

    SQL> exec dbms_stats.gather_table_stats(ownname=>'BOWIE', tabname=>'BOWIE', estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1');

    PL/SQL procedure successfully completed.

    Now check the db_file_multiblock_read_count as this value is important to what happens.

    SQL> show parameter db_file

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    db_file_multiblock_read_count integer 88

    Now flush the buffer cache and trace the session to ACTUALLY see what's happening.

    SQL> set autotrace traceonly

    SQL> alter system flush buffer_cache; -- note diff in 9i and may benefit if run several times

    System altered.

    SQL> alter session set events '10046 trace name context forever, level 12';

    Session altered.

    Now run a simple query to ensure Oracle performs an IFFS ...

    SQL> SELECT /*+ index_ffs (bowie, bowie_i) */ count(*) FROM bowie where date_field > '01-JAN-1980';

    Note the explain plan uses a IFFS


    Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |
    ---------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 8 | 727 (2)| 00:00:09
    |
    | 1 | SORT AGGREGATE | | 1 | 8 | |
    |
    |* 2 | INDEX FAST FULL SCAN| BOWIE_I | 1000K| 7812K| 727 (2)| 00:00:09
    |
    ---------------------------------------------------------------------------------

    Statistics
    ----------------------------------------------------------
    2664 consistent gets

    Yes, the consistent gets is 2664.

    However, if you look at the trace file, you'll notice the db file scattered read waits have a majority of blocks equal to the db_file_multiblock_read_count as expected, for example:

    WAIT #1: nam='db file scattered read' ela= 47782 file#=4 block#=8587 blocks=88 obj#=71215 tim=433050316264

    Note the total waits and time spent reading the index and most importantly, the total response time of the query ...

    Now repeat the same exercise, but this time create the index in a bigger block size tablespace (in this example we've gone from 8K to 16K)

    SQL> drop index bowie_i;

    Index dropped.

    SQL> CREATE INDEX bowie_i ON bowie(date_field) tablespace ts_16k;

    Index created.

    SQL> alter system flush buffer_cache;

    System altered.

    SQL> SELECT /*+ index_ffs (bowie, bowie_i) */ count(*) FROM bowie where date_field > '01-JAN-1980';

    We have the same execution plan (although note different costs):

    ---------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |
    ---------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 8 | 500 (2)| 00:00:07
    |
    | 1 | SORT AGGREGATE | | 1 | 8 | |
    |
    |* 2 | INDEX FAST FULL SCAN| BOWIE_I | 1000K| 7812K| 500 (2)| 00:00:07
    |
    ---------------------------------------------------------------------------------

    And the stats

    Statistics
    ----------------------------------------------------------
    1319 consistent gets

    Wow, look at the consistent reads, how exciting it's approximately 1/2 the previous value, things must be so much better, maybe even twice as good.

    BUT ...

    If you look at the trace file, you'll notice that the majority of db file scattered reads now only have up to 44 blocks read at a time, eg:

    WAIT #3: nam='db file scattered read' ela= 16584 file#=6 block#=70 blocks=44 obj#=71216 tim=433166577910

    So the actual amount of data read by BOTH queries at a time is effectively THE SAME.

    Look at the total wait time for both sets of reads, effectively THE SAME.

    Most importantly, look at the execution times for both queries, they're effectively THE SAME.

    There's a couple of fewer blocks here or there due to slightly less block overhead, but the differences are effectively negligible .

    Do you get it now Don ?

    This is all pretty basic stuff and as you can see it's all pretty easy to investigate. Looking at just the consistent gets stats is meaningless as the consistent gets value has different representation depending on the block size.

    And yet Don, despite there being negligible difference in performance between a IFFS no matter the block size, you have consistently suggested creating indexes in a bigger block size for such accesses.

    You have consistently used Robin's example as some wierd proof that bigger block sizes are better when it means no such thing.

    Again I ask Don, why ?

    Note the Ask Tom example has no such analysis. They're 2 queries in 2 different databases that have 2 different timings. But the timing differences can be caused nearly anything, not least that they're using entirely different execution plans. Unless you can see the execution plans, unless you can see where the time difference is actually spent on, it's totally and fundamentally impossible to say why the timings differ. It's pure guess work, nothing else although I would guess it likely it has nothing to do with the indexes being in different tablespaces.

    Don, please run the demo in whatever environment you like and see for yourself why your advice in this matter is just wrong wrong wrong !!

    When I come back from OOW, I'll break the other myths associated with having indexes in bigger blocks.

    Until then ...

    Cheers

    Richard Foote
  • 21. Re: Index blocksize
    153119 Pro
    Currently Being Moderated
    No problem, Burleson, I don't talk to self-proclaimed Oracle 'gurus'

    Please stop spreading the myth I am racist.

    --
    Sybrand Bakker
    Senior Oracle DBA
  • 22. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    You're missing the point !!
    It wouldn't be the first time! I'll re-read your post . . . .
    why it's a meaningless comparison when discussing different block sizes.
    OK, if I understand you correctly, you are saying the the decreased LIO is not the whole story, and there are other metrics which need to be examined.

    You claim that in your test, the total response times are "about the same", and you acknowledge that there is less block overhead, but that, in your test, the decreased block overhead is negligable. The question for me is:

    - "Is this enough evidence to support your generalization and assertion that it will never make a difference"?

    - "Is it possible your conclusion be a valid rule-of-thumb, with some exceoptions"?

    - "Can we find out the specific conditions where people see an appreciable reducntion in elapsed time"?

    But what about this claim by Santosh Kumar, is it invalid?

    Multiple block size advantages.

    He claims a 3x faster performance using a larger block size . . .

    Can you reply to Santosh Kumar and get more details, perhaps his full script?

    I'm not saying you are wrong, but I don't think that your example is enough to generalize the issue. . . . .

    I feel bad for "questioning authority" here, but your conclusions, while compelling, are more like a rule-of-thumb than a proof, especially since Santosh has shown contrary results.

    Let's get to the bottom of this. I'm sure that there are other people who have tested this . . . .

    Message was edited by:
    burleson
  • 23. Re: Index blocksize
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    Like I said, run the demo or something like it in any database on any platform. Vary it anyway you feel appropriate.

    It's there laid out for you to test and determine the answer to these questions yourself.

    It's all part of investigating the behavior of Oracle but a key part of what many of your writings fail to do is to actually trace the sessions and determine exactly what is happening and exactly where time is being spent.

    These issues should never, ever be determined by guess work or assumptions.

    Never feel bad about questioning authority. Never. If you feel you have an example that goes against what some authority has said or written, then maybe the authority hasn't considered something, or maybe it was once right but no longer in version X.

    Or maybe the authority was always wrong.

    Santosh Kumar didn't make the claims, he simply copied them from the Ask Tom website. The claim was made by "a reader from Russia" so you're placing your faith on a source you haven't even confirmed !!

    That's not "questioning authority", that's simply not performing research to the required standard of getting to the bottom of anything meaningful.

    Like I said, run the tests yourself and see the result for yourself, don't rely on second hand stories that may or may not be true. Like I said, "The reader from Russia" could have gotten the varying results simply because the execution plans differ between databases. We don't know, we can only guess.

    Guessing is not research.

    Cheers

    Richard
  • 24. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    Hi Richard,
    Guessing is not research.
    Publishing evidence is not "guessing", IMHO, and while research is great for shoing how Oracle works in general, test-bases are never representative and cannot "prove" anything about Oracle performance.

    The best we can hope for are the rule-of-thumb, like the one you postulated, guidelines that have exceptions! I'll show evidence below that some shops (and major vendors) have seen worthwhile gains, and let's evaluate their credibility together.
    don't rely on second hand stories
    I have nothing against test-cases, but published evidence from respected professionals is more credible, IMHO. For example, the HP research below would take months to do, and I trust the engineers at HP.

    However, I agree, that we need to carefully evaluate published evidence, and let's defer judgement until Santosh Kumar has had a chance to respond.

    To make this even more challenging, see my notes here:

    http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm

    I found two more cases for employing multiple blocksizes:

    - Faster updates - In 64-bit databases with large data buffers (over 50 gig), some shops claim a benefit fro segregating high-impact DML tables into a separate blocksize, assigned to a separate, small buffer. They claim that this improved DML throughput because there are fewer RAM buffer chains to inspect for dirty blocks.

    - Faster backups? - This exhaustive Hewlett Packard whitepaper titled "Backup and recovery best practices for an ultra-large Oracle database", concluded that "Using a large block size yielded best performance; however, it is least efficient for space utilization. "

    Message was edited by:
    burleson
  • 25. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    you seem to be perfectly happy to suggest that this incomplete sample of SQL and timings does prove something.
    Good question. Does evidence prove anything? I'm noting that the test-cases show no appreciable difference in response time, and I'm scouring for evidence from real-world companies, verifiable and credible sources.

    I've seen you introduce bias into your test cases Jonathan, and test-cases can be so easily manipulated that they are too suspect to be considered as generalizable evidence, IMHO:

    http://www.dba-oracle.com/t_biased_test_cases.htm

    Here is what Bob Jones said about your biased test case:

    "Wow, this guy really has too much time in his hands, or he just hate this Don guy too much. It is pointless to set _AREA_SIZE when using PGA_AGGREGATE_TARGET anyway."
    but it hasn't yet been supplied.
    I'll redact the Santosh evidence, pending verification . . . .
    To point out that the link to Tom Kyte's site was indirect, and not the immediate target of the quoted URL.[/
    When I'm on OTN, I cite OTN, it's considered good netiquette . . .

    If you find contradictory real-world evidence, I'll be glad to include it here:

    http://www.dba-oracle.com/t_multiple_blocksizes_summary.htm

    "Circumstantial evidence is occasionally very convincing, as when you find a trout in the milk" - Arthur Conan Doyle

    Message was edited by:
    burleson
  • 26. Re: Index blocksize
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Don,
    Hi Jonathan,

    [in the past you have been keen to insist that
    'small test cases prove nothing about Oracle
    performance' - yet ]
    you seem to be perfectly happy to suggest that
    this incomplete sample of SQL and timings does prove
    something.

    Good question. Does evidence prove anything? I'm
    noting that the test-cases show no appreciable
    difference in response time, and I'm scouring for
    evidence from real-world companies, verifiable and
    credible sources.
    Avoiding the point and attempting to deflect attention from your inconsistent requirements for quality of evidence, as usual. (You missed a bit when quoting me, by the way, so I've inserted it for you).

    I'll just have to assume that your failure to offer any technical response to Richard's comments means you have none,
    To point out that the link to Tom Kyte's site was
    indirect, and not the immediate target of the quoted
    URL.

    When I'm on OTN, I cite OTN, it's considered good
    netiquette . . .
    Interesting invention. So what do you call the nine times you've cited other sites in this thread (including the two that appear in the post itself) ?

    (Don't bother to try answering that question - I don't really care what you say to justify contradicting yourself again.)


    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.u
  • 27. Re: Index blocksize
    51034 Newbie
    Currently Being Moderated
    Don, you do realise, I hope, that the quotation from HP about using a large block size reads in full:

    Setting up the file systems
    Each of the four EVA virtual disks contained a VxFS file system. One file system was used to back up each of the VGs. Using a large block size yielded best performance; however, it is least efficient for space utilization.


    Their statement you quote as a supporting piece of evidence for the use of multiple block sizes within the same Oracle database is actually from a discussion about what file system block size to use and has got absolutely nothing whatsoever to do with Oracle configuration!

    In fact, as Table 3 in that document makes clear, they used 32K blocks for the database and ONLY 32K blocks.

    Regarding your own page which is a pretty good stab at bringing together lots of people's thoughts on the matter, I think it could be improved by a bit of editing: delete everything after the first 11 words.
  • 28. Re: Index blocksize
    311441 Employee ACE
    Currently Being Moderated
    Hi Don

    Have you attempted to run the demo I provided yet Don ?

    Remember, I was specifically discussing the issue of Index Fast Full Scans benefiting from larger block sizes (or not as the case may be), I haven't started on peeling away some of the other layers (myths) associated with larger block sizes for indexes.

    You can run it on any database version you like you know Don. You can run it on any environment. You can even play around and see what impact different db_file_multiblock_read_counts may have. You can even see what happens with smaller block sizes. Maybe even test the impact of Full Table Scans while you at it. You can see what impact different types of tablespaces has on it.

    The environment can be on any platform, it can be on an environment with 1000's of users or it can be on your laptop at home.

    You see, it doesn't have to be just the one demo, I've hopefully provided you with a basic strategy to develop as many demos as you want, so you can see exactly what behaviors are consistent and what aren't.

    Trust me Don, rather than relying on poor Robin's rather sad and sorry analysis, rather than consistently repeating "but hey, here's a proof that shows consistent gets is halved if you double the block size", once you realise that it only means the bigger bucket is dipped in for only half the time, you might just want to check out what actual benefits larger index block sizes have for index fast full scans.

    I'd be interested to hear your results although you may just find them consistently disappointing ...

    Cheers

    Richard
  • 29. Re: Index blocksize
    108476 Journeyer
    Currently Being Moderated
    Jonathan,
    your inconsistent requirements for quality of evidence
    You are right, I don't have any consistent requirements for evaluating the credibility of Oracle professionals, but I'm trying to refine a model:

    http://www.dba-oracle.com/art_so_web_credibility.htm

    What are your own requirements?
    your failure to offer any technical response to Richard's comments means you have none
    No, I have plenty of "real" evidence, from "real" shops.

    I'm not putting-in anythoing of my own because last time, you created a deceptive and invalid "proof" to show that I was wrong:

    http://www.dba-oracle.com/t_biased_test_cases.htm

    Here is what Bob Jones said about your biased test cases:

    "Wow, this guy really has too much time in his hands, or he just hate this Don guy too much.

    It is pointless to set _AREA_SIZE when using PGA_AGGREGATE_TARGET anyway."

    Your biased test case is a great example about why test cases cannot be trusted for anything to do with Oracle performance. In that case, you turned on a non-default feature that made no sense (the MTS, on a PC no less), and you did not disclose it to your readers.