Improve performant -defragmentation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Improve performant -defragmentation

  1. #1
    Join Date
    Mar 2001
    Posts
    52

    Talking

    Hello to all,

    I have to improve performance when I storing the data and I do not know exactly
    where to start.
    I query dba_segments&dba_extents and I found that I have segments whit more then 20 extents and pct_increase =50.
    I ran a query that gave me the fragmentation of free space in the tablespace
    select tablespace_name,
    SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)))) FSFI
    FROM DBA_FREE_SPACE
    GROUP BY tablespace_name
    ORDER BY 1;

    And
    The output is:
    Tablespace fsfi
    Tabs1 12,34
    Tabs2 6.7
    Tabs3 100

    My questions are:
    1.How can I find how bad a segment is fragmented?
    2.How can modify the storage parameters for a tablespace very fast?


    Thanks

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Red face


    For tablespaces, you should just export the logical data, drop the tablespace, recreate the tablespace with appropriate parameters (pct-used, pct-free), and then import your .dmp file into your new tablespace.

    personally, I was always taught to not fiddle with pct-increase. I would imagine that if you are a VERY educated DBA (like Jeff Hunter!) then you might know what you are doing with it. I always leave it at zero and worry about block-size, extent-size, pct-free and pct-used.

    anyone else have any comments?

    - Magnus

  3. #3
    Join Date
    Mar 2001
    Posts
    52

    Talking

    I am not an VERY educated DBA so this is a reason that there are some things that I can’t ‘see’ like
    - when I have to recreate a tablespace?
    - when recreate a tbs what I have to keep in mind (biger initial_ext,smaller next,pct_increase =0) ?

    thanks

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by jgmagnus

    For tablespaces, you should just export the logical data, drop the tablespace, recreate the tablespace with appropriate parameters (pct-used, pct-free), and then import your .dmp file into your new tablespace.
    Huh? You don't ever have to re-create your tablespaces. If you are trying to de-fragment your tablespaces, you should rebuild your objects via export/import or MOVE/REBUILD. If you want to change the default storage characteristics of your tablespace, you can change it at any time. If you want to change the storage characteristics of the existing segments, you will have to rebuild them.

    Begining DBAs should get in the habit of using Locally Managed Tablespaces. 80% of your STORAGE worries will go away with LMTs.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Posts
    52

    Angry

    Thanks but still I don't know what is the best choice;

    facts:
    1.The db got bigger and bigger in the past year
    2.Every day apps gets slower and slower.

    : fix the problem

    First questions:
    1.how do I know how BAD a tablespace is fragmented
    2.Same for segments.

    Thanks

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    With 20 extents, fragmentation is probably not your problem. I would take a top-down approach to tuning this database. Maybe start with http://technet.oracle.com/deploy/per.../statspack.pdf or http://technet.oracle.com/deploy/per...rf_method1.pdf
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Oh, and if you're absolutely convinced that fragmentation is your problem, check out http://technet.oracle.com/deploy/per...pdf/defrag.pdf
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width