OraFAQ Forum: Performance Tuning » TABLESPACE EXTENT

Home » RDBMS Server » Performance Tuning » TABLESPACE EXTENT (11g )

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

Switch to threaded view of this topic

Create a new topic

Submit Reply

TABLESPACE EXTENT [message #547934]

Mon, 19 March 2012 02:10

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

1.2 million chained rows, 1.7 million blocks, etc. Initial extent for this table is 64k and next 1 mb. I would try to calculate this out better for efficiency and performance. This will not be efficient as it stands.So kindly help me to calculate the size.

Regards,
SathishKumar.K

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547936 is a reply to message #547934]

Mon, 19 March 2012 02:31

msol25
Messages: 211
Registered: June 2011
Location: Mumbai

Senior Member

Hi Satish,

Please execute query:


  select sum(bytes)/1048576 "Size in Mb"
  from   user_segments
  where  segment_name = 'TABLE_NAME';

Thanks

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547937 is a reply to message #547936]

Mon, 19 March 2012 02:40

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

Hi,

It has 10 Million records and it will grow atleast around 800K records per month.

Regards,
Sathish

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547939 is a reply to message #547934]

Mon, 19 March 2012 02:46

Michel Cadot
Messages: 54247
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

1.2 million chained rows

Are these really chained rows or are they also migrated rows?

Quote:

I would try to calculate this out better for efficiency and performance.

Do not calculate anything and use autoallocate (system managed) LMT (Locally Managed Tablespace).

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547946 is a reply to message #547939]

Mon, 19 March 2012 03:10

msol25
Messages: 211
Registered: June 2011
Location: Mumbai

Senior Member

Dear Satish,

Please use Michel's suggestions and if you are sure for inserting 800k records per month then you can use exchange partition in that case.

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547966 is a reply to message #547939]

Mon, 19 March 2012 05:24

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

Hi ,

It is migrated rows.

Regards,
Sathish

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547974 is a reply to message #547966]

Mon, 19 March 2012 06:06

Michel Cadot
Messages: 54247
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So first fix your application so that Oracle does not have to migrate so much rows.

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #547977 is a reply to message #547966]

Mon, 19 March 2012 06:36

John Watson
Messages: 3113
Registered: January 2010
Location: Global Village

Senior Member

As you have over 10% migrated rows, you may want to increase the PCTFREE for the table.

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548076 is a reply to message #547977]

Tue, 20 March 2012 04:25

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

Hi,

How to increase PCTFREE from 10 to 15 in oracle 11g R2?

Regards,
Sathish

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548078 is a reply to message #548076]

Tue, 20 March 2012 04:34

Michel Cadot
Messages: 54247
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you try ALTER TABLE?

Regards
Michel

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548086 is a reply to message #548076]

Tue, 20 March 2012 05:10

John Watson
Messages: 3113
Registered: January 2010
Location: Global Village

Senior Member

Are you certain of the algorithm you used to calculate that 15% is correct? You need to investigate the ratio between the size of row at insertion, and the size following updates.

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548089 is a reply to message #548078]

Tue, 20 March 2012 05:29

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

Hi,

But it wont affect current block.so is there any other way to do this .

Regards,
Sathish

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548092 is a reply to message #548089]

Tue, 20 March 2012 05:37

John Watson
Messages: 3113
Registered: January 2010
Location: Global Village

Senior Member

Open the Administrator's Guide, go to the index, and look for "migrated rows".

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548230 is a reply to message #548078]

Tue, 20 March 2012 23:22

sathish16787
Messages: 51
Registered: September 2011
Location: mumbai

Member

Hi,

Could you please help me .how to find max row length for a table.

Regards
Sathish

Report message to a moderator

Send a private message to this user

Re: TABLESPACE EXTENT [message #548236 is a reply to message #548230]

Wed, 21 March 2012 00:22

Michel Cadot
Messages: 54247
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What do you call "row length"?

Regards
Michel

Report message to a moderator

Send a private message to this user

Switch to threaded view of this topic

Create a new topic

Submit Reply

Previous Topic:	New User Help Me Out (merged 2)
Next Topic:	Ugly View Not Cooperating

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 25 15:18:45 CDT 2013

Total time taken to generate the page: 0.54206 seconds