OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Table extents (10g ver 10.2.0.3.0)

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

Table extents [message #364285]

Sun, 07 December 2008 19:36

rcd70
Messages: 14
Registered: April 2006
Location: Auckland, New Zealand

Junior Member

Hi

Can someone please let me know if the size on the initial extent for a table can affect the performance of a table when doing table inserts of millions of rows. The current initial extent is 64k.

Regards

Rajesh

Report message to a moderator

Re: Table extents [message #364287 is a reply to message #364285]

Sun, 07 December 2008 20:01

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

There is a small overhead whenever you exceed your available space and a new extent is added. If you insert millions of rows with an extent size of 64Kb, then this overhead will happen more frequently.

More importantly, if a table has hundreds of extents then it takes up more space in the Data Dictionary, and therefore the dictionary cache. If your dictionary cache is too small, Oracle will page it to disk. This will make your database slower overall.

Lastly, all that extra time allocating extents when you loaded the table will happen again when you drop the table and Oracle releases the extents.

Why not just place the table in the approariate tablespace with an extentsize that relects its volume?

Ross Leishman

Report message to a moderator

Re: Table extents [message #364293 is a reply to message #364287]

Sun, 07 December 2008 20:43

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

After a small volley of PM's with @anacedent, I concede that some or all of the above may or may not be partially inaccurate... Wink

Let's assume that we are talking about Locally Managed Tablespaces. Certainly LMT's track ALLOCATED SPACE locally within the tablespace. What is not clear is where their "existence" is stored. Is it in the Data Dictionary, if so, is that part of the Dictionary Cache?

I don't know. But it still remains that rational extent sizing can only be a good thing.

Ross Leishman

Report message to a moderator

Re: Table extents [message #364315 is a reply to message #364285]

Sun, 07 December 2008 23:39

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The best option is to estimate the table size for a first period and create the table with initial of this size using a tablespace locally managed with system handled extents.

Regards
Michel

Report message to a moderator

Re: Table extents [message #364642 is a reply to message #364293]

Tue, 09 December 2008 07:13

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Where is @Anacedent - I've not seen any posts from him (or her) lately.

Report message to a moderator

Re: Table extents [message #364713 is a reply to message #364642]

Tue, 09 December 2008 15:05

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

JRowbottom wrote on Tue, 09 December 2008 05:13

Where is @Anacedent - I've not seen any posts from him (or her) lately.

Anacedent was logged in just a bit earlier today. You brought up an interesting question. Is Anacedent male or female? The profile doesn't say. When the profile doesn't say, I tend to default to male. I always thought that anacedent was a word and that Ana C. Dent was just a breakdown of that word, not a real name. But I think I had anacedent confused with antecedent. It would be nice if he or she would clarify it once and for all, preferably by just updating the profile. Not that it really matters, it's just nice to refer to somebody by their proper gender.

Report message to a moderator

Re: Table extents [message #364717 is a reply to message #364713]

Tue, 09 December 2008 15:22

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is interesting to see what points are important for each one to refer to other. dr.s.raghunathan in another post asked for our picture. I don't care about it but would like geographical location, not address but latitude/longitude to see the environment. For you, it is gender and for others this is age (birthdate), occupation or interests (to mention profile fields)...
Maybe the subject for a poll. Smile

Regards
Michel

Report message to a moderator

Previous Topic:	Problems with with-statement
Next Topic:	Keep buffer pool after restart of the server

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 17:55:24 CST 2024