The impact of parsing in a database can be very variable. In some cases, most fortunately, it is not noticeable. In other cases but can cause major problems specific performance of the database. In general the problems of excessive parsing due to poor programming, ie it must analyze and address from the application code that interact with the base. That's why developers should be aware of the negative impact that can lead to poor planning and consideration of this item as a priority since the beginning of the construction of the code.
The parsing is the first step is performed to process a sentence. At this stage you must know what kind of sentence in question (DML, DDL or select) in order to make the appropriate checks. The main activities are checking syntactic and semantic analysis Syntactical check
This check verifies if the statement complies with the grammar of the sentence set for the base version. Semantic Analysis
analyze whether the objects are referenced in the sentence, if the columns exist, if you have access to the segments and columns (privileges, etc.).
Once you successfully pass the two stages mentioned above, Oracle searches the memory (shared pool) to see if it was executed the same sentence for another session. If found, then we say that we performed a SOFT PARSE. On the other hand, if not found, there are two additional steps, which are the optimization of the ruling and plan generation and load into memory (row source generation). The implementation of all steps is called HARD PARSE. The hard parsing is CPU intensive, and if that can be raised serious obligation under general performance given the high containment is raised. To avoid hard parsing is to use BIND variables in the statements (eg use PreparedStatement). If code is a "canned" where not used and can not be changed binds can be used in the base CURSOR_SHARING, whose default is EXACT and would have to change it to SIMILAR (9i there from) or FORCE, but always recommend using SIMILAR, because it is less risky.
The soft parse can be even more soft if the cursor is cached in the session (session_cached_cursor) and so avoided going to the shared pool to get it. Since the code for the app you can enable and define the most suitable cache size (eg ((oracle.jdbc.OracleConnection) connection). SetStatementCacheSize (40)). This is available in almost all interfaces (JDBC,. NET, PL / SQL, OCI, etc).
To avoid reparse in a session must be kept open the cursor. Some interfaces such as PL / SQL, and oci jdbc allow this. OLE DB interface, SQLJ or ODP, at least until the last version I know, do not. Then I will copy 3 fragments of Java code to show the difference between parsing hard, soft and do not parse.
The first fragment below shows the use of binding NO, since concatenating the literal and not using PreparedStatement
------- TEST 1
sql = "SELECT X FROM T WHERE Y = ";
for (int i = 0; i
{statement = connection.createStatement ();
resultset = Statement.executeQuery (sql + Integer.toString (i));
if (ResultSet.next ()) {
val = ResultSet.getString ("X");}
ResultSet.close (); statement.close ();}
This code, and performance is very poor, gives rise to hacking by sql injection.
The second piece uses binding but opens and closes the cursor in each performance which generates soft parse.
TEST 2 -------
sql = "SELECT X FROM T WHERE Y =?"
for (int i = 0; i {
= Connection.prepareStatement statement (sql);
statement.setInt (1, i);
Statement.executeQuery resultset = ();
if (ResultSet.next ()) {
ResultSet.getString val = (X) ;}
ResultSet.close ();
statement.close ();}
The last fragment, which is optimal, reduces the parsing to a minimum (just a soft parsing): TEST 3
-------
sql = "SELECT X FROM T WHERE Y =?" = Connection.prepareStatement statement (sql); for (int i = 0 , I {
statement.setInt (1, i);
Statement.executeQuery resultset = ();
if (ResultSet.next ()) {
val = ResultSet.getString ("X");}
resultset . close ();}
statement.close ();
In a test I made the response times of each test were:
TEST1 -> 12.2 "
TEST2 -> 6.4"
TEST2 (caching) -> 3.9 "
TEST3 -> 3.7"
TEST3 (caching) -> 3.7 "
As seen above, the TEST2 can be improved using caching, but when using caching in TEST3 undistinguishable.
The parser can be viewed as a mini compilation, we could compare a code that runs in a loop a prepareStatement for each statement with an interpreted code. Any programmer knows that the execution of compiled code is faster to run a code that needs to be interpreted line by line.
int.int_sec,2) "User Calls", 
int.int_sec,2) "Parses Hard", 
user_commits-lead(user_commits) 
max(decode(ss.stat_name,'user rollbacks',value,null)) user_rollbacks, 
max(decode(ss.stat_name,'parse count (total)',value,null)) parses, 
where s.snap_id = ss.snap_id 
where t.snap_time = Int.snap 
