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 <[EMAIL PROTECTED]> 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 |[EMAIL PROTECTED] |214.954.1781 | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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).
