0

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 of select statement will be parsed again in library cache.

But Some where i read that the oracle treat's lowercase select as upper SELECT automaticaly in a statement


Which on is true above those two...

2
  • I think select and SELECT do same thing, When you use select, it's parsed to SELECT automatically
    – Nivedita
    Jul 17 '14 at 5:33
  • yeah you r true ... but it is also true that if we write SELECT instead of select oracle will parse again in library cache... But is should not be if oracle is taking lowercase select to upper SELECT... i am confused by this
    – emily soto
    Jul 17 '14 at 5:38
4

There is nothing specific about SELECT here. There is no semantic difference between SELECT, select and SeLeCt.

The issue being described is that if you issue these three semantically identical statements:

 SELECT thing FROM mytable;

 select thing from mytable;

 Select Thing From MyTable;

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.

select a from foo;
Select a From foo;
SELECT a FROM foo;

select * from v$sql where sql_text like '%foo%';

SQL_ID        SQL_TEXT
------------- -------------------
dxdhrwqjpn6b7 Select a From foo  
0dqb3y3bask98 select a from foo  
1y13x9j2rwzuz SELECT a FROM foo  
...
3
  • Looks like I would have lost my bet! Fair play to you for running that query - I don't have an Oracle system to hand... Good explation as to the rationale, if indeed this was intended or is it just a fortunate side effect of a mistake? It would make far more sense to me for the type of debugging you're referring to for Oracle to have put in a query hint keyword like no_shared_pool or something, rather than have the potential for waste with large systems with many programmers each with their own style of coding - uniformity difficult to enforce - how many people know this? Or would guess? Thx.
    – Vérace
    Jul 17 '14 at 6:13
  • You're unlikely to cause problems with just random people entering a query manually slightly differently - the majority of SQL on a real database is either from stored procedures or an application (generated SQL or not) and will be "mechanical". Could be a problem if you let end-users influence the actual SQL code - most problematic being not using bind variables and having hundreds of different versions of the same SQL with different parameters. It is intended and documented. Producing some canonical form of SQL is probably not worth the effort - wouldn't help the most problematic case.
    – Mat
    Jul 17 '14 at 6:40
  • Hmm... I'd still say an SQL_no_case_no_space=1 (default) global variable which could be changed on a session level would be a better solution. You've given me some food for thought though - that's what this site is for.
    – Vérace
    Jul 17 '14 at 7:40
2

Check the Oracle documentation here. There is a hash associated with queries in the shared pool (what Oracle calls the query cache) - so you could issue two - one with caps, one with lower case and check the hash and see if they're the same. [EDIT - removed rash speculation]

1
  • The hashes will not be the same. That's what the quoted text in the question refers to.
    – Mat
    Jul 17 '14 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.