RE: Performance and extents -again

RE: Performance and extents -again

 

  

Thanks Derek, John, and all who have answered.
The sum of the info and opinions I'm getting from all of the people here is
giving me an understanding of this subject that is getting better and better.
Derek, don't worry about the "teacher tone", I'm asking because I want to
learn so you guys are my teachers here.

Regards,

--Claudio


On Wed, 1 Nov 2006 12:11:11 -0500, Derek Otten wrote
> 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
>
> ---------------------------------------------------------------------
> 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