Characters must match in Shared Pool

Oracle is very picky when it comes to deciding whether a newly submitted SQL statement matches one that is already in the shared SQL area.
Character case, white space, and comments are all significant. Oracle matches two statements based on a character-by-character comparison, not by comparing the semantics, or meaning, of the statements.
The following SQL statements, which are all semantically the same, would not be considered identical:

The shared pool caches various types of program data. For example, the shared pool stores parsed SQL, PL/SQL code, system parameters, and data dictionary information. The shared pool is involved in almost every operation that occurs in the database. For example, if a user executes a SQL statement, then Oracle Database accesses the shared pool. The shared pool is divided into several subcomponents.

The database represents each SQL statement that it runs in the following SQL areas:

1. Shared SQL area: The database uses the shared SQL area to process the first occurrence of a SQL statement. This area is accessible to all users and contains the statement parse tree and execution plan. Only one shared SQL area exists for a unique statement .
2. Private SQL area: Each session issuing a SQL statement has a private SQL area in its PGA (see "Private SQL Area" on page 14-5). Each user that submits the same statement has a private SQL area pointing to the same shared SQL area. Thus, many private SQL areas in separate PGAs can be associated with the same shared SQL area.

The database automatically determines when applications submit similar SQL statements. The database considers both SQL statements issued directly by users and applications and recursive SQL statements issued internally by other statements. The database performs the following steps:

1. Checks the shared pool to see if a shared SQL area exists for a syntactically and semantically identical statement:
   a) If an identical statement exists, then the database uses the shared SQL area for the execution of the subsequent new instances of the statement, thereby reducing memory consumption.
   b) If an identical statement does not exist, then the database allocates a new shared SQL area in the shared pool. A statement with the same syntax but different semantics uses a child cursor. In either case, the private SQL area for the user points to the shared SQL area that contains the statement and execution plan.
2. Allocates a private SQL area on behalf of the session: The location of the private SQL area depends on the connection established for the session. If a session is connected through a shared server, then part of the private SQL area is kept in the SGA.

Figure 5-7 shows a dedicated server architecture in which two sessions keep a copy of the same SQL statement in their own PGAs. In a shared server, this copy is in the UGA, which is in the large pool or in the shared pool when no large pool exists.

In addition to case, white space, and comments, Oracle also checks to be sure that the objects referenced by the two statements are the same.
Consider the case where users Jenny and Jeff each own identical tables named coin. Jenny and Jeff each issue the following SQL statement:

select * from coin

In Jenny's case, the object coin refers to the table jenny.coin. In Jeff's case, it refers to the table jeff.coin. Even though the two statements match when compared on a character basis, the objects referred to are different. Because the objects are different, Oracle will not consider the statements to be identical.