Re: Reverse Key Index Performance

Larry,

|control all other factors. And I will not have the chance to do so.
As far
|as they are concerned, the production problem is resolved. So,
there's no
|need to more thoroughly investigate this -- let's move on to other
pressing
|matters. I'd like to have more details, but it's hard to justify
spending
|more time on it.

Sometimes it's a pity that a problem can be resolved without being understood, but that's the real world.

A couple of thoughts (for next time).

It would be useful to see the execution plans (particularly to see the plan dumped in the trace files just in case the theoretical plan was not the same as the actual plan). Also the full EXPLAIN PLAN output to see if the estimated index access costs on the subquery varied.

One thought that could explain the discrepancy, which would be controlled by the type of query and the size of the table.

If Oracle optiimises the query by doing the DISTINCT before doing the subquery (and this is nominally a valid optimisation, depending on scale and statistics) then the EMPNOs being checked would be in empno order.

With a standard index, you would get 100% buffering of index blocks when doing the subquery - with the reverse key, you COULD get 0% buffering on the leaf blocks. It tallies with the timing - does it tally with the execution path ?

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 04 May 2002 23:52

|Jonathan,
|
|Absolutely, the index was being used whether reverse key or not.
Surrogate
|key defined as RKI using direct inserts and a sequence for populating
the
|key (no caching on the sequence). Environment, 64 bit 8.1.7.3 Solaris
2.7
|EMC Symmetrix (raw).
|
|The query was a correlated NOT IN generated by Oracle's replication
process
|for a primary key fast refresh, just like the following except with
real
|table names instead of EMP:
|
|SELECT
| DISTINCT LOG$."EMPNO"
|FROM
| (SELECT MLOG$."EMPNO"
| FROM "SCOTT"."MLOG$_EMP" MLOG$
| WHERE "SNAPTIME$$" > :1 AND
| ("DMLTYPE$$" != 'I')) LOG$
|WHERE (LOG$."EMPNO") NOT IN (SELECT MAS_TAB$."EMPNO"
| FROM "EMP" "MAS_TAB$"
| WHERE LOG$."EMPNO" = MAS_TAB$."EMPNO")
|
|So, for every row in the MLOG$ table, a unique index lookup would be
|performed on the PK of the table being replicated, EMP_PK in the
example
|above. And this was verified by tracing the session and examining the
plan.
|And in my testing of just the query, I would also verify the plan. In
the
|real world case, MLOG$ will vary between 500,000 and 5 million rows a
day,
|just depends on the loads done that day. The table on which the
snapshot is
|created is around 250 - 275 million rows, I'm thinking 30-40 gig
total size
|(I'm not at work, can't verify) with the reverse key PK a few gig.
|
|So, when doing a 10046 trace with waits, saw big time waits on db
file
|sequential reads. Ok, so possible I/O contention, maybe a hot disk,
|saturated switch, whatever. But, they don't have the tools to dig
into the
|black box called EMC to see if we had hot disks. And the SA's don't
have
|anything (they are working on it) that map things out. With the
striping
|that was done, who knows what else might reside on those same disks
that
|could be causing contention. But from a fiber and switch standpoint,
they
|have never seen any saturation issues with everything working well
below
|peak capacity.
|
|But, I did note that of all the tables being replicated, and many
pushing 1
|to 5 million rows a day, sometimes much more, the only two that were
|experiencing performance issues in the past were those with reverse
key
|PK's. So, decided to test RKI's against regular B-Trees. I created a
copy of
|the 250 million row table, and created the snapshot log. And it's
hard to
|say exactly how it ends up getting laid out on the disks -- working
with the
|SA's and production DBA's on that. I then created the reverse key
index. I
|generated 1 million inserts and 1 million updates, giving me 2
million rows
|in my personal MLOG$ table.
|
|Ran the query, it ran for a while (killed it after 1.5 hours). Once
again
|seeing severe waits on db file sequential reads. Dropped the RKI and
|created, using the same TS, as a B-TREE. Query finished in 6 minutes.
And
|this is what I was talking about earlier and having an
"un-controlled"
|environment. Just because I create the RKI, and then the b-tree, in
the same
|TS, the way the data actually got placed on the physical disks could
vary
|greatly and at this time they don't have the tools to investigate it.
So I
|repeated the process, going back to a RKI, ran the query, killed it
after
|1.5 hours, once again seeing waits on sequential reads. Dropped the
RKI and
|created again as a b-tree, 6 minutes for the query. I also tested the
|queries using the MLOG$ table from the real table so that I could
test a
|more random set of updates typical of the real world.
|
|Now, the knee-jerk reaction is the RKI's were the problem. But there
has to
|be more at work here. And there are obvious things like the
possibility of
|benefiting from a good clustering factor when using the b-tree and
for the
|rows that were updated, unless the updates were truly random against
the
|underlying rows. You would think I might have to visit a lot more
blocks
|when using the RKI. Plus, this wasn't really an environment where I
could
|
|So that's it. I'm hesitant to say this was simply a RKI issue since
there
|were so many other things out of my control. And there are lot's of
things
|I'm kicking around in my mind. But switching back and forth between
RKI and
|b-tree, the results were consistent -- bad performance, then good
|performance, seeing the sequential read waits with the RKI. The RKI
on the
|real table was rebuilt as a b-tree, and I have yet to hear of any
problems.
|But like I said, without a controlled environment and
measuring/monitoring
|all aspects of the hardware, OS, DB, and activity, I don't think I
can
|safely draw that conclusion. You know, like telling a frog with no
legs to
|jump and then writing in your journal that frogs with no legs are
deaf ;-) I
|don't want to be that guy ;-)
|
|So, that's why I ask what other people have seen and about their
experiences
|and testing. And sorry for the length.
|
|Regards,
|
|Larry G. Elkins
|elkinsl_at_flash.net
|214.954.1781
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).