[vwnc] Glorp : prepared statement cache
Locked
[vwnc] Glorp : prepared statement cache
 
|
Hello,
while writing a lot of data with Glorp (VW 7.6) to an Oracle 10g DB, Oracle occasionally raises an error: "ORA-01000 Maximum open cursors exceeded". The OPEN_CURSORS variable of Oracle is set to 200 (default). The DB has about 30 tables. The tables are written in random order. The data is read from another DB, which has a different layout, so it would be difficult to write table by table instead. Glorp uses a weak cache with 20 strong references to cache prepared statements. So if more than 20 statements are performed in random order, and the weak finalization is delayed, the number of open cursors can exceed OPEN_CURSORS. It would be possible to work around this issue - use more than 20 strong references to avoid cache thrashing - set OPEN_CURSORS to a higher value But IMHO the correct solution would be to use a MRU cache with a fixed maximum size for prepared statements. This maximum size can then be set to the number of open cursors that are allowed for a session of the user. _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: [vwnc] Glorp : prepared statement cache
|
|
> But IMHO the correct solution would be to use a MRU cache with a fixed
> maximum size for prepared statements. This maximum size can then be set > to the number of open cursors that are allowed for a session of the user. .... which would require that no other prepared statements that allocate resources are in use --- bummer! anyway, most of the prepared statements that were hanging around were statements to fetch sequence numbers, which are not cached. Making sure that these statements are relased immeediately after the sequence is fetched, instead of relying on weak finalization, would be desirable. _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: [vwnc] Glorp : prepared statement cache
|
|
>> But IMHO the correct solution would be to use a MRU cache with a fixed
>> maximum size for prepared statements. This maximum size can then be set >> to the number of open cursors that are allowed for a session of the user. > > .... which would require that no other prepared statements that allocate > resources are in use --- bummer! > > anyway, most of the prepared statements that were hanging around were > statements to fetch sequence numbers, which are not cached. Making sure > that these statements are relased immeediately after the sequence is > fetched, instead of relying on weak finalization, would be desirable. sorry for spamming ;) but I think i've finally found the reason for this behavior: there seems to be a bug in GlorpCursoredStream>>release ... databaseAccessor reusePreparedStatements ifTrue: [statement busy: false] ifFalse: [statement release] .... the accessor uses prepared statements, but the command to fetch sequence numbers does not use prepared statements. so instead of releasing the session, the busy flag is set (which is useless in this context) _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: [vwnc] Glorp : prepared statement cache
|
|
At 07:49 AM 2009-09-22, Holger Kleinsorgen wrote:
>> But IMHO the correct solution would be to use a MRU cache with a fixed Hmmm. Now I wonder why it is that I've not run into this error. So if that's the reason, it seems to me that it might be sufficient in basicExecuteSQLString:returnResult:binding:doing: to change the call to newPreparedStatementFor: to just call statementFor: instead. Then those statements would be handled by the weak array the same as everything else. That's a bit of a waste, because right now we never reuse them (would be nicer to use binding those for the parameter values), but ought to at least get rid of the errors, or defer them to the point where you really were choking the finalization mechanism with the number of prepared statements. --
Alan Knight [|], Engineering Manager, Cincom Smalltalk
_______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: [vwnc] Glorp : prepared statement cache
|
|
In reply to this post by Holger Kleinsorgen-4
>> sorry for spamming ;) but I think i've finally found the reason for this
>> behavior: there seems to be a bug in GlorpCursoredStream>>release >> >> ... >> databaseAccessor reusePreparedStatements >> ifTrue: [statement busy: false] >> ifFalse: [statement release] >> .... >> >> >> the accessor uses prepared statements, but the command to fetch sequence >> numbers does not use prepared statements. so instead of releasing the >> session, the busy flag is set (which is useless in this context) > > Hmmm. Now I wonder why it is that I've not run into this error. > > So if that's the reason, it seems to me that it might be sufficient in > basicExecuteSQLString:returnResult:binding:doing: to change the call to > newPreparedStatementFor: to just call statementFor: instead. Then those > statements would be handled by the weak array the same as everything > else. That's a bit of a waste, because right now we never reuse them > (would be nicer to use binding those for the parameter values), but > ought to at least get rid of the errors, or defer them to the point > where you really were choking the finalization mechanism with the number > of prepared statements. the source DB heavily uses the weak finalization mechanism, which might explain why no one else has encountered this problem yet. I've modified OracleSequence to use a prepared statement. It seems to be a good candidate, because the statement is used quite often. _______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
Re: [vwnc] Glorp : prepared statement cache
|
|
Thanks. It's probably too late for 7.7, but I've created AR
58252, and we should get that addressed shortly after that.
At 02:02 PM 2009-09-22, Holger Kleinsorgen wrote: >> sorry for spamming ;) but I think i've finally found the reason for this --
Alan Knight [|], Engineering Manager, Cincom Smalltalk
_______________________________________________ vwnc mailing list [hidden email] http://lists.cs.uiuc.edu/mailman/listinfo/vwnc |
«
Return to VisualWorks
|
1 view|%1 views
| Free forum by Nabble | Edit this page |