The algorithm used to find SQL in the library cache is based on the
ASCII values of the characters that make up the statement. Means The
SELECT
instead ofselect
statement will be parsed again in library
cache.But Some where i read that the oracle treat's lowercase
select
as
upperSELECT
automaticaly in a statement
Which on is true above those two…
Best Answer
There is nothing specific about
SELECT
here. There is no semantic difference betweenSELECT
,select
andSeLeCt
.The issue being described is that if you issue these three semantically identical statements:
the database treats them as three entirely different entities, that will each occupy some space in the library cache. They will each have been parsed individually, costing CPU each time, even if the resulting execution plan are are identical.
Same thing if you have two identical queries but one with a comment and another without - they will be treated as different statements entirely by the engine, and each consume resources. (I believe even spacing matters.)
(This is actually helpful for debugging. If you're trying different settings to optimize a query without changing the query text, add a comment describing the current settings each time you run it. You're guaranteed a re-parse, and can find the individual runs in
[g]v$sql
easily with those comments. This wouldn't work if the engine "normalized" the query text before parsing.)What this means for you is that you should be consistent with your query syntax. Choose a "coding" style and stick with it to avoid littering the various caches with semantically identical queries.
Example: notice the three different SQL IDs.