Re: Need a volunteer to test a simple script on HPUX 11i - Oracle 9.0.1

Thanks to those who helped, and especially to 'Bobby' from Oracle Support who pointed me in the right direction.

I had thought that the issue was related in some way to a new strategy for latching on the 'cache buffers chain' latches which could not be implemented properly by HP.

In fact the numbers I was seeing were (almost) a coincidence, and the problem was nothing to do with 'cache buffers chains' at all. (Very good news !)

The problem was related to the particular stress test that I was running, combined with the fact that the resource manager was enabled.
(resource_manage_plan=system_plan).

Under this configuration, I was (coincidentally) seeing exactly 2 gets on the "cas latch" for every get on the "cache buffers chains" latch - which was adding about 100% to the expected CPU cost of the operation. However, having
commented out this parameter (don't try
changing it dynamically, there is a known bug which may effectively hang the database) the gets on the cas latch disappeared, bringing the response time back down again.

Running the same test (with and without
resource_manager_plan) on a W2000
system produced a different effect - there was no 'cas latch' activity, instead there was a much smaller overhead (about 5%)
which was probably associated with latching on various 'resmgr%' latches.

In summary then - HP does not have the
totally catastrophic problem I thought it had, but the cost of using resource manager on HP systems could well be much larger than it is on other systems because of this 'cas latch' issue.

Even then, the stress test I used may also have been hitting a bug at a particular point in the resource manager code which exaggerates the impact of the cas latch to ridiculous levels; so I've sent the sample SQL on to Oracle so that their developers can check what's going on behind it.

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

Now running 3-day intensive seminars
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

Author of:
Practical Oracle 8i: Building Efficient Databases


Jonathan Lewis wrote in message
<1012427839.7076.0.nnrp-02.9e984b29_at_news.demon.co.uk>...


>

>Is anyone running 9.0.1 on HP-UX 11i ?

>

>If so can you tell me if it has CAS latch

>problems, or run a short test for me to

>check whether shared hash latches work

>better on 11i than they do on 11.0

>

>Thanks

>