Thanks Tim, I didn't knew these differences between PROC/PLSQL and other modules ...
Raj
______________________________________________________
Rajendra Jamadagni MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
From: Tim Gorman [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 11:04 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: Does the case of an Oracle query statement affect query perfo
All that he is referring to is the possibility that "mixing-n-matching" will cause the same SQL statement to be hashed differently, thus stored individually in the Shared SQL Area cache, thus more "hard parses" unnecessarily. More "hard-parses" is indeed "more work"...
Though technically correct, there are many steps between someone coding a SQL statement and this end-result of additional hard-parses...
If a developer or end-user is working via a precompiler/interpreter such as PRO*C, SQLJ, or PL/SQL or many other reporting tools, then the upper- and lower-case issues will be largely made irrelevant as the precompiler/interpreter tends to set all SQL command-text some similar convention before passing to the RDBMS (i.e. all upper-case and remove all unnecessary white-space, etc)...
If it is not SQL developers writing this SQL into program-modules but instead end-users working interactively, then you have to ask yourself how many times they can type in and execute SQL in order for the increased number of "hard-parses" to matter. Assume 200 ad-hoc interactive end-user sessions, each typing in and executing slightly different SQL 20 times per day. That's 4000 more "hard-parses" -- no big deal...
There are likely more circumstances to consider...
However, if the people doing this coding are developers working in a low-level API such as OCI (i.e. C or C++), DBI::DBD (i.e. Perl), or JDBC (i.e. Java), then this SQL text will be sent straight to the RDBMS parser where it will indeed cause additional hard-parses. Since this code might be embedded inside a high-concurrency application, this problem could grow quite serious, especially if the developers follow-up this particular "bad habit" with other bad habits such as embedded literal data values, etc...
As always, the severity of the problem is dependent on specific circumstances. It could be no problem at all, it could be the harbinger for serious problems...
*********************************************************************This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*********************************************************************1
