Home » RDBMS Server » Performance Tuning » Table extents (10g ver 10.2.0.3.0)
Table extents [message #364285] Sun, 07 December 2008 19:36 Go to next message
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
Re: Table extents [message #364287 is a reply to message #364285] Sun, 07 December 2008 20:01 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Table extents [message #364293 is a reply to message #364287] Sun, 07 December 2008 20:43 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Table extents [message #364315 is a reply to message #364285] Sun, 07 December 2008 23:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Table extents [message #364642 is a reply to message #364293] Tue, 09 December 2008 07:13 Go to previous messageGo to next message
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.
Re: Table extents [message #364713 is a reply to message #364642] Tue, 09 December 2008 15:05 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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.

Re: Table extents [message #364717 is a reply to message #364713] Tue, 09 December 2008 15:22 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: Problems with with-statement
Next Topic: Keep buffer pool after restart of the server
Goto Forum:
  


Current Time: Sat Dec 10 01:35:36 CST 2016

Total time taken to generate the page: 0.13327 seconds