Derek,

Question based on one of your comments below .... Indexes vs table
scans.

Have an extremely large table (over 160 million rows). It's an audit
table tracking changes to other data in system (call them assignments).

Each assignment has a genesis record in the audit table. As changes are
made new children are added...so, groups grow off their associated audit
genesis record. Records are not updated, just inserted. From a
functional perspective a user (mainly adminstrator - me) would only
query audit trail to track a given issue...this is done very
infrequently.

Genral performance appears to be greatly impacted by this audit table
(remainder of system has less than 10 million rows across all tables.)
Believe issue is that given transaction must find genesis record to
build/insert next child record. Genesis records are created whenever new
assignment is generated.

I've defined indexes against the audit table.

Question is; does the existance of the index create more harm than good
due to index management (some additional info is that it takes ~14hours
to analyze this audit table and less than 30 minutes to analyze all
other tables combined.)

Background - have inherited db from a vendor supplied product which is
EJB based and db management has been handed off to EJB...so, there are
some outrageous joins going on. Looking into partitioning the audit
table but have done that yet.

Any feedback is appreciated.

- Mark




-----Original Message-----
From: Derek Otten
Sent: Wednesday, November 01, 2006 12:11 PM
To: LazyDBA Discussion
Subject: RE: Performance and extents -again



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.




DISCLAIMER:
This e-mail is for the intended recipient only.
If you have received it by mistake please let us know by reply and then delete it from your system; access, disclosure, copying, distribution or reliance on any of it by anyone else is prohibited.
If you as intended recipient have received this e-mail incorrectly, please notify the sender (via e-mail) immediately.

Oracle LazyDBA home page