How Query works

Before beginning to code any application, programming standards must be coded. Having moved beyond the rigors of 3GLs and their particular constraints, people question the need for such tight programming controls. However, they are still necessary perhaps even more so in working with SQL.

Coding standards for individual SQL statements are particularly important because of the way that the RDBMS engine handles the statements internally. When a SQL statement is parsed within the database the following steps are performed:

Check syntax
Is the SQL correctly structured? Do all the opening parentheses match the closing parentheses?

Search shared SQL area
Does a parsed copy of the same statement (i.e., in pcode form) already exist? Oracle searches a common area of the System Global Area (SGA) known as the Shared Pool. Among the structures of the Shared Pool is the Library Cache and its Shared SQL Area, which holds SQL statements. See the diagram below.

Search data dictionary
Performs object resolution (checks views, synonyms, etc.) to ensure that the specified objects actually exist. Checks security privileges to ensure that the user issuing the SQL statement has the appropriate permissions to access the objects.

Calculates search path
The Rule Based Optimizer (RBO) or the Cost Based Optimizer (CBO) is used to determine the optimal execution path There can be multiple paths for the database to satisfy the query. The Optimizer determines which execution path is the most optimal. The RBO uses a predefined set of rules to make those determinations. The CBO uses statistics on data distribution and makeup to help make better decisions. In Oracle 10g, the RBO is desupported and the CBO should be used instead.

Saves execution plan
The execution plan is stored in the Shared Pool. The next time the same SQL statement is issued, the work of parsing, evaluating, and determining the execution plan does not have to be done. Executing SQL statements becomes more efficient when the work is shared.

The critical factor is the second step of SQL parsing, "Search shared SQL area." This is where SQL standards for individual statements become important. SQL cannot be shared within the SGA unless it is absolutely identical. Statement components that must be the same include:

Word case (uppercase and lowercase characters)
 
Whitespace
 
Underlying schema objects
The following SQL statements are not the same and will not be shared within the SGA:

sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 212;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID = 213;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID = :b1;
sql> SELECT name FROM s_customer WHERE id = 212;
sql> SELECT NAME FROM S_CUSTOMER WHERE ID=212;
sql> SELECT NAME
FROM S_CUSTOMER
WHERE ID=212;
Oracle recognized that two SQL statements can differ only in their literal values. For instance, the first two statement above are identical except for the value on the right side of the equals sign (212 in the first statement and 213 in the second statement). It would be nice to be able to share the cursors in the Shared Pool.

In Oracle 8i, the CURSOR_SHARING initialization parameter was introduced to help with this problem. The default for CURSOR_SHARING is EXACT, meaning that two cursors must match exactly in order for the cursors to share the same location in the Shared Pool. In Oracle 8i,  the CURSOR_SHARING parameter can be set to FORCE, which tells the database that if two SQL statements differ only in their literals, their cursors will be shared. When set to FORCE, the database will replace all literals with bind variables for you. At first glance, the CURSOR_SHARING parameter seemed to be a silver bullet for performance problems plaguing applications that did not use bind variables. Unfortunately, this new parameter's effects did not take into affect the distribution of the data. Below is an example showing why setting CURSOR_SHARING to FORCE can have a problem. Look at the data distribution of the STYLE_NUM column of our BOX table.

SQL> select style_num,count(*) from box
2 group by style_num;
STYLE_NUM COUNT(*)
---------- ----------
10 1
11 1
12 2
13 8
There is only one row of data where STYLE_NUM is 10 or 11, but eight rows of data where STYLE_NUM is 13. If the following two queries are issued, then the database shares their cursors:

select * from box where style_num = 10;
and

select * from box where style_num = 11;
In these cases, only one row would be returned and the hope is to use the index on the STYLE_NUM column. Now that those queries have been executed, if CURSOR_SHARING is set to FORCE and the following query is issued

select * from box where style_num = 13;
this will use the same index since it used the same cursor. In this case, an index is not desirable since 8 of the table's 12 rows are being returned. It would be desirable to not use the index.

Oracle 9i fixed this problem by adding another value to the CURSOR_SHARING initialization parameter, SIMILAR. If CURSOR_SHARING is set to SIMILAR, then the database will only share cursors if they differ in their literals and that literal value would not significantly alter the execution plan. If that literal value would not alter the execution plan, then the cursor will not be shared. A new cursor will be used instead. The CURSOR_SHARING = SIMILAR initialization parameter value is a much better choice.