we have a lot of delphi and forms3 apps and Session_Cached_Cursors is zero for my database (7.3.4). I am thinking of setting the above parameter to 50. Also would monitor the stat 'session cursor cache hits' before and after setting the parameter. Do i need to increase/decrease any other parameter with this change?
btw found this bug [BUG:931820] Direct Load Fails When Session_Cached_Cursors is larger than 0 Pls advise if i am on a wrong track. Thanks Mandar -----Original Message----- Sent: Thursday, July 25, 2002 11:49 AM To: Multiple recipients of list ORACLE-L When a SQL execution is requested the Shared SQL is first examined to see if the statement is in memory. The first time SQL is processed it goes through a hard parse, the most expensive parsing operation. A hard parse performs the following: checking syntax; validating all database objects referenced, (tables and columns); naming translation, (synonyms); authenticating user privileges on all tables and columns; producing a SQL execution plan via the optimizer; hashing and storing the parsed statement in the Shared SQL Area. If the SQL statement is found in the Shared Pool then a soft parse may be performed in an attempt to use a shareable cursor. There are three types of soft parses: 1) The first time a SQL statement is found in the shared pool Oracle performs name translation, user authentication, and adds the user to the authentication list. 2) On the second soft parse name translation does not need to be performed but user authentication does just in case user privileges were changed since the last execution.; 3) An entry is created for the session's cursor cache and future cursor CLOSEs are ignored. Once in the session cursor cache the SQL statement does not need to be reparsed. This gives a significant performance boost! Giving credit where due: The above was "inspired" from pages 277-280 in "Scaling Oracle8i" by James Morle. Steve Orr Bozeman, Montana -----Original Message----- Sent: Wednesday, July 24, 2002 11:08 PM To: Multiple recipients of list ORACLE-L Importance: High Please define soft parsing. Oracle needs to check that the user submitting a SQL statement has permissions to run it. It has to do this every time a statement is run, bind variables or not. I thought the processing of the statement to check permissions to be soft parsing. But, perhaps I'm misinformed. When "cursor-sharing" converts a statement to use bind variables it would save on hard parsing, if a match were found the pool; also, it could lessen the number of statements present in the pool. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandar A. Ghosalkar 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).
