Printable View
Hi,
I have large fragment on our LMT tablespace (after DMT -> LMT). Because of this, it slows down the performance. What is the best way to tackle this issue? Can I just:
1) create new temporary tablespace
2) move out data to temp tablespace
3) drop old tablespace
4) recreate old tablespace
5) move back in from temp tablespace
What do you think? Thanks
There are 4 types of fragmentation.
Which one are you talking?
Not sure myself. What are they anyway?
Yup.Quote:
Originally Posted by feroz
I think we have discussed that already
1) Create new LMT
2) move all the segments to it
3) Drop the old one
4) If u use 10g rename the newly created tablespace to match the name of the old one
no need to move data forht and back
Hi,
Thanks. But what I dont understand is, why LMT cause fragmentation? I thought one move from DMT into LMT to avoid fragmentation.
Can I paste my sql code that I use to move here? I just want to make sure I dont miss anything. Thanks again.
Im using oracle 9.2.0.6 on solaris OS.
Yes, but when U move dmt to lmt using the Oracle package, actually extents are not touched. Just the free list is converted to bitmap and mooved to the file header. So if u had fragmentation in the DMT it stays in LMT as well
So to say, DMT moved to LMT is half lmt :-) If u create a new LMT and move the objects to it, then fragmentation shold be solved once and forever :-)
sql script that i use to move out:
sql script that i use to move back:Quote:
accept TABLESPACE prompt "Enter tablespace name "
spool move_out.sql
select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'move nologging tablespace LMT_MIG_TMP;'
from dba_tables
where tablespace_name=upper('&TABLESPACE')
/
select 'alter table '
||l.owner||'.'
||l.table_name
|| chr(10)
||' move lob ('
||l.column_name
||') '
|| chr(10)
||'store as '
||l.segment_name
||' (tablespace LMT_MIG_TMP);'
from dba_lobs l, dba_segments s
where s.owner = l.owner
and s.segment_name = l.segment_name
and s.tablespace_name=upper('&TABLESPACE')
/
select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'rebuild nologging tablespace LMT_MIG_TMP;'
from dba_indexes
where tablespace_name=upper('&TABLESPACE')
/
spool off
Quote:
accept TABLESPACE prompt "Enter tablespace name "
spool move_back.sql
select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'move nologging tablespace &TABLESPACE;'
from dba_tables
where tablespace_name='LMT_MIG_TMP'
/
select 'alter table '
||l.owner||'.'
||l.table_name
|| chr(10)
||' move lob ('
||l.column_name
||') '
|| chr(10)
||'store as '
||l.segment_name
||' (tablespace &TABLESPACE);'
from dba_lobs l, dba_segments s
where s.owner = l.owner
and s.segment_name = l.segment_name
and tablespace_name='LMT_MIG_TMP'
/
select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'rebuild nologging tablespace &TABLESPACE;'
from dba_indexes
where tablespace_name='LMT_MIG_TMP'
/
select
'alter table '
||owner
||'.'
||table_name
|| chr(10)
||'logging ;'
from dba_tables
where tablespace_name='LMT_MIG_TMP'
/
select
'alter index '
||owner
||'.'
||index_name
|| chr(10)
||'logging ;'
from dba_indexes
where tablespace_name='LMT_MIG_TMP'
/
spool off
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.
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.