Err 505 work space limit exceeded

forum.sqlbase (2000-2010)
NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 17 Feb 2009, 12:37

 Posted by:  G G 

Hi to all.
I get this error on a 7.5.1 engine with a strange but not huge select (it's
not mine, I inherited the program). It envolves two tables and two views and
ite extracts 800 rows. The server is a dual core with a lot of memory. I
tried to set workalloc to 5000 and worklimit to 4000 (they weren't set),
tried to high cache and sortcache from 2000 to 10000 but the problem
persists. The software that causes this was runnig yesterday, and today the
customer only added an item. On my server (I have a 9.0.1) the same select
takes a lot of time but runs. See the execution plan.
The database has been unloaded and reloaded with update statitics, it is 9
Mb, 88,000 rows. I prefer to not touch the software. Adding key will help?
Thank you.

627 ROWS SELECTED
IN 129.05 SECONDS

4.85 ROWS PER SECOND / 0.20 SECONDS PER ROW

SELECT TABLE INDEX
OPTIONS
======= ====================================
==================================== ====================================
1 PV_COMPONENTI PVCOMP_I3
1 TEMP TABLE-SEL 3
1 TEMP TABLE-SEL 2
HASH JOIN
1 PV_FORNITORI PVFORN_I1
OUTER JOIN
2 PV_COMP_PROD-SEL 7
3 PV_COMP_ORDINI-SEL 4
4 TEMP TABLE-SEL 6
4 TEMP TABLE-SEL 5
OUTER JOIN,HASH JOIN
5 PV_DETT_ORDINI
6 PV_COMPONENTI PVCOMP_I1
7 TEMP TABLE-SEL 9
7 TEMP TABLE-SEL 8
OUTER JOIN,HASH JOIN
8 PV_DETT_PROD
9 PV_COMPONENTI PVCOMP_I1

--

----------------------------------
Luigi Fantelli
Viale Grigoletti, 17
33170 PORDENONE
Tel.: 0434 365 268
Fax: 0434 1820 155
E-Mail: info@fantelli.it
WEB: www.fantelli.it
----------------------------------

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 17 Feb 2009, 23:40

 Posted by:  Mike Vandine 

Hi G G,

This was a known defect with 7.5.1 and was fixed with PTF3.

As a workaround, you might try doing an unload/load of the database.

Best regards,

Mike

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 18 Feb 2009, 07:08

 Posted by:  F R Bhote 

Maybe modifying the statement would help - can we see it please?

PV_COMP_ORDINI-SEL 4 appears to need an index and I cannot remember the same
index being used twice (PVCOMP_I1) except in a subselect. 627 out of 88,000
rows should not take more than a second or two.

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 18 Feb 2009, 09:55

 Posted by:  G G 

Of course, but ...

select
C.c_componente ,C.d_componente ,C.c_gruppo ,C.c_fornitore ,F.d_fornitore,
C.n_rimanenza ,C.n_scortamin , C.n_costo , C.sg_valuta,
prog_impegnato,prog_scaricato,
@NULLVALUE(prog_impegnato,0)-@NULLVALUE(prog_scaricato,0),
prog_ordinato,@NULLVALUE(prog_ordinato,0)-@NULLVALUE(prog_caricato,0),prog_caricato,
@NULLVALUE(prog_caricato,0)-
@NULLVALUE(prog_impegnato,0)+@NULLVALUE(n_rimanenza,0),
@NULLVALUE(prog_caricato,0)-@NULLVALUE(prog_scaricato,0)+@NULLVALUE(n_rimanenza,0)
from
PV_COMPONENTI C,PV_FORNITORI F ,PV_COMP_PROD X,PV_COMP_ORDINI Y
where
C.c_fornitore=F.c_fornitore(+)
and
X.c_componente=C.c_componente and y.c_componente=C.c_componente;

pv_componenti and pv_fornitori are normal tables with indexes on
c_componente, PV_COMP_PROD and PV_COMP_ORDINI are views done this way:

CREATE VIEW SYSADM.PV_COMP_PROD

(C_COMPONENTE,PROG_IMPEGNATO,PROG_SCARICATO)

AS

select C.c_componente ,sum(P.n_qtalanc),sum(P.n_qtaprod)

from PV_DETT_PROD P,PV_COMPONENTI C

where C.c_componente=P.c_componente(+) and (P.fg_chiuso=0 or P.fg_chiuso is
null)

GROUP BY C.C_COMPONENTE

CREATE VIEW SYSADM.PV_COMP_ORDINI

(C_COMPONENTE,PROG_ORDINATO,PROG_CARICATO)

AS

select C.c_componente ,sum(P.n_qtaord),sum(P.n_qtabolla)

from PV_DETT_ORDINI P,PV_COMPONENTI C

where C.c_componente=P.c_componente(+) and (P.fg_chiuso=0 or P.fg_chiuso is
null)

GROUP BY C.C_COMPONENTE

pv_dett_ordini and pv_dett_prod has indexes on c_componente, too. I tried to
add indexes on c_componente and fg_chiuso but nothing changes.

This statement ran till yesterday when they added one item in pv_componenti.
Tha d.b. has been unload and loaded. On the old d.b. (before yesterday) all
runs as usual.

Thanks

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 18 Feb 2009, 10:02

 Posted by:  G G 

Some place to download it from?

"

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 18 Feb 2009, 22:53

 Posted by:  Mike Vandine 

G G,

Contact your local Unify representative and they will organize this for you.

Best regards,

Mike

NewsgroupServer
Robot
Robot
Posts: 118939
Joined: 24 Feb 2017, 12:00
Location: World wide

Err 505 work space limit exceeded

Post by NewsgroupServer » 20 Feb 2009, 15:00

 Posted by:  G G 

No problem, we will migrate soon to SqlServer. Thank you anyway.

Return to “forum.sqlbase”

Who is online

Users browsing this forum: Ccbot [Crawler] and 0 guests