This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Apr 2, 2013 4:04 PM by jgarry RSS

database fragmentation

416690 Newbie
Currently Being Moderated
I have a question about database fragmentation.

I know that fragmentation can reduce performance in query times. The blocks are distributes
in many extents and scans process takes a long time. Oracle engine have to locate the address
of the next extent..

I want to know if there is any system view in which you can check if your table or index
has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.

Any useful script or query to do this, any interesting oracle system view??

Any advice will bre greatly apreciatted.

Thanks in advance
  • 1. Re: database fragmentation
    sybrand_b Guru
    Currently Being Moderated
    Provided you are using Locally Managed Tablespaces:
    Why are you still subscribing to Ye Olde Fragmentation Myth?

    Using LMT, objects won't get fragmented, EVER.

    Ye Olde Fragmentation Myth has been invented by consultants to keep their income high: they come over to de-fragment your database every few months, and this doesn't resolve anything.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 2. Re: database fragmentation
    rajeysh Guru
    Currently Being Moderated
    this will helps you to understand fragmentation.
    http://www.orafaq.com/node/1936
  • 3. Re: database fragmentation
    Hoek Guru
    Currently Being Moderated
    You aren't on a pre-8i database version, are you?

    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1295801859138
  • 4. Re: database fragmentation
    416690 Newbie
    Currently Being Moderated
    Thank you, for your help.
    You're right I have LMT objects in Oracle 9i, but I have others databases in olders version, like....Oracle 7.
    In Oracle 7 I think there is fragmentation.

    Do you know any script or query to check the level of fragmentation???

    Thanks in advance
  • 5. Re: database fragmentation
    moslee Newbie
    Currently Being Moderated
    sybrand_b wrote:
    Provided you are using Locally Managed Tablespaces:
    Why are you still subscribing to Ye Olde Fragmentation Myth?

    Using LMT, objects won't get fragmented, EVER.

    Ye Olde Fragmentation Myth has been invented by consultants to keep their income high: they come over to de-fragment your database every few months, and this doesn't resolve anything.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
    Hi Sybrand

    From Oracle Doc ID 105120.1, I know the advantages of LMT but I would like to know how can I prove and test that using LMT, tablespace won't get fragmented? How can I test the Before and After effects? I have DMT tablespaces in Oracle 8i SE(8.1.6.0) and I will be using sys.dbms_space_admin .tablespace_migrate_to_local('USERS');

    Edited by: moslee on Apr 1, 2013 12:51 AM
  • 6. Re: database fragmentation
    sybrand_b Guru
    Currently Being Moderated
    1 8.1.6 is a desupported bug-ridden release of Oracle
    2 The procedure you quote was reported not to work very well, as it just updates the dictionary and doesn't migrate anything.
    3 IMO, there is no need to test anything. All extents will be a multiple of 64k (using autoallocate), or a multiple of any sensible UNIFORM unit you use (being a multiple of 64k), so simple logical thinking dictates there can be no 'fragmentation'. Yes, there can be a Compulsive Fragmentation Disorder (in many DBAs) as some people continue to spread the myth, and earn their income from 'defragmenting databases', and it is hard to cure, I have no problem to proof that myth is still alive.

    ----------
    Sybrand Bakker
    Senior Oracle DBA
  • 7. Re: database fragmentation
    moslee Newbie
    Currently Being Moderated
    Hi Sybrand

    Thanks for your prompt reply.

    Here's abit of my current situation which I have just taken over:
    Up to now my company is still using 8.1.6 for 3 identical DBs (DMT) and they are supposed to be in sync. For about every 50-60days, they will not be in sync and I will receive an alert. Past DBAs (those my senior) always reckons that the issue is due to fragmentation in any of the DBs. And they will always do a physical copy and paste of the system, datafiles to resolve the issue. Thus, the idea of 'defragmenting databases' still exist.

    I want to change them to LMT but they are all top critcal production DBs. But I have only 1 such DB for development, not 3. One way that I could prove that LMT reduces or eliminates fragmentation is to execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure in those 3 top critical production DBs and hope that I will not receive an alert in the future, but this would be very very risky.

    This is the reason why I need to prove and test that LMT actually works before implementing in my production. With the proof and testing that I have done, I can then confidently claim that LMT reduces or eliminates fragmentation.
  • 8. Re: database fragmentation
    sybrand_b Guru
    Currently Being Moderated
    First of all:
    You don't have 3 'top critical production' databases.
    'Top critical production' databases do not run 8.1.6. This is a bug ridden release, and Oracle won't support you if anything happens.
    Secondly:
    Apparently you don't believe my assertion dbms_space_admin doesn't work correctly.
    Finally:
    You seem to insist to apply a procedure which doesn't work to proof what is already clear by logical thinking.

    As to your current situation:
    I would close the door and run away, as fast as possible.

    I don't believe I can be of any further assistance as you flatly ignore my remarks.

    -----------
    Sybrand Bakker
    Senior Oracle DBA
  • 9. Re: database fragmentation
    moslee Newbie
    Currently Being Moderated
    Firstly, I already know that Oracle is not supporting me for those 3 DBs. But the fact is that they are still using 8.1.6. here. Likewise, apparently you choose not to believe me.
    Secondly, as much as I want to believe you in what you said and in Oracle Doc ID 103020.1, you have to state some facts here to support your words, and not being getting mad when someone don't buy what you said. My purpose here is to find the answer to my problems and not to go against what you said.

    Finally, to be frank, I am not clear in what you are explaining here +"All extents will be a multiple of 64k (using autoallocate), or a multiple of any sensible UNIFORM unit you use (being a multiple of 64k), so simple logical thinking dictates there can be no 'fragmentation'."+

    Please be objective here.

    If you choose not to believe the problem that I have now, likewise I seriously don't think what you said can be of any valuable help at all.

    Edited by: moslee on Apr 1, 2013 2:19 AM
  • 10. Re: database fragmentation
    moslee Newbie
    Currently Being Moderated
    Oh my..When I googled your name, it seems like you are a well known bully =)
  • 11. Re: database fragmentation
    sybrand_b Guru
    Currently Being Moderated
    I can not help it when you don't want to consider an objective statement as objective


    "All extents will be a multiple of 64k (using autoallocate), or a multiple of any sensible UNIFORM unit you use (being a multiple of 64k), so simple logical thinking dictates there can be no 'fragmentation'."

    Please be objective here.


    It seems you prefer subscribing to myths to logical thinking.
    I won't waste my time on you, given you are also subscribing to the common slander in my direction.
    (I'm referring to your subsequent post, which I consider to be a flame).

    If you don't want to hear the truth, I can not help you. The truth is you have 3 production databases out of whack, and 'testing' ad nauseam is not going to help you out.

    Rest assured I will remember your anonymous handle, and not respond to any of your questions anymore.
    -------------
    Sybrand Bakker
    Senior Oracle DBA
  • 12. Re: database fragmentation
    EdStevens Guru
    Currently Being Moderated
    moslee wrote:
    Hi Sybrand

    Thanks for your prompt reply.

    Here's abit of my current situation which I have just taken over:
    Up to now my company is still using 8.1.6 for 3 identical DBs (DMT) and they are supposed to be in sync. For about every 50-60days, they will not be in sync and I will receive an alert. Past DBAs (those my senior) always reckons that the issue is due to fragmentation in any of the DBs. And they will always do a physical copy and paste of the system, datafiles to resolve the issue. Thus, the idea of 'defragmenting databases' still exist.
    this, in itself, makes no sense. What defines a database not being 'in sync' with another? Exactly what situation is it that triggers this alert? The term 'in sync' could mean anything at all, but it usually has something to do with data. I've never seen it to mean anything that would be remotely related to fragmentation.

    I want to change them to LMT but they are all top critcal production DBs. But I have only 1 such DB for development, not 3. One way that I could prove that LMT reduces or eliminates fragmentation is to execute the DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL procedure in those 3 top critical production DBs and hope that I will not receive an alert in the future, but this would be very very risky.

    This is the reason why I need to prove and test that LMT actually works before implementing in my production. With the proof and testing that I have done, I can then confidently claim that LMT reduces or eliminates fragmentation.
    You are not going to be able to "prove" anything until you have a firm definition of "fragmentation" and whatever it is that you refer to as "being in sync". In the broader sense, nothing can ever be "proven" until the terms are defined and the definitions accepted by all interested parties.
  • 13. Re: database fragmentation
    moslee Newbie
    Currently Being Moderated
    yes please.


    The Senior Oracle DBA is a tag hanged by the community in recognition to the professional quality not a tag hanged by itself.

    Edited by: moslee on Apr 1, 2013 6:12 PM

    Edited by: moslee on Apr 1, 2013 6:12 PM
  • 14. Re: database fragmentation
    sb92075 Guru
    Currently Being Moderated
    ju**** wrote:
    Thank you, for your help.
    You're right I have LMT objects in Oracle 9i, but I have others databases in olders version, like....Oracle 7.
    In Oracle 7 I think there is fragmentation.

    Do you know any script or query to check the level of fragmentation???
    Hmmm.
    you assert that fragmentation exists,
    but yet you readily admit that you have NO quantitative means to measure fragmentation.

    Since you can't measure or quantify fragmentation, why do you believe fragmentation exists?
    Some folks believe in the tooth fairy. Do you believe the tooth fairy exists?
1 2 Previous Next

Legend

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