Hi,

I've got a problem with a query I'm running (see below) it works fine when I run it from the development database but when run on the live db (essentially the same setup) it seems to have problems trying to examine the explain plan gives a message that says 'The EXPLAIN PLAN cannot be viewed because the cached plan for this cursor is malformed '. I can't see any real differences between the servers (asides from quantity of data). I've tried googling that message but its not turning up a wealth of info, can anyone give some pointers as to a possible reason or cause for this.

I don't know if it is significant but the debtor_mv is a materialised view, these are new to me so thats the first port of call for my suspicions but I'm screwed if I can find a reason.

Any help appreciated.

SELECT min(contactid) as contactid from
(
select d.contactid
from debtor_mv d
where
d.companytype = ? and
d.invoicedate > '01-MAR-06' and
(
exists
(
select 'x' from paymentdetail
where
contactid = d.contactid and
paymentmethod = 'DIRECTDEBIT' and
datacashref is not null and
drawdownday is not null and
case
when to_number(to_char(d.invoicedate,'D')) >= to_number(to_char(drawdownday)) then
(case when to_date(drawdownday||'/'||to_number(to_char(add_months(d.invoicedate,1),'MM'))||'/'||to_number(to_char(d.invoicedate,'YYYY')),'dd/mm/yyyy') < (sysdate - 10) then 'TRUE'
else 'FALSE' end)
else
(case when to_date(drawdownday||'/'||to_number(to_char(d.invoicedate,'MM'))||'/'||to_number(to_char(d.invoicedate,'YYYY')),'dd/mm/yyyy') < (sysdate - 10) then 'TRUE'
else 'FALSE' end) end = 'TRUE'
)
)
AND NOT EXISTS
(select 'x' from debtor_mv
where
outstandingamount > 0 and
invoicedate < '01-MAR-06' and
contactid = d.contactid)
AND
NOT EXISTS
(
select 'x'
from saleslead
where contactid=d.contactid AND
leadtype = ?
)

group by d.contactid
having sum(d.outstandingamount) >= 20
)