Home » RDBMS Server » Performance Tuning » TABLESPACE EXTENT (11g )
TABLESPACE EXTENT [message #547934] Mon, 19 March 2012 02:10 Go to next message
sathish16787
Messages: 56
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
Re: TABLESPACE EXTENT [message #547936 is a reply to message #547934] Mon, 19 March 2012 02:31 Go to previous messageGo to next message
msol25
Messages: 234
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

Re: TABLESPACE EXTENT [message #547937 is a reply to message #547936] Mon, 19 March 2012 02:40 Go to previous messageGo to next message
sathish16787
Messages: 56
Registered: September 2011
Location: mumbai
Member
Hi,

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

Regards,
Sathish
Re: TABLESPACE EXTENT [message #547939 is a reply to message #547934] Mon, 19 March 2012 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
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
Re: TABLESPACE EXTENT [message #547966 is a reply to message #547939] Mon, 19 March 2012 05:24 Go to previous messageGo to next message
sathish16787
Messages: 56
Registered: September 2011
Location: mumbai
Member
Hi ,

It is migrated rows.

Regards,
Sathish
Re: TABLESPACE EXTENT [message #547974 is a reply to message #547966] Mon, 19 March 2012 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
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
Re: TABLESPACE EXTENT [message #547977 is a reply to message #547966] Mon, 19 March 2012 06:36 Go to previous messageGo to next message
John Watson
Messages: 4489
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.
Re: TABLESPACE EXTENT [message #548076 is a reply to message #547977] Tue, 20 March 2012 04:25 Go to previous messageGo to next message
sathish16787
Messages: 56
Registered: September 2011
Location: mumbai
Member
Hi,


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


Regards,
Sathish
Re: TABLESPACE EXTENT [message #548078 is a reply to message #548076] Tue, 20 March 2012 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you try ALTER TABLE?

Regards
Michel
Re: TABLESPACE EXTENT [message #548086 is a reply to message #548076] Tue, 20 March 2012 05:10 Go to previous messageGo to next message
John Watson
Messages: 4489
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.
Re: TABLESPACE EXTENT [message #548089 is a reply to message #548078] Tue, 20 March 2012 05:29 Go to previous messageGo to next message
sathish16787
Messages: 56
Registered: September 2011
Location: mumbai
Member
Hi,

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

Regards,
Sathish
Re: TABLESPACE EXTENT [message #548092 is a reply to message #548089] Tue, 20 March 2012 05:37 Go to previous messageGo to next message
John Watson
Messages: 4489
Registered: January 2010
Location: Global Village
Senior Member
Open the Administrator's Guide, go to the index, and look for "migrated rows".
Re: TABLESPACE EXTENT [message #548230 is a reply to message #548078] Tue, 20 March 2012 23:22 Go to previous messageGo to next message
sathish16787
Messages: 56
Registered: September 2011
Location: mumbai
Member
Hi,


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


Regards
Sathish
Re: TABLESPACE EXTENT [message #548236 is a reply to message #548230] Wed, 21 March 2012 00:22 Go to previous message
Michel Cadot
Messages: 58861
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What do you call "row length"?

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


Current Time: Sat Aug 23 06:49:31 CDT 2014

Total time taken to generate the page: 0.08873 seconds