Home » RDBMS Server » Performance Tuning » Row chaining and Local Tablespace
Row chaining and Local Tablespace [message #132128] Wed, 10 August 2005 22:51 Go to next message
rkl1
Messages: 97
Registered: June 2005
Member
Hi Guys:

Is row chaining is still a problem under the locally managed tablespace (LMT). I was told or now assumed that, once the tablespace is under LMT, then no need to worry about fragmentation or row chaining.

And again, when we make a table script, is there a need to put the percent free, percent used for tables (under LMT).If I don't, then it puts percent free 10% and percent used 40%. Is this the default. Is there a way to change it under LMT so I dont have to specifically mention every time I create a new table.

thanks.
Re: Row chaining and Local Tablespace [message #132217 is a reply to message #132128] Thu, 11 August 2005 07:00 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10702
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
LMT with uniform extents will completely eliminate fragmentation.
The pctfree, pctused can be left to default ( unless you need a specific case).
And there is really no need to specicy storage clauses inisde table DDL. Just leave it to default the tablespace settings.

[Updated on: Thu, 11 August 2005 07:01]

Report message to a moderator

Re: Row chaining and Local Tablespace [message #132281 is a reply to message #132128] Thu, 11 August 2005 13:39 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
And just to add, yes, row chaining can still happen in LMT. If a row is bigger than the block, it must, by definition, be chained.
Re: Row chaining and Local Tablespace [message #132654 is a reply to message #132128] Mon, 15 August 2005 09:14 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Also, row migration can also still happen with a LMT, it is not the case that you should just leave the settings to the defaults of 10% pctfree and 40% pctused.

PCTFREE
Take the example of a standard 8k block size, we load 500,000 rows into the table, and can fit 200 rows per block. That makes 2500 blocks.

If our row never get's updated then 10% of 2500 8k blocks is just fresh air!! now I know that only adds up to about 1.9M but we could have had 256 less blocks in the table, that makes index and especially full tablescans more efficient.

On the opposite scale, this could be a heavily updated row, which means that in practice many many rows may not be in their original blocks, that in turn means that index lookups will be less efficient.

This is not a trivial matter especially with large volumes of data and you shouldn't be complacent about it.

Now the good news, if your in Oracle9i and you use Locally Managed Tablespaces, you can use Automatic Segment Space Management (ASSM) this puts the management of how full or empty blocks are over to Oracle, no need to set PCTFREE (PCTUSED can still be set).

Post back if you want more clarification.

Allie
Re: Row chaining and Local Tablespace [message #132656 is a reply to message #132128] Mon, 15 August 2005 09:36 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
One minor thing, I think it is pctused that can be ignored and pctfree that is still used.
Re: Row chaining and Local Tablespace [message #132659 is a reply to message #132656] Mon, 15 August 2005 09:44 Go to previous messageGo to next message
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
Member
Quote:

One minor thing, I think it is pctused that can be ignored and pctfree that is still used.


Doh... hits head....

Thanks for pointing that out.

A
Re: Row chaining and Local Tablespace [message #132666 is a reply to message #132128] Mon, 15 August 2005 10:21 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Not a problem, thanks for pointing out in another thread that the index_stats view information from validate structure is lost after you logout.
Previous Topic: Performance problem
Next Topic: Oracle 8.1.6 Solaris 7 - redo log group switch
Goto Forum:
  


Current Time: Fri May 27 16:57:04 CDT 2022