might help if you post what you think fragmentation is - you may not have a problem at all
Printable View
might help if you post what you think fragmentation is - you may not have a problem at all
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?Quote:
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).
Opps !. Missed the last part.
Quote:
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
Could you please define fragmentation at what level - block, row, segment, or extents level?Quote:
Originally Posted by feroz
I think you need to do some SQL Tuning... The CBO is incorrectly choosing an incorrect access path leading to poor, sql performance.Quote:
Originally Posted by feroz
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
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.
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
not much in the sense of "nothing", that is :)Quote:
Originally Posted by Bore
Dave, I never say NOTHING :-) Who knows... maybe someone could somehow relate them but generally yes ;-) nothing
... and collect histograms on the indexed columns.