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...
----- Original Message -----
Sent: Monday, September 30, 2002 7:48
AM
Subject: RE: Does the case of an Oracle
query statement affect query perfo
Raj,
Do
you have any test cases or white papers to support your statement?
Especially the part about
"if you mix-n-match that will make Oracle do
more work."
never heard of this before and I am interested if it is
true.
Tom Mercadante
Oracle Certified Professional
As long as you stick to either (a) or (b) you will be okay ... if
you mix-n-match that will make Oracle do more work.
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!
Hi,
Pardon me for such a naive
question, coz I am a novice when it comes to Oracle. This is basically
got to do with how Oracle parses a query.
Consider the following
queries:
a) SELECT column1, column2
FROM table WHERE column0 = 5;
b) SELECT COLUMN1, COLUMN2
FROM TABLE WHERE COLUMN0 =5;
Scenario 1: I use the naming
convention a) for ALL my queries
Scenario 2: I use the naming
convention b) for ALL my queries
Will there be any difference
in the execution time of the same queries in Scenario 1 vs
2?
Thanx
in advance,
Shantanu.
--------------
