Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Cursor Sharing| Soft Parsing

RE: Cursor Sharing| Soft Parsing

From: Orr, Steve <>
Date: Thu, 25 Jul 2002 10:48:50 -0800
Message-ID: <>

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

Please see the official ORACLE-L FAQ:
Author: Orr, Steve

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: (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).
Received on Thu Jul 25 2002 - 13:48:50 CDT

Original text of this message