Improve performant -defragmentation

[Donna]

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

[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.

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

[Donna]

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

[marist89]

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.

[Donna]

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

[marist89]

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

[marist89]

Oh, and if you're absolutely convinced that fragmentation is your problem, check out http://technet.oracle.com/deploy/per...pdf/defrag.pdf