1) No, you can't compress into a single extent if the TBS is
LMT with uniform extent size, and the size of the index is
extent size. Don't know about auto extent size feature.
2) Why would you want to compress into a single extent?
There's no benefit to doing so.
Jared
Guang Mei [EMAIL
Tim,
The tablespace is dictionary managed.
--- Tim Gorman [EMAIL PROTECTED] wrote:
I was hoping to see * column values from
DBA_TABLESPACES, not just
the
default storage column values. This would show
whether the
tablespace in
question was locally-managed (and SYSTEM or UNIFORM,
if so) as
Well! I'm out of ideas. The only other thing I can think of is a recent
ALTER TABLE which changed the INITIAL on the table since the load, but
that's grasping (gasping?). Still, could you look at LAST_DDL_TIME on
DBA_OBJECTS for the table, just to grasp that last straw?
- Original Message
Tim,
Thanks for all your help. I will check the
LAST_DDL_TIME field (although I didn't know what
INITIAL parameter can be modified) and will let you
know if something comes out of it.
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
Well! I'm out of ideas. The only other thing I can
think of is
SQL*Loader in direct-parallel mode (i.e. DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY segment. After the load completes, then the
TEMPORARY segment is merged with the table segment. All of the direct
(a.k.a. append) operations work this way when executed in parallel (i.e.
INSERT /*+
Tim,
Thanks for the explanation. The table I'm looking at
has an initial size 32K while the new extents are all
16K. Why would this happen?
thanks
Gene
--- Tim Gorman [EMAIL PROTECTED] wrote:
SQL*Loader in direct-parallel mode (i.e.
DIRECT=TRUE, PARALLEL=TRUE) first
loads into a TEMPORARY
What does SELECT * FROM DBA_TABLESPACES show for the tablespace involved?
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Tuesday, May 14, 2002 12:53 PM
Tim,
Thanks for the explanation. The table I'm looking at
has an initial size 32K while
Tim,
SQL select TABLESPACE_NAME, INITIAL_EXTENT/1024,
NEXT_EXTENT/1024, PCT_INCREASE
2 from dba_tablespaces
3 where tablespace_name like 'TEMP%'
4 or tablespace_name = 'TREPD01';
TABLESPACE_NAMEINITIAL_EXTENT/1024
NEXT_EXTENT/1024 PCT_INCREASE
I was hoping to see * column values from DBA_TABLESPACES, not just the
default storage column values. This would show whether the tablespace in
question was locally-managed (and SYSTEM or UNIFORM, if so) as well...
You don't need to include the temporary tablespace -- it's not relevant.
size and round DOWN)
Huh?
Jared
Rachel Carmichael [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/04/02 12:13 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: EXTENTS?
nick
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: EXTENTS?
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the
blocksize
then you can have more than one extent in a block (divide
Title: RE: EXTENTS?
it´s the other way around... how many db blocks in an extent.
an extent is a number of contiguous data blocks ...
-Mensaje original-
De: Seema Singh [mailto:[EMAIL PROTECTED]]
Enviado el: Lunes, 04 de Marzo de 2002 15:19
Para: Multiple recipients of list ORACLE
Depends on the size of the extent.
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Monday, March 04, 2002 1:18 PM
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
Depends on your extent size
-Original Message-
Sent: Monday, March 04, 2002 12:19 PM
To: Multiple recipients of list ORACLE-L
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
-Seema
_
Join
you have it backwards... extents don't fit into blocks, blocks fit into
extents
and extent size is dependent on tablespace storage parameters and
objectg storage parameters
--- Seema Singh [EMAIL PROTECTED] wrote:
Hi
If DB block size is 8k then how many extents in one db block?
Thanks
Title: RE: EXTENTS?
I'm going to say 1. I don't think you can have multiple tables in the same DB block.
-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 10:19 AM
To: Multiple recipients of list ORACLE-L
Subject: EXTENTS?
Hi
If DB
None.
The lowest level of granularity of storage in Oracle is a block.
block-extent-segment
Time to hit the books, the Concepts manual in this case.
Jared
Seema Singh [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/04/02 10:18 AM
Please respond to ORACLE-L
To: Multiple
Title: RE: EXTENTS?
The Server Concepts manual Chapter on Data Blocks, Extents, and Segments would be a helpful Chapter to read.
-Original Message-
From: Kevin Lange [mailto:[EMAIL PROTECTED]]
Sent: Monday, March 04, 2002 1:28 PM
To: Multiple recipients of list ORACLE-L
Subject: RE
Isn't the minimum extent size something like 5 times the DB_BLOCK_SIZE?
If that's true then maximum .2 extents in DB block.
Or you are looking for this info?
db_block_size Max Extents
-- ---
2K121
4K
Seema,
You got it backwards.
If Db Block Size is 8k, and you create a table with it's initial extent of
32k, the you have 4 db blocks making up the extent.
An Extent is composed of one or more Db Blocks. A Db Block is defined when
you create the db (although I heard that different tablespaces
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the blocksize
then you can have more than one extent in a block (divide the block
size by the extent size and round DOWN)
in general though, extents are not usually sized as small as the
database
[EMAIL PROTECTED] wrote:
in general though, extents are not usually sized as small as the
database block size
should extents be multiples of block size?
--
--
Bill Shrek Thater ORACLE DBA
[EMAIL PROTECTED]
:
YAHOO.COM Subject: RE: EXTENTS?
Sent by:
root@fatcity
Yes, this one will get a lot of traffic, I'll bet.
I just looked in the 8.1.7 docs (SQL Reference - Storage Clause) and it says
that the minimum INITIAL is 2 DB blocks for non-bitmapped segments, 3 for
bitmapped segments. The minimum for NEXT is 1 DB block.
Unlesss it's a new 9i feature, a DB
to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: EXTENTS?
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the blocksize
then you can have more than one extent in a block
you create the db (although I heard that different tablespaces can
have
different Db Block sizes in 9i - correct anybody?)
yep I've even tested it :)
--- Mercadante, Thomas F [EMAIL PROTECTED] wrote:
Seema,
You got it backwards.
If Db Block Size is 8k, and you create a table with
ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: EXTENTS?
nick, you're thinking segments not extents.
and I was off as well, if your extent size is less than the blocksize
then you can have more than one extent in a block (divide the block
size by the extent size
The best documentation (and I use the term very
loosely) is:
1) SQL.BSQ ($ORACLE_HOME/rdbms/admin)
2) select view_name, text
from dba_views
where view_name like 'DBA%'
3) select * from v$fixed_view_definition
hth
connor
--- [EMAIL PROTECTED] wrote: This thread
has been very interesting
I have put together a summary of this thread as a tip on my website:
http://www.vampired.net/forums/viewtopic.php?topic=47forum=150
For those who are interested in this.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and
LMT's have their own problems as well though.
Using LMT's, the bitmap on the tablespace is only used to manage free space,
used space is in the segment header of the segment which represents the
extents. Therefore, to do a query of DBA_EXTENTS you can hit all segment
headers (of all tables and
Christopher,
Is the guideline 505 extents for the tablespace or each table in the tablespace?
Tom
Tom Terrian
Oracle DBA
WPAFB - DAASC
[EMAIL PROTECTED]
937-656-3844
-Original Message-
Sent: Wednesday, October 03, 2001 10:05 AM
To: Multiple recipients of list ORACLE-L
LMT's have
Does the 505 extent limit apply to the whole of a partitioned table
or to the number of extents per partition?
For example if I had a table wth 371 partitions (53 weeks per year *
7 years) to hold invoice data for tax purposes, do the number of
extents per partition need to be kept at 1 to avoid
It depends on block size, but yes for each segment, which is for each index,
table, partition, etc.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence
Oracle DBA
Phone: (978)
See post which is similar, but per segment, each partition in a partition
table is a different segment.
Do not criticize someone until you walked a mile in their shoes, that way
when you criticize them, you are a mile a way and have their shoes.
Christopher R. Spence
Oracle DBA
Phone: (978)
This is very noticeable when you are debugging a problem and are seeing scattered
read events. The query against dba_EXTENTS based on file_id, block_id and owner
can take an inordinate amount of time.
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original
I did the same reorganization two weeks ago, not the same amount of data,
but
I used 128k for index tablespaces, 4m for the most activity TS (Sales) and
5160k for the others.
I am very, very, very happy with the performance.
I read two articles that some folks from the list sent me.
Good
Ramon,
Why use an extent size of 5.16 meg when you are
already using 4 meg?
Jared
On Tuesday 02 October 2001 07:40, Ramon Estevez wrote:
I did the same reorganization two weeks ago, not the same amount of data,
but
I used 128k for index tablespaces, 4m for the most activity TS (Sales)
That is completely a myth. There is no notable performance different with a
table with 10,000 extents and one with 1.
The only problem is when it comes to the bitmaps when dealing with LMT and
cluster when dealing with dictionary managed. When you query the extent
views, or do space
well, only when you are deleting massive amounts of data or truncating
with drop storage.. then there is an impact because of the hits on the
dictionary tables.
but basically yes... I've been told by various Oracle employees that up
to 4096 extents cause no problem whatsoever.
--- Christopher
Yea - I keep hearing and seeing tests that show that the number of extents
had no bearing on performance (up to a point). It just 'bothers' me to see
a 500 or 1200 or 2000 extents on a table.. grin
Here is a question - is there any situation that having only 1 big extent
would reduce
by: Subject: Re: Extents size.
[EMAIL PROTECTED]
om
Any DDL like drop table and truncate table definately take longer with
10,000 extents than 1 extent. Try it. There was a test result 1 year back by
a list member on that.
Regards
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date:
Yes, that is very true. That is why I mentioned the performance will be
noticeable at times when dealing with the extent bitmaps for LMT and the
dictionary for dictionary managed, wither it be ad-hoc or recursive sql.
Unless your doing a lot of truncates or drops, I would bear performance
Title: RE: Extents size.
Why is that? And would that only count for an object in a dictionary managed tablespace? Would the time/speed it takes for drops and truncates really matter as far as performance is concerned? What I mean is who would set storage specs for objects with the speed
True - but we're all using LMT's now anyway aren't we
?
:-)
--- Rachel Carmichael [EMAIL PROTECTED] wrote:
well, only when you are deleting massive amounts of
data or truncating
with drop storage.. then there is an impact because
of the hits on the
dictionary tables.
but basically
Thanks.You are right
MOHAMMAD RAFIQ
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Date: Tue, 02 Oct 2001 13:10:30 -0800
Yes, that is very true. That is why I mentioned the performance will be
noticeable at times when dealing with the extent
You could probably mount a argument about more than
505 extents (for an 8k block) not being able to fit in
the segment header block - but unless you're
clobbering dba_extents and the like with queries, I
doubt you'd ever see a difference.
hth
connor
--- Steve Smith [EMAIL PROTECTED] wrote:
Further to my previous message, I am reproudcing an email of a list member
on this subject. It is really interesting to read it..
Reply-To: Ferenc Mantfeld [EMAIL PROTECTED]
To: oracle list [EMAIL PROTECTED]
Date: Tue, 20 Jul 1999 16:25:15 -0500
Hi all
We know that Oracle often gives us
Think of something like a temporary load table. If you have a large load
process that is generating thousands of extents the clearing of the temp
table before the loads will kill you. During the truncate or delete SMON
clears all the extent info out of SYS.UET$ and adds them to SYS.FET$. With
Its not a problem with LMT's - thats for sure:
SQL create table blah ( x number) tablespace users
storage ( minextents 1000 );
Table created.
SQL select count(*) from dba_extents where
segment_name = 'BLAH';
COUNT(*)
--
1000
SQL set timing on
SQL drop table blah;
Table
As far as DDL is concerned ,Yes. I have seen Dataware House application(not
a good design) that dropping/truncating tables with lot of extents takes
longer time because of extent management. Such code must take into account
no of extents of such objects. If those objects are created with
Title: RE: Extents size.
This thread has been very interesting and causing me to learn and go read some more. Which leads me to my next question. Does anyone have any good papers or urls that will discuss and explain all of the base data dictionary tables: fet$, uet$, etc.? Or anyone have
Multiple extents a good thing? YES!
I'm *depending* on many multiple extents of an interMedia index segment (the
DR$$I segment) to distribute I/O for full text indexing and queries. I
plan to distribute the datafiles of the tablespace holding the DR$$I
segment across multiple drives and set
Title: RE: Extents size.
There might be a document somewhere that explains them, but the way I've learned about those tables is to look at the source code for the dba_ views. Go through those views one by one, and look at the tables behind the views.
-Original Message-
From: [EMAIL
First I've heard from Ferenc for awhile, even if
it is 2 years old.
Using extreme cases like this really doesn't make
for a good example. I know an instructor that
dropped/recreated/imported and entire database
because the 'drop table' ran for 2 days with no
end in sight.
Extents were
Of course, if you're using LMT's, this exercise is a waste
of time. :)
that's because they learned to stop defragmenting and start living :)
paul
Jared Still wrote:
First I've heard from Ferenc for awhile, even if
it is 2 years old.
Using extreme cases like this really doesn't make
How to Stop Defragmenting and Start Living can be found at:
http://technet.oracle.com/deploy/availability/pdf/defrag.pdf
-Original Message-
Sent: Monday, October 01, 2001 5:35 PM
To: Multiple recipients of list ORACLE-L
Hello,
I'll do an reorganization of a database (about 140 gigs).
Thanh - I believe the 128k / 4m / 128m comes from the paper Stop
defragmenting and start living (I hope I am recalling the name of the
article correctly), which is posted on Oracle's Web site. It references the
fact that it bases those sizes on Oracle8i, and mentions that Oracle is
recommending
May be it is good practice to keep number of extents to be less than 50, no
matter what the size of extent.
-Original Message-
Sent: Monday, October 01, 2001 3:35 PM
To: Multiple recipients of list ORACLE-L
Hello,
I'll do an reorganization of a database (about 140 gigs). Some people
To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
Micro.com cc:
Sent by:Subject: RE: Extents size
60 matches
Mail list logo
