This discussion is archived
1 2 3 4 5 11 Previous Next 163 Replies Latest reply: Oct 22, 2009 3:23 PM by Hoek Go to original post RSS
  • 30. Re: Index rebuild
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    burleson wrote:

    Do you really think that quoting one of the most hated idiots in the world will help your argument?
    Is on one of the "+most hated idiots in the world+" not your own George W. Bush? And er.. are you not quoting him?
  • 31. Re: Index rebuild
    635471 Expert
    Currently Being Moderated
    Billy  Verreynne  wrote:
    burleson wrote:

    Do you really think that quoting one of the most hated idiots in the world will help your argument?
    Is on one of the "+most hated idiots in the world+" not your own George W. Bush? And er.. are you not quoting him?
    "Yes", and "No". DB is quoting his father, George H.W. Bush.

    But it's probably not helpful to play up to his smoke and noise distractions from the issue of indexes. I'm learning a lot from this thread.
  • 32. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Jonathan,
    Your concept of the scientific approach is also somewhat exotic.
    Don't you recall, you were corrected on your own blog for misunderstanding basic science concepts?

    http://jonathanlewis.wordpress.com/2008/06/08/scientific-method/

    Correct me if I'm wrong, but you don't possess any science degees, nor scientific credentials whatsoever, correct?

    You claim to have the right to judge my scientific qualifications, well, let's see some evidence, some proof!

    I don't think that you are in any position to judge my approach to scientific investigation, given this:

    - You claim to understand the machinations whereby indexes require scheduled maintenance for performance, yet you refuse to disclose them?

    - You say that you understand the functional dependencies, but you refuse to tell us what they are?

    - You claim to know how to prevent the need to coalesce indexes, but you consistently refuse to share them?


    *************************************************************************
    (Actually, even if it's not documented by Metalink, wouldn't a few block dumps be fairly convincing anyway ?)
    No. Sorry . . .

    We have all seen how you can bias a test case to show whatever you want.

    If what you say is true, then simply get Oracle to issue a bug number.  I'm sure that you can convince them with your "proof".

    But I'm not holding my breath. I think that this whole thing is subterfuge, a non-issue . . .

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

    Jonathan, you always present yourself as an intelligent person, so I must assume that this quote is a deliberate attack against all people of Faith:

    +"If you claim that something is true, I will examine the evidence+
    +which supports your claim; if you have no evidence, I will not+
    +accept that what you say is true and I will think you a foolish+
    +and gullible person for believing it so."+ - Richard Dawkins

    Everyone know that this quote is condemning all people who have faith in God as being "foolish and gullible".

    That's why Dawkins is widely hated, he comes off as an insulting, pompous, arrogant, intolerant twit.

    Regardless, anybody who has Faith, regardless of their creed, finds your posting GROSSLY offensive.

    Jonathan Lewis, I respect your right to be a Godless atheist, why can't you respect our belief in God?

    I don't believe that you didn't understand how your post offends all Christians, Jews and Muslims alike.

    Sure you do.

    You are not stupid Jonathan, and this was no accident.

    You know that Rich Niemiec and I are Christians . . .

    Next time I see you, Jonathan, we are going to have a chat about this, face to face. . . .

    Here in America we respect peoples religious beliefs, we don't condemn them because just they believe in something that they cannot see.

    I think that you owe everyone in this forum an apology. . .
  • 33. Re: Index rebuild
    hans forbrich Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    I don't believe that you didn't understand how your post offends all Christians, Jews and Muslims alike.
    Such a wide sweeping statement ... one in which you overstep your authority. I take offense that you presume to speak for me, being in one of the above categories.

    Might I suggest to both you and Jonathan that it is time to put this thread on hold?
  • 34. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:
    Hi Jonathan,


    If what you say is true, then simply get Oracle to issue a bug number.  I'm sure that you can convince them with your "proof".
    It's not a bug, it's simply the expected behaviour - as documented on Metalink and demonstrable with some simple programming.

    I have made an error in the statement though - the requirement for co-prime values applies only in single instance systems (as per the exchange of letters you had with Rich Niemiec). In RAC systems the freelist group block selected is dependent on the Instance id.

    Regards
    Jonathan Lewis


    +"Your manuscript is both good and original; but the part that is+
    +good is not original and the part that is original is not good."+

    Samuel Johnson
  • 35. Re: Index rebuild
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    burleson wrote:

    We used a script like this to monitor the fragmentation and loading of the individual freelists, since free blocks cannot be shared between freelist groups:

    http://www.remote-dba.cc/oracle_tips_freelist_blocks.htm
    Dear Mr. Burleson,

    You might wish to make a few changes to the note on your website that has the title: +"How to count the number of blocks in each freelist group for a table"+.
    1) "Multiple freelists allow multiple transactions to grab free blocks from the segment header without causing buffer busy waits or segment header contention".
    I think you intended to start that sentence with "Multiple freelist groups", although if you are using multiple freelist groups then the free blocks are not grabbed from the segment header, they are grabbed from the freelist group blocks - which is why multiple freelist groups eliminate segment header contention.
    2) "As the single task deletes the rows, only one freelist is re-populated."
    I hesitate to suggest that you might want to mention the distinction between transaction freelists, process freelists, and the master freelist as I don't know if your note is aimed at the novice or the expert; but for the novice you might want to simplify the explanation by suggesting that only one freelist group acquires the free blocks and that the free blocks will not be automatically shared across multiple freelist groups.
    3) ".. a table that continues to extend, even though the dba_segments view shows lot's of free blocks."
    I think you meant the dba_tables view, not the dba_segments view.
    4) "The following script will walk the freelists chains on the tables and provide you with the relative number of free blocks on each freelist chain."
    Again I don't know if you want to say anything to distinguish between the three types of freelists, but your code is trying to report the number of free blocks associated with each freelist group. Possibly all you need do here is delete the word "chains" in the first part of the sentence, and change the word "chain" to "group" in the second part.
    5) "If you see a serious imbalance, you should reorganize the table to coalesce the freelists".
    It would probably be more sensible to use the dbms_repair.rebuild_freelists procedure to re-distribute the free blocks evenly across all the freelist groups - taking note of the bug that means you may have to re-run the procedure under a different process if it doesn't do the job perfectly first time.
    6) The script raises a few questions:
    a) the name of a table does not need to match the name of its data segment so the join is not appropriate. The script will not treat partitioned tables nicely, nor will it handle systems where two different schemas own objects with the same name.

    b) You reference column empty_blocks. This column is not populated unless you use the deprecated analyze command to gather statistics. Moreover, it shows you the number of blocks that have been allocated to the segment but are still above the high water mark, not the number of blocks on the freelists (*num_freelist_blocks*, also populated only by the analyze command) - which is presumably what you should be looking at to decide if you need to do something about an uneven distribution of free blocks across the freelist groups.

    c) If this note is aimed at the novice, you might want to point out that it assumes they are using a 4KB block size for their database.

    d) I am a little puzzled that you check only the first two freelist groups when a small change to the driving cursor would allow you to check all of them and make the entire procedure a little shorter and tidier.
    Regards
    Jonathan Lewis

    +"We have, indeed, developed evidence for such exceptions ourselves, because+
    +a theory should be challenged by those who have postulated it. "+
    J.L.King & T.H.Jukes
  • 36. Re: Index rebuild
    UweHesse Expert
    Currently Being Moderated
    >
    All that you have "proven" is that artificial test cases are less than useless because you can manipulate them at-will to support whatever conslusions you want.

    We both know that "test cases" prove NOTHING. They are just a tool that can be abused to mislead the public and to unfairly discredit honest experts . . .
    >

    Apart from all the "smoke" in this thread (about believing in god resp. insulting people who do so), I think the above quoted statements are the most important ones that should no pass without a contradiction:

    Of course is it possible to prove something with test cases ! If you reveal all of the conditions that you imposed on the case, it should be reproducable for others as well and it will be possible for others to detect biases of your test case as well, should there be any.

    Neglecting that is utterly un-scientific.

    You, Mr. Burleson, take a mere authority posistion, basically saying: This is so because I the expert say so. Believe me (or my experience, for that matter).

    That makes it impossible to get into a scientific discurse and is historically speaking a regression to the age of scholasticism.

    And that is where you and Mr. Lewis (and also the other members of the Oak Table) differ so fundamentally.

    Kind regards
    Uwe

    http://uhesse.wordpress.com
  • 37. Re: Index rebuild
    546494 Journeyer
    Currently Being Moderated
    my personal view on index rebuilding.
    we are running an application called swingbench continually and record the profile of performance from OEM and also AWR and statspack ( this is purely on a development server so I hope I dont need to worry about licensing AWR....................... :) )

    all things in the database stay the same but through monitoring of indexes we do see an improvement is performance when indexes are rebuilt.

    Now I am no great expert on the intracacies of the internals of the database and indexes I can only provide a result on a fixed database installation and an application I believe ti be running a standard load against the said database.

    regards

    Alan
  • 38. Re: Index rebuild
    311441 Employee ACE
    Currently Being Moderated
    Hi Alan

    What about other databases you manage, do you need to rebuild indexes on these to improve performance or is it only this specific application / database that benefits from indexes being rebuilt ?

    Also on this specific database, do you need to rebuild each and every index or is it only specific indexes that are problematic ? When you say an improvement in performance, is this global performance or performance when performing just specific functionality ?

    This is the key issue. If you understand what's causing the performance issue, what indexes are actually problematic, then you can narrow down and ensure any maintenance activities actually addresses the problem at hand and you reduce the effort and overheads associated with this activity by "fixing" just the indexes that are really problematic. For small databases, maybe you can get away with rebuilding all indexes but if you have (say) 5T of indexes and high availability requirements, it may not be feasible to rebuild all such indexes, especially when it's only those specific (say) 5 indexes that are really problematic in your application.

    Also, coalescing an index is often a far better solution than rebuilding the index with most of these problematic indexes.

    My personal view on index rebuilding is that if you have a car that is not running smoothly, you generally don't replace the entire engine to fix the problem (especially when it's the brake system that might be causing the problem). You diagnose the actual problem and address the specific issue and (say) change just the oil filter :)

    Cheers

    Richard Foote
    http://richardfoote.wordpress.com/
  • 39. Re: Index rebuild
    MarcinP Oracle ACE
    Currently Being Moderated
    Hi,

    I think that this is a technical forum and I can't understand why to mix religion with technology.
    Oracle is a piece of software and it has algorithms which should be working anytime in that same way - except bugs.
    So for well defined input data - output will be well defined too. So why test case are useless ?


    regards,
    Marcin Przepiorowski
    http://oraprof.blogspot.com/

    Edited by: Marcin Przepiorowski on Oct 12, 2009 10:50 PM
  • 40. Re: Index rebuild
    723408 Newbie
    Currently Being Moderated
    Hello,

    Index rebuild doesn't improve any performance in my case. In-fact a piece of code with 4 tables, 15 index and 10M records took double time after rebuilding index in my database. I can't test the coalesce, since i have SE.

    Regards
  • 41. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    +"Your manuscript is both good and original; but the part that is+
    +good is not original and the part that is original is not good."+

    Samuel Johnson

    Er, no. Not Johnson. No PROOF he ever said that.


    Love - Don Lewis

    A sure cure for seasickness is to sit under a tree.
    Spike Milligan
  • 42. Re: Index rebuild
    546494 Journeyer
    Currently Being Moderated
    Hello Richard,
    From our monitoring, it is only specific indexes that fit a certain criteria that we rebuild. Yes we do see benefits on client sites also when we look at the indexes. When we analyse statspack over say a weekly basis then we can see significant improvements when the system is carrying out say full index scans.

    The model for assessing whether an index needs to be rebuilt is some complicated mathematical equation which an ex colleague created. It is beyond me so I just take it that if an index falls into this category then we rebuild it. From experience this has always proved to be correct over a wide range of databases.


    regards

    Alan
  • 43. Re: Index rebuild
    706417 Explorer
    Currently Being Moderated
    Could this be a classic case of a monotonically increasing indexed column with deletes and inserts going on throughout the week?

    Regards - Don Lewis
  • 44. Re: Index rebuild
    108476 Journeyer
    Currently Being Moderated
    Hi Uwe,
    Of course is it possible to prove something with test cases
    Sure, but ONLY for that exact release, on that exact OS with those exact init.ora parms . . .

    Oracle is not black-and-white, you cannot claim to prove things because there are too many "exceptions to the rule" . . .
    it should be reproducible for others as well
    But in practice it isn't, that's the problem!

    Test cases (or any benchmark tests for that matter) CANNOT be generalized.

    In science (e.g. Newton, Darwin, etc.), we observe the real world and then try to describe them quantitatively.
    And that is where you and Mr. Lewis (and also the other members of the Oak Table) differ so fundamentally.
    Yup. Test cases can be abused by unethical people to cheat . . .

    Look at the world's bestselling Oracle tuning book by Rich Niemiec.

    Not a single "test case" in the whole thing, not one proof . . .

    Why?

    Rich is a man of honor, honest beyond reproach and with decades of well-documented experience.

    Rich has "nothing to prove" . . .

    On the other hand, there are people of questionable provenance, who I cannot trust, even when they "prove" something . . . They cheat, that much I can prove . . .

    It's the very core nature of credibility, and that's why personal integrity is so important . . .
1 2 3 4 5 11 Previous Next

Legend

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