Locating similar SQL in Oracle

Oracle bind variables are a super important way to make Oracle SQL reentrant.

The dictionary columns exact_matching_signature, and force_matching_signature exist in v$sqlarea, v$sql, stats$sql_summary and v$active_session_history.
 
The Oracle docs note that these signature columns are "normalized" (e.g. standardized" representations of SQL statements, thereby allowing them to be compared with dictionary queries:
 
The "signature [is] calculated on the normalized SQL text. The normalization includes the removal of white space and the uppercasing of all non-literal strings." It also says that FORCE_MATCHING_SIGNATURE is a "signature used when the CURSOR_SHARING parameter is set to FORCE". The signature seems to be just another hash value calculated from SQL statement, but this time, it's a hash of normalized (removed spaces, etc.) SQL statement.
 
FORCE_MATCHING_SIGNATURE is calculated from SQL text as if CURSOR_SHARING would be set to TRUE. (You don't have to set it to TRUE to get the signature values.) CURSOR_SHARING=FORCE forces SQL statements to share cursors by replacing constants with bind variables, so all statements that differ only by constants share the same cursor. Let's have a look at FORCE_MATCHING_SIGNATURE values for the same SQLs"

The activities needed to parse a statement and generate an execution plan are CPU intensive and generate recursive SQL against the data dictionary which may result in physical IO as well. The added statement and parse tree takes up space in the shared pool. I have seen several databases where the shared pool was over a gigabyte in size (one were it was 4 gig!) until bind variables were introduced, reducing the size to a couple of hundred megabytes at most.

A quick method of seeing whether code is being reused (a key indicator of proper bind variable usage) is to look at the values of reusable and non-reusable memory in the shared pool. A SQL for determining this comparison of reusable to non-reusable code is shown in figure 1.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

Figure 1: SQL Code to Show Shared verses Non-shared code

An example report is shown in Figure 2 for an instance with poor code reuse characteristics. The names have been changed to protect the innocent.

Figure 2: Example report output for poor code reuse

As you can see from Figure 2 the majority owner in this application, WHOAPP is only showing 0.3 percent of reusable code by memory usage and is tying up an amazing 530 megabytes with non-reusable code! Let's look at a database with good reuse statistics. Look at Figure 3.

Figure 3: Example of Good Sharing of Code

Notice in Figure 3 how the two application owners, DBAVILLAGE and DBASTAGER show 89.513 and 96.812 reuse percentage by memory footprint for code.

So what else can we look at to see about code reusage, the above reports give us a gross indication, how about something with a bit more usability to correct the situation? The V$SQLAREA and V$SQLTEXT views give us the capability to look at the current code in the shared pool and determine if it is using, or not using bind variables. Look at Figure 4.

For complete scripts, see my complete Oracle script collection at www.dba-oracle.com/oracle_scripts.htm.

Figure 4: Similar SQL report code

Figure 4 shows a simple script to determine, based on the first x characters (input when the report is executed) the number of SQL statements that are identical up to the first x characters. This shows us the repeating code in the database and helps us to track down the offending statements for correction. An example output from the similar_sql.sql script is shown in Figure 5.

Figure 5: Example output from the similar_sql.sql report script.

As you can see from Figure 5, the SQL text is pinpointed that needs fixing. Using a substring from the above SQL the V$SQLTEXT view can be used to pull an entire listing of the code.

Some may be asking: "What is a bind variable?" simply put, a bind variable is a variable inserted into the SQL code in the place of literal values. For example:

SELECT * FROM whousers WHERE first_name='ANNA';

Is not using bind variables. If we issued a second query:

SELECT * FROM whousers WHERE first_name='GRINCH';

Even though the queries are identical until the last bit where we specify the name, the Oracle query engine would treat them as two different queries. By using bind variables, as shown below, we allow Oracle to parse the statement once and reuse it many times.

SELECT * FROM whousers WHERE first_name=:whoname;

The colon in front of the variable "whoname" tells Oracle this is a bind variable that will be supplied at run time.   

So, the proper fix for non-bind variable usage is to re-write the application to use bind variables. This of course can be an expensive and time consuming process, but ultimately it provides the best fix for the problem. However, what if you can't change the code? Maybe you have time, budget or vendor constraints that prevent you from being able to do the "proper" thing. What are your options?

Oracle has provided the CURSOR_SHARING initialization variable that will automatically replace the literals in your code with bind variables. The settings for CURSOR_SHARING are EXACT (the default), FORCE, and SIMILAR.

·        EXACT - The statements have to match exactly to be reusable

·        FORCE - Always replace literals

·        SIMILAR - Perform literal peeking and replace when it makes sense

We usually suggest the use of the SIMILAR option for CURSOR_SHARING. You can tell if cursor sharing is set to FORCE or SIMILAR by either using the SHOW PARAMETER CURSOR_SHARING command or by looking at the code in the shared pool, if you see code that looks like so:

SELECT USERNAME FROM whousers WHERE first_name=:"SYS_B_0"

This tells you that CURSOR_SHARING is set to either FORCE or SIMILAR because of the replacement variable :"SYS_B_O".

If you like Oracle tuning, you may enjoy my new book "Oracle Tuning: The Definitive Reference", over 900 pages of my favorite tuning tips & scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.