OraFAQ Forum: Performance Tuning » Temp segment issue

Home » RDBMS Server » Performance Tuning » Temp segment issue

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Temp segment issue [message #188260]

Thu, 17 August 2006 12:14

kudur_kv
Messages: 75
Registered: February 2005

Member

Hi,

This is an issue I faced with oracle 8i (8.1.7.4)

I was moving an index from its original Tablespace to new tablespace. The table on which this index was built has about 20 mill rows.

I deleted the index(dont ask me why i did not try to rebuild it online!!!??) and then tried to create the new index in the new tablespace. The target tablespace is a dictionary managed tablespace.

The problem was that when i tried to create the index the first time, it reported ORA-1630. some thing like this - max extents reached for temp segments in tablespace ABC

I increased the max extents for that tablespace and tried to create the index , the same issue.

Why is oracle looking for a temporary segment in a permanent tablespace when there is ample amount of temporary tablespace available??

Can some one explain this for me please

Thanks in advance!!

Report message to a moderator

Re: Temp segment issue [message #188263 is a reply to message #188260]

Thu, 17 August 2006 12:39

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Online rebuild is not exactly a very 'good' workaround ( as it appears to be. Because with online rebuild upto somepoint, the before image and after image of index would be maintained. So you index tablepace would need double the actual space). If you can afford to have a downtime, go for offline index rebuild.
Allocate more space for the said tablespace and use UNLIMITED extents. Make sure you are not setting a MAXEXTENTS explicitly in the create index statement.
Create the dictionary tablespace with initial=next and pctincrease=0.
>>Why is oracle looking for a temporary segment in a permanent tablespace when there is ample amount of temporary tablespace available??
It is all about sorting. When you create an index, the data is actually sorted first. Temporary segments are used for this (inside the tablespace used for index). After the sorting is done, the temporary segment is converted into an index segment.

>>I was moving an index from its original Tablespace to new tablespace.
May i ask, why?

Report message to a moderator

Re: Temp segment issue [message #188365 is a reply to message #188263]

Fri, 18 August 2006 04:10

kudur_kv
Messages: 75
Registered: February 2005

Member

The reason i was moving the indexes from their original tablespace to a new one was because the DBA who built/designed the database kept both the indexes and the tables in the same tablespace!! Can you believe that??? This is a database which is the life line of the company's day to day functioning and operations.

I then decided to move all the indexes to the tablespace dedicated to the indexes. This new TBS was unused so far though it was created a long time ago.

I guess your answer makes a lot sense.

What I did was to drop the tablespace after ensuring there were no objects in that TBS, create a new TBS with unlimited max extents and the issue was resloved.

I did not know that oracle will acquire temp segments from the target tablespace for sorting!! Thank you for the quick response!

However, one question still stands. Before I dropped the index TBS and created the new one, I did try create the index back in the original tablespace which has a total of 50+ gigs worth datafiles and about 45+ gig worth data. This TBS should have had enough extents for the index creation right??
But this too gave the same error of not enough temp segments??!!

Report message to a moderator

Re: Temp segment issue [message #188380 is a reply to message #188365]

Fri, 18 August 2006 05:01

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> DBA who built/designed the database kept both the indexes and the tables
>> in the same tablespace!! Can you believe that???
Not really a "evil" thing to do ( as long as all the datafiles are spread across several physical disks).
Indexes are accessed serially. NOT parallel ly.
First oracle looks for indexes. gets the pointer. goes to the table.
So having both index/data in the same tablespace does not make much difference for performance.
size the index/data extents appropriately and spread them across several physical devices.

But for administration/maintenance/backup etc, yes. It is better to have indexes in separate tablespace (and disks).
And most of the present day server/storage are striped/mirrored/use some kind of raid(unless you are using just a series of hard disks like in a PC). So no matter where you keep data/index, it is all the the same at some point.

Separating index and table is good for administrative ease. It has nothing to do with performance.

>>which has a total of 50+ gigs worth datafiles and about 45+ gig worth data.
if i understand it right,
available space 50+ gb.
Data 45 gb.
you tried to create indexes on the same tablespace. You have around 5% space for all index.
You think that would be enough?

PS:
Next time, please post your exact error messages.

Report message to a moderator

Re: Temp segment issue [message #188978 is a reply to message #188380]

Tue, 22 August 2006 11:21

kudur_kv
Messages: 75
Registered: February 2005

Member

Apologies mahesh,

The 45GB of occupied space in the TBS includes all the indexes for 1800+ tables.

BTW, the error that I was receiving was ORA-1630.

Thanks.

Report message to a moderator

Previous Topic:	STATSPACK question on 10g and 8i
Next Topic:	Index Use and Renaming Partitions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 02 23:26:26 CDT 2024