Regarding flushing the shared pool... A couple of weeks ago I had hard copies of 2 Metalink docs side by side on my desk and I highlighted paragraphs on each which were completely contradictory. One said flushing did absolutely no good and the other said it could help. In practice it doesn't seem to do much for severe 4031 type fragmentation. Pinning large stuff at db start up can help but if you've got a lot of SQL with literals then consider setting cursor_sharing = TRUE as well. With a small db just enlarging the shared pool may be enough. Of course if you have control over the code then I think using bind variables is still the preferred solution.
Steve Orr -----Original Message----- Sent: Friday, October 26, 2001 6:05 AM To: Multiple recipients of list ORACLE-L You can either issue an "alter system flush shared pool", or increase the value of your shared_pool_size. Another option would be to pin some or your most used/larger packeges in to your shared pool. HTH Mark -----Original Message----- Sent: Friday, October 26, 2001 10:55 To: Multiple recipients of list ORACLE-L Dear list, we have a small database with very few users, Oracle 8.1.7, NT 4 SP5. A NT-service holds a permanent connection and checks, if some data export are about to be done. The database had now an uptime of several months and each new connect raises ORA-04031. Seems to be a fragmentation problem of the shared pool. How can I prevent that? Thanx very much Mit freundlichen Gr��en i.A. Marc Blum SOPTIM AG Gr�ner Weg 22-24 D-52070 Aachen Telefon: +49 241 / 9 18 79-33 Fax: +49 241 / 15 40 38 mailto:[EMAIL PROTECTED] http://www.soptim.de -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Blum, Marc INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
