Re: ORA-01037: maximum cursor memory exceeded

Hi Scott,

Yes, this is indeed an interesting one. What I can think of is a) different block_size of the dev database on unix (default is 4k, instead of default of 2k for NT)
b) cost based optimization on unix
c) different db_file_multiblock_read_count This would make sense, as cost based optimization calculates the total IO's involved. If you make db_file_multiblock_read_count smaller, it gets more biased towards using indexes (fine tric, isn't it)

Please keep in mind, rule based optimization is desupported in Oracle 8. Of course, you will be able to use hints, but you probably don't want to program them everywhere.
In many cases however, it can be demonstrated cost based optimization does provide better results, ie much less IO. I'm interested to hear whether or not my 'guesses' are correct, there should be differences of this kind between the two dev instances.

Thanks and regards,

Sybrand Bakker, Oracle DBA

Scott Patterson wrote:

> I have an interesting one. I have a production box that gives the error
> "ORA-01037: maximum cursor memory exceeded". If you look that up, the
> solution is to simplify the query. Now for the interesting part. The two
> development boxes have 1/10 the amount memory allocated to oracle as the
> production machine. Basically all the sizing parameters on the production
> box are 10 times that of the dev boxes. Neither of the dev machines have a
> problem with the query. We have even loaded a complete dump of the
> production database on the two dev boxes.
>
> If you run an explain plan on the production machine and the NT dev machine
> you get the same very large query plan. On the dev unix box the plan is a
> simple table scan.
>
> Dev works every time. Prod errors every time.
>
> Oracle 7.3
> Prod DEC Unix
> Dev1 DEC Unix
> DEV2 Intel based NT
> All machines are using rule based optimization (and would prefer to remain
> that way).
> 1.5 million rows in tab1
>
> The query is appended to this message.
>
> Anyone run into this before? Ideas?
>
> Scott
>
> SELECT tab1.id, tab1.name, tab1.org, tab1.pricing_id, tab1.amount,
> TO_CHAR(tab1.start_date, 'YYYY-MM-DD HH24:MI:SS'),
> TO_CHAR(tab1.end_date, 'YYYY-MM-DD HH24:MI:SS')
> FROM tab1
> WHERE tab1.name
> IN ('1','2',..... About 50 literals )
> AND tab1.org
> in ('A','B'...About 10 literals)
> AND tab1.start_date <= TO_DATE('06/11/1998 00:00:00','MM/DD/YYYY
> HH24:MI:SS',
> 'NLS_DATE_LANGUAGE=American') AND tab1.end_date >=
> TO_DATE('06/11/1998 00:00:00','MM/DD/YYYY
> HH24:MI:SS','NLS_DATE_LANGUAGE=American')
> ORDER BY tab1.org ASC
Received on Fri Sep 25 1998 - 14:27:33 CDT