large fragment on LMT tablespace

[davey23uk]

might help if you post what you think fragmentation is - you may not have a problem at all

[feroz]

The real problem is when I had slow running SQL. I have somebody who investigated the problem. This is some of the report.

It was these investigations which highlighted that the slow running SQL wasn't using 3 key indexes required to execute efficiently.

At a low-level, the problem appears to be that theses indexes are being ignored due to a high clustering factor on each of these indexes. As part of the CBO stats regeneration it analyses an index's clustering factor. This clustering factor tells Oracle how many blocks will need to be read to get the data required by the query condition. If to get data Oracle is going to read almost all the table, then the optimiser will choose a full table scan without using the index. It is these full table scans that are taking the time.

Therefore, the problem is that the key indexes are not being used. A high clustering factor implies that the data is fragmented i.e. the rows that make up the table are on a large number of blocks requiring a greater number of I/Os (poorer performance).

Then I've checked through TOAD (DBA module), and I saw high percentage of fragmentation on some of our LMT tablespaces. So I thought of moving IN/OUT tables/indexes to solve my issue. The question is, will this solve my problem?

[feroz]

Opps !. Missed the last part.

The problem started occurring during the time of the migration of the tablespace management being dictionary (DMT) based to locally-managed (LMT). It is this fragmentation that results in the high clustering factor, and therefore, poor performance due to full table scans

[tamilselvan]

Bore,

Actually, I did create LMT tablespace first when moving from DMT. I used scripts that I paste here. And yet I still have fragmentation. I wonder why. Now I want to do the same thing in order to eliminate this fragmentation. DO you think by doing this will solve my issue?

Thanks.

Could you please define fragmentation at what level - block, row, segment, or extents level?

[ixion]

The real problem is when I had slow running SQL. I have somebody who investigated the problem. This is some of the report.



Then I've checked through TOAD (DBA module), and I saw high percentage of fragmentation on some of our LMT tablespaces. So I thought of moving IN/OUT tables/indexes to solve my issue. The question is, will this solve my problem?

I think you need to do some SQL Tuning... The CBO is incorrectly choosing an incorrect access path leading to poor, sql performance.

Why go through all of the work you're talking about when really the problem is SQL tuning????

Have you run stats to gather more current statistics to see if the CBO will correctly pick the correct or at least a better access plan? Was the system originally designed as a "RULE" based? If so maybe dropping status will get you there.

Ken

[slimdave]

The only way to reduce the clustering factor is to reorder the rows in the table. Fragmentation is irrelevant. You ought to spend time making sure you understand the concepts you are being presented with, and less time guessing.

[Bore]

Since u did that, there should not be fragmentation. The question is what u call fragmentation and do you really have an issue.
Generally fragmeted is considered a tablespace, where there are many small pieces of free space, which will never be used by any extent, since they are too small. If u have imagined that you will have a huge continious used space, packaged in the begining of the ts and a huge ammount of free space packaged at the end, then that's not the case

So I support the others, clustering factor has no much to deal with the fragmentation and U should tune the SQL

Regards
Boris

[slimdave]

So I support the others, clustering factor has no much to deal with the fragmentation ...

not much in the sense of "nothing", that is

[Bore]

Dave, I never say NOTHING :-) Who knows... maybe someone could somehow relate them but generally yes ;-) nothing

[waitecj]

... and collect histograms on the indexed columns.