Each time a SQL statement is executed, the statement is searched for in the shared SQL area and, if found, used for execution. This saves parsing time and improves overall performance. Therefore, to ensure optimal use of the shared SQL area, use stored procedures as much as possible since the SQL parsed is exactly the same every time and, therefore, shared.
However, keep in mind, that the only time the SQL statement being executed can use a statement already in the shared SQL area is if the statements are identical (meaning they have the same content exactly—the same case, the same number of spaces, etc.). If the statements are not identical, the new statement is parsed, executed, and placed in the shared SQL area (exceptions to this are possible when the initialization parameter CURSOR_SHARING has been set to SIMILAR or FORCE).
In the following example, the statements are identical in execution, but the word from causes Oracle to treat the two statements as if they were different, thus not reusing the original cursor that was located in the shared SQL area:
TIP
SQL must be written exactly the same to be reused. Case differences and any other differences will cause a reparse of the statement.
In the following example, I am using different values for ENAME, which is causing multiple statements to be parsed:
A query of V$SQLAREA shows that two statements were parsed even though they were very close to the same thing. Note, however, that PL/SQL converted each SQL statement to uppercase and trimmed spaces and carriage returns (which is a benefit of using PL/SQL):
In the following example, there is a problem with third-party applications that do not use bind variables (they do this to keep the code “vanilla” or capable of working on many different databases without modification). The problem with this code is that the developer has created many statements that fill the shared pool and these statements can’t be shared (since they’re slightly different). You can build a smaller shared pool so there is less room for cached cursors and thus fewer cursors to search through to find a match (this is the Band-Aid inexperienced DBAs use). If the following is your output from V$SQLAREA, you may benefit from lowering the SHARED_POOL _SIZE, but using CURSOR_SHARING is a better choice.
Setting CURSOR_SHARING=FORCE and the query to V$SQLAREA will change to the one listed next because Oracle builds a statement internally that can be shared by all of the preceding statements. Now the shared pool is not inundated with all of these statements, but only one simple statement that can be shared by all users:
Leave a Reply