large fragment on LMT tablespace

[dbase77]

Hi,

I have large fragment on our LMT tablespace. 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 tablesapce
5) move back in from temp tablespace

What do you think? Thanks

 

[SantaMufasa]

I have large fragment on our LMT tablespace. Because of this, it slows down the performance.

What proof have you that a "large fragment...slows down the performance"?


Locally Managed Tablespaces (LMT) exist for the very reason that we shouldn't have to worry about fragmentation/performance issues.

In direct response to your question, above, the steps you mention would result in a reorganised tablespace (but, again, I'm not convinced that you need to resort to those steps).

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dbase77]

Hi,

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

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
==========================

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?

 

[SantaMufasa]

If a table or an index has fragmentation, then a fast method of defragmenting any table (that does not include a LONG column) is:

alter table <table_name> move parallel nologging;

...and for indexes:

alter index <index_name> rebuild parallel;

Both of these commands are remarkably fast at defragmenting their respective objects.

If an LMT tablespace contains fragmentation, then that could be caused by a low-quality extent-allocation algorithm. I have never found that Oracle's autoallocate algorithm results in a poorly performing allocation scheme.

Therefore, can you please confirm the syntax that created the poorly performing tablespace?...i.e., did the creator use the syntax "...extent management local autoallocate..." or did s/he use other syntax?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dbase77]

Hi,

This is the code that I used moving from DMT to LMT:

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

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

Example of SQL that I used for LMT tablespace:

CREATE TABLESPACE "LMT_MIG_TMP"
DATAFILE  '/oradata/P/lmt_mig_tmp1P.dbf' SIZE 15360m reuse
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 32m;

 

[SantaMufasa]

First, dbase77, if your extents are uniformally 32M, then I cannot imagine that your tablespace has any harmful fragmentation.

This does not rule out, however, that your tables and indexes are fragmented (from DELETEs, INSERTs, and UPDATEs).

I'm still not convinced that you are encountering unacceptable performance. Just because a full-table scan is occurring does not mean that that is less efficient than using indexes...In fact, under many circumstances, traversing a table via an index can cause extremely poor performance.

If you would like a definitive answer in your case, I can put you in touch, via phone or e-mail, with one of the top Oracle-Performance-Tuning gurus/authors available (located in California), who can consult/advise you, on an hourly basis, concerning your issue.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]