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
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.
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
[EMAIL PROTECTED]
214.954.1781
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Jonathan
> Lewis
> Sent: Saturday, May 04, 2002 4:23 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Reverse Key Index Performance
>
>
>
> If this is Oracle 8.1, it is possible for the optimizer
> to reject even a primary key index as too expensive
> once it has been reversed. Did you check the execution
> path (and I/O characteristics if necessary) to see if the
> index was still being used.
>
> I haven't been able to emulate the problem in 9.0 yet.
>
>
> 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
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Larry Elkins
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
