Re: Oracle v7.3.2 Benchmark Tests

Darren Darnell wrote:

> digging though the documentation, and then changed COMPATIBILITY and
> SORT_DIRECT_WRITES. Changing compatibility to 7.3.2 had no effect.
> Changing SORT_DIRECT_WRITES (which default to FALSE on v7.2, and AUTO on
> v7.3) caused a further decrease in performance using RULE.
>
> It was assumed that v7.3.2 would perform better than v7.1.4 on the same
> database with the same initialization parameters. It did not, and I was
> wondering if there was somthing I missed.

I've seen your later post, with execution plans for test 7, and I'm thinking about it. You might like to look at the underlying trace file, and see what recursive SQL takes place (particularly fet$, uet$, ts$) between the PARSE# step and the first FETCH# step.

In the mean time:
It is rather hard to say ' running 7.3.2 with the same init.ora parameters', as the number of new parameters is rather large. A very simple explanation for performance dropping as you take advantage of SORT_DIRECT_WRITES is that the buffers for sort_direct_writes come out of the sort_area_size, so the decrease performance could be the result of sorts being pushed to disk that were originally in memory.

Also related to the execution plans of your next post, but irrelevant to the problems of difference in performance between 7.1.4 and 7.3.2: a) If you re-order the sort path so that customers are visited before accounts

    (assuming you have an index starting with cust_no on accounts) you should      better performance.

b) If you go cost based, you could eliminate the sub-query to quotes, and use

    an inline view and the INDEX_DESC hint in it to select just the one relevant     row in the QUOTES table for each account.

---
Jonathan Lewis
ora_mail_at_jlcomp.demon.co.uk