EMERGENCY: E_OP0002 optimizer run out of memory
Nick Dobrovolski
We've started using a new version of our software to the production
system. It has been completely tested on test server before. And now on
some selects we recieve 'E_OP0002 optimizer run out of memory before
generating execution plan'. If I delete some conditions from where
clause then all goes fine. I can not find what parameter should I
increase in Ingres configuration on production server. Please, help!!!
Our system is stopped now.
Thanks in advance,
Nick
P.S. We are using OI 1.2/00 (rmx.us5/01) with patch 3953.
Martin Bowes
In CBF.
Enter the Configure DBMS Server Definition screen and select the Derived
parameters option. You need to alter up the 'opf_memory' or reduce
opf_active_limit.
eg. I normally run with opf_memory=9000000 and opf_active_limit=5
This is suitable for my installations yours of course may vary wildly form
this.
Having altered these parameters, you will have to restart the servers.
Martin Bowes
--
Random Duckman Quote #20:
Fluffy - Mr. Duckman your carton of Itch-away Rectal Cream just
arrived and your test results are here. The doctor hasn't
identified your lesions but he's ruled out cold sores!
Graves, Paul
We have seen this occasionally on our system here at EDS Electronic
Commerce.
Our experience was -
1) Two servers were running without shared cache - setting shared cache
helped a lot.
2) We upgraded to the latest patch.
3) Check the SQL - we had some SQL with cart products that worked ok in test
on small datasets, but caused big problems when running against the much
bigger
production database. This was the most consistant problem.
4) Check again that the database is being optimised and sysmoded ok - we
found
a bug ( sorry, feature ) in our weekly script.
5) We found that Increasing the optimizer memory made little difference
compared
to the above points.
We are using OpenIngres 1.2/01 (hp8.us5/00) on HP-UX 10.20
Good luck
Paul Graves
Delevopment Team Leader
EDS Electronic Commece
Cheadle, Cheshire, UK
Lawrence Choy @ Snap
Normally, these errors caused by too many OR or AND clauses. Like what
Martin said, increase the opf.memory parameter via CBF. Another thing to
note is that: OI 1.2/00 is too buggy, try move to OI 1.2/01 with the
appropriate patches.
Ciao,
Lawrence Choy
Ingres + Internet Consultant (Malaysia)
Alternate Address:
lsc...@email.com
________________________________
----- Original Message -----
From: Nick Dobrovolski <ni...@mcd.ru>
To: Ingres Conference <info-...@ams.org>
Sent: Wednesday, May 19, 1999 05:42
Subject: EMERGENCY: E_OP0002 optimizer run out of memory
: Hello,
:
:
Nick Dobrovolski
Many thanks to all of you, especially to Martin Bowes. He gave me the magic
figures. :-))) Before sending this letter I of course played with opf_memory in
cbf. I increased it 2, 4, 8 times but the query still failed. After setting both
parameters to suggested values all went fine. As I can see OI uses 200 Kb of
optimizer memory per session (opf_memory=6400000 and opf_active_limit=32). While
setting it to 1.8 Mb all goes fine. As I can see I missed only one step while
doubling opf_memory in my previous investigations (51200000/32=1600000). :-)))
There was lack of only 200Kb per session. Bewitched number. :-)))
Again thanks to all of you.
Regards,
Nick
Alan Brown
Nick.
In OI1.2 go to CBF DBMS Server; [derived] increase your opf_memory. Don't forget to protect the new value.
Hope this helps.
Cheers,
Alan
>>> Nick Dobrovolski <ni...@mcd.ru> 19/05/99 07:42pm >>>
Hello,
We've started using a new version of our software to the production
system. It has been completely tested on test server before. And now on
some selects we recieve 'E_OP0002 optimizer run out of memory before
generating execution plan'. If I delete some conditions from where
clause then all goes fine. I can not find what parameter should I
increase in Ingres configuration on production server. Please, help!!!
Our system is stopped now.
Thanks in advance,
Nick
P.S. We are using OI 1.2/00 (rmx.us5/01) with patch 3953.
!