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
!!!
Ramon E. Estevez
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED]
809-565-3121
-Mensaje original-
De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Thanh-truc
Nguyen
Enviado el: Monday, 01 October, 2001 5:35 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Extents size
PROTECTED]]En nombre de Thanh-truc
Nguyen
Enviado el: Monday, 01 October, 2001 5:35 PM
Para: Multiple recipients of list ORACLE-L
Asunto: Extents size.
Hello,
I'll do an reorganization of a database (about 140 gigs). Some people say
that it'd be good to use 128K, 4M and 128M extents. I saw
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
Back in the V6 days it was a desired characteristic to have every thing in the
first extent of an object for performance reasons. Thankfully those days are
gone and it really does not matter how many extents there are. Rachel has a
presentation on Oracle Myths where she actually portrays having
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
something along those lines...
as with everything there is no real black or white on this... but
transactional systems that do a lot of small (non-sequential) reads you
are better off with lots of extents while data warehouses are often
better off with fewer extents as the reads tend to be
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:
I would content that the moment the extent size is
greater than SSTIOMAX (or whatever the OS can
service), then you won't see any benefit from fewer
extents even on data warehouse - the only exception I
can think is if you were lucky enough to be the only
user on a particular disk/volume during
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
Hello,
I'll do an reorganization of a database (about 140 gigs). Some people say
that it'd be good to use 128K, 4M and 128M extents. I saw somewhere it'd be
160K, 4M and 160M. Which size do you advice me ? I have also many small
indexes (less than 16K).
Regards,
Thanh-truc Nguyen
--
Please
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
37 matches
Mail list logo
