Re: ORA-04030

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Thu, 20 Sep 2001 09:17:53 +0200
Message-ID: <9oepn7$ptj$1@ctb-nnrp1.saix.net>

"Reid Lai" <reidlai_at_netvigator.com> wrote

> Did you turn on MTS in Oracle? If so, try to allocate more memory in
> large_pool_size. Otherwise, increase sort_area_size.

Nope. Not using MTS. Changing pool and sort area sizes make no difference.

It seems to me that the error is a bug of sorts. I have worked around the problem by making the join less complex by using interim working tables and then doing the final join on rowid. This means that the same data set is now processed 3 times separately (creating 2 work tables + final join), instead of only once.. but at least this works.

What still puzzles me is how this same SQL join did not fail due to memory when processing 40+ million rows (it bombed after several hours due to insufficient TEMP tablespace), but it fails after less than 5 minutes doing a small subset of only a million rows.

The only difference between the huge data set and doing the small data set, is that PQ is used for the first and indexes used for the latter. Something does not smell nice and it is not my flatulence caused by overindulging in curry again.

--
Billy

Received on Thu Sep 20 2001 - 02:17:53 CDT