Ugh....I'be tried to reply to this for over a week now and it never shows
up....trying once more....:
Without seeing any of your code, the makeup of the tables, or your
statistics returned...here are a few thoughts....bear in mind, there's
probably some fluff in my answer that you don't need since I don't have all
the detail.
Additionally, I'm an instructor. I don't say that to show off
qualifications in any way, just that I don't want to offend you if it sounds
like I'm talking down to someone who I'm sure is a very experienced DBA
1. Your question of uniform vs auto allocation:
Uniform will almost always be best. Auto Allocation is a feature
that helps DBAs not have to take a guess about that 'PCTINCREASE' setting
when the next extent must be grabbed after the first two are filled. The
thing is that while it will better manage space than UNIFORM allocation,
Oracle is going to have to scratch it's head a little and think about how
much to allocate.
In using UNIFORM, you basically tell oracle 'oh, shadddap and
allocate it, don't think.' The downside? More space used....if the object
just needs 20k and you have 1mb UNIFORM...guess what it gets????? At least
it won't have to ask for another extent for a while.
2. This sounds like a report being run, and not a query to return a few
records to a user. Therefore, your idea on the larger block size is a good
one. If a physical IO has to happen, it can get much more in one pass with
a larger block size (ok, sorry....sounding like I'm talking to a student).
BTW, feedback from DBA's in my classes and my own experience has shown me
that you can get better IO performance by sizing the multiblock read count
to be the same size as, or a multiple of, the amount of data the OS can get
into it's buffer in one read.
3. Honestly this shot out of your email first as if from a cannon.....if
all three of these tables are 500,000 records apiece, you might want to
consider partitioning them. They'd have their own tablespaces and should be
on different disks to spread out I/O. You can then have local indexes on
each of them. You don't have to even change the query, it won't have a
clue, nor will it care...BUT, you could also add a PARRALLEL hint so the
work is broken down per partition.....just a thought there (unless you
forgot to mention that was what you're doing already).
4. Are there frequent equijoins (like on parent/child keys). In that case
clustering the table(s) may help. The predicate key would be stored in one
segment, and not 2 or three resulting in one read only.
5. If any of the indexes are compound indexes (multiple columns defined in
them to increase cardinality), then your where clause predicates should
favor using the 'leading' column in that index. Basically, the first column
listed in the compound index. If the predicates use the 2nd, 3rd, nth
column, they won't perform as well.
In your statistics, are you collecting histograms on the predicate
columns (especially the indexed ones)? If not, here's is where an index
will actually hurt you. Example....you've got a character column that
you've indexed, lets say it's the last name....I'll just throw five names
out there, of course you've got more:
LNAME #occurences
Whoziwatzits 1
Howerton 1000
Smith 200,000
Jones 100,000
Pebkac 50,000
Now, without a histogram on the column, the optimizer assumes that there is
and equal distribution of these names and it's very likely it'll use an
index....but if it's 'Smith', or 'Jones'? It reads the index block, then
the table block, then index block, table block...etc, etc......WAY more
reads than the full table scan would have done. Smith could mean 400,000
block reads in a 350,101 row table.
Adding a histogram analysis in your stats gathering will group these suckers
into buckets, you basically set the number of buckets to be equal to the
number of unique values in the table...... so with THIS information, the
optimizer will know it can get to 'Whoziwatzits' faster using an index and
in the case of 'Smith', 'Jones', or even 'Pebkac' would favor a table scan
instead. Make sense? Indexes have often been the quick answer in my
experience and as seen here, they can actually be worse that a full table
scan. Full table scans are not the dark evil of performance all make them
out to be, there are cases where they are actually better.
6. BTW, if this is an OLTP system and those tables are getting inserted
into frequently or indexed columns are getting updated......DML is going to
suffer due to index maintenance...so be careful with indexes. Your query
users may like you, but the OLTP users may see a slowdown.
-----Original Message-----
From: Claudio Alonso -Oracle DBA
[mailto:oracledba-ezmlmshield-x56531656.[Email address protected]
Sent: Wednesday, November 01, 2006 10:56 AM
To: LazyDBA Discussion
Subject: RE: Performance and extents -again
Excellent, that was exactly the kind of answer I was expecting.
Thank you very much,
--Claudio
On Wed, 1 Nov 2006 09:36:11 -0600, Kerber Andrew wrote
> 1. Use extent management local, uniform extent sizes, segment space
> management auto for best performance.
>
> 2. Depends on size of table, small table, small extents, large
> table,
large extents. Its more a matter of wasting space than
> performance.
> 3. It matters more on indexes, wouldn't worry about it for table
> data.
> 4. Alter table move tablespace whatever.
>
> -----Original Message-----
> From: Claudio Alonso -Oracle DBA
> [mailto:oracledba-ezmlmshield-x57222683.[Email address protected]
> Sent: Wednesday, November 01, 2006 9:16 AM
> 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
>
> -----------------------------------------
> CONFIDENTIALITY NOTICE
>
> This message and any attachments are from the NAIC and are intended
> only for the addressee. Information contained herein is
> confidential, and may be privileged or exempt from disclosure
> pursuant to applicable federal or state law. This message is not
> intended as a waiver of the confidential, privileged or exempted
> status of the information transmitted. Unauthorized forwarding,
> printing, copying, distribution or use of such information is
> strictly prohibited and may be unlawful. If you are not the
> addressee, please promptly delete this message and notify the
> sender of the delivery error by e-mail or by calling the NAIC Help
> Desk at (816)783-8500.
>
> ---------------------------------------------------------------------
> 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
---------------------------------------------------------------------
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
Oracle LazyDBA home page