Claudio,
1. Use: EXTENT MANAGEMENT LOCAL UNIFORM SEGMENT SPACE MANAGEMENT AUTO
wherever you can as it performs well in 91/10g, especially if you are using
RAC.
2. The number of extents is largely irrelevant. The exceptions are for full
table scans (FTS) where large numbers of extents (especially where lots of
data has been deleted) will make it take a bit longer, and also when you
come to drop the table, where it can take Oracle some time to clean up all
the dropped extents. Index scan operations may also slow if there are lots
of extents. Index/row access is unaffected by number of extents.
3. Set the extent size in a tablespace dependant on the row length and
number of records in the tables so it does not extend too often.
4. Depends on whet you are trying to do and data volumes. Alter table ...
move tablespace... followed by the mandatory alter index ...rebuild...
commands should do it. Don't forget to rebuild all the indexes after moving
a table as they will not be usable otherwise.
John.
-----Original Message-----
From: Claudio Alonso -Oracle DBA
[mailto:oracledba-ezmlmshield-x57222683.[Email address protected]
Sent: 01 November 2006 15:16
To: LazyDBA Discussion
Subject: Performance and extents -again
I'm sorry to insist...
Please, are there any answers for these questions?
Thanks again,
--Claudio
1) I would like to know if you recommend to use uniform extent sizes or auto
allocation for this (I'm not familiar with the last one, which seems to be
used in this installation).
2) Do you consider that a few bigger extents (with size multiple of the
multiblock read size) would lead to better query performance than a lot of
small extents?
3) Would it be good to use a tablespace with bigger block size for the
bigger
tables? (let's say 16k, 32k or 64k against the original 8k).
4) What would be the recommended way to move a table and it's indexes and
lobs
from a group of tablespaces to another group of tablespaces (let's say from
DATA8K, INDEX8K and LOBS8K to DATA32K, INDEX32K and LOBS32K)
--Claudio
PS: Remember I'm using 9i.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
-----------------------------------------
Information in this email including any attachments may be
privileged, confidential and is intended exclusively for the
addressee. The views expressed may not be official policy, but the
personal views of the originator. If you have received it in error,
please notify the sender by return e-mail and delete it from your
system. You should not reproduce, distribute, store, retransmit,
use or disclose its contents to anyone.
Please note we reserve the right to monitor all e-mail
communication through our internal and external networks.
SKY and the SKY marks are trade marks of British Sky Broadcasting
Group plc and are used under licence. British Sky Broadcasting
Limited (Registration No. 2906991), Sky Interactive Limited
(Registration No. 3554332), Sky-In-Home Service Limited
(Registration No. 2067075) and Sky Subscribers Services Limited
(Registration No. 2340150) are direct or indirect subsidiaries of
British Sky Broadcasting Group plc (Registration No. 2247735). All
of the companies mentioned in this paragraph are incorporated in
England and Wales and share the same registered office at Grant
Way, Isleworth, Middlesex TW7 5QD.
Oracle LazyDBA home page