RE: Reverse Key Index Performance

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
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]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: elkinsl_at_flash.net

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).